Top List List

Export SharePoint list to Excel Mac

Excel export is a cool feature that has been part of SharePoint from the early days. It allows used to export the list data to an excel file and use it further.

Even after being used so popularly in different version Microsoft never made it that friendly across all platforms. I beleive the excel export relies on the active x controls that is only shipped with the Windows. Excel export was a topic with one of my recent client where users using Mac-OS are not able to use it as easily as users using Windows.

In this article i will walk you through the process where i created a new excel export button that will allow users using Mac-OS to export the list items without any hesitation.

Following functionality will be provided through this Functionality

Users using any OS will be able to use this Export to Excel button
Users can pick only few items to export if not the whole view
If none of the items is selected then all the items in current view will be exported
It will export any field any data if it is null then it will export blank data
It will export all the items that is generated in current view.

Lets get started

Using Visual Studio 2013 Add a new Empty project
Add New SharePoint Project

2. Pick Deploy as a farm solution select the site used for debugging

Select Farm Solution and Designated Site Collection for debugging

3. Now from the project add new item pick an Empty element which will provide the XML file needed fro the button to appear in the menu.

4. Now lets map the layouts folder where we will add images tot he export to excel icon and the application page which will run the code behind necessary for the button to work.

Select the layouts folder

5. Once the layouts folder is added add an image folder

Add images folder and the icon images

6. Now lets add an application right click on the layouts folder click on add new item it will automatically take you to the template picking screen where you can click on the application page.

Pick the Application Page

7. Now lets add a java script files which will have the main function that links our client end action to server side.

Once all the files are added

8. Now lets add our java script with some client side SharePoint. Basically we will load up all the information about the current list such as list id , guid, view guid, selected items using client side and pass it over to application page.

Following is the code that goes in the java-scriptFile.

var excellistitemid;
var listitemidselected;
var currentWeb;
var currentSite;
var currentListGuid;

// Main function that is executed when this jquery file is accessed. It saves all the information and relays to the application page.

function Export[] {
currentListGuid = SP.ListOperation.Selection.getSelectedList[];

var context = SP.ClientContext.get_current[];
currentSite = context.get_site[];
currentWeb = context.get_web[];

var currentList = currentWeb.get_lists[].getById[currentListGuid];
var items = SP.ListOperation.Selection.getSelectedItems[];

var index;
listitemidselected = ;
for [index in items] {
listitemid = currentList.getItemById[items[index].id];
listitemidselected = listitemidselected + items[index].id + ,;
}

context.executeQueryAsync[Function.createDelegate[this, this.exportsuccess], Function.createDelegate[this, this.exportfailed]];
}

// This function is the one that sends the information server side for processing. we are using a post method creating a form where all the information is passes as an hidden element value inside the dynamically created form.

function exportsuccess[] {
var form = document.createElement[form];
form.setAttribute[method, post];

var hiddenField = document.createElement[input];
hiddenField.setAttribute[type, hidden];
hiddenField.setAttribute[name, IDselected];
hiddenField.setAttribute[value, listitemidselected];
form.appendChild[hiddenField];

var hiddenListGuid = document.createElement[input];
hiddenListGuid.setAttribute[type, hidden];
hiddenListGuid.setAttribute[name, ListGuid];
hiddenListGuid.setAttribute[value, currentListGuid];
form.appendChild[hiddenListGuid];

var hiddenViewGuid = document.createElement[input];
hiddenViewGuid.setAttribute[type, hidden];
hiddenViewGuid.setAttribute[name, ViewGuid];
hiddenViewGuid.setAttribute[value, ctx.view];
form.appendChild[hiddenViewGuid];

form.setAttribute[action, ctx.HttpRoot + /_layouts/15/ExporttoExcels/Exporttoexcel.aspx];
document.body.appendChild[form];
form.submit[];

SP.UI.Notify.addNotification[Exported];
}

//an error is alerted if it does not success

function exportfailed[sender, args] {
var statusId = SP.UI.Status.addStatus[args.get_message[]];
SP.UI.Status.setStatusPriColor[statusId, red];
latestId = statusId;
}

function exporttoexcelenable[] {
return [true];
}

9 Now once we have the Java-script file ready lets work on the application page. Lets edit the code behind file of the application page. Make sure you have the reference Dlls right.

This page will have the functions that will run in code behind which actually creates an excel table and exports the item in excel sheet with todays date.

Here is the code. Below is the whole code of the page. It includes the code of the whole class after the namespace

public partial class ExporttoExcel : LayoutsPageBase //
{
HttpResponse _exportResponse;
bool _isErrorOccured = false;

// this class will read the variables from the java script file

protected void Page_Init[object sender, EventArgs e]
{
_exportResponse = Response;
// Get view and list guids from the request.
Guid listGUID = new Guid[Request[ListGuid].ToString[]];
Guid viewGUID = new Guid[Request[ViewGuid].ToString[]];

if [Request[IDselected].ToString[] != ]
{
var listItemsID = Request[IDselected].ToString[].Split[new Char[] { , }];
ExportSelectedItemsToExcel[listGUID, viewGUID, listItemsID];
}
else
{
var listItemsID = new String[0];
ExportSelectedItemsToExcel[listGUID, viewGUID, listItemsID];
}

}
protected void Page_Load[object sender, EventArgs e]
{
}

// this function actually process everything it reads the list, selected items then retrieves the results put in an html table and export it using excel application.
public void ExportSelectedItemsToExcel[Guid listID, Guid listViewID, String[] selectedlistids]
{

try
{
SPList list = SPContext.Current.Web.Lists[listID];
SPView listView = list.Views[listViewID];

//create the table

var exportListTable = new HtmlTable { Border = 1, CellPadding = 3, CellSpacing = 3 };

HtmlTableCell htmlcell;

var htmlrow = new HtmlTableRow[];
SPViewFieldCollection viewHeaderFields = listView.ViewFields;
for [var index = 0; index < viewHeaderFields.Count; index++]
foreach [
SPField field in
listView.ParentList.Fields.Cast[].Where[
field => field.InternalName == viewHeaderFields[index]]]
{
if [!field.Hidden]
{
htmlcell = new HtmlTableCell
{
BgColor = blue,
InnerHtml = field.Title.ToString[CultureInfo.InvariantCulture]
};
htmlrow.Cells.Add[htmlcell];
}
break;
}
exportListTable.Rows.Add[htmlrow];

SPDiagnosticsService.Local.WriteTrace[0, new SPDiagnosticsCategory[Excel Export, TraceSeverity.Unexpected, EventSeverity.Error], TraceSeverity.Unexpected, Added header :];

// Add rows in HTML table based on the fields in view.
#region when there is items selected
if [selectedlistids != null && selectedlistids.Count[] > 0]
{
foreach [string id in selectedlistids.Where[id => !String.IsNullOrEmpty[id]]]
{
htmlrow = new HtmlTableRow[];

SPListItem item = list.GetItemById[Convert.ToInt32[id]];
SPViewFieldCollection viewFields = listView.ViewFields;
for [var i = 0; i < viewFields.Count; i++]
{
foreach [SPField field in listView.ParentList.Fields.Cast[].Where[field => field.InternalName == viewFields[i]]]
{
if [!field.Hidden]
{
htmlcell = new HtmlTableCell[];
htmlcell = new HtmlTableCell[];
if [item[field.InternalName] != null]
{

if [[string.CompareOrdinal[field.TypeAsString, TaxonomyFieldType] == 0] || [string.CompareOrdinal[field.TypeAsString, TaxonomyFieldTypeMulti] == 0]]
{
htmlcell.InnerHtml = [[GetValuesFromTaxonomyField[field, item] != ] || [GetValuesFromTaxonomyField[field, item] != null]] ? GetValuesFromTaxonomyField[field, item] : ;
}
else if [field.Type == SPFieldType.Lookup]
{
// call the method to get lookup field
htmlcell.InnerHtml = [[GetValuesFromLookupField[field, item] != ] || [GetValuesFromLookupField[field, item] != null]] ? GetValuesFromLookupField[field, item] : ;
}
else if [field.Type == SPFieldType.User]
{
htmlcell.InnerHtml = [[GetValuesFromUserField[field, item] != ] || [GetValuesFromUserField[field, item] != null]] ? GetValuesFromUserField[field, item] : ;
}
else if [field.Type == SPFieldType.Invalid]
{
htmlcell.InnerHtml = [[GetValuesFromInvalidTypeField[field, item] != ] || [GetValuesFromInvalidTypeField[field, item] != null]] ? GetValuesFromInvalidTypeField[field, item] : ;
}
else if [field.Type == SPFieldType.Calculated]
{
var cf = [SPFieldCalculated]field;
htmlcell.InnerHtml = [[cf.GetFieldValueAsText[item[field.InternalName]] != ] || [cf.GetFieldValueAsText[item[field.InternalName]] != null]] ? cf.GetFieldValueAsText[item[field.InternalName]] : ;
}
else
{
htmlcell.InnerHtml = [[Convert.ToString[item[field.InternalName]] != ] || Convert.ToString[item[field.InternalName]] != null] ? item[field.InternalName].ToString[] : ;
}
}
else
{
htmlcell.InnerHtml = String.Empty;
}
htmlrow.Cells.Add[htmlcell];
}
break;
}
}
exportListTable.Rows.Add[htmlrow];

}

}
#endregion

#region When none of the item is selected
else
{
var query = new SPQuery[];
query.Query = listView.Query;
SPListItemCollection GetItems = list.GetItems[query];

foreach [SPListItem item in GetItems]
{
htmlrow = new HtmlTableRow[];

SPViewFieldCollection viewFields = listView.ViewFields;
for [var i = 0; i < viewFields.Count; i++]
{
foreach [SPField field in listView.ParentList.Fields.Cast[].Where[field => field.InternalName == viewFields[i]]]
{
if [!field.Hidden]
{
htmlcell = new HtmlTableCell[];
if [item[field.InternalName] != null]
{

}

// Write the HTML table contents to response as excel file
string doctitle = list.Title + _ + Convert.ToString[DateTime.Now.ToString[dd/MM/yyyy]];
using [var sw = new StringWriter[]]
{
using [var htw = new HtmlTextWriter[sw]]
{
exportListTable.RenderControl[htw];
_exportResponse.Clear[];
_exportResponse.ContentType = application/vnd.ms-excel;
_exportResponse.AddHeader[content-disposition, string.Format[attachment; filename={0}, doctitle + .xls]];
_exportResponse.Cache.SetCacheability[HttpCacheability.NoCache];
_exportResponse.ContentEncoding = System.Text.Encoding.Unicode;
_exportResponse.BinaryWrite[System.Text.Encoding.Unicode.GetPreamble[]];
_exportResponse.Write[sw.ToString[]];
_exportResponse.End[];
}
}
}
catch [System.Threading.ThreadAbortException exception]
{
// Do nothing on thread abort exception.
SPDiagnosticsService.Local.WriteTrace[0, new SPDiagnosticsCategory[Excel Export, TraceSeverity.Unexpected, EventSeverity.Error], TraceSeverity.Unexpected, exception.Message];
}
catch [Exception ex]
{
_isErrorOccured = true;
SPDiagnosticsService.Local.WriteTrace[0, new SPDiagnosticsCategory[Excel Export, TraceSeverity.Unexpected, EventSeverity.Error], TraceSeverity.Unexpected, ex.Message];
}
finally
{
if [_isErrorOccured]
{
SPDiagnosticsService.Local.WriteTrace[0, new SPDiagnosticsCategory[Excel Export, TraceSeverity.Unexpected, EventSeverity.Error], TraceSeverity.Unexpected, Exported with errors];
}

}

Everything below are sub functions that help in getting the field values depending on what field is being selected.
private string GetValuesFromInvalidTypeField[SPField field, SPListItem item]
{
string invalidtypefieldvalue = string.Empty;
try
{
var lookupField = [SPFieldLookup]field;

if [lookupField.AllowMultipleValues]
{
var values = item[field.InternalName] as SPFieldLookupValueCollection;
if [values != null]
invalidtypefieldvalue = values.Aggregate[invalidtypefieldvalue, [current, value] => current + value.LookupValue.ToString[CultureInfo.InvariantCulture] + ; ];
}
else
{
var fieldValue = new SPFieldLookupValue[item[field.InternalName].ToString[]];
invalidtypefieldvalue = fieldValue.LookupValue.ToString[CultureInfo.InvariantCulture];
}
}
catch [Exception ex]
{
SPDiagnosticsService.Local.WriteTrace[0, new SPDiagnosticsCategory[Excel Export, TraceSeverity.Unexpected, EventSeverity.Error], TraceSeverity.Unexpected, Exception:, ex.Message];
}
return invalidtypefieldvalue;
}
public String GetValuesFromLookupField[SPField field, SPItem item]
{

SPDiagnosticsService.Local.WriteTrace[0, new SPDiagnosticsCategory[Excel Export, TraceSeverity.Unexpected, EventSeverity.Error], TraceSeverity.Unexpected, Look up Field];
string lookupfieldvalue = string.Empty;
try
{
var lookupField = [SPFieldLookup]field;

if [lookupField.AllowMultipleValues]
{
var values = item[field.InternalName] as SPFieldLookupValueCollection;
if [values != null]
lookupfieldvalue = values.Aggregate[lookupfieldvalue, [current, value] => current + value.LookupValue.ToString[CultureInfo.InvariantCulture] + ; ];
}
else
{
var fieldValue = new SPFieldLookupValue[item[field.InternalName].ToString[]];
lookupfieldvalue = fieldValue.LookupValue.ToString[CultureInfo.InvariantCulture];
}

SPDiagnosticsService.Local.WriteTrace[0, new SPDiagnosticsCategory[Excel Export, TraceSeverity.Unexpected, EventSeverity.Error], TraceSeverity.Unexpected, Look up Field value + lookupfieldvalue];

}
catch [Exception ex]
{
SPDiagnosticsService.Local.WriteTrace[0, new SPDiagnosticsCategory[Excel Export, TraceSeverity.Unexpected, EventSeverity.Error], TraceSeverity.Unexpected, Look up Field exception + ex.Message];
}
return lookupfieldvalue;
}
public String GetValuesFromUserField[SPField field, SPItem item]
{
var userfieldvalue = string.Empty;
try
{
var userFieldValueCol = new SPFieldUserValueCollection[SPContext.Current.Web, item[field.InternalName].ToString[]];
userfieldvalue = userFieldValueCol.Select[singlevalue => singlevalue.ToString[].Split[#]].Aggregate[userfieldvalue, [current, userValue] => current + userValue[1] + ; ];
}
catch [Exception ex]
{
SPDiagnosticsService.Local.WriteTrace[0, new SPDiagnosticsCategory[Excel Export, TraceSeverity.Unexpected, EventSeverity.Error], TraceSeverity.Unexpected, Exception:, ex.Message];
}

return userfieldvalue;
}
public String GetValuesFromTaxonomyField[SPField field, SPItem item]
{
var txField = field as TaxonomyField;
var taxonomyfieldvalue = string.Empty;
try
{
if [txField != null]
{
if [txField.AllowMultipleValues]
{
var taxfieldValColl = [item[field.InternalName] as TaxonomyFieldValueCollection];
if [taxfieldValColl != null]
taxonomyfieldvalue = taxfieldValColl.Aggregate[taxonomyfieldvalue, [current, singlevalue] => current + singlevalue.Label.ToString[CultureInfo.InvariantCulture] + ; ];
}
else
{
var singlevalue = item[field.InternalName] as TaxonomyFieldValue;
if [singlevalue != null] taxonomyfieldvalue = taxonomyfieldvalue + singlevalue.Label.ToString[CultureInfo.InvariantCulture];
}
}
}
catch [Exception ex]
{
SPDiagnosticsService.Local.WriteTrace[0, new SPDiagnosticsCategory[Excel Export, TraceSeverity.Unexpected, EventSeverity.Error], TraceSeverity.Unexpected, Exception:, ex.Message];
}
return taxonomyfieldvalue;
}
}

9. Now lets actually add the button in the list menu and link everything. This is the main file that is accessed by client and this makes the export to excel button visible int he list menu bar.

from the project edit the XML file

now lets add the code in the xml file.

Bài Viết Liên Quan

Toplist mới

Bài mới nhất

Chủ Đề