Click here to Skip to main content
11,478,174 members (69,100 online)
Click here to Skip to main content

Read and Write Microsoft Excel with Open XML SDK

, 18 Oct 2013 CPOL 71.5K 8.1K 39
Rate this:
Please Sign up or sign in to vote.
This article presents an example on how to read and write Microsoft Excel with Open XML SDK.

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];
                // ASCII 'A' = 65
                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();
	
            // Check if the file is excel
            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;
            }
	
            // Open the excel document
            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;
            }
	
            // Read the header
            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);
                }
            }
	
            // Read the sheet data
            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);
	
            // Add header
            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)
            {
                // Add the column configuration if available
                if (data.ColumnConfigurations != null)
                {
                    var columns = (Columns)data.ColumnConfigurations.Clone();
                    worksheetPart.Worksheet
                        .InsertAfter(columns, worksheetPart
                        .Worksheet.SheetFormatProperties);
                }
            }
	
            // Add sheet data
            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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dr. Song Li

United States United States
I have been working in the IT industry for some time. It is still exciting and I am still learning. I am a happy and honest person, and I want to be your friend.

Comments and Discussions

 
Questioncan we use formatting on excel using open xml Pin
Member 1134024216-Mar-15 20:20
memberMember 1134024216-Mar-15 20:20 
AnswerRe: can we use formatting on excel using open xml Pin
Dr. Song Li17-Mar-15 4:09
mvpDr. Song Li17-Mar-15 4:09 
GeneralRe: can we use formatting on excel using open xml Pin
mmesser_Optum12-Apr-15 13:27
membermmesser_Optum12-Apr-15 13:27 
GeneralMy vote of 1 Pin
Fernando Mello22-Sep-14 14:43
memberFernando Mello22-Sep-14 14:43 
AnswerBravo! This helps me move toward my solution! Thanks! Pin
knick26-Mar-14 6:07
memberknick26-Mar-14 6:07 
Questioncoloring of excel sheet based on cell values using Open XML Pin
Supriya Srivastava18-Mar-14 1:12
memberSupriya Srivastava18-Mar-14 1:12 
QuestionIs Open XML SDK recommended for reading excel spreadsheet-documents these days instead of OleDb? [modified] Pin
Member 1063244627-Feb-14 23:58
memberMember 1063244627-Feb-14 23:58 
AnswerRe: Is Open XML SDK recommended for reading excel spreadsheet-documents these days instead of OleDb? Pin
Dr. Song Li28-Feb-14 5:54
mvpDr. Song Li28-Feb-14 5:54 
GeneralThanks, It's realy helped me!! Pin
Member 1047641927-Feb-14 1:24
memberMember 1047641927-Feb-14 1:24 
QuestionMultiline cells shown as single line until I double click Pin
prichintha18-Feb-14 3:04
memberprichintha18-Feb-14 3:04 
AnswerRe: Multiline cells shown as single line until I double click Pin
Dr. Song Li18-Feb-14 3:57
mvpDr. Song Li18-Feb-14 3:57 
QuestionMicrosoft JScript runtime error Pin
bobk54423-Dec-13 9:23
memberbobk54423-Dec-13 9:23 
AnswerRe: Microsoft JScript runtime error Pin
Dr. Song Li23-Dec-13 9:31
memberDr. Song Li23-Dec-13 9:31 
AnswerRe: Microsoft JScript runtime error Pin
Dr. Song Li23-Dec-13 9:42
memberDr. Song Li23-Dec-13 9:42 
GeneralRe: Microsoft JScript runtime error Pin
bobk54423-Dec-13 10:05
memberbobk54423-Dec-13 10:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 18 Oct 2013
Article Copyright 2013 by Dr. Song Li
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid