Excel date format string not applied

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 ?

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

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 [emoticon:c4563cd7d5574777a71c318021cbbcc8]

Thanks & Regards,
Vaishnavi M G

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.

Got it. But we are not using WebExcelExporter. 
Anyway thanks for clarifying that [emoticon:c4563cd7d5574777a71c318021cbbcc8]