java excel表格导出

前言：该文章主要是利用poi去导出excel表格的总结。

1. 导出excel核心代码，大致分为6步，可见代码注释。

public void exportRemarkExcel(IllegalWordVideoRemark videoRemark, HttpServletResponse res) throws BusinessErrorException {

        // 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet("证据言辞排查");  
        // 设置表格默认列宽度为15个字节  
        sheet.setDefaultColumnWidth((short) 15);  
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);   
        row.setHeight((short)480);

        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle(); 
        // 指定单元格垂直居中对齐
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 创建一个居中格式  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeight((short) 220);
        style.setFont(font);
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);  
        row.setRowStyle(style);

        //设置标题头信息
        HSSFCell cell = row.createCell((short) 0);  
        cell.setCellValue("序号");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 1);  
        cell.setCellValue("录音录像名称");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 2);  
        cell.setCellValue("录音录像时间");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 3);  
        cell.setCellValue("办案人员");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 4);  
        cell.setCellValue("讯问时间");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 5);  
        cell.setCellValue("批注");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 6);  
        cell.setCellValue("标签");  
        cell.setCellStyle(style);  

        // 第四步，写入实体数据 实际应用中这些数据从数据库得到，  
        //获取该案件下所有的录像批注
        List<IllegalWordVideoRemark> pageRemarkList = this.queryRemarkListbByCaseId(videoRemark);
        CaseDTO cases = casesMapper.findByCaseId(videoRemark.getCaseId());
        if (pageRemarkList !=null && pageRemarkList.size() > 0) {
            //录像批注的序号
            int remarkNum = 1; 
            for (int i = 0; i < pageRemarkList.size(); i++) {  
                row = sheet.createRow((int) i + 1);  
                IllegalWordVideoRemark remark = pageRemarkList.get(i);  
                // 第五步，创建单元格，并设置值  
                //序号
                row.createCell((short) 0).setCellValue(remarkNum++);  
                //录像名称
                row.createCell((short) 1).setCellValue(remark.getVideoName());    
                //批注时间轴
                row.createCell((short) 2).setCellValue(remark.getRecordVideoTime());  
                //办案人员
                row.createCell((short) 3).setCellValue(remark.getRoleEditHistory());  
                //讯问时间
                row.createCell((short) 4).setCellValue(remark.getVideoTime());  
                //记录
                row.createCell((short) 5).setCellValue(remark.getContent());  
                //标签
                row.createCell((short) 6).setCellValue(remark.getLabelContents()); 
            }
        }  
        // 第六步，将文件存到指定位置  
        try {

            String fileName = URLEncoder.encode(fileName, "UTF-8");
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            wb.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);

            // 设置response参数，可以打开下载页面
            res.reset();
            res.setContentType("application/vnd.ms-excel;charset=utf-8");
            res.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "ISO-8859-1"));
            res.setCharacterEncoding("UTF-8");

            ServletOutputStream out = res.getOutputStream();
            BufferedInputStream bis = null;
            BufferedOutputStream bos = null;
            try {
              bis = new BufferedInputStream(is);
              bos = new BufferedOutputStream(out);
              byte[] buff = new byte[2048];
              int bytesRead;
              while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
              }

            } catch (Exception e) {
              e.printStackTrace();
            } finally {
              if (bis != null) {
                  bis.close();
              }
              if (bos != null) {
                  bos.close();
              }
            }
        } catch (Exception e) {
            logger.error("导出全部批注失败", e);
        }
    }

路漫漫其修远兮，吾将上下而求索