示例#1
0
        /// <summary>
        /// Write xlsx spreadsheet file of a list of T objects
        /// Maximum of 24 columns
        /// </summary>
        /// <typeparam name="T">Type of objects passed in</typeparam>
        /// <param name="fileName">Full path filename for the new spreadsheet</param>
        /// <param name="def">A sheet definition used to create the spreadsheet</param>
        public static void Create <T>(
            string fileName,
            SheetDefinition <T> def)
        {
            // open a template workbook
            using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                // create workbook part
                var workbookPart = myWorkbook.AddWorkbookPart();

                // add stylesheet to workbook part
                var        stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
                Stylesheet styles     = new CustomStylesheet();
                styles.Save(stylesPart);

                // create workbook
                var workbook = new Workbook();

                // add work sheet
                var sheets = new Sheets();
                sheets.AppendChild(CreateSheet(1, def, workbookPart));
                workbook.AppendChild(sheets);

                // add workbook to workbook part
                myWorkbook.WorkbookPart.Workbook = workbook;
                myWorkbook.WorkbookPart.Workbook.Save();
                myWorkbook.Close();
            }
        }
示例#2
0
        private static Sheet CreateSheet <T>(int sheetIndex, SheetDefinition <T> def, WorkbookPart workbookPart)
        {
            // create worksheet part
            var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
            var worksheetId   = workbookPart.GetIdOfPart(worksheetPart);

            // variables
            var numCols        = def.Fields.Count;
            var numRows        = def.Objects.Count;
            var az             = new List <Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray());
            var headerCols     = az.GetRange(0, numCols);
            var hasTitleRow    = def.Title != null;
            var hasSubtitleRow = def.SubTitle != null;
            var titleRowCount  = hasTitleRow ? 1 + (hasSubtitleRow ? 1 : 0) : hasSubtitleRow ? 1 : 0;

            // get the worksheet data
            int firstTableRow;
            var sheetData = CreateSheetData(def.Objects, def.Fields, headerCols, def.IncludeTotalsRow, def.Title,
                                            def.SubTitle,
                                            out firstTableRow);

            // populate column metadata
            var columns = new Columns();

            for (var col = 0; col < numCols; col++)
            {
                var width = ColumnWidth(sheetData, col, titleRowCount);
                columns.AppendChild(CreateColumnMetadata((UInt32)col + 1, (UInt32)numCols + 1, width));
            }

            // populate worksheet
            var worksheet = new Worksheet();

            worksheet.AppendChild(columns);
            worksheet.AppendChild(sheetData);

            // add an auto filter
            worksheet.AppendChild(new AutoFilter
            {
                Reference =
                    String.Format("{0}{1}:{2}{3}", headerCols.First(), firstTableRow - 1, headerCols.Last(),
                                  numRows + titleRowCount + 1)
            });

            // add worksheet to worksheet part
            worksheetPart.Worksheet = worksheet;
            worksheetPart.Worksheet.Save();

            return(new Sheet {
                Name = def.Name, SheetId = (UInt32)sheetIndex, Id = worksheetId
            });
        }