java操作Excel实现读写

依赖
implementation ‘org.apache.poi:poi:3.17’
implementation ‘org.apache.poi:poi-ooxml:3.17’
implementation ‘org.apache.xmlbeans:xmlbeans:3.1.0’

读取2003以后的Excel

public void testReadExcelAfter(){
    try {
        // 读取Excel
        Workbook wb = new XSSFWorkbook(new FileInputStream("E:\\name.xlsx"));
        // 获取sheet(篇)数目
        for (int t = 0; t < wb.getNumberOfSheets(); t++) {
            Sheet sheet = wb.getSheetAt(t);
            Row row = null;
            int lastRowNum = sheet.getLastRowNum();
            // 循环读取
            for (int i = 0; i <= lastRowNum; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    // 获取每一列的值
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        Cell cell = row.getCell(j);
                        String value = getCellValue(cell);
                        if (!value.equals("")) {
                            System.out.print(value + "  ");
                        }else {
                            continue;
                        }
                    }
                    System.out.println();
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

读取2003以前的Excel

public void testReadExcelBefore(){
        try {
            // 读取Excel
            Workbook wb = new HSSFWorkbook(new FileInputStream("E:\\test.xls"));
            // 获取sheet(篇)数目
            for (int t = 0; t < wb.getNumberOfSheets(); t++) {
                Sheet sheet = wb.getSheetAt(t);
                Row row = null;
                int lastRowNum = sheet.getLastRowNum();
                // 循环读取
                for (int i = 0; i <= lastRowNum; i++) {
                    row = sheet.getRow(i);
                    if (row != null) {
                        // 获取每一列的值
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            Cell cell = row.getCell(j);
                            String value = getCellValue(cell);
                            if (!value.equals("")) {
                                System.out.print(value + "  ");
                            }else {
                                continue;
                            }
                        }
                        System.out.println();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

读取单元格的值

private String getCellValue(Cell cell){
        Object result="";
        if(cell == null){
            result = "  ";
        }else {
            if(cell.getCellType() == Cell.CELL_TYPE_STRING){
                result=cell.getStringCellValue();
            }else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
                result=cell.getNumericCellValue();
            }else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
                result=cell.getBooleanCellValue();
            }else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
                result=cell.getCellFormula();
            }else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
                result=cell.getErrorCellValue();
            }else if (cell.getCellType() == Cell.CELL_TYPE_BLANK){
                result = "";
            }
        }
        return result.toString();
    }

写入Excel文件

public void testWriteExcel() {
        String excelPath = "D:\\javatest\\2.xlsx";
        Workbook workbook=null;
        try {
            workbook = new XSSFWorkbook();
        } catch (Exception e) {
            System.out.println("创建Excel失败: ");
            e.printStackTrace();
        }
        if(workbook!=null){
            Sheet sheet=workbook.createSheet("测试数据");
            Row row0=sheet.createRow(0);
            for(int i=0;i<12;i++){
                Cell cell=row0.createCell(i, Cell.CELL_TYPE_STRING);
                cell.setCellValue("列标题");
                sheet.autoSizeColumn(i);//自动调整宽度
            }
            for (int rowNum = 1; rowNum < 16; rowNum++) {
                Row row = sheet.createRow(rowNum);
                for (int i = 0; i < 12; i++) {
                    Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
                    cell.setCellValue("单元格" + String.valueOf(rowNum + 1)
                            + String.valueOf(i + 1));
                }
            }
            try {
                FileOutputStream outputStream = new FileOutputStream(excelPath);
                workbook.write(outputStream);
                outputStream.flush();
                outputStream.close();
            } catch (Exception e) {
                System.out .println("写入Excel失败: ");
                e.printStackTrace();
            }
        }
    }

测试

public static void main(String[] args) {
       POI poi = new POI();
       poi.testReadExcelAfter();
    }