/**
* 初始化函数
*
* @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;
}