tfileinputexcel empty row

[Anonymous]

Hello.
I want to do something which seems quite simple to me.
I have a tfileinputexcel component and a tadvancedXMLoutput.
In my excel file, some rows can sometimes be empty if the data isn't available.
I want that in this case, the XML bounds doesn't appear in the final XML file.
For example:
With a Excel like this

    ID        B    C     D
classic      111   222   333
c_empty      111         333

I want to get this :

<A id="classic">
   <b>111</b>
   <c>222</c>
   <d>333</d>
</A>
<A id="c_empty">
  <b>111</b>
  <d>333</d>
</A>

The schema of the xlsx has the 'nullable' check, but i get a "IllegalStateException: Bad cell type" when it tries to convert the empty cell into 'string/numeric/...'
What is missing ?
Thanks.

[Anonymous]

Hello

The schema of the xlsx has the 'nullable' check, but i get a "IllegalStateException: Bad cell type" when it tries to convert the empty cell into 'string/numeric/...'

Set the data type as string, see my screeshots.
Best regards

shong

[Anonymous]

I thought this should work, but i already have string for all my fields.
It seems that the empty cells in Excel are considered not as 'null' but as "".
So i got the same schema as you but not still the exception...

The URGENCY field is empty in Excel.

[Anonymous]

Hello
Have you still got the same error even you have string for all my fields? Please show us the whole error message.
Best regards

shong

[Anonymous]

Démarrage du jobOP_SK a 11:03 24/12/2009.
 connecting to socket on port 4178
 connected
log4j:WARN No appenders could be found for logger (org.openxml4j.opc).
log4j:WARN Please initialize the log4j system properly.
Exception in component tFileInputExcel_3
java.lang.IllegalStateException: Bad cell type (3)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluate(XSSFFormulaEvaluator.java:102)
	at sk.op_sk_0_1.OP_SK.tFileInputExcel_3Process(OP_SK.java:3147)
	at sk.op_sk_0_1.OP_SK.runJobInTOS(OP_SK.java:5926)
	at sk.op_sk_0_1.OP_SK.main(OP_SK.java:5822)
 disconnected
Job OP_SK terminé à 11:03 24/12/2009. 

With debugger it stops here:

	for (int i = 0; i < 70; i++) {
						if (i + start_column_tFileInputExcel_3 < actual_end_column_tFileInputExcel_3) {
							org.apache.poi.ss.usermodel.Cell cell_tFileInputExcel_3 = row_tFileInputExcel_3
									.getCell(i + start_column_tFileInputExcel_3);
							if (cell_tFileInputExcel_3 != null) {
								switch (evaluator_tFileInputExcel_3.evaluate(
										cell_tFileInputExcel_3).getCellType()) {
								case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
									temp_row_tFileInputExcel_3 = cell_tFileInputExcel_3
											.getRichStringCellValue()
											.getString();
									break;
								case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
									if (org.apache.poi.ss.usermodel.DateUtil
											.isCellDateFormatted(cell_tFileInputExcel_3)) {
										temp_row_tFileInputExcel_3 = cell_tFileInputExcel_3
												.getDateCellValue().toString();
									} else {
...

I updated the previous post with screenshots.

[Anonymous]

Hello guy
I see the first line is the header in you excel file, so do you set the header as 1 on tFileInputExcel componnet?
Best regards

shong

[Anonymous]

I am getting the same message
Exception in component tFileInputExcel_2
java.lang.IllegalStateException: Bad cell type (3)
field type is set as string, it is nullable and also the header is set to 1.
What could be the error?

[Anonymous]

Seems an exception risen Apache POI code (maybe due to inconsistent call)
Just as a test, try to remove all custom formatting and all formulas in excel and retry with all as strings.

[Anonymous]

OT: shong - for curiosity - are you using a specific software to make screenshots and annotation?

[Anonymous]

To emaxt6, i am using snagit 8 to capture screenshots.
Best regards
shong