Java - Microsoft Office

We all know about Microsoft Office products and its capabilities. For a developer, he/she uses Microsoft Word for document purpose like design specifications, technical specification etc., Microsoft Excel for reporting and Microsoft Powerpoint for presentations. Can a developer write code to manipulate data in Microsoft Office Documents? Or do the Microsoft Office products have the extensibility to use with programming language like Java?

 

Apache POI has the solution, it’s the Java API for Microsoft Documents. In this post, we will look a sample code to manipulate data in Microsoft Excel using Java.

For the Java project, we create a maven java project by skipping the archetype and add the following dependency in the pom.xml of the project,

  

ArtifactId version is 3.12 but 3.15 version is available now but there are some methods deprecated so, i am comfortable with using 3.12 version.

Excel Task - Well when I was studying Microsoft Excel in 2001, we usually do an exercise like writing an excel function to grade marks. Here is a sample below,

So, my java code have to read an Excel File and have to grade Marks value by writing back to Excel File. The below image depicts the reading file data,

The whole code is available at GitHub   https://github.com/meharuban/ApachePOIExcel

I will explain some fragments of the code,

private static final String FileName = "Grade_Report.xlsx";

Excel file name to be read and write.

FileInputStream excelFile = new FileInputStream(new File(FileName));

Workbook workbook = new XSSFWorkbook(excelFile);

Sheet datatypeSheet = workbook.getSheetAt(0);

Iterator iterator = datatypeSheet.iterator();

The above code reads the Microsoft Excel WorkBook file and reads the first sheet.

The Grading happens here,

            for (StudentModel model : modelList) {

                                    if (model.getMarks() > 75) {

                                                model.setGrade("A");

                                    } else if (model.getMarks() > 50) {

                                                model.setGrade("B");

                                    } else if (model.getMarks() > 30) {

                                                model.setGrade("C");

                                    } else {

                                                model.setGrade("F");

                                    }

                                    modelGrade.add(model);

                        }

Finally writing back to Excel File,

FileOutputStream outputStream = new FileOutputStream(FileName);

workbook.write(outputStream);

workbook.close();

Hope everyone will understand the code because it’s simple and not much logic here. I would like to write more on Java and JavaScript. Please expect future posts.