I have a WebDataGrid that I'm exporting to Excel using the WebExcelExporter. The grid contains an Amount field and 2 Date fields. I have been trying to format those columns. The main export function has these lines:
wgReportExcelExporter.DisableCellValueFormatting = true; wgReportExcelExporter.Export(wgReport);
I also have a function which handles OnGridFieldCaptionExported, in which I am attempting to format the columns like this:
if (String.Compare(e.GridCell.FieldKey, "Total", true) == 0) e.Worksheet.Columns[e.CurrentColumnIndex].CellFormat.FormatString = "#,##0.00";else if (e.GridCell.FieldKey.IndexOf("Date") >= 0) e.Worksheet.Columns[e.CurrentColumnIndex].CellFormat.FormatString = "m/d/yyyy";
They both have problems. The Total field will ONLY format numbers in the thousands - once the numbers reach 1,000,000.00 the formatting no longer works. (I have tried using a FormatString of "#,###,##0.00" to no avail.)
The two date fields APPEAR to be formatted - if you open the Excel file and click on the cell, the Category is correctly listed as "Date". However, if you attempt to sort by the column it is apparent that the value is still being perceived as a String by Excel. (You can only sort "A to Z", not "Oldest to Newest".) If I "edit" the cell by clicking the enter button (I do NOT change the value, just hit enter) then it "becomes" a date instead of a string.
What am I missing here??
If I can assist you with anything else please do not hesitate to contact me.
Sorry, I got pulled into a different project. I'll be looking into this further next week, but my immediate question is, do I have to set the formatting in the Infragistics.Documents.Excel.Workbook object? At the moment, my formatting is done when the header cells are exported, because I don't actually know what the columns are. (The grid may contain one of several reports, all with different columns. I search for the word "Date" or "Amount" in each column and format appropriately there.) If I'm formatting in the Workbook though, I'm not sure how to do that when the columns are exported.
Hello,
I am still following up this case.
If you handle Exported event and in there to change the format string also.
protected void WebExcelExporter1_Exported(object sender, Infragistics.Web.UI.GridControls.ExcelExportedEventArgs e){ e.Worksheet.Columns[1].CellFormat.FormatString = "$ 0.00"; e.Worksheet.Columns[2].CellFormat.FormatString = "dd/mm/yyyy";}
I have created a sample for you in order to show you this approach. About your question, this is how you set the formatting through the Workbook.