/** * 初始化函数 * * @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."); }
@Override public void close() { try { int nbS = wb.getNumberOfSheets(); for (int i = 0; i < nbS; i++) { Sheet s = wb.getSheetAt(i); if (s.getRow(0) != null) { // ceci arrive si on vide la mémoire tampon pour les grands fichiers. Dans ce // cas pas de possibilité de traiter la mise en page de gros fichiers for (int j = 0; j < s.getRow(0).getLastCellNum(); j++) { Cell c = s.getRow(0).getCell(j); c.getSheet().autoSizeColumn(c.getColumnIndex()); } Cell firstCell = s.getRow(0).getCell(0); Cell lastCell = s.getRow(0).getCell((int) s.getRow(0).getLastCellNum() - 1); s.setAutoFilter( new CellRangeAddress( firstCell.getRowIndex(), lastCell.getRowIndex(), lastCell.getRowIndex(), lastCell.getColumnIndex())); } } wb.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
private SXSSFWorkbook createWorkbook() throws FileNotFoundException, IOException { SXSSFWorkbook wb; FileInputStream fis; File filee = POIUtil.getStream(filePath, this.titles); fis = new FileInputStream(filee); XSSFWorkbook wbb = new XSSFWorkbook(fis); wb = new SXSSFWorkbook( wbb, Integer.parseInt( PropertiesUtil.getPropertyValue("export.properties", Constant.PAGE_COUNT, "1000"))); wb.setCompressTempFiles(true); return wb; }
/** * 批量导出 * * @param data * @throws FileNotFoundException */ public void transform(Map<String, Object> data) throws FileNotFoundException { FileInputStream templateIs = new FileInputStream(template); try { XSSFWorkbook bookTmp = (XSSFWorkbook) transformer.transformXLS(templateIs, data); bookTmp.setSheetName(0, "第" + count + "页"); if (count == 1) { book = new SXSSFWorkbook(bookTmp); } else { Sheet newSheet = book.createSheet(bookTmp.getSheetName(0)); Sheet sheet = bookTmp.getSheetAt(0); Util.copySheets(newSheet, sheet); } count++; } catch (ParsePropertyException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } finally { try { templateIs.close(); } catch (IOException e) { e.printStackTrace(); } } }
/** * @see * net.sourceforge.squirrel_sql.fw.gui.action.exportData.AbstractDataExportFileWriter#afterWorking() */ @Override protected void afterWorking() throws Exception { FileOutputStream out = new FileOutputStream(this.file); this.workbook.write(out); out.close(); // dispose of temporary files backing this workbook on disk if (workbook instanceof SXSSFWorkbook) { ((SXSSFWorkbook) workbook).dispose(); } }
/** * 添加一个单元格 * * @param row 添加的行 * @param column 添加列号 * @param val 添加值 * @param align 对齐方式(1:靠左;2:居中;3:靠右) * @return 单元格对象 */ public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) { Cell cell = row.createCell(column); CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : "")); try { if (val == null) { cell.setCellValue(""); } else if (val instanceof String) { cell.setCellValue((String) val); } else if (val instanceof Integer) { cell.setCellValue((Integer) val); } else if (val instanceof Long) { cell.setCellValue((Long) val); } else if (val instanceof Double) { cell.setCellValue((Double) val); } else if (val instanceof Float) { cell.setCellValue((Float) val); } else if (val instanceof Date) { DataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("yyyy-MM-dd")); cell.setCellValue((Date) val); } else { if (fieldType != Class.class) { cell.setCellValue( (String) fieldType.getMethod("setValue", Object.class).invoke(null, val)); } else { cell.setCellValue( (String) Class.forName( this.getClass() .getName() .replaceAll( this.getClass().getSimpleName(), "fieldtype." + val.getClass().getSimpleName() + "Type")) .getMethod("setValue", Object.class) .invoke(null, val)); } } } catch (Exception ex) { log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString()); cell.setCellValue(val.toString()); } cell.setCellStyle(style); return cell; }
private Row createRowFrom(IRecordable o) { Row lastRow, r; String[] data; if (dicoLocations.containsKey(o.getClassement())) lastRow = dicoLocations.get(o.getClassement()); else { Sheet logSheet = wb.createSheet(o.getClassement()); lastRow = logSheet.createRow(0); data = o.getTitles(); fillRowWith(lastRow, data); dicoLocations.put(o.getClassement(), lastRow); } r = lastRow.getSheet().createRow(lastRow.getRowNum() + 1); data = o.getRecords(); fillRowWith(r, data); dicoLocations.replace(o.getClassement(), r); return r; }
private void writeToFile(SXSSFWorkbook wb) throws FileNotFoundException, IOException { FileOutputStream out = new FileOutputStream(filePath); wb.write(out); wb.dispose(); out.close(); }
/** 清理临时文件 */ public ExportExcel dispose() { wb.dispose(); return this; }
/** * 输出数据流 * * @param os 输出数据流 */ public ExportExcel write(OutputStream os) throws IOException { wb.write(os); return this; }