Java实现POI导出excel复杂表头
/**
* 导出
* @param request
* @param response
* @throws IOException
*/
@RequestMapping("/")
public void export(HttpServletRequest request, HttpServletResponse response) throws IOException {
String startTime = request.getParameter("startTime")==null ?"":request.getParameter("startTime").toString();//开始时间
String endTime=request.getParameter("endTime")==null ?"":request.getParameter("endTime").toString();//结束时间
String category = request.getParameter("category")==null ?"":request.getParameter("category").toString();//场点类型
String reportcode=request.getParameter("reportcode")==null ?"":request.getParameter("reportcode").toString();//受检编号
String entername=request.getParameter("entername")==null ?"":request.getParameter("entername").toString();//受检单位
String immName = request.getParameter("immName")==null ?"":request.getParameter("immName").toString();//是否免疫
String areaName = request.getParameter("areaName")==null ?"":request.getParameter("areaName").toString();//地市
Map entityMap=new HashMap();
entityMap.put("startTime",startTime);
entityMap.put("endTime",endTime);
entityMap.put("category",category);
entityMap.put("reportcode",reportcode);
entityMap.put("entername",entername);
entityMap.put("immName",immName);
entityMap.put("areaName",areaName);
List<Map> list=multipleItemStatisticsService.getMultipleItemStatisticsList(entityMap);
//1.创建一个workbook,对应一个excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//2.在workbook中添加一个sheet,对应Excel中的sheet
HSSFSheet sheet = wb.createSheet("多重检测项目明细");
//设置每一列的列宽
sheet.setColumnWidth(0,256*15);
sheet.setColumnWidth(1,256*15);
sheet.setColumnWidth(2,256*15);
sheet.setColumnWidth(3,256*15);
sheet.setColumnWidth(4,256*15);
sheet.setColumnWidth(5,256*15);
sheet.setColumnWidth(6,256*15);
sheet.setColumnWidth(7,256*15);
sheet.setColumnWidth(8,256*15);
sheet.setColumnWidth(9,256*15);
sheet.setColumnWidth(10,256*15);
sheet.setColumnWidth(11,256*15);
sheet.setColumnWidth(12,256*15);
sheet.setColumnWidth(13,256*15);
sheet.setColumnWidth(14,256*15);
// (15,256*15);
//3.设置样式以及字体样式
HSSFCellStyle titleStyle = ExcelUtils.createTitleCellStyle(wb);
HSSFCellStyle headerStyle = ExcelUtils.createHeadCellStyle(wb);
HSSFCellStyle contentStyle = ExcelUtils.createContentCellStyle(wb);
//4.创建标题，合并标题单元格
//行号
int rowNum = 0;
//创建第一行，索引从0开始(标题行)
HSSFRow row0 = sheet.createRow(rowNum++);
row0.setHeight((short) 800);// 设置行高
String title = "多重检测项目明细表";
HSSFCell c00 = row0.createCell(0);
c00.setCellValue(title);
c00.setCellStyle(titleStyle);
// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));//标题合并单元格操作，总列数为16
//第二行
HSSFRow row1 = sheet.createRow(rowNum++);
row1.setHeight((short)500);
String[] row_first = {"受检编号","样品编号","受检单位","地市","监测动物种类","年龄阶段","监测场点类型","饲养量","样品总量",list.get(0).get("name").toString(),"","","","",""};
for (int i = 0; i < row_first.length; i++) {
HSSFCell tempCell = row1.createCell(i);
tempCell.setCellValue(row_first[i]);
tempCell.setCellStyle(headerStyle);
}
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));//受检编号
sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));//样品编号
sheet.addMergedRegion(new CellRangeAddress(1, 3, 2, 2));//受检单位
sheet.addMergedRegion(new CellRangeAddress(1, 3, 3, 3));//地市
sheet.addMergedRegion(new CellRangeAddress(1, 3, 4, 4));//监测动物种类
sheet.addMergedRegion(new CellRangeAddress(1, 3, 5, 5));//年龄阶段
sheet.addMergedRegion(new CellRangeAddress(1, 3, 6, 6));//年龄阶段
sheet.addMergedRegion(new CellRangeAddress(1, 3, 7, 7));//年龄阶段
sheet.addMergedRegion(new CellRangeAddress(1, 3, 8, 8));//年龄阶段
sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 14));//截止2019年12月27日业务明细
//第三行
HSSFRow row2 = sheet.createRow(rowNum++);
row2.setHeight((short)500);
String[] row_second = {"","","","","","","","","",list.get(0).get("standard_name0").toString(),"","",list.get(0).get("standard_name1").toString(),"",""};
for (int i = 0; i < row_second.length; i++) {
HSSFCell tempCell = row2.createCell(i);
tempCell.setCellValue(row_second[i]);
tempCell.setCellStyle(headerStyle);
}
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 11));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 14));
//第四行
HSSFRow row3 = sheet.createRow(rowNum++);
row3.setHeight((short)500);
String[] row_third = {"","","","","","","","","","检测样品数","阳性数","阳性率（%）","检测样品数","阳性数","阳性率（%）"};
for (int i = 0; i < row_third.length; i++) {
HSSFCell tempCell = row3.createCell(i);
tempCell.setCellValue(row_third[i]);
tempCell.setCellStyle(headerStyle);
}
//查询月度明细列表
// List<BusinessDetail> list = (businessDetail);
for(int i = 0;i<list.size();i++){
HSSFRow tempRow = sheet.createRow(rowNum++);
tempRow.setHeight((short)500);
//循环单元格填入数据
for(int j=0;j<15;j++){
HSSFCell tempCell = tempRow.createCell(j);
tempCell.setCellStyle(contentStyle);
String cellValue = "";
if(j ==0){
//受检编号
cellValue = list.get(i).get("reportcode").toString();
}else if(j == 1){
//样品编号
cellValue = list.get(i).get("roundsamplecode").toString();
}else if(j ==2){
//受检单位
if(list.get(i).get("takeinspectionunit") ==null){
cellValue = "";
}else{
cellValue = list.get(i).get("takeinspectionunit").toString();
}
}else if(j == 3){
//地市
if(list.get(i).get("areaName") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("areaName").toString();
}
}else if(j ==4){
//监测动物种类
if(list.get(i).get("animal") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("animal").toString();
}
}else if(j == 5){
//年龄阶段
if(list.get(i).get("sampleAnimalName") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("sampleAnimalName").toString();
}
}else if(j == 6){
//监测场点类型
if(list.get(i).get("category") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("category").toString();
}
}else if(j ==7){
//饲养量
if(list.get(i).get("sampleSumNum") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("sampleSumNum").toString();
}
}else if(j == 8){
//样品总量
if(list.get(i).get("allNum") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("allNum").toString();
}
}else if(j == 9){
//a检测样品数
if(list.get(i).get("sampleNum0") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("sampleNum0").toString();
}
}else if(j == 10){
//阳性数
if(list.get(i).get("positiveNum0") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("positiveNum0").toString();
}
}else if(j == 11){
//阳性率（%）
if(list.get(i).get("sun0") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("sun0").toString();
}
}else if( j == 12){
//a检测样品数
if(list.get(i).get("sampleNum1") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("sampleNum1").toString();
}
}else if(j == 13){
//阳性数
if(list.get(i).get("positiveNum1") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("positiveNum1").toString();
}
}else if(j ==14){
//阳性率（%）
if(list.get(i).get("sun1") ==null){
cellValue = "0";
}else{
cellValue = list.get(i).get("sun1").toString();
}
}
tempCell.setCellValue(cellValue);
}
}
//导出excel
// HttpServletResponse response = ();
String fileName = "月度业务明细.xls";
try {
fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
OutputStream stream = response.getOutputStream();
if(null != wb && null != stream){
wb.write(stream);
wb.close();
stream.close();
}
// FileOutputStream outputStream = new FileOutputStream(new File("d:\\月度业务明细.xls"));
// (outputStream);
// ();
}catch (Exception e){
e.printStackTrace();
}
// return null;
}