Import datatable- dates not recognized

Aspose.Cells Product Family
1

Hi,

When i use the ImportDataTable method, and there is a column that contains datefields, excel concatenates the values like so : 06062006, is there a way around this ?

thanks, Tom.

2

Hi Tom,

What's the type of that column? Is it DateTime?

3

yep , datetime

and you have a

Server Error in '/Header' Application.

Login failed for user 'aspose'.

on the aspose site

4

Hi,

Well there are two ways to solve your problem.

1. You have to manually change the format of all those cells in the columns to DateTime in your workbook / Excel file.

2. Aspose.Cells also offer some overloaded version of ImportDataTable methods for your desired task e.g.,

public int ImportDataTable(
DataTable dataTable,
bool isFieldNameShown,
int firstRow,
int firstColumn,
int rowNumber,
int columnNumber,
bool insertRows,
string dateFormatString
);

Sample code:

(I use Northwind Database Table Employees which has HireDate, Birthdate DateTime fields):

leDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\Northwind.mdb");

con.Open();

OleDbCommand cmd = new OleDbCommand("Select * from Employees",con);

OleDbDataAdapter da = new OleDbDataAdapter();

da.SelectCommand = cmd;

DataSet ds = new DataSet();

da.Fill(ds,"Employees");

Workbook workbook = new Workbook();

workbook.Worksheets[0].Cells.ImportDataTable(ds.Tables["Employees"],true,0,0,15,15,false,"m/d/yy");

workbook.Save("d:\\importdt.xls");

(Note: if your table is a huge one, this can slows down the performance)

And thanks for pointing out the error, we will fix the Aspose site soon.

Regards

Amjad Sahi

Aspose Nanjing Team

5

hmm, the problem is that i have 3 columns with dates. and the columnnumber can change ...

ideally there would be a bool preserveDates with the importDataTable method or something ?

kind regards, Tom.

6

Hi Tom,

In MS Excel, DateTime values is saved as number and displayed according to number format. So you can import the data first then format the columns as your expected date time format.

7

Sorry Laurence, can you explain that part some more please ?

8

Hi Tom,

Please try this piece of code:

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Date1", typeof(DateTime));
dt.Columns.Add("Date2", typeof(DateTime));
dt.Columns.Add("Date3", typeof(DateTime));

for(int i = 0; i < 5; i ++)
{
DataRow row = dt.NewRow();
row[0] = i;
row[1] = DateTime.Now.AddDays(i);
row[2] = DateTime.Now.AddDays(2 * i + 1);
row[3] = DateTime.Now.AddDays(3 * i + 1);

dt.Rows.Add(row);
}

Workbook wb = new Workbook();
Cells cells = wb.Worksheets[0].Cells;

Aspose.Cells.Style style1 = wb.Styles[wb.Styles.Add()];
style1.Custom = "m/d/yy";

Aspose.Cells.Style style2 = wb.Styles[wb.Styles.Add()];
style2.Custom = "m/d/yy h:mm:ss";

Aspose.Cells.Style style3 = wb.Styles[wb.Styles.Add()];
style3.Custom = "yyyy/mm/dd";

StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;

//Sets number format to display datetime

cells.Columns[1].ApplyStyle(style1, flag);
cells.Columns[2].ApplyStyle(style2, flag);
cells.Columns[3].ApplyStyle(style3, flag);

//Extends column width to fully show datetime value
cells.SetColumnWidth(2, 20);
cells.SetColumnWidth(3, 20);

cells.ImportDataTable(dt, false, 0, 0, dt.Rows.Count, dt.Columns.Count, true);

wb.Save("d:\\test\\abc.xls");

I attach the output file here.

9

super,

But the problem is that i cannot say which columns are the datetime columns :(

i was thinking of a check of every first value of a column = bad solution.

10

You can use DataColumn.DataType property to check if a column is datetime type.

11

works perfectly, thank you very much

12

I’m looking for a pointer to a good chart for this dateFormatString.

I’m having difficulty achieving the following format, in particular: 12/12/06 1:32 PM.

I don’t seem to be able to get a 12 hour cycle, nor the AM/PM designator.

Thanks.

13

Hi,

Thanks for considering Aspose.

I am not completely clear about your need. Do you want to format DataLabels or TickLabels of the values axis. I think you can utilize Number and NumberFormat properties of DataLabels and TickLables for your need (for setting date formats).

Thank you.

Thank you.

14

I am importing a data table into an Excel 2007 document using the method suggested earlier in this thread:

public int ImportDataTable(
DataTable dataTable,
bool isFieldNameShown,
int firstRow,
int firstColumn,
int rowNumber,
int columnNumber,
bool insertRows,
string dateFormatString
);

Is there a chart available that describes the options for this dateFormatString? I haven’t had much luck attempting custom strings I use in other languages. How would I achieve the format I mentioned in my original post, namely, a 12 hour time component?

Thank You.

15

Hi,

Thanks for considering Aspose.

Well, you may check:

Moreover, you may right-click on a cell and click Format Cells... and check Date related Custom format strings for your need.

Thank you.