/// <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
}
}
}
internal void SetPageSettingsSheetView(SLPageSettings ps)
{
if (ps.bShowFormulas != null)
{
// TODO: images and charts?
// Actually I don't feel like updating those...
if (ps.bShowFormulas.Value != slws.IsDoubleColumnWidth)
{
List<int> keys = slws.ColumnProperties.Keys.ToList<int>();
SLColumnProperties cp;
slws.IsDoubleColumnWidth = ps.bShowFormulas.Value;
if (ps.bShowFormulas.Value)
{
// have to test beforehand because setting the default column width
// assigns the HasDefaultColumnWidth property
if (!slws.SheetFormatProperties.HasDefaultColumnWidth)
{
slws.SheetFormatProperties.DefaultColumnWidth = 2 * slws.SheetFormatProperties.DefaultColumnWidth;
slws.SheetFormatProperties.HasDefaultColumnWidth = false;
}
else
{
slws.SheetFormatProperties.DefaultColumnWidth = 2 * slws.SheetFormatProperties.DefaultColumnWidth;
}
foreach (int colkey in keys)
{
cp = slws.ColumnProperties[colkey];
if (cp.HasWidth)
{
cp.Width = 2 * cp.Width;
slws.ColumnProperties[colkey] = cp.Clone();
}
}
}
else
{
// have to test beforehand because setting the default column width
// assigns the HasDefaultColumnWidth property
if (!slws.SheetFormatProperties.HasDefaultColumnWidth)
{
slws.SheetFormatProperties.DefaultColumnWidth = 0.5 * slws.SheetFormatProperties.DefaultColumnWidth;
slws.SheetFormatProperties.HasDefaultColumnWidth = false;
}
else
{
slws.SheetFormatProperties.DefaultColumnWidth = 0.5 * slws.SheetFormatProperties.DefaultColumnWidth;
}
foreach (int colkey in keys)
{
cp = slws.ColumnProperties[colkey];
if (cp.HasWidth)
{
cp.Width = 0.5 * cp.Width;
slws.ColumnProperties[colkey] = cp.Clone();
}
}
}
}
}
if (ps.HasSheetView)
{
if (slws.SheetViews.Count > 0)
{
bool bFound = false;
foreach (SLSheetView sv in slws.SheetViews)
{
if (sv.WorkbookViewId == 0)
{
bFound = true;
if (ps.bShowFormulas != null) sv.ShowFormulas = ps.bShowFormulas.Value;
if (ps.bShowGridLines != null) sv.ShowGridLines = ps.bShowGridLines.Value;
if (ps.bShowRowColumnHeaders != null) sv.ShowRowColHeaders = ps.bShowRowColumnHeaders.Value;
if (ps.bShowRuler != null) sv.ShowRuler = ps.bShowRuler.Value;
if (ps.vView != null) sv.View = ps.vView.Value;
if (ps.iZoomScale != null) sv.ZoomScale = ps.iZoomScale.Value;
if (ps.iZoomScaleNormal != null) sv.ZoomScaleNormal = ps.iZoomScaleNormal.Value;
if (ps.iZoomScalePageLayoutView != null) sv.ZoomScalePageLayoutView = ps.iZoomScalePageLayoutView.Value;
}
}
if (!bFound)
{
slws.SheetViews.Add(ps.ExportSLSheetView());
}
}
else
{
slws.SheetViews.Add(ps.ExportSLSheetView());
}
}
}
internal void SetPageSettingsSheetView(SLPageSettings ps)
{
if (ps.HasSheetView)
{
if (slws.SheetViews.Count > 0)
{
bool bFound = false;
foreach (SLSheetView sv in slws.SheetViews)
{
if (sv.WorkbookViewId == 0)
{
bFound = true;
if (ps.bShowGridLines != null) sv.ShowGridLines = ps.bShowGridLines.Value;
if (ps.bShowRowColumnHeaders != null) sv.ShowRowColHeaders = ps.bShowRowColumnHeaders.Value;
if (ps.bShowRuler != null) sv.ShowRuler = ps.bShowRuler.Value;
if (ps.vView != null) sv.View = ps.vView.Value;
if (ps.iZoomScale != null) sv.ZoomScale = ps.iZoomScale.Value;
if (ps.iZoomScaleNormal != null) sv.ZoomScaleNormal = ps.iZoomScaleNormal.Value;
if (ps.iZoomScalePageLayoutView != null) sv.ZoomScalePageLayoutView = ps.iZoomScalePageLayoutView.Value;
}
}
if (!bFound)
{
slws.SheetViews.Add(ps.ExportSLSheetView());
}
}
else
{
slws.SheetViews.Add(ps.ExportSLSheetView());
}
}
}