Java处理Excel表格的读取和写入

由于工作需要处理以下excel表格，所以学习了以下关于excel的两个库，这里记录下简单的读取和写入方法。处理xlsx文件使用的是java poi，处理csv文件使用的是opencsv。

一、处理xlsx文件：

首先导入依赖：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

使用java poi 对excel进行无对象的读取和写入：

public class ExcelUtils {

    public static void main(String[] args) throws IOException {
        operationExcel("C:\\Users\\OYMN\\Desktop\\鸿荣翻译\\SDG1.csv", 
                       "C:\\Users\\OYMN\\Desktop\\鸿荣翻译\\newSDG1.csv");
    }

    public static void operationExcel(String sourcePath, String destPath) {
		
        FileOutputStream fos = null;
        try {
            Workbook workbook = new XSSFWorkbook(new FileInputStream(sourcePath));
            int sheetCount = workbook.getNumberOfSheets();  //获取sheet的数量

            Workbook newWorkbook = new XSSFWorkbook();
            for (int i = 0; i < sheetCount; i++) {
                operationExcel(workbook, newWorkbook, i);
            }

            fos = new FileOutputStream(destPath);
            
            //这里有个问题：只能一次性通过fos写入，而不能处理完一张sheet后write一次，否则运行没问题，但是excel打开显示损坏
            newWorkbook.write(fos);

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(fos != null){
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 
     * @param workbook   源workbook
     * @param newWorkbook  新的workbook
     * @param index  第几张sheet（从0开始计数）
     * @throws IOException
     */
    private static void operationExcel(Workbook workbook, Workbook newWorkbook, int index) 
        throws IOException {

        Sheet oldSheet = workbook.getSheetAt(index);
        Sheet newSheet = newWorkbook.createSheet(oldSheet.getSheetName());

        int rowNum = oldSheet.getPhysicalNumberOfRows();
        for (int i = 0; i < rowNum; i++) {
            Row oldRow = oldSheet.getRow(i);
            Row newRow = newSheet.createRow(i);
            for (int j = 0; j < oldRow.getPhysicalNumberOfCells(); j++) {
                //获取单元格
                Cell oldCell = oldRow.getCell(j);
                if (oldCell != null) {
                    
                    //这里可以加入自己的业务，对单元格进行处理，然后再后续写入到新的表格
                    
                    Cell newCell = newRow.createCell(j, oldCell.getCellType());
                    //数字
                    if (oldCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        newCell.setCellValue(oldCell.getNumericCellValue());
                    }
                    //字符串
                    else if (oldCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        newCell.setCellValue(oldCell.getStringCellValue());
                    }
                }
            }
        }
    }
}

二、处理csv文件：

由于java poi并没有支持对csv文件的处理，所以想要处理csv文件，就需要借助其他的库，这里选用opencsv，首先导入依赖：

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

简单读取和写入：

public static void operationCsv(String sourcePath, String destPath)  {
    CSVReader csvReader = null;
    CSVWriter csvWriter = null;
    try {
        //这里是为了处理中文乱码问题
        Writer writer = new FileWriter(destPath);
        writer.write(new String(new byte[] { (byte) 0xEF, (byte) 0xBB,(byte) 0xBF }));
        csvWriter = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER, CSVWriter.NO_ESCAPE_CHARACTER, CSVWriter.DEFAULT_LINE_END);

        //reader
        csvReader = new CSVReader(new FileReader(sourcePath));
        List<String[]> rows = csvReader.readAll();
        //每行再写入新表格
        for (String[] row : rows) {
            csvWriter.writeNext(row);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            csvReader.close();
            csvWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}