In this blog post I will be discussing the steps involved in Excel Upload and Download (Export and Import of Excel Data) For SharePoint List Items with EPPlus in SharePoint 2013. This post contains downloadable source code for both SharePoint 2013 and SharePoint 2010
EPPlus is an Open XML based Excel processing library built on c# and is available for download in codeplex for free. It’s one of the most popular Excel processing library available with extensive set of features and functionality. For more details on EPPlus check out its project page in codeplex.
Setting Up the Environment
To begin with create an Excel file as shown below
Then create a SharePoint List named as Users with 3 columns. The below are its details. (Have renamed the default “Title” column to “Employee ID")
User Interface
Open up the Visual studio and create a new Empty SharePoint Project
Add a new Visual Web Part and place the below code in .ascx file of Visual WebPart to design the UI
<asp:Label ID="ltrlMsg" runat="server" Text="" EnableViewState="false"></asp:Label>
<div id="dvSource" runat="server">
<fieldset>
<legend>Source</legend>
<table>
<tr>
<td width="150px">Select Excel File :
</td>
<td width="600px">
<asp:FileUpload ID="FileUpload1" runat="server" Width="441px" />
<asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Upload Excel Data" />
</td>
</tr>
<tr>
<td colspan="2" style="text-align: center">
<asp:Button ID="btnDownload" runat="server" OnClick="btnDownload_Click" Text="Dowload Data As Excel" />
</td>
</tr>
</table>
</fieldset>
</div>Referencing EPPlus
1. Download EPPlus binaries from codeplex
2. Add reference to EPPlus dll in the project
3. Include Epplus dll into WSP package
Adding 3rd Party (External) dlls to SharePoint 2013 WSP package
3. 1. Double click the package file in the project
3.2 Click the Advanced Tab
3.3 Click the Add Button
3.4 Click the Source Path button and browse and select the dll
3.5 Select Web Application as the deployment Target
3.6 As EPPlus does not require any Safe Control entries, leave the other options to default and click OK to close the window.
Coding
Apart from Upload and Download functions, I have added a Column mapping method, that returns a Dictionary containing column index in Excel sheet as Key and its corresponding column name in SharePoint list as Value. The below code snippet contains the functions which upload and download excel data
/// <summary>
/// Maps the SharePoint List column with Columns in Excel File.
/// </summary>
/// <returns></returns>
private Dictionary<int, string> GetColumnMapping()
{
Dictionary<int, string> map = new Dictionary<int, string>();
map.Add(1, "Employee ID"); // First parameter is the column Index in Excel Sheet and Second Param is SharePoint List's Column Name (Display Name )
map.Add(2, "Name");
map.Add(3, "Location");
return map;
}
/// <summary>
/// Uploads the excel file to share point list.
/// </summary>
private void UploadExcelFileToSharePointList()
{
if (!FileUpload1.HasFile)
{
ltrlMsg.Text = "Please select a valid Excel File";
return;
}
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
spWeb.AllowUnsafeUpdates = true;
SPList list = spWeb.Lists[LIST_NAME];
try
{
byte[] fileData = FileUpload1.FileBytes;
using (MemoryStream memStream = new MemoryStream(fileData))
{
memStream.Flush();
using (ExcelPackage pck = new ExcelPackage(memStream))
{
if (pck != null)
{
CreateListItem(pck, list);
}
}
}
ltrlMsg.Text = "Data successfully Uploaded...";
}
catch (Exception Ex1)
{
ltrlMsg.Text = "Error Occured <br/>" + Ex1.Message;
}
}
}
}
catch (Exception Ex)
{
ltrlMsg.Text = "Error Occured <br/>" + Ex.Message;
}
}
/// <summary>
/// Creates the list item.
/// </summary>
/// <param name="pck">The PCK.</param>
/// <param name="list">The list.</param>
private void CreateListItem(ExcelPackage pck, SPList list)
{
Dictionary<int, string> column = GetColumnMapping();
ExcelWorksheet ws = pck.Workbook.Worksheets[1];
int rowCount = ws.Dimension.End.Row + 1;
int colCount = ws.Dimension.End.Column + 1;
for (int i = 2; i < rowCount; i++) // Row index starts from 2, as the first row is Title
{
SPListItem item = list.AddItem();
for (int j = 1; j < colCount; j++)
{
if (column.ContainsKey(j))
item[column[j]] = ws.Cells[i, j].Value;
}
item.Update();
}
}
/// <summary>
/// Download SharePoint list items as excel file.
/// </summary>
private void DownloadListItemsAsExcelFile()
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
spWeb.AllowUnsafeUpdates = true;
SPList list = spWeb.Lists[LIST_NAME];
ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add(LIST_NAME);
int rowIndex = 1;
// ==== Excel Column Headers ====
ws.Cells[rowIndex, 1].Value = "Emp ID";
ws.Cells[rowIndex, 2].Value = "Name";
ws.Cells[rowIndex, 3].Value = "Location";
rowIndex++;
// End of Excel Column Headers
foreach (SPListItem item in list.Items)
{
ws.Cells[rowIndex, 1].Value = item["Title"];
ws.Cells[rowIndex, 2].Value = item["Name"];
ws.Cells[rowIndex, 3].Value = item["Location"];
rowIndex++;
}
pck.SaveAs(this.Page.Response.OutputStream);
this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
this.Page.Response.AddHeader("content-disposition", "attachment; filename=List_of_Employees_as_on_" + DateTime.Now.ToString("dd_MMM_yyyy_HH_mm_ss") + ".xlsx");
this.Page.Response.Flush();
this.Page.Response.End();
}
}
}Deployment
Build the Application and deploy it and add it to an existing page.You can expect a screen like the one shown below. Now you can use this page to upload the excel file which I have mentioned at the start of the post.
List After data uploaded
To download list data as excel file ,click the download button.
That’s it. Now you might have got a basic idea on how to perform a Upload or Download an Excel File from a SharePoint List
Leave a comment