When exporting a ASP.NET Core Kendo Grid to Excel, we need to add a Custom Header and Footer to the Excel Spreadsheet. Please note, this does not mean we want to add a row to the Spreadsheet with Column Headers, this means we want to use what Excel calls "Custom Header" and "Custom Footer". The kendo ooxml export of the grid into an Excel spreadsheet does not seem to expose properties to set the Custom Header and Footer. As a note, these are custom headers and footers that do not show up with viewing the document in Excel, but DO print, when printing the document, so it can show disclaimers and things.
So, we are trying to open the Kendo exported ooxml on the Server Side to add the custom HeaderFooter elements, using the DocumentFormat.OpenXML Library in C#, before returning the file back to the user/client. After we convert the exported base64 string into a byte array and load it into a MemoryStream, we can successfully use the MemoryStream to create a SpreadsheetDocument object, however all the Workbook parts are empty. When we try to create a new SpreadsheetDocument, create new workbook parts, and try to insert our exported Sheet we received from Kendo, we get various errors on missing Root Elements or Perhaps other Parent Nodes.
Does anyone have an example of either converting a Grid exported ooxml Excel Sheet into a DocumentFormat.OpenXML Excel sheet in order to add <HeaderFooter> or a way of doing this on the client side, using the ooxml before we go to the Server?
Thank you,
Alan
1 Answer, 1 is accepted
I have already responded to the private support thread so I will paste my response here in case anyone else is looking for a similar answer.
---------------------------------------
We have an example which covers a UI for ASP.NET MVC Grid server-side export with the help of the Microsoft DocumentFormat.OpenXML library. I tested it with the UI for ASP.NET Core wrappers and it seems to work well, however, it is a different workflow than the one you described.
Instead of editing a client-side generated workbook, the workbook is generated on the server with the help of a utility Excel.cs file:
https://github.com/telerik/ui-for-aspnet-mvc-examples/blob/master/grid/excel-export-server-side/KendoUIMVC5/Utilities/Excel.cs
I am including here the test project so you do not have to start from scratch. As far as the header/footer tag, I am not entirely sure where it should be added. I suppose you should be able to find it somewhere in their documentation:
https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml?view=openxml-2.8.1
Finally, since you are interested in the server export, I would like to ask you to upvote an item which we have logged as a feature request so we can have this functionality as part of the Documents processing suite:
https://feedback.telerik.com/document-processing/1356226-document-processing-provide-version-for-net-core
------------------------------------------------
Kind Regards,
Alex Hajigeorgieva
Progress Telerik