org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
原因是：
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
<!--处理2003 excel--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.16</version> </dependency> <!--处理2007 excel--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version> </dependency>
在使用POI读取Excel文件内容时，发生了异常，报错如下：
java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell 异常怎么办?
大概意思是不能从一个数值的列获取一个字符串类型的值，我使用下面的代码来获取单元格的值：
解决方法是在读取某单元格时，使用setCellType()方法先将该单元格的类型设置为STRING，代码如下：
cell.setCellType(CellType.STRING); String stringCellValue = cell.getStringCellValue();
poi创建excel 和读取excel (2003版本)
创建
import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.junit.Test;import java.io.FileOutputStream;import java.io.IOException;public class ordingTest { @Test public void createOrding() throws IOException { HSSFWorkbook work = new HSSFWorkbook(); HSSFSheet mysheet = work.createSheet("mysheet"); HSSFRow row = mysheet.createRow(0); HSSFRow row2 = mysheet.createRow(1); HSSFRow row3 = mysheet.createRow(2); row.createCell(0).setCellValue("name"); row.createCell(1).setCellValue("age"); row.createCell(2).setCellValue("adress"); row2.createCell(0).setCellValue("张三"); row2.createCell(1).setCellValue(13); row2.createCell(2).setCellValue("上海"); row3.createCell(0).setCellValue("李四"); row3.createCell(1).setCellValue(16); row3.createCell(2).setCellValue("北京"); FileOutputStream fileOutputStream = new FileOutputStream("E:\\hello.xls"); work.write(fileOutputStream); fileOutputStream.flush(); work.close(); fileOutputStream.close(); }}
读取:
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
public class ordingTest2 {
@Test
public void reading() throws IOException {
InputStream inputStream = new FileInputStream("E:\\hello.xls") {
};
HSSFWorkbook sheets = new HSSFWorkbook(inputStream);
HSSFSheet sheetAt = sheets.getSheetAt(0);
for (Row cells : sheetAt) {
for (Cell cell : cells) {
cell.setCellType(CellType.STRING);
String stringCellValue = cell.getStringCellValue();
System.out.println("stringCellValue = " + stringCellValue);
}
}
sheets.close();
}
}
第二中读取方法:
/*从最后一行读取,起始索引0*/
int lastRowNum = sheetAt.getLastRowNum();
for (int i = 0; i <=lastRowNum ; i++) {
HSSFRow row = sheetAt.getRow(i);
short lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
HSSFCell cell = row.getCell(j);
cell.setCellType(CellType.STRING);
String stringCellValue = cell.getStringCellValue();
System.out.println("stringCellValue = " + stringCellValue);
}
}
