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.
| Java |
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} }; worksheet.getRange("A1:F7").setValue(data); worksheet.getRange("A:F").setColumnWidth(15); //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(); pasteOption.setAllowPasteHiddenRange(false); worksheet.getRange("A1:F7").copy(worksheet2.getRange("A1:F7"), pasteOption); //Copy range and contain hidden range. IWorksheet worksheet3 = workbook.getWorksheets().add(); worksheet.getRange("A1:F7").copy(worksheet3.getRange("A1:F7")); //Ignore pasting data in hidden range and use PasteType options IWorksheet worksheet4 = workbook.getWorksheets().add(); PasteOption pasteOption2 = new PasteOption(); pasteOption2.setAllowPasteHiddenRange(false); pasteOption2.getPasteType().add(PasteType.ColumnWidths); pasteOption2.getPasteType().add(PasteType.Values); worksheet.getRange("A1:F6").copy(worksheet4.getRange("A1:F7"), pasteOption2); //save to an excel file workbook.save("CopyAndIgnoreHiddenRange.xlsx"); | |
