OpenXML spreadsheets with multiple worksheets using .Net SDK

A few months ago I posted an article about how to create and stream an OpenXML spreadsheet using the .Net SDK and ASP.NET MVC.

That example only created one worksheet in the spreadsheet though and a couple of people have asked how to create multiple worksheets. In my last project I also needed to do this, so here is what I did. Hopefully it will be useful to someone!

The ActionResult and Controller code remain as before.

This is the new SpreadsheetService class:


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 = "Wibble Wobble";
                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();

              
                
               
                ws.Append(sd); // Add sheet data to worksheet
               
               
                
               
                spreadSheet.WorkbookPart.Workbook = wb;
                spreadSheet.WorkbookPart.Workbook.Save();
               
            }
            catch
            {
                return false;
            }

            return true;
        }


        public string AddSheet(string Name)
        {
            WorksheetPart wsp = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
            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<Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
            {
                Id = spreadSheet.WorkbookPart.GetIdOfPart(wsp),
                SheetId = sheetId,
                Name = Name
            }
            );

            _cols = new Columns(); // Created to allow bespoke width columns
            // Save our changes
            spreadSheet.WorkbookPart.Workbook.Save();

            return spreadSheet.WorkbookPart.GetIdOfPart(wsp);// wsp;
        }

        private Worksheet _getWorkSheet(string sheetId)
        {
            WorksheetPart wsp = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheetId);
            return wsp.Worksheet;
        }

        /// <summary>
        /// add the bespoke columns for the list spreadsheet
        /// </summary>
        public void CreateColumnWidth(string sheetId, uint startIndex, uint endIndex, double width)
        {
            // Find the columns in the worksheet and remove them all
           
            if (_getWorkSheet(sheetId).Where(x => x.LocalName == "cols").Count() > 0)
                _getWorkSheet(sheetId).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
           
            _getWorkSheet(sheetId).InsertBefore<Columns>(_cols, _getWorkSheet(sheetId).Where(x => x.LocalName == "sheetData").First());
            _getWorkSheet(sheetId).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(string sheetId, List<string> headers)
        {
            // Find the sheetdata of the worksheet
           
            SheetData sd = (SheetData)_getWorkSheet(sheetId).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
            
            _getWorkSheet(sheetId).Save();
           
        }


        /// <summary>
        /// Pass a list of data items to create a data row
        /// </summary>
        /// <param name="dataItems"></param>
        public void AddRow(string sheetId, List<string> dataItems)
        {
            // Find the sheetdata of the worksheet
            
            SheetData sd = (SheetData)_getWorkSheet(sheetId).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
           
            _getWorkSheet(sheetId).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
        }

    }
}


And this is an example of how to call it:

 if (_spreadsheetService.CreateSpreadsheet() == true)
            {
                string oneSheet = _spreadsheetService.AddSheet("Sheet1");
                
                _spreadsheetService.CreateColumnWidth(oneSheet, 1, 1, 15);
                
                _spreadsheetService.CreateColumnWidth(oneSheet, 2, 2, 50);
               
                _spreadsheetService.CreateColumnWidth(oneSheet, 3, 3, 20);
                
                _spreadsheetService.CreateColumnWidth(oneSheet, 4, 6, 20);

  
                _spreadsheetService.AddHeader(oneSheet, new List<string>() { "Header1", "Header2", "Header3", "Header4", "Header5", "Header6"});
                _spreadsheetService.AddRow(oneSheet, new List<string>() { 
                            "Data1", "Data2", "Data3", "Data4", "Data5", "Data6"});

                string twoSheet = _spreadsheetService.AddSheet("Sheet2");
                
                _spreadsheetService.CreateColumnWidth(twoSheet, 1, 1, 15);
                
                _spreadsheetService.CreateColumnWidth(twoSheet, 2, 2, 50);
               
                _spreadsheetService.CreateColumnWidth(twoSheet, 3, 3, 20);
                
                _spreadsheetService.CreateColumnWidth(twoSheet, 4, 6, 20);

  
                _spreadsheetService.AddHeader(twoSheet, new List<string>() { "Header1", "Header2", "Header3", "Header4", "Header5", "Header6"});
                _spreadsheetService.AddRow(twoSheet, new List<string>() { 
                            "Data1", "Data2", "Data3", "Data4", "Data5", "Data6"});


                _spreadsheetService.CloseSpreadsheet();
            }


Advertisements

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);
            }
        }