The API also manages POI Word and PowerPoint documents, over time users have had more confidence.
Download Apache POI API
You can download Apache POI. Then, import the following .jar into your project:- poi
- poi-OOXML
- poi-OOXML-schemas
- xmlbeans
Create and write
The two main classes which processes the Excel file are:HSSFWorkbook: for Microsoft Excel 97 and 2003 files with xls extension.
XSSFWorkbook: for Microsoft Excel 2007 with .xlsx file extension.
The following code creates an excel file with the values of different types:
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class writeDemo{
public static void main(String[] args) {
//create a blanc document
XSSFWorkbook wb = new XSSFWorkbook();
//create a black sheet
Sheet sheet = wb.createSheet("new sheet");
//create a new row 0
Row row = sheet.createRow((short)0);
//create a new cell
Cell cell = row.createCell(0);
//insert value in the created cell
cell.setCellValue(1.4);
//add other cells with different types
/*int*/row.createCell(1).setCellValue(7);
/*int*/row.createCell(2).setCellValue(99);
/*string*/row.createCell(3).setCellValue("string");
/*boolean*/row.createCell(4).setCellValue(true);
FileOutputStream fos;
try {
fos= new FileOutputStream("newFile.xlsx");
wb.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
The date to be inserted and the current time, the date format is created as follows:
//insert value in cell F1
cell = row.createCell((short) 6);
cell.setCellValue(new Date());
XSSFCellStyle cellStyle = wb.createCellStyle();
XSSFDataFormat xssfDataFormat = wb.createDataFormat();
//create date and time format
cellStyle.setDataFormat(xssfDataFormat.getFormat("dd/mm/yyyy h:mm"));
cell.setCellStyle(cellStyle);
Text Formatting
Text formatting includes: font, size, Italic / Bold / Underline, color, background and alignment.
Here is an example of alignment that will be applied to the cell Date:
Here is an example of alignment that will be applied to the cell Date:
//Row high row.setHeightInPoints(20); //horizontal alignement cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //vertical alignement
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
Create new font
/*create a new font*/
Font font = wb.createFont();
//size: 13px
font.setFontHeightInPoints((short)13);
font.setFontName("Courier New");
font.setItalic(true);
font.setBold(true);
/*create a new style*/
CellStyle cs = wb.createCellStyle();
cs.setFont(font);
//apply the style to cell 3(D1)
row.getCell(3).setCellStyle(cs);
Background color
/*change the background color*/
XSSFCellStyle csColor = wb.createCellStyle();
csColor.setFillForegroundColor(new XSSFColor(new Color(194, 154, 250)));
csColor.setFillPattern(csColor.SOLID_FOREGROUND);
//apply to the cell 3
row.getCell(2).setCellStyle(csColor);
/*change font color*/
Font font = wb.createFont();
font.setColor((short)45);
CellStyle csCF = wb.createCellStyle();
csCF.setFont(font);
//apply style to cell 0
row.getCell(0).setCellStyle(csCF);
Merging cells
In this example, we will merge, center horizontally and vertically four cells B2, C2, B3 and C3 with the addMergedRegion method that takes as parameters the range of cells to be fused.
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class mergingCells{
public static void main(String[] args) throws FileNotFoundException {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("sheet1");
Row row = sheet.createRow((short) 1);
Cell cell = row.createCell((short) 1);
cell.setCellValue("testing merge cells");
sheet.addMergedRegion(new CellRangeAddress(
1, //first row B2
2, //last row B3
1, //first column C2
2 //last column C3
));
/*Center alignment*/
cell.getCellStyle().setAlignment((short)2);
cell.getCellStyle().setVerticalAlignment((short)1);
FileOutputStream fs = null;
try {
fs = new FileOutputStream("Mergingdemo.xlsx");
wb.write(fs);
fs.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
Using Formulas
Excel is primarily used in the calculation and use of sometimes complex formulas in cells result. Apache poi provides a very effective means to add and test cells with their formulas.The following code handles a simple calculation of the average 4 semesters. The formula is (A2 + B2 + C2 + D2) / 4.
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class FormulaDemo{
public static void main(String[] args) {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Average");
Row row = sheet.createRow((short) 0);
row.createCell(0).setCellValue("January");
row.createCell(1).setCellValue("February");
row.createCell(2).setCellValue("March");
row.createCell(3).setCellValue("April");
row.createCell(4).setCellValue("Average");
Row row1 = sheet.createRow((short) 1);
row1.createCell(0).setCellValue(4.5);
row1.createCell(1).setCellValue(15.4);
row1.createCell(2).setCellValue(3.4);
row1.createCell(3).setCellValue(6);
row1.createCell(4).setCellFormula("(A2+B2+C2+D2)/4");
try {
FileOutputStream out = new FileOutputStream(new File("formulademo.xlsx"));
wb.write(out);
out.close();
System.out.println("Le fichier excel a été créé avec succés");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
Evaluate and browse cells
To view the value of a cell, you must know its type. Apache poi provides FormulaEvaluator.evaluateFormulaCell class that checks if the cell contains a formula. If so, it evaluates and returns the type of the formula.
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
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 IterateDemo{
public static void main(String[] args) throws IOException {
FileInputStream fichier = new FileInputStream(new File("formuladomo.xlsx"));
//create workbook instance that refers to xlsx file
XSSFWorkbook wb = new XSSFWorkbook(fichier);
XSSFSheet sheet = wb.getSheetAt(0);
FormulaEvaluator formulaEvaluator =
wb.getCreationHelper().createFormulaEvaluator();
for (Row ligne : sheet) {//iterate rows
for (Cell cell : ligne) {//iterate columns
//cell type
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");
break;
}
}
System.out.println();
}
}
}
Execution:January February March April Average 4.5 15.4 3.4 6.0 7.325
For more informations read from apache documentation http://poi.apache.org/spreadsheet/quick-guide.html
How to deal with large excle files?
ReplyDeleteI need to read large file and add some rows and write it, can you please help me?