Пример #1
0
        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;
        }
Пример #2
0
        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);
        }
Пример #4
0
        // 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;
        }
Пример #5
0
        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);
                }
            }
        }
Пример #6
0
        /// <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;
        }
Пример #7
0
        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;
        }