With the exponential increase in mobile devices, the ease of data accessibility has increased tenfold.
We can see all the data relevant to any domain on the mobile devices, for instance, the annual turnover of an organization, the number of employees who joined last month, etc. However, displaying the data on a mobile application is useful, although it becomes quite useless when we need to share the relevant data with the management or others. It is where exporting of data comes in handy and can be seen in most mobile applications today.
We want to create a ‘Contact’ application of our own as shown in the video below.
In this application:
- Initially, we will query the Contacts Content Provider in order to retrieve all the contacts present in the Android device.
- Next, we would export all the contacts into an Excel workbook (.xls format)
- Lastly, we will read data from the previously generated xls workbook and display the results in a recycler-view.
- As a bonus feature, we can also share the excel via the supported applications (eg: Nearby Share, Bluetooth etc.)
You can find the code for this application here.
However, we will only cover the following in this article:
- Downloading the jar file.
- Exporting jar as a dependency in Android Studio.
- Creating an Excel workbook.
- Importing data (Reading) from an Excel workbook.
- Exporting data (Writing) into an Excel Workbook.
By the end of this article, you will be able to generate the below excel workbook:
Without wasting any time further, let us dive into it right away!
Downloading the JAR file
As per the official documentation:
You can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97–2008).
So, not only can we use the POI for generating excel, it can be used for creating MS Word and MS Power point as well. However, it is beyond the scope of this article.
Exporting jar as a dependency in Android Studio
Next, we need to export the JAR as a dependency in Android Studio. I’ve depicted the same in a step-wise manner below:
Step 1: Navigate to the Project Structure on the left
Step 2: Switch to ‘Project’ form ‘Android’ by selecting the dropdown
Step 3: Select the ‘libs’ folder within the ‘app’ directory
Step 4: Copy the downloaded JAR and paste it into ‘libs’ directory
Step 5: (Most Important) Adding JAR as a library
Adding it as a library automatically handles adding and compiling the files (‘libs/library_name.jar’) in build.gradle. We don’t need to add it manually.
Thats it! You have now exported the JAR in Android Studio as a library.
Creating an Excel Workbook
In this section we will be covering a detailed walkthrough of the process of creating an excel workbook.
Before that, let’s familiarise ourselves with few excel terminologies:
We can try relating the terms in the following way:
- Workbook is a collection of sheets.
- Sheet is a collection of rows (and columns).
- Row is a collection of cells.
So, let’s begin!
Step 1: Creating a new ‘Workbook’
Step 2: Creating a new ‘Sheet’
Step 3: Creating a new ‘Row’
Step 4: Creating a new ‘Cell’ and assigning ‘style’ to it
- Initially, we declare a variable ‘cell’ of type ‘Cell’
- Next, we to create a ‘CellStyle’ which can be assigned to any cell (for example, the Header row in an excel sheet) and customise it depending on the requirements. Such as, setting the foreground colour, alignment etc.
- Next, we create a new ‘cell’ and assign it to a ‘Row’
- Lastly, we set value and style to the cell.
Combining Steps 1 to 4:
The above code will generate a single row having 4 cells, which will look something like this:
Before we proceed with the next steps, make sure that you have added the necessary permissions in the ‘Manifest’
Importing data (Reading) from an Excel Workbook
In this section, we will try to understand the process of reading data from an excel workbook.
By the end of this section, we will be able to read data from the excel workbook displayed below i.e, the exported contact list from an Android device (and let’s say, display it in an android device).
Step 1: Access Excel file from storage
Step 2: Create an Instance having reference to .xls file
Step 3: Fetch sheet at desired index from workbook
Step 4: Iterate through rows and cells in the sheet
- Iterate through each row in the sheet.
- Iterate each cell in a row using ‘row.cellIterator()’
- Check the cell type for each cell and format the data accordingly.
- Print the value (refer the GitHub link for the application here where i’ve stored data in a list)
Combining Steps 1 to 4:
Exporting data (Writing) into an Excel Workbook
In this section, we will understand the mechanism to export data from an Android application (let’s say, the list of all contacts in a mobile phone) into an excel workbook and storing it in external storage.
Well, this is really simple since we already looked into the mechanism of generating a workbook before. All we need to do here is read data from any data source (eg: List) and cast it appropriately into cells and rows in order to generate the excel sheet shown below:
However, the complete code for writing data into an excel can be found at the end of this section.
Step 1: Fill data from list into Excel
Step 2: Check if external storage is read-only
Step 3: Check if storage is available
Step 4: Store Excel in External Storage
Combining all the steps:
While using the above function in your code (let’s say in MainActivity) just do the following:
And that’s how we generate an Excel using ‘Apache POI’ library. Also, read and write data from it.
If you’re interested to see the entire code in action, feel free to clone the repository from here.