importResultSet substitutes empty db cell with zero

Aspose.Cells Product Family
1

With a DB table:
A B
10 1.2
20
30 1.4

the filling method:
worksheet.getCells().importResultSet(localResulset, 0, 0, true, dateFormat, false);
substitutes the value of B of the second row with zero instead to leave it blank.

How can I solve it?

2

@f.superga,

Thanks for the details.

I have tested your scenario/ case using our latest version/fix: Aspose.Cells for Java v18.8.x (please try it if you are not already using it). I created a simple MS Access database having a table in it. The table has two fields (with Number and double data types). I inserted the values into the table as per your matrix. Now I used the following sample code with the attached database file. The output Excel file (attached) is fine tuned, there is no “0” value for the blank field value:
e.g
Sample code:

// Create Connection object - connect to Microsoft Access Students Database
		java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:ucanaccess://" + "f:\\files\\Students1.accdb");

		java.sql.ResultSet rs = null;
		
		// Create SQL Statement with Connection object
		java.sql.Statement st = conn.createStatement(rs.TYPE_SCROLL_INSENSITIVE, rs.CONCUR_READ_ONLY);
		

		// Execute SQL Query and obtain ResultSet
		rs = st.executeQuery("SELECT Number1, Value1 FROM Table1");

		
				
		int rowCount = 0;
		while(rs.next()){
		     rowCount++;
		}		    
		
		System.out.println(rowCount);
		
		
		rs.beforeFirst();
		
		
		// Create workbook object
		Workbook wb = new Workbook();
		// Access first worksheet
		Worksheet ws = wb.getWorksheets().get(0);

		// Access cells collection
		Cells cells = ws.getCells();

		// Create import table options
		ImportTableOptions options = new ImportTableOptions();
		options.setInsertRows(true);
		options.setFieldNameShown(true);
		
		 		
		cells.importResultSet(rs, 0, 0, options);

		// Autofit columns
		ws.autoFitColumns();

		// Save the workbook
		wb.save("f:\\files\\out1.xlsx");

If you still find the issue, kindly do provide MS Access database file (as I provided in the zipped archive) and paste your sample code (runnable) and attach your output file to reproduce the issue on our end, we will check it soon.
files1.zip (24.0 KB)

3

Thank you for your response.

I am using Specification-Version: 17.3.0 with Oracle 10g

I think the only way with this version is to do not use importResultSet method but fill excel cell by cell. Isn’t it?

Fulvio

4

@f.superga,

Did you try your scenario/ case using our latest version (v18.8) and how it goes?

If it works fine with latest version but it does not work with older version, then I guess it might be an issue with the older version that you are using.

If the latest version also does not work for your scenario/ case, kindly do provide us sample Java program (runnable) with sample files as I provided the code segment and template files in my previous post to show the issue, we will check it soon.

5

Yes, it work with latest version.

Thank you.

6

@f.superga,

So, it means there was a bug in older version that you are using, so you should try more recent version to figure out your issue. Alternatively, if you want to stick to your older version, you may import data cell by cell as it works you mentioned.