Error with get xlsx file

, ,
1

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.

2

Excel_contentHandler.zip

This might help you to get started.

3

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;
	}