/** * 初始化函数 * * @param title 表格标题,传“空值”,表示无标题 * @param headerList 表头列表 */ private void initialize(String title, List<String> headerList) { this.wb = new SXSSFWorkbook(500); this.sheet = wb.createSheet("Export"); this.styles = createStyles(wb); // Create title if (StringUtils.isNotBlank(title)) { Row titleRow = sheet.createRow(rownum++); titleRow.setHeightInPoints(30); Cell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styles.get("title")); titleCell.setCellValue(title); sheet.addMergedRegion( new CellRangeAddress( titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), headerList.size() - 1)); } // Create header if (headerList == null) { throw new RuntimeException("headerList not null!"); } Row headerRow = sheet.createRow(rownum++); headerRow.setHeightInPoints(16); for (int i = 0; i < headerList.size(); i++) { Cell cell = headerRow.createCell(i); cell.setCellStyle(styles.get("header")); String[] ss = StringUtils.split(headerList.get(i), "**", 2); if (ss.length == 2) { cell.setCellValue(ss[0]); Comment comment = this.sheet .createDrawingPatriarch() .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6)); comment.setString(new XSSFRichTextString(ss[1])); cell.setCellComment(comment); } else { cell.setCellValue(headerList.get(i)); } sheet.autoSizeColumn(i); } for (int i = 0; i < headerList.size(); i++) { int colWidth = sheet.getColumnWidth(i) * 2; sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth); } log.debug("Initialize success."); }
private void createSummerySheet() { sheet0 = workbook.createSheet("Summary"); PrintSetup printSetup = sheet0.getPrintSetup(); printSetup.setLandscape(true); sheet0.setFitToPage(true); sheet0.setHorizontallyCenter(true); // title row Row titleRow = sheet0.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("File Health Report"); titleCell.setCellStyle(styles.get("title")); sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); for (int i = 0; i < titles.length; i++) { Row _row = sheet0.createRow(i + 1); Cell headerCell = _row.createCell(0); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); _row.setHeightInPoints(20); } // finally set column widths, the width is measured in units of 1/256th // of a character width sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide }
private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo) throws Exception { Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK); Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType()); nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME)); nCell.setCellStyle(tCell.getCellStyle()); tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK); nCell = newSheet.createRow(1).createCell(0, tCell.getCellType()); nCell.setCellValue( tCell .getStringCellValue() .replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD") .replaceAll( PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom)) .replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo))); nCell.setCellStyle(tCell.getCellStyle()); Row tempRow = tempSheet.getRow(4); Row newRow = newSheet.createRow(4); for (int c = 0; c < tempRow.getLastCellNum(); c++) { tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK); nCell = newRow.createCell(c, tCell.getCellType()); nCell.setCellValue(tCell.getStringCellValue()); nCell.setCellStyle(tCell.getCellStyle()); } for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i); newSheet.addMergedRegion(mergedRegion); } }
private static void createRow( Sheet sheet, Map<String, Object> rowData, List<String> mapping, List<CellStyle> styles, int startRowNum) { Row row = sheet.createRow(startRowNum); for (int i = 0; i < mapping.size(); i++) { String name = mapping.get(i); Object obj = rowData.get(name); Cell newCell = row.createCell(i); CellStyle style = styles.get(i); newCell.setCellStyle(style); if (obj != null) { if (obj instanceof Date) { newCell.setCellValue((Date) obj); } else if (obj instanceof BigDecimal) { double dd = ((BigDecimal) obj).doubleValue(); newCell.setCellValue(dd); } else { newCell.setCellValue(obj.toString()); } } } }
/** * 默认的字典输出适配器 * * @param fieldValue * @param fieldName * @param cell * @throws AdapterException */ public void outputDicCodeAdapter( DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException { log.debug( "in DefaultOutputAdapter:outputDicCodeAdapter fieldName:{} fieldValue:{}", fieldName, fieldValue); OutputDicConfig config = dataBean.getOutputConfig(fieldName); String dicCode = config.dicCode(); if (fieldValue == null) { log.debug("fieldValue is null return"); cell.setCellValue(""); return; } else { String byKey = dicCodePool.getByKey(dicCode, fieldValue.toString()); if (byKey == null) { if (AdapterUtil.getAllMatch(config)) { throw new AdapterException(Message.DIC_ERROR, cell); } else { cell.setCellValue(fieldValue.toString()); } } else { cell.setCellValue(byKey); } } }
public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) { if (copyStyle) { newCell.setCellStyle(oldCell.getCellStyle()); } switch (oldCell.getCellType()) { case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: newCell.setCellType(Cell.CELL_TYPE_BLANK); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
/* * Don't hardcode US Locale for datetimes in Excel. Experiment a bit. */ public static void issue26() throws InvalidFormatException, IOException { System.out.println("Testing issue26 ================================="); Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("Sheet1"); Row row = sheet.createRow(0); // first cell Cell cell0 = row.createCell(0); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss")); cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM cell0.setCellStyle(cellStyle); // second cell using another format with French locale CellStyle cs2 = wb.createCellStyle(); String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH); System.out.println("The LOCALE prefix is: " + excelFormatPrefix); String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@"; System.out.println( "Converted pattern in FRENCH locale is: " + DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss")); DataFormat df = wb.createDataFormat(); cs2.setDataFormat(df.getFormat(excelFormatPattern)); Cell cell1 = row.createCell(1); cell1.setCellValue(new Date(1385903303326L)); cell1.setCellStyle(cs2); FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx"); wb.write(out); out.close(); System.out.println("Wrote /tmp/issue26_out.xlsx"); }
public void insertDataToExcel(int numRow, Object[] object) { try { if (null != wb.getSheetAt(0)) { Sheet aSheet = wb.getSheetAt(0); Row row = aSheet.getRow((short) numRow); if (row == null) row = aSheet.createRow((short) numRow); for (int i = 0; i < object.length; i++) { Cell csCell = row.createCell((short) i); CellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); csCell.setCellStyle(style); if (object[i] != null) csCell.setCellValue(object[i].toString()); else csCell.setCellValue("0"); } } } catch (Exception e) { System.out.println("insertDataToExcel" + e); } }
/** * Forms report in excel file. Sum weight. Sum volume etc. * * @param sheet Sheet where write the items * @param c Container from where take the info * @param lastRow last row after all items were written method uses lastRow + 2 to write report in * next 2 rows after all items of the container were written */ private void setReport(Container c, int lastRow) { String[] reportHeadings = { "Суммарный вес", // 6 "Суммарный объем", // 7 "Остаток вес", // 8 "Остаток объем" // 9 }; double[] values = { c.getWeight(), c.getVolume(), c.getWeightLimit() - c.getWeight(), c.getVolumeLimit() - c.getVolume() }; int dataCell = 5; int valuesIndex = 0; // index of the array of doubles(weigh, volume, etc) lastRow++; Row headings = outputSheet.createRow(lastRow); lastRow += 2; Row data = outputSheet.createRow(lastRow); for (String report : reportHeadings) { Cell heading = headings.createCell(dataCell); heading.setCellValue(report); Cell dataCellValue = data.createCell(dataCell); dataCellValue.setCellValue(values[valuesIndex]); dataCell++; valuesIndex++; } }
private void dealWithAmt(Cell cell, String amt) { if (StringUtil.isEmpty(amt)) { cell.setCellValue(""); } else { cell.setCellValue(Double.valueOf(amt)); } }
public static Cell createCell(Row row, int index, Boolean value) { Cell cell = row.createCell(index); if (value == null) { cell.setCellValue(""); } cell.setCellValue(value); return cell; }
public static Cell createCell(Row row, int index, Date value) { Cell cell = row.createCell(index); if (value == null) { cell.setCellValue(""); return cell; } cell.setCellValue(DateUtils.formatDate(value, DateUtils.DateFormatType.DATE_FORMAT_STR_CHINA)); return cell; }
public static Cell createCell(Row row, int index, RichTextString value) { Cell cell = row.createCell(index); if (value == null) { cell.setCellValue(""); return cell; } cell.setCellValue(value); return cell; }
public static File writerFile(String[] title, List<String[]> content, String filePath) throws IOException { checkDir(filePath); File f = new File(filePath); if (!f.exists()) { f.createNewFile(); } FileOutputStream out = new FileOutputStream(f); wb = new SXSSFWorkbook(); setStyle(wb); Sheet sheet = wb.createSheet("sheet1"); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(20); int tCount = title.length; for (int i = 0; i < tCount; i++) { Cell cell = titleRow.createCell(i); cell.setCellStyle(titleStyle); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(title[i]); sheet.setColumnWidth(i, 5000); } int rnum = 1; for (String[] c : content) { Row r = sheet.createRow(rnum); for (int i = 0; i < c.length; i++) { Cell cell = r.createCell(i); cell.setCellStyle(contentStyle); cell.setCellType(Cell.CELL_TYPE_STRING); sheet.setColumnWidth(i, 5000); String v = c[i]; if (v == null) { v = ""; } cell.setCellValue(v); } rnum++; } wb.write(out); out.flush(); wb.close(); out.close(); return f; }
public void addSummary(int realCnt) { Cell _c = sheet0.getRow(10).createCell(1); _c.setCellValue(_t13yb); _c = sheet0.getRow(11).createCell(1); _c.setCellValue(_t13ye); _c = sheet0.getRow(12).createCell(1); _c.setCellValue(_t13yw); _c = sheet0.getRow(3).createCell(1); _c.setCellValue(realCnt); _c = sheet0.getRow(1).createCell(1); _c.setCellValue(trueName); _c = sheet0.getRow(2).createCell(1); _c.setCellValue(fileContact); for (int i = 4; i < 8; i++) { _c = sheet0.getRow(i).createCell(1); _c.setCellValue(_sims[4 + (10 - i)]); } int _less70 = 0; for (int i = 1; i < 7; i++) _less70 += _sims[i]; _c = sheet0.getRow(8).createCell(1); _c.setCellValue(_less70); _c = sheet0.getRow(9).createCell(1); _c.setCellValue(_sims[0]); }
protected void exportExtra(Row row, Item item) { QiyiTop50 rank = (QiyiTop50) item; Cell cell = row.createCell(2); cell.setCellValue(rank.getActorsString()); cell = row.createCell(3); cell.setCellValue(rank.getLastWeek().longValue()); cell = row.createCell(4); cell.setCellValue(rank.getYesterDay().longValue()); cell = row.createCell(5); cell.setCellValue(rank.getHistory().longValue()); }
private void addSumBottom() { for (int i = 0; i < book.getNumberOfSheets(); i++) { Sheet sheet = book.getSheetAt(i); Row row = sheet.createRow(sheet.getLastRowNum() + 1); row.setHeight((short) (ROW_HEIGHT + 100)); for (int j = 0; j < 1000000; j++) { if (StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(0).getCell(j))) && StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(2).getCell(j)))) { break; } Cell cell = row.createCell(j); cell.setCellStyle(Style.get(book).SUM); if (j == 0) { cell.setCellValue("合计"); } else { cell.setCellValue(0); } if (j >= 7) { cell.setCellType(Cell.CELL_TYPE_FORMULA); cell.setCellFormula( String.format( "SUM(%s%s:%s%s)", CellUtils.convertToABC(j + 1), 5, CellUtils.convertToABC(j + 1), sheet.getLastRowNum())); } } sheet.addMergedRegion( new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 6)); } for (int i = 0; i < book.getNumberOfSheets(); i++) { Sheet sheet = book.getSheetAt(i); for (int j = 4; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); for (int k = 0; k <= row.getLastCellNum(); k++) { Cell cell = row.getCell(k); if (cell == null) { continue; } if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(k)))) { cell.setCellStyle(Style.get(book).SUM); } } } } }
@SuppressWarnings("unchecked") private void createBodyRow(Row currentRow, Object[] bodyRow, int[] types) { Cell currentCell; // write the section attributes to the row for (int index = 0; index < bodyRow.length; index++) { // create the cell currentCell = currentRow.createCell(index); // set the format of the cells currentCell.setCellStyle(bodyStyle); if (types[index] != Integer.MAX_VALUE) { currentCell.setCellType(types[index]); } // put the value into the cell switch (types[index]) { case Cell.CELL_TYPE_NUMERIC: currentCell.setCellValue((double) bodyRow[index]); break; case Cell.CELL_TYPE_FORMULA: currentCell.setCellFormula(String.valueOf(bodyRow[index])); break; case Integer.MAX_VALUE: HashMap<String, String> cellInfo = (HashMap<String, String>) bodyRow[index]; // sets the hyperlink to be blue and underline HSSFCellStyle hyperlinkStyle = (HSSFCellStyle) GetBodyCellStyle(workbook); Font hyperlinkFont = workbook.createFont(); hyperlinkFont.setUnderline(Font.U_SINGLE); hyperlinkFont.setColor(HSSFColor.BLUE.index); hyperlinkStyle.setFont(hyperlinkFont); currentCell.setCellStyle(hyperlinkStyle); currentCell.setCellValue(cellInfo.get("Label")); currentCell.setHyperlink(LinkDataToMap(cellInfo)); break; // MAX_VALUE integer will be used to denote hyperlinks case Cell.CELL_TYPE_STRING: currentCell.setCellValue(String.valueOf(bodyRow[index])); break; default: throw new UnsupportedOperationException( "The type " + types[index] + " is not supported." + " Only types 0, 1, 2, and Integer.MAX_VALUE (for hyperlinks)" + " are supported. See the javadoc for org.apache.poi.ss.usermodel.Cell " + "for more information on the types of cells."); } } }
private Cell headInfo(CellStyle headerStyle, Row row) { Cell cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue(getText("title.accountDetail.serialNumber")); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue(getText("page.accountDetail.accountNum")); cell = row.createCell(STR_2); cell.setCellStyle(headerStyle); cell.setCellValue(getText("page.accountDetail.accountName")); cell = row.createCell(STR_3); cell.setCellStyle(headerStyle); cell.setCellValue(getText("title.accountDetail.tranDate")); cell = row.createCell(STR_4); cell.setCellStyle(headerStyle); cell.setCellValue(getText("title.accountDetail.currency")); cell = row.createCell(STR_5); cell.setCellStyle(headerStyle); cell.setCellValue(getText("title.accountDetail.expenditure")); cell = row.createCell(STR_6); cell.setCellStyle(headerStyle); cell.setCellValue(getText("title.accountDetail.income")); cell = row.createCell(STR_7); cell.setCellStyle(headerStyle); cell.setCellValue(getText("title.accountDetail.balance")); return cell; }
private void fillData( List<Map<String, Object>> detailList, Sheet sheet1, CellStyle firstCellStyle, CellStyle commonCellStyle, CellStyle amtCellStyle) throws IOException { int i = 1; for (Map<String, Object> detail : detailList) { if (i > STR_65535) { break; } Row row = sheet1.createRow(i); Cell cell = row.createCell(0); cell.setCellStyle(firstCellStyle); cell.setCellValue(i); cell = row.createCell(1); cell.setCellStyle(commonCellStyle); cell.setCellValue(detail.get("accountNo").toString()); cell = row.createCell(STR_2); cell.setCellStyle(commonCellStyle); cell.setCellValue(new String(cstNameHost.getBytes("ISO8859_1"), "UTF8")); cell = row.createCell(STR_3); cell.setCellStyle(commonCellStyle); cell.setCellValue(formatDate(detail.get("transDate").toString())); cell = row.createCell(STR_4); cell.setCellStyle(firstCellStyle); cell.setCellValue(detail.get("currency").toString()); cell = row.createCell(STR_5); cell.setCellStyle(amtCellStyle); dealWithAmt(cell, detail.get("debitamt").toString()); cell = row.createCell(STR_6); cell.setCellStyle(amtCellStyle); dealWithAmt(cell, detail.get("creditamt").toString()); cell = row.createCell(STR_7); cell.setCellStyle(amtCellStyle); dealWithAmt(cell, detail.get("balance").toString()); i++; } }
protected void createHeaderExtra(Row row, CellStyle style) { Cell cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("Actors"); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue("LastWeek"); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue("YesterDay"); cell = row.createCell(5); cell.setCellStyle(style); cell.setCellValue("History"); }
private void fillRowWith(Row r, String[] data) { Cell c = null; for (String s : data) { if (c == null) c = r.createCell(0); else c = r.createCell(c.getColumnIndex() + 1); try { double d = Double.valueOf(s); c.setCellValue(d); } catch (NumberFormatException e) { c.setCellValue(s); } } }
public String setTestData(String sheetName, int rowNum, int colNum, String data) throws EncryptedDocumentException, InvalidFormatException, IOException { FileInputStream fis = new FileInputStream(filepath); Workbook wb = WorkbookFactory.create(fis); Sheet sh = wb.getSheet(sheetName); Row row = sh.getRow(rowNum); Cell cel = row.createCell(colNum); cel.setCellType(Cell.CELL_TYPE_STRING); cel.setCellValue(data); FileOutputStream fos = new FileOutputStream(filepath); cel.setCellValue(data); wb.write(fos); wb.close(); return data; }
public void format() { wb = new XSSFWorkbook(); Map styles = createStyles(wb); Sheet sheet = wb.createSheet("Loan Calculator"); sheet.setPrintGridlines(false); sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 768); sheet.setColumnWidth(1, 768); sheet.setColumnWidth(2, 2816); sheet.setColumnWidth(3, 3584); sheet.setColumnWidth(4, 3584); sheet.setColumnWidth(5, 3584); sheet.setColumnWidth(6, 3584); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35F); for (int i = 1; i <= 7; i++) titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title")); Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple"); }
private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException { Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("sheet1"); CellStyle headerStyle = getHeaderStyle(wb); CellStyle firstCellStyle = getFirsetCellStyle(wb); CellStyle commonCellStyle = getCommonCellStyle(wb); CellStyle amtCellStyle = getAmtCellStyle(wb); for (int i = 0; i < LENGTH_9; i++) { sheet1.setColumnWidth(i, STR_15 * STR_256); } // 表头 Row row = sheet1.createRow(0); row.setHeightInPoints(STR_20); Cell cell = headInfo(headerStyle, row); if (detailList.size() == 0) { row = sheet1.createRow(1); cell = row.createCell(0); cell.setCellValue(NO_RECORD); } else { fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle); } ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); wb.write(outputStream); InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray()); outputStream.close(); return inputStream; }
/** * {@inheritDoc} * * @see * org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#setCellValue(org.apache.poi.ss.usermodel.Cell, * java.lang.Object, org.eclipse.emf.ecore.EStructuralFeature, * org.eclipse.emf.ecp.view.spi.context.ViewModelContext) */ @Override public void setCellValue( Cell cell, Object fromObject, EStructuralFeature eStructuralFeature, ViewModelContext viewModelContext) throws EMFFormsConverterException { final EDataType eDataType = EAttribute.class.cast(eStructuralFeature).getEAttributeType(); final EFactory eFactory = eDataType.getEPackage().getEFactoryInstance(); final StringBuilder result = new StringBuilder(); for (final Object value : (List<?>) fromObject) { if (result.length() != 0) { result.append(SEPARATOR); } result.append(eFactory.convertToString(eDataType, value)); } String valueString = result.toString(); if (isDecimalNumber(eDataType.getInstanceClass())) { valueString = valueString.replace( '.', DecimalFormatSymbols.getInstance(localeProvider.getLocale()).getDecimalSeparator()); } cell.setCellValue(valueString); cell.setCellStyle( (CellStyle) viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT)); }
/** * write excel sheet, specified value to specified cell. * * @param sheetName excel sheet name * @param row row index which to be changed * @param col column index which to be changed * @param value value to be put into cell * @throws RuntimeException */ public void setExcelValue(String sheetName, int row, int col, String value) { Workbook workBook = null; try { if (new File(fileName).exists()) { workBook = getWorkBook(new FileInputStream(fileName), false); } else { workBook = getWorkBook(null, false); } xlSheet = workBook.getSheet(sheetName); if (xlSheet == null) { xlSheet = workBook.createSheet(sheetName); } xlRow = xlSheet.getRow(row - 1); if (xlRow == null) { xlRow = xlSheet.createRow((short) row - 1); } xlCell = xlRow.getCell(col - 1); if (xlCell == null) { xlCell = xlRow.createCell(col - 1); } xlCell.setCellType(1); // set cell type as string xlCell.setCellValue(value); FileOutputStream fileOut = new FileOutputStream(fileName); workBook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (Exception e) { LOG.error(e); throw new RuntimeException("set excel value failed:" + e.getMessage()); } }
@SuppressWarnings("rawtypes") private void generateHeaderRow( HSSFWorkbook workBook, Sheet sheet, JSONArray columnConfigJSONObject) { CellStyle center = workBook.createCellStyle(); center.setAlignment(HSSFCellStyle.ALIGN_CENTER); int columnType = Cell.CELL_TYPE_STRING; String col = null, text = null; short width = 15; sheet.setDefaultColumnWidth(width); Row headerRow = createRow(sheet, headerRowNum); for (short i = 0; i < columnConfigJSONObject.length(); i++) { JSONObject headerObj = columnConfigJSONObject.getJSONObject(i); for (Iterator iterator = headerObj.keys(); iterator.hasNext(); ) { String key = (String) iterator.next(); if ("columnType".equals(key)) { // if(isNumber(headerObj.getString(key))) { // columnType = Cell.CELL_TYPE_NUMERIC; // } } else { col = key; text = headerObj.getString(key); } } headerIdArray.add(col); Cell cell = createCell(headerRow, i, columnType); cell.setCellValue(new HSSFRichTextString(text)); cell.setCellStyle(center); } }
@Override protected Workbook indexExcel(Map<String, String> params, Workbook excel) { List<Map<String, Object>> list = service.financialIndexData_02(params); Sheet sheet = excel.getSheetAt(0); Row row = sheet.getRow(0); Cell cell = row.getCell(0); cell.setCellValue(params.get("statMonth") + reportName); row = sheet.getRow(2); if (list != null && list.size() > 0) { for (Map<String, Object> map : list) { sheet = PoiUtil.copyLastRow(sheet, false); row = sheet.getRow(sheet.getLastRowNum() - 1); row.getCell(0).setCellValue(map.get("ENT_NAME").toString()); row.getCell(1).setCellValue(StringUitl.obj2doule(map.get("TJ1"))); row.getCell(2).setCellValue(StringUitl.obj2doule(map.get("TB1"))); row.getCell(3).setCellValue(StringUitl.obj2doule(map.get("TJ2"))); row.getCell(4).setCellValue(StringUitl.obj2doule(map.get("TB2"))); row.getCell(5).setCellValue(StringUitl.obj2doule(map.get("TJ3"))); row.getCell(6).setCellValue(StringUitl.obj2doule(map.get("TB3"))); } } sheet.removeRow(sheet.getRow(sheet.getLastRowNum())); return excel; }
/** * Called to update the embedded Excel workbook. As the format and structire of the workbook are * known in advance, all this code attempts to do is write a new value into the first cell on the * first row of the first worksheet. Prior to executing this method, that cell will contain the * value 1. * * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException Rather than use the specific * classes (HSSF/XSSF) to handle the embedded workbook this method uses those defeined in the * SS stream. As a result, it might be the case that a SpreadsheetML file is opened for * processing, throwing this exception if that file is invalid. * @throws java.io.IOException Thrown if a problem occurs in the underlying file system. */ public void updateEmbeddedDoc() throws OpenXML4JException, IOException { Workbook workbook = null; Sheet sheet = null; Row row = null; Cell cell = null; PackagePart pPart = null; Iterator<PackagePart> pIter = null; List<PackagePart> embeddedDocs = this.doc.getAllEmbedds(); if (embeddedDocs != null && !embeddedDocs.isEmpty()) { pIter = embeddedDocs.iterator(); while (pIter.hasNext()) { pPart = pIter.next(); if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION) || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) { // Get an InputStream from the pacage part and pass that // to the create method of the WorkbookFactory class. Update // the resulting Workbook and then stream that out again // using an OutputStream obtained from the same PackagePart. workbook = WorkbookFactory.create(pPart.getInputStream()); sheet = workbook.getSheetAt(SHEET_NUM); row = sheet.getRow(ROW_NUM); cell = row.getCell(CELL_NUM); cell.setCellValue(NEW_VALUE); workbook.write(pPart.getOutputStream()); } } // Finally, write the newly modified Word document out to file. this.doc.write(new FileOutputStream(this.docFile)); } }