Springboot Apple poi Excelを使ってExcelをエクスポートします.

4813 ワード

Aache poi Excelを使ってExcel導出データを実現するツール類1、maven依存を追加します.


    org.apache.poi
    poi
    3.16


    org.apache.poi
    poi-ooxml
    3.16

2、Excel導出ツール類は、07版Excel導出フォーマットが「xlsx」であることをサポートします.

package com.linksfield.topup.utils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

/**
 * @author: songgt
 * @date: 2019-12-19 10:11
 */
public class ExcelUtil {

    /**
     *   resultList  Excel,      xlsx      Excel，  Excel 2007         
     *         ，       60000   ，                     
     * @param response
     * @param fileName
     * @param nameArray
     * @param rtnList
     * @throws Exception
     */
    public static void exportExcel2007(HttpServletResponse response,
                                       String fileName, String[] nameArray, String[] keyArray,
                                       List> rtnList) {
        int maxRow = 60000;
        try{
            OutputStream os = response.getOutputStream();
            response.setHeader("Content-disposition", "attachment; filename="
                    + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xlsx");
            response.setContentType("application/msexcel");
            //          
            int rowAccess = 100;
            SXSSFWorkbook wb = new SXSSFWorkbook(rowAccess);
            int listSize = rtnList.size();
            //     
            int sheetNum = listSize / 60000 + 1;
            //    
            int cellTotal = nameArray.length;
            //   list     
            int listNum = 0;
            for (int i = 0; i < sheetNum; i++) {
                Sheet sh = wb.createSheet(fileName + "sheet" + String.valueOf(i));
                //   SHEET 60000ROW
                for (int rowNum = 0; rowNum <= maxRow; rowNum++) {
                    Row row = sh.createRow(rowNum);
                    //    cellTotal CELL
                    if (rowNum == 0){
                        for (int cellNum = 0; cellNum < cellTotal; cellNum++) {
                            Cell cell = row.createCell(cellNum);
                            cell.setCellValue(nameArray[cellNum]);
                        }
                    }else {
                        //          
                        if (listNum < listSize) {
                            Map map = rtnList.get(listNum);
                            listNum++;
                            for (int cellNum = 0; cellNum < cellTotal; cellNum++) {
                                Cell cell = row.createCell(cellNum);
                                String key = keyArray[cellNum];
                                String value = map.get(key) != null ? map.get(key).toString() : "";
                                cell.setCellValue(value);
                            }
                        } else {
                            break;
                        }
                    }
                    //                   ,     
                    if (rowNum % rowAccess == 0) {
                        ((SXSSFSheet) sh).flushRows();
                    }
                }
            }
            wb.write(os);
            os.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

3、試験Controllerを作成する

/**
 * @author: songgt
 * @date: 2019-12-19 10:20
 */
@RestController
public class ExcelExportController {

    @ApiOperation(value = "    -  excel")
    @GetMapping(path = "/exportTrialUserList")
    public void exportTrialUserList(HttpServletResponse response,@RequestHeader(name = "x-access-token", required = false) String token){
        Map map = new HashMap();
        map.put("a",1);
        map.put("b",2);
        map.put("c",3);
        map.put("d",4);
        List> list = new ArrayList>();
        list.add(map);
        String[] names = {"  1","  2","  3","  4"};
        String[] keys = map.keySet().toArray(new String[map.size()]);
        String fileName = "  Excel";
        ExcelUtil.exportExcel2007(response,fileName,names,keys,list);
    }
}

Element ui tableコンポーネントでslotを使って定義ヘッダからデータを更新できませんでした.

カウンタ