This is a migrated thread and some comments may be shown as answers.

Excel output formulas in Group Rows and Footer

1 Answer 91 Views

Grid

This is a migrated thread and some comments may be shown as answers.

David asked on 28 Jan 2013, 04:53 AM

ExcelGrid.png

I output RadGrid to Excel <Excel Format="Html"></Excel>

Here are my issues; (I have attached a picture to help illustrate my questions)

Is there a way to know what row the Grid is currently writing to in Excel? Something like "Excel.CurrentRowNumber"?

Reason: I'm writing formulas to various cells during RadGrid1_ExcelExportCellFormatting but the first row of data it encounters gets written to Excel row 4 (because of the Grid Header). I tried using item.ItemIndex but that starts off at 0 (or 1 maybe, i don't recall). So my formulas are all messed up since I don't know what the current row in Excel is. I need to write out formulas like C4+D4 but I end up with C1+D1.
The problem gets worse as GroupByExpressions are encountered. Each time my data is grouped another 2 rows get written to Excel and now my current row is off by even more.

2. Is there a way to interact with the output to Excel when GroupByExpressions are encountered? I'd like to adjust the cells during that time such as inserting formulas to sum up all the rows in a section for each column. Same question regarding the GridFooterItem..? I'd like to put "grand total formulas" in those various columns.

3. I can't get rid of column A. There is a blank column that gets written out (see my picture). It seems to be known as "column" so I tried hiding it such as:

RadGrid1.MasterTableView.GetColumn("column").Display = false;

but no luck. This is only a minor annoyance, I could live with this as the formula questions above are the more important showstoppers.

Thank you!!

1 Answer, 1 is accepted

answered on 31 Jan 2013, 12:50 PM

Hello David,

Straight to the answers:

1) You can put a simple global counter variable in the ItemDataBound event. That said, you could use this event instead of ExportCellFormatting if this is more convenient for your scenario.
Note that you have to use a flag to ensure that the export-specific code runs only during export.
Let me know if you need help with this step.

2) The same approach can be used here also. Handle the aforementioned event, check the item type and add the formula.

3) Could you please try setting the HideStructureColumns property to true?

Best regards,
Daniel
the Telerik team

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.