internal SLSelection Clone()
{
SLSelection s = new SLSelection();
s.Pane = this.Pane;
s.ActiveCell = this.ActiveCell;
s.ActiveCellId = this.ActiveCellId;
s.SequenceOfReferences = new List<SLCellPointRange>();
foreach (SLCellPointRange pt in this.SequenceOfReferences)
{
s.SequenceOfReferences.Add(new SLCellPointRange(pt.StartRowIndex, pt.StartColumnIndex, pt.EndRowIndex, pt.EndColumnIndex));
}
return s;
}
internal SLSelection Clone()
{
SLSelection s = new SLSelection();
s.Pane = this.Pane;
s.ActiveCell = this.ActiveCell;
s.ActiveCellId = this.ActiveCellId;
s.SequenceOfReferences = new List <SLCellPointRange>();
foreach (SLCellPointRange pt in this.SequenceOfReferences)
{
s.SequenceOfReferences.Add(new SLCellPointRange(pt.StartRowIndex, pt.StartColumnIndex, pt.EndRowIndex, pt.EndColumnIndex));
}
return(s);
}
internal void FromSheetView(SheetView sv)
{
this.SetAllNull();
if (sv.WindowProtection != null) this.WindowProtection = sv.WindowProtection.Value;
if (sv.ShowFormulas != null) this.ShowFormulas = sv.ShowFormulas.Value;
if (sv.ShowGridLines != null) this.ShowGridLines = sv.ShowGridLines.Value;
if (sv.ShowRowColHeaders != null) this.ShowRowColHeaders = sv.ShowRowColHeaders.Value;
if (sv.ShowZeros != null) this.ShowZeros = sv.ShowZeros.Value;
if (sv.RightToLeft != null) this.RightToLeft = sv.RightToLeft.Value;
if (sv.TabSelected != null) this.TabSelected = sv.TabSelected.Value;
if (sv.ShowRuler != null) this.ShowRuler = sv.ShowRuler.Value;
if (sv.ShowOutlineSymbols != null) this.ShowOutlineSymbols = sv.ShowOutlineSymbols.Value;
if (sv.DefaultGridColor != null) this.DefaultGridColor = sv.DefaultGridColor.Value;
if (sv.ShowWhiteSpace != null) this.ShowWhiteSpace = sv.ShowWhiteSpace.Value;
if (sv.View != null) this.View = sv.View.Value;
if (sv.TopLeftCell != null) this.TopLeftCell = sv.TopLeftCell.Value;
if (sv.ColorId != null) this.ColorId = sv.ColorId.Value;
if (sv.ZoomScale != null) this.ZoomScale = sv.ZoomScale.Value;
if (sv.ZoomScaleNormal != null) this.ZoomScaleNormal = sv.ZoomScaleNormal.Value;
if (sv.ZoomScaleSheetLayoutView != null) this.ZoomScaleSheetLayoutView = sv.ZoomScaleSheetLayoutView.Value;
if (sv.ZoomScalePageLayoutView != null) this.ZoomScalePageLayoutView = sv.ZoomScalePageLayoutView.Value;
// required attribute but we'll use 0 as the default in case something terrible happens.
if (sv.WorkbookViewId != null) this.WorkbookViewId = sv.WorkbookViewId.Value;
else this.WorkbookViewId = 0;
using (OpenXmlReader oxr = OpenXmlReader.Create(sv))
{
SLSelection sel;
while (oxr.Read())
{
if (oxr.ElementType == typeof(Pane))
{
this.Pane = new SLPane();
this.Pane.FromPane((Pane)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(Selection))
{
sel = new SLSelection();
sel.FromSelection((Selection)oxr.LoadCurrentElement());
this.Selections.Add(sel);
}
else if (oxr.ElementType == typeof(PivotSelection))
{
this.PivotSelections.Add((PivotSelection)oxr.LoadCurrentElement().CloneNode(true));
}
}
}
}
/// <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 void FromSheetView(SheetView sv)
{
this.SetAllNull();
if (sv.WindowProtection != null)
{
this.WindowProtection = sv.WindowProtection.Value;
}
if (sv.ShowFormulas != null)
{
this.ShowFormulas = sv.ShowFormulas.Value;
}
if (sv.ShowGridLines != null)
{
this.ShowGridLines = sv.ShowGridLines.Value;
}
if (sv.ShowRowColHeaders != null)
{
this.ShowRowColHeaders = sv.ShowRowColHeaders.Value;
}
if (sv.ShowZeros != null)
{
this.ShowZeros = sv.ShowZeros.Value;
}
if (sv.RightToLeft != null)
{
this.RightToLeft = sv.RightToLeft.Value;
}
if (sv.TabSelected != null)
{
this.TabSelected = sv.TabSelected.Value;
}
if (sv.ShowRuler != null)
{
this.ShowRuler = sv.ShowRuler.Value;
}
if (sv.ShowOutlineSymbols != null)
{
this.ShowOutlineSymbols = sv.ShowOutlineSymbols.Value;
}
if (sv.DefaultGridColor != null)
{
this.DefaultGridColor = sv.DefaultGridColor.Value;
}
if (sv.ShowWhiteSpace != null)
{
this.ShowWhiteSpace = sv.ShowWhiteSpace.Value;
}
if (sv.View != null)
{
this.View = sv.View.Value;
}
if (sv.TopLeftCell != null)
{
this.TopLeftCell = sv.TopLeftCell.Value;
}
if (sv.ColorId != null)
{
this.ColorId = sv.ColorId.Value;
}
if (sv.ZoomScale != null)
{
this.ZoomScale = sv.ZoomScale.Value;
}
if (sv.ZoomScaleNormal != null)
{
this.ZoomScaleNormal = sv.ZoomScaleNormal.Value;
}
if (sv.ZoomScaleSheetLayoutView != null)
{
this.ZoomScaleSheetLayoutView = sv.ZoomScaleSheetLayoutView.Value;
}
if (sv.ZoomScalePageLayoutView != null)
{
this.ZoomScalePageLayoutView = sv.ZoomScalePageLayoutView.Value;
}
// required attribute but we'll use 0 as the default in case something terrible happens.
if (sv.WorkbookViewId != null)
{
this.WorkbookViewId = sv.WorkbookViewId.Value;
}
else
{
this.WorkbookViewId = 0;
}
using (OpenXmlReader oxr = OpenXmlReader.Create(sv))
{
SLSelection sel;
while (oxr.Read())
{
if (oxr.ElementType == typeof(Pane))
{
this.Pane = new SLPane();
this.Pane.FromPane((Pane)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(Selection))
{
sel = new SLSelection();
sel.FromSelection((Selection)oxr.LoadCurrentElement());
this.Selections.Add(sel);
}
else if (oxr.ElementType == typeof(PivotSelection))
{
this.PivotSelections.Add((PivotSelection)oxr.LoadCurrentElement().CloneNode(true));
}
}
}
}
/// <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;
}
