internal SLConditionalFormatting2010 Clone() { SLConditionalFormatting2010 cf = new SLConditionalFormatting2010(); int i; cf.Rules = new List <SLConditionalFormattingRule2010>(); for (i = 0; i < this.Rules.Count; ++i) { cf.Rules.Add(this.Rules[i].Clone()); } cf.ReferenceSequence = new List <SLCellPointRange>(); SLCellPointRange cpr; for (i = 0; i < this.ReferenceSequence.Count; ++i) { cpr = new SLCellPointRange(this.ReferenceSequence[i].StartRowIndex, this.ReferenceSequence[i].StartColumnIndex, this.ReferenceSequence[i].EndRowIndex, this.ReferenceSequence[i].EndColumnIndex); cf.ReferenceSequence.Add(cpr); } cf.Pivot = this.Pivot; return(cf); }
internal SLConditionalFormatting2010 Clone() { SLConditionalFormatting2010 cf = new SLConditionalFormatting2010(); int i; cf.Rules = new List<SLConditionalFormattingRule2010>(); for (i = 0; i < this.Rules.Count; ++i) { cf.Rules.Add(this.Rules[i].Clone()); } cf.ReferenceSequence = new List<SLCellPointRange>(); SLCellPointRange cpr; for (i = 0; i < this.ReferenceSequence.Count; ++i) { cpr = new SLCellPointRange(this.ReferenceSequence[i].StartRowIndex, this.ReferenceSequence[i].StartColumnIndex, this.ReferenceSequence[i].EndRowIndex, this.ReferenceSequence[i].EndColumnIndex); cf.ReferenceSequence.Add(cpr); } cf.Pivot = this.Pivot; return cf; }
private void LoadSelectedWorksheet() { // Need to check? //if (string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID)) return; WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID); slws = new SLWorksheet(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors, SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize, SimpleTheme.ThemeRowHeight); int index = 0; SLColumnProperties cp; Column col; SLSheetView slsv; MergeCell mc; SLMergeCell slmc; SLConditionalFormatting condformat; SLHyperlink hl; OpenXmlReader oxrRow; int iRowIndex = -1; int iColumnIndex = -1; int iGuessRowIndex = 0; int iGuessColumnIndex = 0; SLRowProperties rp; Row r; Cell c; SLCell slc; OpenXmlReader oxr = OpenXmlReader.Create(wsp); while (oxr.Read()) { if (oxr.ElementType == typeof(SheetProperties)) { SheetProperties sprop = (SheetProperties)oxr.LoadCurrentElement(); slws.PageSettings.SheetProperties.FromSheetProperties(sprop); } else if (oxr.ElementType == typeof(SheetView)) { slsv = new SLSheetView(); slsv.FromSheetView((SheetView)oxr.LoadCurrentElement()); slws.SheetViews.Add(slsv); // we're concerned only with the first workbook view. if (slsv.ShowFormulas && slsv.WorkbookViewId == 0) slws.IsDoubleColumnWidth = true; } else if (oxr.ElementType == typeof(SheetFormatProperties)) { SheetFormatProperties sfp = (SheetFormatProperties)oxr.LoadCurrentElement(); slws.SheetFormatProperties.FromSheetFormatProperties(sfp); } else if (oxr.ElementType == typeof(Column)) { #region Column int i = 0; col = (Column)oxr.LoadCurrentElement(); int min = (int)col.Min.Value; int max = (int)col.Max.Value; for (i = min; i <= max; ++i) { cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize); if (col.Width != null) { cp.Width = col.Width.Value; cp.HasWidth = true; } if (col.Style != null) { index = (int)col.Style.Value; // default is 0 if (index > 0 && index < listStyle.Count) { cp.StyleIndex = (uint)index; } } if (col.Hidden != null && col.Hidden.Value) cp.Hidden = col.Hidden.Value; if (col.BestFit != null && col.BestFit.Value) cp.BestFit = col.BestFit.Value; if (col.Phonetic != null && col.Phonetic.Value) cp.Phonetic = col.Phonetic.Value; if (col.OutlineLevel != null && col.OutlineLevel.Value > 0) cp.OutlineLevel = col.OutlineLevel.Value; if (col.Collapsed != null && col.Collapsed.Value) cp.Collapsed = col.Collapsed.Value; slws.ColumnProperties[i] = cp; } #endregion } else if (oxr.ElementType == typeof(Row)) { #region Row ++iGuessRowIndex; iGuessColumnIndex = 0; r = (Row)oxr.LoadCurrentElement(); rp = new SLRowProperties(SimpleTheme.ThemeRowHeight); if (r.RowIndex != null) { iRowIndex = (int)r.RowIndex.Value; iGuessRowIndex = iRowIndex; } if (r.StyleIndex != null) { index = (int)r.StyleIndex.Value; // default is 0 if (index > 0 && index < listStyle.Count) { rp.StyleIndex = (uint)index; } } if (r.Height != null) { rp.HasHeight = true; rp.Height = r.Height.Value; } if (r.Hidden != null && r.Hidden.Value) rp.Hidden = r.Hidden.Value; if (r.OutlineLevel != null && r.OutlineLevel.Value > 0) rp.OutlineLevel = r.OutlineLevel.Value; if (r.Collapsed != null && r.Collapsed.Value) rp.Collapsed = r.Collapsed.Value; if (r.ThickTop != null && r.ThickTop.Value) rp.ThickTop = r.ThickTop.Value; if (r.ThickBot != null && r.ThickBot.Value) rp.ThickBottom = r.ThickBot.Value; if (r.ShowPhonetic != null && r.ShowPhonetic.Value) rp.ShowPhonetic = r.ShowPhonetic.Value; if (slws.RowProperties.ContainsKey(iGuessRowIndex)) { slws.RowProperties[iGuessRowIndex] = rp; } else { slws.RowProperties.Add(iGuessRowIndex, rp); } oxrRow = OpenXmlReader.Create(r); while (oxrRow.Read()) { if (oxrRow.ElementType == typeof(Cell)) { ++iGuessColumnIndex; c = (Cell)oxrRow.LoadCurrentElement(); slc = new SLCell(); slc.FromCell(c); if (c.CellReference != null) { if (SLTool.FormatCellReferenceToRowColumnIndex(c.CellReference.Value, out iRowIndex, out iColumnIndex)) { iGuessRowIndex = iRowIndex; iGuessColumnIndex = iColumnIndex; slws.Cells[new SLCellPoint(iGuessRowIndex, iGuessColumnIndex)] = slc; } else { slws.Cells[new SLCellPoint(iGuessRowIndex, iGuessColumnIndex)] = slc; } } else { slws.Cells[new SLCellPoint(iGuessRowIndex, iGuessColumnIndex)] = slc; } } } oxrRow.Close(); #endregion } else if (oxr.ElementType == typeof(SheetProtection)) { SLSheetProtection sp = new SLSheetProtection(); sp.FromSheetProtection((SheetProtection)oxr.LoadCurrentElement()); slws.HasSheetProtection = true; slws.SheetProtection = sp.Clone(); } else if (oxr.ElementType == typeof(AutoFilter)) { SLAutoFilter af = new SLAutoFilter(); af.FromAutoFilter((AutoFilter)oxr.LoadCurrentElement()); slws.HasAutoFilter = true; slws.AutoFilter = af.Clone(); } else if (oxr.ElementType == typeof(MergeCell)) { mc = (MergeCell)oxr.LoadCurrentElement(); slmc = new SLMergeCell(); slmc.FromMergeCell(mc); if (slmc.IsValid) slws.MergeCells.Add(slmc); } else if (oxr.ElementType == typeof(ConditionalFormatting)) { condformat = new SLConditionalFormatting(); condformat.FromConditionalFormatting((ConditionalFormatting)oxr.LoadCurrentElement()); slws.ConditionalFormattings.Add(condformat); } else if (oxr.ElementType == typeof(DataValidations)) { DataValidations dvs = (DataValidations)oxr.LoadCurrentElement(); if (dvs.DisablePrompts != null) slws.DataValidationDisablePrompts = dvs.DisablePrompts.Value; if (dvs.XWindow != null) slws.DataValidationXWindow = dvs.XWindow.Value; if (dvs.YWindow != null) slws.DataValidationYWindow = dvs.YWindow.Value; using (OpenXmlReader oxrDataValidation = OpenXmlReader.Create(dvs)) { SLDataValidation dv; while (oxrDataValidation.Read()) { if (oxrDataValidation.ElementType == typeof(DataValidation)) { dv = new SLDataValidation(); dv.FromDataValidation((DataValidation)oxrDataValidation.LoadCurrentElement()); slws.DataValidations.Add(dv); } } } } else if (oxr.ElementType == typeof(Hyperlink)) { hl = new SLHyperlink(); hl.FromHyperlink((Hyperlink)oxr.LoadCurrentElement()); slws.Hyperlinks.Add(hl); } else if (oxr.ElementType == typeof(PrintOptions)) { PrintOptions po = (PrintOptions)oxr.LoadCurrentElement(); if (po.HorizontalCentered != null) slws.PageSettings.PrintHorizontalCentered = po.HorizontalCentered.Value; if (po.VerticalCentered != null) slws.PageSettings.PrintVerticalCentered = po.VerticalCentered.Value; if (po.Headings != null) slws.PageSettings.PrintHeadings = po.Headings.Value; if (po.GridLines != null) slws.PageSettings.PrintGridLines = po.GridLines.Value; if (po.GridLinesSet != null) slws.PageSettings.PrintGridLinesSet = po.GridLinesSet.Value; } else if (oxr.ElementType == typeof(PageMargins)) { PageMargins pm = (PageMargins)oxr.LoadCurrentElement(); if (pm.Left != null) slws.PageSettings.LeftMargin = pm.Left.Value; if (pm.Right != null) slws.PageSettings.RightMargin = pm.Right.Value; if (pm.Top != null) slws.PageSettings.TopMargin = pm.Top.Value; if (pm.Bottom != null) slws.PageSettings.BottomMargin = pm.Bottom.Value; if (pm.Header != null) slws.PageSettings.HeaderMargin = pm.Header.Value; if (pm.Footer != null) slws.PageSettings.FooterMargin = pm.Footer.Value; } else if (oxr.ElementType == typeof(PageSetup)) { PageSetup ps = (PageSetup)oxr.LoadCurrentElement(); // consider setting to 1 if not one of the "valid" paper sizes? if (ps.PaperSize != null) slws.PageSettings.PaperSize = (SLPaperSizeValues)ps.PaperSize.Value; if (ps.Scale != null) slws.PageSettings.iScale = ps.Scale.Value; if (ps.FirstPageNumber != null) slws.PageSettings.FirstPageNumber = ps.FirstPageNumber.Value; if (ps.FitToWidth != null) slws.PageSettings.iFitToWidth = ps.FitToWidth.Value; if (ps.FitToHeight != null) slws.PageSettings.iFitToHeight = ps.FitToHeight.Value; if (ps.PageOrder != null) slws.PageSettings.PageOrder = ps.PageOrder.Value; if (ps.Orientation != null) slws.PageSettings.Orientation = ps.Orientation.Value; if (ps.UsePrinterDefaults != null) slws.PageSettings.UsePrinterDefaults = ps.UsePrinterDefaults.Value; if (ps.BlackAndWhite != null) slws.PageSettings.BlackAndWhite = ps.BlackAndWhite.Value; if (ps.Draft != null) slws.PageSettings.Draft = ps.Draft.Value; if (ps.CellComments != null) slws.PageSettings.CellComments = ps.CellComments.Value; if (ps.Errors != null) slws.PageSettings.Errors = ps.Errors.Value; if (ps.HorizontalDpi != null) slws.PageSettings.HorizontalDpi = ps.HorizontalDpi.Value; if (ps.VerticalDpi != null) slws.PageSettings.VerticalDpi = ps.VerticalDpi.Value; if (ps.Copies != null) slws.PageSettings.Copies = ps.Copies.Value; } else if (oxr.ElementType == typeof(HeaderFooter)) { HeaderFooter hf = (HeaderFooter)oxr.LoadCurrentElement(); if (hf.OddHeader != null) slws.PageSettings.OddHeaderText = hf.OddHeader.Text; if (hf.OddFooter != null) slws.PageSettings.OddFooterText = hf.OddFooter.Text; if (hf.EvenHeader != null) slws.PageSettings.EvenHeaderText = hf.EvenHeader.Text; if (hf.EvenFooter != null) slws.PageSettings.EvenFooterText = hf.EvenFooter.Text; if (hf.FirstHeader != null) slws.PageSettings.FirstHeaderText = hf.FirstHeader.Text; if (hf.FirstFooter != null) slws.PageSettings.FirstFooterText = hf.FirstFooter.Text; if (hf.DifferentOddEven != null) slws.PageSettings.DifferentOddEvenPages = hf.DifferentOddEven.Value; if (hf.DifferentFirst != null) slws.PageSettings.DifferentFirstPage = hf.DifferentFirst.Value; if (hf.ScaleWithDoc != null) slws.PageSettings.ScaleWithDocument = hf.ScaleWithDoc.Value; if (hf.AlignWithMargins != null) slws.PageSettings.AlignWithMargins = hf.AlignWithMargins.Value; } else if (oxr.ElementType == typeof(RowBreaks)) { SLBreak b; uint rowbkindex; using (OpenXmlReader oxrRowBreaks = OpenXmlReader.Create((RowBreaks)oxr.LoadCurrentElement())) { while (oxrRowBreaks.Read()) { if (oxrRowBreaks.ElementType == typeof(Break)) { b = new SLBreak(); b.FromBreak((Break)oxrRowBreaks.LoadCurrentElement()); rowbkindex = b.Id; slws.RowBreaks[(int)rowbkindex] = b; } } } } else if (oxr.ElementType == typeof(ColumnBreaks)) { SLBreak b; uint colbkindex; using (OpenXmlReader oxrColBreaks = OpenXmlReader.Create((ColumnBreaks)oxr.LoadCurrentElement())) { while (oxrColBreaks.Read()) { if (oxrColBreaks.ElementType == typeof(Break)) { b = new SLBreak(); b.FromBreak((Break)oxrColBreaks.LoadCurrentElement()); colbkindex = b.Id; slws.ColumnBreaks[(int)colbkindex] = b; } } } } else if (oxr.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Drawing)) { DocumentFormat.OpenXml.Spreadsheet.Drawing drawing = (DocumentFormat.OpenXml.Spreadsheet.Drawing)oxr.LoadCurrentElement(); slws.DrawingId = drawing.Id; if (wsp.DrawingsPart != null) { Xdr.NonVisualDrawingProperties nvdp; uint iUniqueId = 1; using (OpenXmlReader oxrDrawing = OpenXmlReader.Create(wsp.DrawingsPart.WorksheetDrawing)) { while (oxrDrawing.Read()) { if (oxrDrawing.ElementType == typeof(Xdr.NonVisualDrawingProperties)) { nvdp = (Xdr.NonVisualDrawingProperties)oxrDrawing.LoadCurrentElement(); if (nvdp.Id != null && nvdp.Id.Value > iUniqueId) { iUniqueId = nvdp.Id.Value; } } } } slws.NextWorksheetDrawingId = iUniqueId + 1; } } else if (oxr.ElementType == typeof(Picture)) { Picture pic = (Picture)oxr.LoadCurrentElement(); slws.BackgroundPictureId = pic.Id; slws.BackgroundPictureDataIsInFile = null; } else if (oxr.ElementType == typeof(WorksheetExtensionList)) { WorksheetExtensionList wsextlist = (WorksheetExtensionList)oxr.LoadCurrentElement(); SLConditionalFormatting2010 cf2010; X14.SparklineGroup sparkgrp; SLSparklineGroup spkgrp; using (OpenXmlReader oxrext = OpenXmlReader.Create(wsextlist)) { while (oxrext.Read()) { if (oxrext.ElementType == typeof(X14.ConditionalFormatting)) { cf2010 = new SLConditionalFormatting2010(); cf2010.FromConditionalFormatting((X14.ConditionalFormatting)oxrext.LoadCurrentElement()); slws.ConditionalFormattings2010.Add(cf2010.Clone()); } else if (oxrext.ElementType == typeof(X14.SparklineGroup)) { sparkgrp = (X14.SparklineGroup)oxrext.LoadCurrentElement(); spkgrp = new SLSparklineGroup(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors); spkgrp.FromSparklineGroup(sparkgrp); slws.SparklineGroups.Add(spkgrp.Clone()); } } } } } oxr.Dispose(); if (wsp.TableDefinitionParts != null) { SLTable t; foreach (TableDefinitionPart tdp in wsp.TableDefinitionParts) { t = new SLTable(); t.FromTable(tdp.Table); t.RelationshipID = wsp.GetIdOfPart(tdp); t.IsNewTable = false; slws.Tables.Add(t); } } }
/// <summary> /// Adds conditional formatting into the currently selected worksheet. /// </summary> /// <param name="ConditionalFormatting">An SLConditionalFormatting object with the formatting rules already set. Remember to set at least one formatting rule (a data bar, color scale, icon set or some custom rule).</param> /// <returns>True if successfully added. False otherwise.</returns> public bool AddConditionalFormatting(SLConditionalFormatting ConditionalFormatting) { bool result = false; if (ConditionalFormatting.Rules.Count > 0 && ConditionalFormatting.SequenceOfReferences.Count > 0) { result = true; int index = ConditionalFormatting.Rules.Count; foreach (SLConditionalFormatting cf in slws.ConditionalFormattings) { foreach (SLConditionalFormattingRule cfr in cf.Rules) { cfr.Priority += index; } } foreach (SLConditionalFormatting2010 cf2010 in slws.ConditionalFormattings2010) { foreach (SLConditionalFormattingRule2010 cfr2010 in cf2010.Rules) { if (cfr2010.Priority != null) cfr2010.Priority += index; } } bool bIs2010 = false; bool bIsDataBar2010 = false; bool bIsIconSet2010 = false; string sGuid = string.Empty; SLConditionalFormatting2010 cf2010new = new SLConditionalFormatting2010(); foreach (SLCellPointRange pt in ConditionalFormatting.SequenceOfReferences) { cf2010new.ReferenceSequence.Add(new SLCellPointRange(pt.StartRowIndex, pt.StartColumnIndex, pt.EndRowIndex, pt.EndColumnIndex)); } SLConditionalFormattingRule2010 cfr2010new; ConditionalFormattingRuleExtension cfrext; index = 1; int i; // the latest added rule takes first priority // And also we might delete (2010 icon set) so need to start at the end for (i = ConditionalFormatting.Rules.Count - 1; i >= 0; --i) { ConditionalFormatting.Rules[i].Priority = index; ++index; if (ConditionalFormatting.Rules[i].HasDifferentialFormat) { ConditionalFormatting.Rules[i].FormatId = (uint)this.SaveToStylesheetDifferentialFormat(ConditionalFormatting.Rules[i].DifferentialFormat.ToHash()); } bIsDataBar2010 = ConditionalFormatting.Rules[i].HasDataBar && ConditionalFormatting.Rules[i].DataBar.Is2010; bIsIconSet2010 = ConditionalFormatting.Rules[i].HasIconSet && ConditionalFormatting.Rules[i].IconSet.Is2010; // supposedly both cannot be true at the same time because each rule // can only have one rule (duh) at any one time, whether it's color scale, // data bar, icon set, top 10 or whatever rule there is. if (bIsDataBar2010 || bIsIconSet2010) { bIs2010 = true; cfr2010new = ConditionalFormatting.Rules[i].ToSLConditionalFormattingRule2010(); if (bIsDataBar2010) { // go read the Open XML specs on why it has to be null. // We null the priority so the extension fill color is not rendered. // Presumably, it uses the color in the normally placed data bar. // I don't know why Microsoft made it such that data bars exist in the // normal place and in the extension place. // Why not exist entirely in either the normal or extension like the // Excel 2010 icon sets? cfr2010new.Priority = null; // If I'm reading the specs correctly, if there's an extension, then // the normal placed data bar needs to have the min/max lengths as defaults. // AKA 10 and 90 percent respectively. // Hey reading that part of the specs takes longer than watching the Titanic // movie... and more convulated than a legal clause... ConditionalFormatting.Rules[i].DataBar.MinLength = 10; ConditionalFormatting.Rules[i].DataBar.MaxLength = 90; sGuid = string.Format("{{{0}}}", Guid.NewGuid().ToString().ToUpperInvariant()); cfr2010new.Id = sGuid; cfrext = new ConditionalFormattingRuleExtension(); cfrext.Uri = SLConstants.DataBarExtensionUri; cfrext.Append(new X14.Id(sGuid)); ConditionalFormatting.Rules[i].Extensions.Add(cfrext); } if (bIsIconSet2010) { sGuid = string.Format("{{{0}}}", Guid.NewGuid().ToString().ToUpperInvariant()); cfr2010new.Id = sGuid; // 2010 icon set exists entirely in the extension part ConditionalFormatting.Rules.RemoveAt(i); } // we insert at index 0 because we started from the end of the existing set // of rules. The new 2010 version will then follow the existing order (sort of). cf2010new.Rules.Insert(0, cfr2010new.Clone()); } } // in case there's only 2010 icon set rule if (ConditionalFormatting.Rules.Count > 0) { slws.ConditionalFormattings.Add(ConditionalFormatting.Clone()); } if (bIs2010) { slws.ConditionalFormattings2010.Add(cf2010new.Clone()); } } return result; }