Converting Datagridview (with date column) to excel fails

Aspose.Cells Product Family
1

Hello,

Iam using Aspose.Cells to export a DataGridView from my C#-Application to excel. The conversion does not work correctly for date-columns assigned to a datatype (see code line 5). When I let line 5 away, the conversion works as desired, but I absolutely need this line. In the attachment you will find a picture of my datagridview and the xls-file created by Aspose.Cell. Could you please tell me what I have to do extra to make this work?

Here is the code Iam using:

1. DataTable dt = new DataTable();

2. DataColumn dci = new DataColumn(“Index1”);
3. dt.Columns.Add(dci);

4. DataColumn dca = new DataColumn(“ArchiveDate”);
5. dca.DataType = System.Type.GetType(“System.DateTime”); // To enable user to sort the date-column correctly
6. dt.Columns.Add(dcVers);

7. DataRow dr = dt.NewRow();
8. dr[“Index1”] = “buggy date follows”;
9. dr["“ArchiveDate”"] = “16.08.2010”; //German date format

10. BindingSource bs = new BindingSource();
11. bs.DataSource = dt;
12. myDataGridView.DataSource = bs;

13. Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
14. Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
15. sheet.Name = “Report”;

16. Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;
17. cells.Clear();

18. cells.ImportDataTable((DataTable)(((BindingSource)myDataGridView.DataSource).DataSource), true, “A1”);

19. workbook.Save(“C:\Temp\Report.xls”);


Thanks in advance
Scheu

2

Hi Andreas,

This is not an issue. In fact you are missing assignment of correct German date format. You have to include the following lines of code:

DateTime objdt = DateTime.Now;
String.Format("{0:d.M.yyyy HH:mm:ss}", objdt);


Code snippet:

DataTable dt = new DataTable();
DataColumn dci = new DataColumn("Index1");
dt.Columns.Add(dci);

DataColumn dca = new DataColumn("ArchiveDate");
dca.DataType = System.Type.GetType("System.DateTime");

// To enable user to sort the date-column correctly
dt.Columns.Add(dca);

DataRow dr = dt.NewRow();
dr["Index1"] = "buggy date follows";
dr["ArchiveDate"] = objdt; // "16.08.2010"; //German date format

BindingSource bs = new BindingSource();
bs.DataSource = dt;
dataGridView1.DataSource = bs;

Thanks,

3

The above code is only an example. In my application Iam doing the right date conversions String.Format("{0:dd.MM.yyyy}", dateTime)
The issue is something else (as said above Line5 causes the problem)!!

4

Hi Andreas,

Please send us a sample project which contains error. This will help us finding the issue.

Thanks,

5

In the attachment you will find a project (with the exactly the same code as I have already sent!!).
You will need to add Aspose.Cells.dll to lib and create the file AsposeTotal.txt in the folder Resources (to avoid attaching the license file). Clicking the button (Export to Excel) will create a xls file with an incorrect date!

6

Hi Andreas,

You have to use System.Globalization.CultureInfo object in order to work with German date format. Following code snippet will help.


Code snippet:

string sdt = "16.08.2010";
System.Globalization.CultureInfo nfo = new System.Globalization.CultureInfo("de-DE");
DateTime objGrmndate = DateTime.Parse(sdt, nfo);

DataTable dt = new DataTable();

DataColumn dci = new DataColumn("Index1");
dt.Columns.Add(dci);

DataColumn dca = new DataColumn("ArchiveDate");
dca.DataType = System.Type.GetType("System.DateTime"); // To enable user to sort the date-column correctly
dt.Columns.Add(dca);

DataRow dr1 = dt.NewRow();
dr1["Index1"] = "buggy date follows";

dr1["ArchiveDate"] = objGrmndate;

dt.Rows.Add(dr1);

BindingSource bs = new BindingSource();
bs.Clear();
bs.DataSource = dt;

bindingNavigator1.BindingSource = bs;
dataGridView1.DataSource = bs;

Thanks,

7

Dear Salman,

Iam a professional programmer and of course Iam using the CultureInfo at the Initialization of my Application. Please REPRODUCE this BUG with help of the project I have sent to you and share a bugfix with me!! I do not have that much time and Iam waiting for a bugfix!!

8

Hi Andreas,

Please use the following method:

public int ImportDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow, int firstColumn, int rowNumber, int columnNumber, bool insertRows, string dateFormatString)
If the value is date time, it will format the cell with the given "dateFormatString".

For more information, please follow the link below:
https://docs.aspose.com/display/cellsnet/Import+Data+into+Worksheet

Thanks,

9

I think you are not understanding my problem (please read carefully the first post again). I have tried all the overloaded methods of ImportDataTable and the bug is still there! Moreover your proposition of the ImportDataTable is not the appropriate one for me, since I have I datagridview with many columns and different types (int, decimal, date, time, string…), which I want to export to excel.

If Iam missing something (and I don´t think so) please make the quick changes in the project I have sent to you and share it with me, after having tested it.

To recap:
DataColumn dc = new DataColumn(“BadDateRepres”)
dc.DataType = typeof(DateTime); // Here is the problem. Without this line everything works fine.


Example:

Gridview representation: BadDateRepres
17.08.2010

Excel produced by Aspose : BadDateRepres

40407 // Aspose.Cells Bug (bad representation)


I hope I will become this time a reasonable answer and not some code snippets that I have tried long ago!



10

Hi Andreas,

DataColumn dc = new DataColumn("BadDateRepres")
dc.DataType = typeof(DateTime); // Here is the problem. Without this line everything works fine.

There is no issue with the above. You may use System.DateTime as DataType or set the CultureInfo. The line of code which needs to be updated is given below:

cells.ImportDataTable((DataTable)(((BindingSource)dataGridView1.DataSource).DataSource), true, 0, 0, 1, 2, true, "dd.mm.yyyy");

The fact is when we pass the date string format to ImportDataTable it will convert the date into the specified format.

Thanks,

11

Iam not sure why I have lost my time to share with you a demo project to reproduce the issue, when you do not look at it. I have set both System.DateTime AND Cultureinfo, and the problem is still there!

12

Hi Andreas,

Sorry for the Inconvenience.

Can you precisely tell me that you are facing issue at what level? I am listing level below
1. Report.xls : Date format is not German?
2. Form1: Date format in the datagrid is not German?

I am attaching the project that shows your desired date format at both level. in the exported report and in the Form1 data grid.

If still problem exist that you are facing then please attach the screen shot.

I am attaching screen shots and project file.
I made the following change in the Form1_Load(,)
==============
using System.Threading;

System.Globalization.CultureInfo nfo = new System.Globalization.CultureInfo("de-DE");
Thread.CurrentThread.CurrentCulture = nfo;
==============

thanks,

13

As you can see in the attached screen shot, Iam using your code and Iam still getting an incorrect date representation in the exported Report.xls (40406). In the Datagridview the date is (16.08.2010).
Can you imagine why this is happening?


14

Hi
thanks for your reply
Dear it is the matter of format setting of that cell. The date is not formatted according to your desired format (German).

Please make sure that u r using the following Green line of code in the function definition toolStripButton1_Click(object sender, EventArgs e) instead of red line of code

=======================

cells.ImportDataTable((DataTable)(((BindingSource)dataGridView1.DataSource).DataSource), true, 0, 0, 1, 2, true, “dd.mm.yyyy”);

//cells.ImportDataTable((DataTable)(((BindingSource)dataGridView1.DataSource).DataSource), true, “A1”);

=======================

Red line of code produces the output as you told to me, and the Green line of code produces the output as i told to you in my previous message.

Just make sure that you are using the green line of code in the above stated function which is exporting the file.

Let me know which line of code you are using?

thanks

15

the green line works as needed but only for s specified cell (a combination of rownumber and colnumber) and thus is not the appropriate method-overload for my case.

In my case the datagridview is a dynamic one, meaning that the columns and rows change from time to time (depending on searchcriteras). That is, I don´t know in advance which column has which type, and sometimes I have a row with 3 or more date columns.
What I need is a cells.ImportDataTable which exports a complete datagridview with many rows and many columns (the columns have various datatypes including string, time, datetime, number…). Is there a way to do this? Or how can I export a datagridview with 2 rows and 3 data columns using your mensioned ImportDataTable?

16

Hi Andreas,

You can use the same cells.ImportDataTable() method in the following way:

cells.ImportDataTable(objDT, true, 0, 0, objDT.Rows.Count, objDT.Columns.Count, true, "dd.mm.yyyy");

I am attaching modified sample project and a screen shot. If still there any issue, feel free to contact us.

Thanks,

17

Thank you for this solution. It works fine!