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.

 

1. Create the source data

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

 

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.

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!

About Dele Taylor

We make Data Pipeline — a lightweight ETL framework for Java. Use it to filter, transform, and aggregate data on-the-fly in your web, mobile, and desktop apps. Learn more about it at northconcepts.com.

One thought on “How to create multiple sheets in a single Excel file

Leave a Reply

Your email address will not be published. Required fields are marked *
You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">