ImportDataTable Method (DataTable, Boolean, Int32, Int32, Int32, Int32, Boolean, String, Boolean)
how to use above method to convert string column to date format
Hi Suthish,
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add(“Date”, System.Type.GetType(“System.DateTime”));
table.Columns.Add(“another Date”, System.Type.GetType(“System.DateTime”));
for (int i = 0; i < 5; i++)
{
System.Data.DataRow row = table.NewRow();
row[0] = “26-sep-1979”;
row[1] = “12-12-2012”;
table.Rows.Add(row);
}
cells.ImportDataTable(table, false, 0, 0, table.Rows.Count, table.Columns.Count, true, “mm-dd-yyyy”);
workbook.Save(myDir + “output.xls”);
Hi,
And, here is another sample code to demonstrate the method overload for your reference, you may refer to it.
e.g
Sample code:
Workbook excel = new Workbook();
DataTable dt = new DataTable("Products");
DataRow dr = null;
dt.Columns.Add("Col0", typeof(string));
dt.Columns.Add("Col1", typeof(DateTime));
for (int x = 0; x < 1000; x++)
{
dr = dt.NewRow();
dr[0] = x.ToString();
dr[1] = DateTime.Now;
dt.Rows.Add(dr);
}
Worksheet sheet = excel.Worksheets[0];
sheet.Cells.ImportDataTable(dt, true, 0, 0, 1000,2, true, "m/d/yy", true);
excel.Save("e:\\test2\\outtestimpoart1.xls");
Hope, this helps a bit.
Thank you.
What if my datacolumn type is string not datetime type
Hi,
Well, I am afraid, you have to convert the strings to datetime for those columns by yourself using your own codes as strings cannot be converted to DateTime by ImportDataTable method I am afraid. You got to convert your string so called datetime cols before using ImportDataTable method. If there are string representation of DateTime values in the cells, the formatting would not be applied.
Thank you.