使用Java导出Excel表格直接下载（合并单元格）——基于POI框架

使用Java导出Excel表格直接下载（合并单元格）——基于POI框架

预期效果：

一、准备工作：
1.导入所需要的jar包

<dependency>
	         <groupId>org.apache.poi</groupId>
	         <artifactId>poi</artifactId>
	         <version>3.17</version>
    	</dependency>
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>3.17</version>
		</dependency>

2.jsp代码：

htmlCode += '<a class="layui-btn layui-btn-normal layui-btn-xs" οnclick="exportExcel(\'' + d.id + '\')"><i class="layui-icon"></i>导出表格</a>';

 /**
     * 导出表格
     */
    function exportExcel(id) {
        window.open(window.path + "/questionnaire/downExla?questionnaireId="+id);
    }

二、编写所需要的类

@RestController
@RequestMapping("/questionnaire")
public class QuestionnaireController extends PageSimplenessController<QuestionnaireDTO, Long> {
/**
     * 导出表格x
     * @param request
     * @param response
     * @returnx
     */
    @GetMapping("downExla")
    public com.ars.common.entity.ResponseEntity downOnlineExla(HttpServletRequest request, HttpServletResponse response) {
        OutputStream os = null;
        FileInputStream fis = null;
        java.io.File file = null;
        String[] tableHead ={"学号","姓名","题目","答案"};
        QuestionnaireDTO questionnaireDTO = null;
        try {
            Map<String, Object> map=new HashMap<String, Object>();
            String questionnaireId = request.getParameter("questionnaireId");
            map.put("id",questionnaireId);
            List<Map> list = questionnaireService.listQuestionnaireDetail(map);

            //创建工作簿
            HSSFWorkbook wb = new HSSFWorkbook();
            if(list.size() < 1) {
                return fail("无数据！");
            }

            // 目标文件存放路径
            String targetFilePath = CommonUtil.uploadFilePath + UUID.randomUUID().toString() + ".xls";

            //创建工作表
            String safeName = WorkbookUtil.createSafeSheetName("问卷详情");
            HSSFSheet sheet = wb.createSheet(safeName);

            //设置列宽
            sheet.setColumnWidth(0, 8*1000);
            sheet.setColumnWidth(1, 8*1000);
            sheet.setColumnWidth(2, 8*1000);
            sheet.setColumnWidth(3, 4*1000);

            //表头字体
            HSSFFont fontTableHeader = wb.createFont();
            fontTableHeader.setBold(true);
            fontTableHeader.setFontHeight((short) (4*50));
            fontTableHeader.setFontName("宋体");

            //单元格字体1
            HSSFFont fontTable = wb.createFont();
            fontTable.setBold(false);
            fontTable.setFontHeight((short) (4*50));
            fontTable.setFontName("宋体");

            //表头样式
            HSSFCellStyle cellTitleStyle = wb.createCellStyle();
            cellTitleStyle.setAlignment(HorizontalAlignment.CENTER);
            cellTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            //单元格边框
            cellTitleStyle.setBorderTop(BorderStyle.THIN);
            cellTitleStyle.setBorderBottom(BorderStyle.THIN);
            cellTitleStyle.setBorderLeft(BorderStyle.THIN);
            cellTitleStyle.setBorderRight(BorderStyle.THIN);

            //单元格样式1
            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);


            //单元格边框
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);



            //设置表头
            HSSFCell cell;

            //设置标题
            HSSFRow row = sheet.createRow(0);


            //设置表头
            for (int j = 0; j < tableHead.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(tableHead[j]);//表头
                cell.setCellStyle(cellTitleStyle);
                row.setHeight((short) (5*100));
            }






            for (int i = 1; i < list.size() + 1; i++) {
                cellStyle.setFont(fontTable);
                //第一行
                HSSFRow rowCell = sheet.createRow(i);


                //设置行高
                rowCell.setHeight((short) (4*100));
                cell = rowCell.createCell(0);
                cell.setCellValue((String)list.get(i - 1).get("create_user"));
                cell.setCellStyle(cellStyle);



                cell = rowCell.createCell(1);
                cell.setCellValue((String)list.get(i - 1).get("name"));
                cell.setCellStyle(cellStyle);

                cell = rowCell.createCell(2);
                cell.setCellValue(((Integer)list.get(i - 1).get("sort")+1+".")+(String)list.get(i - 1).get("topic"));
                cell.setCellStyle(cellStyle);

                cell = rowCell.createCell(3);
                cell.setCellValue((String)list.get(i - 1).get("answer"));
                cell.setCellStyle(cellStyle);


            }


            //合并单元格
            String last="";

            int k=0;
            for (int i = 1; i < list.size() + 1; i++) {

                if (i==1){
                    last=(String)list.get(i - 1).get("create_user");

                }
                else {
                    if (last.equals((String)list.get(i - 1).get("create_user"))){
                        k+=1;
                    }else {
                        sheet.addMergedRegion(new CellRangeAddress(i-(k+1), i-1,0 ,0));
                        sheet.addMergedRegion(new CellRangeAddress(i-(k+1), i-1,1 ,1));
                        last=(String)list.get(i - 1).get("create_user");
                        k=0;
                    }
                    if(i == list.size()){
                        sheet.addMergedRegion(new CellRangeAddress(i-(k+1)+1, i,0 ,0));
                        sheet.addMergedRegion(new CellRangeAddress(i-(k+1)+1, i,1 ,1));
                    }
                }
            }



            FileOutputStream fos = new FileOutputStream(targetFilePath);
            wb.write(fos);
            fos.close();

            //文件下载前端
            file = new File(targetFilePath);
            if(file.exists()){
                fis = new FileInputStream(file);
                byte[] bytes = new byte[fis.available()];
                fis.read(bytes);

                response.setContentType(new MimetypesFileTypeMap().getContentType(file));
                response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(
                        URLDecoder.decode("问卷详情.xls", "UTF-8")
                                .getBytes("gb2312"), "ISO8859-1"));
                os = response.getOutputStream();
                os.write(bytes);
            }else {
                return fail("导出失败");
            }

        } catch (IOException e) {
            e.printStackTrace();
            return fail("导出失败");
        } finally {
            try {
                if (os != null) {
                    os.flush();
                    os.close();
                }
                if (fis != null) {
                    fis.close();
                }
                if (file != null && file.exists()){
                    file.delete();
                }
            } catch (Exception e) {

            }
        }
        return success("导出成功");
    }
}