Java：用Apache事件库解析大Excel，将StringBuilder内容按行写入文本并还原行列

Hey there! Let's work through these two Java tasks together. I'll share working code snippets with explanations so you can implement them smoothly.

This is straightforward with Java's built-in I/O tools, and we'll use try-with-resources to avoid resource leaks (no need to manually close streams!). Assuming your StringBuilder already has lines separated by newline characters (\n), here's a clean implementation:

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;

public class StringBuilderToFile {
    public static void writeStringBuilderToFile(StringBuilder content, String filePath) throws IOException {
        // Try-with-resources auto-closes the writer even if an exception hits
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            writer.write(content.toString());
        }
    }

    public static void main(String[] args) {
        StringBuilder sb = new StringBuilder();
        sb.append("First line of sample content\n");
        sb.append("Second line with test data\n");
        sb.append("Third line to verify output\n");

        try {
            writeStringBuilderToFile(sb, "output.txt");
            System.out.println("File written successfully!");
        } catch (IOException e) {
            System.err.println("Oops, error writing file: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

What's going on here? #

• try-with-resources takes care of closing the BufferedWriter automatically, so you don't have to remember to call .close() (which is easy to forget!).
• If your StringBuilder doesn't have newline characters yet, just add \n each time you append a new line of content.

For large Excel files (especially .xlsx), using Apache POI's event-based (SAX-like) model is critical—it doesn't load the entire workbook into memory, which prevents out-of-memory errors with huge datasets. We'll parse the Excel, capture exactly 24 columns per row, build the content in a StringBuilder, then use the first function to write it to a text file.

First, add these dependencies if you're using Maven (adjust for Gradle as needed):

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

Now the full code:

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class LargeExcelParser {
    private static final int EXPECTED_COLUMNS = 24;
    private final StringBuilder outputContent = new StringBuilder();

    public void parseExcel(String excelFilePath) throws Exception {
        try (OPCPackage pkg = OPCPackage.open(new File(excelFilePath))) {
            XSSFReader reader = new XSSFReader(pkg);
            SharedStringsTable sst = reader.getSharedStringsTable();

            XMLReader parser = XMLReaderFactory.createXMLReader();
            parser.setContentHandler(new XSSFSheetXMLHandler(
                    sst,
                    null,
                    new SheetContentsHandler(),
                    false // Skip formula processing since we want cell values
            ));

            // Parse every sheet in the Excel file
            for (InputStream sheetStream : reader.getSheetsData()) {
                try {
                    parser.parse(new InputSource(sheetStream));
                } finally {
                    sheetStream.close();
                }
            }
        }
    }

    // Custom handler to process rows and cells as we parse them
    private class SheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
        private List<String> currentRowCells = new ArrayList<>(EXPECTED_COLUMNS);

        @Override
        public void startRow(int rowNum) {
            // Reset our list for a new row
            currentRowCells.clear();
        }

        @Override
        public void endRow(int rowNum) {
            // Fill empty columns if the row has fewer than 24 cells (keeps structure consistent)
            while (currentRowCells.size() < EXPECTED_COLUMNS) {
                currentRowCells.add("");
            }
            // Join cells with a tab separator (easy to read, matches Excel's column alignment)
            String rowText = String.join("\t", currentRowCells);
            outputContent.append(rowText).append("\n");
        }

        @Override
        public void cell(String cellReference, String cellValue, XSSFComment comment) {
            // Add the cell value (or empty string if null) to our current row list
            currentRowCells.add(cellValue != null ? cellValue : "");
        }

        // We don't need these methods, so we can leave them empty
        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {}
    }

    public StringBuilder getOutputContent() {
        return outputContent;
    }

    public static void main(String[] args) {
        LargeExcelParser parser = new LargeExcelParser();
        String excelPath = "large_dataset.xlsx";
        String outputPath = "excel_to_text_output.txt";

        try {
            parser.parseExcel(excelPath);
            // Reuse our first function to write the parsed content to file
            StringBuilderToFile.writeStringBuilderToFile(parser.getOutputContent(), outputPath);
            System.out.println("Excel parsed and text file generated successfully!");
        } catch (Exception e) {
            System.err.println("Error processing Excel file: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

Key Details: #

• Memory Efficiency: The event model parses the Excel row by row, so even files with 100k+ rows won't crash your app with memory issues.
• Column Consistency: We ensure every row has exactly 24 columns—if a row is missing cells, we fill in empty strings to match the original Excel structure.
• Separator Choice: We used tabs (\t) to separate columns because it keeps the text file neatly aligned. If you need CSV instead, just change \t to ,.
• Error Handling: The code includes basic error handling, but you can extend it (e.g., skip empty rows, validate cell data types) based on your needs.

内容的提问来源于stack exchange，提问作者Jeet