// returns true if data is set successfully else false public boolean setCellData( String sheetName, String colName, int rowNum, String data, String url) { // System.out.println("setCellData setCellData******************"); try { fis = new FileInputStream(path); workBook = new XSSFWorkbook(fis); if (rowNum <= 0) return false; int index = workBook.getSheetIndex(sheetName); int colNum = -1; if (index == -1) return false; sheet = workBook.getSheetAt(index); // System.out.println("A"); row = sheet.getRow(0); for (int i = 0; i < row.getLastCellNum(); i++) { // System.out.println(row.getCell(i).getStringCellValue().trim()); if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i; } if (colNum == -1) return false; sheet.autoSizeColumn(colNum); // ashish row = sheet.getRow(rowNum - 1); if (row == null) row = sheet.createRow(rowNum - 1); cell = row.getCell(colNum); if (cell == null) cell = row.createCell(colNum); cell.setCellValue(data); XSSFCreationHelper createHelper = workBook.getCreationHelper(); // cell style for hyperlinks // by default hypelrinks are blue and underlined CellStyle hlink_style = workBook.createCellStyle(); XSSFFont hlink_font = workBook.createFont(); hlink_font.setUnderline(XSSFFont.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); // hlink_style.setWrapText(true); XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE); link.setAddress(url); cell.setHyperlink(link); cell.setCellStyle(hlink_style); fos = new FileOutputStream(path); workBook.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); return false; } return true; }
public boolean setCellData(String sheetName, String colName, int rowNum, String data) { try { fis = new FileInputStream(path); workBook = new XSSFWorkbook(fis); if (rowNum <= 0) return false; int index = workBook.getSheetIndex(sheetName); int colNum = -1; if (index == -1) return false; sheet = workBook.getSheetAt(index); row = sheet.getRow(0); for (int i = 0; i < row.getLastCellNum(); i++) { // System.out.println(row.getCell(i).getStringCellValue().trim()); if (row.getCell(i).getStringCellValue().trim().equals(colName)) colNum = i; } if (colNum == -1) return false; sheet.autoSizeColumn(colNum); row = sheet.getRow(rowNum - 1); if (row == null) row = sheet.createRow(rowNum - 1); cell = row.getCell(colNum); if (cell == null) cell = row.createCell(colNum); // cell style // CellStyle cs = workbook.createCellStyle(); // cs.setWrapText(true); // cell.setCellStyle(cs); cell.setCellValue(data); fos = new FileOutputStream(path); workBook.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); return false; } return true; }
private void generateExcelDoc(String docName) throws FileNotFoundException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet overall = workbook.createSheet("Overall"); XSSFRow row = overall.createRow(0); XSSFCellStyle topStyle = workbook.createCellStyle(); topStyle.setAlignment(CellStyle.ALIGN_CENTER); XSSFCell theme = row.createCell(0); theme.setCellValue("Theme"); overall.autoSizeColumn(0); XSSFCell occurs = row.createCell(1); occurs.setCellValue("Occurrences"); overall.autoSizeColumn(1); XSSFCell prev = row.createCell(2); prev.setCellValue("Prevalence"); overall.autoSizeColumn(2); theme.setCellStyle(topStyle); occurs.setCellStyle(topStyle); prev.setCellStyle(topStyle); for (int i = 0; i < themes.size(); i++) { XSSFRow r = overall.createRow((i + 1)); XSSFCell c = r.createCell(0); c.setCellValue(themes.get(i).getName()); XSSFCell c1 = r.createCell(1); c1.setCellValue(themes.get(i).getTotalOccurs()); XSSFCell c2 = r.createCell(2); c2.setCellValue(calculatePrevalence(themes.get(i).getTotalOccurs(), lineCount)); } // This could be done in the previous loop but since we don't need // indices as much, we may as well use the cleaner for each loop for (Theme t : themes) { XSSFSheet themeSheet = workbook.createSheet(t.getName()); XSSFRow row1 = themeSheet.createRow(0); XSSFCell keyword = row1.createCell(0); keyword.setCellValue("Keyword"); keyword.setCellStyle(topStyle); XSSFCell occ = row1.createCell(1); occ.setCellValue("Occurrences"); occ.setCellStyle(topStyle); XSSFCell themePrev = row1.createCell(2); themePrev.setCellValue("Prevalence"); themePrev.setCellStyle(topStyle); for (int i = 0; i < t.getKeywords().size(); i++) { Keyword k = t.getKeywords().get(i); XSSFRow r = themeSheet.createRow((i + 1)); XSSFCell c = r.createCell(0); c.setCellValue(k.getName()); XSSFCell c1 = r.createCell(1); c1.setCellValue(k.getNumOccurs()); XSSFCell c2 = r.createCell(2); c2.setCellValue(calculatePrevalence(k.getNumOccurs(), t.getTotalOccurs())); } } FileOutputStream output = new FileOutputStream(docName); workbook.write(output); output.close(); }
@Override public Workbook writeWorkbook( Workbook workbook, String sheetTitle, List<List<String>> tableDataList, int headerNum) { if (workbook == null) workbook = new XSSFWorkbook(); int exportRecordNum = tableDataList.size(); // 导出的总记录数 int tableStartRowNum = headerNum > 0 ? headerNum : 0; // 表体数据开始行数 int sheetNum = exportRecordNum / (CommonConst.EXCEL_MAX_EXPORT_NUM - headerNum) + 1; // 工作表的页数 List<XSSFSheet> sheetList = new ArrayList<XSSFSheet>(); if (StringUtils.isEmpty(sheetTitle)) sheetTitle = "sheet"; for (int i = 0; i < sheetNum; i++) { // 在Excel工作簿中建一工作表 String sTitle = sheetTitle + (i + 1); XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sTitle); sheet.setSelected(true); // 设置工作薄为选中 sheet.setAutobreaks(true); sheet.setPrintGridlines(true); sheetList.add(sheet); } /** ***********************输出表头********************************* */ if (headerNum > 0) { for (XSSFSheet sheet : sheetList) { XSSFRow headRow = sheet.createRow(0); headRow.setHeightInPoints(20); for (int i = 0; i < headerNum; i++) { List<String> headerRowDataList = tableDataList.get(i); for (int j = 0; j < headerRowDataList.size(); j++) { XSSFCellStyle cellStyle = (XSSFCellStyle) createDefHeaderCellStyle(workbook); // 默认表头样式 createCell(headRow, j, headerRowDataList.get(j), cellStyle); } } // 固定表头 sheet.createFreezePane(0, 1); } } /** *********************输出表体内容************************* */ // 设置列样式 XSSFCellStyle columnStyle = (XSSFCellStyle) workbook.createCellStyle(); columnStyle.setFillBackgroundColor(HSSFColor.GREEN.index); columnStyle.setWrapText(true); if (tableDataList.size() > (tableStartRowNum + 1)) { for (int i = tableStartRowNum; i < exportRecordNum; i++) { List<String> rowDataList = tableDataList.get(i); XSSFRow row = null; int currentSheet = i / CommonConst.EXCEL_MAX_EXPORT_NUM; // 当前工作表的页数 XSSFSheet sheet = sheetList.get(currentSheet); int rowIndex = i - CommonConst.EXCEL_MAX_EXPORT_NUM * currentSheet; row = sheet.createRow(rowIndex); for (int colIndex = 0, colLength = rowDataList.size(); colIndex < colLength; colIndex++) { createCell(row, colIndex, rowDataList.get(colIndex)); } } } // 调整列的宽度(取第一列为基准) for (XSSFSheet sheet : sheetList) { for (int i = 0; i < tableDataList.get(0).size(); i++) { sheet.autoSizeColumn((short) i); sheet.setColumnWidth((short) i, (short) (sheet.getColumnWidth((short) i) + 1000)); } } return workbook; }
@Override public byte[] getImportTemplate(Boolean editProductType) throws IOException { XSSFWorkbook workBook = new XSSFWorkbook(); XSSFSheet productSheet = workBook.createSheet(PRODUCT_SHEETNAME); XSSFSheet typeSheet = createTypeSheet(workBook); if (!(true == editProductType)) { workBook.setSheetHidden( workBook.getSheetIndex(typeSheet), XSSFWorkbook.SHEET_STATE_VERY_HIDDEN); } XSSFRow titleRow = productSheet.createRow(0); List<TransformSetting> transSettings = new TransformSettingFactory().getProductSetting(); XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet); for (int i = 0; i < transSettings.size(); i++) { TransformSetting setting = transSettings.get(i); XSSFCell titleCell = null; titleCell = titleRow.createCell(i, Cell.CELL_TYPE_STRING); String cellValue = setting.getDispName(); titleCell.setCellValue(cellValue); productSheet.autoSizeColumn(i, true); int columnWidth = productSheet.getColumnWidth(i); int needWidth = cellValue.getBytes().length * 256; if (columnWidth < needWidth) { productSheet.setColumnWidth(i, needWidth); } if ("productTypeName".equals(setting.getPropertyName())) { Name type = workBook.createName(); String name = "type"; type.setNameName(name); // 定义名称 type.setRefersToFormula( "OFFSET(" + typeSheet.getSheetName() + "!$A$3,0,0,COUNTA(" + typeSheet.getSheetName() + "!$A:$A)-2)"); XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) helper.createFormulaListConstraint(name); // 使用定义的名称 CellRangeAddressList regions = new CellRangeAddressList(1, 10, i, i); DataValidation dataValidation = helper.createValidation(constraint, regions); productSheet.addValidationData(dataValidation); } else if ("productTypeItem".equals(setting.getPropertyName())) { // item是通过查询前面的名称来自动取得 for (int j = 0; j < 10; j++) { XSSFRow row = productSheet.createRow(j + 1); XSSFCell itemCell = row.createCell(i, Cell.CELL_TYPE_STRING); int cellnum = j + 2; itemCell.setCellFormula( "IF(A" + cellnum + "=\"\",\"\",VLOOKUP(A" + cellnum + ",OFFSET(productType!$A$3,,,COUNTA(productType!$A:$A),2),2,0))"); } } else if ("costMethod".equals(setting.getPropertyName())) { int len = CostMethod.values().length; String[] costNames = new String[len]; for (int ci = 0; ci < len; ci++) { costNames[ci] = CostMethod.values()[ci].getMethodName(); } // XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet); XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) helper.createExplicitListConstraint(costNames); CellRangeAddressList addressList = new CellRangeAddressList(1, 10, i, i); XSSFDataValidation validation = (XSSFDataValidation) helper.createValidation(constraint, addressList); validation.setShowErrorBox(true); productSheet.addValidationData(validation); } } workBook.setActiveSheet(workBook.getSheetIndex(productSheet)); ByteArrayOutputStream baos = new ByteArrayOutputStream(1024); workBook.write(baos); return baos.toByteArray(); }