1、添加依赖
<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><dependency><groupId>joda-time</groupId><artifactId>joda-time</artifactId><version>2.10.1</version></dependency>
2、读 EXCEL
public static void read() throws Exception{FileInputStream stream = new FileInputStream("D:\\Test\\file.xlsx");// 1.创建工作簿对象,并指定操作的文件Workbook workbook = new XSSFWorkbook(stream);// 2.获取工作表对象,既可以根据工作表的顺序获取,也可以根据工作表的名称获取Sheet sheet = workbook.getSheet("Sheet0");// 获取行数(获取当前工作表最后一行的行号,行号从0开始)int lastRowNum = sheet.getLastRowNum();// 获取列数Row row1 = sheet.getRow(0);int colNum = row1.getPhysicalNumberOfCells();// 3.遍历工作表获得行对象for(int i=0; i<=lastRowNum;i++){Row row = sheet.getRow(i);for (int j = 0; j < colNum; j++) {Cell cell = row.getCell(j);// 处理数字值if (cell.getCellType() == CellType.NUMERIC) {double numericValue = cell.getNumericCellValue();System.out.println(numericValue);} else if (cell.getCellType() == CellType.STRING) {// 处理字符串值String stringValue = cell.getStringCellValue();System.out.println(stringValue);}}}}
注意:
1、操作的 EXCEL 对象要区分 .xls文件 还是 .xlsx 文件
FileInputStream stream = new FileInputStream("D:\\Test\\file.xlsx");// XSSF操作的是Excel2007以上的版本,对应文件的后缀名是xlsx Workbook workbook = new XSSFWorkbook(inputStream) // HSSF操作的是Excel2003以前的版本,对应的文件后缀名是xlsWorkbook workbook = new HSSFWorkbook(inputStream)
2、读取单元格数据要注意数据格式
读取单元格数据之前,可以先检查该单元格的数据类型。如果它是数字类型,那么你应该读取它的数值值;如果它是文本类型,那么你应该读取它的字符串值。这样可以避免数据类型不匹配的问题。Cell cell = row.getCell(columnIndex); // 处理数字值 } if(cell.getCellType() == CellType.NUMERIC) {double numericValue = cell.getNumericCellValue();// 处理字符串值 else if (cell.getCellType() == CellType.STRING) {String stringValue = cell.getStringCellValue();}
2、写 EXCEL
public static void write(Workbook workbook,String path) throws Exception {/*创建一个表*/Sheet sheet = workbook.createSheet("Sheet1");/*创建一行*/Row row1 = sheet.createRow(0);/*创建一个单元格*/Cell cell = row1.createCell(0);/*设置单元格的内容*/cell.setCellValue("学习的内容");cell = row1.createCell(1);cell.setCellValue("java操作ecxel");Row row2 = sheet.createRow(1);cell = row2.createCell(0);cell.setCellValue("学习的时间");cell = row2.createCell(1);cell.setCellValue("2025-03-14 15:35:26");FileOutputStream fileOutputStream = new FileOutputStream(path);workbook.write(fileOutputStream);fileOutputStream.flush();fileOutputStream.close();}
3、主程序调用
public static void main(String[] args) throws Exception {Workbook workbook = new XSSFWorkbook();//xlsxwrite(workbook,"D:\\Test\\file.xlsx");read();}