Improving export to Excel for SharePoint
The standard ability to export to Excel in SharePoint works in a rather unusual way. When you click on the export button, SharePoint sends the request file in a special format that opens Excel, and Excel itself already tightens the data.
The advantage of this approach is that the data in Excel can be updated, since there is a connection. But more disadvantages:
Using a small amount of code, you can replace the standard export function with your own, so that users will not notice anything.
')
To replace an existing item in the Ribbon, add a new
In order to replace the export buttons in Excel, you need to find the buttons with Id equal to
There is also a separate button for the calendar with
The button should call some server code. To make the behavior similar to the standard button, it is best to do DelegateControl , which will be placed on each page. For this there is a container with
The custom button will call PostBack , processed by our control. The button can be pressed on the list view page, then no additional parameters need to be passed. Also, list views can be added to regular pages as web parts, then you need to transfer the list Id and views to the server.
Alas, getting the Id of the view in which the button was pressed is not a trivial task. Therefore, I will confine myself to the transfer of the Id list
Next on the server, you need to check whether the button is pressed on the list view page or on the regular page:
Getting the data table by view is very simple:
The last step is to create an Excel file and give it to the client. One of the easiest ways to generate Excel is to use the ClosedXml library ( http://closedxml.codeplex.com/ ).
The first three lines of the method actually form the Excel document (thanks to ClosedXml), and the rest is the code to give the file to the client.
The
I found the code to form the correct
For everything to work on Production, you need to add the ClosedXml.dll and DocumentFormat.OpenXml.dll files to your package. The Ribbon and Control buttons should be placed in one feature of the Site or Web level.
The entire project can be viewed at the link - spsamples.codeplex.com/SourceControl/latest#ExportToExcel
Ready WSP file here - spsamples.codeplex.com/releases/view/117220
Most of the code is universal, and does not depend on SharePoint. You can use a similar approach in any ASP.NET project.
The advantage of this approach is that the data in Excel can be updated, since there is a connection. But more disadvantages:
- You need Excel installed on your computer to get the data.
- In the resulting document, the InternalName fields are used for the column names.
- The resulting document uses the “raw” data format, which is not always suitable.
Using a small amount of code, you can replace the standard export function with your own, so that users will not notice anything.
')
The substitution of export buttons
To replace an existing item in the Ribbon, add a new
CommandUIDefinition with the Location parameter equal to the Id of the existing item. All standard elements are in the file C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ 14 \TEMPLATE\GLOBAL\XML\CMDUI.XML for SharePoint 2010 (or 15 for SharePoint 2013).In order to replace the export buttons in Excel, you need to find the buttons with Id equal to
Ribbon.List.Actions.ExportToSpreadsheet and Ribbon.Library.Actions.ExportToSpreadsheet and completely copy to your project. As a Location for new items, you must specify these Id.A lot of copy-paste
<CommandUIDefinitions> <CommandUIDefinition Location="Ribbon.Library.Actions.ExportToSpreadsheet"> <Button Id="Ribbon.Library.Actions.ExportToSpreadsheet-Replacement" Sequence="40" Command="ExportToSpreadsheet-Replacement" Image16by16="/_layouts/$Resources:core,Language;/images/formatmap16x16.png" Image16by16Top="-152" Image16by16Left="-32" Image32by32="/_layouts/$Resources:core,Language;/images/formatmap32x32.png" Image32by32Top="-352" Image32by32Left="0" LabelText="$Resources:core,cui_ButExportToSpreadsheet;" ToolTipTitle="$Resources:core,cui_ButExportToSpreadsheet;" ToolTipDescription="$Resources:core,cui_STT_ButExportListToSpreadsheet;" TemplateAlias="o2" /> </CommandUIDefinition> <CommandUIDefinition Location="Ribbon.List.Actions.ExportToSpreadsheet"> <Button Id="Ribbon.List.Actions.ExportToSpreadsheet-Replacement" Sequence="40" Command="ExportToSpreadsheet-Replacement" Image16by16="/_layouts/$Resources:core,Language;/images/formatmap16x16.png" Image16by16Top="-152" Image16by16Left="-32" Image32by32="/_layouts/$Resources:core,Language;/images/formatmap32x32.png" Image32by32Top="-352" Image32by32Left="0" LabelText="$Resources:core,cui_ButExportToSpreadsheet;" ToolTipTitle="$Resources:core,cui_ButExportToSpreadsheet;" ToolTipDescription="$Resources:core,cui_STT_ButExportListToSpreadsheet;" TemplateAlias="o1" /> </CommandUIDefinition> </CommandUIDefinitions> There is also a separate button for the calendar with
Id="Ribbon.Calendar.Calendar.Actions.ExportToSpreadsheet" , you can do the same with it.Creating control
The button should call some server code. To make the behavior similar to the standard button, it is best to do DelegateControl , which will be placed on each page. For this there is a container with
Id="AdditionalPageHead" : <Control Id="AdditionalPageHead" Sequence="1000" ControlAssembly="$SharePoint.Project.AssemblyFullName$" ControlClass="$SharePoint.Type.7fd7c6f0-4eda-48ce-ac8f-aa9f9d2666ac.FullName$"/> The custom button will call PostBack , processed by our control. The button can be pressed on the list view page, then no additional parameters need to be passed. Also, list views can be added to regular pages as web parts, then you need to transfer the list Id and views to the server.
Alas, getting the Id of the view in which the button was pressed is not a trivial task. Therefore, I will confine myself to the transfer of the Id list
<CommandUIHandlers> <CommandUIHandler Command="ExportToSpreadsheet-Replacement" CommandAction="javascript:(function(){var x=SP.ListOperation.Selection.getSelectedList(); if (x) {__doPostBack('ExportToSpreadsheet-Replacement', x);}})();" /> </CommandUIHandlers> Next on the server, you need to check whether the button is pressed on the list view page or on the regular page:
protected override void OnLoad(EventArgs e) { if (this.Page.Request["__EVENTTARGET"] == "ExportToSpreadsheet-Replacement") { var spContext = SPContext.Current; SPList list; SPView view; if (spContext.ViewContext.View != null) { list = spContext.List; view = spContext.ViewContext.View; } else { var listId = new Guid(this.Page.Request["__EVENTARGUMENT"]); var web = spContext.Web; list = web.Lists[listId]; view = list.DefaultView; } ExportData(list.Title + " - " + view.Title, GetDataTable(list, view)); } } Getting the data table by view is very simple:
private static System.Data.DataTable GetDataTable(SPList list, SPView view) { var query = new SPQuery(view); SPListItemCollectionPosition position; var flags = SPListGetDataTableOptions.UseBooleanDataType | SPListGetDataTableOptions.UseCalculatedDataType; var result = list.GetDataTable(query, flags, out position); while (position != null) { list.AppendDataTable(query, flags, result, out position); } return result; } Excel file generation
The last step is to create an Excel file and give it to the client. One of the easiest ways to generate Excel is to use the ClosedXml library ( http://closedxml.codeplex.com/ ).
private void ExportData(string title, System.Data.DataTable table) { var wb = new XLWorkbook(); var ws = wb.Worksheets.Add(title); ws.Cell(1, 1).InsertTable(table); var response = this.Page.Response; response.Clear(); response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var filename = title+".xlsx"; response.AddHeader("content-disposition", GetContentDisposition(filename)); // Flush the workbook to the Response.OutputStream using (var memoryStream = new MemoryStream()) { wb.SaveAs(memoryStream); memoryStream.WriteTo(response.OutputStream); } response.End(); } The first three lines of the method actually form the Excel document (thanks to ClosedXml), and the rest is the code to give the file to the client.
The
content-disposition response header is very differently perceived by different browsers, so giving a file with the correct Russian name requires some dances with a tambourine.I found the code to form the correct
content-disposition header on StackOverflow stackoverflow.com/questions/93551/how-to-encode-the-filename-parameter-of-content-disposition-header-in-http private string GetContentDisposition(string filename) { var request = this.Page.Request; string contentDisposition; if (request.Browser.Browser == "IE" && (request.Browser.Version == "7.0" || request.Browser.Version == "8.0")) contentDisposition = "attachment; filename=" + Uri.EscapeDataString(filename); else if (request.UserAgent != null && request.UserAgent.ToLowerInvariant().Contains("android")) // android built-in download manager (all browsers on android) contentDisposition = "attachment; filename=\"" + MakeAndroidSafeFileName(filename) + "\""; else contentDisposition = "attachment; filename=\"" + filename + "\"; filename*=UTF-8''" + Uri.EscapeDataString(filename); return contentDisposition; } private static readonly Dictionary<char, char> AndroidAllowedChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ._-+,@£$€!½§~'=()[]{}0123456789".ToDictionary(c => c); private string MakeAndroidSafeFileName(string fileName) { char[] newFileName = fileName.ToCharArray(); for (int i = 0; i < newFileName.Length; i++) { if (!AndroidAllowedChars.ContainsKey(newFileName[i])) newFileName[i] = '_'; } return new string(newFileName); } Conclusion
For everything to work on Production, you need to add the ClosedXml.dll and DocumentFormat.OpenXml.dll files to your package. The Ribbon and Control buttons should be placed in one feature of the Site or Web level.
The entire project can be viewed at the link - spsamples.codeplex.com/SourceControl/latest#ExportToExcel
Ready WSP file here - spsamples.codeplex.com/releases/view/117220
Most of the code is universal, and does not depend on SharePoint. You can use a similar approach in any ASP.NET project.
Source: https://habr.com/ru/post/208948/
All Articles