Introduction
This article presents an example on how to read and write Microsoft Excel with Open XML SDK.
Background
In the Information Technology, it is very common that the users will give us the following requests:
- Given some data in the database or any other data storage, you will be asked to create an Excel file with the data and present the Excel file to the users;
- Given an Excel file, you will be asked to read the data and process it in your program.
The article will show you an example to read from and write to Microsoft Excel files with the Open XML SDK. The example is a MVC 2 web application written in Visual Studio 2010. To keep things simple, the example only reads the first sheet of the Excel files, and the Excel files created by the example also have only a single sheet.
I am well aware that the current version of the ASP.Net MVC is 4. I am also aware that not all the people are using the most recent version. The purpose to keep the example application in a lower version MVC and Visual Studio 2010 is to make the readers easier to download and run the example, because a lot of the readers do not have the most recent version of the Visual Studio.
- The "Controllers\HomeController.cs" is the application's controller;
- The "Views\Home\Index.aspx" is the application's view;
- The 3 files in the "SLExcelUtility" folder implements the data and utility classes to read from and write to the Excel files.
You can download the complete
Open XML SDK from
here. The attached Visual Studio solution comes with the core SDK "DocumentFormat.OpenXml.dll", so you can simply download the attached solution and open it in your Visual Studio and compile to run the example application.
The Example Excel File
The following picture shows the example Excel file attached in this article, which has the information of 1000 randomly generated students.
The purpose of the example web application is to allow users to upload the Excel file to the web server. When the Excel file is uploaded, the application will read the data in the file and save the data in the web session. The web application then shows the users a hyperlink. If the users click on the link, the web application will use the data in the session to recreate an Excel file and download it back to the user. So this example web application shows you both reading from and writing to Excel files.
The Excel Utility Classes
The "SLExcelUtility\SLExcelData.cs" file implements the data class to keep the data in the Excel file.
using System.Collections.Generic;
using DocumentFormat.OpenXml.Spreadsheet;
namespace OpenXMLExcel.SLExcelUtility
{
public class SLExcelStatus
{
public string Message { get; set; }
public bool Success
{
get { return string.IsNullOrWhiteSpace(Message); }
}
}
public class SLExcelData
{
public SLExcelStatus Status { get; set; }
public Columns ColumnConfigurations { get; set; }
public List<string> Headers { get; set; }
public List<List<string>> DataRows { get; set; }
public string SheetName { get; set; }
public SLExcelData()
{
Status = new SLExcelStatus();
Headers = new List<string>();
DataRows = new List<List<string>>();
}
}
} The "SLExcelData" class is used to store the data read from the Excel file.
- The "Status" property keeps any possible errors when reading the Excel file;
- The "Headers" property keeps the data in the first row of the Excel sheet;
- The "DataRows" property keeps the data for the rest of the rows of the Excel sheet;
- The "SheetName" property keeps the name of the Excel sheet;
- The "ColumnConfigurations" property keeps the configuration information of the columns in the Excel sheet.
The "SLExcelUtility\SLExcelReader.cs" implements the class to read the Excel file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace OpenXMLExcel.SLExcelUtility
{
public class SLExcelReader
{
private string GetColumnName(string cellReference)
{
var regex = new Regex("[A-Za-z]+");
var match = regex.Match(cellReference);
return match.Value;
}
private int ConvertColumnNameToNumber(string columnName)
{
var alpha = new Regex("^[A-Z]+$");
if (!alpha.IsMatch(columnName)) throw new ArgumentException();
char[] colLetters = columnName.ToCharArray();
Array.Reverse(colLetters);
var convertedValue = 0;
for (int i = 0; i < colLetters.Length; i++)
{
char letter = colLetters[i];
int current = i == 0 ? letter - 65 : letter - 64;
convertedValue += current * (int)Math.Pow(26, i);
}
return convertedValue;
}
private IEnumerator<Cell> GetExcelCellEnumerator(Row row)
{
int currentCount = 0;
foreach (Cell cell in row.Descendants<Cell>())
{
string columnName = GetColumnName(cell.CellReference);
int currentColumnIndex = ConvertColumnNameToNumber(columnName);
for (; currentCount < currentColumnIndex; currentCount++)
{
var emptycell = new Cell()
{
DataType = null, CellValue = new CellValue(string.Empty)
};
yield return emptycell;
}
yield return cell;
currentCount++;
}
}
private string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
{
var cellValue = cell.CellValue;
var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
{
text = workbookPart.SharedStringTablePart.SharedStringTable
.Elements<SharedStringItem>().ElementAt(
Convert.ToInt32(cell.CellValue.Text)).InnerText;
}
return (text ?? string.Empty).Trim();
}
public SLExcelData ReadExcel(HttpPostedFileBase file)
{
var data = new SLExcelData();
if (file.ContentLength <= 0)
{
data.Status.Message = "You uploaded an empty file";
return data;
}
if (file.ContentType
!= "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
data.Status.Message
= "Please upload a valid excel file of version 2007 and above";
return data;
}
WorkbookPart workbookPart; List<Row> rows;
try
{
var document = SpreadsheetDocument.Open(file.InputStream, false);
workbookPart = document.WorkbookPart;
var sheets = workbookPart.Workbook.Descendants<Sheet>();
var sheet = sheets.First();
data.SheetName = sheet.Name;
var workSheet = ((WorksheetPart)workbookPart
.GetPartById(sheet.Id)).Worksheet;
var columns = workSheet.Descendants<Columns>().FirstOrDefault();
data.ColumnConfigurations = columns;
var sheetData = workSheet.Elements<SheetData>().First();
rows = sheetData.Elements<Row>().ToList();
}
catch (Exception e)
{
data.Status.Message = "Unable to open the file";
return data;
}
if (rows.Count > 0)
{
var row = rows[0];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
data.Headers.Add(text);
}
}
if (rows.Count > 1)
{
for (var i = 1; i < rows.Count; i++)
{
var dataRow = new List<string>();
data.DataRows.Add(dataRow);
var row = rows[i];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
dataRow.Add(text);
}
}
}
return data;
}
}
} The "ReadExcel" method takes the Excel file as input and returns an object of the "SLExcelData" class. If the read is successful, the returned "SLExcelData" object will have the data in the Excel sheet. You may pay some attention on the two private methods.
- The "ReadExcelCell" method read the data in an Excel cell. When the cell is a "SharedString", we will need to get the data from a shared location;
- The "GetExcelCellEnumerator" method turns the Excel row into an Enumerator. The reason why we need this enumerator is that when an Excel cell is empty, the cell simply does not exist in the Open XML "Row".
The "SLExcelWriter.cs" class implements the class to create an Excel file from a "SLExcelData" object.
using System;
using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace OpenXMLExcel.SLExcelUtility
{
public class SLExcelWriter
{
private string ColumnLetter(int intCol)
{
var intFirstLetter = ((intCol) / 676) + 64;
var intSecondLetter = ((intCol % 676) / 26) + 64;
var intThirdLetter = (intCol % 26) + 65;
var firstLetter = (intFirstLetter > 64)
? (char)intFirstLetter : ' ';
var secondLetter = (intSecondLetter > 64)
? (char)intSecondLetter : ' ';
var thirdLetter = (char)intThirdLetter;
return string.Concat(firstLetter, secondLetter,
thirdLetter).Trim();
}
private Cell CreateTextCell(string header, UInt32 index,
string text)
{
var cell = new Cell
{
DataType = CellValues.InlineString,
CellReference = header + index
};
var istring = new InlineString();
var t = new Text { Text = text };
istring.AppendChild(t);
cell.AppendChild(istring);
return cell;
}
public byte[] GenerateExcel(SLExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument
.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
var sheet = new Sheet() { Id = document.WorkbookPart
.GetIdOfPart(worksheetPart),
SheetId = 1, Name = data.SheetName??"Sheet 1" };
sheets.AppendChild(sheet);
UInt32 rowIdex = 0;
var row = new Row { RowIndex = ++rowIdex };
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, header??string.Empty));
}
if (data.Headers.Count > 0)
{
if (data.ColumnConfigurations != null)
{
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart
.Worksheet.SheetFormatProperties);
}
}
foreach (var rowData in data.DataRows)
{
cellIdex = 0;
row = new Row { RowIndex = ++rowIdex };
sheetData.AppendChild(row);
foreach (var callData in rowData)
{
var cell = CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, callData??string.Empty);
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
document.Close();
return stream.ToArray();
}
}
} The "GenerateExcel" method takes an object of type "SLExcelData" and put the data into an Excel sheet. The Excel file is returned in the form of a byte array.
The MVC Application
Built upon the Excel utility classes, the MVC application is pretty simple.
The "Controllers\HomeController.cs" is implemented as the following:
using System.Web;
using System.Web.Mvc;
using OpenXMLExcel.SLExcelUtility;
namespace OpenXMLExcel.Controllers
{
public class HomeController : Controller
{
private const string SessionExcelData = "SessionExcelData";
public ActionResult Index()
{
return View();
}
public ActionResult UploadFile(HttpPostedFileBase excelFile)
{
var data = (new SLExcelReader()).ReadExcel(excelFile);
Session[SessionExcelData] = data;
return View("Index", data.Status);
}
public ActionResult DownloadFile()
{
var data = (SLExcelData) Session[SessionExcelData];
if (data == null)
{
ViewData["RedirectUrl"] = Url.Action("Index");
return View("SessionExpired");
}
var file = (new SLExcelWriter()).GenerateExcel(data);
Response.AddHeader("Content-Disposition",
"attachment; filename=ExcelFile.xlsx");
return new FileContentResult(file,
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}
}
} - The action method "Index" loads the view page;
- The action method "UploadFile" uses the "SLExcelReader" class to read the uploaded Excel file and keeps the "SLExcelData" object in the web session;
- The action method "DownloadFile" uses the "SLExcelWriter" to create an Excel file based on the "SLExcelData" object in the web session.
The "Views\Home\Index.aspx" file is implemented as the following:
<%@ Page Language="C#"
Inherits="System.Web.Mvc.ViewPage<OpenXMLExcel.SLExcelUtility.SLExcelStatus>" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Upload & Download Excel Example</title>
<link href="<%=Url.Content("~/Content/AppStyle.css") %>" rel="stylesheet"
type="text/css" />
<script type="text/javascript"
src="<%=Url.Content("~/Scripts/jquery-2.0.3.min.js") %>"></script>
<script type="text/javascript"
src="<%=Url.Content("~/Scripts/OpenXMLExcelAppScript.js") %>"></script>
</head>
<body>
<form action="<%=Url.Action("UploadFile") %>"
enctype="multipart/form-data" method="post">
<div style="margin: 10px">
<%if (Model != null)
{%>
<p>
<%if (Model.Success)
{%>
<a href="<%= Url.Action("DownloadFile") %>">
Excel file is uploaded successfully, let us download it back
</a>
<% }
else
{%>
<label class="errorMsg"><%=Model.Message %></label>
<% } %>
</p>
<% } %>
<p>
<button type="button" id="btnSubmit">Submit</button>
<input type="file" name="excelFile"/>
</p>
</div>
</form>
</body>
</html> The JavaScript code used by the view page is the following:
$(document).ready(function () {
$('#btnSubmit').click(function () {
var fileName = $('[name="excelFile"]').val().trim();
var pos = fileName.lastIndexOf('.');
var extension = (pos <= 0) ? '' : fileName.substring(pos);
if (extension != '.xlsx') {
alert('Please browse a correct excel file to upload');
return;
}
$('form').submit();
});
}); Run the Application
We now finish this simple example application, we can then test run it. When the web application first launches, it show us the buttons to allow use to browse the Excel file and upload it to the server.
We can then choose the attached Excel file in this article and upload it. When the upload succeeds, we will have a download link.
If we click on the download link, an Excel file is generated by the data in the file that we just uploaded.
We can open this file and compare with the file that we just uploaded. We should see that the two Excel files have the same data. You do not have to upload the Excel file comes with this article, you can actually upload any Excel file and see how the program works. You should keep in the following in mind though,
- The Excel utility classes in the article only process the first sheet of the Excel file. If you have more than 1 sheet, it only reads the first sheet and the generated Excel has only the data of the first sheet of the Excel file uploaded.
- The Excel utility classes only process the data in the Excel files. If you have a fancy Excel sheet that has a lot of beautiful formatting and pictures, they are not processed by the utility classes.
Points of Interest
- This article presents an example on how to read and write Microsoft Excel with Open XML SDK.
- The Excel utility classes in the article only process the first sheet of the Excel file. If you have more than 1 sheet, it only reads the first sheet and the generated Excel has only the data of the first sheet of the Excel file uploaded. If you need to handle multiple sheets, you can easily extend these classes to do it.
- The Excel utility classes only process the data in the Excel files. If you have a fancy Excel sheet that has a lot of beautiful formatting and pictures, they are not processed by the utility classes.
- You do not need to use the Excel file attached in this article to test the application, you can choose any excel file to see how it works.
- I have tested this example multiple times, but my own test is really limited. If you find any problems when you test it, please do not hesitate to let me know. I will make my effort to make it to work better, if I have some time to spare.
- I hope you like my postings and I hope this article can help you one way or the other.
History
First Revision - 10/18/2013.
