Document Solutions for Excel, Java 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 setAllowPasteHiddenRange method 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.

    Users can also combine the two different paste options. For instance - if users want to paste values and number formats concurrently in the worksheet, then they can use combinations like :

    PasteType.Values, PasteType.NumberFormats
    PasteType.Formulas, PasteType.NumberFormats

    Similarly other paste options can also be combined with each other.

    Refer to the following example code to ignore and paste data in a hidden range.

    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    Object[][] data = new Object[][]{
    {"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
    {"Richard", "New York", new GregorianCalendar(1968, 6, 8), "Blue", 67, 165},
    {"Nia", "New York", new GregorianCalendar(1972, 7, 3), "Brown", 62, 134},
    {"Jared", "New York", new GregorianCalendar(1964, 3, 2), "Hazel", 72, 180},
    {"Natalie", "Washington", new GregorianCalendar(1972, 8, 8), "Blue", 66, 163},
    {"Damon", "Washington", new GregorianCalendar(1986, 2, 2), "Hazel", 76, 176},
    {"Angela", "Washington", new GregorianCalendar(1993, 2, 15), "Brown", 68, 145}
    //Weight less than 80.
    worksheet.getRange("A1:F7").autoFilter(4, "<72");
    //Copy range and ignore hidden range.
    IWorksheet worksheet2 = workbook.getWorksheets().add();
    PasteOption pasteOption = new PasteOption();
    worksheet.getRange("A1:F7").copy(worksheet2.getRange("A1:F7"), pasteOption);
    //Copy range and contain hidden range.
    IWorksheet worksheet3 = workbook.getWorksheets().add();
    //Ignore pasting data in hidden range and use PasteType options
    IWorksheet worksheet4 = workbook.getWorksheets().add();
    PasteOption pasteOption2 = new PasteOption();
    worksheet.getRange("A1:F6").copy(worksheet4.getRange("A1:F7"), pasteOption2);
    //save to an excel file"CopyAndIgnoreHiddenRange.xlsx");