如何将 xlsx 文件转换为 csv? [英] How to convert xlsx file to csv?

问题描述

目前我正在使用以下代码使用 Java 将 XLSX 文件转换为 CSV.我需要一个更快的解决方案，因为这太慢了.

公共类Test1 {静态无效转换(文件输入文件，文件输出文件){尝试 {FileOutputStream fos = new FileOutputStream(outputFile);//获取 XLSX 文件的工作簿对象XSSFWorkbook wBook = 新的 XSSFWorkbook(新的 FileInputStream(inputFile));//从工作簿中获取第一张工作表XSSFSheet sheet = wBook.getSheetAt(0);行行；细胞细胞；//遍历第一张表的每一行迭代器<行>rowIterator = sheet.iterator();而 (rowIterator.hasNext()) {row = rowIterator.next();//对于每一行，遍历每一列迭代器<Cell>cellIterator = row.cellIterator();而(cellIterator.hasNext()){cell = cellIterator.next();开关(cell.getCellType()){案例 Cell.CELL_TYPE_BOOLEAN:data.append(cell.getBooleanCellValue() + ",");休息;案例 Cell.CELL_TYPE_NUMERIC:data.append(cell.getNumericCellValue() + ",");休息;案例 Cell.CELL_TYPE_STRING:data.append(cell.getStringCellValue() + ",");休息;案例 Cell.CELL_TYPE_BLANK:data.append("" + ",");休息;默认:data.append(cell + ",");}}}fos.write(data.toString().getBytes());fos.close();} 捕捉(异常ioe){ioe.printStackTrace();}}//测试应用程序公共静态无效主(字符串 [] args){//从桌面读取文件File inputFile = new File("D:\\Test.xlsx");//将excel数据写入csvFile outputFile = new File("D:\\Test1.csv");转换(输入文件，输出文件)；}}

解决方案

文本提取器 将转储整个工作簿的 TSV.性能取决于所选的实现方式和您的内存可用性.

然后您可以将其传送到 CSVPrinter 以获得正确的 CSV 输出.我认为 Excel 单元格不能包含制表符，所以这应该是安全的.如果您的单元格中有换行符，我不确定 TSV 输出是否有效，但如果有效，您可以使用 CSVParser 来读取它而不是 lines().

XSSFWorkbook input = new XSSFWorkbook(new File("input.xlsx"));CSVPrinter output = new CSVPrinter(new FileWriter("output.csv"), CSVFormat.DEFAULT);String tsv = new XSSFExcelExtractor(input).getText();BufferedReader reader = new BufferedReader(new StringReader(tsv));reader.lines().map(line -> line.split("\t").forEach(output::printRecord);

如果将整个 this 缓冲为 String 效率太低，请复制基于事件的提取器之一的实现并直接写入 CSVPrinter.>

Currently I am using below code to convert XLSX file to CSV using Java. I need a faster solution because this is too slow.

public class Test1 {
    static void convert(File inputFile, File outputFile) {
        try {
            FileOutputStream fos = new FileOutputStream(outputFile);
            // Get the workbook object for XLSX file
            XSSFWorkbook wBook = new XSSFWorkbook(
                    new FileInputStream(inputFile));
            // Get first sheet from the workbook
            XSSFSheet sheet = wBook.getSheetAt(0);
            Row row;
            Cell cell;
            // Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.iterator();

            while (rowIterator.hasNext()) {
                row = rowIterator.next();

                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        data.append(cell.getBooleanCellValue() + ",");

                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        data.append(cell.getNumericCellValue() + ",");

                        break;
                    case Cell.CELL_TYPE_STRING:
                        data.append(cell.getStringCellValue() + ",");
                        break;

                    case Cell.CELL_TYPE_BLANK:
                        data.append("" + ",");
                        break;
                    default:
                        data.append(cell + ",");

                    }
                }
            }

            fos.write(data.toString().getBytes());
            fos.close();

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

    // testing the application

    public static void main(String[] args) {
        // reading file from desktop
        File inputFile = new File("D:\\Test.xlsx");
        // writing excel data to csv
        File outputFile = new File("D:\\Test1.csv");
        convert(inputFile, outputFile);
    }
}

解决方案

The text extractors will dump a TSV of the entire workbook. Performance depends on the implementation chosen and your memory availability.

You can then pipe that into a CSVPrinter to get correct CSV output. I don't think Excel cells can ever contain tab characters, so this should be safe. If you have newlines in your cells I'm not sure whether the TSV output will be valid, but if it is you can use a CSVParser to read it instead of lines().

XSSFWorkbook input = new XSSFWorkbook(new File("input.xlsx"));
CSVPrinter output = new CSVPrinter(new FileWriter("output.csv"), CSVFormat.DEFAULT); 

String tsv = new XSSFExcelExtractor(input).getText();
BufferedReader reader = new BufferedReader(new StringReader(tsv));
reader.lines().map(line -> line.split("\t").forEach(output::printRecord);

If buffering the whole this as a String is too inefficient, copy the implementation of one of the event-based extractors and write directly to the CSVPrinter instead.

这篇关于如何将 xlsx 文件转换为 csv?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！