Ultra Fast Excel Export Component for MVC4

• Download source - 24.2 KB
• Download source (external link)

In this post, I want to show you how to create a component in C#4 for exporting data in Excel 2007 XLSX format.
I’m not going to 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 going to create a generic class with an IList<t> 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<t> 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 simply renamed ZIP files, we will need to use a zip component to read/write zip files. We’re going to use SharpZipLib.

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 in 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 an 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<string,int> 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 reading!

CodeProject