本文共 6097 字，大约阅读时间需要 20 分钟。
一：分批次将数据写入Excel的优化方案
分批次写入：效率较低，适用于小数据量这种方法效率较低，特别是面对大数据量时表现明显。例如，4万条数据可能需要2分钟左右的时间。这一种方法通常用于小规模的数据处理，但在大规模数据处理时可能显得力不从心。
一次性写入：效率更高，适用于大数据量相比之下，一次性写入的方式效率更高，属于批量处理的优化版本。这种方法可以在较短的时间内处理更大的数据量。例如，4万条数据只需17秒左右。这是一种更高效的数据处理方式，适用于大规模数据的快速处理。
以下是两种方法的实现代码示例
代码示例1：分批次写入
package com.test;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class POIController { /** * 分批次写入数据 * @param args * @throws FileNotFoundException * @throws InvalidFormatException */ public static void main(String[] args) throws FileNotFoundException, InvalidFormatException { long startTime = System.currentTimeMillis(); BufferedOutputStream outPutStream = null; XSSFWorkbook workbook = null; FileInputStream inputStream = null; String filePath = "E:\\txt\\111.xlsx"; try { workbook = getWorkBook(filePath); XSSFSheet sheet = workbook.getSheetAt(0); for (int i = 0; i < 40; i++) { for (int z = 0; z < 1000; z++) { XSSFRow row = sheet.createRow(i * 1000 + z); for (int j = 0; j < 10; j++) { row.createCell(j).setCellValue("你好：" + j); } } // 每次获取新的文件流对象，避免覆盖数据 outPutStream = new BufferedOutputStream(new FileOutputStream(filePath)); workbook.write(outPutStream); } } catch (IOException e) { e.printStackTrace(); } finally { if (outPutStream != null) { try { outPutStream.close(); } catch (IOException e) { e.printStackTrace(); } } if (inputStream != null) { try { inputStream.close(); } catch (IOException e) { e.printStackTrace(); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { e.printStackTrace(); } } } long endTime = System.currentTimeMillis(); System.out.println(endTime - startTime); } /** * 获取Excel工作簿 * @param filePath * @return */ public static XSSFWorkbook getWorkBook(String filePath) { XSSFWorkbook workbook = null; try { File fileXlsxPath = new File(filePath); BufferedOutputStream outPutStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath)); workbook = new XSSFWorkbook(); workbook.createSheet("测试"); workbook.write(outPutStream); } catch (Exception e) { e.printStackTrace(); } return workbook; }} 代码示例2：一次性写入数据
package com.test;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class POIController { /** * 一次性写入数据 * @param args * @throws FileNotFoundException * @throws InvalidFormatException */ public static void main(String[] args) throws FileNotFoundException, InvalidFormatException { long startTime = System.currentTimeMillis(); BufferedOutputStream outPutStream = null; XSSFWorkbook workbook = null; FileInputStream inputStream = null; String filePath = "E:\\txt\\111.xlsx"; try { workbook = getWorkBook(filePath); XSSFSheet sheet = workbook.getSheetAt(0); for (int i = 0; i < 40; i++) { for (int z = 0; z < 1000; z++) { XSSFRow row = sheet.createRow(i * 1000 + z); for (int j = 0; j < 10; j++) { row.createCell(j).setCellValue("你好：" + j); } } } // 获取新的文件流对象，避免覆盖数据 outPutStream = new BufferedOutputStream(new FileOutputStream(filePath)); workbook.write(outPutStream); } catch (IOException e) { e.printStackTrace(); } finally { if (outPutStream != null) { try { outPutStream.close(); } catch (IOException e) { e.printStackTrace(); } } if (inputStream != null) { try { inputStream.close(); } catch (IOException e) { e.printStackTrace(); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { e.printStackTrace(); } } } long endTime = System.currentTimeMillis(); System.out.println(endTime - startTime); } /** * 获取Excel工作簿 * @param filePath * @return */ public static XSSFWorkbook getWorkBook(String filePath) { XSSFWorkbook workbook = null; try { File fileXlsxPath = new File(filePath); BufferedOutputStream outPutStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath)); workbook = new XSSFWorkbook(); workbook.createSheet("测试"); workbook.write(outPutStream); } catch (Exception e) { e.printStackTrace(); } return workbook; }} 通过以上优化方案，可以更高效地完成Excel数据的批量写入任务。分批次写入适用于小规模数据，而一次性写入则更适合大规模数据处理。两种方法都基于Apache POI库，提供了灵活的数据处理能力。
转载地址：http://vuql.baihongyu.com/