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.
