SpringBoot中使用poi导出Excel

在项目中导出excel是难免不可缺少的，总结了一下，可以copy直接用。有什么地方不足的还请指出。

注意：这里固定了前台入参形式为json字符串，主要有两个key（title标题，data数据）如下：{“title”:{},”data”:[{},{},{}]}

• pom依赖

         <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        
        <dependency>
            <groupId>net.sf.json-lib</groupId>
            <artifactId>json-lib</artifactId>
            <version>2.4</version>
            <classifier>jdk15</classifier>
        </dependency>

• 工具类：ExcelUtil（如果方法报错，看一下是否导错包了，必须有json-lib jdk15的依赖哦）json-lib是一个java类库，用来处理json数据，包括生成和解析。

package com.mycompany.myapp.web.rest.util;


import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
@Component
public class ExcelExportUtil {
    //文件下载地址
    public static String DOWNLOAD_URL;

    @Value("${downloadUrl}")
    public void setDownloadUrl(String downloadUrl) {
        DOWNLOAD_URL = downloadUrl;
    }

    public static void outPutExcel(String jsonParam) throws Exception {
        JSONObject jsonObject = JSONObject.fromObject(jsonParam);
        //文件名
        String fileName = jsonObject.getString("fileName");
        //文件存放路径
        String url = DOWNLOAD_URL + fileName + ".xls";
        //标题
        String title = jsonObject.getString("title");
        JSONObject jsonObjectTitle = JSONObject.fromObject(title);
        String[] titleArray = new String[jsonObjectTitle.size()];
        Iterator iteratorTitle = jsonObjectTitle.keys();
        int m = 0;
        while (iteratorTitle.hasNext()){
            String key = (String)iteratorTitle.next();
            titleArray[m] = jsonObjectTitle.getString(key);
            m++;
        }
        //数据
        String data = jsonObject.getString("data");
        JSONArray jsonArray = JSONArray.fromObject(data);
        List<Object[]> dataList = new ArrayList<>();
        for(int i = 0;i<jsonArray.size();i++){
            JSONObject json = jsonArray.getJSONObject(i);
            Object[] objects = new Object[json.size()];
            Iterator iterator = json.keys();
            int n = 0;
            while (iterator.hasNext()){
                String key = (String)iterator.next();
                objects[n] = json.getString(key);
                n++;
            }
            dataList.add(objects);
        }

        //字段为null时设置为""
        for(int i =0;i<dataList.size();i++){
            Object[] obj = dataList.get(i);
            for(int j = 0;j<obj.length;j++){
                if(obj[j]==null||"null".equals(obj[j])){
                    obj[j] = "";
                }
            }
        }

        //excel创建
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(fileName);
        createTitle(workbook,sheet,titleArray);

        for(int i =0;i<dataList.size();i++){
            HSSFRow row = sheet.createRow(i+1);
            Object[] obj = dataList.get(i);
            for(int j = 0;j<obj.length;j++){
                row.createCell(j).setCellValue(obj[j].toString());
            }
        }

        File file = new File(url);
        //文件不存在则创建
        if(!file.exists())
        {
            try {
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        FileOutputStream outputStream = new FileOutputStream(url);
        workbook.write(outputStream);
        outputStream.close();
    }

    //创建表头
    private static void createTitle(HSSFWorkbook workbook, HSSFSheet sheet,String[] titleArray) {
        HSSFRow row = sheet.createRow(0);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontName("仿宋");
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);

        HSSFCell cell;
        for(int i = 0;i<titleArray.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(titleArray[i]);
            cell.setCellStyle(style);
            //自适应列宽
            sheet.autoSizeColumn((short)i);
        }
    }
}

• yml文件中自定义路径配置：

 比如：downloadUrl: /Users/parkin/Downloads/

• 控制层调用：

 /**
     * 学校列表导出excel
     * @param jsonParam
     * @param response
     */
    @PostMapping(value = "/schoolsExcelExport",produces="application/octet-stream")
    @ResponseBody
    @PreAuthorize("hasRole(\""+ AuthoritiesConstants.ADMIN +"\")")
    public void schoolsExcelExport(@RequestBody String jsonParam, HttpServletResponse response) {
        try{
            String sheetName = "学校列表";
            //excel导出
            ExcelExportUtil.outPutExcel(jsonParam,response,sheetName);
        }
        catch (Exception ex){
            throw new BadRequestAlertException(ex.getMessage(),ENTITY_NAME,"param");
        }
    }

• postman 测试：