Free Support Forum - aspose.com

Cells.ImportResultSet with smalldatetime field from SQL Server using Aspose.Cells for Java

When I use the importResultSet function to import a query with smalldatetime fields, i get a Calendar exception. If I replace those fields with text it imports fine. Stack Trace follows:

Exception in thread "main" java.lang.NullPointerException
at java.util.Calendar.setTime(Calendar.java:1070)
at com.aspose.cells.Cells.importResultSet(Unknown Source)
at com.aspose.cells.Cells.importResultSet(Unknown Source)
at mil.fmsuite.statusoffunds.DataSet.exportWithPivot(DataSet.java:671)
at mil.fmsuite.statusoffunds.DataSet.exportFile(DataSet.java:421)
at mil.fmsuite.test.Main.main(Main.java:100)

Hi,
Please the fix in
http://www.aspose.com/Community/forums/ShowThread.aspx?PostID=58564#59118

I don't get an exception now, which is good. But the dates all display as the same float value, which is bad. I used the sample code below with a simple SQL Server stored procedure shown below that. In this example, all four dates are being set in Excel as 38831.90625, which shows as "4/24/2006 9:45:00 PM" when fomatted as a date. That date does happen to be the value of the last record. Is the date being cached on your end somehow and set to all rows?

public static final void test() throws SQLException {

String filename = "C:/datetime.xls";
Workbook wkb = new Workbook();

// get reference to cells of first sheet
Worksheet wks = wkb.getWorksheets().getSheet(0);
Cells cells = wks.getCells();

// Get database connection
Connection conn = Database.getConnection();

// Create a statement for the import
CallableStatement test = conn.prepareCall("{call spTestDates()}");
ResultSet testRS = test.executeQuery();

// test the import
cells.importResultSet(testRS, 0, 0, false);

testRS.close();
test.close();

try
{
wkb.save( filename );
}
catch(Exception e) {
}
}

Use that code with this stored procedure:

CREATE PROCEDURE spTestDates
AS

CREATE TABLE #TMP_DATES (
MY_ID int,
MY_DT smalldatetime
)

INSERT INTO #TMP_DATES VALUES ( 1, '1 JAN 2006 08:45' )
INSERT INTO #TMP_DATES VALUES ( 2, '15 FEB 2006 14:17' )
INSERT INTO #TMP_DATES VALUES ( 3, '19 MAR 2006 19:00' )
INSERT INTO #TMP_DATES VALUES ( 4, '24 APR 2006 21:45' )

SELECT MY_ID, MY_DT FROM #TMP_DATES

DROP TABLE #TMP_DATES
GO

Hi,

You can set the style of the column to format number now.

We will add Cells.importResultSet(.....,String customDateFormatString,boolean convertStringToNumber) method to apply date format to the cells which contain date value.Please try the fix in

http://www.aspose.com/Community/forums/thread/59079.aspx

The problem with this was not the formatting. If I set a date style, the numbers do show properly. (Although it would be even better with more date/time formats.

The problem is that is puts the same value for ALL date fields. It appears to use the last date value and write that to all date cells. Look at the attached sheet, which shows the results from importResultSet(). The procedure returns four different dates, but the spreasheet has the same value for all four rows. That value happens to be the same value found on the last row of the resultSet.

I think I owe Warren a pizza by now... Thanks for all the quick fixes so far.