I have a fairly sophisticated grid with 22 visible columns. The data source is a collection derived from List<T>. There are more columns in the records than displayed. The grid allows for grouping, filtering, tri-mode multi-column sorting, and editing (no paging). The display of the grid changes based on flags which are re-evaluated at each Ajax postback.
So based on that, is it clear which Excel export format to use? : HTML, SpreadsheetML, or BIFF
I don't understand exactly how RadGrid goes about generating a spreadsheet document. In RadGrid_ItemCommand, I check as follows:
What do I do with that isExcelExport information now that I know what the user wants? The code looks like it goes through the standard event cycle, but generates different format output rather than building the web form with related controls. Is that accurate? Do I need to populate the following in ItemCreated or ItemDataBound?
And do I need to do something to wrap up in RadGrid_PreRender or elsewhere?
I'm familiar with SpreadsheetML, and I develop Excel Addins, so I'm familiar with the internals, but I'd prefer to let the RadGrid do as much for us as possible. For example, can Excel headers and other styling be inherited from the grid?
I just need to know how much control I need to exercise over the creation process of the workbook, worksheet, and individual rows, columns, and cells. I don't see this spelled out anywhere as to what needs to be done or where for each output type.
And can we control the workbook headers, setting Author and other properties which are available to both ML and binary files?
Thanks!!
So based on that, is it clear which Excel export format to use? : HTML, SpreadsheetML, or BIFF
I don't understand exactly how RadGrid goes about generating a spreadsheet document. In RadGrid_ItemCommand, I check as follows:
if (e.CommandName == RadGrid.ExportToExcelCommandName) isExcelExport = true;What do I do with that isExcelExport information now that I know what the user wants? The code looks like it goes through the standard event cycle, but generates different format output rather than building the web form with related controls. Is that accurate? Do I need to populate the following in ItemCreated or ItemDataBound?
if (isExcelExport){ if (e.Item is GridHeaderItem) { } if (e.Item is GridDataItem) { } if (e.Item is GridFooterItem) { } return;}And do I need to do something to wrap up in RadGrid_PreRender or elsewhere?
I'm familiar with SpreadsheetML, and I develop Excel Addins, so I'm familiar with the internals, but I'd prefer to let the RadGrid do as much for us as possible. For example, can Excel headers and other styling be inherited from the grid?
I just need to know how much control I need to exercise over the creation process of the workbook, worksheet, and individual rows, columns, and cells. I don't see this spelled out anywhere as to what needs to be done or where for each output type.
And can we control the workbook headers, setting Author and other properties which are available to both ML and binary files?
Thanks!!
5 Answers, 1 is accepted
0
Vasssek
Top achievements
Rank 1
Rank 1
answered on 18 Jul 2012, 10:14 AM
Hello,
Did you check this RadGrid documentation section ? If I would recommend you, try to use ExcelML export format. Then you can customize styles for headers, columns, or cells... A new Excel BIFF export format as I know, doesn't support hierarchy or grouping, yet.
Did you check this RadGrid documentation section ? If I would recommend you, try to use ExcelML export format. Then you can customize styles for headers, columns, or cells... A new Excel BIFF export format as I know, doesn't support hierarchy or grouping, yet.
http://www.telerik.com/help/aspnet-ajax/grid-excelml-export.html
Export to Excel ML example is here:
http://demos.telerik.com/aspnet-ajax/grid/examples/generalfeatures/excelmlexport/defaultcs.aspx
Vasssek
0
TonyG
Top achievements
Rank 1
Rank 1
answered on 18 Jul 2012, 05:31 PM
Vasssek, I actually spent a few hours on those pages yesterday and found myself no more enlightened than when I started. The RadControls code for generating ExcelML is more complex than hardcoding an XML document.
I guess what I'm looking for is something from Telerik to compare each of their methods, and better examples than what we see. Maybe I'm wrong but it's my perception that the methods available are each deficient in some way, forcing is to choose a method that's adequate for a specific task and not one that's really right. For example, "RadControls" itself is the right tool for our development needs compared to standard ASP.NET development. But we would be forced to make choices if the RadGrid didn't support grouping or filtering or other significant features.
Again, I could be wrong, but the export tools seem to be missing properties to control the Excel document (beyond the limitations of SpreadsheetML itself). And while there are a few examples, the documentation includes a method that is deprecated, and doesn't tell us where the events fire in the life cycle. I have time to do research and experiments on the tools of my trade. I don't have time for random guesswork to investigate a product when the product should have adequate documentation (and I'm only talking about this specific export functionality).
Until I can find more compelling information, for now I'm more inclined to write my own XML, looping on rows and crafting cell nodes for each column. At least this code can be used in non-RadControls projects.
More comments appreciated, especially from a blogger at Telerik or someone from technical marketing or documentation.
Thanks!
I guess what I'm looking for is something from Telerik to compare each of their methods, and better examples than what we see. Maybe I'm wrong but it's my perception that the methods available are each deficient in some way, forcing is to choose a method that's adequate for a specific task and not one that's really right. For example, "RadControls" itself is the right tool for our development needs compared to standard ASP.NET development. But we would be forced to make choices if the RadGrid didn't support grouping or filtering or other significant features.
Again, I could be wrong, but the export tools seem to be missing properties to control the Excel document (beyond the limitations of SpreadsheetML itself). And while there are a few examples, the documentation includes a method that is deprecated, and doesn't tell us where the events fire in the life cycle. I have time to do research and experiments on the tools of my trade. I don't have time for random guesswork to investigate a product when the product should have adequate documentation (and I'm only talking about this specific export functionality).
Until I can find more compelling information, for now I'm more inclined to write my own XML, looping on rows and crafting cell nodes for each column. At least this code can be used in non-RadControls projects.
More comments appreciated, especially from a blogger at Telerik or someone from technical marketing or documentation.
Thanks!
0
Hello Tony,
HTML format on the other hand relies on the HTML code that is rendered by the RadGrid.
Put simply and considering the above, it is virtually impossible to make these formats support the same features and work in exactly the same way.
Speaking of the events, we will happily update the existing topics to improve that, thanks for the suggestion.
Let me know if you need more information.
Kind regards,
Daniel
the Telerik team
- The RadControls code for generating ExcelML is more complex than hardcoding an XML document.
- I guess what I'm looking for is something from Telerik to compare each of their methods, and better examples than what we see.
- Maybe I'm wrong but it's my perception that the methods available are each deficient in some way, forcing is to choose a method that's adequate for a specific task and not one that's really right.
HTML format on the other hand relies on the HTML code that is rendered by the RadGrid.
Put simply and considering the above, it is virtually impossible to make these formats support the same features and work in exactly the same way.
- Again, I could be wrong, but the export tools seem to be missing properties to control the Excel document (beyond the limitations of SpreadsheetML itself).
- And while there are a few examples, the documentation includes a method that is deprecated, and doesn't tell us where the events fire in the life cycle.
Speaking of the events, we will happily update the existing topics to improve that, thanks for the suggestion.
Let me know if you need more information.
Kind 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.
0
TonyG
Top achievements
Rank 1
Rank 1
answered on 23 Jul 2012, 06:15 PM
Daniel - Sincere thanks for taking the time to respond to my plea for help here.
Regarding the deprecated method, see this doc page where these quotes are found:
"Please note that the ExcelExportCellFormatting event (Excel-specific) is marked as obsolete as from RadControls for ASP.NET AJAX Q1 2011. "
"Thanks to the ExcelExportCellFormatting event it is really easy to apply custom styles to the rows/cells."
So "thanks to" an obsolete method that might go away at any moment, it used to be easy to do something - but how do we do that now? :)
I wrote: "The RadControls code for generating ExcelML is more complex than hardcoding an XML document."
You responded: "If I'm not getting this wrong, you are trying to generate XMLSS document directly, e.g. without RadGrid. If this is so, please note that this code is not intended to be used outside of RadGrid."
What I was saying is that the RadControls mechanism seems to be more difficult to use than hardcoded XML, so personally, and unfortunately, I'm inclined to not use the RadControls mechanism. I completed my Excel export task last week with code as follows. The mydatasource is a List<T> that's used to populate the grid.DataSource:
All I'm saying there is that it seems like we need to learn a completely new interface that doesn't actually do everything that we need to do. I can see someone spending a lot of time coding to the RadControls Excel Export API, and then having to tell users "sorry I can't complete the next request" because they've invested so heavily into that API. But hardcoding the XML (assuming one knows the schema) sets the limit at the capabilities of the schema, not the API. Yes, I know this only applies to ExcelML but the same goes for the HTML and binary methods.
As to more specific recommendations regarding the ExcelML export, I'll try to find some time to articulate exactly what I find deficient or difficult there, but I'm guessing the developers of that API know exactly what I would write. The challenge here is simple. Take someone who has never generated Excel from a RadGrid before, and without providing any other tips, ask them to export a plain worksheet off of a very basic grid and data source, then slowly increase the complexity of the grid and the Excel sheet. You'll immediately find a requirement to know details that aren't documented, and to do things that are not documented as being possible or not possible. Use that experience to improve the docs for each export method. Notice how the developer refers to the docs. How much time are they spending hunting around? Do they need to jump to different pages before gathering a general understanding? Are they asking questions or hunting around in the forum for clues? That's lost productivity and the documentation should be the means by which we improve our productivity! I'd be happy to do that kind of QA effort, but of course I lack the knowledge (for the reasons discussed here) and no one is paying me to write Telerik documentation. (Though I gladly offer my services.)
Regarding your statement: "Indeed, we do not cover the XMLSS format specification on 100% and we are not planning to change this. If you are looking for a complete control over the rendering it would be better to build the XML code manually without relying on our code."
Each export mechanism has limitations which a developer should learn from other resources. And RadControls supports a subset of that functionality. I don't think anyone has a problem with that. But there's nothing in the doc to tell a developer what detailed features they do and do not get out of the box for each export type. I can't look at the doc and compare that to my immediate (and expected) requirements, and know which of your export methods are appropriate. One must spend time (weeks, months?) to learn the nuances of each mechanism to gain that knowledge. That's wrong - that information should be in the docs. Personally I don't have the time to learn the limitations of the API through the school of hard knocks, so I choose immediately to roll my own code. I'm sure your marketing people don't want a situation where lack of information causes a licensed developer to stop using the software (only a small component of course), and your development managers don't want paying customers to not use the software they've painfully engineered because of a documentation deficiency.
I'm not asking for a product change. I would like to see a revamp of the documentation, with more and current details for each export type, and lots of code examples. As I think about this, I'm asking for code because the doc is inadequate. If the docs on this topic were up to Telerik standards then a lot of code wouldn't be required. In the absence of docs we learn by example.
Thanks again!
Regarding the deprecated method, see this doc page where these quotes are found:
"Please note that the ExcelExportCellFormatting event (Excel-specific) is marked as obsolete as from RadControls for ASP.NET AJAX Q1 2011. "
"Thanks to the ExcelExportCellFormatting event it is really easy to apply custom styles to the rows/cells."
So "thanks to" an obsolete method that might go away at any moment, it used to be easy to do something - but how do we do that now? :)
I wrote: "The RadControls code for generating ExcelML is more complex than hardcoding an XML document."
You responded: "If I'm not getting this wrong, you are trying to generate XMLSS document directly, e.g. without RadGrid. If this is so, please note that this code is not intended to be used outside of RadGrid."
What I was saying is that the RadControls mechanism seems to be more difficult to use than hardcoded XML, so personally, and unfortunately, I'm inclined to not use the RadControls mechanism. I completed my Excel export task last week with code as follows. The mydatasource is a List<T> that's used to populate the grid.DataSource:
StringBuilder writer = new StringBuilder();writer.AppendLine("<?xml version=\"1.0\"?>");writer.AppendLine("<?mso-application progid=\"Excel.Sheet\"?>");writer.AppendLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\""); // etc etcwriter.AppendLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");writer.AppendLine(" <Author>" + user + "</Author>"); // etc etcfor (int i = 0; i < containers.Count; i++){ MyDataClass d = mydatasource[i]; writer.AppendLine("<Row" + (i == 0 ? " ss:Index=\"4\"" : "") + " ss:AutoFitHeight=\"0\">"); // build <Cell> tag here, accounting for data type and styling writer.AppendLine("</Row>");}...Response.Clear();// set ContentType and other specs depending on browserusing (TextWriter textWriter = new StreamWriter(Response.OutputStream,Encoding.UTF8)) textWriter.Write(writer.ToString());Response.Flushtry { Response.End } Catch (Exception expectedException) { // ignore }All I'm saying there is that it seems like we need to learn a completely new interface that doesn't actually do everything that we need to do. I can see someone spending a lot of time coding to the RadControls Excel Export API, and then having to tell users "sorry I can't complete the next request" because they've invested so heavily into that API. But hardcoding the XML (assuming one knows the schema) sets the limit at the capabilities of the schema, not the API. Yes, I know this only applies to ExcelML but the same goes for the HTML and binary methods.
As to more specific recommendations regarding the ExcelML export, I'll try to find some time to articulate exactly what I find deficient or difficult there, but I'm guessing the developers of that API know exactly what I would write. The challenge here is simple. Take someone who has never generated Excel from a RadGrid before, and without providing any other tips, ask them to export a plain worksheet off of a very basic grid and data source, then slowly increase the complexity of the grid and the Excel sheet. You'll immediately find a requirement to know details that aren't documented, and to do things that are not documented as being possible or not possible. Use that experience to improve the docs for each export method. Notice how the developer refers to the docs. How much time are they spending hunting around? Do they need to jump to different pages before gathering a general understanding? Are they asking questions or hunting around in the forum for clues? That's lost productivity and the documentation should be the means by which we improve our productivity! I'd be happy to do that kind of QA effort, but of course I lack the knowledge (for the reasons discussed here) and no one is paying me to write Telerik documentation. (Though I gladly offer my services.)
Regarding your statement: "Indeed, we do not cover the XMLSS format specification on 100% and we are not planning to change this. If you are looking for a complete control over the rendering it would be better to build the XML code manually without relying on our code."
Each export mechanism has limitations which a developer should learn from other resources. And RadControls supports a subset of that functionality. I don't think anyone has a problem with that. But there's nothing in the doc to tell a developer what detailed features they do and do not get out of the box for each export type. I can't look at the doc and compare that to my immediate (and expected) requirements, and know which of your export methods are appropriate. One must spend time (weeks, months?) to learn the nuances of each mechanism to gain that knowledge. That's wrong - that information should be in the docs. Personally I don't have the time to learn the limitations of the API through the school of hard knocks, so I choose immediately to roll my own code. I'm sure your marketing people don't want a situation where lack of information causes a licensed developer to stop using the software (only a small component of course), and your development managers don't want paying customers to not use the software they've painfully engineered because of a documentation deficiency.
I'm not asking for a product change. I would like to see a revamp of the documentation, with more and current details for each export type, and lots of code examples. As I think about this, I'm asking for code because the doc is inadequate. If the docs on this topic were up to Telerik standards then a lot of code wouldn't be required. In the absence of docs we learn by example.
Thanks again!
0
Hello Tony,
Thanks for sharing your opinion about the documentation. I will pass your suggestions to our documentation team.
As to the first part about the events, the only major difference between ExcelExportCellFormatting and ExportCellFormatting is that the latter will be fired for both HTML Excel and Word formats.
Don't hesitate to ask if you have any questions about features that are not (well) documented. You can also use the support ticketing system as you will have faster replies this way.
Best regards,
Daniel
the Telerik team
Thanks for sharing your opinion about the documentation. I will pass your suggestions to our documentation team.
As to the first part about the events, the only major difference between ExcelExportCellFormatting and ExportCellFormatting is that the latter will be fired for both HTML Excel and Word formats.
Don't hesitate to ask if you have any questions about features that are not (well) documented. You can also use the support ticketing system as you will have faster replies this way.
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.