Cells.importResultSet customDateFormatString

Aspose.Cells Product Family
1

Hello,

I am importing a resultset directly into a spreadsheet but some of the columns are of type DATE. I was using the simple 4-arg importResultSet function (rs, row, column, boolheaders). This formats dates as integers, e.g. “38437.33883”. I want this to switch to the Date format. I saw in the API that there is another importResultSet that includes another String argument, “customDateFormatString”.

There is no documentation about what this does or how it’s used. I tried passing it “yyyy-mm-dd” but it didn’t change anything. How do I use this argument?

Thanks
Sam

2

Hi,

I use the following sample code with the attached latest Aspose.Cells for Java version and it works fine. I used Northwind MS Access sample database table “Employees”.

Sample code:
//Create a new Workbook.

Workbook workbook =new Workbook();



//Define the Access Database URL String constant.

final String DB_URL = “jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=e:\test\Northwind.mdb”;



//Load the JDBC-ODBC bridge driver.

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);



//Define the connection.

Connection conn = DriverManager.getConnection(DB_URL);



//Create the Statement with the specified cursor type and lock option.

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);



//Get the ResultSet executing the SQL statement.

ResultSet rs = stmt.executeQuery(“select EmployeeID, BirthDate from Employees”);



//Fetch the first worksheet.

Worksheet worksheet = workbook.getWorksheets().getSheet(0);



//Import the ResultSet to the worksheet.

worksheet.getCells().importResultSet(rs,0,0,true, “yyyy-mm-dd”, true);



//Save the excel file.

workbook.save(“d:\Files\importdata.xls”);

Please make sure the type of the column should be Date/Time. Please try the attached version. If you still find the issue, kindly do create sample code with files here (same as mine). We will check it soon.

Thank you.

3

Hello Amjad,


I am doing something similar. I am getting a resultset and then processing it in this way for testing:

…
Statement stmt = con.createStatement();
ResultSet rs = null;
rs = stmt.executeQuery("Select * from " + schema + “.” + table);
currentCells.importResultSet(rs,currentRow,0,true, “yyyy-mm-dd”, true);
…

The datatype in the Oracle DB is Date. Does this function simply determine which columns are of type Date and formats them as such in the excel export? It does not seem to be doing that at all on my end.
4

Amjad,


I see now what was happening. I was applying another style to the cells after the import, and instead of getting the cell’s style and applying the color/font/border settings I wanted, I was doing workbook.createStyle(), which effectively overwrote whatever datatype settings the import was doing. Thanks for your help.
5

Hi,

I think your problem is sorted out now, good to know that :slight_smile: