/// <summary> /// Set page settings to a sheet. /// </summary> /// <param name="PageSettings">An SLPageSettings object with the properties already set.</param> /// <param name="SheetName">The name of the sheet.</param> public void SetPageSettings(SLPageSettings PageSettings, string SheetName) { if (SheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase)) { slws.PageSettings = PageSettings.Clone(); this.SetPageSettingsSheetView(PageSettings); return; } // we're not going to double column widths for non-currently-selected worksheets // when show formulas is true... // Too much work for the rare occurence that it happens... bool bSheetFound = false; SLSheet sheet = new SLSheet(string.Empty, 0, string.Empty, SLSheetType.Unknown); foreach (SLSheet s in slwb.Sheets) { if (s.Name.Equals(SheetName, StringComparison.OrdinalIgnoreCase)) { bSheetFound = true; sheet = s.Clone(); break; } } if (bSheetFound) { bool bFound = false; OpenXmlElement oxe; if (sheet.SheetType == SLSheetType.Worksheet) { #region Worksheet WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sheet.Id); if (PageSettings.HasSheetProperties) { wsp.Worksheet.SheetProperties = PageSettings.SheetProperties.ToSheetProperties(); } else { wsp.Worksheet.SheetProperties = null; } #region SheetViews if (PageSettings.HasSheetView) { if (wsp.Worksheet.SheetViews != null) { bool bSheetViewFound = false; foreach (SheetView sv in wsp.Worksheet.SheetViews) { if (sv.WorkbookViewId == 0) { if (PageSettings.bShowFormulas != null) sv.ShowFormulas = PageSettings.bShowFormulas.Value; if (PageSettings.bShowGridLines != null) sv.ShowGridLines = PageSettings.bShowGridLines.Value; if (PageSettings.bShowRowColumnHeaders != null) sv.ShowRowColHeaders = PageSettings.bShowRowColumnHeaders.Value; if (PageSettings.bShowRuler != null) sv.ShowRuler = PageSettings.bShowRuler.Value; if (PageSettings.vView != null) sv.View = PageSettings.vView.Value; if (PageSettings.iZoomScale != null) sv.ZoomScale = PageSettings.iZoomScale.Value; if (PageSettings.iZoomScaleNormal != null) sv.ZoomScaleNormal = PageSettings.iZoomScaleNormal.Value; if (PageSettings.iZoomScalePageLayoutView != null) sv.ZoomScalePageLayoutView = PageSettings.iZoomScalePageLayoutView.Value; } } if (!bSheetViewFound) { wsp.Worksheet.SheetViews.Append(PageSettings.ExportSLSheetView().ToSheetView()); } } else { wsp.Worksheet.SheetViews = new SheetViews(); wsp.Worksheet.SheetViews.Append(PageSettings.ExportSLSheetView().ToSheetView()); } } #endregion #region PrintOptions if (PageSettings.HasPrintOptions) { if (wsp.Worksheet.Elements<PrintOptions>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<PrintOptions>(); } bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(PageSettings.ExportPrintOptions(), oxe); } else { wsp.Worksheet.PrependChild(PageSettings.ExportPrintOptions()); } } #endregion #region PageMargins if (PageSettings.HasPageMargins) { if (wsp.Worksheet.Elements<PageMargins>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<PageMargins>(); } bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(PageSettings.ExportPageMargins(), oxe); } else { wsp.Worksheet.PrependChild(PageSettings.ExportPageMargins()); } } #endregion #region PageSetup if (PageSettings.HasPageSetup) { if (wsp.Worksheet.Elements<PageSetup>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<PageSetup>(); } bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(PageSettings.ExportPageSetup(), oxe); } else { wsp.Worksheet.PrependChild(PageSettings.ExportPageSetup()); } } #endregion #region HeaderFooter if (PageSettings.HasHeaderFooter) { if (wsp.Worksheet.Elements<HeaderFooter>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<HeaderFooter>(); } bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(PageSettings.ExportHeaderFooter(), oxe); } else { wsp.Worksheet.PrependChild(PageSettings.ExportHeaderFooter()); } } #endregion wsp.Worksheet.Save(); #endregion } else if (sheet.SheetType == SLSheetType.Chartsheet) { #region Chartsheet ChartsheetPart csp = (ChartsheetPart)wbp.GetPartById(sheet.Id); if (PageSettings.HasChartSheetProperties) { csp.Chartsheet.ChartSheetProperties = PageSettings.SheetProperties.ToChartSheetProperties(); } else { csp.Chartsheet.ChartSheetProperties = null; } if (PageSettings.HasPageMargins) { csp.Chartsheet.PageMargins = PageSettings.ExportPageMargins(); } else { csp.Chartsheet.PageMargins = null; } if (PageSettings.HasChartSheetPageSetup) { csp.Chartsheet.ChartSheetPageSetup = PageSettings.ExportChartSheetPageSetup(); } else { csp.Chartsheet.ChartSheetPageSetup = null; } if (PageSettings.HasHeaderFooter) { csp.Chartsheet.HeaderFooter = PageSettings.ExportHeaderFooter(); } else { csp.Chartsheet.HeaderFooter = null; } csp.Chartsheet.Save(); #endregion } else if (sheet.SheetType == SLSheetType.DialogSheet) { #region DialogSheet DialogsheetPart dsp = (DialogsheetPart)wbp.GetPartById(sheet.Id); if (PageSettings.HasSheetProperties) { dsp.DialogSheet.SheetProperties = PageSettings.SheetProperties.ToSheetProperties(); } else { dsp.DialogSheet.SheetProperties = null; } if (PageSettings.HasPrintOptions) { dsp.DialogSheet.PrintOptions = PageSettings.ExportPrintOptions(); } else { dsp.DialogSheet.PrintOptions = null; } if (PageSettings.HasPageMargins) { dsp.DialogSheet.PageMargins = PageSettings.ExportPageMargins(); } else { dsp.DialogSheet.PageMargins = null; } if (PageSettings.HasPageSetup) { dsp.DialogSheet.PageSetup = PageSettings.ExportPageSetup(); } else { dsp.DialogSheet.PageSetup = null; } if (PageSettings.HasHeaderFooter) { dsp.DialogSheet.HeaderFooter = PageSettings.ExportHeaderFooter(); } else { dsp.DialogSheet.HeaderFooter = null; } dsp.DialogSheet.Save(); #endregion } else if (sheet.SheetType == SLSheetType.Macrosheet) { #region Macrosheet // don't care about macrosheets for now. What *are* macrosheets? #endregion } } }
/// <summary> /// Set page settings to the currently selected worksheet. /// </summary> /// <param name="PageSettings">An SLPageSettings object with the properties already set.</param> public void SetPageSettings(SLPageSettings PageSettings) { slws.PageSettings = PageSettings.Clone(); this.SetPageSettingsSheetView(PageSettings); }