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 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

GemBox.Spreadsheet for Java is a component that enables you to read, write, edit and convert spreadsheet files from your applications using one simple API.

Download Buy