/**
* 写入工作表
*
* @param wb Excel工作簿
* @param title Sheet工作表名称
* @param styles 表头样式
* @param creator 创建人
* @param tableData 表格数据
* @throws Exception
*/
public HSSFWorkbook writeSheet(
HSSFWorkbook wb,
HashMap<String, HSSFCellStyle> styles,
String creator,
List<TableData> tableDataLst)
throws Exception {
SimpleDateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分");
String create_time = formater.format(new Date());
int cnt = 1;
for (TableData tableData : tableDataLst) {
String sheetTitle = tableData.getSheetTitle();
sheetTitle = sheetTitle == null || sheetTitle.equals("") ? "sheet" + cnt : sheetTitle;
cnt++;
TableHeaderMetaData headerMetaData = tableData.getTableHeader(); // 获得HTML的表头元素
HSSFSheet sheet = wb.createSheet(sheetTitle); // 在Excel工作簿中建一工作表
sheet.setDisplayGridlines(false); // 设置表标题是否有表格边框
wb.cloneSheet(0);
// 创建标题
HSSFRow row = sheet.createRow(0); // 创建新行
HSSFCell cell = row.createCell(0); // 创建新列
int rownum = 0;
cell.setCellValue(new HSSFRichTextString(sheetTitle));
HSSFCellStyle style = styles.get("TITLE"); // 设置标题样式
if (style != null) cell.setCellStyle(style);
sheet.addMergedRegion(
new CellRangeAddress(
0, 0, 0, headerMetaData.getColumnCount() - 1)); // 合并标题行：起始行号，终止行号， 起始列号，终止列号
// 创建副标题
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue(new HSSFRichTextString("创建人:"));
style = styles.get("SUB_TITLE");
if (style != null) cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue(new HSSFRichTextString(creator));
style = styles.get("SUB_TITLE2");
if (style != null) cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue(new HSSFRichTextString("创建时间:"));
style = styles.get("SUB_TITLE");
if (style != null) cell.setCellStyle(style);
cell = row.createCell(3);
style = styles.get("SUB_TITLE2");
cell.setCellValue(new HSSFRichTextString(create_time));
if (style != null) cell.setCellStyle(style);
rownum = 3; // 如果rownum = 1，则去掉创建人、创建时间等副标题；如果rownum = 0， 则把标题也去掉
HSSFCellStyle headerstyle = styles.get("TABLE_HEADER");
int colnum = 0;
for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) {
TableColumn tc = headerMetaData.getOriginColumns().get(i);
if (i != 0) {
colnum += headerMetaData.getOriginColumns().get(i - 1).getLength();
}
generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle);
}
rownum += headerMetaData.maxlevel;
List<TableDataRow> dataRows = tableData.getRows();
int index = 0;
for (TableDataRow dataRow : dataRows) {
row = sheet.createRow(rownum);
List<TableDataCell> dataCells = dataRow.getCells();
int size = headerMetaData.getColumns().size();
index = -1;
for (int i = 0; i < size; i++) {
TableColumn tc = headerMetaData.getColumns().get(i);
if (!tc.isVisible()) continue;
index++;
createCell(row, tc, dataCells, i, index, styles);
}
rownum++;
}
// 设置前两列根据数据自动列宽
for (int c = 0; c < headerMetaData.getColumns().size(); c++) {
sheet.autoSizeColumn((short) c);
String t = headerMetaData.getColumns().get(c).getDisplay();
if (sheet.getColumnWidth(c) < t.length() * 256 * 3)
sheet.setColumnWidth(c, t.length() * 256 * 3);
}
sheet.setGridsPrinted(true);
}
return wb;
}
