Copy and delete Excel sheet

Following example shows how to copy and delete worksheets.

One worksheet is used as the template for other worksheets.

Excel worksheet copied with GemBox.Spreadsheet for Java
Screenshot of Excel worksheet copied with GemBox.Spreadsheet for Java
Upload your file (Drag file here)
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 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%");
    }
}

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Spreadsheet for Java yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.