Java中竖线分隔TXT转XLS、Selenium中竖线分隔CSV转Excel的实现问询

Hey there! Let's tackle these two file conversion problems you're working on—both are super common in Java/Selenium workflows, so I've got you covered with step-by-step solutions and code examples.

The most reliable tool for this is Apache POI, the go-to library for Excel manipulation in Java. First, add the required dependencies to your project:

If you're using Maven, include these in your pom.xml:

<dependencies>
    <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>
</dependencies>

Here's the full conversion code with detailed comments:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

public class TxtToXlsConverter {
    public static void main(String[] args) {
        String txtInputPath = "your-input-file.txt";
        String xlsOutputPath = "your-output-file.xlsx";

        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream outputStream = new FileOutputStream(xlsOutputPath);
             BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(txtInputPath), "UTF-8"))) {

            Sheet dataSheet = workbook.createSheet("Converted Data");
            String line;
            int rowIndex = 0;

            while ((line = reader.readLine()) != null) {
                // Split by vertical bar, escape it since it's a regex special character
                // Use -1 to preserve empty fields and avoid data misalignment
                String[] rowData = line.split("\\|", -1);
                Row row = dataSheet.createRow(rowIndex);

                for (int colIndex = 0; colIndex < rowData.length; colIndex++) {
                    Cell cell = row.createCell(colIndex);
                    cell.setCellValue(rowData[colIndex].trim()); // Clean up extra whitespace
                }

                rowIndex++;
            }

            // Auto-adjust column widths for better readability
            for (int i = 0; i < dataSheet.getRow(0).getPhysicalNumberOfCells(); i++) {
                dataSheet.autoSizeColumn(i);
            }

            workbook.write(outputStream);
            System.out.println("TXT to XLS conversion completed successfully!");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Key notes:

• Use split("\\|", -1) instead of plain split("|")—the backslash escapes the vertical bar, and -1 ensures empty fields are retained to prevent data shifting.
• Reading with UTF-8 encoding avoids messy character issues with non-English text.
• autoSizeColumn makes the output Excel file much easier to read.

For your specific CSV with headers PERIOD|EMPLID|EMPL_RCD|HOME HOST|NAME|FIRST_NAME|LAST_NAME|FTE|EMPL_STATUS, combining OpenCSV (for robust CSV parsing) with Apache POI is the way to go. This handles edge cases like quoted fields or inconsistent spacing better than manual splitting.

First, add the OpenCSV dependency to your pom.xml:

<dependency>
    <groupId>com.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>5.6</version>
</dependency>

Here's the tailored code for your use case:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;

import java.io.*;

public class CsvToExcelConverter {
    public static void main(String[] args) {
        String csvInputPath = "your-input-data.csv";
        String excelOutputPath = "employee-data.xlsx";

        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream outputStream = new FileOutputStream(excelOutputPath);
             CSVReader csvReader = new CSVReaderBuilder(new FileReader(csvInputPath))
                     .withSeparator('|') // explicitly set vertical bar as delimiter
                     .withIgnoreQuotations(true) // skip quotation marks if present in fields
                     .build()) {

            Sheet employeeSheet = workbook.createSheet("Employee Records");
            String[] rowData;
            int rowIndex = 0;

            while ((rowData = csvReader.readNext()) != null) {
                Row row = employeeSheet.createRow(rowIndex);
                for (int colIndex = 0; colIndex < rowData.length; colIndex++) {
                    Cell cell = row.createCell(colIndex);
                    cell.setCellValue(rowData[colIndex].trim());
                }
                rowIndex++;
            }

            // Auto-adjust columns to fit content
            for (int i = 0; i < employeeSheet.getRow(0).getPhysicalNumberOfCells(); i++) {
                employeeSheet.autoSizeColumn(i);
            }

            workbook.write(outputStream);
            System.out.println("CSV to Excel conversion done!");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

If you're integrating this into your Selenium workflow, just wrap this logic in a utility method and call it after exporting the CSV file from your test. Common issues to check if your previous code failed:

• Did you forget to set the correct delimiter (vertical bar) instead of the default comma?
• Were empty fields causing data misalignment? Using OpenCSV or split("\\|", -1) fixes this.
• Was there whitespace around fields? The trim() call cleans that up.

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