The world’s leading publication for data science, AI, and ML professionals.

Here’s How You Can Auto-Adjust Your Datatable Range in Excel with Java

A step-by-step illustration of using Apache Poi 3.15 JAR lib

As some countries are in midst of transitioning into an ❝Endemic❞ state, the past 2 years of dealing with the Coronavirus pandemic has inevitably flagged out much room for improvement in both information transfer and current inefficiency of data flows within the public healthcare sector. Hence, it didn’t come as a surprise to me when an influx of requests from my company’s leaders had flooded my email inbox almost immediately after the ❝Pandemic❞ state of things had settled down at the place where I reside. Long story short, I was tasked to revamp and introduce automation within the current workflow process of manually updating & compiling Excel reports on a regular basis.

Taking into consideration that, unlike many other corporate firms which strive to constantly innovate and reinforce a technical culture, my workplace on the other hand does not currently have the required setups available to run overly sophisticated technical solutions. Therefore, this eventually led me to make the instinctive choice of implementing my final deliverable in the form of a Java application due to the Programming language’s platform-independent property.


While my previous article was primarily focused on Excel & Outlook Data Extraction in Java programming language (with the exact same aforementioned constraints taken into account):

Extracting Data from Excel and Outlook Files with Java

This article instead aims to showcase yet another capability of Apache POI – which is an open-sourced Java library to manipulate Microsoft Office documents. More specifically, I plan to demonstrate 1 of its lesser known capabilities i.e. The Auto-adjustment of Datatable range(s) embedded within Microsoft Excel spreadsheets.

Points to note:

  • This feature is specific to excel files with the extension (.xlsx) only. This is unlike Part I where the functionality is applicable to both .xlsx and .xls extensions.
  • The list of Java library dependencies used are no different from Part I (i.e. the versions of all JAR dependencies are consistent as well):
Image by Author | List of Java libraries used to read & write to Microsoft Excel File(s) | Note that the specific version of [apache poi](https://poi.apache.org/) used is 3.15. Different versions of apache poi have varying versions for its dependencies.
Image by Author | List of Java libraries used to read & write to Microsoft Excel File(s) | Note that the specific version of [apache poi](https://poi.apache.org/) used is 3.15. Different versions of apache poi have varying versions for its dependencies.

Use-Case Explanation:

To minimise the tedious, manual updates of excel reports, a common request I have received from users at my workplace is to append and input incoming records into the same Excel Datatable on a regular basis.

Image by Author | An example to illustrate a Datatable rendered in Excel | Note that the the table range reads $A$1:$J$31. Hence new records appended after row no. 31 onwards are not input into the datatable.
Image by Author | An example to illustrate a Datatable rendered in Excel | Note that the the table range reads $A$1:$J$31. Hence new records appended after row no. 31 onwards are not input into the datatable.

Part I. Appending new incoming data rows

In my specific use-case, since the incoming data records are specified in CSV format, the Java code I implemented reads in a CSV File and appends to the first spreadsheet detected embedded within the selected excel file:

To enable users to run this easily, it has been incorporated into a Java Swing application (the application is the same application built and illustrated in the previous article. I highly encourage others to view it as this article is a direct follow-up of this) as illustrated below:

Screenshot by Author | In the [Update Datatable in Excel] tab, the first part of the application module enables users to select an excel file to update and another CSV file which contains the latest data records
Screenshot by Author | In the [Update Datatable in Excel] tab, the first part of the application module enables users to select an excel file to update and another CSV file which contains the latest data records
Screenshot by Author | On selection of [Append Data >>], the application reads in the 2 file inputs. The data records from the CSV is successfully appended to the excel file. This is depicted above where the file logs output the message "Data is appended".
Screenshot by Author | On selection of [Append Data >>], the application reads in the 2 file inputs. The data records from the CSV is successfully appended to the excel file. This is depicted above where the file logs output the message "Data is appended".

Part II. Updating the Datatable range

While the new data records have been included in the excel sheet in part I, the range of data read by the excel table remains unchanged. Hence, pivot tables, charts etc. which render their data based on the same Datatable remains unchanged as well.

To reflect the latest data range, the following code snippet proceeds to read in the Datatable range of an excel file. In the event the last row number of the excel sheet is greater than the Datatable’s current range, the table shall extend its range to include the last row number of the spreadsheet.

Similar to before, this functionality is incorporated into the Java Swing application available at my GitHub. Users are able to input multiple excel files at once for all Datatables to be updated:

Screenshot by Author | Note that the output of the application has a table datatable range inclusive of the records which were previously appended to the file
Screenshot by Author | Note that the output of the application has a table datatable range inclusive of the records which were previously appended to the file

The following is an illustration of the steps taken to run the Java application:

Screenshot by Author | The excel file(s) with the appended data records are selected. Upon updating the table range, the app prompts the user to save an output ZIP archive which contains all the respective updated excel files.
Screenshot by Author | The excel file(s) with the appended data records are selected. Upon updating the table range, the app prompts the user to save an output ZIP archive which contains all the respective updated excel files.

If you would like a copy of the application it is currently available at my GitHub. The previous Runnable JAR application is now renamed to be "DataDocUtility_v1" while the JAR application being referred to in this article is stated to be "DataDocUtility_v2". Just double click it and the Java Swing GUI will display itself. Just in case you forgot, the 1st article is available at 😛 :

Extracting Data from Excel and Outlook Files with Java

In addition, all sample data files and output files used in this demonstration are available in my GitHub repository along with the source code as well. Feel free to fork the code/tweak it for your own use-cases ☺


Many thanks for reading and please follow me on Medium if you have found this content useful. Would really appreciate it! 😃


Related Articles