Berbagi teknologi

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Ada skenario baru-baru ini di mana file csv dibaca di server ftp dan dimasukkan ke dalam database, tetapi beberapa file yang disediakan oleh pelanggan adalah file xls. Jadi kami harus mencari cara untuk mengonversi file excel ke csv dimulai tanpa basa-basi lagi.

metode

    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 "";
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

tes

    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();
        }
    }
1
2
3
4
5
6
7
8
9
10
11

Di atas adalah integrasi保留时间格式Semua kode diperlukan, tes pribadi sangat mudah digunakan!