Create a Simple Java/Maven Project to Read Excel

This tutorial will take you through how to use Java to read an Excel sheet and output its data.

Prerequisites:

• Java
• Maven
• Eclipse

Tutorial:

1. Open Eclipse
2. Create a new Maven project:
   1. File → New → Other → Maven Project
   2. Next
   3. Check “create a simple project”
   4. Next
   5. Give it a Group ID and Artifact ID
   6. Finish
3. Add dependencies to the pom file for Apache POI-ooxml. The code for dependencies can be found at https://mvnrepository.com/ . Search "apache poi" and make sure to get the one with ooxml. (Note that you will need to surround the dependency in the pom file with <dependencies></dependencies>.)
4. Under src/test/java, create a new class:
   1. Right-click on src/test/java
   2. New → Class
   3. Give it a name
   4. Check “public static main void” (if you miss this step, there’s another way to do it I’ll mention later)
   5. Click Finish
5. Right-click on the class, and select “Properties” (you may have to scroll down)
6. Click the button at the end of the “Location” line to open the directory in Explorer
7. In that directory, create an Excel file (make sure it’s .xlsx)
8. Fill the Excel file with data. For this tutorial, I am inputting this:

   Pos.	Planet	Type
   1	Mercury	Rocky
   2	Venus	Rocky
   3	Earth	Rocky
   4	Mars	Rocky
   5	Jupiter	Gas giant
   6	Saturn	Gas giant
   7	Uranus	Ice giant
   8	Neptune	Ice giant

9. If you don’t have a main method in your java class, you can create one by typing main, then ctrl space, then selecting the main method.
10. In the main method of your java class, create a String variable to hold the path and filename of your excel sheet (such as “./src/test/java/ExcelData.xlsx”). Note that “./” can be used to reference the project’s directory. String excelPath = "./src/test/java/ExcelData.xlsx";
11. Initialize the excel workbook, using the String you created for the path: XSSFWorkbook workbook = new XSSFWorkbook(excelPath);
12. Hover over XSSFWorkbook and import it from apache poi.
13. Hover over again and surround with try/catch. Everything else we do will be within the try block.
14. Get the first sheet in the excel file, using index 0: XSSFSheet sheet = workbook.getSheetAt(0);
15. Hover over XSSFSheet and import it.
16. Now, let’s get the total number of rows and store it into an integer variable: int totalRows = sheet.getPhysicalNumberOfRows();
17. And we’ll get the number of columns, assuming the header row (the row with index 0) has a label for each used column: int totalCols = sheet.getRow(0).getLastCellNum();
18. To print everything out to the console, let’s create a String: String text = "";
19. Now, we want to loop through all the rows and columns. We’ll start by looping through the rows: for(int row = 0; row < totalRows; row++) {} This will increment the variable “row” from 0 to totalRows.
20. To make the output pretty, let’s start each row with a pipe, a tab, and a space: text = "|\t ";
21. If you don’t trust the rows to all have the same number of columns, you can move the “totalCols” line here, using your row variable as the index instead of 0, such as: int totalCols = sheet.getRow(row).getLastCellNum();
22. Now we need to cycle through each column to get the cell data. We can do this with another for loop: for (int col = 0; col < totalCols; col++) {}
23. To be able to read the data regardless of its type (String, integer, etc.), let’s use a data formatter: DataFormatter formatter = new DataFormatter ();
24. Hover over DataFormatter to import it.
25. Now we can extract the data into an object, using “row” and “col” as our indexes: Object value = formatter.formatCellValue(sheet.getRow(row).getCell(col));
26. Let’s concatenate this onto our string, followed by another space, tab, pipe, and space. Note that we’ll need to convert the Object to a string, with .toString(): text += value.toString() + " \t| "; (If you use the same data in your Excel file as I did, it will look best with two spaces before the tab.)
27. Now, outside the column “for” loop (but still within the row loop), print out the text line we’ve created: System.out.println(text);
28. And we’re done! Running it should output the excel data into the console.

Note: Eclipse might complain that “workbook” is never closed. If you want, you can fix this by adding workbook.close(); to the end of the “try” block, but this will throw errors at run time if you have the file open in Excel.

Your final file should look something like this: (I have commented out the close command, as well as the original totalCols line.)

-- SummerDale - 07 Oct 2020