internal SLSheetView Clone()
{
SLSheetView sv = new SLSheetView();
sv.Pane = this.Pane.Clone();
sv.Selections = new List <SLSelection>();
foreach (SLSelection sel in this.Selections)
{
sv.Selections.Add(sel.Clone());
}
sv.PivotSelections = new List <PivotSelection>();
foreach (PivotSelection psel in this.PivotSelections)
{
sv.PivotSelections.Add((PivotSelection)psel.CloneNode(true));
}
sv.WindowProtection = this.WindowProtection;
sv.ShowFormulas = this.ShowFormulas;
sv.ShowGridLines = this.ShowGridLines;
sv.ShowRowColHeaders = this.ShowRowColHeaders;
sv.ShowZeros = this.ShowZeros;
sv.RightToLeft = this.RightToLeft;
sv.TabSelected = this.TabSelected;
sv.ShowRuler = this.ShowRuler;
sv.ShowOutlineSymbols = this.ShowOutlineSymbols;
sv.DefaultGridColor = this.DefaultGridColor;
sv.ShowWhiteSpace = this.ShowWhiteSpace;
sv.View = this.View;
sv.TopLeftCell = this.TopLeftCell;
sv.ColorId = this.ColorId;
sv.iZoomScale = this.iZoomScale;
sv.iZoomScaleNormal = this.iZoomScaleNormal;
sv.iZoomScaleSheetLayoutView = this.iZoomScaleSheetLayoutView;
sv.iZoomScalePageLayoutView = this.iZoomScalePageLayoutView;
sv.WorkbookViewId = this.WorkbookViewId;
return(sv);
}
internal SLSheetView Clone()
{
SLSheetView sv = new SLSheetView();
sv.Pane = this.Pane.Clone();
sv.Selections = new List<SLSelection>();
foreach (SLSelection sel in this.Selections)
{
sv.Selections.Add(sel.Clone());
}
sv.PivotSelections = new List<PivotSelection>();
foreach (PivotSelection psel in this.PivotSelections)
{
sv.PivotSelections.Add((PivotSelection)psel.CloneNode(true));
}
sv.WindowProtection = this.WindowProtection;
sv.ShowFormulas = this.ShowFormulas;
sv.ShowGridLines = this.ShowGridLines;
sv.ShowRowColHeaders = this.ShowRowColHeaders;
sv.ShowZeros = this.ShowZeros;
sv.RightToLeft = this.RightToLeft;
sv.TabSelected = this.TabSelected;
sv.ShowRuler = this.ShowRuler;
sv.ShowOutlineSymbols = this.ShowOutlineSymbols;
sv.DefaultGridColor = this.DefaultGridColor;
sv.ShowWhiteSpace = this.ShowWhiteSpace;
sv.View = this.View;
sv.TopLeftCell = this.TopLeftCell;
sv.ColorId = this.ColorId;
sv.iZoomScale = this.iZoomScale;
sv.iZoomScaleNormal = this.iZoomScaleNormal;
sv.iZoomScaleSheetLayoutView = this.iZoomScaleSheetLayoutView;
sv.iZoomScalePageLayoutView = this.iZoomScalePageLayoutView;
sv.WorkbookViewId = this.WorkbookViewId;
return sv;
}
internal SLSheetView ExportSLSheetView()
{
SLSheetView sv = new SLSheetView();
if (this.bShowGridLines != null) sv.ShowGridLines = this.bShowGridLines.Value;
if (this.bShowRowColumnHeaders != null) sv.ShowRowColHeaders = this.bShowRowColumnHeaders.Value;
if (this.bShowRuler != null) sv.ShowRuler = this.bShowRuler.Value;
if (this.vView != null) sv.View = this.vView.Value;
if (this.iZoomScale != null) sv.ZoomScale = this.iZoomScale.Value;
if (this.iZoomScaleNormal != null) sv.ZoomScaleNormal = this.iZoomScaleNormal.Value;
if (this.iZoomScalePageLayoutView != null) sv.ZoomScalePageLayoutView = this.iZoomScalePageLayoutView.Value;
return sv;
}
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>
/// 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>
/// Freeze panes in the worksheet (for the first workbook view). Will do nothing if both parameters are zero (because there's nothing to freeze). Will also do nothing if either of the parameters is equal to their respective limits (maximum number of rows, or maximum number of columns).
/// </summary>
/// <param name="NumberOfTopMostRows">Number of top-most rows to keep in place.</param>
/// <param name="NumberOfLeftMostColumns">Number of left-most columns to keep in place.</param>
public void FreezePanes(int NumberOfTopMostRows, int NumberOfLeftMostColumns)
{
if (NumberOfTopMostRows == 0 && NumberOfLeftMostColumns == 0) return;
// no point if they're negative, right?
if (NumberOfTopMostRows < 0 || NumberOfLeftMostColumns < 0) return;
// the "greater than" part ensures correct limit checks as well.
if (NumberOfTopMostRows >= SLConstants.RowLimit || NumberOfLeftMostColumns >= SLConstants.ColumnLimit) return;
SLSheetView slsv = new SLSheetView();
// will use the first workbook view by default
slsv.WorkbookViewId = 0;
if (NumberOfLeftMostColumns > 0) slsv.Pane.HorizontalSplit = NumberOfLeftMostColumns;
if (NumberOfTopMostRows > 0) slsv.Pane.VerticalSplit = NumberOfTopMostRows;
int iRowIndex = NumberOfTopMostRows + 1;
int iColumnIndex = NumberOfLeftMostColumns + 1;
SLSelection sel;
slsv.Pane.TopLeftCell = SLTool.ToCellReference(iRowIndex, iColumnIndex);
if (NumberOfLeftMostColumns == 0)
{
slsv.Pane.ActivePane = PaneValues.BottomLeft;
slsv.Pane.State = PaneStateValues.Frozen;
slsv.Pane.VerticalSplit = NumberOfTopMostRows;
sel = new SLSelection();
sel.Pane = PaneValues.BottomLeft;
sel.ActiveCell = SLTool.ToCellReference(iRowIndex, iColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(iRowIndex, iColumnIndex, iRowIndex, iColumnIndex));
slsv.Selections.Add(sel);
}
else if (NumberOfTopMostRows == 0)
{
slsv.Pane.ActivePane = PaneValues.TopRight;
slsv.Pane.State = PaneStateValues.Frozen;
slsv.Pane.HorizontalSplit = NumberOfLeftMostColumns;
sel = new SLSelection();
sel.Pane = PaneValues.TopRight;
sel.ActiveCell = SLTool.ToCellReference(iRowIndex, iColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(iRowIndex, iColumnIndex, iRowIndex, iColumnIndex));
slsv.Selections.Add(sel);
}
else
{
slsv.Pane.ActivePane = PaneValues.BottomRight;
slsv.Pane.State = PaneStateValues.Frozen;
sel = new SLSelection();
sel.Pane = PaneValues.TopRight;
sel.ActiveCell = SLTool.ToCellReference(1, iColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(1, iColumnIndex, 1, iColumnIndex));
slsv.Selections.Add(sel);
sel = new SLSelection();
sel.Pane = PaneValues.BottomLeft;
sel.ActiveCell = SLTool.ToCellReference(iRowIndex, 1);
sel.SequenceOfReferences.Add(new SLCellPointRange(iRowIndex, 1, iRowIndex, 1));
slsv.Selections.Add(sel);
sel = new SLSelection();
sel.Pane = PaneValues.BottomRight;
if (slws.ActiveCell.RowIndex == 1 && slws.ActiveCell.ColumnIndex == 1)
{
sel.ActiveCell = SLTool.ToCellReference(iRowIndex, iColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(iRowIndex, iColumnIndex, iRowIndex, iColumnIndex));
}
else
{
sel.ActiveCell = SLTool.ToCellReference(slws.ActiveCell.RowIndex, slws.ActiveCell.ColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(slws.ActiveCell.RowIndex, slws.ActiveCell.ColumnIndex, slws.ActiveCell.RowIndex, slws.ActiveCell.ColumnIndex));
}
slsv.Selections.Add(sel);
}
bool bFound = false;
foreach (SLSheetView sv in slws.SheetViews)
{
if (sv.WorkbookViewId == 0)
{
bFound = true;
sv.Pane = slsv.Pane.Clone();
sv.Selections = new List<SLSelection>();
foreach (SLSelection slsel in slsv.Selections)
{
sv.Selections.Add(slsel.Clone());
}
sv.PivotSelections = new List<PivotSelection>();
}
}
if (!bFound)
{
slws.SheetViews.Add(slsv);
}
}
/// <summary>
/// Split panes in the worksheet (for the first workbook view). Will do nothing if both number of rows and number of columns are zero (because there's nothing to split). Will also do nothing if either is equal to their respective limits (maximum number of rows, or maximum number of columns).
/// The underlying engine tries to guess the individual row heights and column widths. Then the horizontal and vertical split lines are placed based on the guesses.
/// Forcing the row and column dimensions to fit the split lines might mean the worksheet looking oddly sized.
/// </summary>
/// <param name="NumberOfRows">Number of top-most rows above the horizontal split line.</param>
/// <param name="NumberOfColumns">Number of left-most columns left of the vertical split line.</param>
/// <param name="ShowRowColumnHeadings">True if the row and column headings are shown. False otherwise.</param>
/// <param name="VerticalOffsetInPoints">This is more useful when row and column headings are shown. This will be the height of the column heading in points.</param>
/// <param name="HorizontalOffsetInPoints">This is more useful when row and column headings are shown. This will be the width of the row heading in points.</param>
/// <param name="ForceCustomRowColumnDimensions">Set true to force the worksheet's row height and column width to fit the given horizontal and vertical splits. False otherwise.</param>
public void SplitPanes(int NumberOfRows, int NumberOfColumns, bool ShowRowColumnHeadings, double VerticalOffsetInPoints, double HorizontalOffsetInPoints, bool ForceCustomRowColumnDimensions)
{
// At Calibri 11pt as minor font at 96 DPI,
// suggested vertical offset is 15 points ~= 20 pixels high
// suggested horizontal offset is 19.5 points ~= 26 pixels wide
// At Calibri 11pt as minor font at 120 DPI,
// suggested vertical offset is 14.4 points ~= 24 pixels high
// suggested horizontal offset is 20.4 points ~= 34 pixels wide
if (NumberOfRows == 0 && NumberOfColumns == 0) return;
// no point if they're negative, right?
if (NumberOfRows < 0 || NumberOfColumns < 0) return;
// the "greater than" part ensures correct limit checks as well.
if (NumberOfRows >= SLConstants.RowLimit || NumberOfColumns >= SLConstants.ColumnLimit) return;
// we don't care for negative offsets...
if (VerticalOffsetInPoints < 0) VerticalOffsetInPoints = 0;
if (HorizontalOffsetInPoints < 0) HorizontalOffsetInPoints = 0;
slws.ForceCustomRowColumnDimensionsSplitting = ForceCustomRowColumnDimensions;
long lWidth = 0, lHeight = 0;
int i = 0;
SLSheetView slsv = new SLSheetView();
// will use the first workbook view by default
slsv.WorkbookViewId = 0;
slsv.ShowRowColHeaders = ShowRowColumnHeadings;
if (NumberOfColumns > 0)
{
SLColumnProperties cp;
for (i = 1; i <= NumberOfColumns; ++i)
{
if (slws.ColumnProperties.ContainsKey(i))
{
cp = slws.ColumnProperties[i];
if (cp.HasWidth)
{
lWidth += cp.WidthInEMU;
}
else
{
lWidth += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
else
{
lWidth += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
}
// split lengths are in twentieth's of a point
if (ShowRowColumnHeadings)
{
lWidth = (long)Math.Round(20.0 * (((double)lWidth / (double)SLConstants.PointToEMU) + HorizontalOffsetInPoints));
}
else
{
lWidth = (long)Math.Round(20.0 * ((double)lWidth / (double)SLConstants.PointToEMU));
}
slsv.Pane.HorizontalSplit = lWidth;
if (NumberOfRows > 0)
{
SLRowProperties rp;
for (i = 1; i <= NumberOfRows; ++i)
{
if (slws.RowProperties.ContainsKey(i))
{
rp = slws.RowProperties[i];
if (rp.HasHeight)
{
lHeight += rp.HeightInEMU;
}
else
{
lHeight += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
else
{
lHeight += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
}
// split lengths are in twentieth's of a point
if (ShowRowColumnHeadings)
{
lHeight = (long)Math.Round(20.0 * (((double)lHeight / (double)SLConstants.PointToEMU) + VerticalOffsetInPoints));
}
else
{
lHeight = (long)Math.Round(20.0 * ((double)lHeight / (double)SLConstants.PointToEMU));
}
slsv.Pane.VerticalSplit = lHeight;
int iRowIndex = NumberOfRows + 1;
int iColumnIndex = NumberOfColumns + 1;
slsv.Pane.TopLeftCell = SLTool.ToCellReference(iRowIndex, iColumnIndex);
slsv.Pane.ActivePane = PaneValues.BottomRight;
slsv.Pane.State = PaneStateValues.Split;
SLSelection sel;
if (slws.ActiveCell.RowIndex < iRowIndex)
{
if (slws.ActiveCell.ColumnIndex < iColumnIndex)
{
// it seems that if the active cell is A1 Excel don't render the selection XML tag.
if (slws.ActiveCell.RowIndex != 1 || slws.ActiveCell.ColumnIndex != 1)
{
slsv.Pane.ActivePane = PaneValues.TopLeft;
sel = new SLSelection();
sel.ActiveCell = SLTool.ToCellReference(slws.ActiveCell.RowIndex, slws.ActiveCell.ColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(slws.ActiveCell.RowIndex, slws.ActiveCell.ColumnIndex, slws.ActiveCell.RowIndex, slws.ActiveCell.ColumnIndex));
slsv.Selections.Add(sel);
}
}
else
{
slsv.Pane.ActivePane = PaneValues.TopRight;
}
}
else
{
if (slws.ActiveCell.ColumnIndex < iColumnIndex)
{
slsv.Pane.ActivePane = PaneValues.BottomLeft;
}
else
{
slsv.Pane.ActivePane = PaneValues.BottomRight;
}
}
sel = new SLSelection();
sel.Pane = PaneValues.TopRight;
sel.ActiveCell = SLTool.ToCellReference(1, iColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(1, iColumnIndex, 1, iColumnIndex));
slsv.Selections.Add(sel);
sel = new SLSelection();
sel.Pane = PaneValues.BottomLeft;
sel.ActiveCell = SLTool.ToCellReference(iRowIndex, 1);
sel.SequenceOfReferences.Add(new SLCellPointRange(iRowIndex, 1, iRowIndex, 1));
slsv.Selections.Add(sel);
sel = new SLSelection();
sel.Pane = PaneValues.BottomRight;
sel.ActiveCell = SLTool.ToCellReference(iRowIndex, iColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(iRowIndex, iColumnIndex, iRowIndex, iColumnIndex));
slsv.Selections.Add(sel);
bool bFound = false;
foreach (SLSheetView sv in slws.SheetViews)
{
if (sv.WorkbookViewId == 0)
{
bFound = true;
sv.Pane = slsv.Pane.Clone();
sv.Selections = new List<SLSelection>();
foreach (SLSelection slsel in slsv.Selections)
{
sv.Selections.Add(slsel.Clone());
}
sv.PivotSelections = new List<PivotSelection>();
}
}
if (!bFound)
{
slws.SheetViews.Add(slsv);
}
}
internal SLSheetView Clone()
{
SLSheetView sv = new SLSheetView();
sv.Pane = this.Pane.Clone();
sv.SelectionAndPivotSelectionXml = this.SelectionAndPivotSelectionXml;
sv.WindowProtection = this.WindowProtection;
sv.ShowFormulas = this.ShowFormulas;
sv.ShowGridLines = this.ShowGridLines;
sv.ShowRowColHeaders = this.ShowRowColHeaders;
sv.ShowZeros = this.ShowZeros;
sv.RightToLeft = this.RightToLeft;
sv.TabSelected = this.TabSelected;
sv.ShowRuler = this.ShowRuler;
sv.ShowOutlineSymbols = this.ShowOutlineSymbols;
sv.DefaultGridColor = this.DefaultGridColor;
sv.ShowWhiteSpace = this.ShowWhiteSpace;
sv.View = this.View;
sv.TopLeftCell = this.TopLeftCell;
sv.ColorId = this.ColorId;
sv.iZoomScale = this.iZoomScale;
sv.iZoomScaleNormal = this.iZoomScaleNormal;
sv.iZoomScaleSheetLayoutView = this.iZoomScaleSheetLayoutView;
sv.iZoomScalePageLayoutView = this.iZoomScalePageLayoutView;
sv.WorkbookViewId = this.WorkbookViewId;
return sv;
}
/// <summary>
/// Freeze panes in the worksheet (for the first workbook view). Will do nothing if both parameters are zero (because there's nothing to freeze). Will also do nothing if either of the parameters is equal to their respective limits (maximum number of rows, or maximum number of columns).
/// </summary>
/// <param name="NumberOfTopMostRows">Number of top-most rows to keep in place.</param>
/// <param name="NumberOfLeftMostColumns">Number of left-most columns to keep in place.</param>
public void FreezePanes(int NumberOfTopMostRows, int NumberOfLeftMostColumns)
{
if (NumberOfTopMostRows == 0 && NumberOfLeftMostColumns == 0) return;
// no point if they're negative, right?
if (NumberOfTopMostRows < 0 || NumberOfLeftMostColumns < 0) return;
// the "greater than" part ensures correct limit checks as well.
if (NumberOfTopMostRows >= SLConstants.RowLimit || NumberOfLeftMostColumns >= SLConstants.ColumnLimit) return;
SLSheetView slsv = new SLSheetView();
// will use the first workbook view by default
slsv.WorkbookViewId = 0;
if (NumberOfLeftMostColumns > 0) slsv.Pane.HorizontalSplit = NumberOfLeftMostColumns;
if (NumberOfTopMostRows > 0) slsv.Pane.VerticalSplit = NumberOfTopMostRows;
slsv.Pane.TopLeftCell = SLTool.ToCellReference(NumberOfTopMostRows + 1, NumberOfLeftMostColumns + 1);
slsv.Pane.ActivePane = PaneValues.BottomRight;
slsv.Pane.State = PaneStateValues.Frozen;
bool bFound = false;
foreach (SLSheetView sv in slws.SheetViews)
{
if (sv.WorkbookViewId == 0)
{
bFound = true;
sv.Pane = slsv.Pane;
}
}
if (!bFound)
{
slws.SheetViews.Add(slsv);
}
}
/// <summary>
/// Set the active cell for the currently selected worksheet.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool SetActiveCell(int RowIndex, int ColumnIndex)
{
if (RowIndex < 1 || RowIndex > SLConstants.RowLimit || ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit)
return false;
slws.ActiveCell = new SLCellPoint(RowIndex, ColumnIndex);
int i, j;
SLSheetView sv;
SLSelection sel;
if (slws.SheetViews.Count == 0)
{
// if it's A1, I'm not going to do anything. It's the default!
if (RowIndex != 1 || ColumnIndex != 1)
{
sv = new SLSheetView();
sv.WorkbookViewId = 0;
sel = new SLSelection();
sel.ActiveCell = SLTool.ToCellReference(RowIndex, ColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex));
sv.Selections.Add(sel);
slws.SheetViews.Add(sv);
}
}
else
{
bool bFound = false;
PaneValues vActivePane = PaneValues.TopLeft;
for (i = 0; i < slws.SheetViews.Count; ++i)
{
if (slws.SheetViews[i].WorkbookViewId == 0)
{
bFound = true;
if (slws.SheetViews[i].Selections.Count == 0)
{
if (RowIndex != 1 || ColumnIndex != 1)
{
sel = new SLSelection();
sel.ActiveCell = SLTool.ToCellReference(RowIndex, ColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex));
slws.SheetViews[i].Selections.Add(sel);
}
}
else
{
// else there are selections. We'll need to look for the selection that
// has TopLeft as the pane. Not sure if the Pane class is tightly connected
// to the Selection classes, so we're going to check separately.
// It appears that the Pane class exists only if the worksheet is split or
// frozen, but I might be wrong... And when the Pane class exists, then
// there seems to be 3 or 4 Selection classes. There seems to be 4 Selection
// classes only when the worksheet is split and the active cell is in the
// top left corner.
if (slws.SheetViews[i].HasPane)
{
vActivePane = slws.SheetViews[i].Pane.ActivePane;
for (j = slws.SheetViews[i].Selections.Count - 1; j >= 0; --j)
{
if (slws.SheetViews[i].Selections[j].Pane == vActivePane)
{
slws.SheetViews[i].Selections[j].ActiveCell = SLTool.ToCellReference(RowIndex, ColumnIndex);
slws.SheetViews[i].Selections[j].SequenceOfReferences.Clear();
slws.SheetViews[i].Selections[j].SequenceOfReferences.Add(new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex));
}
}
}
else
{
for (j = slws.SheetViews[i].Selections.Count - 1; j >= 0; --j)
{
if (slws.SheetViews[i].Selections[j].Pane == PaneValues.TopLeft)
{
if (RowIndex == 1 && ColumnIndex == 1)
{
slws.SheetViews[i].Selections.RemoveAt(j);
}
else
{
slws.SheetViews[i].Selections[j].ActiveCell = SLTool.ToCellReference(RowIndex, ColumnIndex);
slws.SheetViews[i].Selections[j].SequenceOfReferences.Clear();
slws.SheetViews[i].Selections[j].SequenceOfReferences.Add(new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex));
}
}
}
}
}
break;
}
}
if (!bFound)
{
sv = new SLSheetView();
sv.WorkbookViewId = 0;
sel = new SLSelection();
sel.ActiveCell = SLTool.ToCellReference(RowIndex, ColumnIndex);
sel.SequenceOfReferences.Add(new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex));
sv.Selections.Add(sel);
slws.SheetViews.Add(sv);
}
}
return true;
}