JAVA读取Excel数据 JAVA怎样读取Excel数据
孟应杰 人气:01.创建Maven项目在pom文件中添加依赖
<dependencies> <!-- 旧的 .xls --> <!--<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency>--> <!-- 新的 .xlsx --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> </dependencies>
2.编写代码
import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream; import java.io.IOException; public class PoiTest { public static void main(String[] args) throws IOException { FileInputStream is = new FileInputStream("src/main/resources/test.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(is); //读取Sheet Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); //获取最大行数 int rownum = sheet.getPhysicalNumberOfRows(); //获取最大列数 int colnum = row.getPhysicalNumberOfCells(); for (int i = 0; i < rownum; i++) { //获取第i行数据 row = sheet.getRow(i); for (int j = 0; j < colnum; j++) { Cell cell = row.getCell(j); cell.setCellType(CellType.STRING); String cellText = cell.getStringCellValue(); System.out.print(cellText + "\t"); } System.out.println(); } } }
3.报错
3.1 异常解决Cannot get a STRING value from a NUMERIC cell poi
poi导入excel表格数据时报java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell异常是因为在读取cell单元格字符串时,有number类型的数据,因此需要把它转化为纯String类型,这样就不会报错了。
报错的地方类似于这样。
//获取单元格 XSSFCell cell = row.getCell(0); //获取单元格数据 String cellValue = cell.getStringCellValue();
在number类型转化为String类型的过程中造成了Cannot get a STRING value from a NUMERIC cell这样的问题,因此需要在读取excel单元格数据转化之前设置单元格类型为String,代码如下。
//获取单元格 XSSFCell cell = row.getCell(0); //设置单元格类型 cell.setCellType(CellType.STRING); //获取单元格数据 String cellValue = cell.getStringCellValue();
3.2 poi导出Excel报错java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTWorkbook$Factoryat
Exception in thread "main" java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTWorkbook$Factoryat org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:436)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:238)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:229)
at exportexcel.BuildXLSX.main(BuildXLSX.java:35)
Caused by: java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
... 4 more
错误原因:
未导入poi-ooxml-schema jar包。我使用的是poi-3.15 , 应该是poi-ooxml-schemas-3.15.jar包
加载全部内容