This project has moved. For the latest updates, please go here.

[4.0.3] Can I copy ranges using "Paste Special"-like options?

Feb 18, 2015 at 12:58 PM
Hi,

Is it possible to copy a range from one worksheet to another the way "Paste Special" does, out of the box?
ExcelRangeBase.Copy copies everything. If you want to copy only values or formats that does not help.

Regards
Jun 10, 2015 at 1:33 PM
Edited Jun 10, 2015 at 1:33 PM
Hi,

Did you find a solution?

I also want to 'Paste Special' my HTML text as Unicode text, so that it is recogniized as HTML in excel file.
Any help from anyone will be more than welcome!

Thanks!
Vedran
Jun 12, 2015 at 9:41 AM
Well, yes and no.

I added/changed some code in the ExcelRangeBase.cs file, but it's kind of a first shot and I did not really test it yet.

The main idea is to extend the ExcelRangeBase.Copy method with an optional second parameter. This second parameter is of the new enumeration type ExcelPasteSpecialFlags and depending on the flags set (default: All, tested with new private method _IsSet()) some things are copied while others are not. So this is not a PasteSpecial method but rather a CopySpecial.

I enclose the code here (hasn't there been a file upload option?). Feel free to use and/or change it. Would be nice to hear if it works as expected or what has to be changed.

Maybe the developers could take a look on it, too?

Regards
[Flags]
public enum ExcelPasteSpecialFlags : ushort
{
    None = 0,
    Formulas = 1, Values = 2, Formats = 4, Comments = 8, Validation = 16, // Excel
    Hyperlinks = 32, Flags = 64,    // EPP
    AllUsingSourceTheme = 128, AllExceptBorders = 256, ColumnWidths = 512,  // Excel
    FormulasAndNumberFormats = 1025, // Excel, include formulas as well (1024 + 1)
    ValuesAndNumberFormats = 2050, // Excel, include values as well (2048 + 2)
    AllMergingConditionalFormats = 4096,    // Excel, ?
    All = 65535
}

public class ExcelRangeBase : ExcelAddress, IExcelCell, IDisposable, IEnumerable<ExcelRangeBase>, IEnumerator<ExcelRangeBase>
{
    /*
        code intentionally removed
    */
    
    private bool _IsSet(ExcelPasteSpecialFlags flags, ExcelPasteSpecialFlags flag)
    {
        return (flags & flag) > 0;
    }

    public void Copy(ExcelRangeBase Destination, ExcelPasteSpecialFlags pasteFlag = ExcelPasteSpecialFlags.All)
    {
            bool sameWorkbook = Destination._worksheet.Workbook == _worksheet.Workbook;
            ExcelStyles sourceStyles = _worksheet.Workbook.Styles,
                                    styles = Destination._worksheet.Workbook.Styles;
            Dictionary<int, int> styleCashe = new Dictionary<int, int>();

            //Delete all existing cells; 
            int toRow = _toRow - _fromRow + 1,
                    toCol = _toCol - _fromCol + 1;

            string s = "";
            int i=0;
            object o = null;
            byte flag=0;
            Uri hl = null;
            ExcelComment comment=null;

            var cse = new CellsStoreEnumerator<object>(_worksheet._values, _fromRow, _fromCol, _toRow, _toCol);
            var copiedValue = new List<CopiedCell>();
            while (cse.Next())
            {
                    var row=cse.Row;
                    var col = cse.Column;       //Issue 15070
                    var cell = new CopiedCell
                    {
                            Row = Destination._fromRow + (row - _fromRow),
                            Column = Destination._fromCol + (col - _fromCol),
                            Value = _IsSet(pasteFlag, ExcelPasteSpecialFlags.Values) ? cse.Value : null
                    };

                    //Destination._worksheet._values.SetValue(row, col, cse.Value);

                    if ((_IsSet(pasteFlag, ExcelPasteSpecialFlags.Values) || _IsSet(pasteFlag, ExcelPasteSpecialFlags.Formulas))
                        && _worksheet._types.Exists(row, col, ref s))
                    {
                            //Destination._worksheet._types.SetValue(row, col,s);
                            cell.Type=s;
                    }

                    if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Formulas) 
                        && _worksheet._formulas.Exists(row, col, ref o))
                    {
                            if (o is int)
                            {
                                 // Destination._worksheet._formulas.SetValue(row, col, _worksheet.GetFormula(cse.Row, cse.Column));    //Shared formulas, set the formula per cell to simplify
                                    cell.Formula=_worksheet.GetFormula(cse.Row, cse.Column);
                            }
                            else
                            {
                                    //Destination._worksheet._formulas.SetValue(row, col, o);
                                    cell.Formula=o;
                            }
                    }

                    if ((_IsSet(pasteFlag, ExcelPasteSpecialFlags.Formats))
                        && _worksheet._styles.Exists(row, col, ref i))
                    {
                            if (sameWorkbook)
                            {
                                    //Destination._worksheet._styles.SetValue(row, col, i);
                                    cell.StyleID=i;
                            }
                            else
                            {
                                    if (styleCashe.ContainsKey(i))
                                    {
                                            i = styleCashe[i];
                                    }
                                    else
                                    {
                                            var oldStyleID = i;
                                            i = styles.CloneStyle(sourceStyles, i);
                                            styleCashe.Add(oldStyleID, i);
                                    }
                                    //Destination._worksheet._styles.SetValue(row, col, i);
                                    cell.StyleID=i;
                            }
                    }

                    if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Hyperlinks)
                        && _worksheet._hyperLinks.Exists(row, col, ref hl))
                    {
                            //Destination._worksheet._hyperLinks.SetValue(row, col, hl);
                            cell.HyperLink=hl;
                    }

                    if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Comments)
                        && _worksheet._commentsStore.Exists(row, col, ref comment))
                    {
                            cell.Comment=comment;
                    }

                    if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Flags)
                        && _worksheet._flags.Exists(row, col, ref flag))
                    {
                            cell.Flag = flag;
                    }
                    copiedValue.Add(cell);
            }

            //Copy styles with no cell value
            var cses = new CellsStoreEnumerator<int>(_worksheet._styles, _fromRow, _fromCol, _toRow, _toCol);
            while (cses.Next())
            {
                    if (!_worksheet._values.Exists(cses.Row, cses.Column))
                    {
                            var row = Destination._fromRow + (cses.Row - _fromRow);
                            var col = Destination._fromCol + (cses.Column - _fromCol);
                            var cell = new CopiedCell
                            {
                                    Row = row,
                                    Column = col,
                                    Value = null
                            };

                            if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Formats)) {
                                i = cses.Value;
                                if (sameWorkbook) {
                                    cell.StyleID = i;
                                }
                                else {
                                    if (styleCashe.ContainsKey(i)) {
                                        i = styleCashe[i];
                                    }
                                    else {
                                        var oldStyleID = i;
                                        i = styles.CloneStyle(sourceStyles, i);
                                        styleCashe.Add(oldStyleID, i);
                                    }
                                    //Destination._worksheet._styles.SetValue(row, col, i);
                                    cell.StyleID = i;
                                }
                            }
                            copiedValue.Add(cell);
                    }
            }
            var copiedMergedCells = new Dictionary<int, ExcelAddress>();
            //Merged cells
            var csem = new CellsStoreEnumerator<int>(_worksheet.MergedCells._cells, _fromRow, _fromCol, _toRow, _toCol);
            while (csem.Next())
            {
                    if(!copiedMergedCells.ContainsKey(csem.Value))
                    {
                            var adr = new ExcelAddress(_worksheet.Name, _worksheet.MergedCells.List[csem.Value]);
                            if(this.Collide(adr)==eAddressCollition.Inside)
                            {                        
                                    copiedMergedCells.Add(csem.Value, new ExcelAddress(
                                            Destination._fromRow + (adr.Start.Row - _fromRow),
                                            Destination._fromCol + (adr.Start.Column - _fromCol),
                                            Destination._toRow + (adr.End.Row - _fromRow),
                                            Destination._toCol + (adr.End.Column - _fromCol)));
                            }
                            else
                            {
                                    //Partial merge of the address ignore.
                                    copiedMergedCells.Add(csem.Value, null);
                            }
                    }
            }

            Destination._worksheet.MergedCells.Delete(new ExcelAddressBase(Destination._fromRow, Destination._fromCol, Destination._fromRow+toRow, Destination._fromCol+toCol));

            if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Values)) 
                Destination._worksheet._values.Clear(Destination._fromRow, Destination._fromCol, toRow, toCol);
            if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Formulas))
                Destination._worksheet._formulas.Clear(Destination._fromRow, Destination._fromCol, toRow, toCol);
            if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Formats))
                Destination._worksheet._styles.Clear(Destination._fromRow, Destination._fromCol, toRow, toCol);
            if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Values) || _IsSet(pasteFlag, ExcelPasteSpecialFlags.Formulas))
                Destination._worksheet._types.Clear(Destination._fromRow, Destination._fromCol, toRow, toCol);
            if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Hyperlinks))
                Destination._worksheet._hyperLinks.Clear(Destination._fromRow, Destination._fromCol, toRow, toCol);
            if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Flags))
                Destination._worksheet._flags.Clear(Destination._fromRow, Destination._fromCol, toRow, toCol);
            if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Comments))
                Destination._worksheet._commentsStore.Clear(Destination._fromRow, Destination._fromCol, toRow, toCol);
         
            foreach(var cell in copiedValue)
            {
                if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Values))
                    Destination._worksheet._values.SetValue(cell.Row, cell.Column, cell.Value);

                if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Values) || _IsSet(pasteFlag, ExcelPasteSpecialFlags.Formulas))
                    if (cell.Type != null)
                    {
                            Destination._worksheet._types.SetValue(cell.Row, cell.Column, cell.Type);
                    }

                if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Formats))
                    if (cell.StyleID != null)
                    {
                            Destination._worksheet._styles.SetValue(cell.Row, cell.Column, cell.StyleID.Value);
                    }

                if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Formulas))
                    if (cell.Formula != null)
                    {
                            cell.Formula = UpdateFormulaReferences(cell.Formula.ToString(), Destination._fromRow - _fromRow, Destination._fromCol - _fromCol, 0, 0, true);
                            Destination._worksheet._formulas.SetValue(cell.Row, cell.Column, cell.Formula);
                    }

                if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Hyperlinks))
                    if (cell.HyperLink != null)
                    {
                            Destination._worksheet._hyperLinks.SetValue(cell.Row, cell.Column, cell.HyperLink);
                    }

                if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Comments))
                    if (cell.Comment != null)
                    {
                            //Destination._worksheet._commentsStore.SetValue(cell.Row, cell.Column, cell.Comment);
                    }

                if (_IsSet(pasteFlag, ExcelPasteSpecialFlags.Flags))
                    if (cell.Flag != null) {
                        Destination._worksheet._flags.SetValue(cell.Row, cell.Column, cell.Flag);
                    }
            }

            //Add merged cells
            foreach(var m in copiedMergedCells.Values)
            {
                    if(m!=null)
                    {
                            Destination._worksheet.MergedCells.Add(m, true);
                    }
            }
    }
    /*
        code intentionally removed
    */
}