且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

使用 Apache POI API 将 xlsx 转换为 csv

更新时间:2022-06-14 23:12:42

感谢@Swapnil!

This is thanks @Swapnil!

data.append("\r\n"); // After the columns have been appended.


以下内容由@Abdullah 编辑(添加)我上面的原始答案没有那么大的影响,但阿卜杜拉的编辑显示了很多努力,所以我把它留给那些遇到这个问题和答案的人.


The following was edited (added) by @Abdullah My original answer above does not has that much impact, but Abdullah's edit shows much effort, so I leave it for those that encounter this question&answer.

public class App {

    public void convertExcelToCSV(Sheet sheet, String sheetName) {
        StringBuilder data = new StringBuilder();
        try {
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    CellType type = cell.getCellTypeEnum();
                    if (type == CellType.BOOLEAN) {
                        data.append(cell.getBooleanCellValue());
                    } else if (type == CellType.NUMERIC) {
                        data.append(cell.getNumericCellValue());
                    } else if (type == CellType.STRING) {
                        String cellValue = cell.getStringCellValue();
                        if(!cellValue.isEmpty()) {
                            cellValue = cellValue.replaceAll("\"", "\"\"");
                            data.append("\"").append(cellValue).append("\"");
                        }
                    } else if (type == CellType.BLANK) {
                    } else {
                        data.append(cell + "");
                    }
                    if(cell.getColumnIndex() != row.getLastCellNum()-1) {
                        data.append(",");
                    }
                }
                data.append('\n');
            }
            Files.write(Paths.get("C:\\Users\\" + sheetName + ".csv"),
                data.toString().getBytes("UTF-8"));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String [] args)
    {
        App app = new App();
        String path =  "C:\\Users\\myFile.xlsx";
        try (InputStream inp = new FileInputStream(path)) {
            Workbook wb = WorkbookFactory.create(inp);

            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                System.out.println(wb.getSheetAt(i).getSheetName());
                app.convertExcelToCSV(wb.getSheetAt(i), wb.getSheetAt(i).getSheetName());
            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        } 
    }
}