Dependencies
First, download the dependency files as mentioned in the Introduction to Apache POI For Manipulation of MS Office Documents with Java tutorial. After downloading the libraries, you have to add them to JAVA_PATH. Alternatively, you can create an Eclipse project and add them to the project as a library. You are being suggested to add the libraries as an internal library instead of an external library.
If you are not familiar with library linking, follow these steps.
Create a directory, lib, in your project. Paste the jars in the lib directory. Right-click on the project, select Properties, go to Java Build Path, click on Add Jars and browse the lib directory you have created. It's done!
If you are not familiar with library linking, follow these steps.
Create a directory, lib, in your project. Paste the jars in the lib directory. Right-click on the project, select Properties, go to Java Build Path, click on Add Jars and browse the lib directory you have created. It's done!
Explanation of Source Code
- Create an instance of XSSFWorkbook class for .xlsx file.
- Create an instance of XSSFSheet. A new sheet can be created by createSheet() method of class XSSFWorkbook, by passing name of the sheet as an argument.
- Prepare content to be written in a sheet using Map
- Now create rows using createRow() method and create cells using createCell() methods as per your requirements.
- Create an instance of FileOutputStream for Demo.xlsx file.
- Finally, write the content.
Try the following code
package com.t4b.demo.poi;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelFormulaDemo {
public static void main(String[] args) {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
XSSFSheet xssfSheet = xssfWorkbook.createSheet("Calculate Simple Interest");
Row header = xssfSheet.createRow(0);
header.createCell(0).setCellValue("Pricipal");
header.createCell(1).setCellValue("RoI");
header.createCell(2).setCellValue("Time");
header.createCell(3).setCellValue("Interest (P r t)");
Row dataRow = xssfSheet.createRow(1);
dataRow.createCell(0).setCellValue(14500d);
dataRow.createCell(1).setCellValue(9.25);
dataRow.createCell(2).setCellValue(3d);
dataRow.createCell(3).setCellFormula("A2*B2*C2");
try {
FileOutputStream fileOutputStream = new FileOutputStream(new File("formulaDemo.xlsx"));
xssfWorkbook.write(fileOutputStream);
fileOutputStream.close();
readSheetWithFormula();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void readSheetWithFormula() {
try {
FileInputStream fileInputStream = new FileInputStream(new File("formulaDemo.xlsx"));
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
FormulaEvaluator formulaEvaluator = xssfWorkbook.getCreationHelper().createFormulaEvaluator();
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (formulaEvaluator.evaluateInCell(cell).getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_FORMULA:
break;
}
}
System.out.println();
}
fileInputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Posts
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.