POIUtils 导出 poi Test 100w 600w 条数据 读取模板，填写数据

 依赖：

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.51</version>
        </dependency>
        <!-- ############ poi ############## -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>

 

 //

java用POI设置Excel的列宽

HSSFSheet.setColumnWidth(int columnIndex, int width);
ｅｇ:

sheet.setColumnWidth(0, 252*width+323);//width=35

 

PoiExportUtils:

package com.icil.esolution.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @ClassName: PoiExportUtils
 * @Description: use export excel , some common code
 * @Author: Sea
 * @Date: 15 Oct 2018 2:26:38 PM
 * @Copyright: 2018 ICIL All rights reserved.
 */
public class PoiExportUtils {

    private static String STANDARD_TIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
    public Workbook workbook = new XSSFWorkbook();
    DataFormat format = null;

    {
        format = workbook.createDataFormat();
    }

    public Sheet createXSheet(String sheetName) {
        
        // 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称
        Sheet sheet = null;
        if (StringUtils.isNotBlank(sheetName)) {
            sheet = workbook.createSheet(sheetName);
        } else {
            workbook.createSheet();
        }
        //Freeze the title row
                /**
                 *  cellNum:表示要冻结的列数；
                    rowNum:表示要冻结的行数；
                    firstCellNum:表示被固定列右边第一列的列号；
                    firstRollNum :表示被固定行下边第一列的行号;
                 */
        sheet.createFreezePane( 0, 1, 0, 1 ); 
                
        return sheet;

    }

    public CellStyle getTitleCellStyle() {
        // 用于格式化单元格的数据
//        DataFormat format = workbook.createDataFormat();
        // 设置字体
        Font font = workbook.createFont();
//      font.setFontHeightInPoints((short) 20); // 字体高度
//      font.setColor(Font.COLOR_RED); // 字体颜色
        font.setFontName("黑体"); // 字体
        font.setBold(true); // 加粗
//      font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
      font.setItalic(true); // 是否使用斜体
      font.setStrikeout(true); //是否使用划线
        // 设置单元格类型
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        titleCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        titleCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        titleCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
//      titleCellStyle.setFillForegroundColor(HSSFColor.GREEN.index);    //
//      titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //填充形式
        titleCellStyle.setFont(font);
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局：居中
        titleCellStyle.setWrapText(true);

        return titleCellStyle;
    }

    public CellStyle getDateCellStyle() {
        CellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setDataFormat(format.getFormat(STANDARD_TIME_FORMAT));
        return cellStyle1;
    }
    /**
     * @ such as  0.000  |  yyyy-MM-dd hh:mm:ss
     * @param formats
     * @return
     */
    public CellStyle getDataCellStyle(String formats) {
        CellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setDataFormat(format.getFormat(formats));
        return cellStyle1;
    }

}

 

 

POIUtils

package com.icil.report.utils;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
/**
 * *************************************************************************
 * <PRE>
 *  @ClassName:    : POIUtils 
 *
 *  @Description:    : 
 *
 *  @Creation Date   : 8 May 2019 1:58:29 PM
 *
 *  @Author          :  Sea
 *  
 *
 * </PRE>
 **************************************************************************
 */
public class POIUtils {

    
    public static  CellStyle getTitleCellStyle(Workbook workbook) {
        // 用于格式化单元格的数据
//        DataFormat format = workbook.createDataFormat();
        // 设置字体
        Font font = workbook.createFont();
//      font.setFontHeightInPoints((short) 20); // 字体高度
//      font.setColor(Font.COLOR_RED); // 字体颜色
        font.setFontName("黑体"); // 字体
        font.setBold(true); // 加粗
//      font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
        font.setItalic(true); // 是否使用斜体
//        font.setStrikeout(true); //是否使用划线
        // 设置单元格类型
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        titleCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        titleCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        titleCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
//      titleCellStyle.setFillForegroundColor(HSSFColor.GREEN.index);    //
//      titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //填充形式
        titleCellStyle.setFont(font);
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局：居中
        titleCellStyle.setWrapText(true);

        return titleCellStyle;
    }
    
    
    /**
     * @font "黑体" "加粗" “斜体”
     * @param workbook
     * @return
     */
    public static  CellStyle getFontStyle(Workbook workbook,boolean isItalic) {
        // 设置字体
        Font font = workbook.createFont();
//      font.setFontHeightInPoints((short) 20); // 字体高度
//      font.setColor(Font.COLOR_RED); // 字体颜色
        font.setFontName("黑体"); // 字体
        font.setBold(true); // 加粗
//      font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
        font.setItalic(true); // 是否使用斜体
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setFont(font);
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局：居中
        titleCellStyle.setWrapText(true);

        return titleCellStyle;
    }
    
    
    /**
     * @param sheet
     * @param rownum
     * @param cellColNum
     * @param cellValue
     * @param cellstyle
     */
        public static void  setCellValue(SXSSFSheet sheet, int rownum, int cellColNum, String cellValue,
                CellStyle cellstyle) {
            
            SXSSFRow row = sheet.getRow(rownum);
            if(null==sheet.getRow(rownum)){
                row= sheet.createRow(rownum);
            }
            SXSSFCell cell= row.getCell(cellColNum);
             if(null==row.getCell(cellColNum)){
                 cell = row.createCell(cellColNum);
             }
            cell.setCellStyle(cellstyle);
            cell.setCellValue(cellValue);
        }

    
    
}

 

 

 test

package com.sea.shan.poi;

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import com.sea.shan.utils.POIUtils;
import com.sea.shan.utils.PoiExportUtils;

public class POIUtilsTest {

    @Test
    public void readExcel() throws Exception {
        Workbook workBook = POIUtils.getWorkBook("/home/sea/Desktop/Test/airline-airport-country-code.xlsx");

        Sheet sheetAt0 = workBook.getSheetAt(0);
        int lastRowNum = sheetAt0.getLastRowNum();

        for (int i = 1; i <= lastRowNum; i++) {
            // get per row
            Row row = sheetAt0.getRow(i);

            if (row == null) {
                continue;
            }

            // String cellValue0 = POIUtils.getCellValue(row.getCell(0));
            // String cellValue1 = POIUtils.getCellValue(row.getCell(1));
            // String cellValue0 = POIUtils.getCellValues(row.getCell(0));
            // String cellValue1 = POIUtils.getCellValues(row.getCell(1));
            String cellValue0 = new DataFormatter().formatCellValue(row.getCell(0));

            String cellValue1 = new DataFormatter().formatCellValue(row.getCell(1));

            System.err.println(cellValue0 + "=" + cellValue1);

        }
    }

    
    
    
    @Test
    public void writeExcel() throws Exception {

        String sheetName = "Inventory";
        PoiExportUtils poiExportUtils = new PoiExportUtils();
        Sheet sheet = poiExportUtils.createXSheet(sheetName);
        // 2. set title //"seqId","partNo","partDesc","qtyInv","storeInDtLoc"
        String[] title = { "商品編號 ", " 商品描述   ", "  數量       ", " 數量單位  ", "入庫時間 " };
        // set order by
        sheet.setAutoFilter(CellRangeAddress.valueOf("A1:E1"));
        // set content
        for (int contentColumn = 0; contentColumn <= 100; contentColumn++) {

            Row contentRow = sheet.createRow(contentColumn);
            // set title
            sheet.autoSizeColumn((short) contentColumn); // 自动调整该列的宽度
            if (contentColumn == 0) {
                for (int titleColumn = 0; titleColumn < title.length; titleColumn++) {
                    Cell titleCell = contentRow.createCell(titleColumn);
                    titleCell.setCellStyle(poiExportUtils.getTitleCellStyle());
                    titleCell.setCellValue(title[titleColumn]);
                }
                continue;
            }
            // set content body
            int i = 0;
            contentRow.createCell(i++).setCellValue("cell" + i);
            contentRow.createCell(i++).setCellValue("cell" + i++);
            Cell cell2 = contentRow.createCell(i++);
            cell2.setCellValue("cell" + i++);
            contentRow.createCell(i++).setCellValue("cell" + i++);
            contentRow.createCell(i++).setCellValue("cell" + i++);
        }
        Workbook workbook = poiExportUtils.workbook;
        // 保存
        String filename = "/home/sea/Desktop/workbook0oo1.xls";
        if (workbook instanceof XSSFWorkbook) {
            filename = filename + "x";
        }
        FileOutputStream out = new FileOutputStream(filename);
        workbook.write(out);
        out.close();

    }

    
    
    /**
     * test 导出大量的数据
     * @throws Exception
     */
    @Test
    public void testWriteExcel() throws Exception {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
//        Workbook workbook = new XSSFWorkbook(5000);
        
        long start = System.currentTimeMillis();
        SXSSFWorkbook workbook = new SXSSFWorkbook(10000);//内存中保留 10000 条数据，以免内存溢出，其余写入 硬盘     
        String sheetName = "test";
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        SXSSFSheet sheet = workbook.createSheet(sheetName);
        Sheet sheet1 = workbook.createSheet("sa1");
        Sheet sheet2 = workbook.createSheet("sa2");
        Sheet sheet3 = workbook.createSheet("sa3");
        // Freeze the title row
        /**
         * cellNum:表示要冻结的列数； rowNum:表示要冻结的行数； firstCellNum:表示被固定列右边第一列的列号；
         * firstRollNum :表示被固定行下边第一列的行号;
         */
        sheet.createFreezePane(0, 1, 0, 1);
        sheet.setAutoFilter(CellRangeAddress.valueOf("A1:H1"));

        String[] title = { "商品編號 ", " 商品描述", "  數量", " 數量單位  ", "入庫時間 " };
        
        // set content
        for (int contentColumn = 0; contentColumn <= 1040000; contentColumn++) 
        {
                Row contentRow = sheet.createRow(contentColumn);
//                sheet.autoSizeColumn((short) contentColumn); // 自动调整该列的宽度
    
                // ################# set title ################
                if (contentColumn == 0) {
                    for (int titleColumn = 0; titleColumn < title.length; titleColumn++) {
                        Cell titleCell = contentRow.createCell(titleColumn);
                        titleCell.setCellStyle(getTitleCellStyle(workbook));
                        titleCell.setCellValue(title[titleColumn]);
                    }
                    continue;
                }
                // ################# set title end ################
            
                
                //********************* set body content **************************************
                for (int titleColumn = 0; titleColumn < title.length+10; titleColumn++) {
                contentRow.createCell(titleColumn).setCellValue("cell" + contentColumn);
                }
                //********************* set body content **************************************
        }
        
        FileOutputStream out = new FileOutputStream("/home/sea/Desktop/seatest.xlsx");
        workbook.write(out);

        System.out.println("total cost time:"+(System.currentTimeMillis()-start));
    }
    
    
    
    
    
    
    @Test
    public void testWriteExcel01() throws Exception {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
//        Workbook workbook = new XSSFWorkbook(5000);
        
        long start = System.currentTimeMillis();
        SXSSFWorkbook workbook = new SXSSFWorkbook(10000);//内存中保留 10000 条数据，以免内存溢出，其余写入 硬盘     
        String sheetName = "test";
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet

        String[] title = { "商品編號 ", " 商品描述", "  數量", " 數量單位  ", "入庫時間 " };
        
        
        for(int i=0;i<6;i++)
        {
             SXSSFSheet sheet =workbook.createSheet(sheetName+i);;
             // Freeze the title row
             /**
              * cellNum:表示要冻结的列数； rowNum:表示要冻结的行数； firstCellNum:表示被固定列右边第一列的列号；
              * firstRollNum :表示被固定行下边第一列的行号;
              */
             sheet.createFreezePane(0, 1, 0, 1);
             sheet.setAutoFilter(CellRangeAddress.valueOf("A1:H1"));
        
                                // set content
                                for (int contentColumn = 0; contentColumn <= 1040000; contentColumn++) 
                                {
                                        Row contentRow = sheet.createRow(contentColumn);
                        //                sheet.autoSizeColumn((short) contentColumn); // 自动调整该列的宽度
                            
                                        // ################# set title ################
                                        if (contentColumn == 0) {
                                            for (int titleColumn = 0; titleColumn < title.length; titleColumn++) {
                                                Cell titleCell = contentRow.createCell(titleColumn);
                                                titleCell.setCellStyle(getTitleCellStyle(workbook));
                                                titleCell.setCellValue(title[titleColumn]);
                                            }
                                            continue;
                                        }
                                        // ################# set title end ################
                                    
                                        
                                        //********************* set body content **************************************
                                        for (int titleColumn = 0; titleColumn < title.length+10; titleColumn++) {
                                        contentRow.createCell(titleColumn).setCellValue("cell" + contentColumn);
                                        }
                                        //********************* set body content **************************************
                                }
        }                        
        
        FileOutputStream out = new FileOutputStream("/home/sea/Desktop/seatest.xlsx");
        workbook.write(out);

        System.out.println("total cost time:"+(System.currentTimeMillis()-start));
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    public CellStyle getTitleCellStyle(Workbook workbook) {
        // 用于格式化单元格的数据
//        DataFormat format = workbook.createDataFormat();
        // 设置字体
        Font font = workbook.createFont();
//      font.setFontHeightInPoints((short) 20); // 字体高度
//      font.setColor(Font.COLOR_RED); // 字体颜色
        font.setFontName("黑体"); // 字体
        font.setBold(true); // 加粗
//      font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
        font.setItalic(true); // 是否使用斜体
//        font.setStrikeout(true); //是否使用划线
        // 设置单元格类型
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        titleCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        titleCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        titleCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
//      titleCellStyle.setFillForegroundColor(HSSFColor.GREEN.index);    //
//      titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //填充形式
        titleCellStyle.setFont(font);
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局：居中
        titleCellStyle.setWrapText(true);

        return titleCellStyle;
    }

}

 

 

读取模板，填写数据

    public XSSFWorkbook genManifestReport(List<SmallPkgDetailOdsDO>  smallPkgs) throws Exception
    {
        InputStream ipts = this.getClass().getClassLoader().getResourceAsStream("template\\Sea.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(ipts);
        XSSFSheet sheet = workbook.getSheetAt(0);
        //set value  for  3 行 4 列  date
        sheet.getRow(2).getCell(3).setCellValue(DateFormatUtils.format(System.currentTimeMillis(),TIME_PATTERN, Locale.CHINA));
        int index = 0;
        for (int line = 6; line < smallPkgs.size()+6; line++)
        {
            Row contentRow = sheet.createRow(line);
//            sheet.autoSizeColumn((short) line); // 自动调整该列的宽度
            for (int col = 0; col <10 ; col++) {
                contentRow.createCell(col).setCellValue(col+line);
            }
        }
//        FileOutputStream out = new FileOutputStream("seatest1.xlsx");
//        workbook.write(out);
        return  workbook;
    }

 

controller:

    @GetMapping("/seaReport")
    public ResponseEntity getSeaReportIds(@RequestParam  List<String> bagIds, HttpServletRequest request, HttpServletResponse response) throws Exception{

        JSONObject result = seaReportHandler.getReportByBagIds(bagIds);
        if((ResponseCode.SUCCESS.getCode()+"").equalsIgnoreCase(result.getString(ResponseUtil.RESPONSE_CODE))){
            XSSFWorkbook workbook = (XSSFWorkbook)result.get(ResponseUtil.RESPONSE_DATA);
            OutputStream output=response.getOutputStream();
            response.reset();
            String fileName="sea_Report" + DateUtils.formatDate(new Date(),DateUtils.STANDARD_NO_BLANK);
            response.setHeader("Content-disposition", "attachment; filename="+fileName+".xlsx");
            response.setContentType("application/msexcel");
            workbook.write(output);
            return  ResponseEntity.status(200).body("");
        }else
        {  // 失败，响应失败原因
           return ResponseEntity.status(Integer.valueOf(result.getString(ResponseUtil.RESPONSE_CODE))).body(result);
        }

    }

 

 

 

通过Maping.json  填充数据：

    public XSSFWorkbook genRpt(List<JSONObject> datas) throws Exception {
        //List<JSONObject> dataFlat = flatmapData(datas);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("sea");
        CellStyle boldCellStyle = POIUtils.getBoldCellStyle(workbook);
        //title
        for (int col = 0; col < title.length; col++) {
            sheet.autoSizeColumn((short) 0); // 自动调整该列的宽度
            // contentRow.createCell(col).setCellValue(title[col]);
            POIUtils.setCellValue(sheet,0,col,title[col],boldCellStyle);
        }
        // content
        for (int line = 0; line < datas.size(); line++)
        {
            Row contentRow = sheet.createRow(line+1);
            setValueByMap(datas.get(line),contentRow,title.length);
        }
//        FileOutputStream out = new FileOutputStream("seaRpt.xlsx");
//        workbook.write(out);
        return workbook;
    }

  

 

    private  void  setValueByMap(JSONObject data, Row contentRow,int colMax){
        String OR = "\\|";
        String AND = "&";
        String EQ = "==";
        JSONObject mapping = getMapping("templates/lazadaMainfestNewMapping.json");
        //set value
        for(int col=0;col<colMax;col++)
        {
            String key = mapping.getJSONObject(col + "").keySet().stream().findFirst().orElseGet(() -> " ");
            String value = "";
            //check default value
            if((key+"").contains(EQ))
            {
                value = key.replaceAll(EQ,"");
            }
            // 可能需要匹配多个字段的值, 多个字段使用   or | 分割
            else if((key+"").contains("|"))
            {
                for (String k : key.split(OR)) {
                    Object  vobj = JSONPath.eval(data, "$."+k.trim());
                    value = (vobj == null ? value : (vobj + ""));
                }
            }else
            { //多个字段使用 & 分割
                for (String k : key.split(AND)) {
                    Object  vobj = JSONPath.eval(data, "$."+k.trim());
                    value += (vobj == null ? "" : vobj + "") + " ";
                }
            }
            contentRow.createCell(col).setCellValue(value);
        }
      
    }

 

 

 

seaNewMapping.json

mapping :

{
  "注释": {
    "说明1": " 数字表示 excel 的 列数 ,   key: 我们DO字段(lazada small detail),  value: excel 中具体的列的名字 (没有用到)",
    "说明2": " 如果一个字段需要多个字段匹配  用&连接 eg: sender.address1&sender.address2: SHRP ADD 1 ",
    "说明": " 如果需要给定默认值, 用 ==default_value    对于 or 用 |连接 ， 默认后面的如果有值会覆盖前面的  "
  },
  "0": {
    "orderCode": " xpl_code"
  },
  "1": {
    "id": "ID  == index ++ "
  },
  "2": {
    "trackingNumber": "HAWB NO"
  },
  "3": {
    "package.packageCode": "pkg ORD NO"
  },
  "4": {
    "package.packageOrd": "REF_ORD_NO"
  }}

 

 

    private static JSONObject getMapping(String path)
    {
        try
        {
            InputStream resourceAsStream = LAZADANewManifestReportHandler.class.getClassLoader().getResourceAsStream(path);
            String str = IOUtils.toString(resourceAsStream, "utf-8");
            return JSON.parseObject(str);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return  new JSONObject();
    }