如下图，需要做如下报表表头的导出，表头有合并行、合并列。
layui本身没有复杂表头导出的方法，所以根据POI做导出。
dao层：
public class CellModel {
/**
* 用于设置复杂表头
* @author asus
**/
private StringcellName;
private IntegerstartRow;//起始行
private IntegerstartColumn;//起始列
private IntegerendRow;//结束行
private IntegerendColumn;//结束列public String getCellName() {
return cellName;
}
public void setCellName(String cellName) {
this.cellName = cellName;
}
public Integer getStartRow() {
return startRow;
}
public void setStartRow(Integer startRow) {
this.startRow = startRow;
}
public Integer getStartColumn() {
return startColumn;
}
public void setStartColumn(Integer startColumn) {
this.startColumn = startColumn;
}
public Integer getEndRow() {
return endRow;
}
public void setEndRow(Integer endRow) {
this.endRow = endRow;
}
public Integer getEndColumn() {
return endColumn;
}
public void setEndColumn(Integer endColumn) {
this.endColumn = endColumn;
}
}
POIUtils类：
public class ExcelUtils {
/**
* 生成表格（用于生成复杂表头）
* @param sheetName sheet名称
* @param wb 表对象
* @param cellListMap 表头数据 {key=cellRowNum-1}
* @param cellRowNum 表头总占用行数
* @param exportData 行数据
* @return
* @throws Exception
*/@SuppressWarnings({"rawtypes","unchecked" })
public static SXSSFWorkbook createCSVUtil(String sheetName, SXSSFWorkbook wb, Map> cellListMap,Integer cellRowNum, List exportData)throws Exception {//设置表格名称
SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(sheetName);
//如果报错，把这个注释掉就
//sheet.autoSizeColumn(1, true);
XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
//设置单元格内容水平对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置单元格内容垂直对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
cellStyle.setWrapText(true);
for(int t =0; t < cellRowNum; t++) {
SXSSFRow row = (SXSSFRow) sheet.createRow(t);
List cellNameList = cellListMap.get(String.valueOf(t));
for (CellModel cellModel : cellNameList) {
//如果有不需要合并的行，需加此判断，不然会报错
if (cellModel.getEndRow() - cellModel.getStartRow() ==0 &&
cellModel.getEndColumn() - cellModel.getStartColumn() ==0) {
continue;
}else {
sheet.addMergedRegion(new CellRangeAddress(cellModel.getStartRow(),
cellModel.getEndRow(), cellModel.getStartColumn(), cellModel.getEndColumn()));
}
}
for (int i =0; i < cellNameList.size(); i++) {
CellModel cellModel = cellNameList.get(i);
// 遍历插入表头
SXSSFCell cell = (SXSSFCell) row.createCell(cellModel.getStartColumn());
cell.setCellValue(cellModel.getCellName());
cell.setCellStyle(cellStyle);
wb.createFont().setBold(true);
}
}
for (LinkedHashMap hashMap : exportData) {
SXSSFRow rowValue = (SXSSFRow) sheet.createRow(cellRowNum);
Iterator iteratorRow = hashMap.entrySet().iterator();
while (iteratorRow.hasNext()) {
Map.Entry entryRow = iteratorRow.next();
Integer key = Integer.valueOf(entryRow.getKey().toString());
String value ="";
if (entryRow.getValue() !=null) {
value = entryRow.getValue().toString();
}else {
value ="";
}
SXSSFCell cellValue = (SXSSFCell) rowValue.createCell(key -1);
cellValue.setCellValue(value);
cellValue.setCellStyle(cellStyle);
}
cellRowNum++;
}
return wb;
}
}
设计表头，调用工具栏导出报表：
@RequestMapping(value ="/getAssistantVisitExport", method = RequestMethod.GET)
@ResponseBody
@Logsrecord(dowhat ="医助回访结果导出")
public JSONObject getAssistantVisitExport(@RequestParam Map map, HttpServletResponse response)throws Exception {
//向指定的Excel中写入数据
OutputStream out =null;
//设置最大数据行数
SXSSFWorkbook wb =new SXSSFWorkbook();
try {
String tabName ="患者随访统计表";
Map<String, Object> rowMap =new HashMap>();
// 设置数据
List firstRow =new ArrayList();
CellModel cellModel1 =new CellModel();
//总占用2行
Integer cellRow =2;
cellModel1.setCellName("医馆名称");
cellModel1.setStartRow(0);
cellModel1.setEndRow(1);
cellModel1.setStartColumn(0);
cellModel1.setEndColumn(0);
CellModel cellModel2 =new CellModel();
cellModel2.setCellName("看诊效果");
cellModel2.setStartRow(0);
cellModel2.setEndRow(0);
cellModel2.setStartColumn(1);
cellModel2.setEndColumn(10);
firstRow.add(cellModel1);
firstRow.add(cellModel2);
rowMap.put("0", firstRow );
List secondRow =new ArrayList();
CellModel cellMode21 =new CellModel();
cellMode21.setCellName("有效回访条数");
cellMode21.setStartRow(1);
cellMode21.setEndRow(1);
cellMode21.setStartColumn(1);
cellMode21.setEndColumn(1);
CellModel cellMode22 =new CellModel();
cellMode22.setCellName("没有效果");
cellMode22.setStartRow(1);
cellMode22.setEndRow(1);
cellMode22.setStartColumn(2);
cellMode22.setEndColumn(2);
CellModel cellMode23 =new CellModel();
cellMode23.setCellName("一般");
cellMode23.setStartRow(1);
cellMode23.setEndRow(1);
cellMode23.setStartColumn(3);
cellMode23.setEndColumn(3);
CellModel cellMode24 =new CellModel();
cellMode24.setCellName("效果好");
cellMode24.setStartRow(1);
cellMode24.setEndRow(1);
cellMode24.setStartColumn(4);
cellMode24.setEndColumn(4);
CellModel cellMode25 =new CellModel();
cellMode25.setCellName("本周好评率");
cellMode25.setStartRow(1);
cellMode25.setEndRow(1);
cellMode25.setStartColumn(5);
cellMode25.setEndColumn(5);
CellModel cellMode26 =new CellModel();
cellMode26.setCellName("上周好评率");
cellMode26.setStartRow(1);
cellMode26.setEndRow(1);
cellMode26.setStartColumn(6);
cellMode26.setEndColumn(6);
CellModel cellMode27 =new CellModel();
cellMode27.setCellName("环比");
cellMode27.setStartRow(1);
cellMode27.setEndRow(1);
cellMode27.setStartColumn(7);
cellMode27.setEndColumn(7);
CellModel cellMode28 =new CellModel();
cellMode28.setCellName("本周差评率");
cellMode28.setStartRow(1);
cellMode28.setEndRow(1);
cellMode28.setStartColumn(8);
cellMode28.setEndColumn(8);
CellModel cellMode29 =new CellModel();
cellMode29.setCellName("上周差评率");
cellMode29.setStartRow(1);
cellMode29.setEndRow(1);
cellMode29.setStartColumn(9);
cellMode29.setEndColumn(9);
CellModel cellMode210 =new CellModel();
cellMode210.setCellName("环比");
cellMode210.setStartRow(1);
cellMode210.setEndRow(1);
cellMode210.setStartColumn(10);
cellMode210.setEndColumn(10);
secondRow .add(cellMode21);
secondRow .add(cellMode22);
secondRow .add(cellMode23);
secondRow .add(cellMode24);
secondRow .add(cellMode25);
secondRow .add(cellMode26);
secondRow .add(cellMode27);
secondRow .add(cellMode28);
secondRow .add(cellMode29);
secondRow .add(cellMode210);
rowMap.put("1", secondRow );
List<Map<String, Object>> list =visitReportBiz.getAssistantVisitResult(map);
List exportData =new ArrayList();
for (int i =0; i < list.size(); i++) {
Map<String, Object> map1 = list.get(i);
LinkedHashMap rowPut =new LinkedHashMap();
rowPut.put("1", (String) map1.get("HOSPITAL_NAME"));
rowPut.put("2", String.valueOf(map1.get("SUCCESS_COUNT")));
rowPut.put("3", String.valueOf(map1.get("NO_EFFECT")));
rowPut.put("4", String.valueOf(map1.get("GENERAL")));
rowPut.put("5", String.valueOf(map1.get("GOOD")));
rowPut.put("6", String.valueOf(map1.get("FAVORABLE_RATING_THISWEEK")));
rowPut.put("7", String.valueOf(map1.get("FAVORABLE_RATING_LASTWEEK")));
rowPut.put("8", String.valueOf(map1.get("GOOD_SEQUENTIAL")));
rowPut.put("9", String.valueOf(map1.get("BAD_RATING_THISWEEK")));
rowPut.put("10",String.valueOf(map1.get("BAD_RATING_LASTWEEK")));
rowPut.put("11",String.valueOf(map1.get("BAD_SEQUENTIAL")));
exportData.add(rowPut);
}
// 用于写入文件
wb = ExcelUtils.createCSVUtil(tabName,wb, rowMap, cellRow, exportData);
SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date =new Date();
String sd = sdf.format(date);
// 设置文件名，加上格式化的时间
String excelName = tabName + sd +".xlsx";
String s_attachment ="attachment; filename=" + excelName;
// 设置字符编码的格式
s_attachment =new String(s_attachment.getBytes("gb2312"),"ISO8859-1");
response.setHeader("Content-disposition", s_attachment);// 设定输出文件头
response.setContentType("application/vnd.ms-excel");// 定义输出类型
response.setContentType("text/plain;charset=UTF-8");
out = response.getOutputStream();
wb.write(out);
out.flush();
}catch (Exception e) {
e.printStackTrace();
}finally {
if (out !=null) {
try {
out.close();
return null;
}catch (Exception e) {}
}
if (wb !=null) {
wb.dispose();
}
}
return null;
}
