Excel date format string not applied

470

Vaishnavi Makam posted over 5 years ago

Hi,

I have been trying to set the date format to worksheet cell but it not working. Here's what i have -

row.Cells[columnIndex].CellFormat.FormatString = "mm/dd/yyyy h:mm AM/PM";
row.Cells[columnIndex].CellFormat.Alignment = HorizontalCellAlignment.Right;

Due to this the filter does not work as expected. The column is considered as a text -

But the same when i do by opening the Excel from my system and then set the format, the column is correctly set as Date and the filter looks like below -

The one in the second image is the expected outcome.

Can you please help me fix this ?

Top Replies

470

Offline Vaishnavi Makam posted over 5 years ago in reply to Milko Venkov +1 verified
Hi Milko, Thank you for the sample and apologies for the very late response. Unfortunately i was not able to run your application. but i have managed...view reply »
21795

Offline Milko Venkov posted over 5 years ago in reply to Vaishnavi Makam +1 verified
Hi Vaishnavi, If you are assigning the cells' values you should convert them to dates, so what you did is correct. However, WebExcelExporter does this...view reply »

470

Offline Vaishnavi Makam posted over 5 years ago in reply to Milko Venkov

Got it. But we are not using WebExcelExporter.
Anyway thanks for clarifying that
Cancel
Reply

Cancel

21795

Verified Answer

Offline Milko Venkov posted over 5 years ago in reply to Vaishnavi Makam

Hi Vaishnavi,

If you are assigning the cells' values you should convert them to dates, so what you did is correct. However, WebExcelExporter does this for you out of the box. Here is how I have exported the grid to Excel in my sample:

string fileName = "result";
this.WebExcelExporter1.DownloadName = fileName;
this.WebExcelExporter1.WorkbookFormat = Infragistics.Documents.Excel.WorkbookFormat.Excel2007;

var wb = new Workbook();
var ws = wb.Worksheets.Add("MyGrid");
ws.Columns[INDEX_OF_DATE_COLUMN].CellFormat.FormatString = @"mm/dd/yyyy h:mm AM/PM";
ws.Columns[INDEX_OF_DATE_COLUMN].].CellFormat.Alignment = HorizontalCellAlignment.Right;
this.WebExcelExporter1.Export(this.WebDataGrid1, ws);

As a result WebExcelExporter exports the date column of my grid correctly.

470

Verified Answer
Offline Vaishnavi Makam posted over 5 years ago in reply to Milko Venkov
Hi Milko,

Thank you for the sample and apologies for the very late response.
Unfortunately i was not able to run your application. but i have managed to fix this issue.

Original approach :

Add Format to the cell

Assign the string value to the cell

Fix :

Add Format to the Cell

Parse the string value to the exact date format

Assign the datetime value to the cell.

Not sure if this is supposed to be done so or if this is fixed in the later version.

Thanks again for the quick response

Thanks & Regards,
Vaishnavi M G
Cancel
Reply

Reject Answer

Cancel
21795

Offline Milko Venkov posted over 5 years ago

Hello Vaishnavi,

Following the steps you described I have created a small sample website with a single grid. Then I have exported the grid with WebExcelExporter, have set the FormatString of the date column as you did. In the exported Excel file the format of the column is correct in my sample.

I am sending you my sample to test it on your side. Please run it and let me know what the result of this test is.

Looking forward to your reply.

webGridToExcel.zip
Cancel
Reply

Cancel