Example #1
0
        /// <summary>
        ///  Create a number format in the stylesheet and apply to the cellFormat
        /// </summary>
        /// <param name="cellInfo"></param>
        /// <param name="formatCode"></param>
        /// <param name="stylesheet"></param>
        /// <param name="cellFormat"></param>
        private static void UpdateNumberFormat(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, string formatCode, ref Stylesheet stylesheet, ref CellFormat cellFormat)
        {
            if (cellFormat == null)
            {
                throw new ArgumentNullException("cellFormat");
            }

            string numFormat = cellInfo.NumberFormat ?? formatCode;

            // Anything to apply?
            if (string.IsNullOrEmpty(numFormat))
            {
                return;
            }

            // Look up existing...
            var         item = stylesManager.numberFormats.Find(numFormat);
            UInt32Value id   = item.Value;

            if (id == null)
            {
                // Create (and append?) a new numbering format in the stylesheet
                id = stylesheet.AddNumberingFormat(cellInfo.NumberFormat ?? formatCode);
                stylesManager.numberFormats.Add(numFormat, id);
            }

            cellFormat.NumberFormatId = id;
        }
Example #2
0
        /// <summary>
        /// Updates the supplied <see cref="CellFormat"/> with a colour index which relates to the Fill Colour.
        /// The colour is created in the excel <see cref="Stylesheet"/> if it is not already there.
        /// </summary>
        /// <param name="stylesheet"></param>
        /// <param name="cellInfo"></param>
        /// <returns></returns>
        private static void UpdateBorder(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, ref Stylesheet stylesheet, ref CellFormat cellFormat)
        {
            if (cellFormat == null)
            {
                throw new ArgumentNullException("cellFormat");
            }

            if (cellInfo.BorderInfo != null && cellInfo.BorderInfo.HasBorder)
            {
                var         item = stylesManager.borders.Find(cellInfo.BorderInfo);
                UInt32Value id   = item.Value;

                if (id == null)
                {
                    // Add and return the index of a fill colour
                    id = CreateNewBorder(stylesheet, cellInfo.BorderInfo);
                    stylesManager.borders.Add(cellInfo.BorderInfo, id);
                }
                cellFormat.BorderId = id;
            }
        }
Example #3
0
        /// <summary>
        /// Updates the supplied <see cref="CellFormat"/> with a colour index which relates to the Fill Colour.
        /// The colour is created in the excel <see cref="Stylesheet"/> if it is not already there.
        /// </summary>
        /// <param name="stylesheet"></param>
        /// <param name="cellInfo"></param>
        /// <returns></returns>
        private static void UpdateFillColour(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, ref Stylesheet stylesheet, ref CellFormat cellFormat)
        {
            if (cellFormat == null)
            {
                throw new ArgumentNullException("cellFormat");
            }

            if (cellInfo.FillColour != null && cellInfo.FillColour.HasValue && cellInfo.FillColour.Value != System.Windows.Media.Colors.Transparent)
            {
                var         fillItem = stylesManager.fills.Find(cellInfo.FillColour.Value);
                UInt32Value fillId   = fillItem.Value;

                if (fillId == null)
                {
                    // Add and return the index of a fill colour (Badly named as simply returns index of existing colour if it already exists)
                    fillId = stylesheet.AddIndexedColor(cellInfo.FillColour.Value);
                    stylesManager.fills.Add(cellInfo.FillColour.Value, fillId);
                }
                cellFormat.FillId = fillId;
            }
        }
Example #4
0
        /// <summary>
        /// Creates a new style in the excel <see cref="Stylesheet"/> based on the supplied <see cref="ExportStyle"/>.<br/>
        /// Return the index of the newly created style.
        /// </summary>
        /// <param name="stylesheet"></param>
        /// <param name="cellInfo"></param>
        /// <param name="formatCode"></param>
        /// <returns></returns>
        private static uint CreateNewStyle(ExcelStylesManager stylesManager, Stylesheet stylesheet, ExcelCellStyleInfo cellInfo, string formatCode)
        {
            // Create a cellFormat to which style attributes can be applied.
            var cellFormat = new CellFormat();

            // Looks up/creates a new fill colour in the stylesheet and applies it to the cellFormat
            UpdateFillColour(stylesManager, cellInfo, ref stylesheet, ref cellFormat);

            // Create and append a new font to the stylesheet and applies it to the cellFormat
            UpdateFont(stylesManager, cellInfo, ref stylesheet, ref cellFormat);

            // Create a number format in the stylesheet and apply to the cellFormat
            UpdateNumberFormat(stylesManager, cellInfo, formatCode, ref stylesheet, ref cellFormat);

            // Creates a border in the stylesheet and applies it to the cellFormat
            UpdateBorder(stylesManager, cellInfo, ref stylesheet, ref cellFormat);

            // Create an Allignment object which manages indentation, alignment and text rotation and applies it to the cellFormat.
            UpdateTextAlignmentAndRotation(cellInfo, ref cellFormat);

            uint styleIndex = stylesheet.AddCellFormat(cellFormat);

            return(styleIndex);
        }
Example #5
0
        /// <summary>
        /// Updates the supplied <see cref="CellFormat"/> with a font index which relates to the fund in the supplied <see cref="ExportStyle"/>
        /// </summary>
        /// <param name="stylesheet"></param>
        /// <param name="cellInfo"></param>
        /// <returns></returns>
        private static void UpdateFont(ExcelStylesManager stylesManager, ExcelCellStyleInfo cellInfo, ref Stylesheet stylesheet, ref CellFormat cellFormat)
        {
            if (cellFormat == null)
            {
                throw new ArgumentNullException("cellFormat");
            }

            // Anything to apply?
            if (cellInfo.FontInfo == null)
            {
                return;
            }

            // Look up existing...
            var         fontItem = stylesManager.fonts.Find(cellInfo.FontInfo);
            UInt32Value id       = fontItem.Value;

            if (id == null)
            {
                // Fonts
                Font font = new Font();

                if (cellInfo.FontInfo.FontFamily != null)
                {
                    font.FontName = new FontName()
                    {
                        Val = new StringValue(cellInfo.FontInfo.FontFamily.ToString())
                    };
                }

                font.FontSize = new FontSize()
                {
                    Val = new DoubleValue(cellInfo.FontInfo.FontSize)
                };

                if ((cellInfo.FontInfo.FontWeight != null) &&
                    (cellInfo.FontInfo.FontWeight == FontWeights.Bold))
                {
                    font.Bold = new Bold();
                }

                if (cellInfo.FontInfo.FontStyle != null)
                {
                    if (cellInfo.FontInfo.FontStyle == FontStyles.Italic)
                    {
                        font.Italic = new Italic();
                    }
                }

                font.Color = new DocumentFormat.OpenXml.Spreadsheet.Color()
                {
                    Rgb = new HexBinaryValue(StyleTranslator.Translate(cellInfo.FontInfo.FontColour))
                };

                if (cellInfo.FontInfo.FontUnderlined)
                {
                    font.Underline = new Underline();
                }

                id = stylesheet.AddFont(font);
                stylesManager.fonts.Add(cellInfo.FontInfo, id);
            }

            cellFormat.FontId = id;
        }
Example #6
0
        /// <summary>
        /// Write the content of the <see cref="ExcelMapCoOrdinateContainer"> to the worksheet</see>.
        /// </summary>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <param name="worksheetPart">The worksheet part.</param>
        /// <param name="mapCoOrdinateContainer">The map co ordinate container.</param>
        /// <param name="stylesManager">The styles manager.</param>
        /// <param name="spreadsheetDocument">The spreadsheet document.</param>
        public static void WriteMapToExcel(string sheetName,
                                           WorksheetPart worksheetPart,
                                           ExcelMapCoOrdinateContainer mapCoOrdinateContainer,
                                           ExcelStylesManager stylesManager,
                                           SpreadsheetDocument spreadsheetDocument)
        {
            TempDiagnostics.Output(string.Format("Writing map for ExcelMap[{0}] to worksheet '{1}'", mapCoOrdinateContainer.ContainerType, sheetName));

            var dimensionConverter = new ExcelDimensionConverter("Calibri", 11.0f);

            // Manages the writing to Excel.
            var excelWriteManager = new OpenXmlExcelWriteManager(worksheetPart.Worksheet);

            //** First we need to clear the destination worksheet down (rows, columns and merged areas)
            excelWriteManager.EmptyWorksheet();

            // Build up Columns models on all nested containers.
            RowOrColumnsModel columnsModel = mapCoOrdinateContainer.BuildColumnsModel();
            int colCount = columnsModel.Count();

            // ==========================================================
            // Traverse each column assigning start and end
            // column indexes to the maps associated with that column.
            // ==========================================================
            RowOrColumnInfo columnInfo = columnsModel.First;

            while (columnInfo != null)
            {
                double?width = columnInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.WidthToOpenXmlWidth(columnInfo.HeightOrWidth.Value) : null;
                excelWriteManager.AddColumn(width, columnInfo.Hidden);

                // Assign Excel start and end columns to maps for merge operations.
                foreach (ExcelMapCoOrdinate map in columnInfo.Maps)
                {
                    // Extend any cells that are merged across maps.
                    if (map is ExcelMapCoOrdinatePlaceholder)
                    {
                        var cell = map as ExcelMapCoOrdinatePlaceholder;
                        if (cell.MergeWith != null)
                        {
                            cell.ExcelColumnStart         = cell.MergeWith.ExcelColumnStart;
                            cell.MergeWith.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol
                        }
                    }

                    if (map.ExcelColumnStart == 0)
                    {
                        map.ExcelColumnStart = columnInfo.ExcelIndex; //excelCol;
                        map.ExcelColumnEnd   = columnInfo.ExcelIndex; //excelCol;
                    }
                    else
                    {
                        map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol;
                    }
                }

                // Move on to next column in list
                columnInfo = columnInfo.Next;
            }
            TempDiagnostics.Output(string.Format("Created ColumnsModel which contains '{0}' columns", colCount));

            // Build up Rows models on all nested containers.
            RowOrColumnsModel rowsModel = mapCoOrdinateContainer.BuildRowsModel();
            int rowCount = rowsModel.Count();

            // ==========================================================
            // Traverse each row assigning start and end
            // row indexes to the maps associated with that row.
            // ==========================================================
            RowOrColumnInfo rowInfo = rowsModel.First;

            while (rowInfo != null)
            {
                double?height = rowInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.HeightToOpenXmlHeight(rowInfo.HeightOrWidth.Value) : null;
                excelWriteManager.AddRow(height, rowInfo.Hidden);

                // Assign Excel start and end rows to maps for merge operations.
                foreach (ExcelMapCoOrdinate map in rowInfo.Maps)
                {
                    if (map.ExcelRowStart == 0)
                    {
                        map.ExcelRowStart = rowInfo.ExcelIndex; //excelRow;
                        map.ExcelRowEnd   = rowInfo.ExcelIndex; //excelRow;
                    }
                    else
                    {
                        map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow;
                    }
                }

                // Move on to next Row in list
                rowInfo = rowInfo.Next;
            }
            TempDiagnostics.Output(string.Format("Created RowsModel which contains '{0}' rows", rowCount));

            // Build a layered cells dictionary for all cells, keyed by Excel row and column index
            var layeredCellsDictionary = new LayeredCellsDictionary();

            mapCoOrdinateContainer.UpdateLayeredCells(ref layeredCellsDictionary);
            TempDiagnostics.Output(string.Format("Updated Layered Cell Information for worksheet '{0}' = {1}", sheetName, layeredCellsDictionary.Count));

            // Probe the Row, Column and Cell Layered maps, embellishing them with row, column and cell formatting information.
            for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++)
            {
                for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++)
                {
                    // We can now use the layeredCellsDictionary to build the
                    // excel workbook based on layered cell information
                    var             currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow);
                    LayeredCellInfo layeredCellInfo   = layeredCellsDictionary[currentCoOrdinate];

                    // Work through the layered maps to determine what needs to be written to the Excel worksheet at that Row/Column
                    ProcessLayeredCellMaps(currentCoOrdinate, layeredCellInfo);
                }
            }
            TempDiagnostics.Output(string.Format("Built Worksheet CellInfos[Cols={0},Rows={1}]", colCount, rowCount));

            //** Write the 2D array of cell information to the Excel Worksheet
            //** building a list of areas that are to be merged.
            for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++)
            {
                OpenXmlSpreadsheet.Row row = excelWriteManager.GetRow(worksheetRow);

                for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++)
                {
                    // Pluck out information relating to the current cell
                    var           currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow);
                    ExcelCellInfo cellInfo          = layeredCellsDictionary[currentCoOrdinate].CellInfo;

                    // Not sure if we need this as column letters are easily (quickly) translated using existing OpenXml helpers
                    string columnLetter = excelWriteManager.GetColumnLetter(worksheetCol);

                    // All merge cells should have the same style as the source merge cell.
                    if (cellInfo.MergeFrom != null)
                    {
                        // If MergeFrom is non-null, then this cell has been already been marked as a merge cell,
                        // whose style is to be the same as the source 'MergeFrom cell.
                        // Update the source (MergeFrom) cell so it ends up containing a reference to the last (MergeTo) cell to be merged.
                        cellInfo.MergeFrom.MergeTo = cellInfo;

                        // Create/lookup styles in the target workbook and return index of created style
                        uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.MergeFrom.StyleInfo);

                        // Write in to Excel (style information only)
                        var cell = new OpenXmlSpreadsheet.Cell();
                        cell.SetCellReference(columnLetter, worksheetRow);
                        cell.StyleIndex = cellStyleIndex;
                        row.Append(cell);
                        cellInfo.Cell = cell;
                    }
                    else
                    {
                        // Create/lookup styles in the target workbook and return index of created style
                        uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.StyleInfo);

                        // NB! If we write a Null value then we lose cell formatting information for some reason
                        object value = cellInfo.Value == null ? string.Empty : cellInfo.Value;

                        // Write in to Excel
                        var cell = new OpenXmlSpreadsheet.Cell();
                        cell.SetCellReference(columnLetter, worksheetRow);
                        excelWriteManager.SetCellValue(cell, value);
                        cell.StyleIndex = cellStyleIndex;
                        row.Append(cell);
                        cellInfo.Cell = cell;
                    }

                    // Span the cell accross it's parent columns if specified
                    if (cellInfo.LastSpanRow == 0)
                    {
                        cellInfo.LastSpanRow = worksheetRow;
                    }
                    if (cellInfo.LastSpanColumn == 0)
                    {
                        cellInfo.LastSpanColumn = worksheetCol;
                    }

                    // Merge cells if required (spanning)
                    for (uint rowIdx = worksheetRow; rowIdx <= cellInfo.LastSpanRow; rowIdx++)
                    {
                        for (uint colIdx = worksheetCol; colIdx <= cellInfo.LastSpanColumn; colIdx++)
                        {
                            // Mark processed (so we don't over-write)
                            var coOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx);
                            if (coOrdinate != currentCoOrdinate)
                            {
                                var           mergeCoOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx);
                                ExcelCellInfo mergeCellInfo   = layeredCellsDictionary[mergeCoOrdinate].CellInfo;
                                if (mergeCellInfo.MergeFrom == null)
                                {
                                    mergeCellInfo.MergeFrom = cellInfo;
                                }
                            }
                        }
                    }
                }
            }

//#if DEBUG
//            if (!skipMerge)
//            {
//#endif
            // Merge cells that have been marked to merge in the cellInfos dictionary.
            MergeCells(worksheetPart.Worksheet, (uint)rowCount, (uint)colCount, layeredCellsDictionary);
//#if DEBUG
//            }
//#endif
            TempDiagnostics.Output(string.Format("Written CellInfos[Cols={0},Rows={1}] to Excel", colCount, rowCount));

            // Create a list of all of the the defined names in the container.
            var definedNameList = new List <ExcelDefinedNameInfo>();

            mapCoOrdinateContainer.UpdateDefinedNameList(ref definedNameList, sheetName);

            // And write into Excel workbook
            foreach (var definedNameInfo in definedNameList)
            {
                uint rangeColumnCount = definedNameInfo.EndColumnIndex - definedNameInfo.StartColumnIndex + 1;
                uint rangeRowCount    = definedNameInfo.EndRowIndex - definedNameInfo.StartRowIndex + 1;

                if (rangeRowCount > 0 && rangeColumnCount > 0)
                {
                    spreadsheetDocument.WorkbookPart.Workbook.AddDefinedName(sheetName,
                                                                             definedNameInfo.DefinedName,
                                                                             (uint)definedNameInfo.StartColumnIndex,
                                                                             (uint)definedNameInfo.StartRowIndex,
                                                                             (int)rangeColumnCount,
                                                                             (int)rangeRowCount);
                }
            }

            TempDiagnostics.Output(string.Format("Defined Names added - write map for {0} complete...", mapCoOrdinateContainer.ContainerType));
        }