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??
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.
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.
If I can assist you with anything else please do not hesitate to contact me.
Hi Allison,
Thank you for your patience.
As I understand when you export some data into excel file there are some column format issues with it. I have created a sample in order to investigate this further for you. You can see the exported file attached also. In the file I have two columns Number and Date2 which have DataFormatString like the one that you've mentioned, although I couldn't reproduce the issues. I am able to sort Oldest to Newest and also the format of Number column is correct even if the value reach 1,000,000.00.
Could you please make the necessary changes to the sample in order to invoke the issues. This will be highly appreciated.
Code snippet:
Could you please also have a look at the following discussion regarding using of DisableCellValueFormatting property:
http://ko.infragistics.com/community/forums/t/60904.aspx
This property disables formatting of the cell value when grid BoundDataField or UnboundField has DataFormatString specified.
Looking forward to hearing from you.