Springboot+poi导出Excel

Springboot+poi导出Excel

一、引入jar包

注意需要引入3.8版本,POI3.8提供了SXSSFWorkbook类,来处理大数据内存溢出的问题.可设置默认内存大小,多出的部分可存入硬盘中,不会内存溢出.

<!-- poi依赖 -->
<dependency>	
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
	<version>3.8</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.8</version>
</dependency>

二、编写poi工具类

工具类的核心在于,将从数据库查询出来的bean对象,转化成json对象,然后通过bean中的字段去获取出json对象的值,

再依次写入每个单元格.本工具类兼容中文,对中文乱码等问题单独处理.导出所对应的字段key可保存在数据库中.

	/**
     * Description: 1、将list中的data写入表格中
     * 
     * @param messageService
     *            国际化对象
     * @param keyName
     *            导出文件的key值
     * @param list
     *            数据库查询出的Bean集合
     * @param fieldNames
     *            查询出的字段list
     * @return
     * @author: yizl
     * @date: Oct 30, 2018 1:08:08 PM
     */
    public static <T, S> SXSSFWorkbook export(LocaleMessageSourceService messageService,
                                              String keyName, List<T> list,
                                              List<String> fieldNames)
    {
        // 内存中保留 10000 条数据，以免内存溢出，其余写入 硬盘
        SXSSFWorkbook wb = new SXSSFWorkbook(2000);
        // 创建一个Excel的sheet
        Sheet sheet = wb.createSheet(messageService.getMessage(keyName));
        for (int j = 0; j <= list.size(); j++ )
        {
            Row row = sheet.createRow(j);// 第j行
            JSONObject jsonObj = null;
            if (j != 0)
            {

                jsonObj = (JSONObject)JSON.toJSON(list.get(j - 1));
            }
            for (int k = 0; k < fieldNames.size(); k++ )
            {
                // 第一列单元格
                Cell cell = row.createCell(k);
                // 第一行
                if (j == 0)
                {
                    CellStyle style = getColumnTopStyle(wb);
                    cell.setCellStyle(style);
                    // 第一列数据
                    cell.setCellValue(
                    	//将表头国际化
                        messageService.getMessage(keyName + "." + fieldNames.get(k)));
                }
                else
                {
                    // 将bean对象转换成JSON对象
                    String content = jsonObj.getString(fieldNames.get(k));
                    if (content == null)
                    {
                        content = "";
                    }
                    CellStyle style = getBodyStyle(wb);
                    cell.setCellStyle(style);
                    cell.setCellValue(content);
                }
            }

        }

        /*
         * for (int k = 0; k < fieldNames.size(); k++ ) { 
         * // poi提供的自动调整每列的宽度,但是不兼容中文调整
         * sheet.autoSizeColumn(k); }
         */
        setSizeColumn(sheet, fieldNames.size());
        return wb;
    }

    /**
     * Description: 1、调整列宽,兼容中文
     * 
     * @param sheet
     * @param size
     * @author: yizl
     * @date: Nov 1, 2018 11:31:56 AM
     */
    private static void setSizeColumn(Sheet sheet, int size)
    {
        for (int columnNum = 0; columnNum < size; columnNum++ )
        {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++ )
            {
                Row currentRow;
                // 当前行未被使用过
                if (sheet.getRow(rowNum) == null)
                {
                    currentRow = sheet.createRow(rowNum);
                }
                else
                {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null)
                {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == Cell.CELL_TYPE_STRING)
                    {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }
                }
            }

            // Excel的长度为字节码长度*256,*1.3为了处理数字格式
            columnWidth = (int)Math.floor(columnWidth * 256 * 1.3);
            //单元格长度大于20000的话也不美观,设置个最大长度
            columnWidth = columnWidth >= 20000 ? 20000 : columnWidth;
            //设置每列长度
            sheet.setColumnWidth(columnNum, columnWidth);
        }
    }

    /**
     * Description: 1、通过浏览器workbook以流的形式输出,为了处理中文表名路是你吗问题.
     * 
     * @param workbook
     *            文件对象
     * @param request
     * @param response
     * @param fileName
     *            文件名
     * @author: yizl
     * @date: Oct 30, 2018 1:06:27 PM
     */
    public static void writeToResponse(SXSSFWorkbook workbook, HttpServletRequest request,
                                       HttpServletResponse response, String fileName)
    {
        try
        {
            String userAgent = request.getHeader("User-Agent");
            // 解决中文乱码问题
            String fileName1 = "Excel-" + fileName + ".xlsx";
            String newFilename = URLEncoder.encode(fileName1, "UTF8");
            // 如果没有userAgent，则默认使用IE的方式进行编码，因为毕竟IE还是占多数的
            String rtn = "filename=\"" + newFilename + "\"";
            if (userAgent != null)
            {
                userAgent = userAgent.toLowerCase();
                // IE浏览器，只能采用URLEncoder编码
                if (userAgent.indexOf(IE) != -1)
                {
                    rtn = "filename=\"" + newFilename + "\"";
                }
                // Opera浏览器只能采用filename*
                else if (userAgent.indexOf(OPERA) != -1)
                {
                    rtn = "filename*=UTF-8''" + newFilename;
                }
                // Safari浏览器，只能采用ISO编码的中文输出
                else if (userAgent.indexOf(SAFARI) != -1)
                {
                    rtn = "filename=\"" + new String(fileName1.getBytes("UTF-8"), "ISO8859-1")
                          + "\"";
                }
                // FireFox浏览器，可以使用MimeUtility或filename*或ISO编码的中文输出
                else if (userAgent.indexOf(FIREFOX) != -1)
                {
                    rtn = "filename*=UTF-8''" + newFilename;
                }
            }

            String headStr = "attachment;  " + rtn;
            response.setContentType("APPLICATION/ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", headStr);
            // 响应到客户端
            OutputStream os = response.getOutputStream();
            workbook.write(os);
        }
        catch (UnsupportedEncodingException e)
        {

            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }

    /**
     * Description: 1、列头单元格样式
     * 
     * @param workbook
     * @return
     * @author: yizl
     * @date: Oct 30, 2018 1:22:44 PM
     */
    public static CellStyle getColumnTopStyle(SXSSFWorkbook workbook)
    {

        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short)11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

    /**
     * Description: 1、设置表体的单元格样式
     * 
     * @param workbook
     * @return
     * @author: yizl
     * @date: Oct 30, 2018 1:18:42 PM
     */
    private static CellStyle getBodyStyle(SXSSFWorkbook workbook)
    {
        // 创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        // 设置单元格居中对齐
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 设置单元格居中对齐
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 创建单元格内容不显示自动换行
        cellStyle.setWrapText(false);
        // 设置单元格字体样式
        XSSFFont font = (XSSFFont)workbook.createFont();
        font.setFontName("Courier New");// 设置字体
        font.setFontHeight(11);// 设置字体的大小
        cellStyle.setFont(font);// 将字体添加到表格中去
        // 设置单元格边框为细线条
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }

三、编写controller层

获取SXSSFWorkbook对象，在进行输出。request获取用户使用的浏览器内核类型。通过这个字段设置表名的编码格式，从而兼容不同浏览器。

 	@GetMapping("/getCameraListExcel")
    public void getCameraListExcel(Camera camera)
    {
        long startTime = System.currentTimeMillis();
        ServletRequestAttributes servletRequestAttributes = 								
        (ServletRequestAttributes)RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = servletRequestAttributes.getRequest();
        HttpServletResponse response = servletRequestAttributes.getResponse();
        SXSSFWorkbook workbook = service.getCameraListExcel(camera);
       //以流输出到浏览器
       POIUtil.writeToResponse(workbook, request, response,
            messageService.getMessage(ExcelConstant.EXPORTCAMERA));
        long endTime = System.currentTimeMillis();
        System.out.println("运行时间:" + (endTime - startTime) + "ms");
    }

四、编写service层

字段的key集合，保存在数据库中，每列对应一个key，表头通过这个key可获取出来（实现导出Excel国际化），表体的内容通过这个key获取出json对象。

	/**
     * Description: 1、获取Camera对象放入excel中
     * 
     * @param camera
     * @return
     * @author: yizl
     * @date: Nov 3, 2018 1:33:05 PM
     */
    public SXSSFWorkbook getCameraListExcel(Camera camera)
    {
        // 字段名Key
        List<String> fieldNames = mapper.getExcelConfig();
        List<Camera> cameraList= mapper.getCameraListExcel(camera);
        SXSSFWorkbook workbook = POIUtil.export(messageService, ExcelConstant.EXPORTCAMERA,
            cameraList, fieldNames);
        return workbook;
    }

五、总结

设置样式的话可根据需求进行调整，这个过程比较费时，导出7000条数据，无样式在2s左右，有样式需要3分钟。