internal SLSheet Clone()
{
SLSheet s = new SLSheet(this.Name, this.SheetId, this.Id, this.SheetType);
s.State = this.State;
return(s);
}
private void OpenExistingSpreadsheet(string SheetNameOnOpen)
{
xl = SpreadsheetDocument.Open(memstream, true);
wbp = xl.WorkbookPart;
IsNewSpreadsheet = false;
slwb = new SLWorkbook();
this.DocumentProperties = new SLDocumentProperties();
this.LoadDocumentProperties();
InitialiseAutoFitCache();
LoadBuiltInNumberingFormats();
InitialiseStylesheetWhatNots(SLThemeTypeValues.Office);
LoadSharedStringTable();
giWorksheetIdCounter = 0;
using (OpenXmlReader oxr = OpenXmlReader.Create(wbp))
{
SLWorkbookView wv;
Sheet s;
SLSheet sheet;
DefinedName dn;
SLDefinedName sldn;
while (oxr.Read())
{
if (oxr.ElementType == typeof(WorkbookView))
{
wv = new SLWorkbookView();
wv.FromWorkbookView((WorkbookView)oxr.LoadCurrentElement());
slwb.WorkbookViews.Add(wv);
}
else if (oxr.ElementType == typeof(Sheet))
{
s = (Sheet)oxr.LoadCurrentElement();
sheet = new SLSheet(s.Name.Value, s.SheetId.Value, s.Id.Value, SLSheetType.Unknown);
if (s.State != null) sheet.State = s.State.Value;
slwb.Sheets.Add(sheet);
if (sheet.SheetId > giWorksheetIdCounter)
{
giWorksheetIdCounter = (int)sheet.SheetId;
}
}
else if (oxr.ElementType == typeof(DefinedName))
{
dn = (DefinedName)oxr.LoadCurrentElement();
sldn = new SLDefinedName(dn.Name.Value);
sldn.FromDefinedName(dn);
slwb.DefinedNames.Add(sldn);
}
else if (oxr.ElementType == typeof(PivotCache))
{
// cache IDs supposed to be unique, so I'm not gonna check for the hash set
slwb.PivotTableCacheIds.Add(((PivotCache)oxr.LoadCurrentElement()).CacheId.Value);
}
}
}
if (wbp.Workbook.WorkbookProperties != null)
{
slwb.WorkbookProperties.FromWorkbookProperties(wbp.Workbook.WorkbookProperties);
}
if (wbp.CalculationChainPart != null)
{
int iCurrentSheetId = 0;
SLCalculationCell slcc = new SLCalculationCell(string.Empty);
CalculationCell cc;
using (OpenXmlReader oxr = OpenXmlReader.Create(wbp.CalculationChainPart))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CalculationCell))
{
cc = (CalculationCell)oxr.LoadCurrentElement();
if (cc.SheetId == null)
{
cc.SheetId = iCurrentSheetId;
}
else
{
if (cc.SheetId.Value != iCurrentSheetId)
iCurrentSheetId = cc.SheetId.Value;
}
slcc.FromCalculationCell(cc);
slwb.CalculationCells.Add(slcc.Clone());
}
}
}
}
// To determine the type of sheet. Do this before the part
// where the table and pivot table parts are set.
bool bFound = false;
string sRelID = string.Empty;
foreach (SLSheet sheet in slwb.Sheets)
{
bFound = false;
foreach (WorksheetPart wspFound in wbp.WorksheetParts)
{
sRelID = wbp.GetIdOfPart(wspFound);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.Worksheet;
bFound = true;
break;
}
}
if (!bFound)
{
foreach (ChartsheetPart csp in wbp.ChartsheetParts)
{
sRelID = wbp.GetIdOfPart(csp);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.Chartsheet;
bFound = true;
break;
}
}
}
if (!bFound)
{
foreach (DialogsheetPart dsp in wbp.DialogsheetParts)
{
sRelID = wbp.GetIdOfPart(dsp);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.DialogSheet;
bFound = true;
break;
}
}
}
if (!bFound)
{
foreach (MacroSheetPart msp in wbp.MacroSheetParts)
{
sRelID = wbp.GetIdOfPart(msp);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.Macrosheet;
bFound = true;
break;
}
}
}
}
WorksheetPart wsp;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.SheetType == SLSheetType.Worksheet)
{
wsp = (WorksheetPart)wbp.GetPartById(sheet.Id);
foreach (TableDefinitionPart tdp in wsp.TableDefinitionParts)
{
if (tdp.Table.Id != null && !slwb.TableIds.Contains(tdp.Table.Id.Value))
slwb.TableIds.Add(tdp.Table.Id.Value);
if (tdp.Table.Name != null && !slwb.TableNames.Contains(tdp.Table.Name.Value))
slwb.TableNames.Add(tdp.Table.Name.Value);
}
foreach (PivotTablePart ptp in wsp.PivotTableParts)
{
if (ptp.PivotTableDefinition.Name != null
&& !slwb.PivotTableNames.Contains(ptp.PivotTableDefinition.Name.Value))
slwb.PivotTableNames.Add(ptp.PivotTableDefinition.Name.Value);
// the cache ID should already be added, from the workbook part above.
// But we check again just to be sure. Cache IDs have to be unique throughout
// the workbook.
if (ptp.PivotTableDefinition.CacheId != null
&& !slwb.PivotTableCacheIds.Contains(ptp.PivotTableDefinition.CacheId.Value))
slwb.PivotTableCacheIds.Add(ptp.PivotTableDefinition.CacheId.Value);
}
}
}
string sWorksheetName = SLConstants.DefaultFirstSheetName;
int i = 1;
bool bCannotFind = true;
bool bIsLegit = true;
if (wbp.WorksheetParts.Count() == 0)
{
// no worksheets! Apparently an Excel file with only 1 dialog sheet is perfectly legit...
// come up with a legit worksheet name that's not already taken...
i = 1;
bCannotFind = true;
while (bCannotFind)
{
sWorksheetName = string.Format("Sheet{0}", i);
bIsLegit = true;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase))
{
bIsLegit = false;
break;
}
}
++i;
if (bIsLegit) bCannotFind = false;
}
AddWorksheet(sWorksheetName);
}
else
{
bFound = false;
// there's a given worksheet name
if (SheetNameOnOpen.Length > 0)
{
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(SheetNameOnOpen, StringComparison.OrdinalIgnoreCase)
&& sheet.SheetType == SLSheetType.Worksheet)
{
giSelectedWorksheetID = sheet.SheetId;
gsSelectedWorksheetName = sheet.Name;
gsSelectedWorksheetRelationshipID = sheet.Id;
bFound = true;
break;
}
}
}
else
{
// we try to get the "actively selected" worksheet already selected.
uint iActiveTab = 0;
if (slwb.WorkbookViews.Count > 0)
{
iActiveTab = slwb.WorkbookViews[0].ActiveTab;
}
// there should be at least *this* number of sheets (whether it's a worksheet
// chartsheet or whatnot).
if (slwb.Sheets.Count > iActiveTab
&& slwb.Sheets[(int)iActiveTab].SheetType == SLSheetType.Worksheet)
{
giSelectedWorksheetID = slwb.Sheets[(int)iActiveTab].SheetId;
gsSelectedWorksheetName = slwb.Sheets[(int)iActiveTab].Name;
gsSelectedWorksheetRelationshipID = slwb.Sheets[(int)iActiveTab].Id;
bFound = true;
}
}
if (!bFound)
{
// we get here either if there's no given worksheet name (bFound is still false),
// or there's a given worksheet name but corresponding values weren't found.
// The given worksheet name must be that of a worksheet. A chartsheet name is
// considered "invalid".
// Either way, we use the first available worksheet as the selected worksheet.
wsp = wbp.WorksheetParts.First();
sRelID = wbp.GetIdOfPart(wsp);
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
giSelectedWorksheetID = sheet.SheetId;
gsSelectedWorksheetName = sheet.Name;
gsSelectedWorksheetRelationshipID = sheet.Id;
bFound = true;
break;
}
}
}
if (bFound)
{
// A viable worksheet should be found by now. Otherwise, it's probably
// a corrupted spreadsheet...
LoadSelectedWorksheet();
IsNewWorksheet = false;
}
else
{
// why is it not found!?! The file is corrupted somehow... we'll try to recover
// by adding a new worksheet and selecting it. Same algorithm as above.
i = 1;
bCannotFind = true;
while (bCannotFind)
{
sWorksheetName = string.Format("Sheet{0}", i);
bIsLegit = true;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase))
{
bIsLegit = false;
break;
}
}
++i;
if (bIsLegit) bCannotFind = false;
}
AddWorksheet(sWorksheetName);
}
}
}
internal SLSheet Clone()
{
SLSheet s = new SLSheet(this.Name, this.SheetId, this.Id, this.SheetType);
s.State = this.State;
return s;
}
/// <summary>
/// Get the page settings of sheet.
/// </summary>
/// <param name="SheetName">The name of the sheet.</param>
/// <returns>An SLPageSettings object with the page settings of the specified sheet.</returns>
public SLPageSettings GetPageSettings(string SheetName)
{
if (SheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
return slws.PageSettings.Clone();
}
SLPageSettings ps = new SLPageSettings(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
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)
{
if (sheet.SheetType == SLSheetType.Worksheet)
{
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sheet.Id);
using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
{
bool bFound = false;
SLSheetView slsv;
while (oxr.Read())
{
if (oxr.ElementType == typeof(SheetProperties))
{
ps.SheetProperties.FromSheetProperties((SheetProperties)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(SheetView))
{
// if we find a sheet view with the default workbook view id,
// we'll just take that.
if (!bFound)
{
slsv = new SLSheetView();
slsv.FromSheetView((SheetView)oxr.LoadCurrentElement());
if (slsv.ShowFormulas) ps.bShowFormulas = slsv.ShowFormulas;
if (!slsv.ShowGridLines) ps.bShowGridLines = slsv.ShowGridLines;
if (!slsv.ShowRowColHeaders) ps.bShowRowColumnHeaders = slsv.ShowRowColHeaders;
if (!slsv.ShowRuler) ps.bShowRuler = slsv.ShowRuler;
if (slsv.View != SheetViewValues.Normal) ps.vView = slsv.View;
if (slsv.ZoomScale != 100) ps.ZoomScale = slsv.ZoomScale;
if (slsv.ZoomScaleNormal != 0) ps.ZoomScaleNormal = slsv.ZoomScaleNormal;
if (slsv.ZoomScalePageLayoutView != 0) ps.ZoomScalePageLayoutView = slsv.ZoomScalePageLayoutView;
if (slsv.WorkbookViewId == 0) bFound = true;
}
}
else if (oxr.ElementType == typeof(PrintOptions))
{
ps.ImportPrintOptions((PrintOptions)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(PageMargins))
{
ps.ImportPageMargins((PageMargins)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(PageSetup))
{
ps.ImportPageSetup((PageSetup)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(HeaderFooter))
{
ps.ImportHeaderFooter((HeaderFooter)oxr.LoadCurrentElement());
}
}
}
}
else if (sheet.SheetType == SLSheetType.Chartsheet)
{
ChartsheetPart csp = (ChartsheetPart)wbp.GetPartById(sheet.Id);
if (csp.Chartsheet.ChartSheetProperties != null)
{
ps.SheetProperties.FromChartSheetProperties(csp.Chartsheet.ChartSheetProperties);
}
if (csp.Chartsheet.PageMargins != null)
{
ps.ImportPageMargins(csp.Chartsheet.PageMargins);
}
if (csp.Chartsheet.ChartSheetPageSetup != null)
{
ps.ImportChartSheetPageSetup(csp.Chartsheet.ChartSheetPageSetup);
}
if (csp.Chartsheet.HeaderFooter != null)
{
ps.ImportHeaderFooter(csp.Chartsheet.HeaderFooter);
}
}
else if (sheet.SheetType == SLSheetType.DialogSheet)
{
DialogsheetPart dsp = (DialogsheetPart)wbp.GetPartById(sheet.Id);
if (dsp.DialogSheet.SheetProperties != null)
{
ps.SheetProperties.FromSheetProperties(dsp.DialogSheet.SheetProperties);
}
if (dsp.DialogSheet.PrintOptions != null)
{
ps.ImportPrintOptions(dsp.DialogSheet.PrintOptions);
}
if (dsp.DialogSheet.PageMargins != null)
{
ps.ImportPageMargins(dsp.DialogSheet.PageMargins);
}
if (dsp.DialogSheet.PageSetup != null)
{
ps.ImportPageSetup(dsp.DialogSheet.PageSetup);
}
if (dsp.DialogSheet.HeaderFooter != null)
{
ps.ImportHeaderFooter(dsp.DialogSheet.HeaderFooter);
}
}
else if (sheet.SheetType == SLSheetType.Macrosheet)
{
// not doing anything for macrosheets. What *are* macrosheets?
}
}
return ps;
}
/// <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>
/// Get the page settings of sheet.
/// </summary>
/// <param name="SheetName">The name of the sheet.</param>
/// <returns></returns>
public SLPageSettings GetPageSettings(string SheetName)
{
if (SheetName.Equals(gsSelectedWorksheetName, StringComparison.InvariantCultureIgnoreCase))
{
return slws.PageSettings.Clone();
}
SLPageSettings ps = new SLPageSettings(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
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.InvariantCultureIgnoreCase))
{
bSheetFound = true;
sheet = s.Clone();
break;
}
}
if (bSheetFound)
{
if (sheet.SheetType == SLSheetType.Worksheet)
{
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sheet.Id);
using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(SheetProperties))
{
ps.SheetProperties.FromSheetProperties((SheetProperties)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(PrintOptions))
{
ps.ImportPrintOptions((PrintOptions)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(PageMargins))
{
ps.ImportPageMargins((PageMargins)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(PageSetup))
{
ps.ImportPageSetup((PageSetup)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(HeaderFooter))
{
ps.ImportHeaderFooter((HeaderFooter)oxr.LoadCurrentElement());
}
}
}
}
else if (sheet.SheetType == SLSheetType.Chartsheet)
{
ChartsheetPart csp = (ChartsheetPart)wbp.GetPartById(sheet.Id);
if (csp.Chartsheet.ChartSheetProperties != null)
{
ps.SheetProperties.FromChartSheetProperties(csp.Chartsheet.ChartSheetProperties);
}
if (csp.Chartsheet.PageMargins != null)
{
ps.ImportPageMargins(csp.Chartsheet.PageMargins);
}
if (csp.Chartsheet.ChartSheetPageSetup != null)
{
ps.ImportChartSheetPageSetup(csp.Chartsheet.ChartSheetPageSetup);
}
if (csp.Chartsheet.HeaderFooter != null)
{
ps.ImportHeaderFooter(csp.Chartsheet.HeaderFooter);
}
}
else if (sheet.SheetType == SLSheetType.DialogSheet)
{
DialogsheetPart dsp = (DialogsheetPart)wbp.GetPartById(sheet.Id);
if (dsp.DialogSheet.SheetProperties != null)
{
ps.SheetProperties.FromSheetProperties(dsp.DialogSheet.SheetProperties);
}
if (dsp.DialogSheet.PrintOptions != null)
{
ps.ImportPrintOptions(dsp.DialogSheet.PrintOptions);
}
if (dsp.DialogSheet.PageMargins != null)
{
ps.ImportPageMargins(dsp.DialogSheet.PageMargins);
}
if (dsp.DialogSheet.PageSetup != null)
{
ps.ImportPageSetup(dsp.DialogSheet.PageSetup);
}
if (dsp.DialogSheet.HeaderFooter != null)
{
ps.ImportHeaderFooter(dsp.DialogSheet.HeaderFooter);
}
}
else if (sheet.SheetType == SLSheetType.Macrosheet)
{
// not doing anything for macrosheets. What *are* macrosheets?
}
}
return ps;
}
