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; }