Document Solutions for Excel, .NET Edition | Document Solutions
Features / Worksheet / Range Operations / Paste or Ignore Data in Hidden Range
In This Topic
    Paste or Ignore Data in Hidden Range
    In This Topic

    DsExcel allows you to choose whether to copy and paste the data in a hidden range. The parameters of overloaded Copy method can be used to specify the destination where the copied data needs to be pasted, whether to paste the data in a hidden range and various paste types.

    The pasteOption parameter of Copy method belongs to the PasteOption class. This class provides the AllowPasteHiddenRange property which if true, will paste the data in a hidden range to the destination, else will ignore the hidden range. The default value is true.

    The PasteOption class also provides PasteType property which can be used to specify various paste types by setting it to any PasteType enumeration value.

    The below table explains different options which can be used to specify the paste type using PasteType enumeration:

    Option Description
    Default Pastes all the cell data to the destination range except the row heights and column widths.
    Values Pastes only the cell value to the destination.
    Formulas If you're working in a formula cell, it pastes the formula to the destination . However, for a non-formula cell, it pastes the cell value to the destination.
    Formats Pastes formats.
    NumberFormats Pastes number formats.
    RowHeights Pastes the row height to the destination.
    ColumnWidths Pastes the column width to the destination.

    You can also combine two different paste type options. For example, if you want to paste values and number formats concurrently in a worksheet, you can use any of the below combinations: 

    PasteType.Values | PasteType.NumberFormats 
    PasteType.Formulas | PasteType.NumberFormats

    Refer to the following example code to ignore and paste data in a hidden range. It also uses the combination of paste options while copying and pasting data.

    Copy Code
    var workbook = new Workbook();
    IWorksheet worksheet = workbook.Worksheets[0];
    object[,] data = new object[,]{
    {"Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165},
    {"Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134},
    {"Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180},
    {"Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163},
    {"Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176},
    {"Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145}
    worksheet.Range["A1:F6"].Value = data;
    worksheet.Range["A:F"].ColumnWidth = 15;
    worksheet.Range["1:1"].Hidden = true;
    worksheet.Range["3:3"].Hidden = true;
    worksheet.Range["5:5"].Hidden = true;
    //Ignore pasting data in hidden range
    var worksheet2 = workbook.Worksheets.Add();
    worksheet.Range["A1:F6"].Copy(worksheet2.Range["A1:F6"], new PasteOption { AllowPasteHiddenRange = false });
    //Paste data in hidden range
    var worksheet3 = workbook.Worksheets.Add();
    //Ignore pasting data in hidden range and use PasteType options
    var worksheet4 = workbook.Worksheets.Add();
    worksheet.Range["A1:F6"].Copy(worksheet4.Range["A1:F6"], new PasteOption { AllowPasteHiddenRange = false, PasteType = PasteType.ColumnWidths | PasteType.Values });
    //save to an excel file