POI导出Excel

　　前言

　　数据报表是许多项目都有的模块，一般都是导出Excel或者PDF，这里记录下我在项目里用POI导出Excel。项目中，我需要根据页面jqgrid的机架查询条件导出对应的机架数据，jqgrid是分页的，但导出是要导出所有。

　　POI

　　Apache POI - the Java API for Microsoft Documents，官网：http://poi.apache.org/

　　代码编写

　　maven引如POI

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
</dependency>

　　或者

<dependency><!--Excel工具类（Easy POI）-->
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency><!--Excel工具类（Easy POI）-->
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency><!--Excel工具类（Easy POI）-->
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.2.0</version>
</dependency>

　　html、js调用

<button type="button" class="btn btn-default fa table-download float-right" onclick="exportRackExcel();">导出</button>

//导出excel
function exportRackExcel() {
    //获取当前jqGrid分页参数
    var postData = $("#rack").jqGrid("getGridParam", "postData");
    postData.page = 1;
    postData.rows = 999999999;//设置每页9亿条记录（相当于无穷大，查询所有）
    //ajax不支持Excel类型，使用location.href或者表单提交
    //window.location.href，get提交，数据会暴露在URL，相对不安全
    //创建临时的、隐藏的form表单，post提交，数据在请求体里，相对安全
    var $form = $(document.createElement('form')).css({display: 'none'}).attr("method", "POST").attr("action", ctx + "/excel");
    for (var key in postData) {
        var $input = $(document.createElement('input')).attr('name', key).val(postData[key]);
        $form.append($input);
    }
    $("body").append($form);
    $form.submit();
    //过河拆桥，提交完成后remove掉
    $form.remove();
}

　　

　　controller

    /**
     * 根据当前jqGrid分页情况，创建并导出Excel文件
     *
     * @param entity 机架实体，用来接收查询条件
     * @return ResponseEntity
     */
    @PostMapping("/excel")
    public ResponseEntity createExcel(RackVo entity) {
        //Excel对应的columnNames列名集合 { key,label }
        String[][] excelMap = {
                {"no", "Rack Code"},
                {"rackName", "Rack Name"},
                {"roomName", "Room"},
                {"idc", "IDC Center"},
                {"clientName", "Customer"},
                {"rackTypeName", "Type"},
                {"existentialMode", "Existential Mode"},
                {"maxPower", "Maximum Power(KVA)"},
                {"status", "Status"},
                {"administrate", "Administrate"},
        };
        return DownloadUtil.download(ExportExcelUtil.createExcel("Rack Management", excelMap, rackService.createExcel(entity).getData()).getData(), "机架数据报表");
    }

　　两个工具类：导出Excel工具类 ExportExcelUtil，下载工具类 DownloadUtil

/**
 * java POI 导出Excel表工具类
 */
public class ExportExcelUtil {

    //禁止实例化
    private ExportExcelUtil() {
    }

    /**
     * 只支持一级表头
     *
     * @param titleName   表标题
     * @param columnNames 列名集合，key是用来设置填充数据时对应单元格的值，label就是对应的列名，生成Excel表时，
     *                    第一维数组下标0对应值为Excel表最左边的列的列名 例：{ { key,label },{ key,label } }
     * @param dataLists   数据集合，key对应的是列名集合的key，value是要填充到单元格的值 例：ArrayList<HashMap<String key, String vaule>>
     * @return ResultModel<Workbook>
     */
    public static ResultModel<Workbook> createExcel(String titleName, String[][] columnNames, ArrayList<HashMap<String, String>> dataLists) {
      
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet(titleName);//设置表单名

        //1、标题名
        //创建标题行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1 = sheet.createRow(0);

        //标题的字体
        HSSFFont font1 = wb.createFont();
        font1.setFontHeightInPoints((short) 12);
        font1.setFontName("黑体");

        //标题的样式
        HSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

        // 把字体 应用到当前样式
        style1.setFont(font1);

        //自动换行
        style1.setWrapText(true);

        //自定义填充颜色（天空蓝）
        style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style1.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());

        // 设置边框
        style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

        createCell(row1, 0, style1, titleName);
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnNames.length - 1));

        //2、列名
        //创建列名行
        //列名的字体
        HSSFFont font2 = wb.createFont();
        font2.setFontHeightInPoints((short) 12);
        font2.setFontName("新宋体");

        //列名的样式
        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

        // 把字体 应用到当前样式
        style2.setFont(font2);

        //自动换行
        style2.setWrapText(true);

        //自定义填充颜色（浅蓝色）
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());

        // 设置边框
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFRow row2 = sheet.createRow(1);
        for (int i = 0; i < columnNames.length; i++) {
            //单元格宽度
            sheet.setColumnWidth(i, 20 * 256);
            createCell(row2, i, style2, columnNames[i][1]);//例：[[key,label],[key,label]] 取label
        }

        //3、填充数据
        //内容的字体
        HSSFFont font3 = wb.createFont();
        font3.setFontHeightInPoints((short) 12);
        font3.setFontName("新宋体");

        //内容的样式
        HSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

        // 把字体 应用到当前样式
        style3.setFont(font3);

        //自动换行
        style3.setWrapText(true);

        //默认无填充
        style3.setFillPattern(FillPatternType.NO_FILL);
        style3.setFillForegroundColor(IndexedColors.RED.getIndex());

        // 设置边框
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style3.setBorderTop(HSSFCellStyle.BORDER_THIN);

        int index = 2;//标题行、列名行，所以数据行默认从第三行开始
        for (HashMap<String, String> map : dataLists) {
            //创建内容行
            HSSFRow row3 = sheet.createRow(index);
            for (int i = 0; i < columnNames.length; i++) {
                String val = map.get(columnNames[i][0]);
                createCell(row3, i, style3, val == null ? "" : val);//例：[[key,label],[key,label]] 取key
            }
            index++;
        }

        return ResultModel.of(wb);
    }

    /**
     * 创建一个单元格
     *
     * @param row       行
     * @param column    列
     * @param cellStyle 单元格样式
     * @param text      值
     */
    private static void createCell(Row row, int column, CellStyle cellStyle, String text) {
        Cell cell = row.createCell(column);  // 创建单元格
        cell.setCellValue(text);  // 设置值
        cell.setCellStyle(cellStyle); // 设置单元格样式
    }
}

/**
 * 文件下载工具类
 */
public class DownloadUtil{
/**
     * 快速下载
     */
    public static ResponseEntity download(byte[] fileBytes, String fileName) {
        //设置文件
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.setContentDispositionFormData("attachment", new String(fileName.getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
        //下载文件
        return new ResponseEntity<>(fileBytes, headers, HttpStatus.CREATED);
    }

    /**
     * 快速下载
     */
    public static ResponseEntity download(File file) {

        return download(getByteArray(file), file.getName());
    }

    /**
     * 快速下载
     */
    public static ResponseEntity download(Workbook workbook, String fileName) {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            fileName = fileName + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xls";
            workbook.write(outputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return download(outputStream.toByteArray(), fileName);
    }

    //获取文件的字节数组
    private static byte[] getByteArray(File file) {
        if (!file.exists()) {
            throw new RuntimeException("File Not Found：" + file.getPath());
        }
        ByteArrayOutputStream bos = new ByteArrayOutputStream((int) file.length());
        BufferedInputStream in = null;
        try {
            in = new BufferedInputStream(new FileInputStream(file));
            int buf_size = 1024;
            byte[] buffer = new byte[buf_size];
            int len;
            while (-1 != (len = in.read(buffer, 0, buf_size))) {
                bos.write(buffer, 0, len);
            }
            return bos.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            try {
                assert in != null;
                in.close();
                bos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    //获取文件名后缀
    private static String getSuffix(String fileName) {
        int lastPointIndex = fileName.lastIndexOf(".");
        if (StringUtils.isEmpty(fileName) || lastPointIndex == -1) {
            return null;
        }
        return fileName.substring(lastPointIndex + 1);
    }
}

　　获取封装数据的service层 createExcel，直接到取page分页方法，遍历机架数据集合，设置Map<key,value>，add到list<Map>中，最后将封装好的数据return回controller，传入工具类，最后下载。

    /**
     * 根据当前jqGrid分页情况，创建并导出Excel文件
     *
     * @param entity 查询条件
     * @return 封装好的数据集合
     */
    @Override
    public ResultModel<ArrayList<HashMap<String, String>>> createExcel(RackVo entity) {
        ArrayList<HashMap<String, String>> dataLists = new ArrayList<HashMap<String, String>>();
        //直接调page分页方法，获取当前jqGrid分页条件对应的数据集合，
        ResultModel<PageInfo<RackVo>> rm = page(entity);
        if (rm.isFlag()) {
            List<RackVo> rackVoList = rm.getData().getRows();
            for (RackVo rackVo : rackVoList) {
                HashMap<String, String> map = new HashMap<String, String>(16);
                map.put("no", rackVo.getNo() != null ? rackVo.getNo() : "");
                map.put("rackName", rackVo.getName() != null ? rackVo.getName() : "");
                map.put("roomName", rackVo.getRoom() != null ? rackVo.getRoom().getRoomname() : "");
                map.put("idc", rackVo.getOrg() != null ? rackVo.getOrg().getOrgName() : "");
                map.put("clientName", rackVo.getCustomer() != null ? rackVo.getCustomer().getClientname() : "");
                map.put("rackTypeName", rackVo.getRacktype() != null ? rackVo.getRacktype().getName() : "");
                map.put("existentialMode", "1".equals(rackVo.getExistentialMode()) ? "Physical" : "Virtual");
                map.put("maxPower", rackVo.getMaxpower() != null ? rackVo.getMaxpower() : "");
                String status = rackVo.getServiceStatus();
                switch (status != null ? status : "") {
                    case "1":
                        status = "Idle";
                        break;
                    case "2":
                        status = "Reserved";
                        break;
                    case "3":
                        status = "Occupied";
                        break;
                    default:
                        status = "";
                        break;
                }
                map.put("status", status);
                String administrate = rackVo.getAdministrate();
                switch (administrate != null ? administrate : "") {
                    case "R":
                        administrate = "Cust Own";
                        break;
                    case "U":
                        administrate = "CTG Own";
                        break;
                    default:
                        administrate = "";
                        break;
                }
                map.put("administrate", administrate);
                dataLists.add(map);
            }
        }
        return ResultModel.of(dataLists);
    }

　　效果

　　从开发阶段到测试阶段，导了无数次，没毛病

　　小升级

　　excelMap，Excel对应的columnNames列名集合 { key,label }，可以不用再controller设置了，直接从页面jqgrid抓取，传入controller就行（滑稽脸~）

//获取jqgrid头部标题tr，有多少个tr就有多少级标题
var thead_tr = $(".ui-jqgrid-htable").find("tr.ui-jqgrid-labels");

//遍历thead_tr找出每一个标题，并保存到对象中
var titles = [];
thead_tr.each(function(index_tr,element_tr){
    titles.push([]);
    $(element_tr).find("th").each(function(index_th,element_th){
        //内容
        var label = $(element_th).text();        
        
        //所占行 rowspan 默认1
        var rowspan = $(element_th).attr("rowspan") || 1;
        
        //所占列 colspan 默认1
        var colspan = $(element_th).attr("colspan") || 1;
        
        //键
        var key = $(element_th).attr("id");
        key = key.substring(key.lastIndexOf("_")+1,key.length);
        
        if(label){
            titles[index_tr].push({
                label:label,
                key:key,
                rowspan:rowspan,
                colspan:colspan,
            });
        }
    });
});
//JSON.stringify(titles)
console.log(titles);