Using Apache POI and SXSSFWorkbook to Export Large Excel Files in Java

Hello everyone, recently ChatGPT has become very popular, so I tried using it to automatically write code out of curiosity.

First, a simple program is written:

import java.io.FileOutputStream;
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.xssf.usermodel.XSSFWorkbook;

public class ExcelExporter {
    public static void exportData(List
> data, String filePath) throws IOException {
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        
        // 创建工作表
        Sheet sheet = workbook.createSheet();
        
        // 遍历数据，并将其写入工作表
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(i);
            List
rowData = data.get(i);
            
            for (int j = 0; j < rowData.size(); j++) {
                row.createCell(j).setCellValue(rowData.get(j));
            }
        }
        
        // 将工作簿写入文件
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);
        out.close();
        workbook.close();
    }
}

Usage example:

List
> data = new ArrayList<>();
data.add(Arrays.asList("a", "b", "c"));
data.add(Arrays.asList("1", "2", "3"));

ExcelExporter.exportData(data, "data.xlsx");

This code creates an Excel file named "data.xlsx" and writes two rows with three columns of data. Note that this is only an example and may not work in all cases. If you need help, provide more information and I will try to assist.

It is clear that the generated code does not fully meet my requirements, and XSSFWorkbook may cause memory overflow with large data sets, so I asked further.

To handle large data volumes, I switched to SXSSFWorkbook:

import java.io.FileOutputStream;
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.xssf.streaming.SXSSFWorkbook;

public class ExcelExporter {
    public static void exportData(List
> data, String filePath) throws IOException {
        // 创建工作簿
        Workbook workbook = new SXSSFWorkbook();
        
        // 创建工作表
        Sheet sheet = workbook.createSheet();
        
        // 遍历数据，并将其写入工作表
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(i);
            List
rowData = data.get(i);
            
            for (int j = 0; j < rowData.size(); j++) {
                row.createCell(j).setCellValue(rowData.get(j));
            }
        }
        
        // 将工作簿写入文件
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);
        out.close();
        workbook.close();
    }
}

With this change, the exported Excel file will not cause memory overflow. Again, this is a sample and may not work in all scenarios; provide more details for further assistance.

During the interaction, I improved the solution by using SXSSFWorkbook to handle large data sets, showing how ChatGPT can be a powerful aid compared to earlier AI chat tools.

Question: Will OpenAI's ChatGPT replace programmers?

I believe it will not. While language models can help generate code quickly, they cannot fully replace programmers because they lack the deep understanding and problem‑solving skills that developers bring.

Moreover, language models cannot comprehend the purpose behind code or provide effective guidance, so programmers remain essential for designing and implementing solutions.

In summary, language models can assist programmers but cannot replace them.

Thank you for reading, hope this helps :)

Source: blog.csdn.net/xc2011/article/details/128246980