Compartilhamento de tecnologia

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

Houve um cenário recentemente em que um arquivo csv foi lido no servidor ftp e colocado no banco de dados, mas alguns dos arquivos fornecidos pelo cliente eram arquivos xls. Então, tivemos que encontrar uma maneira de converter arquivos Excel em csv. começou sem mais delongas.

método

    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

teste

    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

O acima é a integração保留时间格式Todos os códigos necessários, o teste pessoal é muito fácil de usar!