ImportDataTable Method (DataTable- Boolean- Int32- Int32- Int32- Int32- Boolean- String- Boolean)

Aspose.Cells Product Family
1

ImportDataTable Method (DataTable, Boolean, Int32, Int32, Int32, Int32, Boolean, String, Boolean)

how to use above method to convert string column to date format

2

Hi Suthish,


Thank you for contacting Aspose support.

If your requirement is to import the string type data in date format, you can use the mechanism as suggested below.

C#

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”);
Please feel free to write back.
3

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.

4

What if my datacolumn type is string not datetime type

5

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.