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.

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

We’ll use a few Canadian and US province/state population stats for the input data set.

 

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 you can write to a CSV file instead of Excel, you’ll be able to significantly reduce your memory usage down to the size of a single record.  You’ll also be able to improve performance and stream your data in real-time without waiting for it to be collected (or batched) in memory.

This example uses MemoryReader to retrieve the province/state 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 readers, writers, transformers, filters, and converters extend either DataReader or DataWriter.  This lets you replace any endpoint with another — like swapping Excel for JDBC.  It also lets you add operators on top of endpoints and on top of other operators.

 

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