Apachepoitutorial

Apache POI Tutorial
Apache POI is a popular API that allows programmers to create, modify, and
display MS Office files using Java programs. It is an open source library
developed and distributed by Apache Software Foundation to design or
modify Microsoft Office files using Java program. The name POI was
originally an acronym for Poor Obfuscation Implementation, referring
humorously to the fact that the file formats seemed to be deliberately
obfuscated, but poorly, since they were successfully reverse-engineered.

• HSSF (Horrible SpreadSheet Format) – reads and writes Microsoft Excel
(XLS) format files.
• XSSF (XML SpreadSheet Format) – reads and writes Office Open XML
(XLSX) format files.
• HPSF (Horrible Property Set Format) – reads “Document Summary”
information from Microsoft Office files.
• HWPF (Horrible Word Processor Format) – aims to read and write
Microsoft Word 97 (DOC) format files.
• HSLF (Horrible Slide Layout Format) – a pure Java implementation for
Microsoft PowerPoint files.
• HDGF (Horrible DiaGram Format) – an initial pure Java implementation
for Microsoft Visio binary files.
• HPBF (Horrible PuBlisher Format) – a pure Java implementation for
Microsoft Publisher files.
• HSMF (Horrible Stupid Mail Format) – a pure Java implementation for
Microsoft Outlook MSG files
• DDF (Dreadful Drawing Format) – a package for decoding the Microsoft
Office Drawing format.

• Working with .xlsx files
• The classes we used in above code
snippet, HSSFWorkbook and HSSFSheet works for .xls format. In order to
work with newer xls format viz .xlsx, you need to see newer POI classes
like:
• import org.apache.poi.hssf.usermodel.HSSFSheet;
• import org.apache.poi.xssf.usermodel.XSSFSheet;
• FileInputStream file = new FileInputStream(new File("C:test.xlsx"));
• //Get the workbook instance for XLS file
• XSSFWorkbook workbook = new XSSFWorkbook (file);
• //Get first sheet from the workbook XSSFSheet sheet =
workbook.getSheetAt(0);
• //Get iterator to all the rows in current sheet
• Iterator<Row> rowIterator = sheet.iterator();
• //Get iterator to all cells of current row
• Iterator<Cell> cellIterator = row.cellIterator();

Create Blank Workbook
• The following simple program is used to create a blank Microsoft
Excel Workbook.
• import java.io.*; import org.apache.poi.xssf.usermodel.*;
• public class CreateWorkBook {
• public static void main(String[] args)throws Exception {
• //Create Blank workbook
• XSSFWorkbook workbook = new XSSFWorkbook(); //Create file
system using specific name
• FileOutputStream out = new FileOutputStream( new
File("createworkbook.xlsx"));
• //write operation workbook using file out object
workbook.write(out); out.close(); System.out.println("
createworkbook.xlsx written successfully");
• } }

Open Existing Workbook
• Use the following code to open an existing workbook.
• import java.io.*; import org.apache.poi.xssf.usermodel.*;
• public class OpenWorkBook {
• public static void main(String args[])throws Exception { File file =
new File("openworkbook.xlsx"); FileInputStream fIP = new
FileInputStream(file);
• //Get the workbook instance for XLSX file
• XSSFWorkbook workbook = new XSSFWorkbook(fIP); if(file.isFile()
&& file.exists())
• { System.out.println( "openworkbook.xlsx file open successfully.");
• } else { System.out.println( "Error to open openworkbook.xlsx file.");
}
}
}

We will read above xls file using
Apache POI and prints the data.
• Consider a sample excel file:
• test.xls

• try {
•
• FileInputStream file = new FileInputStream(new
File("C:test.xls"));
•
• //Get the workbook instance for XLS file
• HSSFWorkbook workbook = new HSSFWorkbook(file);
• //Get first sheet from the workbook
• HSSFSheet sheet = workbook.getSheetAt(0);
•
• //Iterate through each rows from first sheet
• Iterator<Row> rowIterator = sheet.iterator();
• while(rowIterator.hasNext()) {
• Row row = rowIterator.next();
•
• //For each row, iterate through each columns
• Iterator<Cell> cellIterator = row.cellIterator();
• while(cellIterator.hasNext()) {
•
• Cell cell = cellIterator.next();

• switch(cell.getCellType()) {
• case
Cell.CELL_TYPE_BOOLEAN:
•
System.out.print(cell.getBooleanCellValue() + "tt");
• break;
• case
Cell.CELL_TYPE_NUMERIC:
•
System.out.print(cell.getNumericCellValue() + "tt");
• break;
• case
Cell.CELL_TYPE_STRING:
•
System.out.print(cell.getStringCellValue() + "tt");
• break;
• }
• }
• System.out.println("");
• }

• file.close();
• FileOutputStream out =
• new FileOutputStream(new
File("C:test.xls"));
• workbook.write(out);
• out.close();
•
• } catch (FileNotFoundException e) {
• e.printStackTrace();
• } catch (IOException e) {
• }

• 3. Create New Excel File
• Let us create a new excel file and write data in it. Following is the API
which we will use for this purpose.
• import org.apache.poi.hssf.usermodel.HSSFSheet;
• import org.apache.poi.hssf.usermodel.HSSFWorkbook;
• //..
• HSSFWorkbook workbook = new HSSFWorkbook();
• HSSFSheet sheet = workbook.createSheet("Sample sheet");
• //Create a new row in current sheet
• Row row = sheet.createRow(0);
• //Create a new cell in current row
• Cell cell = row.createCell(0);
• //Set value to new value
• cell.setCellValue("Blahblah");

• Below is the complete code that writes a new excel with dummy data:
• HSSFWorkbook workbook = new HSSFWorkbook();
• HSSFSheet sheet = workbook.createSheet("Sample sheet");
•
• Map<String, Object[]> data = new HashMap<String, Object[]>();
• data.put("1", new Object[] {"Emp No.", "Name", "Salary"});
• data.put("2", new Object[] {1d, "John", 1500000d});
• data.put("3", new Object[] {2d, "Sam", 800000d});
• data.put("4", new Object[] {3d, "Dean", 700000d});
•
• Set<String> keyset = data.keySet();
• int rownum = 0;
• for (String key : keyset) {
• Row row = sheet.createRow(rownum++);
• Object [] objArr = data.get(key);
• int cellnum = 0;
• for (Object obj : objArr) {
• Cell cell = row.createCell(cellnum++);
• if(obj instanceof Date)
• cell.setCellValue((Date)obj);
• else if(obj instanceof Boolean)
• cell.setCellValue((Boolean)obj);
• else if(obj instanceof String)
• cell.setCellValue((String)obj);
• else if(obj instanceof Double)
• cell.setCellValue((Double)obj);
• }
• }
•

• try {
• FileOutputStream out =
• new FileOutputStream(new
File("C:new.xls"));
• workbook.write(out);
• out.close();
• System.out.println("Excel written
successfully..");
•
• } catch (FileNotFoundException e) {
• } catch (IOException e) {
• }

Apachepoitutorial

Recommended

More Related Content

What's hot (20)

Similar to Apachepoitutorial (20)

More from Srikrishna k (16)

Recently uploaded (20)

Apachepoitutorial