阅读Excel文档时出现问题（Java代码） [英] Issue while reading Excel document (Java code)
本文介绍了阅读Excel文档时出现问题（Java代码）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
（一个小编辑） 。例如：
年龄
19
20
21
工资
35k
20k
40k
。
。
。
线程main中的异常
org.apache。 poi.poifs.filesystem.OfficeXmlFileException：提供的
数据似乎在Office 2007+ XML中。您正在调用处理OLE2 Office文档的POI部分
。您需要调用
POI的不同部分来处理此数据（例如XSSF而不是HSSF）
org.apache.poi.poifs.storage.HeaderBlock。（HeaderBlock.java： 131）
at
org.apache.poi.poifs.storage.HeaderBlock。（HeaderBlock.java:104）
at
org.apache.poi.poifs.filesystem.POIFSFileSystem （POIFSFileSystem.java:138）
org.apache.poi.hssf.usermodel.HSSFWorkbook（HSSFWorkbook.java:322）
org.apache.poi .hssf.usermodel.HSSFWorkbook。（HSSFWorkbook.java:303）
在ExcelRead.main（ExcelRead.java:18）
Java代码如下：
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
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.Row;
public class ExcelRead {
public static void main（String [] args）{
try {
FileInputStream file = new FileInputStream （新文件（C：/Users/vinayakp/Desktop/Book.xlsx））;
HSSFWorkbook工作簿=新HSSFWorkbook（文件）;
HSSFSheet sheet = workbook.getSheetAt（0）;
迭代器<行> rowIterator = sheet.iterator（）;
while（rowIterator.hasNext（））{
Row row = rowIterator.next（）;
迭代器<单元格> cellIterator = row.cellIterator（）;
while（cellIterator.hasNext（））{
Cell cell = cellIterator.next（）;
switch（cell.getCellType（））{
case Cell.CELL_TYPE_BOOLEAN：
System.out.print（cell.getBooleanCellValue（）+\t\t）;
break;
case Cell.CELL_TYPE_NUMERIC：
System.out.print（cell.getNumericCellValue（）+\t\t）;
break;
case Cell.CELL_TYPE_STRING：
System.out.print（cell.getStringCellValue（）+\t\t）;
break;
}
}
System.out.println（）;
}
file.close（）;
} catch（FileNotFoundException e）{
e.printStackTrace（）;
} catch（IOException ae）{
ae.printStackTrace（）;
}
}
}
解决方案
删除以前的导入类后，添加
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
private static void read（String path）{
Workbook workbook = null;
FileInputStream fis = null;
try {
文件源=新建文件（路径）;
if（source.exists（））{
fis = new FileInputStream（source）;
workbook = WorkbookFactory.create（source）;
} else {
JOptionPane.showMessageDialog（null，文件路径不存在，错误，JOptionPane.ERROR_MESSAGE）;
}
Sheet sheet = null;
int lastRowNum = 0;
int numSheets = workbook.getNumberOfSheets（）; （int i = 0; i< numSheets; i ++）
{
sheet = workbook.getSheetAt（i）;
if（sheet.getPhysicalNumberOfRows（）> 0）{
lastRowNum = sheet.getLastRowNum（）;
int lastCellNum = 0;
for（Row row：sheet）{
Employee emp = new Employee（）;
int numOfCell = row.getPhysicalNumberOfCells（）;
System.out.println（numOfCell ::+ numOfCell）;
String stringValues [] = new String [numOfCell];
for（单元格：行）{
// cell = row.getCell（cellIndex）;
int cellIndex = cell.getColumnIndex（）;
logger.info（cellIndex ::+ cellIndex）;
switch（cell.getCellType（））{
case Cell.CELL_TYPE_FORMULA：
// printValue =FORMULA value =+ cell.getCellFormula（）;
stringValues [cellIndex] = cell.getCellFormula（）;
break;
case Cell.CELL_TYPE_NUMERIC：
// printValue =NUMERIC value =+ cell.getNumericCellValue（）;
System.out.println（Value is numeric ::+ cell.getNumericCellValue（））;
stringValues [cellIndex] = String.valueOf（cell.getNumericCellValue（））;
break;
case Cell.CELL_TYPE_STRING：
// printValue =STRING value =+ cell.getStringCellValue（）;
stringValues [cellIndex] = cell.getStringCellValue（）;
break;
case Cell.CELL_TYPE_BLANK：
// printValue =STRING value =+ cell.getStringCellValue（）;
stringValues [cellIndex] = cell.getStringCellValue（）;
break;
默认值：
}
}
}
}
}
}
} catch（InvalidFormatException e）{
logger.error（e.getMessage（））;
e.printStackTrace（）;
} catch（FileNotFoundException e）{
e.printStackTrace（）;
logger.error（e.getMessage（））;
} catch（IOException e）{
logger.error（e.getMessage（））;
e.printStackTrace（）;
}
catch（Exception e）{
logger.error（e.getMessage（））;
e.printStackTrace（）;
}
finally {
if（fis！= null）{
try {
fis.close（）;
fis = null;
} catch（IOException ioEx）{
logger.error（ioEx.getMessage（））;
}
}
}
}
I have some Java code which reads the Excel data. On running the Java code, it's showing the following error. Help me resolve the same. Also, I need to know other method of reading .xlsx file.
(A small edit) how I can print rows with their respective columns. For example:
Age
19
20
21
Salary
35k
20k
40k
.
.
.
Exception in thread "main" 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) at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:131) at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:104) at org.apache.poi.poifs.filesystem.POIFSFileSystem.(POIFSFileSystem.java:138) at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:322) at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:303) at ExcelRead.main(ExcelRead.java:18)
The Java code is as follows:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
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.Row;
public class ExcelRead {
public static void main(String[] args) {
try {
FileInputStream file = new FileInputStream(new File("C:/Users/vinayakp/Desktop/Book.xlsx"));
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while(rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while(cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch(cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t\t");
break;
}
}
System.out.println("");
}
file.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException ae) {
ae.printStackTrace();
}
}
}
解决方案
After deleting previus importing class then add
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
private static void read(String path){
Workbook workbook = null;
FileInputStream fis = null;
try {
File source = new File(path);
if(source.exists()){
fis = new FileInputStream(source);
workbook = WorkbookFactory.create(source);
}else{
JOptionPane.showMessageDialog(null, "File path is not exist.", "Error", JOptionPane.ERROR_MESSAGE);
}
Sheet sheet = null;
int lastRowNum = 0;
int numSheets = workbook.getNumberOfSheets();
for(int i = 0; i < numSheets; i++) {
sheet = workbook.getSheetAt(i);
if(sheet.getPhysicalNumberOfRows() > 0) {
lastRowNum = sheet.getLastRowNum();
int lastCellNum = 0;
for(Row row : sheet) {
Employee emp = new Employee();
int numOfCell = row.getPhysicalNumberOfCells();
System.out.println("numOfCell:: "+numOfCell);
String stringValues [] = new String[numOfCell];
for(Cell cell : row) {
// cell = row.getCell(cellIndex);
int cellIndex = cell.getColumnIndex();
logger.info("cellIndex:: "+ cellIndex);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
// printValue = "FORMULA value=" + cell.getCellFormula();
stringValues[cellIndex] = cell.getCellFormula();
break;
case Cell.CELL_TYPE_NUMERIC:
//printValue = "NUMERIC value=" + cell.getNumericCellValue();
System.out.println("Value is numeric:: "+ cell.getNumericCellValue());
stringValues[cellIndex] = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
// printValue = "STRING value=" + cell.getStringCellValue();
stringValues[cellIndex] = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BLANK:
// printValue = "STRING value=" + cell.getStringCellValue();
stringValues[cellIndex] = cell.getStringCellValue();
break;
default:
}
}
}
}
}
}
} catch (InvalidFormatException e) {
logger.error(e.getMessage());
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
logger.error(e.getMessage());
} catch (IOException e) {
logger.error(e.getMessage());
e.printStackTrace();
}
catch (Exception e) {
logger.error(e.getMessage());
e.printStackTrace();
}
finally {
if (fis != null) {
try {
fis.close();
fis = null;
} catch (IOException ioEx) {
logger.error(ioEx.getMessage());
}
}
}
}
这篇关于阅读Excel文档时出现问题（Java代码）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
