Apache poi 复杂表头导出

待我称王封你为后i 2022-04-22 06:38 280阅读 0赞

本来公司是有一套自己的Excel导出工具，他是基于EasyPoi，奈何集成的EasyPoi版本过低，不支持一些复杂表头，只好用Apache poi 的这套框架来做，当然EasyPoi也是基于Apache poi 封装的

[http://poi.apache.org/components/spreadsheet/quick-guide.html\#CreateCells][http_poi.apache.org_components_spreadsheet_quick-guide.html_CreateCells]

我参考的是这篇文章

[https://blog.csdn.net/Damon\_wdc/article/details/78925005][https_blog.csdn.net_Damon_wdc_article_details_78925005]

但是对于这篇博文有一些问题，自己又在上面按照自己的理解改了一下，也达到了与原博主相同的效果

修改后的代码如下：

public HSSFWorkbook export(List list) {
    
        // 声明String数组，并初始化元素（表头名称）
        //第一行表头字段，合并单元格时字段跨几列就将该字段重复几次
        String[] excelHeader0 = {
                "城市名称",
                "监测点",
                "污染物浓度及空气质量分指数（AQI）", "污染物浓度及空气质量分指数（AQI）","污染物浓度及空气质量分指数（AQI）", "污染物浓度及空气质量分指数（AQI）",
                "污染物浓度及空气质量分指数（AQI）", "污染物浓度及空气质量分指数（AQI）","污染物浓度及空气质量分指数（AQI）", "污染物浓度及空气质量分指数（AQI）",
                "污染物浓度及空气质量分指数（AQI）", "污染物浓度及空气质量分指数（AQI）","污染物浓度及空气质量分指数（AQI）", "污染物浓度及空气质量分指数（AQI）",
                "空气质量指数（AQI）",
                "首要污染物",
                "空气质量指数级别","空气质量指数类别", "空气质量指数类别"
        };
        //  “0,2,0,0”  ===>  “起始行，截止行，起始列，截止列”
        String[] headnum0 = {
                "0,2,0,0", //第0列跨3行
                "0,2,1,1", //第1列跨3行
                "0,0,2,13", //第2-13列跨1行
                "0,2,14,14",//第14列跨3行
                "0,2,15,15",//第15列跨3行
                "0,2,16,16",//第16列跨3行
                "0,1,17,18" //第17-18列跨2行
        };
        //第二行表头字段，其中的空的双引号是为了补全表格边框
        String[] excelHeader1 = {
                "二氧化硫（SO₂）24小时平均", "二氧化硫（SO₂）24小时平均",
                "二氧化氮（NO₂）24小时平均", "二氧化氮（NO₂）24小时平均",
                "颗粒物（粒径小于等于10μm）24小时平均","颗粒物（粒径小于等于10μm）24小时平均",
                "一氧化碳（CO）24小时平均", "一氧化碳（CO）24小时平均",
                "臭氧（O₃）最大8小时平均", "臭氧（O₃）最大8小时平均",
                "颗粒物（粒径小于等于2.5μm）24小时平均", "颗粒物（粒径小于等于2.5μm）24小时平均"
        };
        // 合并单元格
        String[] headnum1 = {
                "1,1,2,3", //第2-3列跨第1行
                "1,1,4,5", //第4-5列跨第1行
                "1,1,6,7",
                "1,1,8,9",
                "1,1,10,11",
                "1,1,12,13" };
        System.out.println(headnum1.length);
    
        //第三行表头字段
        String[] excelHeader2 = {
                "浓度/（μg/m3）",  "分指数",
                "浓度/（μg/m3）",  "分指数",
                "浓度/（μg/m3）",  "分指数",
                "浓度/（μg/m3）",  "分指数",
                "浓度/（μg/m3）",  "分指数",
                "浓度/（μg/m3）",  "分指数",
                "类别", "颜色"
        };
    
        String[] headnum2 = {
                "2,2,2,2",  //第2列跨第2行
                "2,2,3,3",
                "2,2,4,4",
                "2,2,5,5",
                "2,2,6,6",
                "2,2,7,7",
                "2,2,8,8",
                "2,2,9,9",
                "2,2,10,10",
                "2,2,11,11",
                "2,2,12,12",
                "2,2,13,13",
                "2,2,17,17",
                "2,2,18,18"
        };
    
        // 声明一个工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = wb.createSheet("竞品收集汇总报表2");
    
        // 生成一种样式style
        HSSFCellStyle style = wb.createCellStyle();
        // 设置样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
        // 生成一种字体
        HSSFFont font = wb.createFont();
        // 设置字体
        font.setFontName("微软雅黑");
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 在样式中引用这种字体
        style.setFont(font);
    
        // 生成并设置另一个样式style2
        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
        // 生成另一种字体2
        HSSFFont font2 = wb.createFont();
        // 设置字体
        font2.setFontName("微软雅黑");
        // 设置字体大小
        font2.setFontHeightInPoints((short) 12);
        // 字体加粗
        // font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 在样式2中引用这种字体
        style2.setFont(font2);
    
        // 生成表格的第0行表头
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < excelHeader0.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeader0[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
        }
        // 动态合并单元格
        for (int i = 0; i < headnum0.length; i++) {
            sheet.autoSizeColumn(i, true);
            String[] temp = headnum0[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
        }
    
        // 第二行表头
        row = sheet.createRow(1);
        for (int i = 0; i < excelHeader1.length; i++) {
            HSSFCell cell = row.createCell(i+2);
            cell.setCellValue(excelHeader1[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i+2, true);// 自动调整宽度
        }
        // 动态合并单元格
        for (int i = 0; i < headnum1.length; i++) {
    
            sheet.autoSizeColumn(i, true);
            String[] temp = headnum1[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
        }
    
        // 第三行表头
          row = sheet.createRow(2);
          for (int i = 0; i < excelHeader2.length; i++) {
              if(i>=12){
                  HSSFCell cell = row.createCell(i + 2 + 3);
                  cell.setCellValue(excelHeader2[i]);
                  cell.setCellStyle(style);
                  sheet.autoSizeColumn(i + 2 + 3, true);// 自动调整宽度
              }else {
                  HSSFCell cell = row.createCell(i + 2);
                  cell.setCellValue(excelHeader2[i]);
                  cell.setCellStyle(style);
                  sheet.autoSizeColumn(i + 2, true);// 自动调整宽度
              }
    
        }
        // 动态合并单元格
        for (int i = 0; i < headnum2.length; i++) {
            sheet.autoSizeColumn(i, true);
            String[] temp = headnum2[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
        }
        //这个至此只是输出了表头，对于表格内容应当遍历list，循环填充每一个单元格
        return wb;
    }

调用这个方法

protected  <T> void doExportXls(HttpServletResponse response, HttpServletRequest request,  List<TsReportVo> dataSet, Class<?> pojoClass, String title) {
            response.setContentType("application/vnd.ms-excel");
            ServletOutputStream fOut = null;
            try {
                    workbookname = new String(title.getBytes("UTF-8"), "ISO8859-1");
                    response.setHeader("Content-Disposition", "attachment;filename=" + workbookname + ".xls");
                }
              /**
                Workbook workbook = new HSSFWorkbook();
                //如果没有sheet导出来的表格内容异常
                Sheet sheet = workbook.createSheet("new sheet");
                // Create a row and put some cells in it. Rows are 0 based.
                Row row = sheet.createRow(0);
                // Create a cell and put a value in it.
                Cell cell = row.createCell(0);
                cell.setCellValue("我是123");
                Cell cell2 = row.createCell(9);
                cell2.setCellValue("我是row.createCell(9)创建，9代表这一行第9个单元格，从0开始");
    
                fOut = response.getOutputStream();
               workbook.write(fOut);
              **/
                ExportUtil exportUtil = new ExportUtil();
                HSSFWorkbook wb = exportUtil.export(dataSet);//这一句调用我们上面写的方法
                fOut = response.getOutputStream();
                wb.write(fOut);
            } catch (Exception var15) {
                var15.printStackTrace();
                throw new Exception("导出失败！");
            } finally {
                try {
                    fOut.flush();
                    fOut.close();
                } catch (IOException var14) {
                    var14.printStackTrace();
                }
            }
        }

![20181102224259336.png][]

上面一一共渲染了三个表头，我们分解一下

第一个表头

![2018110223011644.png][]

第一二个表头

![20181102230201731.png][] 可以看到这个表头的表格中的字比较拥挤，主要是因为这个方法

[https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Sheet.html\#autoSizeColumn][https_poi.apache.org_apidocs_dev_org_apache_poi_ss_usermodel_Sheet.html_autoSizeColumn]  
autoSizeColumn  
void autoSizeColumn（int column）  
调整列宽以适合内容。  
在大型工作表上，此过程可能相对较慢，因此通常只应在处理结束时每列调用一次。  
您可以指定是否应考虑或忽略合并单元格的内容。默认是忽略合并的单元格。  
参数：  
column - 列索引

autoSizeColumn  
void autoSizeColumn（int column， boolean useMergedCells）  
调整列宽以适合内容。  
在大型工作表上，此过程可能相对较慢，因此通常只应在处理结束时每列调用一次。  
您可以指定是否应考虑或忽略合并单元格的内容。默认是忽略合并的单元格。  
参数：  
column - 列索引  
useMergedCells - 在计算列的宽度时是否使用合并单元格的内容

![20181102230316632.png][]

![20181102230414879.png][]

需要注意的是要理解Excel的表格数据的渲染是从左往右，从上往下，逐个单元格渲染的

单个单元格

**Workbook** workbook = new HSSFWorkbook();

//如果没有sheet导出来的表格内容异常   
**Sheet** sheet = workbook.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.   
**Row** row = sheet.createRow(0);

// Create a cell and put a value in it.  
** Cell** cell = row.createCell(0);   
 cell.setCellValue("我是123");   
 Cell cell2 = row.createCell(9);   
 cell2.setCellValue("我是row.createCell(9)创建，9代表这一行第9个单元格，从0开始");

fOut = response.getOutputStream();

workbook.write(fOut);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VvdGFpbA_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VvdGFpbA_size_16_color_FFFFFF_t_70 1][]

最后贴上我实际写的代码

@RequestMapping(params = "exportExcel2")
        public void ExportExcel2(TsCGCReportVo vo, HttpServletRequest request, HttpServletResponse response){
            List<TsCGCReportVo> collection = tsCompGoodsCollectionReportService.findGoodsCollection2(vo, null);
            this.doExportXls(response, request, collection, TsCGCReportVo.class, "竞品收集汇总表");
        }
    
        protected  <T> void doExportXls(HttpServletResponse response, HttpServletRequest request,  List<TsCGCReportVo> dataSet, Class<?> pojoClass, String title) {
            response.setContentType("application/vnd.ms-excel");
            ServletOutputStream fOut = null;
    
            try {
                String workbookname;
                if (BrowserUtils.isIE(request)) {
                    response.reset();
                    response.setHeader("Cache-Control", "private");
                    response.setHeader("Pragma", "private");
                    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                    response.setHeader("Content-Type", "application/force-download");
                    workbookname = URLEncoder.encode(title + ".xls", "UTF-8");
                    response.setHeader("Content-disposition", "attachment;filename=" + workbookname);
                } else {
                    workbookname = new String(title.getBytes("UTF-8"), "ISO8859-1");
                    response.setHeader("Content-Disposition", "attachment;filename=" + workbookname + ".xls");
                }
                ExportUtil exportUtil = new ExportUtil();
                HSSFWorkbook wb = exportUtil.exportGoods(dataSet);
                fOut = response.getOutputStream();
                wb.write(fOut);
            } catch (Exception var15) {
                var15.printStackTrace();
                throw new BusinessException("导出失败！");
            } finally {
                try {
                    fOut.flush();
                    fOut.close();
                } catch (IOException var14) {
                    var14.printStackTrace();
                }
            }
        }
    }

public class ExportUtil {
    
        public HSSFWorkbook exportGoods(List<TsCGCReportVo> list) {
            //int titleRow = 6;//表头标题及副标题占6行
            //int tableBody = titleRow+1;//表头开始
            // 声明String数组，并初始化元素（表头名称）
            //第一行表头字段，合并单元格时字段跨几列就将该字段重复几次
            String[] excelHeader0 = {
                    "组织",
                    "职位",
                    "姓名",
                    "终端编码",
                    "终端名称",
                    "终端地址",
                    "拜访时间",
                    "竞品编码",
                    "竞品名称",
                    "当月销量",
                    "竞品渠道促销","竞品渠道促销","竞品渠道促销","竞品渠道促销","竞品渠道促销",
                    "竞品消费者促销","竞品消费者促销","竞品消费者促销"
    
            };//length=12
            //  “0,2,0,0”  ===>  “起始行，截止行，起始列，截止列”
            String[] headnum0 = {
                    "7,9,0,0",
                    "7,9,1,1",
                    "7,9,2,2",
                    "7,9,3,3",
                    "7,9,4,4",
                    "7,9,5,5",
                    "7,9,6,6",
                    "7,9,7,7",
                    "7,9,8,8",
                    "7,9,9,9",
                    "7,7,10,14",
                    "7,7,15,17"
            };
            //第二行表头字段，其中的空的双引号是为了补全表格边框
            String[] excelHeader1 = {
                   "渠道促销",
                   "陈列奖励","陈列奖励",
                   "促销效果打分",
                   "文字描述",
                   "消费者活动",
                   "促销效果打分",
                   "文字描述"
            };
            // 合并单元格
            String[] headnum1 = {
                    "8,9,10,10",
                    "8,8,11,12",
                    "8,9,13,13",
                    "8,9,14,14",
                    "8,9,15,15",
                    "8,9,16,16",
                    "8,9,17,17"
            };
            System.out.println(headnum1.length);
    
            //第三行表头字段
            String[] excelHeader2 = {
                  "天",
                  "瓶"
            };
    
            String[] headnum2 = {
                    "9,9,11,11",
                    "9,9,12,12",
    
            };
    
            // 声明一个工作簿
            HSSFWorkbook wb = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = wb.createSheet("竞品收集汇总报表2");
    
            // 生成一种样式style
            HSSFCellStyle style = wb.createCellStyle();
            // 设置样式
            style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
            // 生成一种字体
            HSSFFont font = wb.createFont();
            // 设置字体
            font.setFontName("微软雅黑");
            // 设置字体大小
            font.setFontHeightInPoints((short) 12);
            // 在样式中引用这种字体
            style.setFont(font);
    
            // 生成标题样式style1
            HSSFCellStyle style1 = wb.createCellStyle();
            // 设置样式
            style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
            // 生成标题字体1
            HSSFFont font1 = wb.createFont();
            // 设置字体
            font1.setFontName("微软雅黑");
            // 设置字体大小
            font1.setFontHeightInPoints((short) 25);
            // 字体加粗
            font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 在样式中引用这种字体
            style1.setFont(font1);
    
    
            HSSFRow titleRow = sheet.createRow(0);
            HSSFCell titleCell = titleRow.createCell(0);
            titleCell.setCellValue("竞品收集报表");
            titleCell.setCellStyle(style1);
            sheet.autoSizeColumn(0, true);
            sheet.addMergedRegion(new CellRangeAddress(0, 4, 0, 17));
    
            // 生成标题样式style2
            HSSFCellStyle style2 = wb.createCellStyle();
            // 设置样式
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
            HSSFRow titleRow2 = sheet.createRow(5);
            HSSFCell titleCell2 = titleRow2.createCell(14);
            titleCell2.setCellValue("导出人：超级管理员");
            titleCell2.setCellStyle(style2);
            sheet.autoSizeColumn(14, true);
            sheet.addMergedRegion(new CellRangeAddress(5, 6, 14, 17));
    
            HSSFRow row = sheet.createRow(7);
            for (int i = 0; i < excelHeader0.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(excelHeader0[i]);
                cell.setCellStyle(style);
                sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
            }
            // 动态合并单元格
            for (int i = 0; i < headnum0.length; i++) {
                sheet.autoSizeColumn(i, true);
                String[] temp = headnum0[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
            }
    
            // 第二行表头
            row = sheet.createRow(8);
            for (int i = 0; i < excelHeader1.length; i++) {
                HSSFCell cell = row.createCell(i+10);
                cell.setCellValue(excelHeader1[i]);
                cell.setCellStyle(style);
                sheet.autoSizeColumn(i+10, true);// 自动调整宽度
            }
            // 动态合并单元格
            for (int i = 0; i < headnum1.length; i++) {
    
                sheet.autoSizeColumn(i, true);
                String[] temp = headnum1[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
            }
    
            // 第三行表头
            row = sheet.createRow(9);
            for (int i = 0; i < excelHeader2.length; i++) {
                HSSFCell cell = row.createCell(i + 11);
                cell.setCellValue(excelHeader2[i]);
                cell.setCellStyle(style);
                sheet.autoSizeColumn(i + 11, true);// 自动调整宽度
            }
            // 动态合并单元格
            for (int i = 0; i < headnum2.length; i++) {
                sheet.autoSizeColumn(i, true);
                String[] temp = headnum2[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
            }
    
            for(int i =0;i<list.size();i++){
                TsCGCReportVo vo = list.get(i);
                row = sheet.createRow(i+10);
    
                HSSFCell cell0 = row.createCell(0);
                cell0.setCellValue(vo.getOrgName());
    
                HSSFCell cell1 = row.createCell(1);
                cell1.setCellValue(vo.getPositionName());
    
                HSSFCell cell2 = row.createCell(2);
                cell2.setCellValue(vo.getUserName());
    
                HSSFCell cell3 = row.createCell(3);
                cell3.setCellValue(vo.getTerminalCode());
    
                HSSFCell cell4 = row.createCell(4);
                cell4.setCellValue(vo.getTerminalName());
    
                HSSFCell cell5 = row.createCell(5);
                cell5.setCellValue(vo.getTerminalAddress());
    
                HSSFCell cell6 = row.createCell(6);
                cell6.setCellValue(vo.getCreateDate());
    
                HSSFCell cell7 = row.createCell(7);
                cell7.setCellValue(vo.getComGoodsName());
    
                HSSFCell cell8 = row.createCell(8);
                cell8.setCellValue(vo.getTheMonthSale());
    
                HSSFCell cell9 = row.createCell(9);
                cell9.setCellValue(vo.getChannelGiftName());
    
                HSSFCell cell10 = row.createCell(10);
                cell10.setCellValue(vo.getColumnDay());
    
                HSSFCell cell11 = row.createCell(11);
                cell11.setCellValue(vo.getColumnReward());
    
                HSSFCell cell12 = row.createCell(12);
                cell12.setCellValue(vo.getChannelAnalPromEff());
    
                HSSFCell cell13 = row.createCell(13);
                cell13.setCellValue(vo.getChannelSaleRemark());
    
                HSSFCell cell14 = row.createCell(14);
                cell14.setCellValue(vo.getConsumerActivitiesName());
    
                HSSFCell cell15 = row.createCell(15);
                cell15.setCellValue(vo.getConsumerActivitiesName());
    
                HSSFCell cell16 = row.createCell(16);
                cell16.setCellValue(vo.getConAnalPromEff());
    
                HSSFCell cell17 = row.createCell(17);
                cell17.setCellValue(vo.getConSaleRemark());
            }
            return wb;
        }
    }

页面上写个导出按钮，onclick函数就一句话

window.location.href = 'testController.do?exportExcel2';  就可以导出了

可以看出这个请求是get方式的后面还可以拼接参数，但是对中文的支持不是很友好

具体解决方案如下

[https://blog.csdn.net/uotail/article/details/84192036][https_blog.csdn.net_uotail_article_details_84192036]   的 10.2所述

![20181103121051877.png][]

报表对于图片的导出如下

if(StringUtil.isNotEmpty(entity.getPicture())){
    	try {
    		ByteArrayOutputStream outputStream = this.urlImageToStream(entity.getPicture());
    
    		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    		HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1020, 250,(short) 17, 7, (short) 17, 7);
    		anchor.setAnchorType(2);
    		patriarch.createPicture(anchor, wb.addPicture(outputStream.toByteArray(), wb.PICTURE_TYPE_JPEG));
    
    	} catch (Exception e) {
    		e.printStackTrace();
    	}
    }

其中entity.getPicture()存放的是图片的http地址

public ByteArrayOutputStream urlImageToStream(String httpImagePath){
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            try {
                InputStream inputStream = null;
                HttpURLConnection connection = (HttpURLConnection) new URL(httpImagePath).openConnection();
    //这里读取超时和连接超时都为5秒，实际可以设小一点，否则网络不好或图片地址不正确会很慢
                connection.setReadTimeout(5000);
                connection.setConnectTimeout(5000);
                connection.setRequestMethod("GET");
                if (connection.getResponseCode() == HttpURLConnection.HTTP_OK) {
                    inputStream = connection.getInputStream();
                }
                BufferedImage read = ImageIO.read(inputStream);
                ImageIO.write(read,"jpg",outputStream);
    
            } catch (IOException e) {
                e.printStackTrace();
            }
                return outputStream;
        }

**在实际开发中我们的业务报表往往是很复杂的特别是动态报表，但只要我们掌握了规律，认真的计算表头的位置，那也是很简单的，就是有点枯燥而已**

[http_poi.apache.org_components_spreadsheet_quick-guide.html_CreateCells]: http://poi.apache.org/components/spreadsheet/quick-guide.html#CreateCells
[https_blog.csdn.net_Damon_wdc_article_details_78925005]: https://blog.csdn.net/Damon_wdc/article/details/78925005
[20181102224259336.png]: /images/20220422/73473384a7114313a3259c714c53576b.png
[2018110223011644.png]: /images/20220422/6eb891d074a648cdab8c3fea0b95d5ea.png
[20181102230201731.png]: /images/20220422/35eb0d07d7384be59ed8feae5965d6f6.png
[https_poi.apache.org_apidocs_dev_org_apache_poi_ss_usermodel_Sheet.html_autoSizeColumn]: https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Sheet.html#autoSizeColumn
[20181102230316632.png]: /images/20220422/bef12bf3eaa84ca4aef7391ceb3b6558.png
[20181102230414879.png]: /images/20220422/70f98d62b0764fe79cfbee0288d2d719.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VvdGFpbA_size_16_color_FFFFFF_t_70]: /images/20220422/05a66426e018497ea1871d3cdb8a781a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VvdGFpbA_size_16_color_FFFFFF_t_70 1]: /images/20220422/2373a1dcabc544528b0fa15147771b98.png
[https_blog.csdn.net_uotail_article_details_84192036]: https://blog.csdn.net/uotail/article/details/84192036
[20181103121051877.png]: /images/20220422/24f25a0859c1438c9551be77b92780af.png