internal SLHyperlink Clone()
{
SLHyperlink hl = new SLHyperlink();
hl.IsExternal = this.IsExternal;
hl.HyperlinkUri = this.HyperlinkUri;
hl.HyperlinkUriKind = this.HyperlinkUriKind;
hl.IsNew = this.IsNew;
hl.Reference = new SLCellPointRange(this.Reference.StartRowIndex, this.Reference.StartColumnIndex, this.Reference.EndRowIndex, this.Reference.EndColumnIndex);
hl.Id = this.Id;
hl.Location = this.Location;
hl.ToolTip = this.ToolTip;
hl.Display = this.Display;
return hl;
}
internal SLHyperlink Clone()
{
SLHyperlink hl = new SLHyperlink();
hl.IsExternal = this.IsExternal;
hl.HyperlinkUri = this.HyperlinkUri;
hl.HyperlinkUriKind = this.HyperlinkUriKind;
hl.IsNew = this.IsNew;
hl.Reference = new SLCellPointRange(this.Reference.StartRowIndex, this.Reference.StartColumnIndex, this.Reference.EndRowIndex, this.Reference.EndColumnIndex);
hl.Id = this.Id;
hl.Location = this.Location;
hl.ToolTip = this.ToolTip;
hl.Display = this.Display;
return(hl);
}
// TODO: Hyperlink cell range
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="HyperlinkType">The type of hyperlink.</param>
/// <param name="Address">The URL for web pages, the file path for existing files, a cell reference (such as Sheet1!A1 or Sheet1!A1:B5), a defined name or an email address. NOTE: Do NOT include the "mailto:" portion for email addresses.</param>
/// <param name="Display">The display text. Set null or an empty string to use the default.</param>
/// <param name="ToolTip">The tooltip (or screentip) text. Set null or an empty string to ignore this.</param>
/// <param name="OverwriteExistingCell">True to overwrite the existing cell value with the hyperlink display text. False otherwise.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool InsertHyperlink(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell)
{
if (RowIndex < 1 || RowIndex > SLConstants.RowLimit)
{
return(false);
}
if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit)
{
return(false);
}
SLHyperlink hl = new SLHyperlink();
hl.IsNew = true;
hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex);
switch (HyperlinkType)
{
case SLHyperlinkTypeValues.EmailAddress:
hl.IsExternal = true;
hl.HyperlinkUri = string.Format("mailto:{0}", Address);
hl.HyperlinkUriKind = UriKind.Absolute;
break;
case SLHyperlinkTypeValues.FilePath:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
// assume if it starts with ../ or ./ it's a relative path.
hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute;
break;
case SLHyperlinkTypeValues.InternalDocumentLink:
hl.IsExternal = false;
hl.Location = Address;
break;
case SLHyperlinkTypeValues.Url:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
hl.HyperlinkUriKind = UriKind.Absolute;
break;
}
if (Display == null)
{
hl.Display = Address;
}
else
{
if (Display.Length == 0)
{
hl.Display = Address;
}
else
{
hl.Display = Display;
}
}
if (ToolTip != null && ToolTip.Length > 0)
{
hl.ToolTip = ToolTip;
}
SLCell c;
SLStyle style;
if (slws.CellWarehouse.Exists(RowIndex, ColumnIndex))
{
c = slws.CellWarehouse.Cells[RowIndex][ColumnIndex];
style = new SLStyle();
if (c.StyleIndex < listStyle.Count)
{
style.FromHash(listStyle[(int)c.StyleIndex]);
}
else
{
style.FromHash(listStyle[0]);
}
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
if (OverwriteExistingCell)
{
// in case there's a formula
c.CellFormula = null;
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
}
// else don't have to do anything
slws.CellWarehouse.SetValue(RowIndex, ColumnIndex, c);
}
else
{
c = new SLCell();
style = new SLStyle();
style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
slws.CellWarehouse.SetValue(RowIndex, ColumnIndex, c);
}
slws.Hyperlinks.Add(hl);
return(true);
}
// TODO: Hyperlink cell range
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="HyperlinkType">The type of hyperlink.</param>
/// <param name="Address">The URL for web pages, the file path for existing files, a cell reference (such as Sheet1!A1 or Sheet1!A1:B5), a defined name or an email address. NOTE: Do NOT include the "mailto:" portion for email addresses.</param>
/// <param name="Display">The display text. Set null or an empty string to use the default.</param>
/// <param name="ToolTip">The tooltip (or screentip) text. Set null or an empty string to ignore this.</param>
/// <param name="OverwriteExistingCell">True to overwrite the existing cell value with the hyperlink display text. False otherwise.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool InsertHyperlink(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell)
{
if (RowIndex < 1 || RowIndex > SLConstants.RowLimit) return false;
if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return false;
SLHyperlink hl = new SLHyperlink();
hl.IsNew = true;
hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex);
switch (HyperlinkType)
{
case SLHyperlinkTypeValues.EmailAddress:
hl.IsExternal = true;
hl.HyperlinkUri = string.Format("mailto:{0}", Address);
hl.HyperlinkUriKind = UriKind.Absolute;
break;
case SLHyperlinkTypeValues.FilePath:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
// assume if it starts with ../ or ./ it's a relative path.
hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute;
break;
case SLHyperlinkTypeValues.InternalDocumentLink:
hl.IsExternal = false;
hl.Location = Address;
break;
case SLHyperlinkTypeValues.Url:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
hl.HyperlinkUriKind = UriKind.Absolute;
break;
}
if (Display == null)
{
hl.Display = Address;
}
else
{
if (Display.Length == 0)
{
hl.Display = Address;
}
else
{
hl.Display = Display;
}
}
if (ToolTip != null && ToolTip.Length > 0) hl.ToolTip = ToolTip;
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
SLStyle style;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
style = new SLStyle();
if (c.StyleIndex < listStyle.Count) style.FromHash(listStyle[(int)c.StyleIndex]);
else style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
if (OverwriteExistingCell)
{
// in case there's a formula
c.CellFormula = null;
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
}
// else don't have to do anything
slws.Cells[pt] = c.Clone();
}
else
{
c = new SLCell();
style = new SLStyle();
style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
slws.Cells[pt] = c.Clone();
}
slws.Hyperlinks.Add(hl);
return true;
}
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>
/// Copy a range of cells from another worksheet to the currently selected worksheet, given the anchor cell of the destination range (top-left cell).
/// </summary>
/// <param name="WorksheetName">The name of the source worksheet.</param>
/// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param>
/// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param>
/// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param>
/// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param>
/// <param name="AnchorRowIndex">The row index of the anchor cell.</param>
/// <param name="AnchorColumnIndex">The column index of the anchor cell.</param>
/// <param name="PasteOption">Paste option.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool CopyCellFromWorksheet(string WorksheetName, int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, int AnchorRowIndex, int AnchorColumnIndex, SLPasteTypeValues PasteOption)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (WorksheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
return this.CopyCell(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, AnchorRowIndex, AnchorColumnIndex, false);
}
string sRelId = string.Empty;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(WorksheetName, StringComparison.OrdinalIgnoreCase))
{
sRelId = sheet.Id;
break;
}
}
// there has to be a valid existing worksheet
if (sRelId.Length == 0) return false;
bool result = false;
if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
&& iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit
&& iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
&& iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit
&& AnchorRowIndex >= 1 && AnchorRowIndex <= SLConstants.RowLimit
&& AnchorColumnIndex >= 1 && AnchorColumnIndex <= SLConstants.ColumnLimit)
{
result = true;
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sRelId);
int i, j, iSwap, iStyleIndex, iStyleIndexNew;
SLCell origcell, newcell;
SLCellPoint pt, newpt;
int rowdiff = AnchorRowIndex - iStartRowIndex;
int coldiff = AnchorColumnIndex - iStartColumnIndex;
Dictionary<SLCellPoint, SLCell> cells = new Dictionary<SLCellPoint, SLCell>();
Dictionary<SLCellPoint, SLCell> sourcecells = new Dictionary<SLCellPoint, SLCell>();
Dictionary<int, uint> sourcecolstyleindex = new Dictionary<int, uint>();
Dictionary<int, uint> sourcerowstyleindex = new Dictionary<int, uint>();
string sCellRef = string.Empty;
HashSet<string> hsCellRef = new HashSet<string>();
// I use a hash set on the logic that it's easier to check a string hash (of cell references)
// first, rather than load a Cell class into SLCell and then check with row/column indices.
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
sCellRef = SLTool.ToCellReference(i, j);
if (!hsCellRef.Contains(sCellRef))
{
hsCellRef.Add(sCellRef);
}
}
}
// hyperlink ID, URL
Dictionary<string, string> hlurl = new Dictionary<string, string>();
List<SLHyperlink> sourcehyperlinks = new List<SLHyperlink>();
foreach (HyperlinkRelationship hlrel in wsp.HyperlinkRelationships)
{
if (hlrel.IsExternal)
{
hlurl[hlrel.Id] = hlrel.Uri.OriginalString;
}
}
using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
{
Column col;
int iColumnMin, iColumnMax;
Row r;
Cell c;
SLHyperlink hl;
while (oxr.Read())
{
if (oxr.ElementType == typeof(Column))
{
col = (Column)oxr.LoadCurrentElement();
iColumnMin = (int)col.Min.Value;
iColumnMax = (int)col.Max.Value;
for (i = iColumnMin; i <= iColumnMax; ++i)
{
sourcecolstyleindex[i] = (col.Style != null) ? col.Style.Value : 0;
}
}
else if (oxr.ElementType == typeof(Row))
{
r = (Row)oxr.LoadCurrentElement();
if (r.RowIndex != null)
{
if (r.StyleIndex != null) sourcerowstyleindex[(int)r.RowIndex.Value] = r.StyleIndex.Value;
else sourcerowstyleindex[(int)r.RowIndex.Value] = 0;
}
using (OpenXmlReader oxrRow = OpenXmlReader.Create(r))
{
while (oxrRow.Read())
{
if (oxrRow.ElementType == typeof(Cell))
{
c = (Cell)oxrRow.LoadCurrentElement();
if (c.CellReference != null)
{
sCellRef = c.CellReference.Value;
if (hsCellRef.Contains(sCellRef))
{
origcell = new SLCell();
origcell.FromCell(c);
// this should work because hsCellRef already contains valid cell references
SLTool.FormatCellReferenceToRowColumnIndex(sCellRef, out i, out j);
pt = new SLCellPoint(i, j);
sourcecells[pt] = origcell.Clone();
}
}
}
}
}
}
else if (oxr.ElementType == typeof(Hyperlink))
{
hl = new SLHyperlink();
hl.FromHyperlink((Hyperlink)oxr.LoadCurrentElement());
sourcehyperlinks.Add(hl);
}
}
}
Dictionary<int, uint> colstyleindex = new Dictionary<int, uint>();
Dictionary<int, uint> rowstyleindex = new Dictionary<int, uint>();
List<int> rowindexkeys = slws.RowProperties.Keys.ToList<int>();
SLRowProperties rp;
foreach (int rowindex in rowindexkeys)
{
rp = slws.RowProperties[rowindex];
rowstyleindex[rowindex] = rp.StyleIndex;
}
List<int> colindexkeys = slws.ColumnProperties.Keys.ToList<int>();
SLColumnProperties cp;
foreach (int colindex in colindexkeys)
{
cp = slws.ColumnProperties[colindex];
colstyleindex[colindex] = cp.StyleIndex;
}
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
newpt = new SLCellPoint(i + rowdiff, j + coldiff);
switch (PasteOption)
{
case SLPasteTypeValues.Formatting:
if (sourcecells.ContainsKey(pt))
{
origcell = sourcecells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = origcell.StyleIndex;
cells[newpt] = newcell.Clone();
}
else
{
if (origcell.StyleIndex != 0)
{
// if not the default style, then must create a new
// destination cell.
newcell = new SLCell();
newcell.StyleIndex = origcell.StyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// else source cell has default style.
// Now check if destination cell lies on a row/column
// that has non-default style. Remember, we don't have
// a destination cell here.
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = 0;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
}
else
{
// else no source cell
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
cells[newpt] = newcell.Clone();
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
break;
case SLPasteTypeValues.Formulas:
if (sourcecells.ContainsKey(pt))
{
origcell = sourcecells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
else newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
cells[newpt] = newcell.Clone();
}
else
{
newcell = new SLCell();
if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
else newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
cells[newpt] = newcell.Clone();
}
}
else
{
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellText = string.Empty;
newcell.DataType = CellValues.Number;
cells[newpt] = newcell.Clone();
}
// no else because don't have to do anything
}
break;
case SLPasteTypeValues.Paste:
if (sourcecells.ContainsKey(pt))
{
origcell = sourcecells[pt].Clone();
cells[newpt] = origcell.Clone();
}
else
{
// else the source cell is empty
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
if (iStyleIndex != 0)
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// if the source cell is empty, then direct pasting
// means overwrite the existing cell, which is faster
// by just removing it.
slws.Cells.Remove(newpt);
}
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
break;
case SLPasteTypeValues.Transpose:
newpt = new SLCellPoint(i - iStartRowIndex, j - iStartColumnIndex);
iSwap = newpt.RowIndex;
newpt.RowIndex = newpt.ColumnIndex;
newpt.ColumnIndex = iSwap;
newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff;
newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff;
// in case say the millionth row is transposed, because we can't have a millionth column.
if (newpt.RowIndex <= SLConstants.RowLimit && newpt.ColumnIndex <= SLConstants.ColumnLimit)
{
// this part is identical to normal paste
if (sourcecells.ContainsKey(pt))
{
origcell = sourcecells[pt].Clone();
cells[newpt] = origcell.Clone();
}
else
{
// else the source cell is empty
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
if (iStyleIndex != 0)
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// if the source cell is empty, then direct pasting
// means overwrite the existing cell, which is faster
// by just removing it.
slws.Cells.Remove(newpt);
}
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
}
break;
case SLPasteTypeValues.Values:
// this part is identical to the formula part, except
// for assigning the cell formula part.
if (sourcecells.ContainsKey(pt))
{
origcell = sourcecells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
cells[newpt] = newcell.Clone();
}
else
{
newcell = new SLCell();
newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
cells[newpt] = newcell.Clone();
}
}
else
{
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellFormula = null;
newcell.CellText = string.Empty;
newcell.DataType = CellValues.Number;
cells[newpt] = newcell.Clone();
}
// no else because don't have to do anything
}
break;
}
}
}
int AnchorEndRowIndex = AnchorRowIndex + iEndRowIndex - iStartRowIndex;
int AnchorEndColumnIndex = AnchorColumnIndex + iEndColumnIndex - iStartColumnIndex;
for (i = AnchorRowIndex; i <= AnchorEndRowIndex; ++i)
{
for (j = AnchorColumnIndex; j <= AnchorEndColumnIndex; ++j)
{
// any cell within destination "paste" operation is taken out
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt)) slws.Cells.Remove(pt);
}
}
foreach (SLCellPoint cellkey in cells.Keys)
{
origcell = cells[cellkey];
// the source cells are from another worksheet. Don't know how to rearrange any
// cell references in cell formulas...
slws.Cells[cellkey] = origcell.Clone();
}
// See CopyCell() for the behaviour explanation
// I'm not going to figure out how to copy merged cells from the source worksheet
// and decide under what conditions the existing merged cells in the destination
// worksheet should be removed.
// So I'm going to just remove any merged cells in the delete range.
List<SLMergeCell> mca = this.GetWorksheetMergeCells();
foreach (SLMergeCell mc in mca)
{
if (mc.StartRowIndex >= AnchorRowIndex && mc.EndRowIndex <= AnchorEndRowIndex
&& mc.StartColumnIndex >= AnchorColumnIndex && mc.EndColumnIndex <= AnchorEndColumnIndex)
{
slws.MergeCells.Remove(mc);
}
}
// TODO: conditional formatting and data validations?
#region Hyperlinks
if (sourcehyperlinks.Count > 0)
{
// we only care if normal paste or transpose paste. Just like Excel.
if (PasteOption == SLPasteTypeValues.Paste || PasteOption == SLPasteTypeValues.Transpose)
{
List<SLHyperlink> copiedhyperlinks = new List<SLHyperlink>();
SLHyperlink hlCopied;
int iOverlapStartRowIndex = 1;
int iOverlapStartColumnIndex = 1;
int iOverlapEndRowIndex = 1;
int iOverlapEndColumnIndex = 1;
foreach (SLHyperlink hl in sourcehyperlinks)
{
// this comes from the separating axis theorem.
// See merged cells for more details.
// In this case however, we're doing stuff when there's overlapping.
if (!(iEndRowIndex < hl.Reference.StartRowIndex
|| iStartRowIndex > hl.Reference.EndRowIndex
|| iEndColumnIndex < hl.Reference.StartColumnIndex
|| iStartColumnIndex > hl.Reference.EndColumnIndex))
{
// get the overlapping region
iOverlapStartRowIndex = Math.Max(iStartRowIndex, hl.Reference.StartRowIndex);
iOverlapStartColumnIndex = Math.Max(iStartColumnIndex, hl.Reference.StartColumnIndex);
iOverlapEndRowIndex = Math.Min(iEndRowIndex, hl.Reference.EndRowIndex);
iOverlapEndColumnIndex = Math.Min(iEndColumnIndex, hl.Reference.EndColumnIndex);
// offset to the correctly pasted region
if (PasteOption == SLPasteTypeValues.Paste)
{
iOverlapStartRowIndex += rowdiff;
iOverlapStartColumnIndex += coldiff;
iOverlapEndRowIndex += rowdiff;
iOverlapEndColumnIndex += coldiff;
}
else
{
// can only be transpose. See if check above.
if (iOverlapEndRowIndex > SLConstants.ColumnLimit)
{
// probably won't happen. This means that after transpose,
// the end row index will flip to exceed the column limit.
// I don't feel like testing how Excel handles this, so
// I'm going to just take it as normal paste.
iOverlapStartRowIndex += rowdiff;
iOverlapStartColumnIndex += coldiff;
iOverlapEndRowIndex += rowdiff;
iOverlapEndColumnIndex += coldiff;
}
else
{
iOverlapStartRowIndex -= iStartRowIndex;
iOverlapStartColumnIndex -= iStartColumnIndex;
iOverlapEndRowIndex -= iStartRowIndex;
iOverlapEndColumnIndex -= iStartColumnIndex;
iSwap = iOverlapStartRowIndex;
iOverlapStartRowIndex = iOverlapStartColumnIndex;
iOverlapStartColumnIndex = iSwap;
iSwap = iOverlapEndRowIndex;
iOverlapEndRowIndex = iOverlapEndColumnIndex;
iOverlapEndColumnIndex = iSwap;
iOverlapStartRowIndex += (iStartRowIndex + rowdiff);
iOverlapStartColumnIndex += (iStartColumnIndex + coldiff);
iOverlapEndRowIndex += (iStartRowIndex + rowdiff);
iOverlapEndColumnIndex += (iStartColumnIndex + coldiff);
}
}
hlCopied = new SLHyperlink();
hlCopied = hl.Clone();
hlCopied.IsNew = true;
if (hlCopied.IsExternal)
{
if (hlurl.ContainsKey(hlCopied.Id))
{
hlCopied.HyperlinkUri = hlurl[hlCopied.Id];
if (hlCopied.HyperlinkUri.StartsWith("."))
{
// assume this is a relative file path such as ../ or ./
hlCopied.HyperlinkUriKind = UriKind.Relative;
}
else
{
hlCopied.HyperlinkUriKind = UriKind.Absolute;
}
hlCopied.Id = string.Empty;
}
}
hlCopied.Reference = new SLCellPointRange(iOverlapStartRowIndex, iOverlapStartColumnIndex, iOverlapEndRowIndex, iOverlapEndColumnIndex);
copiedhyperlinks.Add(hlCopied);
}
}
if (copiedhyperlinks.Count > 0)
{
slws.Hyperlinks.AddRange(copiedhyperlinks);
}
}
}
#endregion
#region Calculation cells
if (slwb.CalculationCells.Count > 0)
{
List<int> listToDelete = new List<int>();
int iRowIndex = -1;
int iColumnIndex = -1;
for (i = 0; i < slwb.CalculationCells.Count; ++i)
{
if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID)
{
iRowIndex = slwb.CalculationCells[i].RowIndex;
iColumnIndex = slwb.CalculationCells[i].ColumnIndex;
if (iRowIndex >= AnchorRowIndex && iRowIndex <= AnchorEndRowIndex
&& iColumnIndex >= AnchorColumnIndex && iColumnIndex <= AnchorEndColumnIndex)
{
// existing calculation cell lies within destination "paste" operation
if (!listToDelete.Contains(i)) listToDelete.Add(i);
}
}
}
for (i = listToDelete.Count - 1; i >= 0; --i)
{
slwb.CalculationCells.RemoveAt(listToDelete[i]);
}
}
#endregion
}
return result;
}
private bool CopyCell(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, int AnchorRowIndex, int AnchorColumnIndex, bool ToCut, SLPasteTypeValues PasteOption)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
bool result = false;
if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
&& iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit
&& iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
&& iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit
&& AnchorRowIndex >= 1 && AnchorRowIndex <= SLConstants.RowLimit
&& AnchorColumnIndex >= 1 && AnchorColumnIndex <= SLConstants.ColumnLimit
&& (iStartRowIndex != AnchorRowIndex || iStartColumnIndex != AnchorColumnIndex))
{
result = true;
int i, j, iSwap, iStyleIndex, iStyleIndexNew;
SLCell origcell, newcell;
SLCellPoint pt, newpt;
int rowdiff = AnchorRowIndex - iStartRowIndex;
int coldiff = AnchorColumnIndex - iStartColumnIndex;
Dictionary<SLCellPoint, SLCell> cells = new Dictionary<SLCellPoint, SLCell>();
Dictionary<int, uint> colstyleindex = new Dictionary<int, uint>();
Dictionary<int, uint> rowstyleindex = new Dictionary<int, uint>();
List<int> rowindexkeys = slws.RowProperties.Keys.ToList<int>();
SLRowProperties rp;
foreach (int rowindex in rowindexkeys)
{
rp = slws.RowProperties[rowindex];
rowstyleindex[rowindex] = rp.StyleIndex;
}
List<int> colindexkeys = slws.ColumnProperties.Keys.ToList<int>();
SLColumnProperties cp;
foreach (int colindex in colindexkeys)
{
cp = slws.ColumnProperties[colindex];
colstyleindex[colindex] = cp.StyleIndex;
}
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
newpt = new SLCellPoint(i + rowdiff, j + coldiff);
if (ToCut)
{
if (slws.Cells.ContainsKey(pt))
{
cells[newpt] = slws.Cells[pt].Clone();
slws.Cells.Remove(pt);
}
}
else
{
switch (PasteOption)
{
case SLPasteTypeValues.Formatting:
if (slws.Cells.ContainsKey(pt))
{
origcell = slws.Cells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = origcell.StyleIndex;
cells[newpt] = newcell.Clone();
}
else
{
if (origcell.StyleIndex != 0)
{
// if not the default style, then must create a new
// destination cell.
newcell = new SLCell();
newcell.StyleIndex = origcell.StyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// else source cell has default style.
// Now check if destination cell lies on a row/column
// that has non-default style. Remember, we don't have
// a destination cell here.
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = 0;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
}
else
{
// else no source cell
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
cells[newpt] = newcell.Clone();
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
break;
case SLPasteTypeValues.Formulas:
if (slws.Cells.ContainsKey(pt))
{
origcell = slws.Cells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
else newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
cells[newpt] = newcell.Clone();
}
else
{
newcell = new SLCell();
if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
else newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
cells[newpt] = newcell.Clone();
}
}
else
{
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellText = string.Empty;
newcell.DataType = CellValues.Number;
cells[newpt] = newcell.Clone();
}
// no else because don't have to do anything
}
break;
case SLPasteTypeValues.Paste:
if (slws.Cells.ContainsKey(pt))
{
origcell = slws.Cells[pt].Clone();
cells[newpt] = origcell.Clone();
}
else
{
// else the source cell is empty
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
if (iStyleIndex != 0)
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// if the source cell is empty, then direct pasting
// means overwrite the existing cell, which is faster
// by just removing it.
slws.Cells.Remove(newpt);
}
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
break;
case SLPasteTypeValues.Transpose:
newpt = new SLCellPoint(i - iStartRowIndex, j - iStartColumnIndex);
iSwap = newpt.RowIndex;
newpt.RowIndex = newpt.ColumnIndex;
newpt.ColumnIndex = iSwap;
newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff;
newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff;
// in case say the millionth row is transposed, because we can't have a millionth column.
if (newpt.RowIndex <= SLConstants.RowLimit && newpt.ColumnIndex <= SLConstants.ColumnLimit)
{
// this part is identical to normal paste
if (slws.Cells.ContainsKey(pt))
{
origcell = slws.Cells[pt].Clone();
cells[newpt] = origcell.Clone();
}
else
{
// else the source cell is empty
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
if (iStyleIndex != 0)
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// if the source cell is empty, then direct pasting
// means overwrite the existing cell, which is faster
// by just removing it.
slws.Cells.Remove(newpt);
}
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
}
break;
case SLPasteTypeValues.Values:
// this part is identical to the formula part, except
// for assigning the cell formula part.
if (slws.Cells.ContainsKey(pt))
{
origcell = slws.Cells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
cells[newpt] = newcell.Clone();
}
else
{
newcell = new SLCell();
newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
cells[newpt] = newcell.Clone();
}
}
else
{
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellFormula = null;
newcell.CellText = string.Empty;
newcell.DataType = CellValues.Number;
cells[newpt] = newcell.Clone();
}
// no else because don't have to do anything
}
break;
}
}
}
}
int AnchorEndRowIndex = AnchorRowIndex + iEndRowIndex - iStartRowIndex;
int AnchorEndColumnIndex = AnchorColumnIndex + iEndColumnIndex - iStartColumnIndex;
for (i = AnchorRowIndex; i <= AnchorEndRowIndex; ++i)
{
for (j = AnchorColumnIndex; j <= AnchorEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
// any cell within destination "paste" operation is taken out
slws.Cells.Remove(pt);
}
}
}
int iNumberOfRows = iEndRowIndex - iStartRowIndex + 1;
if (AnchorRowIndex <= iStartRowIndex) iNumberOfRows = -iNumberOfRows;
int iNumberOfColumns = iEndColumnIndex - iStartColumnIndex + 1;
if (AnchorColumnIndex <= iStartColumnIndex) iNumberOfColumns = -iNumberOfColumns;
foreach (SLCellPoint cellkey in cells.Keys)
{
origcell = cells[cellkey];
if (PasteOption != SLPasteTypeValues.Transpose)
{
this.ProcessCellFormulaDelta(ref origcell, AnchorRowIndex, iNumberOfRows, AnchorColumnIndex, iNumberOfColumns);
}
else
{
this.ProcessCellFormulaDelta(ref origcell, AnchorRowIndex, iNumberOfColumns, AnchorColumnIndex, iNumberOfRows);
}
slws.Cells[cellkey] = origcell.Clone();
}
// TODO: tables!
// cutting and pasting into a region with merged cells unmerges the existing merged cells
// copying and pasting into a region with merged cells leaves existing merged cells alone.
// Why does Excel do that? Don't know.
// Will just standardise to leaving existing merged cells alone.
List<SLMergeCell> mca = this.GetWorksheetMergeCells();
foreach (SLMergeCell mc in mca)
{
if (mc.StartRowIndex >= iStartRowIndex && mc.EndRowIndex <= iEndRowIndex
&& mc.StartColumnIndex >= iStartColumnIndex && mc.EndColumnIndex <= iEndColumnIndex)
{
if (ToCut)
{
slws.MergeCells.Remove(mc);
}
if (PasteOption == SLPasteTypeValues.Transpose)
{
pt = new SLCellPoint(mc.StartRowIndex - iStartRowIndex, mc.StartColumnIndex - iStartColumnIndex);
iSwap = pt.RowIndex;
pt.RowIndex = pt.ColumnIndex;
pt.ColumnIndex = iSwap;
pt.RowIndex = pt.RowIndex + iStartRowIndex + rowdiff;
pt.ColumnIndex = pt.ColumnIndex + iStartColumnIndex + coldiff;
newpt = new SLCellPoint(mc.EndRowIndex - iStartRowIndex, mc.EndColumnIndex - iStartColumnIndex);
iSwap = newpt.RowIndex;
newpt.RowIndex = newpt.ColumnIndex;
newpt.ColumnIndex = iSwap;
newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff;
newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff;
this.MergeWorksheetCells(pt.RowIndex, pt.ColumnIndex, newpt.RowIndex, newpt.ColumnIndex);
}
else
{
this.MergeWorksheetCells(mc.StartRowIndex + rowdiff, mc.StartColumnIndex + coldiff, mc.EndRowIndex + rowdiff, mc.EndColumnIndex + coldiff);
}
}
}
// TODO: conditional formatting and data validations?
#region Hyperlinks
if (slws.Hyperlinks.Count > 0)
{
if (ToCut)
{
foreach (SLHyperlink hl in slws.Hyperlinks)
{
// if hyperlink is completely within copy range
if (iStartRowIndex <= hl.Reference.StartRowIndex
&& hl.Reference.EndRowIndex <= iEndRowIndex
&& iStartColumnIndex <= hl.Reference.StartColumnIndex
&& hl.Reference.EndColumnIndex <= iEndColumnIndex)
{
hl.Reference = new SLCellPointRange(hl.Reference.StartRowIndex + rowdiff,
hl.Reference.StartColumnIndex + coldiff,
hl.Reference.EndRowIndex + rowdiff,
hl.Reference.EndColumnIndex + coldiff);
}
// else don't change anything (Excel doesn't, so we don't).
}
}
else
{
// we only care if normal paste or transpose paste. Just like Excel.
if (PasteOption == SLPasteTypeValues.Paste || PasteOption == SLPasteTypeValues.Transpose)
{
List<SLHyperlink> copiedhyperlinks = new List<SLHyperlink>();
SLHyperlink hlCopied;
// hyperlink ID, URL
Dictionary<string, string> hlurl = new Dictionary<string, string>();
if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID))
{
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID);
foreach (HyperlinkRelationship hlrel in wsp.HyperlinkRelationships)
{
if (hlrel.IsExternal)
{
hlurl[hlrel.Id] = hlrel.Uri.OriginalString;
}
}
}
int iOverlapStartRowIndex = 1;
int iOverlapStartColumnIndex = 1;
int iOverlapEndRowIndex = 1;
int iOverlapEndColumnIndex = 1;
foreach (SLHyperlink hl in slws.Hyperlinks)
{
// this comes from the separating axis theorem.
// See merged cells for more details.
// In this case however, we're doing stuff when there's overlapping.
if (!(iEndRowIndex < hl.Reference.StartRowIndex
|| iStartRowIndex > hl.Reference.EndRowIndex
|| iEndColumnIndex < hl.Reference.StartColumnIndex
|| iStartColumnIndex > hl.Reference.EndColumnIndex))
{
// get the overlapping region
iOverlapStartRowIndex = Math.Max(iStartRowIndex, hl.Reference.StartRowIndex);
iOverlapStartColumnIndex = Math.Max(iStartColumnIndex, hl.Reference.StartColumnIndex);
iOverlapEndRowIndex = Math.Min(iEndRowIndex, hl.Reference.EndRowIndex);
iOverlapEndColumnIndex = Math.Min(iEndColumnIndex, hl.Reference.EndColumnIndex);
// offset to the correctly pasted region
if (PasteOption == SLPasteTypeValues.Paste)
{
iOverlapStartRowIndex += rowdiff;
iOverlapStartColumnIndex += coldiff;
iOverlapEndRowIndex += rowdiff;
iOverlapEndColumnIndex += coldiff;
}
else
{
// can only be transpose. See if check above.
if (iOverlapEndRowIndex > SLConstants.ColumnLimit)
{
// probably won't happen. This means that after transpose,
// the end row index will flip to exceed the column limit.
// I don't feel like testing how Excel handles this, so
// I'm going to just take it as normal paste.
iOverlapStartRowIndex += rowdiff;
iOverlapStartColumnIndex += coldiff;
iOverlapEndRowIndex += rowdiff;
iOverlapEndColumnIndex += coldiff;
}
else
{
iOverlapStartRowIndex -= iStartRowIndex;
iOverlapStartColumnIndex -= iStartColumnIndex;
iOverlapEndRowIndex -= iStartRowIndex;
iOverlapEndColumnIndex -= iStartColumnIndex;
iSwap = iOverlapStartRowIndex;
iOverlapStartRowIndex = iOverlapStartColumnIndex;
iOverlapStartColumnIndex = iSwap;
iSwap = iOverlapEndRowIndex;
iOverlapEndRowIndex = iOverlapEndColumnIndex;
iOverlapEndColumnIndex = iSwap;
iOverlapStartRowIndex += (iStartRowIndex + rowdiff);
iOverlapStartColumnIndex += (iStartColumnIndex + coldiff);
iOverlapEndRowIndex += (iStartRowIndex + rowdiff);
iOverlapEndColumnIndex += (iStartColumnIndex + coldiff);
}
}
hlCopied = new SLHyperlink();
hlCopied = hl.Clone();
hlCopied.IsNew = true;
if (hlCopied.IsExternal)
{
if (hlurl.ContainsKey(hlCopied.Id))
{
hlCopied.HyperlinkUri = hlurl[hlCopied.Id];
if (hlCopied.HyperlinkUri.StartsWith("."))
{
// assume this is a relative file path such as ../ or ./
hlCopied.HyperlinkUriKind = UriKind.Relative;
}
else
{
hlCopied.HyperlinkUriKind = UriKind.Absolute;
}
hlCopied.Id = string.Empty;
}
}
hlCopied.Reference = new SLCellPointRange(iOverlapStartRowIndex, iOverlapStartColumnIndex, iOverlapEndRowIndex, iOverlapEndColumnIndex);
copiedhyperlinks.Add(hlCopied);
}
}
if (copiedhyperlinks.Count > 0)
{
slws.Hyperlinks.AddRange(copiedhyperlinks);
}
}
}
}
#endregion
#region Calculation cells
if (slwb.CalculationCells.Count > 0)
{
List<int> listToDelete = new List<int>();
int iRowIndex = -1;
int iColumnIndex = -1;
for (i = 0; i < slwb.CalculationCells.Count; ++i)
{
if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID)
{
iRowIndex = slwb.CalculationCells[i].RowIndex;
iColumnIndex = slwb.CalculationCells[i].ColumnIndex;
if (ToCut && iRowIndex >= iStartRowIndex && iRowIndex <= iEndRowIndex
&& iColumnIndex >= iStartColumnIndex && iColumnIndex <= iEndColumnIndex)
{
// just remove because recalculation of cell references is too complicated...
if (!listToDelete.Contains(i)) listToDelete.Add(i);
}
if (iRowIndex >= AnchorRowIndex && iRowIndex <= AnchorEndRowIndex
&& iColumnIndex >= AnchorColumnIndex && iColumnIndex <= AnchorEndColumnIndex)
{
// existing calculation cell lies within destination "paste" operation
if (!listToDelete.Contains(i)) listToDelete.Add(i);
}
}
}
for (i = listToDelete.Count - 1; i >= 0; --i)
{
slwb.CalculationCells.RemoveAt(listToDelete[i]);
}
}
#endregion
// defined names is hard to calculate...
// need to check the row and column indices based on the cell references within.
}
return result;
}