How to create multiple sheets in a single Excel file

Data Pipeline lets you read, write, and convert Excel files using a very simple API. This post will show you how to create Excel files containing more than one work sheet or tab.

Updated: July 2021

You’ve already seen how to import and export Excel files from your RESTful Java web applications in our previous posts.

We’ll keep this example simple and use hard-coded data instead of reading from a database, reading from a JSON webservice, or reading a flat fixed-width file.

Download Data Pipeline

1. Create the source data

For our input data set, we will use a few Canadian province and US state population statistics.

private static RecordList getCanadianProvinces() {
    RecordList list = new RecordList();
    list.add(new Record()
	.setField("Province", "Ontario")
	.setField("Population", 12851821)
	.setField("Population Growth", 5.7));
    
    list.add(new Record()
	.setField("Province", "Quebec")
	.setField("Population", 7903001)
	.setField("Population Growth", 4.7));
    
    list.add(new Record()
	.setField("Province", "British Columbia")
	.setField("Population", 4400057)
	.setField("Population Growth", 13.1));
    
    list.add(new Record()
	.setField("Province", "Alberta")
	.setField("Population", 3645257)
	.setField("Population Growth", 10.9));
    
    list.add(new Record()
	.setField("Province", "Manitoba")
	.setField("Population", 1208268)
	.setField("Population Growth", 3.6));
    
    return list;
}

private static RecordList getUsStates() {
    RecordList list = new RecordList();
    list.add(new Record()
	.setField("State", "California")
	.setField("Population", 38802500)
	.setField("Population Growth", 9.99));
    
    list.add(new Record()
	.setField("State", "Texas")
	.setField("Population", 26956958)
	.setField("Population Growth", 20.59));
    
    list.add(new Record()
	.setField("State", "Florida")
	.setField("Population", 19893297)
	.setField("Population Growth", 17.64));
    
    list.add(new Record()
	.setField("State", "New York")
	.setField("Population", 19746227)
	.setField("Population Growth", 2.12));
    
    list.add(new Record()
	.setField("State", "Illinois")
	.setField("Population", 12880580)
	.setField("Population Growth", 3.31));
    
    return list;
}

private static RecordList getCanadianProvinces() {

RecordList list = new RecordList();

list.add(new Record()

.setField("Province", "Ontario")

.setField("Population", 12851821)

.setField("Population Growth", 5.7));

list.add(new Record()

.setField("Province", "Quebec")

.setField("Population", 7903001)

.setField("Population Growth", 4.7));

list.add(new Record()

.setField("Province", "British Columbia")

.setField("Population", 4400057)

.setField("Population Growth", 13.1));

list.add(new Record()

.setField("Province", "Alberta")

.setField("Population", 3645257)

.setField("Population Growth", 10.9));

list.add(new Record()

.setField("Province", "Manitoba")

.setField("Population", 1208268)

.setField("Population Growth", 3.6));

return list;

}

private static RecordList getUsStates() {

RecordList list = new RecordList();

list.add(new Record()

.setField("State", "California")

.setField("Population", 38802500)

.setField("Population Growth", 9.99));

list.add(new Record()

.setField("State", "Texas")

.setField("Population", 26956958)

.setField("Population Growth", 20.59));

list.add(new Record()

.setField("State", "Florida")

.setField("Population", 19893297)

.setField("Population Growth", 17.64));

list.add(new Record()

.setField("State", "New York")

.setField("Population", 19746227)

.setField("Population Growth", 2.12));

list.add(new Record()

.setField("State", "Illinois")

.setField("Population", 12880580)

.setField("Population Growth", 3.31));

return list;

}

2. Generate the Excel file

When working with Excel files, we use an in-memory ExcelDocument object to temporarily hold the data before saving to disk. If writing to a CSV file instead of Excel is an option, you’ll be able to reduce your memory use substantially. You’ll also improve performance and stream your data in real-time without waiting for it to be collected or batched in memory.

public static void main(String[] args) {
    ExcelDocument document = new ExcelDocument();

    DataReader reader;
    DataWriter writer;
    
    // Write to first sheet named "Canadian Provinces"
    reader = new MemoryReader(getCanadianProvinces());
    writer = new ExcelWriter(document).setSheetName("Canadian Provinces").setAutofitColumns(true);
    JobTemplate.DEFAULT.transfer(reader, writer);
    
    // Write to first sheet named "US States"
    reader = new MemoryReader(getUsStates());
    writer = new ExcelWriter(document).setSheetName("US States").setAutofitColumns(true);
    JobTemplate.DEFAULT.transfer(reader, writer);

    // Save the entire Excel file/workbook to disk
    document.save(new File("population.xlsx"));
}

public static void main(String[] args) {

ExcelDocument document = new ExcelDocument();

DataReader reader;

DataWriter writer;

// Write to first sheet named "Canadian Provinces"

reader = new MemoryReader(getCanadianProvinces());

writer = new ExcelWriter(document).setSheetName("Canadian Provinces").setAutofitColumns(true);

JobTemplate.DEFAULT.transfer(reader, writer);

// Write to first sheet named "US States"

reader = new MemoryReader(getUsStates());

writer = new ExcelWriter(document).setSheetName("US States").setAutofitColumns(true);

JobTemplate.DEFAULT.transfer(reader, writer);

// Save the entire Excel file/workbook to disk

document.save(new File("population.xlsx"));

}

This particular example uses MemoryReader to retrieve the data and transfer it to specific worksheets in the Excel document. It then saves the Excel workbook with all the sheets to disk.

All Data Pipeline transformers, filters, readers, writers, and converters extend either DataReader or DataWriter. This way you can substitute any endpoint with another — like swapping Excel for JDBC. It also lets you stack operators on top of endpoints and on top of other operators.

We hope that was useful.

More Excel examples

Data Pipeline is documented with hundreds of useful examples available for developers on our website. Here are some examples you may find interesting:

Read from an Excel File

Read BigDecimal and BigInteger from an Excel file

Use Streaming Excel for Reading or Writing

Read from CSV And Writer to Excel

Happy coding!

Download Data Pipeline