CSV文件转换成Excel文件

在Java中将CSV文件转换成Excel文件，可以使用Apache POI、JXL或更为现代的库如Apache Commons CSV与OpenCSV结合使用，或者使用Spire.XLS for Java等商业库。

1.使用Open csv将csv文件转换为excel文件           

            <dependency>
                <groupId>com.opencsv</groupId>
                <artifactId>opencsv</artifactId>
                <version>5.6</version>
            </dependency>

  /**
     * Csv文件转换为Excel文件
     * csvFile:csvFile文件
     * fileName:要转换的xlsx文件名
     */
    public static File convertCsvToExcel(File csvFile, String fileName) {
        try {
            //创建SXSSFWorkbook对象，参数表示要保持在内存中的行数
            SXSSFWorkbook workbook = new SXSSFWorkbook(100);
            CSVReader reader = new CSVReader(new FileReader(csvFile.getCanonicalPath()));
            Sheet sheet = workbook.createSheet("Sheet1");
 
            Row row;
            Cell cell;
            String[] dataRow;
            while ((dataRow = reader.readNext()) != null) {
                row = sheet.createRow(sheet.getPhysicalNumberOfRows());
                for (int j = 0; j < dataRow.length; j++) {
                    cell = row.createCell(j);
                    cell.setCellValue(dataRow[j]);
                    cell.setCellType(CellType.STRING);
                }
            }
            String newFileName = Files.createTempDirectory(THEME_ANALYSE_XLSX_FILE).toString() + File.separator + FilenameUtils.getName(fileName);
            File xlsxFile = new File(newFileName);
            FileOutputStream fos = new FileOutputStream(xlsxFile);
            workbook.write(fos);
            workbook.dispose();
            csvFile.delete();
            return xlsxFile;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

2.1.使用commons-csv将csv文件转换为excel文件

            <dependency>
                <groupId>com.opencsv</groupId>
                <artifactId>commons-csv</artifactId>
                <version>1.9</version>
            </dependency>

  /**
     * 使用commons csv转换csv文件
     * csvFile:csvFile文件
     * fileName:要转换的xlsx文件名
     */
    private File convertCsvToExcel2(File csvFile, String fileName) {
        try (FileReader reader = new FileReader(csvFile.getCanonicalPath());
             CSVParser parser = new CSVParser(reader, CSVFormat.DEFAULT)) {
            // 解析CSV文件
            Iterator<CSVRecord> iterator = parser.iterator();
            //创建SXSSFWorkbook对象，参数表示要保持在内存中的行数
            SXSSFWorkbook workbook = new SXSSFWorkbook(100);
            Sheet sheet = workbook.createSheet("Sheet1");
            Row row;
            Cell cell;
            while (iterator.hasNext()) {
                CSVRecord record = iterator.next();
                row = sheet.createRow(sheet.getPhysicalNumberOfRows());
                for (int j = 0; j < record.size(); j++) {
                    cell = row.createCell(j);
                    cell.setCellValue(record.get(j));
                    cell.setCellType(CellType.STRING);
                }
 
            }
            Path tempFile = Files.createTempDirectory("di.download.requirement");
            String sourceFilePath = tempFile.toString() + File.separator;
 
            String newFileName = sourceFilePath + FilenameUtils.getName(fileName);
            File xlsxFile= new File(newFileName);
            FileOutputStream fos = new FileOutputStream(xlsxFile);
            workbook.write(fos);
            workbook.dispose();
            return xlsxFile;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

3.使用easyexcel 转换csv文件

            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.1.0</version>
            </dependency>

     /**
     * 使用EasyExcel转换csv文件
     * csvFile:csvFile文件
     * fileName:要转换的xlsx文件名
     */
    public static File convertCsvToExcel(File csvFile, String fileName) {
        try {
            String baseName = FilenameUtils.getBaseName(fileName);
            File xlsxFile = File.createTempFile(baseName, EXCEL_FILE_SUFFIX);
            //使用EasyExcel读取csv文件
            ExcelReaderBuilder readerBuilder = EasyExcel.read(csvFile);
            readerBuilder.headRowNumber(0);
 
            ExcelReader excelReader = readerBuilder.build();
            ReadWorkbookHolder readWorkbookHolder = excelReader.analysisContext().readWorkbookHolder();
 
            if (readWorkbookHolder instanceof CsvReadWorkbookHolder) {
                CsvReadWorkbookHolder csvReadWorkbookHolder = (CsvReadWorkbookHolder) readWorkbookHolder;
                // 底层读取csv 用的是apache的common-csv 所以设置 CsvFormat即可
                csvReadWorkbookHolder.setCsvFormat(csvReadWorkbookHolder.getCsvFormat().withDelimiter(',').withQuote(null));
            }
 
            List<Object> dataList = new ExcelReaderSheetBuilder(excelReader).doReadSync();
 
            // 使用EasyExcel写入xlsx文件
            ExcelWriterBuilder writerBuilder = EasyExcel.write(xlsxFile);
            writerBuilder.sheet().doWrite(dataList);
            return xlsxFile;
        } catch (Throwable e) {
            throw new RuntimeException(e);
        }
    }

对于大量数据且内存有限的情况，推荐使用如xlsxtream这样的库，它可以实现流式读写，从而避免一次性加载整个CSV文件到内存中。

            <dependency>
                <groupId>com.monitorjbl</groupId>
                <artifactId>xlsx-streamer</artifactId>
                <version>2.1.0</version>
            </dependency>

 /**
     * 解析Excel文件,判断是否是空文件
     */
    public static Boolean parseExcel(String filePath) {
        Workbook workbook = null;
        try {
            InputStream inputStream = new FileInputStream(new File(filePath));
            workbook = StreamingReader.builder()
                    .rowCacheSize(100)
                    .bufferSize(4096)
                    .open(inputStream);

            for (Sheet sheet : workbook) {
                for (Row row : sheet) {
                    int colnum = row.getPhysicalNumberOfCells();
                    for (int i = 1; i < colnum; i++) {
                        Cell cell = row.getCell(i);
                        if (!BLANK.equals(cell.getCellType()) && StringUtils.isNotEmpty(cell.getStringCellValue())) {
                            return false;
                        }
                    }
                }
            }
            workbook.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return true;
    }