Documents for Excel, Java Edition Documentation
Features / Worksheet / Range Operations / Cut or Copy Cell Ranges
In This Topic
    Cut or Copy Cell Ranges
    In This Topic

    GcExcel Java enables users to cut or copy a cell or a range of cells from a specific area and paste it into another area within the same worksheet. To cut or copy data across multiple sheets, refer to Cut or Copy Across Sheets.

    You can refer to the following sections in order to cut or copy data from the cell range.

    Copy Cell Range

    You can copy a cell or a range of cells in the worksheet by calling the copy method of the IRange interface. In order to copy a single cell or a range of cells, specify the cell range to be copied, for example B3:D12.

    GcExcel Java provides the following different ways to use the copy method.

    Example Description
    copy(sheet.getRange["E5"]) This method copies data from cell range B3:D12 and pastes the data to cell E5 onwards.
    copy(sheet.getRange["E5:G14"]) This method copies data from cell range B3:D12 and pastes the data in cell range E5:G14. In case the range of cells copied does not fit into the destination cell range, the data is lost.

    In order to copy the cell range in a workbook, refer to the following example code:

    Java
    Copy Code
    // Initialize workbook
    Workbook workbook = new Workbook();
            
    // Fetch default worksheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    
    // Set data of PC
    worksheet.getRange("A2").setValue("PC");
    
    Object data = new Object[] { "Device", "Quantity", "Unit Price" };
    worksheet.getRange("A4:C4").setValue(data);
    
    Object otherData = new Object[][] { 
    { "T540p", 12, 9850 }, { "T570", 5, 7460 }, { "Y460", 6, 5400 },
    { "Y460F", 8, 6240 }, };
    worksheet.getRange("A5:C10").setValue(otherData);
    
    // Set style
    worksheet.getRange("A2").setRowHeight(30);
    worksheet.getRange("A2").getFont().setSize(20);
    worksheet.getRange("A2").getFont().setBold(true);
    worksheet.getRange("A4:C4").getFont().setBold(true);
    worksheet.getRange("A4:C4").getFont().setColor
    (com.grapecity.documents.excel.Color.GetWhite());
    worksheet.getRange("A4:C4").getInterior().setColor
    (com.grapecity.documents.excel.Color.GetLightBlue());
    worksheet.getRange("A5:C10").getBorders()
    .get(BordersIndex.InsideHorizontal)
    .setColor(com.grapecity.documents.excel.Color.GetOrange());
    worksheet.getRange("A5:C10").getBorders()
    .get(BordersIndex.InsideHorizontal)
    .setLineStyle(BorderLineStyle.DashDot);
    
    // Copy only style and row height from cells A2:C10
    worksheet.getRange("H1").setValue("Copy style and row height from previous cells.");
    worksheet.getRange("H1").getFont().setColor(com.grapecity.documents.excel.Color.GetRed());
    worksheet.getRange("H1").getFont().setBold(true);
    worksheet.getRange("A2:C10").copy(worksheet.getRange("H2"), EnumSet.of(PasteType.Formats));
    
    // Set data of mobile devices
    worksheet.getRange("H2").setValue("Mobile");
            
    // Object data = new Object[] {"Device", "Quantity", "Unit Price" };
    worksheet.getRange("H4:J4").setValue(data);
    
    Object otherDataRange = new Object[][] { 
    { "T540p", 12, 9850 }, { "T570", 5, 7460 }, { "Y460", 6, 5400 },
    { "Y460F", 8, 6240 }, };
    worksheet.getRange("H5:J10").setValue(otherDataRange);
            
    // Add new sheet
    IWorksheet worksheet2 = workbook.getWorksheets().add();
    
    // Copy only style of Cell A2:C10 to new sheet
    worksheet.getRange("A2:C10")
    .copy(worksheet2.getRange("A2"), EnumSet.of(PasteType.Formats));
    worksheet2.getRange("A3").setValue("Copy style from sheet1.");
    worksheet2.getRange("A3").getFont()
    .setColor(com.grapecity.documents.excel.Color.GetRed());
    worksheet2.getRange("A3").getFont().setBold(true);
    
    // Saving workbook to xlsx
    workbook.save("PasteOptionsEnhancements.xlsx", SaveFileFormat.Xlsx);

    Working With Paste Options

    Users can choose from several paste options while copying the data from the cell range. The PasteType enumeration can be used to work with multiple paste options as described in the table shared below.

    Option Description
    Default This option can be used to paste all the cell data to the destination range except the row heights and column widths.
    Values This option can be used to paste only the cell value to the destination.
    Formulas If you're working in a formula cell, this option can be used to paste the formula to the destination . However, for a non-formula cell, this option pastes the cell value to the destination.
    Formats This option can be used to paste formats.
    NumberFormats This option can be used to paste number formats.
    RowHeights This option can be used to paste the row height to the destination.
    ColumnWidths This option can be used to paste 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 in order to use the combination of paste options while copying data from the cell range in a workbook and paste it to the destination.

    Java
    Copy Code
    // Initialize workbook
    Workbook workbook = new Workbook();
            
    // Fetch default worksheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    
    // Set data of PC
    worksheet.getRange("A2").setValue("PC");
    
    Object data = new Object[] { "Device", "Quantity", "Unit Price" };
    worksheet.getRange("A4:C4").setValue(data);
    
    Object otherData = new Object[][] { 
    { "T540p", 12, 9850 }, { "T570", 5, 7460 }, { "Y460", 6, 5400 },
    { "Y460F", 8, 6240 }, };
    worksheet.getRange("A5:C10").setValue(otherData);
    
    // Set style
    worksheet.getRange("A2").setRowHeight(30);
    worksheet.getRange("A2").getFont().setSize(20);
    worksheet.getRange("A2").getFont().setBold(true);
    worksheet.getRange("A4:C4").getFont().setBold(true);
    worksheet.getRange("A4:C4").getFont().setColor
    (com.grapecity.documents.excel.Color.GetWhite());
    worksheet.getRange("A4:C4").getInterior().setColor
    (com.grapecity.documents.excel.Color.GetLightBlue());
    worksheet.getRange("A5:C10").getBorders()
    .get(BordersIndex.InsideHorizontal)
    .setColor(com.grapecity.documents.excel.Color.GetOrange());
    worksheet.getRange("A5:C10").getBorders()
    .get(BordersIndex.InsideHorizontal)
    .setLineStyle(BorderLineStyle.DashDot);
    
    // Copy only style and row height from cells A2:C10
    worksheet.getRange("H1").setValue("Copy style and row height from previous cells.");
    worksheet.getRange("H1").getFont().setColor(com.grapecity.documents.excel.Color.GetRed());
    worksheet.getRange("H1").getFont().setBold(true);
    worksheet.getRange("A2:C10").copy(worksheet.getRange("H2"), EnumSet.of(PasteType.Formats));
    
    // Set data of mobile devices
    worksheet.getRange("H2").setValue("Mobile");
            
    // Object data = new Object[] {"Device", "Quantity", "Unit Price" };
    worksheet.getRange("H4:J4").setValue(data);
    
    Object otherDataRange = new Object[][] { 
    { "T540p", 12, 9850 }, { "T570", 5, 7460 }, { "Y460", 6, 5400 },
    { "Y460F", 8, 6240 }, };
    worksheet.getRange("H5:J10").setValue(otherDataRange);
            
    // Add new sheet
    IWorksheet worksheet2 = workbook.getWorksheets().add();
    
    // Copy only style of Cell A2:C10 to new sheet
    worksheet.getRange("A2:C10")
    .copy(worksheet2.getRange("A2"), EnumSet.of(PasteType.Formats));
    worksheet2.getRange("A3").setValue("Copy style from sheet1.");
    worksheet2.getRange("A3").getFont()
    .setColor(com.grapecity.documents.excel.Color.GetRed());
    worksheet2.getRange("A3").getFont().setBold(true);
    
    // Saving workbook to xlsx
    workbook.save("PasteOptionsEnhancements.xlsx", SaveFileFormat.Xlsx);

    Cut Cell Range

    You can cut a cell or a range of cells in a worksheet by calling the cut method of the IRange interface. To cut a cell or a range of cells, specify the cell range that you want to move, for example B3:D12.

    GcExcel Java provides the following different ways to use the cut method.

    Example Description
    cut(sheet.getRange["E5"]) This method cuts the data from cell range B3:D12 and pastes the data to cell E5 onwards.
    cut(sheet.getRange["E5:G14"]) This method cuts the data from cell range B3:D12 and pastes the data in cell range E5:G14. In case the range of cells cut does not fit into the destination cell range, the data is lost.

    Refer to the following example code to cut a range of cells in the workbook.

    Java
    Copy Code
    IRange range1 = worksheet2.getRange("E5");
            
    // Cut the data of the range of cell
    worksheet.getRange("B3:D12").cut(range1);
            
    // OR
    IRange range1 = worksheet2.getRange("E5;G14");
    worksheet.getRange("B3:D12").cut(range1);
            

    See Also