java poi-ooxml构造导入数据

在测试过程中经常需要构造excel导入文件，有时数据量较大时，使用excel拖动或填充经常会受到限制，而且速度也慢。这里借助java poi-ooxml生成10000条数据，一次写好代码后，亲测10000条，生成速度5s左右。

一、pom文件导入依赖

         <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
            <exclusions>
                <exclusion>
                    <artifactId>commons-codec</artifactId>
                    <groupId>commons-codec</groupId>
                </exclusion>
            </exclusions>
        </dependency>

二、代码如下

package com.mytest.Learning;

import com.mytest.Dto.User;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * ClassName: UserServiceTest
 * Description:
 *
 * @author Weiwan
 * @date 2022/11/21 18:00
 */
public class UserServiceTest2 {
    @Test
    public void testLogin() throws IOException {
        int size = 10001;
        String[] columnName = {"货主编码", "商品编码", "批次属性号", "源库位编码", "目标库位编码","LPN","跟踪号","箱ID","转移数量",
                "生产日期", "失效日期", "质量状态", "批次号", "入库日期","扩展属性01","扩展属性02","扩展属性03","扩展属性04",
                "扩展属性05", "扩展属性06", "扩展属性07", "扩展属性08", "扩展属性09","扩展属性10","扩展属性11","扩展属性12","转移原因"
                ,"备注",
        };
        Object[][] data = new Object[size][28];
        for (int index=0;index<=10000;index++) {
            data[index][0] = "WWTEST001";
            data[index][1] = "LWSP160546";
            data[index][2] = "LT-00000"+Integer.valueOf( index ).toString();
            data[index][3] = "TESTKW001";
            data[index][8] = "1";
            data[index][11] = "良品";
            data[index][12] = Integer.valueOf( index ).toString();
            data[index][13] = "2023-05-06";
            data[index][26] = "正常";
        }
        XSSFWorkbook xssfWorkbook = generateExcel("测试", null, columnName, data);

        String fileName = "G:/测试/导入模板1.xlsx";
        createNewFile( fileName );
        try(FileOutputStream fos = new FileOutputStream( fileName )){
            xssfWorkbook.write( fos );
        } finally {
            xssfWorkbook.close();
        }
    }

    public static void createNewFile(String fileName) throws IOException {
        File file = new File( fileName );
        File fileParent = file.getParentFile();
        if (!fileParent.exists()){
            fileParent.mkdirs();
        }
        file.createNewFile();
    }

    private static XSSFWorkbook generateExcel(String sheetName, String title, String[] columnName, Object[][] data) {

        XSSFWorkbook workBook = new XSSFWorkbook();

        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        // 如果没有给定sheet名，则默认使用Sheet1
        XSSFSheet sheet;
        if (StringUtils.isNotBlank(sheetName)) {
            sheet = workBook.createSheet(sheetName);
        } else {
            sheet = workBook.createSheet();
        }
        sheet.setColumnWidth(1, 256*12+184);//12为excel列宽，width未转化为poi的列宽
        sheet.setColumnWidth(4, 256*21+184);
//        sheet.autoSizeColumn( 4,true );

        // 构建大标题，可以没有
        XSSFRow headRow = sheet.createRow(0);
        XSSFCell cell = null;

        cell = headRow.createCell(0);
        cell.setCellValue(title);

        //大标题行的偏移
        int offset = 0;
        if (StringUtils.isNotBlank(title)) {
            offset = 1;
        }

        // 构建列标题，不能为空
        headRow = sheet.createRow(offset);
        for (int i = 0; i < columnName.length; i++) {
            cell = headRow.createCell(i);
//            cell.setCellStyle(getStyle2(workBook,true,18));
            cell.setCellValue(columnName[i]);
        }

        //设置跨行,前两个参数是需要合并的开始行数和结束行数,后两个是开始列数和结束列数
//        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnName.length-1));
        //冻结行数,冻结前两行
        sheet.createFreezePane( 0, 2, 0, 2 );

        // 构建表体数据（二维数组），不能为空
        for (int i = 0; i < data.length; i++) {
            headRow = sheet.createRow(++offset);
            for (int j = 0; j < data[0].length; j++) {
                cell = headRow.createCell(j);
//                cell.setCellStyle(getStyle2(workBook,true,15));
                if (data[i][j] instanceof BigDecimal)
                    cell.setCellValue(((BigDecimal) data[i][j]).doubleValue());
                else if (data[i][j] instanceof Double)
                    cell.setCellValue((Double) data[i][j]);
                else if (data[i][j] instanceof Long)
                    cell.setCellValue((Long) data[i][j]);
                else if (data[i][j] instanceof Integer)
                    cell.setCellValue((Integer) data[i][j]);
                else if (data[i][j] instanceof Boolean)
                    cell.setCellValue((Boolean) data[i][j]);
                else if (data[i][j] instanceof Date)
                    cell.setCellValue((Date) data[i][j]);
                else
                    cell.setCellValue((String) data[i][j]);
            }
        }
        return workBook;
    }
}

三、运行截图