/**
*
*@注释 导出Excel
*@方法名称 genericSheet
* @param title 标题
* @param headList 表头列(KeyValueBean key是字段名称 value是说明文字)
* @param data 导出数据
* @param work 可添加新增的sheet
* @return
*/
public static HSSFWorkbook genericSheet(String title,List<KeyValueBean> headList, List<Map<String,Object>> data, HSSFWorkbook work) {
if(work == null) work = new HSSFWorkbook();
HSSFCellStyle dataStyle = work.createCellStyle();
dataStyle.setWrapText(true);
// 字体对象设置
HSSFFont font = work.createFont();
font.setFontHeightInPoints((short) 16);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 表头字体颜色
HSSFFont colorFont = work.createFont();
colorFont.setColor(HSSFColor.RED.index);
// 大标题格式
HSSFCellStyle titleStyle = work.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titleStyle.setFont(font);
// 数据表头格式 并设置值表头 设置表头居中
HSSFCellStyle style = work.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置背景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
// 说明格式
HSSFCellStyle explainStyle = work.createCellStyle();//说明格式加红标注
explainStyle.setWrapText(true);//设置自动换行
explainStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
explainStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
explainStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
explainStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
explainStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
explainStyle.setFont(colorFont);
HSSFCellStyle wrapStyle = work.createCellStyle(); // 说明格式标注
wrapStyle.setWrapText(true);
wrapStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
wrapStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
wrapStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
wrapStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
wrapStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
wrapStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
// 创建sheet
HSSFSheet sheet = work.createSheet(title);
sheet.setDefaultColumnWidth(20); // 默认列宽
sheet.setDefaultRowHeightInPoints(20);
int rows = 2;// 行号
HSSFRow headerRow = sheet.createRow((int) 0);
Region region = new Region(0, (short) 0, 1, (short) (headList.size()-1)); //参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号O
sheet.addMergedRegion(region);
HSSFCell cellTitle = headerRow.createCell((short) 0);
cellTitle.setCellValue(title);
cellTitle.setCellStyle(titleStyle);
HSSFRow explRow1 = null;// 说明表头1
HSSFRow explRow2 = null;// 说明表头2
HSSFCell explCell1 = null;//说明1表头单元格
HSSFCell explCell2 = null;//说明2表头单元格
if(StringUtils.isNotBlank(headList.get(0).getValue())) {
explRow1 = sheet.createRow(rows);
explRow2 = sheet.createRow(rows + 1);
rows = rows + 2;
}
HSSFRow row = sheet.createRow(rows);//表头
HSSFCell cell = row.createCell((short) 0);//表头单元格
for (int k = 0; k < headList.size(); k++) {//循环迭代表头并设置说明及列名称
String colName = headList.get(k).getKey();
if(explRow1 != null && explRow2 != null) {// 存在说明表头设置列说明
explCell1 = explRow1.createCell((short) (k));//创建表头说明1单元格
explCell2 = explRow2.createCell((short) (k));//创建表头说明2单元格
explCell1.setCellValue(headList.get(k).getValue());
if(colName.indexOf("!") != -1){
explCell1.setCellStyle(explainStyle);
explCell2.setCellStyle(explainStyle);
} else {
explCell1.setCellStyle(wrapStyle);
explCell2.setCellStyle(wrapStyle);
}
Region explainRegion = new Region(2, (short) k, 3, (short) k); //参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号O
sheet.addMergedRegion(explainRegion);
}
cell = row.createCell((short) (k));// 设置列名称
cell.setCellValue(colName.replaceAll("!", ""));
cell.setCellStyle(style);
}
row = sheet.createRow(rows + 1); // 生成数据
if(!CollectionUtil.isEmpty(data)) {
for(int j = 0; j < data.size(); j++) {
Map<String,Object> obj = data.get(j);
row = sheet.createRow(rows + j + 1);
for(int i = 0; i < headList.size(); i++) {
HSSFCell createCell = row.createCell(i);
createCell.setCellStyle(dataStyle);
createCell.setCellValue(obj.get(headList.get(i).getKey()) != null?obj.get(headList.get(i).getKey()).toString():Constant.EMPTY_STRING);
}
}
}
return work;
}