@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;
}
public static boolean writeArray2XLSXFile(
String FileName, String[] titleNames, List<String[]> arry) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = titleNames.length;
int rowNum = arry.size();
// for (int i = 0; i < columnNum; i++) {
// sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20)));
// }
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
XSSFFont headFont = wb.createFont();
headFont.setFontName("Courier New");
headFont.setFontHeightInPoints((short) 10);
headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
headStyle.setFillForegroundColor(HSSFColor.BLACK.index);
headStyle.setLocked(true);
headStyle.setFont(headFont);
headStyle.setBorderTop((short) 2);
headStyle.setBorderBottom((short) 1);
XSSFCellStyle contentStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
XSSFFont contentFont = wb.createFont();
contentFont.setFontName("Courier New");
contentFont.setFontHeightInPoints((short) 9);
// headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
contentFont.setColor(HSSFColor.BLACK.index);
contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
contentStyle.setFillForegroundColor(HSSFColor.BLACK.index);
contentStyle.setFont(contentFont);
// create titile row
XSSFRow row = sheet1.createRow(0);
int heandLine = 0;
XSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
heandLine++;
}
for (int i = 0; i < rowNum; i++) {
row = sheet1.createRow((i + heandLine));
String[] line = (String[]) arry.get(i);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(FileName);
wb.write(fileOut);
fileOut.close();
return true;
}
