JAVA使用POI导出Excel

开发过程中会经常需要将报表导出成Excel，本人整理了一份，应该复制下来就能用

使用之前需要先导入poi的相关依赖包，可以去maven库拉取，我这边使用的是3.6的版本，还有一些其他的json相关的依赖也同理

代码部分：

poi工具类

package com.example.Poi.util;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
import java.util.Map;

/**
 * 通过POI实现excel导出
 */
public class ExcelLeadUtil {

    /**
     * keys格式实例
     * 导出的子标题顺序  let titleArr = ["姓名","性别","年龄","地址","身份证号"];
     * { //导出数据示例
     *    name:"张三",
     *    sex:"男",
     *    age:"18",
     *    site:"重庆",
     *    code:"465874651541",
     * }
     * 对应的变量名顺序  let keys = ["name","sex","age","site","code"];
     */

    /**
     * @param bigTitle 大标题
     * @param widthArr 单元格宽度 比如5000(5厘米)
     * @param dataArr 所有的导出数据
     * @param titleArr 单元格标题
     * @param keys 数据导出顺序key 顺序必须和单元格标题导出顺序一致,数据为导出数据的实体类变量名 或者map key
     * @return @return 返回一个工作簿
     */
    public static HSSFWorkbook exportExcel(String bigTitle, List<Integer> widthArr,
                                           List dataArr, List<String> titleArr,List<String> keys){
        //创建工作簿
        HSSFWorkbook work = new HSSFWorkbook();
        //创建工作簿分页(sheet)
        HSSFSheet sheet = work.createSheet();
        //设置单元格样式
        HSSFCellStyle cellStyle = work.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中

        //设置大标题
        int size = titleArr.size();
        //参数要求：起始行，结束行，起始列，结束列
        CellRangeAddress region = new CellRangeAddress(0,0,0,size-1);
        sheet.addMergedRegion(region);
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(bigTitle);

        // 设置单元格宽度,并添加子标题
        HSSFRow row1 = sheet.createRow(1);//子标题行
        for (int i = 0; i < size; i++) {
            sheet.setColumnWidth(i, widthArr.get(i));
            HSSFCell cell1 = row1.createCell(i);
            cell1.setCellValue(titleArr.get(i));
            cell1.setCellStyle(cellStyle);
        }

        //添加内容
        int length = dataArr.size();
        for(int i=2;i<length+2;i++){//确认数据行数
            HSSFRow rows = sheet.createRow(i);
            String s = JSONObject.toJSONString(dataArr.get(i-2));
            Map<String,String> map = (Map<String,String>)JSONObject.parseObject(s, Map.class);
            int ce = 0;
            //根据顺序key进行导出
            for(int u=0;u<keys.size();u++){
                for(String key : map.keySet()){
                    if(key.equals(keys.get(u))){
                        HSSFCell cel = rows.createCell(ce);
                        cel.setCellStyle(cellStyle);
                        cel.setCellValue(map.get(key));
                        ce++;
                    }
                }
            }
        }

        return work;
    }


}

JS相关代码

$("#poi").click(()=>{
        //设置标题
        let titleArr = ["姓名","性别","年龄","地址","身份证号",];
        let keys = ["name","sex","age","site","code"];
        let widthArr = [2000,2000,2000,2000,5000];
        //设置导出内容(测试数据)
        let dataArr = [];
        let zs = {
            name:"张三",
            sex:"男",
            age:"18",
            site:"重庆",
            code:"465874651541",
        };
        let ls = {
            name:"李四",
            sex:"男",
            age:"19",
            site:"重庆",
            code:"486841234978512",
        };
        let we = {
            name:"王二",
            sex:"男",
            age:"20",
            site:"重庆",
            code:"8945842146352",
        };
        let kk = {
            name:"康康",
            sex:"男",
            age:"21",
            site:"重庆",
            code:"4514566489023",
        };
        let jie = {
            name:"简",
            sex:"女",
            age:"22",
            site:"重庆",
            code:"922048512050",
        };
        dataArr.push(zs);
        dataArr.push(ls);
        dataArr.push(we);
        dataArr.push(kk);
        dataArr.push(jie);

        let $eleForm = $("<form method='post'></form>");
        $eleForm.attr("action", "downExcel");
        $eleForm.append("<input name='bigTile' type='hidden' value='" + "这就是爱吗" + "'>");
        $eleForm.append("<input name='widthArr' type='hidden' value='" + JSON.stringify(widthArr) + "'>");
        $eleForm.append("<input name='dataArr' type='hidden' value='" + JSON.stringify(dataArr) + "'>");
        $eleForm.append("<input name='titleArr' type='hidden' value='" + JSON.stringify(titleArr) + "'>");
        $eleForm.append("<input name='keyArr' type='hidden' value='" + JSON.stringify(keys) + "'>");
        $(document.body).append($eleForm);
        $eleForm.submit();
        $eleForm.remove();

    })

Controller代码

package com.example.Poi.Controller;

import com.alibaba.fastjson.JSON;
import com.example.swagger_test.util.ExcelLeadUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;

@Controller
public class ExcelController {

    @RequestMapping("downExcel")
    public void downloadExcel(HttpServletResponse response,String bigTile,String widthArr,
                              String dataArr,String titleArr,String keyArr){

        ArrayList widths = JSON.parseObject(widthArr, ArrayList.class);
        ArrayList datas = JSON.parseObject(dataArr, ArrayList.class);
        ArrayList keys = (ArrayList<String>)JSON.parseObject(keyArr, ArrayList.class);
        ArrayList titles = (ArrayList<String>) JSON.parseObject(titleArr, ArrayList.class);

        HSSFWorkbook workbook = ExcelLeadUtil.exportExcel(bigTile, widths, datas, titles,keys);
        // 响应到客户端
        try {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            try {
                bigTile = URLEncoder.encode(bigTile + ".xlsx", "UTF-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + bigTile);
            } catch (UnsupportedEncodingException e) {
                response.setHeader("Content-Disposition", "attachment;filename=error.xlsx");
            }
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

最终效果