Click here to Skip to main content
15,868,016 members
Articles / Web Development / ASP.NET

Using ASP.NET MVC and the OpenXML API to Stream Excel Files

Rate me:
Please Sign up or sign in to vote.
4.76/5 (12 votes)
27 Sep 2009CPOL5 min read 117.8K   4.8K   35   16
Using ASP.NET MVC and the OpenXML API to stream Excel files

Introduction

I've been heads down for the last several weeks scrumming, and it has been a while since I've updated my blog, so I figured if I was going to keep your interest, I should give you something really useful. We've been transitioning from web forms to ASP.NET MVC, and we had a requirement to export the contents of a grid to an Excel spreadsheet from one of our ASP.NET MVC pages and prompt the user to open or save.

After some digging, I wasn't happy with the idea of porting the legacy solution over, so I did a little digging. Opening an Excel spreadsheet is not anything new, but I had a few requirements of my own:

  • I wanted to call the function from my ASP.NET MVC controller.
  • I wanted to send in a collection of data from my model.
  • I wanted to minimize hard disk I/O.
  • I wanted to add custom headers.
  • I wanted to give my Excel file a meaningful name.
  • I wanted to give my spreadsheet a meaningful name, say matching the page the user was exporting from to the sheet name.
  • I wanted to be able to extend the class to add custom styles.
  • I wanted to format the columns for a specific type of data
  • I wanted Excel to recognize the file right away. We can't have any “unrecognized file type” nag screens confusing our customers.
  • Finally, I wanted to use this with web forms.

Background

It seemed pretty obvious that I could minimize I/O by saving the file to a memory stream, but what about the rest of my requirements?

I'll take you through the steps and provide source code in the following sections.

Step 1: Extending the controllers. Steve Sanderson’s blog has an excellent article on extending the controllers and returning an Excel file as an action result. Phil Haack also has an excellent post on the subject, which you can find here.

Steve’s example uses a DataContext and XML to stream the file; however, Excel doesn't recognize the file and prompts you with a nag screen. Excel will eventually open the file and it looks perfect, but I really wanted to eliminate this prompt. I also wanted to be able to pass in my own data from the model and localize the headers and the data. I borrowed extensively from Steve’s tutorial with a little modification. So, how do I get Excel to play nice?

I had to learn a little bit about Excel compatibility to get things to work, so I started with the Office Developer’s kit going back to Excel 97. If you have a few hours or an open weekend, I would suggest you read up on BIFF8. While BIFF8 is well documented in the Office Developer’s SDK, and I did eventually get the file to stream in BIFF8, I found it almost incomprehensible when it comes to adding additional features, and frankly, I didn't want to be the keeper of knowledge Excel spreadsheets within my group, which is ultimately what would have happened. This solution needs to scale out because I work with a lot of other developers and they will have more complex requirements as the project grows. I came across the OpenXML API from Microsoft, the OOXML extensions from James Westgate, and I was off and running.

  • More information on OpenXml and file formats are available here.
  • Get the Open XML SDK here.
  • Get the OOXML API here.

So to recap, I'm going to extend my ASP.NET MVC controllers so all of my controllers will have access to the functionality. I will send in data from my model. Next, I will create a custom spreadsheet and workbook with styled headers. And finally, I will assign a meaningful name to the Excel file and sheet.

Using the Code

Extending the Controller

If you have read Steve or Phil’s blog, I encourage you to read these blogs first. This will look like pretty standard stuff, no magic going on here.

C#
namespace YourNamespaceGoesHere.Controllers.Extensions 
{ 
    using System.Linq; 
    using System.Web.Mvc; 
    using YourNamespaceGoesHere.Controllers.Results; 

    /// <summary> 
    /// Excel controller extensions class. 
    /// </summary> 
    public static class ExcelControllerExtensions 
    { 
        /// <summary> 
        /// Controller Extension: Returns an Excel
        /// result constructor for returning values from rows. 
        /// </summary> 
        /// <param name="controller">This controller.</param> 
        /// <param name="fileName">Excel file name.</param> 
        /// <param name="excelWorkSheetName">Excel worksheet name: 
        /// default: sheet1.</param> 
        /// <param name="rows">Excel row values.</param> 
        /// <returns>Action result.</returns> 
        public static ActionResult Excel(this Controller controller, 
               string fileName, string excelWorkSheetName, IQueryable rows) 
        { 
            return new ExcelResult(fileName, excelWorkSheetName, rows); 
        } 

        /// <summary> 
        /// Controller Extension: Excel result constructor
        /// for returning values from rows and headers. 
        /// </summary> 
        /// <param name="controller">This controller.</param> 
        /// <param name="fileName">Excel file name.</param> 
        /// <param name="excelWorkSheetName">
        /// Excel worksheet name: default: sheet1.</param> 
        /// <param name="rows">Excel row values.</param> 
        /// <param name="headers">Excel header values.</param> 
        /// <returns>Action result.</returns> 
        public static ActionResult Excel(this Controller controller, 
               string fileName, string excelWorkSheetName, 
               IQueryable rows, string[] headers) 
        { 
            return new ExcelResult(fileName, excelWorkSheetName, rows, headers); 
        } 

        /// <summary> 
        /// Controller Extension: Excel result constructor for returning
        /// values from rows and headers and row keys. 
        /// </summary> 
        /// <param name="controller">This controller.</param> 
        /// <param name="fileName">Excel file name.</param> 
        /// <param name="excelWorkSheetName">
        /// Excel worksheet name: default: sheet1.</param> 
        /// <param name="rows">Excel row values.</param> 
        /// <param name="headers">Excel header values.</param> 
        /// <param name="rowKeys">Key values for the rows collection.</param> 
        /// <returns>Action result.</returns> 
        public static ActionResult Excel(this Controller controller, string fileName, 
               string excelWorkSheetName, IQueryable rows, 
		string[] headers, string[] rowKeys) 
        { 
            return new ExcelResult(fileName, excelWorkSheetName, rows, headers, rowKeys); 
        }
    } 
}

I have three overloads here:

  • Overload 1 takes the Excel file name and an IQueryable collection of rows.
  • Overload 2 takes the Excel file name, a worksheet name, and an IQueryable collection of rows and an array of headers.

    This method allows for passing in headers separately, but they must match the keys for the rows.

  • Overload 3 takes the Excel file name, a worksheet name, an IQueryable collection of rows, and an array of headers and a collection of row keys.

    This method allows you to have different header names for your rows.

Extending the Action Result

This class stores the values passed in when constructed. If you examine this class, you'll see that it overrides the ExcecuteResult function and adds the functionality to create the Excel file and save it to a memory stream. This is a pretty clever technique, and I have deliberately separated the functionality of streaming the file from the functionality of creating the file, so my Excel class can be reused in non-MVC applications.

C#
namespace YourNamespaceGoesHere.Controllers.Results 
{
    using System; 
    using System.IO; 
    using System.Linq; 
    using System.Web; 
    using System.Web.Mvc; 
    using YourNamespaceGoesHere.Controllers.ControllerExtensions; 

    /// <summary> 
    /// Excel result class 
    /// </summary> 
    public class ExcelResult : ActionResult 
    {
        /// <summary> 
        /// File Name. 
        /// </summary> 
        private string excelFileName; 
        /// <summary> 
        /// Sheet Name. 
        /// </summary> 
        private string excelWorkSheetName; 
        /// <summary> 
        /// Excel Row data. 
        /// </summary> 
        private IQueryable rowData; 
        /// <summary> 
        /// Excel Header Data. 
        /// </summary> 
        private string[] headerData = null; 
        /// <summary> 
        /// Row Data Keys. 
        /// </summary> 
        private string[] rowPointers = null; 
        /// <summary> 
        /// Action Result: Excel result constructor for returning values from rows. 
        /// </summary> 
        /// <param name="fileName">Excel file name.</param> 
        /// <param name="workSheetName">Excel worksheet name: default: sheet1.</param> 
        /// <param name="rows">Excel row values.</param> 
        public ExcelResult(string fileName, string workSheetName, IQueryable rows) 
        : this(fileName, workSheetName, rows, null, null) 
        { 
        }

        /// <summary> 
        namespace YourNamespaceGoesHere.Controllers.Results 
        { 
        using System; 
        using System.IO; 
        using System.Linq; 
        using System.Web; 
        using System.Web.Mvc; 
        using YourNamespaceGoesHere.Controllers.ControllerExtensions; 
        /// <summary> 
        /// Excel result class 
        /// </summary> 
        public class ExcelResult : ActionResult 
        { 
        /// <summary> 
        /// File Name. 
        /// </summary> 
        private string excelFileName; 
        /// <summary> 
        /// Sheet Name. 
        /// </summary> 
        private string excelWorkSheetName; 
        /// <summary> 
        /// Excel Row data. 
        /// </summary> 
        private IQueryable rowData; 
        /// <summary> 
        /// Excel Header Data. 
        /// </summary> 
        private string[] headerData = null; 
        /// <summary> 
        /// Row Data Keys. 
        /// </summary> 
        private string[] rowPointers = null; 
        /// <summary> 
        /// Action Result: Excel result constructor for returning values from rows. 
        /// </summary> 
        /// <param name="fileName">Excel file name.</param> 
        /// <param name="workSheetName">Excel worksheet name: default: sheet1.</param> 
        /// <param name="rows">Excel row values.</param> 
        public ExcelResult(string fileName, string workSheetName, IQueryable rows) 
        : this(fileName, workSheetName, rows, null, null) 
        { 
        } 

        /// <summary> 
        /// Action Result: Excel result constructor
        /// for returning values from rows and headers. 
        /// </summary> 
        /// <param name="fileName">Excel file name.</param> 
        /// <param name="workSheetName">Excel worksheet name: default: sheet1.</param> 
        /// <param name="rows">Excel row values.</param> 
        /// <param name="headers">Excel header values.</param> 
        public ExcelResult(string fileName, string workSheetName, 
               IQueryable rows, string[] headers) 
               : this(fileName, workSheetName, rows, headers, null) 
        { 
        } 
        /// <summary> 
        /// Action Result: Excel result constructor for returning
        /// values from rows and headers and row keys. 
        /// </summary> 
        /// <param name="fileName">Excel file name.</param> 
        /// <param name="workSheetName">Excel worksheet name: default: sheet1.</param> 
        /// <param name="rows">Excel row values.</param> 
        /// <param name="headers">Excel header values.</param> 
        /// <param name="rowKeys">Key values for the rows collection.</param> 
        public ExcelResult(string fileName, string workSheetName, 
               IQueryable rows, string[] headers, string[] rowKeys) 
        { 
            this.rowData = rows; 
            this.excelFileName = fileName; 
            this.excelWorkSheetName = workSheetName; 
            this.headerData = headers; 
            this.rowPointers = rowKeys; 
        }

        /// <summary> 
        /// Gets a value for file name. 
        /// </summary> 
        public string ExcelFileName 
        { 
            get { return this.excelFileName; } 
        } 
        /// <summary> 
        /// Gets a value for file name. 
        /// </summary> 
        public string ExcelWorkSheetName 
        { 
            get { return this.excelWorkSheetName; } 
        } 
        /// <summary> 
        /// Gets a value for rows. 
        /// </summary> 
        public IQueryable ExcelRowData 
        { 
            get { return this.rowData; } 
        } 
        /// <summary> 
        /// Execute the Excel Result. 
        /// </summary> 
        /// <param name="context">Controller context.</param> 
        public override void ExecuteResult(ControllerContext context) 
        { 
            MemoryStream stream = ExcelDocument.Create(this.excelFileName, 
                                  this.excelWorkSheetName, this.rowData, 
                                  this.headerData, this.rowPointers); 
            WriteStream(stream, this.excelFileName); 
        } 
        /// <summary> 
        /// Writes the memory stream to the browser. 
        /// </summary> 
        /// <param name="memoryStream">Memory stream.</param> 
        /// <param name="excelFileName">Excel file name.</param> 
        private static void WriteStream(MemoryStream memoryStream, string excelFileName) 
        { 
            HttpContext context = HttpContext.Current; 
            context.Response.Clear(); 
            context.Response.AddHeader("content-disposition", 
              String.Format("attachment;filename={0}", excelFileName)); 
            memoryStream.WriteTo(context.Response.OutputStream); 
            memoryStream.Close(); 
            context.Response.End(); 
        } 
    } 
}

Finally, creating the Excel file. Just a note about static methods. I wanted to be sure that my Excel document was thread-safe, so I created static methods at the expense of making this easily mock-able. You'll need to weigh that decision on your own.

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

C#
namespace YourNamespaceGoesHere.Controllers.ControllerExtensions 
{ 
    using System; 
    using System.IO; 
    using System.Linq; 
    using DocumentFormat.OpenXml; 
    using DocumentFormat.OpenXml.Extensions; 
    using DocumentFormat.OpenXml.Packaging; 
    using DocumentFormat.OpenXml.Spreadsheet; 

    /// <summary> 
    /// Excel document. 
    /// </summary> 
    public static class ExcelDocument 
    { 
        /// <summary> 
        /// Default spread sheet name. 
        /// </summary> 
        private const string DefaultSheetName = "Sheet1"; 

        /// <summary> 
        /// Create the exel document for streaming. 
        /// </summary> 
        /// <param name="documentName">Excel file name.</param> 
        /// <param name="excelWorkSheetName">
        /// Excel worksheet name: default: sheet1.</param> 
        /// <param name="rowData">Row data to write.</param> 
        /// <param name="headerData">Header data.</param> 
        /// <param name="rowPointers">Row pointers.</param> 
        /// <returns>Memory stream.</returns> 
        public static MemoryStream Create(string documentName, 
               string excelWorkSheetName, IQueryable rowData, 
               string[] headerData, string[] rowPointers) 
        { 
            return CreateSpreadSheet(documentName, excelWorkSheetName, 
                   rowData, headerData, rowPointers, null); 
        } 

        /// <summary> 
        /// Create the spreadsheet. 
        /// </summary> 
        /// <param name="documentName">Excel file name.</param> 
        /// <param name="excelWorkSheetName">
        /// Excel worksheet name: default: sheet1.</param> 
        /// <param name="rowData">Row data to write.</param> 
        /// <param name="headerData">Header data.</param> 
        /// <param name="rowPointers">Row pointers.</param> 
        /// <param name="styleSheet">Style sheet.</param> 
        /// <returns>Memory stream.</returns> 
        private static MemoryStream CreateSpreadSheet(string documentName, 
                string excelWorkSheetName, IQueryable rowData, 
                string[] headerData, string[] rowPointers, Stylesheet styleSheet) 
        { 
            int rowNum = 0; 
            int colNum = 0; 
            int maxWidth = 0; 
            int minCol = 1; 
            int maxCol = rowPointers == null ? minCol : rowPointers.Length; 
            maxCol = maxCol == 1 && headerData == null ? 1 : headerData.Length; 
            MemoryStream xmlStream = SpreadsheetReader.Create(); 
            SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xmlStream, true); 
            SetSheetName(excelWorkSheetName, spreadSheet); 
            if (styleSheet == null) 
            { 
                SetStyleSheet(spreadSheet); 
            } 
            else 
            { 
                spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet = styleSheet; 
                spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save(); 
            }     
            WorksheetPart worksheetPart = 
              SpreadsheetReader.GetWorksheetPartByName(spreadSheet, 
                                excelWorkSheetName); 
            WriteHeaders(headerData, out rowNum, out colNum, 
                         out maxWidth, spreadSheet, worksheetPart); 
            AddCellWidthStyles(Convert.ToUInt32(minCol), 
                Convert.ToUInt32(maxCol), maxWidth, spreadSheet, worksheetPart); 
            if (rowPointers == null || rowPointers.Length == 0) 
            { 
                WriteRowsFromHeaders(rowData, headerData, rowNum, 
                                     out maxWidth, spreadSheet, worksheetPart); 
            } 
            else 
            { 
                WriteRowsFromKeys(rowData, rowPointers, rowNum, 
                         out maxWidth, spreadSheet, worksheetPart); 
            } 

            // Save to the memory stream 
            SpreadsheetWriter.Save(spreadSheet); 
            spreadSheet.Close(); 
            spreadSheet.Dispose(); 
            return xmlStream; 
        } 

        /// <summary> 
        /// Set the name of the spreadsheet. 
        /// </summary> 
        /// <param name="excelSpreadSheetName">Spread sheet name.</param> 
        /// <param name="spreadSheet">Spread sheet.</param> 
        private static void SetSheetName(string excelSpreadSheetName, 
                            SpreadsheetDocument spreadSheet) 
        { 
            excelSpreadSheetName = excelSpreadSheetName ?? DefaultSheetName; 
            Sheet ss = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(
                         s => s.Name == DefaultSheetName).SingleOrDefault<Sheet>(); 
            ss.Name = excelSpreadSheetName; 
        }

        /// <summary> 
        /// Add cell width styles. 
        /// </summary> 
        /// <param name="minCol">Minimum column index.</param> 
        /// <param name="maxCol">Maximum column index.</param> 
        /// <param name="maxWidth">Maximum column width.</param> 
        /// <param name="spreadSheet">Spread sheet.</param> 
        /// <param name="workSheetPart">Work sheet.</param> 
        private static void AddCellWidthStyles(uint minCol, uint maxCol, 
                int maxWidth, SpreadsheetDocument spreadSheet, 
		WorksheetPart workSheetPart) 
        { 
            Columns cols = new Columns(new Column() { CustomWidth = true, 
               Min = minCol, Max = maxCol, Width = maxWidth, BestFit = false }); 
            workSheetPart.Worksheet.InsertBefore<Columns>(cols, 
               workSheetPart.Worksheet.GetFirstChild<SheetData>()); 
        } 

        /// <summary> 
        /// Set the style sheet. 
        // Note: Setting the style here rather than passing it in ensures
        // that all worksheets will have a common user interface design. 
        /// </summary> 
        /// <param name="spreadSheet">Spread sheet to change.</param> 
        private static void SetStyleSheet(SpreadsheetDocument spreadSheet) 
        { 
            // Note: Setting the style here rather than passing it in
            // ensures that all worksheets will have a common user interface design. 
            Stylesheet styleSheet = 
		spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet; 
            styleSheet.Fonts.AppendChild( 
              new Font(new FontSize() { Val = 11 }, 
              new Color() { Rgb = "FFFFFF" }, new FontName() { Val = "Arial" })); 
            styleSheet.Fills.AppendChild(new Fill() 
            { 
                PatternFill = new PatternFill() 
                { 
                    PatternType = PatternValues.Solid, 
                    BackgroundColor = new BackgroundColor() { Rgb = "D8D8D8" } 
                } 
            }); 
            spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save(); 
        }

        /// <summary> 
        /// Save the styl for worksheet headers. 
        /// </summary> 
        /// <param name="cellLocation">Cell location.</param> 
        /// <param name="spreadSheet">Spreadsheet to change.</param> 
        /// <param name="workSheetPart">Worksheet to change.</param> 
        private static void SeatHeaderStyle(string cellLocation, 
                SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart) 
        { 
            Stylesheet styleSheet = 
              spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet; 
            Cell cell = workSheetPart.Worksheet.Descendants<Cell>().Where(
                            c => c.CellReference == cellLocation).FirstOrDefault(); 
            if (cell == null) 
            { 
                throw new ArgumentNullException("Cell not found"); 
            } 

            cell.SetAttribute(new OpenXmlAttribute("", "s", "", "1")); 
            OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s", ""); 
            CellFormats cellFormats = 
              spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats; 
            // pick the first cell format. 
            CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(0); 
            CellFormat cf = new CellFormat(cellFormat.OuterXml); 
            cf.FontId = styleSheet.Fonts.Count; 
            cf.FillId = styleSheet.Fills.Count; 
            cellFormats.AppendChild(cf); 
            int a = (int)styleSheet.CellFormats.Count.Value; 
            cell.SetAttribute(cellStyleAttribute); 
            cell.StyleIndex = styleSheet.CellFormats.Count; 
            workSheetPart.Worksheet.Save(); 
        } 

        /// <summary> 
        /// Replace special characters. 
        /// </summary> 
        /// <param name="value">Value to input.</param> 
        /// <returns>Value with special characters replaced.</returns> 
        private static string ReplaceSpecialCharacters(string value) 
        { 
            value = value.Replace("’", "'"); 
            value = value.Replace(""", "\""); 
            value = value.Replace(""", "\""); 
            value = value.Replace("–", "-"); 
            value = value.Replace("…", "..."); 
            return value; 
        } 

        /// <summary> 
        /// Write values to the spreadsheet. 
        /// </summary> 
        /// <param name="cellLocation">Row Column Value.</param> 
        /// <param name="strValue">Value to write.</param> 
        /// <param name="spreadSheet">Spreadsheet to write to. </param> 
        /// <param name="workSheet">Worksheet to write to. </param> 
        private static void WriteValues(string cellLocation, 
                string strValue, SpreadsheetDocument spreadSheet, 
                WorksheetPart workSheet) 
        { 
            WorksheetWriter workSheetWriter = 
                    new WorksheetWriter(spreadSheet, workSheet); 
            int intValue = 0; 
            if (strValue.Contains("$")) 
            { 
            strValue = strValue.Replace("$", ""); 
            strValue = strValue.Replace(",", ""); 
            workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number); 
        } 
        else if (int.TryParse(strValue, out intValue)) 
        { 
            workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number); 
        } 
        else if (string.IsNullOrEmpty(strValue)) 
        { 
            workSheetWriter.PasteText(cellLocation, strValue); 
        } 
        else 
        { 
            workSheetWriter.PasteText(cellLocation, strValue); 
        } 
    } 

    /// <summary> 
    /// Write the excel rows for the spreadsheet. 
    /// </summary> 
    /// <param name="rowData">Excel row values.</param> 
    /// <param name="rowDataKeys">Excel row-key values.</param> 
    /// <param name="rowNum">Row number.</param> 
    /// <param name="maxWidth">Max width.</param> 
    /// <param name="spreadSheet">Spreadsheet to write to. </param> 
    /// <param name="workSheet">Worksheet to write to. </param> 
    private static void WriteRowsFromKeys(IQueryable rowData, 
            string[] rowDataKeys, int rowNum, out int maxWidth, 
            SpreadsheetDocument spreadSheet, WorksheetPart workSheet) 
    { 
        maxWidth = 0; 
        foreach (object row in rowData) 
        { 
            int colNum = 0; 
            foreach (string rowKey in rowDataKeys) 
            { 
                string strValue = 
                  row.GetType().GetProperty(rowKey).GetValue(row, null).ToString(); 
                strValue = ReplaceSpecialCharacters(strValue); 
                maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth; 
                string cellLocation = string.Format("{0}{1}", 
                       GetColumnLetter(colNum.ToString()), rowNum); 
                ExcelDocument.WriteValues(cellLocation, strValue, 
                                          spreadSheet, workSheet); 
                colNum++; 
            } 
            rowNum++; 
        } 
    } 

    /// <summary> 
    /// Convert column number to alpha numeric value. 
    /// </summary> 
    /// <param name="colNumber">Column number.</param> 
    /// <returns>ASCII value for number.</returns> 
    private static string GetColumnLetter(string colNumber) 
    { 
        if (string.IsNullOrEmpty(colNumber)) 
        { 
            throw new ArgumentNullException(colNumber); 
        } 
        string colName = null; 
        try 
        { 
            for (int i = 0; i < colNumber.Length; i++) 
            { 
                string colValue = colNumber.Substring(i, 1); 
                int asc = Convert.ToInt16(colValue) + 65; 
                colName += Convert.ToChar(asc); 
            } 
        } 
        finally 
        { 
            colName = colName ?? "A"; 
        } 
        return colName; 
    } 

    /// <summary> 
    /// Write the values for the rows from headers. 
    /// </summary> 
    /// <param name="rowData">Excel row values.</param> 
    /// <param name="headerData">Excel header values.</param> 
    /// <param name="rowNum">Row number.</param> 
    /// <param name="maxWidth">Max width.</param> 
    /// <param name="spreadSheet">Spreadsheet to write to. </param> 
    /// <param name="workSheet">Worksheet to write to. </param> 
    private static void WriteRowsFromHeaders(IQueryable rowData, 
            string[] headerData, int rowNum, out int maxWidth, 
            SpreadsheetDocument spreadSheet, WorksheetPart workSheet) 
    { 
        WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet); 
        maxWidth = 0; 
        foreach (object row in rowData) 
        { 
            int colNum = 0; 
            foreach (string header in headerData) 
            { 
                string strValue = 
                  row.GetType().GetProperty(header).GetValue(row, null).ToString(); 
                strValue = ReplaceSpecialCharacters(strValue); 
                maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth; 
                string cellLocation = string.Format("{0}{1}", 
                       GetColumnLetter(colNum.ToString()), rowNum); 
                ExcelDocument.WriteValues
			(cellLocation, strValue, spreadSheet, workSheet); 
                colNum++; 
            } 
            rowNum++; 
        } 
    } 

    /// <summary> 
    /// Write the excel headers for the spreadsheet. 
    /// </summary> 
    /// <param name="headerData">Excel header values.</param> 
    /// <param name="rowNum">Row number.</param> 
    /// <param name="colNum">Column Number.</param> 
    /// <param name="maxWidth">Max column width</param> 
    /// <param name="spreadSheet">Maximum Column Width to write to. </param> 
    /// <param name="workSheet">Worksheet to write to. </param> 
    private static void WriteHeaders(string[] headerData, out int rowNum, 
            out int colNum, out int maxWidth, 
            SpreadsheetDocument spreadSheet, WorksheetPart workSheet) 
    { 
        rowNum = 1; 
        colNum = 0; 
        maxWidth = 0; 
        foreach (string header in headerData) 
        { 
            string strValue = ReplaceSpecialCharacters(header); 
            string cellLocation = string.Format("{0}{1}", 
                   GetColumnLetter(colNum.ToString()), rowNum); 
            maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth; 
            ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet); 
            SeatHeaderStyle(cellLocation, spreadSheet, workSheet); 
            colNum++; 
        } 
        rowNum++; 
    } 
}

Points of Interest

I hope you found this useful. I'm not going to go into great detail here about the OpenXml API. This is all working code, so I would actually recommend you step through the code yourself and get acquainted with Microsoft’s OpenXml developer website.

Read more at my blog here.

History

  • 24th September, 2009: Initial post
  • 25th September, 2009: Added source files

License

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


Written By
Software Developer (Senior) Microsoft
United States United States
Chris Aloia lives in the Seattle area and works as a Senior Software Development Engineer for Microsoft Corporation.

Comments and Discussions

 
QuestionTaking more time to export while having more than 5000 records(almost 7min) Pin
Member 1006189529-Jun-17 3:47
Member 1006189529-Jun-17 3:47 
GeneralMy vote of 5 Pin
peter gabris31-Oct-15 15:42
peter gabris31-Oct-15 15:42 
GeneralNull row values should be set to the empty string Pin
Marozick24-Mar-10 5:33
Marozick24-Mar-10 5:33 
Questiononly 10 columns?? Pin
jeremyzb1-Feb-10 21:10
jeremyzb1-Feb-10 21:10 
AnswerRe: only 10 columns?? Pin
palandor4ya15-Mar-10 9:55
palandor4ya15-Mar-10 9:55 
GeneralRe: only 10 columns?? Pin
scottmh17-Aug-10 17:23
scottmh17-Aug-10 17:23 
AnswerRe: only 10 columns?? Pin
Matthew Cuba22-Jul-11 4:33
Matthew Cuba22-Jul-11 4:33 
GeneralRe: only 10 columns?? Pin
0x29A1-Aug-12 5:27
0x29A1-Aug-12 5:27 
AnswerRe: only 10 columns?? Pin
Batailleye3-May-13 13:33
Batailleye3-May-13 13:33 
SuggestionRe: only 10 columns?? Pin
peter gabris26-Oct-15 17:34
peter gabris26-Oct-15 17:34 
another take on column name, goes from 0 way up...

private static string GetColumnLetter(int colNumber)
{
    if (colNumber < 0 || colNumber >= 26*27)
    {
        throw new ArgumentNullException(colNumber.ToString());
    }

    if(colNumber > 25)
    {
        int firstChar = (colNumber / 26) - 1;
        int secondChar = colNumber % 26;
        return $"{Convert.ToChar(firstChar + 65)}{Convert.ToChar(secondChar + 65)}";
    }
    else
    {
        return Convert.ToChar(colNumber + 65).ToString();
    }
}

GeneralI have sent the source code to the code project for posting - please check back for updates Pin
caloia25-Sep-09 17:34
caloia25-Sep-09 17:34 
GeneralView Engine Pin
paul.vencill25-Sep-09 4:45
paul.vencill25-Sep-09 4:45 
GeneralMy vote of 1 Pin
dan!sh 25-Sep-09 2:18
professional dan!sh 25-Sep-09 2:18 
GeneralRe: My vote of 1 Pin
Juergen Gutsch25-Sep-09 4:38
Juergen Gutsch25-Sep-09 4:38 
GeneralRe: My vote of 1 Pin
dan!sh 25-Sep-09 9:35
professional dan!sh 25-Sep-09 9:35 
GeneralRe: My vote of 1 Pin
caloia25-Sep-09 17:34
caloia25-Sep-09 17:34 

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

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