读取 Excel 文档时出现问题(Java 代码) [英] Issue while reading Excel document (Java code)
问题描述
我有一些读取 Excel 数据的 Java 代码.在运行 Java 代码时，它显示以下错误.帮我解决同样的问题.另外，我需要知道读取 .xlsx 文件的其他方法.
(一个小的编辑)我如何打印带有各自列的行.例如:
年龄192021薪水35k20k40k...<块引用>
线程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)在org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:104)在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;导入 java.io.FileInputStream;导入 java.io.FileNotFoundException;导入 java.io.IOException;导入 java.util.Iterator;导入 org.apache.poi.hssf.usermodel.HSSFSheet;导入 org.apache.poi.hssf.usermodel.HSSFWorkbook；导入 org.apache.poi.ss.usermodel.Cell;导入 org.apache.poi.ss.usermodel.Row;公共类 ExcelRead {公共静态无效主(字符串 [] args){尝试 {FileInputStream file = new FileInputStream(new File("C:/Users/vinayakp/Desktop/Book.xlsx"));HSSFWorkbook 工作簿 = 新的 HSSFWorkbook(file);HSSFSheet 工作表 = workbook.getSheetAt(0);迭代器<行>rowIterator = sheet.iterator();while(rowIterator.hasNext()) {行行 = rowIterator.next();迭代器<Cell>cellIterator = row.cellIterator();而(cellIterator.hasNext()){Cell cell = cellIterator.next();开关(cell.getCellType()){案例 Cell.CELL_TYPE_BOOLEAN:System.out.print(cell.getBooleanCellValue() + "\t\t");休息;案例 Cell.CELL_TYPE_NUMERIC:System.out.print(cell.getNumericCellValue() + "\t\t");休息;案例 Cell.CELL_TYPE_STRING:System.out.print(cell.getStringCellValue() + "\t\t");休息;}}System.out.println("");}文件.关闭()；} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException ae) {ae.printStackTrace();}}}删除之前的导入类后尝试添加
import org.apache.poi.ss.usermodel.Cell;导入 org.apache.poi.ss.usermodel.Row;导入 org.apache.poi.ss.usermodel.Sheet;导入 org.apache.poi.ss.usermodel.Workbook;导入 org.apache.poi.ss.usermodel.WorkbookFactory;私有静态无效读取(字符串路径){工作簿工作簿=空；FileInputStream fis = null;尝试 {文件源 = 新文件(路径)；如果(源.存在()){fis = new FileInputStream(source);工作簿 = WorkbookFactory.create(source);}别的{JOptionPane.showMessageDialog(null, "文件路径不存在.", "错误", JOptionPane.ERROR_MESSAGE);}工作表 = null;int lastRowNum = 0;int numSheets = workbook.getNumberOfSheets();for(int i = 0; i 0) {lastRowNum = sheet.getLastRowNum();int lastCellNum = 0;for(行行:工作表){员工 emp = 新员工()；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);开关(cell.getCellType()){案例 Cell.CELL_TYPE_FORMULA://printValue = "FORMULA value=" + cell.getCellFormula();stringValues[cellIndex] = cell.getCellFormula();休息;案例 Cell.CELL_TYPE_NUMERIC://printValue = "NUMERIC value=" + cell.getNumericCellValue();System.out.println("数值为数字::"+ cell.getNumericCellValue());stringValues[cellIndex] = String.valueOf(cell.getNumericCellValue());休息;案例 Cell.CELL_TYPE_STRING://printValue = "STRING value=" + cell.getStringCellValue();stringValues[cellIndex] = cell.getStringCellValue();休息;案例 Cell.CELL_TYPE_BLANK://printValue = "STRING value=" + cell.getStringCellValue();stringValues[cellIndex] = cell.getStringCellValue();休息;默认:}}}}}}} 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();}捕获(异常 e){logger.error(e.getMessage());e.printStackTrace();}最后 {如果(fis ！= null){尝试 {fis.close();fis = 空；} 捕捉(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 previous imports class then try to 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屋！
