java POi excel 写入大批量数据

最新推荐文章于 2024-08-16 17:32:00 发布

SimminonGarcia

最新推荐文章于 2024-08-16 17:32:00 发布

阅读量726

点赞数

文章标签： java 大数据

本文介绍了一个使用Java批量向Excel文件中填充大量数据的方法。通过利用Apache POI库，特别是SXSSFWorkbook类来提高处理大规模数据集时的性能。该示例展示了如何创建并写入超过49万行的数据到一个名为ProductMST的工作表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

直接贴代码：

package jp.co.misumi.mdm.batch.common.jobrunner;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Demo16
{

    public static void main(String[] args) throws InvalidFormatException
    {
        FileOutputStream out = null;  
        OPCPackage pkg;
        // TODO Auto-generated method stub
        
        XSSFWorkbook wbTemplate;
        try
        {
            pkg = OPCPackage.open(new File("F:/code/mdm-batch/target/classes/template/excel/TP.xlsx"));
            wbTemplate = new XSSFWorkbook(pkg);
            SXSSFWorkbook writeWB = new SXSSFWorkbook(wbTemplate, 5000, true, true);
            SXSSFSheet writeSheet = writeWB.getSheet("ProductMST");
            // Workbook writeWB = new SXSSFWorkbook(500);    
             //org.apache.poi.ss.usermodel.Sheet writeSheet = writeWB.createSheet("1");  
            System.out.println(String.valueOf(new Date())); 
            try
            {
                Thread.sleep(5000);
            }
            catch (Exception e)
            {
                // TODO: handle exception
            }
            for (int i=101; i<500000;i++) {
                 System.out.println(String.valueOf(i));
                 Row writeRow = writeSheet.createRow(i);        
                 for (int j = 32; j < 100; j++) {    
                     Cell cell = writeRow.createCell(j);
                     //cell.setCellValue("Hello");
                     cell.setCellValue(j+"Hello"+i);
                     cell.setCellValue(j+"Hello");
                    /* CellStyle cellStyle = cell.getCellStyle();
                     cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
                     cellStyle.setBorderTop(CellStyle.BORDER_THIN);
                     cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
                     cellStyle.setBorderRight(CellStyle.BORDER_THIN);
                     cell.setCellStyle(cellStyle);*/
                 } 
             }    
            System.out.println(String.valueOf(new Date())); 
             File file = new File("F:/code/mdm-batch/target/classes/template/excel/6666666666666555.xlsx");
             out = new FileOutputStream(file);
             writeWB.write(out);    
             out.flush();  
             out.close();  
             System.out.println("测试成功！");  
        }
        catch (FileNotFoundException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        } catch (IOException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
    }

}