Creating and streaming an OpenXML Spreadsheet using the .Net SDK and ASP.NET MVC

I wanted to use the OpenXML SDK to create an Excel 2007 spreadsheet on the fly and stream it directly without having to save it to a physical file first. As usual documentation on the Net was pretty sketchy, and the bits that I did find usually frustratingly left a crucial point out. (As an aside, if developers really want their products to be used then perhaps they should actually tell us how to use them – not everyone has all day to fiddle!).

Anyway, below is my solution for a very simple spreadsheet generator service and ActionResult. This just allowed me to create a single worksheet table of data, which was all I required. Hopefully it is a good start point if you want anything more complex. I dare say some points are done clunkily but I didn’t have the time to figure out anything else! Some parts I grabbed from other sites on the Net, but most was added myself.

Update: I have since updated the class to allow multiple worksheets to be added. If anyone is interested I put this in a separate post, to avoid making this one too long.

Note: The OpenXML SDK dll is installed direct to the GAC. So when you publish the website it will not get copied to your apps’s bin directory as Visual Studio seems to assume that as it is in your GAC it will also be on the server. So you need to either install it on your server too, or manually copy it to your app’s bin directory.

Here is the code along with some explanation in the comments – note I am assuming you are using S#arp Architecture.

First the custom ActionResult, which should hopefully be self-explanatory:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Mvc;
using System.IO;
using SharpArch.Core;

namespace MyProject.Web.Controllers.CustomActionResults
{
    public class OpenSpreadsheetResult : ActionResult
    {
        private MemoryStream _spreadStream;
        private string _fileName;

        public OpenSpreadsheetResult(MemoryStream spreadsheetStream, string fileName)
        {
            Check.Require(spreadsheetStream != null, "Spreadsheet stream cannot be null");
            _spreadStream = spreadsheetStream;

            if (String.IsNullOrEmpty(fileName) == true)
                _fileName = "Default.xlsx";
            else
                _fileName = fileName;
        }

        public override void ExecuteResult(ControllerContext context)
        {

            context.HttpContext.Response.Clear();
            context.HttpContext.Response.AddHeader("content-disposition",
              String.Format("attachment;filename={0}", _fileName));
            context.HttpContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            _spreadStream.WriteTo(context.HttpContext.Response.OutputStream);
            _spreadStream.Close();
            context.HttpContext.Response.End(); 
                    
        } 
    }
}

Create the basic spreadsheet generator service, and also an interface to it if you are using the Castle Windsor part of S#arp:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace MyProject.ApplicationServices.ReportsServices
{
    public class SpreadsheetService: ISpreadsheetService
    {
        public MemoryStream SpreadsheetStream { get; set; } // The stream that the spreadsheet gets returned on
        private Worksheet currentWorkSheet { get { return spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet; } }
        private SpreadsheetDocument spreadSheet;
        private Columns _cols;

        /// <summary>
        /// Create a basic spreadsheet template 
        /// The structure of OpenXML spreadsheet is something like this from what I can tell:
        ///                        Spreadsheet
        ///                              |         
        ///                         WorkbookPart    
        ///                   /         |             \
        ///           Workbook WorkbookStylesPart WorksheetPart
        ///                 |          |               |
        ///            Sheets     StyleSheet        Worksheet
        ///                |                        /        \       
        ///          (refers to               SheetData        Columns  
        ///           Worksheetparts)            |   
        ///                                     Rows 
        /// 
        /// Obviously this only covers the bits in this class!
        /// </summary>
        /// <returns></returns>
        public bool CreateSpreadsheet()
        {
            try
            {
                SpreadsheetStream = new MemoryStream();

                // Create the spreadsheet on the MemoryStream
                spreadSheet =
                         SpreadsheetDocument.Create(SpreadsheetStream, SpreadsheetDocumentType.Workbook);

                WorkbookPart wbp = spreadSheet.AddWorkbookPart();   // Add workbook part
                WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>(); // Add worksheet part
                Workbook wb = new Workbook(); // Workbook
                FileVersion fv = new FileVersion();
                fv.ApplicationName = "App Name";
                Worksheet ws = new Worksheet(); // Worksheet
                SheetData sd = new SheetData(); // Data on worksheet

                // Add stylesheet
                WorkbookStylesPart stylesPart = spreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                stylesPart.Stylesheet = GenerateStyleSheet();
                stylesPart.Stylesheet.Save();

              
                _cols = new Columns(); // Created to allow bespoke width columns
               
                ws.Append(sd); // Add sheet data to worksheet
                wsp.Worksheet = ws; // Add the worksheet to the worksheet part
                wsp.Worksheet.Save();
                // Define the sheets that the workbooks has in it.
                
                Sheets sheets = new Sheets();
                Sheet sheet = new Sheet();
                sheet.Name = "StudentData";
                sheet.SheetId = 1; // Only one sheet per spreadsheet in this class so call it sheet 1
                sheet.Id = wbp.GetIdOfPart(wsp); // ID of sheet comes from worksheet part
                sheets.Append(sheet);
                wb.Append(fv);
                wb.Append(sheets); // Append sheets to workbook

                spreadSheet.WorkbookPart.Workbook = wb;
                spreadSheet.WorkbookPart.Workbook.Save();
               
            }
            catch
            {
                return false;
            }

            return true;
        }


        /// <summary>
        /// add the bespoke columns for the list spreadsheet
        /// </summary>
        public void CreateColumnWidth(uint startIndex, uint endIndex, double width)
        {
            // Find the columns in the worksheet and remove them all
            if (currentWorkSheet.Where(x => x.LocalName == "cols").Count() > 0)
                currentWorkSheet.RemoveChild<Columns>(_cols);

            // Create the column
            Column column = new Column();
            column.Min = startIndex;
            column.Max = endIndex;
            column.Width = width;
            column.CustomWidth = true;
            _cols.Append(column); // Add it to the list of columns

            // Make sure that the column info is inserted *before* the sheetdata
            currentWorkSheet.InsertBefore<Columns>(_cols, currentWorkSheet.Where(x => x.LocalName == "sheetData").First());
            currentWorkSheet.Save();
            spreadSheet.WorkbookPart.Workbook.Save();

        }

        /// <summary>
        /// Close the spreadsheet
        /// </summary>
        public void CloseSpreadsheet()
        {
            spreadSheet.Close();
        }

        /// <summary>
        /// Pass a list of column headings to create the header row
        /// </summary>
        /// <param name="headers"></param>
        public void AddHeader(List<string> headers)
        {
            // Find the sheetdata of the worksheet
            SheetData sd = (SheetData)currentWorkSheet.Where(x => x.LocalName == "sheetData").First();
            Row header = new Row();
            // increment the row index to the next row
            header.RowIndex = Convert.ToUInt32(sd.ChildElements.Count()) + 1; 
            sd.Append(header); // Add the header row

            foreach (string heading in headers)
            {
                AppendCell(header, header.RowIndex, heading, 1);
  
            }

            // save worksheet
            currentWorkSheet.Save();
           
        }


        /// <summary>
        /// Pass a list of data items to create a data row
        /// </summary>
        /// <param name="dataItems"></param>
        public void AddRow(List<string> dataItems)
        {
            // Find the sheetdata of the worksheet
            SheetData sd = (SheetData)currentWorkSheet.Where(x => x.LocalName == "sheetData").First();
            Row header = new Row();
            // increment the row index to the next row
            header.RowIndex = Convert.ToUInt32(sd.ChildElements.Count()) + 1;

            
            sd.Append(header);

            foreach (string item in dataItems)
            {
                AppendCell(header, header.RowIndex, item, 0);

            }

            // save worksheet
            currentWorkSheet.Save();
        }

        /// <summary>
        /// Add cell into the passed row.
        /// </summary>
        /// <param name="row"></param>
        /// <param name="rowIndex"></param>
        /// <param name="value"></param>
        /// <param name="styleIndex"></param>
        private void AppendCell(Row row,  uint rowIndex, string value, uint styleIndex)
        {
            Cell cell = new Cell();
            cell.DataType = CellValues.InlineString;
            cell.StyleIndex = styleIndex;  // Style index comes from stylesheet generated in GenerateStyleSheet()
            Text t = new Text();
            t.Text = value;

            // Append Text to InlineString object
            InlineString inlineString = new InlineString();
            inlineString.AppendChild(t);

            // Append InlineString to Cell
            cell.AppendChild(inlineString);

            // Get the last cell's column
            string nextCol = "A";
            Cell c = (Cell)row.LastChild;
            if (c != null) // if there are some cells already there...
            {
                int numIndex = c.CellReference.ToString().IndexOfAny(new char[] { '1', '2', '3', '4', '5', '6', '7', '8', '9' });

                // Get the last column reference
                string lastCol = c.CellReference.ToString().Substring(0, numIndex);
                // Increment
                nextCol = IncrementColRef(lastCol);
            }

            cell.CellReference = nextCol + rowIndex;

            row.AppendChild(cell);
        }

        // Increment the column reference in an Excel fashion, i.e. A, B, C...Z, AA, AB etc.
        // Partly stolen from somewhere on the Net and modified for my use.
        private string IncrementColRef(string lastRef)
        {
            char[] characters = lastRef.ToUpperInvariant().ToCharArray();
            int sum = 0;
            for (int i = 0; i < characters.Length; i++)
            {
                sum *= 26;
                sum += (characters[i] - 'A' + 1);
            }

            sum++;

            string columnName = String.Empty;
            int modulo;

            while (sum > 0)
            {
                modulo = (sum - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                sum = (int)((sum - modulo) / 26);
            }

            return columnName;


        }

        /// <summary>
        /// Return a stylesheet. Completely stolen from somewhere, possibly this guy's blog,
        /// although I can't find it on there:
        /// http://polymathprogrammer.com/. Thanks whoever it was, it would have been a 
        /// nightmare trying to figure this one out!
        /// </summary>
        /// <returns></returns>
        private Stylesheet GenerateStyleSheet()
        {
            return new Stylesheet(
                new Fonts(
                    new Font(                                                               // Index 0 - The default font.
                        new FontSize() { Val = 11 },
                        new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                        new FontName() { Val = "Calibri" }),
                    new Font(                                                               // Index 1 - The bold font.
                        new Bold(),
                        new FontSize() { Val = 11 },
                        new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                        new FontName() { Val = "Calibri" }),
                    new Font(                                                               // Index 2 - The Italic font.
                        new Italic(),
                        new FontSize() { Val = 11 },
                        new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                        new FontName() { Val = "Calibri" }),
                    new Font(                                                               // Index 2 - The Times Roman font. with 16 size
                        new FontSize() { Val = 16 },
                        new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                        new FontName() { Val = "Times New Roman" })
                ),
                new Fills(
                    new Fill(                                                           // Index 0 - The default fill.
                        new PatternFill() { PatternType = PatternValues.None }),
                    new Fill(                                                           // Index 1 - The default fill of gray 125 (required)
                        new PatternFill() { PatternType = PatternValues.Gray125 }),
                    new Fill(                                                           // Index 2 - The yellow fill.
                        new PatternFill(
                            new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } }
                        ) { PatternType = PatternValues.Solid })
                ),
                new Borders(
                    new Border(                                                         // Index 0 - The default border.
                        new LeftBorder(),
                        new RightBorder(),
                        new TopBorder(),
                        new BottomBorder(),
                        new DiagonalBorder()),
                    new Border(                                                         // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
                        new LeftBorder(
                            new Color() { Auto = true }
                        ) { Style = BorderStyleValues.Thin },
                        new RightBorder(
                            new Color() { Auto = true }
                        ) { Style = BorderStyleValues.Thin },
                        new TopBorder(
                            new Color() { Auto = true }
                        ) { Style = BorderStyleValues.Thin },
                        new BottomBorder(
                            new Color() { Auto = true }
                        ) { Style = BorderStyleValues.Thin },
                        new DiagonalBorder())
                ),
                new CellFormats(
                    new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 },                          // Index 0 - The default cell style.  If a cell does not have a style index applied it will use this style combination instead
                    new CellFormat() { FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true },       // Index 1 - Bold 
                    new CellFormat() { FontId = 2, FillId = 0, BorderId = 0, ApplyFont = true },       // Index 2 - Italic
                    new CellFormat() { FontId = 3, FillId = 0, BorderId = 0, ApplyFont = true },       // Index 3 - Times Roman
                    new CellFormat() { FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true },       // Index 4 - Yellow Fill
                    new CellFormat(                                                                   // Index 5 - Alignment
                        new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
                    ) { FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true },
                    new CellFormat() { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }      // Index 6 - Border
                )
            ); // return
        }

    }
}

This is how your would call the methods of the above class. I put this method in a separate ‘reportManager’ class, but I guess you could just put it in the controller as a quicky:

        // These private variables are instantiated by injection into the constructor
        private IMyRepo _myRepo;
        private ISpreadsheetService _spreadsheetService;
      public MemoryStream GenerateExcelList(// any parameters to your report here)
        {
            if (_spreadsheetService.CreateSpreadsheet() == true)
            {
                // Create a few columns
                _spreadsheetService.CreateColumnWidth(1, 1, 15);
                _spreadsheetService.CreateColumnWidth(2, 3, 20);
                _spreadsheetService.CreateColumnWidth(4, 4, 40);
                _spreadsheetService.CreateColumnWidth(5, 5, 15);
                _spreadsheetService.CreateColumnWidth(6, 7, 50);
                _spreadsheetService.CreateColumnWidth(8, 8, 25);
                _spreadsheetService.CreateColumnWidth(9, 11, 15);

                // Add column headers
                _spreadsheetService.AddHeader(new List<string>() { "ID", "Col1", "Col2", "Col3", "Col4", "Col5", "col6", "Col7", "Col8", "Col9", "Col10", "Total"});

                // Get your data from the database here...
                List<Mydata> MyDataRows = _myRepo.GetAll();

                foreach (MyDataRow instance in MyDataRows)
                {
                   
                    _spreadsheetService.AddRow(new List<string>() { instance.ID, instance.Col1, instance.Col2, instance.Col3, instance.Col4, instance.Col5, instance.Col6, instance.Col7, instance.Col8, instance.Col9, instance.Col10, instance.Total});
                 
                }
                
                // Very important to close it!
                _spreadsheetService.CloseSpreadsheet();
            }
        
            return _spreadsheetService.SpreadsheetStream;
}

Finally in your controller:

public ActionResult GetListReport(// any parameters to report here)
        {
            if (ModelState.IsValid)
            {

             
                try
                {
                   
                    MemoryStream reportStream = _reportsManager.GenerateExcelList(// any parameters here);


                    return new OpenSpreadsheetResult(reportStream, "MyName.xlsx");
                }
                catch (Exception ex)
                {
                    ModelState.AddModelError("MyField", "Error creating report: " +ex.StackTrace);
                    return View("MyReportView", model);
                }


            }
            else
            {
                return View("MyReportView", model);
            }
        }


Advertisements

18 Responses to Creating and streaming an OpenXML Spreadsheet using the .Net SDK and ASP.NET MVC

  1. Thank you, this has gotten me a lot farther than other articles I’ve read!

    I do need to be able to access multiple sheets, and haven’t been able to get that working yet.

    I created a separate method for adding a new sheet, and was successfully able to create a document with three sheets.

    However, when I call AddRow, the row contents show up on all three sheets, whereas I obviously expected them to only appear on the first sheet.

    Do you have any code snippets or suggestions on manipulating a certain sheet in AddRow?

    Thanks!

    • Andrew Jerrison says:

      Glad you found it at least partly useful! I don’t currently have the time to look into multiple sheets I’m afraid, but I will try and come back to it in the future when I am less busy.

      To be going on with though: One part that is likely to need changing is to replace the ‘currentWorksheet’ property with a method that returns the worksheet that you want. I think that you will probably also need a new WorksheetPart for every worksheet (see the comment above the CreateSpreadsheet() method), and therefore a new SheetData object for each too. Also, check out the code around the call to GetIDOfPart() in CreateSpreadSheet(). I suspect that this will need doing for each new worksheet.

      I will no doubt be needing to do this myself in the future too, so I will update when I have got something useful working.

  2. Thanks, I figured it out. I wasn’t creating a new SheetData object for each sheet. The needed changes were as you described. I removed the logic creating the first sheet from CreateSpreadsheet, created a new AddSheet method which returns a WorksheetPart, and then added a WorksheetPart parameter to the CreateColumnWidth/AddRow/etc methods to use that instead of the currentWorkSheet property. So far so good!

    In case it helps you, here is my AddSheet method which deals with setting the Id and SheetId:

    public WorksheetPart AddSheet(string Name)
    {
    WorksheetPart wsp = spreadSheet.WorkbookPart.AddNewPart();
    wsp.Worksheet = new Worksheet();

    wsp.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

    wsp.Worksheet.Save();

    UInt32 sheetId;

    // If this is the first sheet, the ID will be 1. If this is not the first sheet, we calculate the ID based on the number of existing
    // sheets + 1.
    if (spreadSheet.WorkbookPart.Workbook.Sheets == null)
    {
    spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
    sheetId = 1;
    }
    else
    {
    sheetId = Convert.ToUInt32(spreadSheet.WorkbookPart.Workbook.Sheets.Count() + 1);
    }

    // Create the new sheet and add it to the workbookpart
    spreadSheet.WorkbookPart.Workbook.GetFirstChild().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
    {
    Id = spreadSheet.WorkbookPart.GetIdOfPart(wsp),
    SheetId = sheetId,
    Name = Name
    }
    );

    // Save our changes
    spreadSheet.WorkbookPart.Workbook.Save();

    return wsp;
    }

  3. Pingback: OpenXML spreadsheets with multiple worksheets using .Net SDK « Dot Scrap Book

  4. Hi, I have been given this code which uses the OfficeOpenXml EPPLUS.dll

    Should I use your approach to populate this file far below with results from a command:

    SqlDataAdapter da = new SqlDataAdapter(command);
    DataSet ds = new DataSet();
    da.Fill(ds);

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using OfficeOpenXml;

    namespace ExcelTest
    {
    class Program
    {
    static void Main(string[] args)
    {
    FileInfo newFile = new FileInfo(@”C:\Temp\A\Test.xlsx”);

    ExcelPackage package = new ExcelPackage(newFile);

    ExcelWorksheet ws = package.Workbook.Worksheets.Add(“Content”);
    ws.Cells[2, 3].Value = “Hello, world”;
    ws.View.ShowGridLines = false;

    package.Save();

    }
    }
    }

    • Andrew Jerrison says:

      Hi Nikos, I don’t really understand your question but it looks like this article is not quite what you are looking for. My article was about writing a simple class to use the OpenXML SDK with ASP.NET MVC. You appear to be developing a Console app using a recently developed 3rd party library called EPPlus that is possibly a wrapper for OpenXML.

  5. mldisibio says:

    I appreciate your article and effort. It comes up has Google’s first listing for “stream open xml to response”. Unfortunately, OpenXml, as you say, has very sketchy documentation, but as far as I can tell, we are not streaming the writing of Excel bytes directly to the Response.
    As far as I can tell from profiling and testing, OpenXml creates the Xml document structure in memory. Even the “Workbook.Save()” methods seem only to flush the DOM and release some xml element structures from memory, but doesn’t write to the underlying stream.
    Seems only ‘Package.Save()’ meaning the SpreadsheetDocument or whatever document type is being created, is what flushes the xml structure to the underlying stream. I believe this is because it creates a System.IO.Package, or zip file, with the DOM contents, so this can never be a forward only write operation.
    So, when you supply a MemoryStream as the underlying backing stream, the Excel doc will first all be composed in memory, then copied to a memory stream, and then written to the Response. Just be aware of this if you have a very large set of rows and restricted memory resources.
    My experience is that you can’t avoid two copies of the workbook in memory (DOM and zip stream) but at least you can avoid a third copy that sloppily copies the output stream. I don’t think your code does that, so it’s about as good as it gets…just pointing out that its not a direct write to the ResponseStream.
    Thanks again.

    • Andrew Jerrison says:

      Good point, Thanks for that. Yes, I had assumed that there would be a copy in memory, but hadn’t realised there might be another. I just wanted a method that would allow me to stream without first having to save it as a physical file to a server (which is not always possible for me). I perhaps should have made it clearer in the post, but as it was mainly just intended as notes for some colleagues I knew I could mention some things in person!

      I’m surprised to see it rank so highly! Wish I knew how I did it. 🙂

      I think the complicated format of an OpenSpreadsheet document would make it difficult to do anything other than first generate the entire doc in memory and then stream it.

      • mldisibio says:

        …I could not ‘edit’ my post, but that should read that “Package.Close” is what writes the DOM to the underlying stream (not just ‘Save()’)…thanks again.

  6. tweak to get faster file creation if you are aware of the number of columns … get rid of IncrementColRef

  7. I was rlooking at you app code and found out the the GenerateStyleSheet code can be found at: http://blogs.msdn.com/b/chrisquon/archive/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0.aspx. The code at that location is almost line for line the same. However, I need to figure out how to use the style components such as format, and border for a project that I am working on.

    • Andrew Jerrison says:

      Thanks, that might well have been where I got the code from although I don’t recall the page. Quite possibly someone else took it from this page and I saw what they had done!

  8. Pingback: Creating an Excel Spreadsheet in memory | Tim's Dev Blog

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: