将数据库中表生成excel

依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.10-FINAL</version>
</dependency>

/**
     * 生成Excel表
     * @param response
     * @param dataList 表格数据
     * List<String[]> dataList = new ArrayList<>();
     *         List<Map<String, Object>> results = service.findAll();
     *         for (Map<String, Object> result : results) {
     *             String[] data = new String[header.length];
     *
     *             for (int i = 0; i < header.length; i++) {
     *                 if (result.get(header[i]) != null)
     *                  //一行中所有单元格数据
     *                     data[i] = result.get(header[i]).toString();
     *             }
     *             dataList.add(data);
     *
     *         }
     * @param header 表头
     * @param name 表名
     * @throws IOException
     */
    public void writeToExcel(HttpServletResponse response, List<String[]> dataList, String[] header,String name) throws IOException{

        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle style = workbook.createCellStyle();

        //生成一个表格，设置表格名称为"UserData"
        HSSFSheet sheet = workbook.createSheet(name);

        //设置表格列宽度为10个字节
        sheet.setDefaultColumnWidth(10);

        //创建第一行表头
        HSSFRow headrow = sheet.createRow(0);

//        遍历添加表头
        for (int i = 0; i < header.length; i++) {
            //创建一个单元格

            HSSFCell cell = headrow.createCell(i);

            //创建一个内容对象
            HSSFRichTextString text = new HSSFRichTextString(header[i]);

            //将内容对象的文字内容写入到单元格中
            cell.setCellValue(text);
        }

        //模拟遍历结果集，把内容加入表格
        int temp = 1;
        for (String[] data : dataList) {
            HSSFRow row = sheet.createRow(temp++);
            for (int i = 0; i < data.length; i++) {
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(data[i]);
                cell.setCellValue(text);
            }

        }


        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");

        //这后面可以设置导出Excel的名称，此例中名为userData.xls
        response.setHeader("Content-disposition", "attachment;filename="+name+".xls");

        //刷新缓冲
        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载
        workbook.write(response.getOutputStream());
    }