Пример #1
0
        /// <summary>
        /// Loads the default configuration.
        /// </summary>
        /// <param name="Grid">The grid.</param>
        private void LoadDefaultConfiguration(JDHSoftware.Krypton.Toolkit.KryptonOutlookGrid.KryptonOutlookGrid Grid)
        {
            Grid.ClearEverything();
            Grid.GroupBox.Visible     = true;
            Grid.HideColumnOnGrouping = false;

            Grid.FillMode  = FillMode.GroupsAndNodes; //treemode enabled;
            Grid.ShowLines = true;

            activeColumns = new SandBoxGridColumn[] {
                SandBoxGridColumn.ColumnCustomerID,
                SandBoxGridColumn.ColumnCustomerName,
                SandBoxGridColumn.ColumnAddress,
                SandBoxGridColumn.ColumnCity,
                SandBoxGridColumn.ColumnCountry,
                SandBoxGridColumn.ColumnOrderDate,
                SandBoxGridColumn.ColumnProduct,
                SandBoxGridColumn.ColumnPrice,
                SandBoxGridColumn.SatisfactionColumn,
                SandBoxGridColumn.ColumnToken
            };

            DataGridViewColumn[] columnsToAdd = new DataGridViewColumn[10]
            {
                SetupColumn(SandBoxGridColumn.ColumnCustomerID),
                SetupColumn(SandBoxGridColumn.ColumnCustomerName),
                SetupColumn(SandBoxGridColumn.ColumnAddress),
                SetupColumn(SandBoxGridColumn.ColumnCity),
                SetupColumn(SandBoxGridColumn.ColumnCountry),
                SetupColumn(SandBoxGridColumn.ColumnOrderDate),
                SetupColumn(SandBoxGridColumn.ColumnProduct),
                SetupColumn(SandBoxGridColumn.ColumnPrice),
                SetupColumn(SandBoxGridColumn.SatisfactionColumn),
                SetupColumn(SandBoxGridColumn.ColumnToken)
            };
            Grid.Columns.AddRange(columnsToAdd);

            //Define the columns for a possible grouping
            Grid.AddInternalColumn(columnsToAdd[0], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
            Grid.AddInternalColumn(columnsToAdd[1], new OutlookGridAlphabeticGroup(null), SortOrder.None, -1, -1);
            Grid.AddInternalColumn(columnsToAdd[2], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
            Grid.AddInternalColumn(columnsToAdd[3], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
            Grid.AddInternalColumn(columnsToAdd[4], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
            Grid.AddInternalColumn(columnsToAdd[5], new OutlookGridDateTimeGroup(null), SortOrder.None, -1, -1);
            Grid.AddInternalColumn(columnsToAdd[6], new OutlookGridDefaultGroup(null)
            {
                OneItemText = "1 product", XXXItemsText = " products"
            }, SortOrder.None, -1, -1);
            Grid.AddInternalColumn(columnsToAdd[7], new OutlookGridPriceGroup(null), SortOrder.None, -1, -1);
            Grid.AddInternalColumn(columnsToAdd[8], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
            Grid.AddInternalColumn(columnsToAdd[9], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);

            //Add a default conditionnal formatting
            ConditionalFormatting cond = new ConditionalFormatting();

            cond.ColumnName   = SandBoxGridColumn.ColumnPrice.ToString();
            cond.FormatType   = EnumConditionalFormatType.TwoColorsRange;
            cond.FormatParams = new TwoColorsParams(Color.White, Color.FromArgb(255, 255, 58, 61));
            Grid.ConditionalFormatting.Add(cond);
        }
Пример #2
0
        public static bool SetConditionalFormatting(this Worksheet sheet, WorkbookPart book, string columnName,
                                                    uint startRow,
                                                    Func <ConditionalFormatting, bool> isConditionalFormatting,
                                                    Func <WorkbookPart, uint, ConditionalFormatting> CreateConditionalFormatting)
        {
            var conditionalFormattings = sheet.Elements <ConditionalFormatting>();
            ConditionalFormatting conditionalFormatting = null;

            for (int x = 0; x < conditionalFormattings.Count(); x++)
            {
                var currentConditionalFormatting = conditionalFormattings.ElementAt(x);
                var cfRules = currentConditionalFormatting.Elements <ConditionalFormattingRule>();
                if (isConditionalFormatting(currentConditionalFormatting))
                {
                    conditionalFormatting = currentConditionalFormatting;
                    break;
                }
            }
            if (conditionalFormatting == null)
            {
                conditionalFormatting = sheet.InsertAfter(CreateConditionalFormatting(book, startRow), conditionalFormattings.Count() > 0 ? (OpenXmlElement)conditionalFormattings.Last() : sheet.Elements <MergeCells>().First());
            }

            var sqref         = conditionalFormatting.SequenceOfReferences;
            var cellReference = columnName + startRow;

            if (!sqref.Items.Any(s => s.Value == cellReference))
            {
                sqref.Items.Add(cellReference);
            }
            return(true);
        }
Пример #3
0
        public static void CreateConditionalFormatting()
        {
            var workbook = new Workbook(); //creating workbook and adding sheet
            var companiesSheet = new Sheet("CompaniesByRevenue");
            workbook.Sheets.AddSheet(companiesSheet);

            companiesSheet.Columns[0].Width = 5;
            companiesSheet.Columns[1].Width = 30;
            companiesSheet.Columns[2].Width = 25;
            companiesSheet.Columns[3].Width = 25;
            companiesSheet.Columns[4].Width = 15;

            var headingStyle = new CellStyle(); //define bold and centered header style
            headingStyle.Font.Bold = true;
            headingStyle.Alignment.Horizontal = HorizontalAlignment.Center;

            var thousandsFormatStyle = new CellStyle(); //adding thousands separator format on Employees column
            thousandsFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
            thousandsFormatStyle.Format = "#,#0";

            var currencyFormatStyle = new CellStyle(); //adding $ (dollar) sign on Revenue and Capitalization columns
            currencyFormatStyle.Format = "$#,#0";
            currencyFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;

            var table = companiesSheet["A2", "E6"];
            table.GetRow(-1).SetValues("Rank", "Company", "Revenue (USD billions)", "Capitalization (USD billions)", "Employees").SetStyle(headingStyle);
            table.GetColumn(0).SetValues(1, 2, 3, 4, 5);
            table.GetColumn(1).SetValues("Wal-Mart Stores, Inc. (USA)", "Royal Dutch Shell (NLD)", "Exxon Mobil Corporation (USA)", "China National Petroleum (CHN)", "Sinopec Group (CHN)");
            table.GetColumn(2).SetValues(469, 467, 453, 425, 411).SetStyle(currencyFormatStyle);
            table.GetColumn(3).SetValues(248, 132, 406, null, 81).SetStyle(currencyFormatStyle);
            table.GetColumn(4).SetValues(2200000, 90000, 76900, 1668072, 401000).SetStyle(thousandsFormatStyle);

            var originFormating = new ConditionalFormatting(Range.Parse("B2:B6")); //creating ConditionalFormatting for specified range
            originFormating.AddContainsText("USA", new CellStyle { Fill = CellFill.BackColor(Color.Red) });
            originFormating.AddEndsWithText("(NLD)", new CellStyle { Fill = CellFill.BackColor(new Color("#0096FF")) });
            originFormating.AddContainsText("CHN", new CellStyle { Fill = CellFill.BackColor(new Color("#FFFF96")) });

            var revenueFormatting = new ConditionalFormatting();
            revenueFormatting.AddRange("C2:C6"); //multiple ranges or cells can be added
            revenueFormatting.AddDefaultIconSet(IconSetType.Item5Arrows); //add IconSet with 5 arrows
            revenueFormatting.AddGreaterThan("460", new CellStyle { Font = new CellFont { Underline = FontUnderline.Single } });

            var capitalizationFormatting = new ConditionalFormatting(Range.Parse("D1:D6")); //specifying range in constructor
            capitalizationFormatting.AddColorScale(Color.Yellow, Color.Orange);
            capitalizationFormatting.AddDefaultIconSet();
            capitalizationFormatting.AddIsBlank(new CellStyle { Fill = CellFill.BackColor(Color.Red) }); //add style applied on blank cell

            ConditionalFormatting employeesNumberFormating = new ConditionalFormatting(Range.Parse("E1:E6"));
            employeesNumberFormating.AddDefaultDataBar(Color.Blue); //adding blue data bar conditional formatting rule

            //adding different conditional formattings to sheet
            companiesSheet.ConditionalFormatting.Add(originFormating);
            companiesSheet.ConditionalFormatting.Add(revenueFormatting);
            companiesSheet.ConditionalFormatting.Add(capitalizationFormatting);
            companiesSheet.ConditionalFormatting.Add(employeesNumberFormating);

            workbook.Save(@"ConditionalFormatting.xlsx");
        }
Пример #4
0
        public static void AddConditionalFormatting(this Worksheet worksheet, int rowStart, int rowCount, int columnStart, int columnCount)
        {
            string range = ComputeRange(rowStart, rowCount, columnStart, columnCount);

            ConditionalFormatting conditionalFormatting1 = new ConditionalFormatting()
            {
                SequenceOfReferences = new ListValue <StringValue>()
                {
                    InnerText = range
                }
            };

            ConditionalFormattingRule conditionalFormattingRule1 =
                new ConditionalFormattingRule()
            {
                Type = ConditionalFormatValues.ColorScale, Priority = 1
            };

            ColorScale colorScale1 = new ColorScale();
            ConditionalFormatValueObject conditionalFormatValueObject1 = new ConditionalFormatValueObject()
            {
                Type = ConditionalFormatValueObjectValues.Number, Val = "0"
            };
            ConditionalFormatValueObject conditionalFormatValueObject2 = new ConditionalFormatValueObject()
            {
                Type = ConditionalFormatValueObjectValues.Percentile, Val = "50"
            };
            ConditionalFormatValueObject conditionalFormatValueObject3 = new ConditionalFormatValueObject()
            {
                Type = ConditionalFormatValueObjectValues.Number, Val = "100"
            };
            Color color1 = new Color()
            {
                Rgb = "FFF8696B"
            };
            Color color2 = new Color()
            {
                Rgb = "FFFFEB84"
            };
            Color color3 = new Color()
            {
                Rgb = "FF63BE7B"
            };

            colorScale1.Append(conditionalFormatValueObject1);
            colorScale1.Append(conditionalFormatValueObject2);
            colorScale1.Append(conditionalFormatValueObject3);
            colorScale1.Append(color1);
            colorScale1.Append(color2);
            colorScale1.Append(color3);

            conditionalFormattingRule1.Append(colorScale1);

            conditionalFormatting1.Append(conditionalFormattingRule1);

            // If we don't have this after SheetData, it corrupts the file if we have added hyperlinks before
            worksheet.InsertAfter(conditionalFormatting1, worksheet.Descendants <SheetData>().First());
        }
Пример #5
0
 public void ComputeAggregates()
 {
     if (computeAggregateExecuted || GroupValues != null || TotalGroupValues != null)
     {
         return;
     }
     computeAggregateExecuted = true;
     Column.ComputeAggregates(this, ColumnHierarchy.AggregateType);
     ConditionalFormatting?.AddValue(GetValue());
 }
Пример #6
0
        public void SetCondition(string range, string opr, string formula, int priority, int style)
        {
            ConditionalFormatting conditionalFormatting = this.part.Worksheet.Elements <ConditionalFormatting>().FirstOrDefault();

            if (conditionalFormatting == null)
            {
                conditionalFormatting = new ConditionalFormatting();
                conditionalFormatting.SequenceOfReferences = new ListValue <StringValue>();

                this.part.Worksheet.Append(conditionalFormatting);
            }

            conditionalFormatting.SequenceOfReferences.Items.Add(new StringValue()
            {
                InnerText = range
            });

            ConditionalFormattingOperatorValues mopr = ConditionalFormattingOperatorValues.Equal;

            if (opr == "<")
            {
                mopr = ConditionalFormattingOperatorValues.LessThan;
            }
            if (opr == "<=")
            {
                mopr = ConditionalFormattingOperatorValues.LessThanOrEqual;
            }
            if (opr == ">")
            {
                mopr = ConditionalFormattingOperatorValues.GreaterThan;
            }
            if (opr == ">=")
            {
                mopr = ConditionalFormattingOperatorValues.GreaterThanOrEqual;
            }

            ConditionalFormattingRule rule = new ConditionalFormattingRule()
            {
                Type     = ConditionalFormatValues.CellIs,
                FormatId = (UInt32)style,
                Priority = priority,
                Operator = mopr
            };

            rule.Append(new Formula(formula));

            conditionalFormatting.Append(rule);
        }
        /// <summary>
        /// Добавить условное форматирование
        /// </summary>
        /// <param name="worksheet">Лист в который добавляется форматирование</param>
        /// <param name="formattingExpression">Выражение которое определяет ячейки для форматирования</param>
        /// <param name="style">Стиль условного форматирования</param>
        /// <param name="targetCellAddresses">Области ячеек для которых будет задействовано данное условие</param>
        public static void AddFormattingRule(this Worksheet worksheet, string formattingExpression, DifferentialFormat style, params string[] targetCellAddresses)
        {
            if (targetCellAddresses == null || targetCellAddresses.Count() == 0)
            {
                targetCellAddresses = new string[] { "1:1048576" };
            }

            var styleSheet = worksheet.GetWorkbookPart().GetStylesheet();

            if (styleSheet.DifferentialFormats == null)
            {
                styleSheet.DifferentialFormats = new DifferentialFormats()
                {
                    Count = 0
                };
            }
            var dfList = styleSheet.DifferentialFormats;

            dfList.AddDFormat(style);
            var formattingRule = Fabric.MakeFormattingRule(formattingExpression);

            formattingRule.FormatId = (uint)style.Index();

            IEnumerable <StringValue> stringValues = targetCellAddresses.Select(rng => new StringValue(rng));
            var sqref          = new ListValue <StringValue>(stringValues);
            var condFormatting = new ConditionalFormatting(formattingRule)
            {
                SequenceOfReferences = sqref
            };

            worksheet.Insert(condFormatting).AfterOneOf(
                typeof(MergeCells)
                , typeof(CustomSheetView)
                , typeof(DataConsolidate)
                , typeof(SortState)
                , typeof(AutoFilter)
                , typeof(Scenarios)
                , typeof(ProtectedRanges)
                , typeof(SheetProtection)
                , typeof(SheetCalculationProperties)
                , typeof(SheetData)
                );
        }
Пример #8
0
        private void AddConditionalFormatting(Worksheet ws, int index)
        {
            var conditionalFormatting1 =
                new ConditionalFormatting {
                SequenceOfReferences = new ListValue <StringValue> {
                    InnerText = formatReference
                }
            };
            var conditionalFormattingRule1 =
                new ConditionalFormattingRule {
                Type = ConditionalFormatValues.Expression, FormatId = (uint)index, Priority = 1
            };
            var formula1 = new Formula {
                Text = formatFormula
            };

            conditionalFormattingRule1.Append(formula1);
            conditionalFormatting1.Append(conditionalFormattingRule1);
            var pp = ws.Descendants <PhoneticProperties>()
                     .FirstOrDefault();

            ws.InsertAfter(conditionalFormatting1, pp);
        }
Пример #9
0
        public void AppendConditionalFormatting(String[] sequenceOfReferences, UInt32 formatId, String formula)
        {
            ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
            {
                SequenceOfReferences = new ListValue <StringValue>()
                {
                    InnerText = sequenceOfReferences[0]  // пока только первая стока
                }
            };
            ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
            {
                Type     = ConditionalFormatValues.Expression,
                FormatId = formatId,
                Priority = 1
            };

            conditionalFormattingRule.Append(new Formula()
            {
                Text = formula
            });
            conditionalFormatting.Append(conditionalFormattingRule);
            worksheet.Append(conditionalFormatting);
        }
Пример #10
0
        private PageSetup GetPageSetup()
        {
            PageSetup pageSetup = worksheet.GetFirstChild <PageSetup>();

            if (pageSetup == null)
            {
                pageSetup = new PageSetup();
                // ищем куда вставить
                ConditionalFormatting conditionalFormatting = null;
                var conditionalFormattings = worksheet.Elements <ConditionalFormatting>();
                if (conditionalFormattings != null && conditionalFormattings.Count <ConditionalFormatting>() > 0)
                {
                    conditionalFormatting = conditionalFormattings.Last <ConditionalFormatting>();
                }
                if (conditionalFormatting != null)
                {
                    conditionalFormatting.InsertAfterSelf <PageSetup>(pageSetup);
                }
                else
                {
                    MergeCells mergeCells = worksheet.GetFirstChild <MergeCells>();
                    if (mergeCells != null)
                    {
                        mergeCells.InsertAfterSelf <PageSetup>(pageSetup);
                    }
                    else
                    {
                        SheetData sheetData = worksheet.GetFirstChild <SheetData>();
                        if (sheetData != null)
                        {
                            sheetData.InsertAfterSelf <PageSetup>(pageSetup);
                        }
                    }
                }
            }
            return(pageSetup);
        }
Пример #11
0
        static void Main(string[] args)
        {
            var workbookClosed = new XLWorkbook();

            var sheet = workbookClosed.Worksheets.Add("Sheet1");
            sheet.Cell(1, 1).Value = 1;
            sheet.Cell(2, 1).Value = -1;

            workbookClosed.SaveAs("closed.xlsx");

            using (SpreadsheetDocument report = SpreadsheetDocument.Open("closed.xlsx", true))
            {
                Workbook workbook = report.WorkbookPart.Workbook;
                WorksheetPart worksheetPart = workbook.WorkbookPart.WorksheetParts.First();

                DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)2U };

                DifferentialFormat lessThanFormat = new DifferentialFormat();
                Font lessThanFont = new Font();
                lessThanFont.Append(new Condense() { Val = false });
                lessThanFont.Append(new Extend() { Val = false });
                lessThanFont.Append(new Color() { Rgb = "FF9C0006" });
                Fill lessThanFill = new Fill();
                PatternFill lessThanPatternFill = new PatternFill();
                lessThanPatternFill.Append(new BackgroundColor() { Rgb = "FFFFC7CE" });
                lessThanFill.Append(lessThanPatternFill);
                lessThanFormat.Append(lessThanFont);
                lessThanFormat.Append(lessThanFill);

                DifferentialFormat greaterThanFormat = new DifferentialFormat();
                Font greaterThanFont = new Font();
                greaterThanFont.Append(new Condense() { Val = false });
                greaterThanFont.Append(new Extend() { Val = false });
                greaterThanFont.Append(new Color() { Rgb = "FF006100" });
                Fill greatherThanFill = new Fill();
                PatternFill greaterThanPatternFill = new PatternFill();
                greaterThanPatternFill.Append(new BackgroundColor() { Rgb = "FFC6EFCE" });
                greatherThanFill.Append(greaterThanPatternFill);
                greaterThanFormat.Append(greaterThanFont);
                greaterThanFormat.Append(greatherThanFill);

                differentialFormats.Append(lessThanFormat);
                differentialFormats.Append(greaterThanFormat);
                workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Append(differentialFormats);

                ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
                {
                    SequenceOfReferences = new ListValue<StringValue>() { InnerText = "D2:D10" }
                };

                ConditionalFormattingRule greaterThanRule = new ConditionalFormattingRule()
                {
                    Type = ConditionalFormatValues.CellIs,
                    FormatId = (UInt32Value)1U,
                    Priority = 2,
                    Operator = ConditionalFormattingOperatorValues.GreaterThan
                };
                Formula greaterThanFormula = new Formula();
                greaterThanFormula.Text = "0";
                greaterThanRule.Append(greaterThanFormula);

                ConditionalFormattingRule lessThanRule = new ConditionalFormattingRule()
                {
                    Type = ConditionalFormatValues.CellIs,
                    FormatId = (UInt32Value)0U,
                    Priority = 1,
                    Operator = ConditionalFormattingOperatorValues.LessThan
                };
                Formula lessThanFormula = new Formula();
                lessThanFormula.Text = "0";
                lessThanRule.Append(lessThanFormula);

                conditionalFormatting.Append(greaterThanRule);
                conditionalFormatting.Append(lessThanRule);

                worksheetPart.Worksheet.PrependChild<ConditionalFormatting>(conditionalFormatting);

                report.WorkbookPart.Workbook.Save();
                report.Close();
            }
        }
Пример #12
0
        internal void FromConditionalFormatting(ConditionalFormatting cf)
        {
            this.SetAllNull();

            if (cf.Pivot != null) this.Pivot = cf.Pivot.Value;

            SLCellPointRange pt;
            int index;
            int iStartRowIndex = -1;
            int iStartColumnIndex = -1;
            int iEndRowIndex = -1;
            int iEndColumnIndex = -1;
            foreach (var s in cf.SequenceOfReferences.Items)
            {
                index = s.Value.IndexOf(":");
                if (index > -1)
                {
                    if (SLTool.FormatCellReferenceRangeToRowColumnIndex(s.Value, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex))
                    {
                        pt = new SLCellPointRange(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex);
                        this.SequenceOfReferences.Add(pt);
                    }
                }
                else
                {
                    if (SLTool.FormatCellReferenceToRowColumnIndex(s.Value, out iStartRowIndex, out iStartColumnIndex))
                    {
                        pt = new SLCellPointRange(iStartRowIndex, iStartColumnIndex, iStartRowIndex, iStartColumnIndex);
                        this.SequenceOfReferences.Add(pt);
                    }
                }
            }

            foreach (var rule in cf.Elements<ConditionalFormattingRule>())
            {
                var cfr = new SLConditionalFormattingRule();
                cfr.FromConditionalFormattingRule(rule);
                this.Rules.Add(cfr);
            }
        }
        private static void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet,
            SaveContext context)
        {
            #region Worksheet

            if (worksheetPart.Worksheet == null)
                worksheetPart.Worksheet = new Worksheet();

            GenerateTables(xlWorksheet, worksheetPart, context);

            if (
                !worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair<String, String>("r",
                    "http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
            {
                worksheetPart.Worksheet.AddNamespaceDeclaration("r",
                    "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            }

            #endregion

            var cm = new XLWSContentManager(worksheetPart.Worksheet);

            #region SheetProperties

            if (worksheetPart.Worksheet.SheetProperties == null)
                worksheetPart.Worksheet.SheetProperties = new SheetProperties();

            worksheetPart.Worksheet.SheetProperties.TabColor = xlWorksheet.TabColor.HasValue
                ? GetTabColor(xlWorksheet.TabColor)
                : null;

            cm.SetElement(XLWSContentManager.XLWSContents.SheetProperties, worksheetPart.Worksheet.SheetProperties);

            if (worksheetPart.Worksheet.SheetProperties.OutlineProperties == null)
                worksheetPart.Worksheet.SheetProperties.OutlineProperties = new OutlineProperties();

            worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryBelow =
                (xlWorksheet.Outline.SummaryVLocation ==
                 XLOutlineSummaryVLocation.Bottom);
            worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryRight =
                (xlWorksheet.Outline.SummaryHLocation ==
                 XLOutlineSummaryHLocation.Right);

            if (worksheetPart.Worksheet.SheetProperties.PageSetupProperties == null
                && (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0))
                worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties {FitToPage = true};

            #endregion

            var maxColumn = 0;

            var sheetDimensionReference = "A1";
            if (xlWorksheet.Internals.CellsCollection.Count > 0)
            {
                maxColumn = xlWorksheet.Internals.CellsCollection.MaxColumnUsed;
                var maxRow = xlWorksheet.Internals.CellsCollection.MaxRowUsed;
                sheetDimensionReference = "A1:" + XLHelper.GetColumnLetterFromNumber(maxColumn) +
                                          maxRow.ToStringLookup();
            }

            if (xlWorksheet.Internals.ColumnsCollection.Count > 0)
            {
                var maxColCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max();
                if (maxColCollection > maxColumn)
                    maxColumn = maxColCollection;
            }

            #region SheetViews

            if (worksheetPart.Worksheet.SheetDimension == null)
                worksheetPart.Worksheet.SheetDimension = new SheetDimension {Reference = sheetDimensionReference};

            cm.SetElement(XLWSContentManager.XLWSContents.SheetDimension, worksheetPart.Worksheet.SheetDimension);

            if (worksheetPart.Worksheet.SheetViews == null)
                worksheetPart.Worksheet.SheetViews = new SheetViews();

            cm.SetElement(XLWSContentManager.XLWSContents.SheetViews, worksheetPart.Worksheet.SheetViews);

            var sheetView = (SheetView)worksheetPart.Worksheet.SheetViews.FirstOrDefault();
            if (sheetView == null)
            {
                sheetView = new SheetView {WorkbookViewId = 0U};
                worksheetPart.Worksheet.SheetViews.AppendChild(sheetView);
            }

            if (xlWorksheet.TabSelected)
                sheetView.TabSelected = true;
            else
                sheetView.TabSelected = null;


            if (xlWorksheet.ShowFormulas)
                sheetView.ShowFormulas = true;
            else
                sheetView.ShowFormulas = null;

            if (xlWorksheet.ShowGridLines)
                sheetView.ShowGridLines = null;
            else
                sheetView.ShowGridLines = false;

            if (xlWorksheet.ShowOutlineSymbols)
                sheetView.ShowOutlineSymbols = null;
            else
                sheetView.ShowOutlineSymbols = false;

            if (xlWorksheet.ShowRowColHeaders)
                sheetView.ShowRowColHeaders = null;
            else
                sheetView.ShowRowColHeaders = false;

            if (xlWorksheet.ShowRuler)
                sheetView.ShowRuler = null;
            else
                sheetView.ShowRuler = false;

            if (xlWorksheet.ShowWhiteSpace)
                sheetView.ShowWhiteSpace = null;
            else
                sheetView.ShowWhiteSpace = false;

            if (xlWorksheet.ShowZeros)
                sheetView.ShowZeros = null;
            else
                sheetView.ShowZeros = false;

            if (xlWorksheet.RightToLeft)
                sheetView.RightToLeft = true;
            else
                sheetView.RightToLeft = null;

            if (xlWorksheet.SheetView.View == XLSheetViewOptions.Normal)
                sheetView.View = null;
            else
                sheetView.View = xlWorksheet.SheetView.View.ToOpenXml();

            var pane = sheetView.Elements<Pane>().FirstOrDefault();
            if (pane == null)
            {
                pane = new Pane();
                sheetView.AppendChild(pane);
            }


            pane.State = PaneStateValues.FrozenSplit;
            Double hSplit = xlWorksheet.SheetView.SplitColumn;
            Double ySplit = xlWorksheet.SheetView.SplitRow;


            pane.HorizontalSplit = hSplit;
            pane.VerticalSplit = ySplit;

            pane.TopLeftCell = XLHelper.GetColumnLetterFromNumber(xlWorksheet.SheetView.SplitColumn + 1)
                               + (xlWorksheet.SheetView.SplitRow + 1);

            if (hSplit == 0 && ySplit == 0)
                sheetView.RemoveAllChildren<Pane>();

            if (xlWorksheet.SelectedRanges.Any() || xlWorksheet.ActiveCell != null)
            {
                sheetView.RemoveAllChildren<Selection>();

                var firstSelection = xlWorksheet.SelectedRanges.FirstOrDefault();
                var selection = new Selection();
                if (xlWorksheet.ActiveCell != null)
                    selection.ActiveCell = xlWorksheet.ActiveCell.Address.ToStringRelative(false);
                else if (firstSelection != null)
                    selection.ActiveCell = firstSelection.RangeAddress.FirstAddress.ToStringRelative(false);


                var seqRef = new List<String> {selection.ActiveCell.Value};
                seqRef.AddRange(xlWorksheet.SelectedRanges
                    .Select(range => range.RangeAddress.ToStringRelative(false)));


                selection.SequenceOfReferences = new ListValue<StringValue> {InnerText = String.Join(" ", seqRef.Distinct().ToArray())};

                sheetView.Append(selection);
            }

            #endregion

            var maxOutlineColumn = 0;
            if (xlWorksheet.ColumnCount() > 0)
                maxOutlineColumn = xlWorksheet.GetMaxColumnOutline();

            var maxOutlineRow = 0;
            if (xlWorksheet.RowCount() > 0)
                maxOutlineRow = xlWorksheet.GetMaxRowOutline();

            #region SheetFormatProperties

            if (worksheetPart.Worksheet.SheetFormatProperties == null)
                worksheetPart.Worksheet.SheetFormatProperties = new SheetFormatProperties();

            cm.SetElement(XLWSContentManager.XLWSContents.SheetFormatProperties,
                worksheetPart.Worksheet.SheetFormatProperties);

            worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight;

            if (xlWorksheet.RowHeightChanged)
                worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true;
            else
                worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = null;


            var worksheetColumnWidth = GetColumnWidth(xlWorksheet.ColumnWidth);
            if (xlWorksheet.ColumnWidthChanged)
                worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = worksheetColumnWidth;
            else
                worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = null;


            if (maxOutlineColumn > 0)
                worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte)maxOutlineColumn;
            else
                worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = null;

            if (maxOutlineRow > 0)
                worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = (byte)maxOutlineRow;
            else
                worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = null;

            #endregion

            #region Columns

            if (xlWorksheet.Internals.CellsCollection.Count == 0 &&
                xlWorksheet.Internals.ColumnsCollection.Count == 0
                && xlWorksheet.Style.Equals(DefaultStyle))
                worksheetPart.Worksheet.RemoveAllChildren<Columns>();
            else
            {
                if (!worksheetPart.Worksheet.Elements<Columns>().Any())
                {
                    var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Columns);
                    worksheetPart.Worksheet.InsertAfter(new Columns(), previousElement);
                }

                var columns = worksheetPart.Worksheet.Elements<Columns>().First();
                cm.SetElement(XLWSContentManager.XLWSContents.Columns, columns);

                var sheetColumnsByMin = columns.Elements<Column>().ToDictionary(c => c.Min.Value, c => c);
                //Dictionary<UInt32, Column> sheetColumnsByMax = columns.Elements<Column>().ToDictionary(c => c.Max.Value, c => c);

                Int32 minInColumnsCollection;
                Int32 maxInColumnsCollection;
                if (xlWorksheet.Internals.ColumnsCollection.Count > 0)
                {
                    minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min();
                    maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max();
                }
                else
                {
                    minInColumnsCollection = 1;
                    maxInColumnsCollection = 0;
                }

                var worksheetStyleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId;
                if (minInColumnsCollection > 1)
                {
                    UInt32Value min = 1;
                    UInt32Value max = (UInt32)(minInColumnsCollection - 1);


                    for (var co = min; co <= max; co++)
                    {
                        var column = new Column
                        {
                            Min = co,
                            Max = co,
                            Style = worksheetStyleId,
                            Width = worksheetColumnWidth,
                            CustomWidth = true
                        };

                        UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax);
                    }
                }

                for (var co = minInColumnsCollection; co <= maxInColumnsCollection; co++)
                {
                    UInt32 styleId;
                    Double columnWidth;
                    var isHidden = false;
                    var collapsed = false;
                    var outlineLevel = 0;
                    if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co))
                    {
                        styleId = context.SharedStyles[xlWorksheet.Internals.ColumnsCollection[co].GetStyleId()].StyleId;
                        columnWidth = GetColumnWidth(xlWorksheet.Internals.ColumnsCollection[co].Width);
                        isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden;
                        collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed;
                        outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel;
                    }
                    else
                    {
                        styleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId;
                        columnWidth = worksheetColumnWidth;
                    }

                    var column = new Column
                    {
                        Min = (UInt32)co,
                        Max = (UInt32)co,
                        Style = styleId,
                        Width = columnWidth,
                        CustomWidth = true
                    };

                    if (isHidden)
                        column.Hidden = true;
                    if (collapsed)
                        column.Collapsed = true;
                    if (outlineLevel > 0)
                        column.OutlineLevel = (byte)outlineLevel;

                    UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax);
                }

                var collection = maxInColumnsCollection;
                foreach (
                    var col in
                        columns.Elements<Column>().Where(c => c.Min > (UInt32)(collection)).OrderBy(
                            c => c.Min.Value))
                {
                    col.Style = worksheetStyleId;
                    col.Width = worksheetColumnWidth;
                    col.CustomWidth = true;

                    if ((Int32)col.Max.Value > maxInColumnsCollection)
                        maxInColumnsCollection = (Int32)col.Max.Value;
                }

                if (maxInColumnsCollection < XLHelper.MaxColumnNumber && !xlWorksheet.Style.Equals(DefaultStyle))
                {
                    var column = new Column
                    {
                        Min = (UInt32)(maxInColumnsCollection + 1),
                        Max = (UInt32)(XLHelper.MaxColumnNumber),
                        Style = worksheetStyleId,
                        Width = worksheetColumnWidth,
                        CustomWidth = true
                    };
                    columns.AppendChild(column);
                }

                CollapseColumns(columns, sheetColumnsByMin);

                if (!columns.Any())
                {
                    worksheetPart.Worksheet.RemoveAllChildren<Columns>();
                    cm.SetElement(XLWSContentManager.XLWSContents.Columns, null);
                }
            }

            #endregion

            #region SheetData

            if (!worksheetPart.Worksheet.Elements<SheetData>().Any())
            {
                var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetData);
                worksheetPart.Worksheet.InsertAfter(new SheetData(), previousElement);
            }

            var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
            cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData);

            var cellsByRow = new Dictionary<Int32, List<IXLCell>>();
            foreach (var c in xlWorksheet.Internals.CellsCollection.GetCells())
            {
                var rowNum = c.Address.RowNumber;
                if (!cellsByRow.ContainsKey(rowNum))
                    cellsByRow.Add(rowNum, new List<IXLCell>());

                cellsByRow[rowNum].Add(c);
            }

            var lastRow = 0;
            var sheetDataRows =
                sheetData.Elements<Row>().ToDictionary(r => r.RowIndex == null ? ++lastRow : (Int32)r.RowIndex.Value,
                    r => r);
            foreach (
                var r in
                    xlWorksheet.Internals.RowsCollection.Deleted.Where(r => sheetDataRows.ContainsKey(r.Key)))
            {
                sheetData.RemoveChild(sheetDataRows[r.Key]);
                sheetDataRows.Remove(r.Key);
                xlWorksheet.Internals.CellsCollection.Deleted.RemoveWhere(d => d.Row == r.Key);
            }

            var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys);
            var noRows = (sheetData.Elements<Row>().FirstOrDefault() == null);
            foreach (var distinctRow in distinctRows.OrderBy(r => r))
            {
                Row row; // = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex.Value == (UInt32)distinctRow);
                if (sheetDataRows.ContainsKey(distinctRow))
                    row = sheetDataRows[distinctRow];
                else
                {
                    row = new Row {RowIndex = (UInt32)distinctRow};
                    if (noRows)
                    {
                        sheetData.AppendChild(row);
                        noRows = false;
                    }
                    else
                    {
                        if (sheetDataRows.Any(r => r.Key > row.RowIndex.Value))
                        {
                            var minRow = sheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r => r.Key);
                            var rowBeforeInsert = sheetDataRows[minRow];
                            sheetData.InsertBefore(row, rowBeforeInsert);
                        }
                        else
                            sheetData.AppendChild(row);
                    }
                }

                if (maxColumn > 0)
                    row.Spans = new ListValue<StringValue> {InnerText = "1:" + maxColumn.ToStringLookup()};

                row.Height = null;
                row.CustomHeight = null;
                row.Hidden = null;
                row.StyleIndex = null;
                row.CustomFormat = null;
                row.Collapsed = null;
                if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow))
                {
                    var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow];
                    if (thisRow.HeightChanged)
                    {
                        row.Height = thisRow.Height;
                        row.CustomHeight = true;
                        row.CustomFormat = true;
                    }

                    if (thisRow.GetStyleId() != xlWorksheet.GetStyleId())
                    {
                        row.StyleIndex = context.SharedStyles[thisRow.GetStyleId()].StyleId;
                        row.CustomFormat = true;
                    }

                    if (thisRow.IsHidden)
                        row.Hidden = true;
                    if (thisRow.Collapsed)
                        row.Collapsed = true;
                    if (thisRow.OutlineLevel > 0)
                        row.OutlineLevel = (byte)thisRow.OutlineLevel;
                }

                var lastCell = 0;
                var cellsByReference = row.Elements<Cell>().ToDictionary(c => c.CellReference == null
                    ? XLHelper.GetColumnLetterFromNumber(
                        ++lastCell) + distinctRow
                    : c.CellReference.Value, c => c);

                foreach (var c in xlWorksheet.Internals.CellsCollection.Deleted.ToList())
                {
                    var key = XLHelper.GetColumnLetterFromNumber(c.Column) + c.Row.ToStringLookup();
                    if (!cellsByReference.ContainsKey(key)) continue;
                    row.RemoveChild(cellsByReference[key]);
                    xlWorksheet.Internals.CellsCollection.Deleted.Remove(c);
                }

                if (!cellsByRow.ContainsKey(distinctRow)) continue;

                var isNewRow = !row.Elements<Cell>().Any();
                var mRows = row.Elements<Cell>().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference.Value), c => c);
                foreach (var opCell in cellsByRow[distinctRow]
                    .OrderBy(c => c.Address.ColumnNumber)
                    .Select(c => (XLCell)c))
                {
                    var styleId = context.SharedStyles[opCell.GetStyleId()].StyleId;

                    var dataType = opCell.DataType;
                    var cellReference = (opCell.Address).GetTrimmedAddress();

                    Cell cell;
                    if (cellsByReference.ContainsKey(cellReference))
                        cell = cellsByReference[cellReference];
                    else
                    {
                        cell = new Cell {CellReference = new StringValue(cellReference)};
                        if (isNewRow)
                            row.AppendChild(cell);
                        else
                        {
                            var newColumn = XLHelper.GetColumnNumberFromAddress(cellReference);

                            Cell cellBeforeInsert = null;
                            int[] lastCo = {Int32.MaxValue};
                            foreach (var c in mRows.Where(kp => kp.Key > newColumn).Where(c => lastCo[0] > c.Key))
                            {
                                cellBeforeInsert = c.Value;
                                lastCo[0] = c.Key;
                            }
                            if (cellBeforeInsert == null)
                                row.AppendChild(cell);
                            else
                                row.InsertBefore(cell, cellBeforeInsert);
                        }
                    }

                    cell.StyleIndex = styleId;
                    var formula = opCell.FormulaA1;
                    if (opCell.HasFormula)
                    {
                        if (formula.StartsWith("{"))
                        {
                            formula = formula.Substring(1, formula.Length - 2);
                            var f = new CellFormula {FormulaType = CellFormulaValues.Array};
                            
                            if (opCell.FormulaReference.FirstAddress.Equals(opCell.Address))
                            {
                                f.Text = formula;
                                f.Reference = opCell.FormulaReference.ToStringRelative();
                            }

                            cell.CellFormula = f;
                        }
                        else
                            cell.CellFormula = new CellFormula(formula);

                        cell.CellValue = null;
                    }
                    else
                    {
                        cell.CellFormula = null;

                        cell.DataType = opCell.DataType == XLCellValues.DateTime ? null : GetCellValue(opCell);

                        var cellValue = new CellValue();
                        if (dataType == XLCellValues.Text)
                        {
                            if (opCell.InnerText.Length == 0)
                                cell.CellValue = null;
                            else
                            {
                                if (opCell.ShareString)
                                {
                                    cellValue.Text = opCell.SharedStringId.ToString();
                                    cell.CellValue = cellValue;
                                }
                                else
                                {
                                    var text = opCell.GetString();
                                    var t = new Text(text);
                                    if (text.PreserveSpaces())
                                        t.Space = SpaceProcessingModeValues.Preserve;

                                    cell.InlineString = new InlineString {Text = t};
                                }
                            }
                        }
                        else if (dataType == XLCellValues.TimeSpan)
                        {
                            var timeSpan = opCell.GetTimeSpan();
                            cellValue.Text =
                                XLCell.BaseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture);
                            cell.CellValue = cellValue;
                        }
                        else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number)
                        {
                            if (!XLHelper.IsNullOrWhiteSpace(opCell.InnerText))
                                cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture);
                            cell.CellValue = cellValue;
                        }
                        else
                        {
                            cellValue.Text = opCell.InnerText;
                            cell.CellValue = cellValue;
                        }
                    }
                }
                xlWorksheet.Internals.CellsCollection.Deleted.RemoveWhere(d => d.Row == distinctRow);
            }
            foreach (
                var r in
                    xlWorksheet.Internals.CellsCollection.Deleted.Select(c => c.Row).Distinct().Where(
                        sheetDataRows.ContainsKey))
            {
                sheetData.RemoveChild(sheetDataRows[r]);
                sheetDataRows.Remove(r);
            }

            #endregion

            #region SheetProtection

            if (xlWorksheet.Protection.Protected)
            {
                if (!worksheetPart.Worksheet.Elements<SheetProtection>().Any())
                {
                    var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetProtection);
                    worksheetPart.Worksheet.InsertAfter(new SheetProtection(), previousElement);
                }

                var sheetProtection = worksheetPart.Worksheet.Elements<SheetProtection>().First();
                cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, sheetProtection);

                var protection = xlWorksheet.Protection;
                sheetProtection.Sheet = protection.Protected;
                if (!XLHelper.IsNullOrWhiteSpace(protection.PasswordHash))
                    sheetProtection.Password = protection.PasswordHash;
                sheetProtection.FormatCells = GetBooleanValue(!protection.FormatCells, true);
                sheetProtection.FormatColumns = GetBooleanValue(!protection.FormatColumns, true);
                sheetProtection.FormatRows = GetBooleanValue(!protection.FormatRows, true);
                sheetProtection.InsertColumns = GetBooleanValue(!protection.InsertColumns, true);
                sheetProtection.InsertHyperlinks = GetBooleanValue(!protection.InsertHyperlinks, true);
                sheetProtection.InsertRows = GetBooleanValue(!protection.InsertRows, true);
                sheetProtection.DeleteColumns = GetBooleanValue(!protection.DeleteColumns, true);
                sheetProtection.DeleteRows = GetBooleanValue(!protection.DeleteRows, true);
                sheetProtection.AutoFilter = GetBooleanValue(!protection.AutoFilter, true);
                sheetProtection.PivotTables = GetBooleanValue(!protection.PivotTables, true);
                sheetProtection.Sort = GetBooleanValue(!protection.Sort, true);
                sheetProtection.SelectLockedCells = GetBooleanValue(!protection.SelectLockedCells, false);
                sheetProtection.SelectUnlockedCells = GetBooleanValue(!protection.SelectUnlockedCells, false);
            }
            else
            {
                worksheetPart.Worksheet.RemoveAllChildren<SheetProtection>();
                cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, null);
            }

            #endregion

            #region AutoFilter

            worksheetPart.Worksheet.RemoveAllChildren<AutoFilter>();
            if (xlWorksheet.AutoFilter.Enabled)
            {
                var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter);
                worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement);


                var autoFilter = worksheetPart.Worksheet.Elements<AutoFilter>().First();
                cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, autoFilter);

                PopulateAutoFilter(xlWorksheet.AutoFilter, autoFilter);
            }
            else
            {
                cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, null);
            }

            #endregion

            #region MergeCells

            if ((xlWorksheet).Internals.MergedRanges.Any())
            {
                if (!worksheetPart.Worksheet.Elements<MergeCells>().Any())
                {
                    var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells);
                    worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement);
                }

                var mergeCells = worksheetPart.Worksheet.Elements<MergeCells>().First();
                cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells);
                mergeCells.RemoveAllChildren<MergeCell>();

                foreach (var mergeCell in (xlWorksheet).Internals.MergedRanges.Select(
                    m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString()).Select(
                        merged => new MergeCell {Reference = merged}))
                    mergeCells.AppendChild(mergeCell);

                mergeCells.Count = (UInt32)mergeCells.Count();
            }
            else
            {
                worksheetPart.Worksheet.RemoveAllChildren<MergeCells>();
                cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, null);
            }

            #endregion

            #region Conditional Formatting

            if (!xlWorksheet.ConditionalFormats.Any())
            {
                worksheetPart.Worksheet.RemoveAllChildren<ConditionalFormatting>();
                cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, null);
            }
            else
            {
                worksheetPart.Worksheet.RemoveAllChildren<ConditionalFormatting>();
                var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting);


                //if (!worksheetPart.Worksheet.Elements<ConditionalFormatting>().Any())
                //{
                //    var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting);
                //    worksheetPart.Worksheet.InsertAfter(new ConditionalFormatting(), previousElement);
                //}

                //var conditionalFormats = worksheetPart.Worksheet.Elements<ConditionalFormatting>().First();
                //cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormats);
                ////conditionalFormats.RemoveAllChildren<ConditionalFormat>();
                var priority = 0;
                foreach (var cf in xlWorksheet.ConditionalFormats)
                {
                    priority++;
                    var conditionalFormatting = new ConditionalFormatting
                    {
                        SequenceOfReferences =
                            new ListValue<StringValue> {InnerText = cf.Range.RangeAddress.ToStringRelative(false)}
                    };
                    conditionalFormatting.Append(XLCFConverters.Convert(cf, priority, context));

                    worksheetPart.Worksheet.InsertAfter(conditionalFormatting, previousElement);
                    previousElement = conditionalFormatting;
                    cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormatting);
                }
            }

            #endregion

            #region DataValidations

            if (!xlWorksheet.DataValidations.Any(d => d.IsDirty()))
            {
                worksheetPart.Worksheet.RemoveAllChildren<DataValidations>();
                cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null);
            }
            else
            {
                if (!worksheetPart.Worksheet.Elements<DataValidations>().Any())
                {
                    var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.DataValidations);
                    worksheetPart.Worksheet.InsertAfter(new DataValidations(), previousElement);
                }

                var dataValidations = worksheetPart.Worksheet.Elements<DataValidations>().First();
                cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, dataValidations);
                dataValidations.RemoveAllChildren<DataValidation>();
                foreach (var dv in xlWorksheet.DataValidations)
                {
                    var sequence = dv.Ranges.Aggregate(String.Empty, (current, r) => current + (r.RangeAddress + " "));

                    if (sequence.Length > 0)
                        sequence = sequence.Substring(0, sequence.Length - 1);

                    var dataValidation = new DataValidation
                    {
                        AllowBlank = dv.IgnoreBlanks,
                        Formula1 = new Formula1(dv.MinValue),
                        Formula2 = new Formula2(dv.MaxValue),
                        Type = dv.AllowedValues.ToOpenXml(),
                        ShowErrorMessage = dv.ShowErrorMessage,
                        Prompt = dv.InputMessage,
                        PromptTitle = dv.InputTitle,
                        ErrorTitle = dv.ErrorTitle,
                        Error = dv.ErrorMessage,
                        ShowDropDown = !dv.InCellDropdown,
                        ShowInputMessage = dv.ShowInputMessage,
                        ErrorStyle = dv.ErrorStyle.ToOpenXml(),
                        Operator = dv.Operator.ToOpenXml(),
                        SequenceOfReferences =
                            new ListValue<StringValue> {InnerText = sequence}
                    };

                    dataValidations.AppendChild(dataValidation);
                }
                dataValidations.Count = (UInt32)xlWorksheet.DataValidations.Count();
            }

            #endregion

            #region Hyperlinks

            var relToRemove = worksheetPart.HyperlinkRelationships.ToList();
            relToRemove.ForEach(worksheetPart.DeleteReferenceRelationship);
            if (!xlWorksheet.Hyperlinks.Any())
            {
                worksheetPart.Worksheet.RemoveAllChildren<Hyperlinks>();
                cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, null);
            }
            else
            {
                if (!worksheetPart.Worksheet.Elements<Hyperlinks>().Any())
                {
                    var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Hyperlinks);
                    worksheetPart.Worksheet.InsertAfter(new Hyperlinks(), previousElement);
                }

                var hyperlinks = worksheetPart.Worksheet.Elements<Hyperlinks>().First();
                cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, hyperlinks);
                hyperlinks.RemoveAllChildren<Hyperlink>();
                foreach (var hl in xlWorksheet.Hyperlinks)
                {
                    Hyperlink hyperlink;
                    if (hl.IsExternal)
                    {
                        var rId = context.RelIdGenerator.GetNext(RelType.Workbook);
                        hyperlink = new Hyperlink {Reference = hl.Cell.Address.ToString(), Id = rId};
                        worksheetPart.AddHyperlinkRelationship(hl.ExternalAddress, true, rId);
                    }
                    else
                    {
                        hyperlink = new Hyperlink
                        {
                            Reference = hl.Cell.Address.ToString(),
                            Location = hl.InternalAddress,
                            Display = hl.Cell.GetFormattedString()
                        };
                    }
                    if (!XLHelper.IsNullOrWhiteSpace(hl.Tooltip))
                        hyperlink.Tooltip = hl.Tooltip;
                    hyperlinks.AppendChild(hyperlink);
                }
            }

            #endregion

            #region PrintOptions

            if (!worksheetPart.Worksheet.Elements<PrintOptions>().Any())
            {
                var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PrintOptions);
                worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement);
            }

            var printOptions = worksheetPart.Worksheet.Elements<PrintOptions>().First();
            cm.SetElement(XLWSContentManager.XLWSContents.PrintOptions, printOptions);

            printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally;
            printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically;
            printOptions.Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings;
            printOptions.GridLines = xlWorksheet.PageSetup.ShowGridlines;

            #endregion

            #region PageMargins

            if (!worksheetPart.Worksheet.Elements<PageMargins>().Any())
            {
                var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageMargins);
                worksheetPart.Worksheet.InsertAfter(new PageMargins(), previousElement);
            }

            var pageMargins = worksheetPart.Worksheet.Elements<PageMargins>().First();
            cm.SetElement(XLWSContentManager.XLWSContents.PageMargins, pageMargins);
            pageMargins.Left = xlWorksheet.PageSetup.Margins.Left;
            pageMargins.Right = xlWorksheet.PageSetup.Margins.Right;
            pageMargins.Top = xlWorksheet.PageSetup.Margins.Top;
            pageMargins.Bottom = xlWorksheet.PageSetup.Margins.Bottom;
            pageMargins.Header = xlWorksheet.PageSetup.Margins.Header;
            pageMargins.Footer = xlWorksheet.PageSetup.Margins.Footer;

            #endregion

            #region PageSetup

            if (!worksheetPart.Worksheet.Elements<PageSetup>().Any())
            {
                var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageSetup);
                worksheetPart.Worksheet.InsertAfter(new PageSetup(), previousElement);
            }

            var pageSetup = worksheetPart.Worksheet.Elements<PageSetup>().First();
            cm.SetElement(XLWSContentManager.XLWSContents.PageSetup, pageSetup);

            pageSetup.Orientation = xlWorksheet.PageSetup.PageOrientation.ToOpenXml();
            pageSetup.PaperSize = (UInt32)xlWorksheet.PageSetup.PaperSize;
            pageSetup.BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite;
            pageSetup.Draft = xlWorksheet.PageSetup.DraftQuality;
            pageSetup.PageOrder = xlWorksheet.PageSetup.PageOrder.ToOpenXml();
            pageSetup.CellComments = xlWorksheet.PageSetup.ShowComments.ToOpenXml();
            pageSetup.Errors = xlWorksheet.PageSetup.PrintErrorValue.ToOpenXml();

            if (xlWorksheet.PageSetup.FirstPageNumber > 0)
            {
                pageSetup.FirstPageNumber = (UInt32)xlWorksheet.PageSetup.FirstPageNumber;
                pageSetup.UseFirstPageNumber = true;
            }
            else
            {
                pageSetup.FirstPageNumber = null;
                pageSetup.UseFirstPageNumber = null;
            }

            if (xlWorksheet.PageSetup.HorizontalDpi > 0)
                pageSetup.HorizontalDpi = (UInt32)xlWorksheet.PageSetup.HorizontalDpi;
            else
                pageSetup.HorizontalDpi = null;

            if (xlWorksheet.PageSetup.VerticalDpi > 0)
                pageSetup.VerticalDpi = (UInt32)xlWorksheet.PageSetup.VerticalDpi;
            else
                pageSetup.VerticalDpi = null;

            if (xlWorksheet.PageSetup.Scale > 0)
            {
                pageSetup.Scale = (UInt32)xlWorksheet.PageSetup.Scale;
                pageSetup.FitToWidth = null;
                pageSetup.FitToHeight = null;
            }
            else
            {
                pageSetup.Scale = null;

                if (xlWorksheet.PageSetup.PagesWide > 0)
                    pageSetup.FitToWidth = (UInt32)xlWorksheet.PageSetup.PagesWide;
                else
                    pageSetup.FitToWidth = 0;

                if (xlWorksheet.PageSetup.PagesTall > 0)
                    pageSetup.FitToHeight = (UInt32)xlWorksheet.PageSetup.PagesTall;
                else
                    pageSetup.FitToHeight = 0;
            }

            #endregion

            #region HeaderFooter

            var headerFooter = worksheetPart.Worksheet.Elements<HeaderFooter>().FirstOrDefault();
            if (headerFooter == null)
                headerFooter = new HeaderFooter();
            else
                worksheetPart.Worksheet.RemoveAllChildren<HeaderFooter>();

            {
                var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.HeaderFooter);
                worksheetPart.Worksheet.InsertAfter(headerFooter, previousElement);
                cm.SetElement(XLWSContentManager.XLWSContents.HeaderFooter, headerFooter);
            }
            if (((XLHeaderFooter)xlWorksheet.PageSetup.Header).Changed
                || ((XLHeaderFooter)xlWorksheet.PageSetup.Footer).Changed)
            {
                //var headerFooter = worksheetPart.Worksheet.Elements<HeaderFooter>().First();

                headerFooter.RemoveAllChildren();

                headerFooter.ScaleWithDoc = xlWorksheet.PageSetup.ScaleHFWithDocument;
                headerFooter.AlignWithMargins = xlWorksheet.PageSetup.AlignHFWithMargins;
                headerFooter.DifferentFirst = xlWorksheet.PageSetup.DifferentFirstPageOnHF;
                headerFooter.DifferentOddEven = xlWorksheet.PageSetup.DifferentOddEvenPagesOnHF;

                var oddHeader = new OddHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.OddPages));
                headerFooter.AppendChild(oddHeader);
                var oddFooter = new OddFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.OddPages));
                headerFooter.AppendChild(oddFooter);

                var evenHeader = new EvenHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.EvenPages));
                headerFooter.AppendChild(evenHeader);
                var evenFooter = new EvenFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.EvenPages));
                headerFooter.AppendChild(evenFooter);

                var firstHeader = new FirstHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.FirstPage));
                headerFooter.AppendChild(firstHeader);
                var firstFooter = new FirstFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.FirstPage));
                headerFooter.AppendChild(firstFooter);
            }

            #endregion

            #region RowBreaks

            if (!worksheetPart.Worksheet.Elements<RowBreaks>().Any())
            {
                var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks);
                worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement);
            }

            var rowBreaks = worksheetPart.Worksheet.Elements<RowBreaks>().First();

            var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count;
            if (rowBreakCount > 0)
            {
                rowBreaks.Count = (UInt32)rowBreakCount;
                rowBreaks.ManualBreakCount = (UInt32)rowBreakCount;
                var lastRowNum = (UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber;
                foreach (var break1 in xlWorksheet.PageSetup.RowBreaks.Select(rb => new Break
                {
                    Id = (UInt32)rb,
                    Max = lastRowNum,
                    ManualPageBreak = true
                }))
                    rowBreaks.AppendChild(break1);
                cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, rowBreaks);
            }
            else
            {
                worksheetPart.Worksheet.RemoveAllChildren<RowBreaks>();
                cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, null);
            }

            #endregion

            #region ColumnBreaks

            if (!worksheetPart.Worksheet.Elements<ColumnBreaks>().Any())
            {
                var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks);
                worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement);
            }

            var columnBreaks = worksheetPart.Worksheet.Elements<ColumnBreaks>().First();

            var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count;
            if (columnBreakCount > 0)
            {
                columnBreaks.Count = (UInt32)columnBreakCount;
                columnBreaks.ManualBreakCount = (UInt32)columnBreakCount;
                var maxColumnNumber = (UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber;
                foreach (var break1 in xlWorksheet.PageSetup.ColumnBreaks.Select(cb => new Break
                {
                    Id = (UInt32)cb,
                    Max = maxColumnNumber,
                    ManualPageBreak = true
                }))
                    columnBreaks.AppendChild(break1);
                cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, columnBreaks);
            }
            else
            {
                worksheetPart.Worksheet.RemoveAllChildren<ColumnBreaks>();
                cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, null);
            }

            #endregion

            #region Drawings

            //worksheetPart.Worksheet.RemoveAllChildren<Drawing>();
            //{
            //    OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Drawing);
            //    worksheetPart.Worksheet.InsertAfter(new Drawing() { Id = String.Format("rId{0}", 1) }, previousElement);
            //}

            //Drawing drawing = worksheetPart.Worksheet.Elements<Drawing>().First();
            //cm.SetElement(XLWSContentManager.XLWSContents.Drawing, drawing);

            #endregion

            #region Tables

            worksheetPart.Worksheet.RemoveAllChildren<TableParts>();
            {
                var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts);
                worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement);
            }

            var tableParts = worksheetPart.Worksheet.Elements<TableParts>().First();
            cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts);

            tableParts.Count = (UInt32)xlWorksheet.Tables.Count();
            foreach (
                var tablePart in
                    from XLTable xlTable in xlWorksheet.Tables select new TablePart {Id = xlTable.RelId})
                tableParts.AppendChild(tablePart);

            #endregion

            #region LegacyDrawing

            if (xlWorksheet.LegacyDrawingIsNew)
            {
                worksheetPart.Worksheet.RemoveAllChildren<LegacyDrawing>();
                {
                    if (!XLHelper.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId))
                    {
                        var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing);
                        worksheetPart.Worksheet.InsertAfter(new LegacyDrawing {Id = xlWorksheet.LegacyDrawingId},
                            previousElement);
                    }
                }
            }

            #endregion

            #region LegacyDrawingHeaderFooter

            //LegacyDrawingHeaderFooter legacyHeaderFooter = worksheetPart.Worksheet.Elements<LegacyDrawingHeaderFooter>().FirstOrDefault();
            //if (legacyHeaderFooter != null)
            //{
            //    worksheetPart.Worksheet.RemoveAllChildren<LegacyDrawingHeaderFooter>();
            //    {
            //            var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawingHeaderFooter);
            //            worksheetPart.Worksheet.InsertAfter(new LegacyDrawingHeaderFooter { Id = xlWorksheet.LegacyDrawingId },
            //                                                previousElement);
            //    }
            //}

            #endregion
        }
Пример #14
0
        // Generates content of worksheetPart1.
        private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
        {
            Worksheet worksheet1 = new Worksheet();
            worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1:A2" };

            SheetViews sheetViews1 = new SheetViews();

            SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
            Selection selection1 = new Selection() { ActiveCell = "A2", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A2" } };

            sheetView1.Append(selection1);

            sheetViews1.Append(sheetView1);
            SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D };

            SheetData sheetData1 = new SheetData();

            Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" } };

            Cell cell1 = new Cell() { CellReference = "A1" };
            CellValue cellValue1 = new CellValue();
            cellValue1.Text = "1";

            cell1.Append(cellValue1);

            row1.Append(cell1);

            Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:1" } };

            Cell cell2 = new Cell() { CellReference = "A2" };
            CellValue cellValue2 = new CellValue();
            cellValue2.Text = "-1";

            cell2.Append(cellValue2);

            row2.Append(cell2);

            sheetData1.Append(row1);
            sheetData1.Append(row2);

            ConditionalFormatting conditionalFormatting1 = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1" } };

            ConditionalFormattingRule conditionalFormattingRule1 = new ConditionalFormattingRule() { Type = ConditionalFormatValues.CellIs, FormatId = (UInt32Value)2U, Priority = 2, Operator = ConditionalFormattingOperatorValues.GreaterThan };
            Formula formula1 = new Formula();
            formula1.Text = "0";

            conditionalFormattingRule1.Append(formula1);

            conditionalFormatting1.Append(conditionalFormattingRule1);

            ConditionalFormatting conditionalFormatting2 = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A2" } };

            ConditionalFormattingRule conditionalFormattingRule2 = new ConditionalFormattingRule() { Type = ConditionalFormatValues.CellIs, FormatId = (UInt32Value)0U, Priority = 1, Operator = ConditionalFormattingOperatorValues.LessThan };
            Formula formula2 = new Formula();
            formula2.Text = "0";

            conditionalFormattingRule2.Append(formula2);

            conditionalFormatting2.Append(conditionalFormattingRule2);
            PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

            worksheet1.Append(sheetDimension1);
            worksheet1.Append(sheetViews1);
            worksheet1.Append(sheetFormatProperties1);
            worksheet1.Append(sheetData1);
            worksheet1.Append(conditionalFormatting1);
            worksheet1.Append(conditionalFormatting2);
            worksheet1.Append(pageMargins1);

            worksheetPart1.Worksheet = worksheet1;
        }
Пример #15
0
        public static void CreateConditionalFormatting()
        {
            var workbook       = new Workbook(); //creating workbook and adding sheet
            var companiesSheet = new Sheet("CompaniesByRevenue");

            workbook.Sheets.AddSheet(companiesSheet);

            companiesSheet.Columns[0].Width = 5;
            companiesSheet.Columns[1].Width = 30;
            companiesSheet.Columns[2].Width = 25;
            companiesSheet.Columns[3].Width = 25;
            companiesSheet.Columns[4].Width = 15;

            var headingStyle = new CellStyle(); //define bold and centered header style

            headingStyle.Font.Bold            = true;
            headingStyle.Alignment.Horizontal = HorizontalAlignment.Center;

            var thousandsFormatStyle = new CellStyle(); //adding thousands separator format on Employees column

            thousandsFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
            thousandsFormatStyle.Format = "#,#0";

            var currencyFormatStyle = new CellStyle(); //adding $ (dollar) sign on Revenue and Capitalization columns

            currencyFormatStyle.Format = "$#,#0";
            currencyFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;

            var table = companiesSheet["A2", "E6"];

            table.GetRow(-1).SetValues("Rank", "Company", "Revenue (USD billions)", "Capitalization (USD billions)", "Employees").SetStyle(headingStyle);
            table.GetColumn(0).SetValues(1, 2, 3, 4, 5);
            table.GetColumn(1).SetValues("Wal-Mart Stores, Inc. (USA)", "Royal Dutch Shell (NLD)", "Exxon Mobil Corporation (USA)", "China National Petroleum (CHN)", "Sinopec Group (CHN)");
            table.GetColumn(2).SetValues(469, 467, 453, 425, 411).SetStyle(currencyFormatStyle);
            table.GetColumn(3).SetValues(248, 132, 406, null, 81).SetStyle(currencyFormatStyle);
            table.GetColumn(4).SetValues(2200000, 90000, 76900, 1668072, 401000).SetStyle(thousandsFormatStyle);

            var originFormating = new ConditionalFormatting(Range.Parse("B2:B6")); //creating ConditionalFormatting for specified range

            originFormating.AddContainsText("USA", new CellStyle {
                Fill = CellFill.BackColor(Color.Red)
            });
            originFormating.AddEndsWithText("(NLD)", new CellStyle {
                Fill = CellFill.BackColor(new Color("#0096FF"))
            });
            originFormating.AddContainsText("CHN", new CellStyle {
                Fill = CellFill.BackColor(new Color("#FFFF96"))
            });

            var revenueFormatting = new ConditionalFormatting();

            revenueFormatting.AddRange("C2:C6");                          //multiple ranges or cells can be added
            revenueFormatting.AddDefaultIconSet(IconSetType.Item5Arrows); //add IconSet with 5 arrows
            revenueFormatting.AddGreaterThan("460", new CellStyle {
                Font = new CellFont {
                    Underline = FontUnderline.Single
                }
            });

            var capitalizationFormatting = new ConditionalFormatting(Range.Parse("D1:D6")); //specifying range in constructor

            capitalizationFormatting.AddColorScale(Color.Yellow, Color.Orange);
            capitalizationFormatting.AddDefaultIconSet();
            capitalizationFormatting.AddIsBlank(new CellStyle {
                Fill = CellFill.BackColor(Color.Red)
            });                                                                                          //add style applied on blank cell

            ConditionalFormatting employeesNumberFormating = new ConditionalFormatting(Range.Parse("E1:E6"));

            employeesNumberFormating.AddDefaultDataBar(Color.Blue); //adding blue data bar conditional formatting rule

            //adding different conditional formattings to sheet
            companiesSheet.ConditionalFormatting.Add(originFormating);
            companiesSheet.ConditionalFormatting.Add(revenueFormatting);
            companiesSheet.ConditionalFormatting.Add(capitalizationFormatting);
            companiesSheet.ConditionalFormatting.Add(employeesNumberFormating);

            workbook.Save(@"ConditionalFormatting.xlsx");
        }
Пример #16
0
        internal ConditionalFormatting ToConditionalFormatting()
        {
            ConditionalFormatting cf = new ConditionalFormatting();
            if (this.Pivot) cf.Pivot = this.Pivot;
            if (this.SequenceOfReferences.Count > 0)
            {
                string sRef = string.Empty;
                cf.SequenceOfReferences = new ListValue<StringValue>();
                foreach (SLCellPointRange pt in this.SequenceOfReferences)
                {
                    if (pt.StartRowIndex == pt.EndRowIndex && pt.StartColumnIndex == pt.EndColumnIndex)
                    {
                        sRef = SLTool.ToCellReference(pt.StartRowIndex, pt.StartColumnIndex);
                    }
                    else
                    {
                        sRef = string.Format("{0}:{1}", SLTool.ToCellReference(pt.StartRowIndex, pt.StartColumnIndex), SLTool.ToCellReference(pt.EndRowIndex, pt.EndColumnIndex));
                    }
                    cf.SequenceOfReferences.Items.Add(new StringValue(sRef));
                }
            }

            foreach (SLConditionalFormattingRule cfr in this.Rules)
            {
                cf.Append(cfr.ToConditionalFormattingRule());
            }

            return cf;
        }
Пример #17
0
        private void AddConditionalFormatting()
        {
            //use openxml directly to apply conditional formatting.
            using (SpreadsheetDocument report = SpreadsheetDocument.Open(fileName, true))
            {
                Workbook workbook = report.WorkbookPart.Workbook;
                WorksheetPart worksheetPart = workbook.WorkbookPart.WorksheetParts.First();

                DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)2U };

                DifferentialFormat lessThanFormat = new DifferentialFormat();
                Font lessThanFont = new Font();
                lessThanFont.Append(new Condense() { Val = false });
                lessThanFont.Append(new Extend() { Val = false });
                lessThanFont.Append(new Color() { Rgb = "FF9C0006" });
                Fill lessThanFill = new Fill();
                PatternFill lessThanPatternFill = new PatternFill();
                lessThanPatternFill.Append(new BackgroundColor() { Rgb = "FFFFC7CE" });
                lessThanFill.Append(lessThanPatternFill);
                lessThanFormat.Append(lessThanFont);
                lessThanFormat.Append(lessThanFill);

                DifferentialFormat greaterThanFormat = new DifferentialFormat();
                Font greaterThanFont = new Font();
                greaterThanFont.Append(new Condense() { Val = false });
                greaterThanFont.Append(new Extend() { Val = false });
                greaterThanFont.Append(new Color() { Rgb = "FF006100" });
                Fill greatherThanFill = new Fill();
                PatternFill greaterThanPatternFill = new PatternFill();
                greaterThanPatternFill.Append(new BackgroundColor() { Rgb = "FFC6EFCE" });
                greatherThanFill.Append(greaterThanPatternFill);
                greaterThanFormat.Append(greaterThanFont);
                greaterThanFormat.Append(greatherThanFill);

                differentialFormats.Append(lessThanFormat);
                differentialFormats.Append(greaterThanFormat);
                workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Append(differentialFormats);

                ConditionalFormatting conditionalFormatting = new ConditionalFormatting() {
                    SequenceOfReferences = new ListValue<StringValue>() { InnerText = "D2:D10" }
                };

                ConditionalFormattingRule greaterThanRule = new ConditionalFormattingRule()
                {
                    Type = ConditionalFormatValues.CellIs,
                    FormatId = (UInt32Value)1U,
                    Priority = 2,
                    Operator = ConditionalFormattingOperatorValues.GreaterThan
                };
                Formula greaterThanFormula = new Formula();
                greaterThanFormula.Text = "0";
                greaterThanRule.Append(greaterThanFormula);

                ConditionalFormattingRule lessThanRule = new ConditionalFormattingRule()
                {
                    Type = ConditionalFormatValues.CellIs,
                    FormatId = (UInt32Value)0U,
                    Priority = 1,
                    Operator = ConditionalFormattingOperatorValues.LessThan
                };
                Formula lessThanFormula = new Formula();
                lessThanFormula.Text = "0";
                lessThanRule.Append(lessThanFormula);

                conditionalFormatting.Append(greaterThanRule);
                conditionalFormatting.Append(lessThanRule);

                worksheetPart.Worksheet.Append(conditionalFormatting);

                report.WorkbookPart.Workbook.Save();
                report.Close();
            }
        }
        public static Page GetChartContentPage(ChartSample chartSample)
        {
            Page page = null;

            switch (chartSample.SampleViewType)
            {
            case (int)ChartSampleViewType.GETTING_STARTED:
                page = new GettingStartedSample();
                break;

            case (int)ChartSampleViewType.CHART_TYPES:
                page = new ChartTypesSample();
                break;

            case (int)ChartSampleViewType.MIXED_CHART_TYPES:
                page = new MixedChartTypesSample();
                break;

            case (int)ChartSampleViewType.LEGEND_AND_TITLES:
                page = new TitleAndLegendSample();
                break;

            case (int)ChartSampleViewType.TOOLTIPS:
                page = new TooltipsSample();
                break;

            case (int)ChartSampleViewType.STYLING_SERIES:
                page = new StylingSeriesSample();
                break;

            case (int)ChartSampleViewType.CUSTOMIZING_AXES:
                page = new CustomizingAxesSample();
                break;

            case (int)ChartSampleViewType.THEMING:
                page = new ThemingSample();
                break;

            case (int)ChartSampleViewType.SELECTION_MODES:
                page = new SelectionModesSample();
                break;

            case (int)ChartSampleViewType.TOGGLE_SERIES:
                page = new ToggleSeriesSample();
                break;

            case (int)ChartSampleViewType.DYNAMIC_CHARTS:
                page = new DynamicChartsSample();
                break;

            case (int)ChartSampleViewType.BUBBLE_CHART:
                page = new BubbleChartSample();
                break;

            case (int)ChartSampleViewType.FINANCIAL_CHART:
                page = new FinancialChart();
                break;

            case (int)ChartSampleViewType.ZOOMING_AND_SCROLLING:
                page = new ZoomingAndScrolling();
                break;

            case (int)ChartSampleViewType.HITTEST:
                page = new HitTest();
                break;

            case (int)ChartSampleViewType.ANIMATION:
                page = new AnimationOptions();
                break;

            case (int)ChartSampleViewType.MUTIPLE_AXES:
                page = new MultipleAxesSamples();
                break;

            case (int)ChartSampleViewType.CUSTOM_PLOT_ELEMENTS:
                page = new CustomPlotElements();
                break;

            case (int)ChartSampleViewType.CONDITIONAL_FORMAT:
                page = new ConditionalFormatting();
                break;

            case (int)ChartSampleViewType.DATA_LABEL:
                page = new DataLabelSample();
                break;

            case (int)ChartSampleViewType.SNAPSHOT:
                page = new Snapshot();
                break;

            case (int)ChartSampleViewType.UPDATE_ANIMATION:
                page = new UpdateAnimation();
                break;

            case (int)ChartSampleViewType.SCROLLING:
                page = new ScrollingSample();
                break;
            }

            page.Title = chartSample.Name;

            return(page);
        }
Пример #19
0
        private void ReadSheet(string sheetPath, Sheet sheet)
        {
            Dictionary<uint, SharedFormula> sharedFormulas = new Dictionary<uint, SharedFormula>();
            try
            {
                var xml = ReadFile<CT_Worksheet>(sheetPath);

                if (xml.sheetFormatPr != null)
                {
                    if (xml.sheetFormatPr.customHeight)
                        sheet.DefaultRowHeight = xml.sheetFormatPr.defaultRowHeight;

                    if (xml.sheetFormatPr.defaultColWidthSpecified)
                        sheet.DefaultColumnWidth = xml.sheetFormatPr.defaultColWidth;
                }

                if (xml.sheetViews!=null && xml.sheetViews.sheetView!=null)
                    foreach (var sheetView in xml.sheetViews.sheetView)
                        if (sheetView.workbookViewId == 0) //default view
                        {
                            sheet.ShowGridLines = sheetView.showGridLines;

                            if (sheetView.selection != null && sheetView.selection.Length == 1)
                            {
                                if (sheetView.selection[0].activeCell != null)
                                    sheet.ActiveCell = Cell.Parse(sheetView.selection[0].activeCell);
                            }
                        }

                if (xml.cols != null)
                {
                    foreach (var col in xml.cols)
                    {
                        sheet.Columns.AppendRange((int)col.min - 1, (int)col.max - 1, new SheetColumn
                        {
                            BestFit = col.bestFit,
                            Width = col.customWidth && col.widthSpecified ? col.width : (double?)null,
                            style = GetStyle(col.style),
                            Phonetic = col.phonetic,
                            OutlineLevel = col.outlineLevel,
                            Hidden = col.hidden
                        });
                    }
                }

                if (xml.colBreaks != null)
                    foreach (var b in xml.colBreaks.brk)
                        if (b.man)
                            sheet.ColumnBreaks.Add((int)b.id - 1);

                if (xml.mergeCells!=null && xml.mergeCells.mergeCell != null)
                    foreach (var mc in xml.mergeCells.mergeCell)
                    {
                        var range = Range.Parse(mc.@ref);
                        sheet[range].Merge();
                    }

                if (xml.sheetData!=null)
                    foreach (var rd in xml.sheetData)
                    {
                        var row = sheet[(int)rd.r - 1];

                        if (rd.customFormat)
                            row.Style = GetStyle(rd.s);

                        if (rd.customHeight && rd.htSpecified)
                            row.Height = rd.ht;

                        row.Phonetic = rd.ph;
                        row.Collapsed = rd.collapsed;
                        row.Hidden = rd.hidden;

                        if (rd.c != null)
                            foreach (var c in rd.c)
                            {
                                var cell = Cell.Parse(c.r);
                                var data = row[cell.Col];

                                data.Style = GetStyle(c.s);

                                if (c.v != null)
                                {
                                    object value = null;
                                    switch (c.t)
                                    {
                                        case ST_CellType.n:
                                            var n = Convert.ToDouble(c.v, CultureInfo.InvariantCulture);
                                            value = data.style != null && NumberFormat.IsDateTimeFormat(data.style.format) ? value = XlioUtil.ToDateTime(n) : n;
                                            break;

                                        case ST_CellType.str:
                                        case ST_CellType.inlineStr:
                                            value = c.v;
                                            break;

                                        case ST_CellType.s:
                                            value = sharedStrings[Convert.ToInt32(c.v, CultureInfo.InvariantCulture)];
                                            break;

                                        case ST_CellType.b:
                                            value = c.v != null && c.v.Length > 0 && (c.v[0] == 'T' || c.v[0] == 't' || c.v[0] == '1');
                                            break;
                                    }
                                    row[cell.Col].Value = value;
                                }

                                if (c.f != null)
                                {
                                    switch (c.f.t)
                                    {
                                        case ST_CellFormulaType.normal:
                                            row[cell.Col].Formula = c.f.Value;
                                            break;
                                        case ST_CellFormulaType.shared:
                                            if (c.f.siSpecified)
                                            {
                                                SharedFormula sharedFormula = null;
                                                if (c.f.@ref != null && c.f.Value != null) //shared formula definition
                                                {
                                                    var range = Range.Parse(c.f.@ref);
                                                    var origin = cell;
                                                    var formula = c.f.Value;
                                                    sharedFormula = new SharedFormula(formula, range, origin);
                                                    sharedFormulas[c.f.si] = sharedFormula;
                                                }
                                                else
                                                    sharedFormulas.TryGetValue(c.f.si, out sharedFormula);

                                                row[cell.Col].SharedFormula = sharedFormula;
                                            }
                                            break;
                                        case ST_CellFormulaType.array:
                                            row[cell.Col].Formula = "{" + c.f.Value + "}";
                                            break;
                                    }
                                }

                            }

                        if (xml.rowBreaks != null)
                            foreach (var brk in xml.rowBreaks.brk)
                                if (brk.man)
                                    sheet.RowBreaks.Add((int)brk.id - 1);

                        if (xml.pageSetup != null)
                        {
                            switch (xml.pageSetup.orientation)
                            {
                                case ST_Orientation.portrait:
                                    sheet.Page.Orientation = PageOrientation.Portrait;
                                    break;
                                case ST_Orientation.landscape:
                                    sheet.Page.Orientation = PageOrientation.Landscape;
                                    break;
                            }

                            if (xml.pageSetup.scale > 0)
                                sheet.Page.Scale = (int)xml.pageSetup.scale;
                        }

                        if (xml.pageMargins != null)
                        {
                            if (xml.pageMargins != null)
                                sheet.Page.Margins = new PageMargins
                                {
                                    Bottom = xml.pageMargins.bottom,
                                    Footer = xml.pageMargins.footer,
                                    Header = xml.pageMargins.header,
                                    Left = xml.pageMargins.left,
                                    Right = xml.pageMargins.right,
                                    Top = xml.pageMargins.top
                                };
                        }
                    }

                //conditional formatting
                if (xml.conditionalFormatting != null)
                foreach (CT_ConditionalFormatting conditionalFormatting in xml.conditionalFormatting)
                {
                    var rules = new List<CFRule>();
                    if (conditionalFormatting.cfRule !=null)
                        foreach (var cfRule in conditionalFormatting.cfRule)
                        {
                            var rule = GetConditionalFormattingRule(cfRule);
                            rules.Add(rule);
                        }
                    ConditionalFormatting cf = new ConditionalFormatting
                    {
                        Ranges = conditionalFormatting.sqref.ToList(),
                        Rules = rules
                    };
                    sheet.ConditionalFormatting.Add(cf);
                }

            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                Debug.WriteLine(ex.StackTrace);
                throw;
            }
        }
Пример #20
0
        private void ReadSheet(string sheetPath, Sheet sheet)
        {
            Dictionary <uint, SharedFormula> sharedFormulas = new Dictionary <uint, SharedFormula>();

            try
            {
                var xml = ReadFile <CT_Worksheet>(sheetPath);

                if (xml.sheetFormatPr != null)
                {
                    if (xml.sheetFormatPr.customHeight)
                    {
                        sheet.DefaultRowHeight = xml.sheetFormatPr.defaultRowHeight;
                    }

                    if (xml.sheetFormatPr.defaultColWidthSpecified)
                    {
                        sheet.DefaultColumnWidth = xml.sheetFormatPr.defaultColWidth;
                    }
                }

                if (xml.sheetViews != null && xml.sheetViews.sheetView != null)
                {
                    foreach (var sheetView in xml.sheetViews.sheetView)
                    {
                        if (sheetView.workbookViewId == 0) //default view
                        {
                            sheet.ShowGridLines = sheetView.showGridLines;

                            if (sheetView.selection != null && sheetView.selection.Length == 1)
                            {
                                if (sheetView.selection[0].activeCell != null)
                                {
                                    sheet.ActiveCell = Cell.Parse(sheetView.selection[0].activeCell);
                                }
                            }
                        }
                    }
                }

                if (xml.cols != null)
                {
                    foreach (var col in xml.cols)
                    {
                        sheet.Columns.AppendRange((int)col.min - 1, (int)col.max - 1, new SheetColumn
                        {
                            BestFit      = col.bestFit,
                            Width        = col.customWidth && col.widthSpecified ? col.width : (double?)null,
                            style        = GetStyle(col.style),
                            Phonetic     = col.phonetic,
                            OutlineLevel = col.outlineLevel,
                            Hidden       = col.hidden
                        });
                    }
                }

                if (xml.colBreaks != null)
                {
                    foreach (var b in xml.colBreaks.brk)
                    {
                        if (b.man)
                        {
                            sheet.ColumnBreaks.Add((int)b.id - 1);
                        }
                    }
                }

                if (xml.mergeCells != null && xml.mergeCells.mergeCell != null)
                {
                    foreach (var mc in xml.mergeCells.mergeCell)
                    {
                        var range = Range.Parse(mc.@ref);
                        sheet[range].Merge();
                    }
                }

                if (xml.sheetData != null)
                {
                    foreach (var rd in xml.sheetData)
                    {
                        var row = sheet[(int)rd.r - 1];

                        if (rd.customFormat)
                        {
                            row.Style = GetStyle(rd.s);
                        }

                        if (rd.customHeight && rd.htSpecified)
                        {
                            row.Height = rd.ht;
                        }

                        row.Phonetic  = rd.ph;
                        row.Collapsed = rd.collapsed;
                        row.Hidden    = rd.hidden;

                        if (rd.c != null)
                        {
                            foreach (var c in rd.c)
                            {
                                var cell = Cell.Parse(c.r);
                                var data = row[cell.Col];

                                data.Style = GetStyle(c.s);

                                if (c.v != null)
                                {
                                    object value = null;
                                    switch (c.t)
                                    {
                                    case ST_CellType.n:
                                        var n = Convert.ToDouble(c.v, CultureInfo.InvariantCulture);
                                        value = data.style != null && NumberFormat.IsDateTimeFormat(data.style.format) ? value = XlioUtil.ToDateTime(n) : n;
                                        break;

                                    case ST_CellType.str:
                                    case ST_CellType.inlineStr:
                                        value = c.v;
                                        break;

                                    case ST_CellType.s:
                                        value = sharedStrings[Convert.ToInt32(c.v, CultureInfo.InvariantCulture)];
                                        break;

                                    case ST_CellType.b:
                                        value = c.v != null && c.v.Length > 0 && (c.v[0] == 'T' || c.v[0] == 't' || c.v[0] == '1');
                                        break;
                                    }
                                    row[cell.Col].Value = value;
                                }

                                if (c.f != null)
                                {
                                    switch (c.f.t)
                                    {
                                    case ST_CellFormulaType.normal:
                                        row[cell.Col].Formula = c.f.Value;
                                        break;

                                    case ST_CellFormulaType.shared:
                                        if (c.f.siSpecified)
                                        {
                                            SharedFormula sharedFormula = null;
                                            if (c.f.@ref != null && c.f.Value != null)     //shared formula definition
                                            {
                                                var range   = Range.Parse(c.f.@ref);
                                                var origin  = cell;
                                                var formula = c.f.Value;
                                                sharedFormula          = new SharedFormula(formula, range, origin);
                                                sharedFormulas[c.f.si] = sharedFormula;
                                            }
                                            else
                                            {
                                                sharedFormulas.TryGetValue(c.f.si, out sharedFormula);
                                            }

                                            row[cell.Col].SharedFormula = sharedFormula;
                                        }
                                        break;

                                    case ST_CellFormulaType.array:
                                        row[cell.Col].Formula = "{" + c.f.Value + "}";
                                        break;
                                    }
                                }
                            }
                        }

                        if (xml.rowBreaks != null)
                        {
                            foreach (var brk in xml.rowBreaks.brk)
                            {
                                if (brk.man)
                                {
                                    sheet.RowBreaks.Add((int)brk.id - 1);
                                }
                            }
                        }

                        if (xml.pageSetup != null)
                        {
                            switch (xml.pageSetup.orientation)
                            {
                            case ST_Orientation.portrait:
                                sheet.Page.Orientation = PageOrientation.Portrait;
                                break;

                            case ST_Orientation.landscape:
                                sheet.Page.Orientation = PageOrientation.Landscape;
                                break;
                            }

                            if (xml.pageSetup.scale > 0)
                            {
                                sheet.Page.Scale = (int)xml.pageSetup.scale;
                            }
                        }

                        if (xml.pageMargins != null)
                        {
                            if (xml.pageMargins != null)
                            {
                                sheet.Page.Margins = new PageMargins
                                {
                                    Bottom = xml.pageMargins.bottom,
                                    Footer = xml.pageMargins.footer,
                                    Header = xml.pageMargins.header,
                                    Left   = xml.pageMargins.left,
                                    Right  = xml.pageMargins.right,
                                    Top    = xml.pageMargins.top
                                }
                            }
                            ;
                        }
                    }
                }

                //conditional formatting
                if (xml.conditionalFormatting != null)
                {
                    foreach (CT_ConditionalFormatting conditionalFormatting in xml.conditionalFormatting)
                    {
                        var rules = new List <CFRule>();
                        if (conditionalFormatting.cfRule != null)
                        {
                            foreach (var cfRule in conditionalFormatting.cfRule)
                            {
                                var rule = GetConditionalFormattingRule(cfRule);
                                rules.Add(rule);
                            }
                        }
                        ConditionalFormatting cf = new ConditionalFormatting
                        {
                            Ranges = conditionalFormatting.sqref.ToList(),
                            Rules  = rules
                        };
                        sheet.ConditionalFormatting.Add(cf);
                    }
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                Debug.WriteLine(ex.StackTrace);
                throw;
            }
        }