Using java to realize the function of copying excel worksheet

Time:2021-6-8

This paper summarizes the methods of how Java copies excel worksheets

1. Copy worksheet

1.1 copy worksheet in the same workbook

1.2 copying worksheets between different workbooks

2. Copy the specified cell data

For the replication method copy (), here is a simple table, which contains different application requirements for data replication

method

explain

copyFrom(Worksheet worksheet)

Copy data from source sheet

copy(CellRange sourceRange, CellRange destRange)

Copy source data to target data range

copy(CellRange sourceRange, CellRange destRange, Boolean copyStyle)

When copying the source data to the target data range, do you want to copy the source data style

copy(CellRange sourceRange, Worksheet worksheet, int destRow, int destColumn)

Copies the source data to the specified rows and columns in the destination worksheet

copy(CellRange sourceRange, Worksheet worksheet, int destRow, int destColumn, Boolean copyStyle)

When copying source data to specified rows and columns in the target worksheet, do you want to copy the source data style

copy(CellRange sourceRange, CellRange destRange, Boolean copyStyle, Boolean updateReference, Boolean ignoreSize)

When copying the source data to the target data range, do you want to copy the source data style, update the reference, and ignore the size

copy(CellRange sourceRange, Worksheet worksheet, int destRow, int destColumn, boolean copyStyle, boolean undateReference)

When copying the source data to the specified row and column in the target worksheet, do you want to copy the source data style and update the reference

copy(CellRange sourceRange, CellRange destRange, boolean copyStyle, boolean updateReference, boolean ignoreSize, boolean copyShape)

When copying the source data to the target data range, do you want to copy the source data style, update the reference, ignore the size, and copy the shape

The method in this paper uses free spire.xls for Java, which can download the package from the official website. After decompression, the jar file in the Lib folder can be imported into the Java program; Or download and import jars through Maven warehouse. You can refer to the tutorial for Maven warehouse path configuration and dependency addition. The import effect is as follows:

 

Java code examples

[example 1] copy in the same workbook

import com.spire.xls.*;

public class Copy1 {
 public static void main(String[] args) {
  //Loading documents
  Workbook wb = new Workbook();
  wb.loadFromFile("test.xlsx");

  //Get the first sheet
  Worksheet sheet0 = wb.getWorksheets().get(0);

  //Get the third sheet, name it, and copy the contents of the first sheet to it
  Worksheet sheet2 = wb.getWorksheets().get(2);
  sheet2.setName("Copiedsheet");
  sheet2.copyFrom(sheet0);

  //Save document
  wb.saveToFile("Copy1.xlsx",FileFormat.Version2013);
 }
}

Copy effect:

 

[example 2] copy between different workbooks

import com.spire.xls.*;

public class Copy2 {
 public static void main(String[] args) {
  //Load document 1 and get the first slide
  Workbook wb1 = new Workbook();
  wb1.loadFromFile("test1.xlsx");
  Worksheet sheet1 = wb1.getWorksheets().get(0);

  //Load document 2, get the third slide, and copy the contents of the worksheet in document 1 to the worksheet
  Workbook wb2 = new Workbook();
  wb2.loadFromFile("test2.xlsx");
  Worksheet sheet2 = wb2.getWorksheets().get(2);
  sheet2.setName("Copied");
  sheet2.copyFrom(sheet1);

  //Save document
  wb2.saveToFile("Copy2.xlsx",FileFormat.Version2013);
 }
}

Copy effect:

[example 3] copy cell data range

import com.spire.xls.*;

public class CopyRange {
 public static void main(String[] args) {
  //Loading documents
  Workbook wb = new Workbook();
  wb.loadFromFile("test.xlsx");

  //Get the first sheet
  Worksheet sheet = wb.getWorksheets().get(0);

  //Copies data in a specified range of cells
  CellRange range1 = sheet.getCellRange(8,1,8,7);
  CellRange range2 = sheet.getCellRange(11,1,11,7);
  sheet.copy(range1,range2,true);

  //Save document
  wb.saveToFile("CopyRange.xlsx",FileFormat.Version2013);
 }
}

Copy result:

summary

The above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support to developer.