Obtention d'Exception(org.apache.pi.openxml4j.exception - pas de type de contenu [M1.13]) lors de la lecture de fichier xlsx à l'aide d'Apache POI?

À peu près sûr que cette exception est levée lorsque le fichier Excel est soit protégé par mot de passe ou le fichier est endommagé. Si vous voulez juste lire un .fichier xlsx, essayer mon code ci-dessous. C'est beaucoup plus courte et plus facile à lire.

import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
//.....
static final String excelLoc = "C:/Documents and Settings/Users/Desktop/testing.xlsx";
public static void ReadExcel() {
InputStream inputStream = null;
try {
inputStream = new FileInputStream(new File(excelLoc));
Workbook wb = WorkbookFactory.create(inputStream);
int numberOfSheet = wb.getNumberOfSheets();
for (int i = 0; i < numberOfSheet; i++) {
Sheet sheet = wb.getSheetAt(i);
//.... Customize your code here
//To get sheet name, try -> sheet.getSheetName()
}
} catch {}
}

J'ai été en utilisant XSSFWorkbook à lire .xls, ce qui a entraîné InvalidFormatException. Je dois utiliser un plus générique Classeur et de la Feuille pour le faire fonctionner.

Ce post m'a aidé à résoudre mon problème.

Nettoyage du code (a commenté sur l'enregistreur pour la plupart) pour le faire fonctionner dans mon environnement Eclipse.

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;
public class ReadXLSX {
private String filepath;
private XSSFWorkbook workbook;
//private static Logger logger=null;
private InputStream resourceAsStream;
public ReadXLSX(String filePath) {
//logger=LoggerFactory.getLogger("ReadXLSX");
this.filepath = filePath;
resourceAsStream = ClassLoader.getSystemResourceAsStream(filepath);
}
public ReadXLSX(InputStream fileStream) {
//logger=LoggerFactory.getLogger("ReadXLSX");
this.resourceAsStream = fileStream;
}
private void loadFile() throws FileNotFoundException,
NullObjectFoundException {
if (resourceAsStream == null)
throw new FileNotFoundException("Unable to locate give file..");
else {
try {
workbook = new XSSFWorkbook(resourceAsStream);
} catch (IOException ex) {
}
}
}//end loadxlsFile
public String[] getSheetsName() {
int totalsheet = 0;
int i = 0;
String[] sheetName = null;
try {
loadFile();
totalsheet = workbook.getNumberOfSheets();
sheetName = new String[totalsheet];
while (i < totalsheet) {
sheetName[i] = workbook.getSheetName(i);
i++;
}
} catch (FileNotFoundException ex) {
//logger.error(ex);
} catch (NullObjectFoundException ex) {
//logger.error(ex);
}
return sheetName;
}
public int[] getSheetsIndex() {
int totalsheet = 0;
int i = 0;
int[] sheetIndex = null;
String[] sheetname = getSheetsName();
try {
loadFile();
totalsheet = workbook.getNumberOfSheets();
sheetIndex = new int[totalsheet];
while (i < totalsheet) {
sheetIndex[i] = workbook.getSheetIndex(sheetname[i]);
i++;
}
} catch (FileNotFoundException ex) {
//logger.error(ex);
} catch (NullObjectFoundException ex) {
//logger.error(ex);
}
return sheetIndex;
}
private boolean validateIndex(int index) {
if (index < getSheetsIndex().length && index >= 0)
return true;
else
return false;
}
public int getNumberOfSheet() {
int totalsheet = 0;
try {
loadFile();
totalsheet = workbook.getNumberOfSheets();
} catch (FileNotFoundException ex) {
//logger.error(ex.getMessage());
} catch (NullObjectFoundException ex) {
//logger.error(ex.getMessage());
}
return totalsheet;
}
public int getNumberOfColumns(int SheetIndex) {
int NO_OF_Column = 0;
@SuppressWarnings("unused")
XSSFCell cell = null;
XSSFSheet sheet = null;
try {
loadFile(); //load give Excel
if (validateIndex(SheetIndex)) {
sheet = workbook.getSheetAt(SheetIndex);
Iterator<Row> rowIter = sheet.rowIterator();
XSSFRow firstRow = (XSSFRow) rowIter.next();
Iterator<Cell> cellIter = firstRow.cellIterator();
while (cellIter.hasNext()) {
cell = (XSSFCell) cellIter.next();
NO_OF_Column++;
}
} else
throw new InvalidSheetIndexException("Invalid sheet index.");
} catch (Exception ex) {
//logger.error(ex.getMessage());
}
return NO_OF_Column;
}
public int getNumberOfRows(int SheetIndex) {
int NO_OF_ROW = 0;
XSSFSheet sheet = null;
try {
loadFile(); //load give Excel
if (validateIndex(SheetIndex)) {
sheet = workbook.getSheetAt(SheetIndex);
NO_OF_ROW = sheet.getLastRowNum();
} else
throw new InvalidSheetIndexException("Invalid sheet index.");
} catch (Exception ex) {
//logger.error(ex);
}
return NO_OF_ROW;
}
public String[] getSheetHeader(int SheetIndex) {
int noOfColumns = 0;
XSSFCell cell = null;
int i = 0;
String columns[] = null;
XSSFSheet sheet = null;
try {
loadFile(); //load give Excel
if (validateIndex(SheetIndex)) {
sheet = workbook.getSheetAt(SheetIndex);
noOfColumns = getNumberOfColumns(SheetIndex);
columns = new String[noOfColumns];
Iterator<Row> rowIter = sheet.rowIterator();
XSSFRow Row = (XSSFRow) rowIter.next();
Iterator<Cell> cellIter = Row.cellIterator();
while (cellIter.hasNext()) {
cell = (XSSFCell) cellIter.next();
columns[i] = cell.getStringCellValue();
i++;
}
} else
throw new InvalidSheetIndexException("Invalid sheet index.");
}
catch (Exception ex) {
//logger.error(ex);
}
return columns;
}//end of method
public String[][] getSheetData(int SheetIndex) {
int noOfColumns = 0;
XSSFRow row = null;
XSSFCell cell = null;
int i = 0;
int noOfRows = 0;
int j = 0;
String[][] data = null;
XSSFSheet sheet = null;
try {
loadFile(); //load give Excel
if (validateIndex(SheetIndex)) {
sheet = workbook.getSheetAt(SheetIndex);
noOfColumns = getNumberOfColumns(SheetIndex);
noOfRows = getNumberOfRows(SheetIndex) + 1;
data = new String[noOfRows][noOfColumns];
Iterator<Row> rowIter = sheet.rowIterator();
while (rowIter.hasNext()) {
row = (XSSFRow) rowIter.next();
Iterator<Cell> cellIter = row.cellIterator();
j = 0;
while (cellIter.hasNext()) {
cell = (XSSFCell) cellIter.next();
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
data[i][j] = cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
String formatCellValue = new DataFormatter()
.formatCellValue(cell);
data[i][j] = formatCellValue;
} else {
data[i][j] = Double.toString(cell
.getNumericCellValue());
}
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
data[i][j] = Boolean.toString(cell
.getBooleanCellValue());
}
else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
data[i][j] = cell.getCellFormula().toString();
}
j++;
}
i++;
} //outer while
} else
throw new InvalidSheetIndexException("Invalid sheet index.");
} catch (Exception ex) {
//logger.error(ex);
}
return data;
}
public String[][] getSheetData(int SheetIndex, int noOfRows) {
int noOfColumns = 0;
XSSFRow row = null;
XSSFCell cell = null;
int i = 0;
int j = 0;
String[][] data = null;
XSSFSheet sheet = null;
try {
loadFile(); //load give Excel
if (validateIndex(SheetIndex)) {
sheet = workbook.getSheetAt(SheetIndex);
noOfColumns = getNumberOfColumns(SheetIndex);
data = new String[noOfRows][noOfColumns];
Iterator<Row> rowIter = sheet.rowIterator();
while (i < noOfRows) {
row = (XSSFRow) rowIter.next();
Iterator<Cell> cellIter = row.cellIterator();
j = 0;
while (cellIter.hasNext()) {
cell = (XSSFCell) cellIter.next();
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
data[i][j] = cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
String formatCellValue = new DataFormatter()
.formatCellValue(cell);
data[i][j] = formatCellValue;
} else {
data[i][j] = Double.toString(cell
.getNumericCellValue());
}
}
j++;
}
i++;
} //outer while
} else
throw new InvalidSheetIndexException("Invalid sheet index.");
} catch (Exception ex) {
//logger.error(ex);
}
return data;
}
}

Créé ce petit testcode:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
public class ReadXLSXTest {
/**
* @param args
* @throws FileNotFoundException 
*/
public static void main(String[] args) throws FileNotFoundException {
//TODO Auto-generated method stub
ReadXLSX test = new ReadXLSX(new FileInputStream(new File("./sample.xlsx")));
System.out.println(test.getSheetsName());
System.out.println(test.getNumberOfSheet());
}
}

Tout cela a couru comme un charme, si ma conjecture est que vous avez un fichier XLSX qui est "corrompu" d'une manière ou d'une autre. Essayez de tester avec d'autres données.

Cheers,
Wim

Si le fichier est protégé par mot de passe, alors cette erreur vient.