Here are some FAQ items that would really help.
- How to export numeric data so it shows up as numbers instead of text (excel thinks it's text) Excel 2010 thinks the data is TEXT.
-- how is data string formatting such as "{0:N2}" in webdatagrid translated to excel output
- What is the best practices for doing things at the spreadsheet level, roll level and cell level.
- How and in what event should a cell be hidden
- How is a cell column formatted
- What is the best practice for removing hyperlink text from a web data grid export to excel For example, a hyperlink value of 999 is shown as a hyperlink w/possible div tags. Need to extract the inner inner html (i.e. 999) - vs <a href="ddddkdkd"><div align left> 9999 </div></a>
Hello jimkennelly ,
By default the exporter exports all the values as string. If you check the value of a cell during CellExported event you’ll notice that even numeric values are set as strings. It basically exports exactly the string representation of what’s in the cell so for example if you have 1.001 you’ll get “1.001” for a value of the WorksheetCell.
You can manually set the value to be a number by getting the corresponding cell from the grid and its value and setting it to the worksheet cell:
e.WorksheetCell.Value =(decimal)this.WebDataGrid1.Rows[e.WorksheetCell.RowIndex-1].Items[e.WorksheetCell.ColumnIndex].Value;
You can also define a format string for worksheet cell:
e.WorksheetCell.CellFormat.FormatString = "0.00";
The best approach to modify values or formats for cells, rows, summaries is during the corresponding Exported event: CellExported, GridFieldCaptionExported, RowExported, SummaryCellExpored and so on. During those event you can get the element that is set on the worksheet for example in the case of the CellExported event you’ll have in the event args: e.WorksheetCell. Once you get it you can modify it. Same with e.WorksheetRow on RowExported event.
You can also use the CellExported event to modify the value when you have a template column. The value would be the html representation of what you have in the cell so you can modify it and pass it back as a new value.
If you need to cancel a specific event for a specific row or cell you can handle the Exporting event: CellExporting, RowExporting and set the e.Cancel=true;
That will prevent the exporting of the corresponding element.
Here’s also a link to some cell styling tips:
http://help.infragistics.com/NetAdvantage/ASPNET/2011.1/CLR4.0/?page=ExcelEngine_Applying_Styles_to_Cells.html
I’m attaching a sample of how I change the value of all cells in a column and apply a format string for them. Please refer to it and let me know if you have any questions or concerns.
Best Regards,
Maya Kirova
Developer Support Engineer
Infragistics, Inc.
http://ko.infragistics.com/support