Hi,
i am trying to parse the new xlsx-Excel-Format by using a slightly changed version of the MSExcelDocumentToRecord-Java-Service and relying on the new org.apache.poi-3.5.beta3 package. Unfortunately it doesn’t work. The code looks like this:
// Get file name from pipeline
IDataCursor pipelineCursor = pipeline.getCursor();
String file_name = “”;
file_name = IDataUtil.getString( pipelineCursor, “file_name” );
String date_format = null;
date_format = IDataUtil.getString( pipelineCursor, “date_format” );
String time_format = null;
time_format = IDataUtil.getString( pipelineCursor, “time_format” );
String number_format = null;
//number_format = IDataUtil.getString( pipelineCursor, “number_format” );
DecimalFormat df = (DecimalFormat)DecimalFormat.getInstance(Locale.GERMAN);
df.applyPattern(“.########”);
if (date_format == null)
{
date_format = “yyyy-MM-dd”;
}
SimpleDateFormat dateFormat = new SimpleDateFormat(date_format);
if (time_format == null)
{
time_format = “HH:mm:ss”;
}
SimpleDateFormat timeFormat = new SimpleDateFormat(time_format);
pipelineCursor.destroy();
IData dataRows[] = null;
IData excelWorksheet = IDataFactory.create();
int row_cnt = 0;
try
{
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file_name));
XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(0);
XSSFRow row = null;
XSSFCell cell = null;
// Read Excel data and create dynamic record based on fileds
row_cnt = sheet.getLastRowNum();
dataRows = new IData[row_cnt+1];
// read and process rows
for (int i = 0; i <= row_cnt; i++)
{
// process row i
row = (XSSFRow) sheet.getRow(i);
// create instance of data row representing this row
dataRows[i] = IDataFactory.create();
IDataCursor dataRowCursor = dataRows[i].getCursor();
if ((row!=null) && (row.getLastCellNum()>0))
{
// create and fill array for values
String[] cellValues = new String[row.getLastCellNum()];
int isRowEmpty = 0;
for (int j = 0; j < row.getLastCellNum(); j++)
{
cell = (XSSFCell) row.getCell((short)j);
if (cell != null)
{
int type = cell.getCellType();
switch(type)
{
case XSSFCell.CELL_TYPE_STRING:
cellValues[j]=cell.getRichStringCellValue().getString();
if (cellValues[j].length() > 0) {
isRowEmpty = 1;
}
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell))
{
java.util.Date dateValue = cell.getDateCellValue();
Integer year = dateValue.getYear(); // or getYear
if (year != -1)
{
cellValues[j]=dateFormat.format(dateValue);
}
else
{
cellValues[j]=timeFormat.format(dateValue);
}
isRowEmpty = 1;
}
else
{
//cellValues[j]=Double.toString(cell.getNumericCellValue());
cellValues[j]=df.format(cell.getNumericCellValue());
isRowEmpty = 1;
}
break;
case XSSFCell.CELL_TYPE_BLANK:
cellValues[j]=“”;
}
}
}
// convert array to data row
IDataUtil.put(dataRowCursor, “cells”, cellValues);
if (isRowEmpty == 0)
{
IDataUtil.put(dataRowCursor, “rowEmpty”, “true”);
}
else
{
IDataUtil.put(dataRowCursor, “rowEmpty”, “false”);
}
}
dataRowCursor.destroy();
}
}
catch (Exception e)
{
throw new ServiceException(e);
}
// Assemble generated rows in excelWorksheet object
IDataCursor excelWorksheetCursor = excelWorksheet.getCursor();
IDataUtil.put(excelWorksheetCursor, “dataRows”, dataRows);
excelWorksheetCursor.destroy();
// put data into pipeline
IDataCursor pipelineCursorOut = pipeline.getCursor();
IDataUtil.put(pipelineCursorOut,“excelWorksheet”, excelWorksheet );
IDataUtil.put(pipelineCursorOut, “row_cnt”, row_cnt);
pipelineCursorOut.destroy();
Does anybody have an idea how to solve this problem. Many Thanks in advance!
With best Regards,
Benedikt
#Integration-Server-and-ESB#Flow-and-Java-services#webMethods