Hello,

I have problem to create solution to read excel files. I saw a lot of solutions but I have problem with first step of my service.

In first step i create Java class with all imports POI.

I declare:

		XSSFWorkbook  wb = null;
wb = new XSSFWorkbook(new File("C:/...../test.xlsx"));

And I have error:

Could not run ‘MSExcelWorkSheetToRecord’
com.wm.app.b2b.server.ServiceException: InputStream of class class org.apache.commons.compress.archivers.zip.ZipFile$1 is not implementing InputStreamStatistics.

I wast many time for that problem and I can’t find solution.

Excel_contentHandler.zip

This might help you to get started.

Since it’s been a while the original code was written i thought i will take a stab with the latest POI jars.

Using latest POI jars… this should handle records from multiple sheets within single Excel doc. The output will be a string list with “||” delimited records from all sheets.

Imports:

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.io.*;

IDataCursor pipelineCursor = pipeline.getCursor();
String fileName =  IDataUtil.getString( pipelineCursor, "fileName" );
pipelineCursor.destroy();

List<String> rowsInSheet=new ArrayList<String>();

FileInputStream fis=null;

try {
fis=new FileInputStream(fileName);

Workbook workbook=new XSSFWorkbook(fis);
int numOfSheets=workbook.getNumberOfSheets();

for (int i = 0; i < numOfSheets; i++) {				
Sheet sheet=workbook.getSheetAt(i);

int numOfRows=sheet.getPhysicalNumberOfRows();

for (int j = 0; j < numOfRows; j++) {
Row row=sheet.getRow(j);					
if(row==null){

}
else{
int numOfCells=row.getPhysicalNumberOfCells();

String lineItem="";

for (int k = 0; k < numOfCells; k++) {
Cell cell=row.getCell(k,row.RETURN_BLANK_AS_NULL);

if(cell==null){
}
else{
int cellType=cell.getCellType();
if(k==0){									
lineItem=sheet.getSheetName()+"||"+formatCellValue(cellType, cell);
}
else{	
lineItem=lineItem+"||"+formatCellValue(cellType, cell);
}
}
}
rowsInSheet.add(lineItem);
lineItem=null;
}
}								
}
IDataUtil.put(pipelineCursor, "results", rowsInSheet.toArray(new String[rowsInSheet.size()]));
} catch (FileNotFoundException e) {
e.printStackTrace();
IDataUtil.put( pipelineCursor, "error", e.toString() );
} catch (IOException e) {
e.printStackTrace();
IDataUtil.put( pipelineCursor, "error", e.toString() );
}
pipelineCursor.destroy();

Shared source code:

private static String formatCellValue(int cellType, Cell cell){
String formattedString=null;

if(cellType==0){			
if(HSSFDateUtil.isCellDateFormatted(cell)){
formattedString=formatDate("YYYY-MM-dd HH:mm:ss.SSS", cell.getDateCellValue());
}
else{
formattedString=formatSSN(String.valueOf(cell.getNumericCellValue()));	
}
}
else if (cellType==1) {
formattedString=cell.getStringCellValue();
if(formattedString.isEmpty()){
formattedString="null";
}
}
else if (cellType==4) {
formattedString=String.valueOf(cell.getBooleanCellValue());
}
else if(cell==null || cellType==Cell.CELL_TYPE_BLANK){
formattedString="null";
}
return formattedString;
}

private static String formatDate(String dateFormat,Date inDate){
DateFormat df=new SimpleDateFormat(dateFormat);
String resultDate=df.format(inDate);
return resultDate;
}