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