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

Custom stored procedures returning DataSet instead of TList in NetTiers – and how to change!

When we upgraded our Nettiers installation on an old website recently some of our custom stored procedures suddenly started to return a DataSet rather than a TList. This is understandable – the stored procs were complex and so Nettiers wouldn’t have been able to guarantee the return type. Probably the earlier version of Nettiers was wrong to trust us in the first place. However *we* knew that it would always return the correct thing, and there were far too many places in the code to go back and recode it all to cope with a Dataset.

It seemed tricky to find the answer so I’ve reposted it here from the original source (http://community.codesmithtools.com/nettiers/f/16/p/10097/37547.aspx):

Set the Codesmith template setting AllowCustomProcMultipleResults (under the CRUD -Advanced section) to True and then regenerate. This seems to tell Nettiers to just trust that you know what you are doing, and sets the return type to that of the entity in the provider.