Click here to Skip to main content
Click here to Skip to main content

Ultra fast Excel export component for MVC4

, 22 Apr 2014
Rate this:
Please Sign up or sign in to vote.
Hi all. Today I want to show you how to create a component in C#4 for exporting data in Excel 2007 XLSX format. I’m not gonna talk about the XLSX format, which I guess you’re already familiar with. Instead, I’ll … Continue reading →

Hi all.
Today I want to show you how to create a component in C#4 for exporting data in Excel 2007 XLSX format.
I’m not gonna talk about the XLSX format, which I guess you’re already familiar with. Instead, I’ll focus more on how to make the component very fast, by leveraging generics, and how to make it work on files or over the response stream (MVC/ASP).

The IXlsxExporter interface
We start by defining the public members that our component will have.

public interface IXlsxExporter<T>
{
    void AddTextColumn(string caption, Func<T, string> getValue);
    void AddDateColumn(string caption, Func<T, DateTime> getValue);
    void AddIntegerColumn(string caption, Func<T, int> getValue);
    
    IList<T> DataSource { get; set; }

    void Export(Stream outStream);
}

To get a super-fast export component, we’re gonna create a generic class with an IList object as datasource. This will give us the best performance, since we don’t need to use reflection to get member information.

The DataSource itself is any IList class.
And the Export method allows us to write over any stream, being a file or a response stream.

Columns
Each Add***Column method allows the developer to add a new column to the export document. The Func parameter is the lambda expression we have to use to extract the value for each element in the source. You will need additional methods, one for each value type you need, but this allows box/unbox and gives a super fast component.

internal abstract class XlsxColumn<T>
{
    internal string Caption { get; set; }
    internal abstract Type ValueType { get; }
    internal abstract string GetDisplayValueFor(T element);
}

Zip library and template file
As the XLSX docs are simple renamed ZIP files, we will need to use a zip component to read/write zip files. We’re gonna use SharZipLib http://www.sharpziplib.com/

Inside each XLSX file there are several compressed files. Because we’ll focus only on the sheet and the string table document, we will use a blank xlsx file as a template, then we’ll simply write the data we want to export, maintaining all other files.

We’ll create a new file in excel and embed it on our component library.

The Export method
The Export method is the most important. It does all of the unzip template/write data/zip contents operations.

//First, create string tables
CreateStringTables();

//create the zip output file
using (ZipOutputStream zipOutStream = new ZipOutputStream(outStream))
{
    zipOutStream.SetLevel(6);

    using (ZipInputStream zipInStream = new ZipInputStream(Assembly.GetExecutingAssembly()
        .GetManifestResourceStream(TEMPLATE_FILE)))
    {
        //loop over all zip entries
        ZipEntry readEntry;
        while ((readEntry = zipInStream.GetNextEntry()) != null)
        {
            //creates the new zip entry based on the entry read
            ZipEntry writeEntry = new ZipEntry(readEntry.Name);
            zipOutStream.PutNextEntry(writeEntry);

            //shared strings?
            if (readEntry.Name == @"xl/sharedStrings.xml")
            {
                WriteStringTable(zipOutStream);
            }
            //worksheet file?
            else if (readEntry.Name == @"xl/worksheets/sheet1.xml")
            {
                WriteWorksheet(zipOutStream);
            }
            else
            {
                //template file, no modification, just read and write
                //read the whole stream and put it on the target
                int size = 2048;
                byte[] data = new byte[2048];
                while (size > 0)
                {
                    size = zipInStream.Read(data, 0, data.Length);
                    if (size > 0)
                        zipOutStream.Write(data, 0, size);
                }
            }
        }
    }
}

Create the String Table
As you can see in the code, the first task is creating the String Table. This is a XML file where all unique strings are stored in a dictionary like way. Excel uses this to save space when same strings are used across cells/sheets.

To create this, we simply use a Dictionary where each unique string corresponds to a unique position in the file.

//init collections
_StringsTableCount = 0;
_LookupTable = new Dictionary<string, int>();

for(int rowIndex = 0; rowIndex < RowCount; rowIndex++)
{
    T element = _DataSource[rowIndex];
    for(int colIndex = 0; colIndex < ColumnCount; colIndex++)
    {
        XlsxColumn<T> column = _Columns[colIndex];
        if(column.ValueType == typeof(string))
        {
            string value = column.GetDisplayValueFor(element) ?? "";
            value = value.Trim();
            if (string.IsNullOrEmpty(value))
                continue;

            if(!_LookupTable.ContainsKey(value))
                _LookupTable.Add(value, _LookupTable.Count);

            _StringsTableCount++;
        }

    }
}

Helper methods
We use this to translate column indexes to letters, as required by the Excel format.

/// <summary>
/// Returns the column/row name from given row/column index
/// </summary>
private string RowColumnToPosition(int row, int column)
{
    return ColumnIndexToName(column) + RowIndexToName(row);
}

/// <summary>
/// Returns the column name A,B,C from column index
/// </summary>
private string ColumnIndexToName(int columnIndex)
{
    var second = (char)(((int)'A') + columnIndex % 26);
    columnIndex /= 26;

    if (columnIndex == 0)
        return second.ToString();
    else
        return ((char)(((int)'A') - 1 + columnIndex)).ToString() + second.ToString();
}

/// <summary>
/// Returns the row name from row index
/// </summary>
private string RowIndexToName(int rowIndex)
{
    return (rowIndex + 1).ToString(CultureInfo.InvariantCulture);
}

Writing the sheet data

We write the sheet data using this method, which uses the helper functions to get the row/column name from the index position, and the ValueType property of each column, to know how to properly output the value.

string relPos;
for(int rowIndex = 0; rowIndex < RowCount; rowIndex++)
{
    T element = _DataSource[rowIndex];

    //start row element
    writer.WriteStartElement("row");

    //write relative position
    relPos = RowIndexToName(rowIndex);
    writer.WriteAttributeString("r", relPos);

    //write spans
    writer.WriteAttributeString("spans", "1:" + ColumnCount.ToString(CultureInfo.InvariantCulture));

    //write all columns data
    for (int colIndex = 0; colIndex < ColumnCount; colIndex++)
    {
        XlsxColumn<T> column = _Columns[colIndex];
        string value = column.GetDisplayValueFor(element) ?? "";
        value = value.Trim();
        if (string.IsNullOrEmpty(value))
            continue;

        writer.WriteStartElement("c");
        relPos = RowColumnToPosition(rowIndex, colIndex);
        writer.WriteAttributeString("r", relPos);

        //use lookup table to write string ref
        if (column.ValueType == typeof(string))
        {
            writer.WriteAttributeString("t", "s");
            writer.WriteElementString("v", _LookupTable[value].ToString());
        }
        else
        {
            if (column.ValueType == typeof(DateTime))
                writer.WriteAttributeString("s", "1");
            if(column.ValueType == typeof(int) || column.ValueType == typeof(double))
                writer.WriteAttributeString("t", "n");
            else if (column.ValueType == typeof(bool))
                writer.WriteAttributeString("t", "b");
            writer.WriteElementString("v", value);
        }

        writer.WriteEndElement();
    }

    writer.WriteEndElement();
}

Sample usage output to a file

XlsxExporter<Customer> exporter = new XlsxExporter<Customer>();
exporter.DataSource = service.GetQuarantines();

exporter.AddTextColumn("Name", s => s.Name);
exporter.AddTextColumn("Address", s => s.Address);
exporter.AddDateColumn("Create", s => s.Create);

using (FileStream fs = new FileStream(@"c:\temp\test.xlsx", FileMode.Create))
{
    exporter.Export(fs);
}

Sample usage output to response

HttpContext.Response.Clear();
HttpContext.Response.Buffer = false;

HttpContext.Response.AddHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
HttpContext.Response.AddHeader("Content-Disposition", "attachment;filename=export.xlsx");
HttpContext.Response.AddHeader("Content-Transfer-Encoding", "binary");

XlsxExporter<Customer> exporter = new XlsxExporter<Customer>();
exporter.DataSource = GetSampleData();

exporter.AddTextColumn("Name", s => s.Name);
exporter.AddTextColumn("Address", s => s.Address);

using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
    exporter.Export(ms);
    HttpContext.Response.BinaryWrite(ms.ToArray());
}

HttpContext.Response.End();

Done
As you can check, it’s very fast, due to the generic mechanism, avoiding box/unbox operations.
Thanks for your readings!

Download Source

License

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

About the Author

mopicus
Software Developer (Senior) Ford Motor Company
Spain Spain
No Biography provided
Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionLooks great Pinmemberredjoy23-Apr-14 3:53 
AnswerRe: Looks great Pinprofessionalmopicus24-Apr-14 4:17 
GeneralMy vote of 4 Pinmembergore0122-Apr-14 19:38 
GeneralRe: My vote of 4 Pinprofessionalmopicus24-Apr-14 4:15 

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 | Mobile
Web01 | 2.8.140718.1 | Last Updated 22 Apr 2014
Article Copyright 2014 by mopicus
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid