文件类：如何将excel文件转为csv文件（且保留时间格式）？

最近有个场景，在ftp服务器上，读取csv文件并入库，但是客户提供的一部分文件却是xls文件，就得搞个将excel转为csv文件的方法，话不多说直接开干。

方法

    public static void convertExcelToCSV(String excelFilePath, String csvFilePath) throws IOException {
        FileInputStream inputStream = new FileInputStream(excelFilePath);
        Workbook workbook = getWorkbook(inputStream, excelFilePath);
        inputStream.close();

        FileWriter writer = new FileWriter(csvFilePath);

        for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            Iterator<Row> rowIterator = sheet.iterator();

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                StringBuilder stringBuilder = new StringBuilder();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    stringBuilder.append(getCellValue(cell));
                    if (cellIterator.hasNext()) {
                        stringBuilder.append(",");
                    }
                }

                writer.write(stringBuilder.toString() + "\n");
            }

            if (sheetIndex < workbook.getNumberOfSheets() - 1) {
                writer.write("\n"); // 在不同工作表之间添加一个空行
            }
        }

        writer.flush();
        writer.close();
        workbook.close();
    }

    private static Workbook getWorkbook(FileInputStream inputStream, String excelFilePath) throws IOException {
        Workbook workbook = null;
        if (excelFilePath.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        } else if (excelFilePath.endsWith("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else {
            throw new IllegalArgumentException("The specified file is not an Excel file");
        }
        return workbook;
    }

    private static String getCellValue(Cell cell) {
        //DataFormatter formatter = new DataFormatter();
        String formatPattern = "yyyy-MM-dd HH:mm:ss"; // 自定义日期时间格式
        //FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    DateFormat dateFormat = new SimpleDateFormat(formatPattern);
                    return dateFormat.format(date);
                } else {
                    return Double.toString(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return Boolean.toString(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

测试

    public static void main(String[] args) {
        String excelFilePath = "D:\\我是excel文件.xlsx";
        String csvFilePath = "D:\\我是csv文件.csv";

        try {
            convertExcelToCSV(excelFilePath, csvFilePath);
            System.out.println("Excel file converted to CSV successfully!");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

以上为整合保留时间格式需求的全部代码，亲测非常好用！