SharePoint के लिए Excel निर्यात में सुधार

• डेटा प्राप्त करने के लिए आपको अपने कंप्यूटर पर Excel स्थापित करना होगा।
• परिणामी दस्तावेज़ कॉलम नामों के लिए आंतरिक नाम फ़ील्ड का उपयोग करता है।
• प्राप्त दस्तावेज़ एक "कच्चे" डेटा प्रारूप का उपयोग करता है, जो हमेशा उपयुक्त होता है।

निर्यात बटन का प्रतिस्थापन

 <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> 

नियंत्रण बनाएँ

 <Control Id="AdditionalPageHead" Sequence="1000" ControlAssembly="$SharePoint.Project.AssemblyFullName$" ControlClass="$SharePoint.Type.7fd7c6f0-4eda-48ce-ac8f-aa9f9d2666ac.FullName$"/> 

 <CommandUIHandlers> <CommandUIHandler Command="ExportToSpreadsheet-Replacement" CommandAction="javascript:(function(){var x=SP.ListOperation.Selection.getSelectedList(); if (x) {__doPostBack('ExportToSpreadsheet-Replacement', x);}})();" /> </CommandUIHandlers> 

 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)); } } 

 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; } 

एक्सेल फ़ाइल पीढ़ी

 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(); } 

 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); } 

निष्कर्ष