Copy and delete Excel sheet
GemBox.Spreadsheet for Java has been retired
Following example shows how to copy and delete worksheets.
One worksheet is used as the template for other worksheets.

import com.gembox.spreadsheet.*;
import java.time.DayOfWeek;
import java.time.LocalDateTime;
import java.util.Random;
class Program {
public static void main(String[] args) throws java.io.IOException {
// If using the Professional version, put your serial key below.
SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");
ExcelFile workbook = ExcelFile.load("%InputFileName%");
// Get template sheet.
ExcelWorksheet templateSheet = workbook.getWorksheet(0);
// Copy template sheet.
for (int i = 0; i < 4; i++)
workbook.getWorksheets().addCopy("Invoice " + (i + 1), templateSheet);
// Delete template sheet.
workbook.getWorksheets().remove(0);
LocalDateTime startTime = LocalDateTime.now();
// Go to the first Monday from today.
while (startTime.getDayOfWeek() != DayOfWeek.MONDAY)
startTime = startTime.plusDays(1);
Random rnd = new Random();
// For each sheet.
for (int i = 0; i < 4; i++) {
// Get sheet.
ExcelWorksheet worksheet = workbook.getWorksheet(i);
// Set some fields.
worksheet.getCell("J5").setValue(14 + i);
worksheet.getCell("J6").setValue(LocalDateTime.now());
worksheet.getCell("J6").getStyle().setNumberFormat("m/dd/yyyy");
worksheet.getCell("D12").setValue("ACME Corp");
worksheet.getCell("D13").setValue("240 Old Country Road, Springfield, IL");
worksheet.getCell("D14").setValue("USA");
worksheet.getCell("D15").setValue("Joe Smith");
worksheet.getCell("E18").setValue(startTime.toLocalDate().toString() + " until " + startTime.plusDays(11).toLocalDate().toString());
for (int j = 0; j < 10; j++) {
worksheet.getCell(21 + j, 1).setValue(startTime); // Set date.
worksheet.getCell(21 + j, 1).getStyle().setNumberFormat("dddd, mmmm dd, yyyy");
worksheet.getCell(21 + j, 4).setValue(rnd.nextInt(3) + 6); // Work hours.
// Skip Saturday and Sunday.
startTime = startTime.plusDays(j == 4 ? 3 : 1);
}
// Skip Saturday and Sunday.
startTime = startTime.plusDays(2);
worksheet.getCell("B36").setValue("Payment via check.");
}
workbook.save("Sheet Copying Deleting.%OutputFileType%");
}
}
See also
Next steps
Published: December 13, 2018 | Modified: December 19, 2022 | Author: Marek Turis