POI examples
Creating Excel file using Apache POI
Example For Writing in Excel fileExample For Writing in Excel file
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
public class WriteEXCEL
{
public static void main(String args[])
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet =
workbook.createSheet("Sample sheet");
TreeMap<String, Object[]> data =
new TreeMap<String, Object[]>();
data.put("1", new Object[]
{"Emp No.", "Name", "Salary"});
data.put("2", new Object[]
{1d, "shalabh", 1500000.00d});
data.put("3", new Object[]
{2d, "yutika", 800000.00d});
data.put("4", new Object[]
{3d, "john", 700000.09d});
data.put("5", new Object[]
{4d, "harry", 788884d});
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("D:\\First.xls"));
workbook.write(out);
out.close();
System.out.println
("Excel written successfully..");
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}
}
Example For Reading Excel file
Example For Reading Excel file
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import java.util.Vector;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class ExcelRead
{
public ExcelRead()
{}
public static void main(String[] args)
throws FileNotFoundException,IOException
{
Vector cellVectorHolder = new Vector();
FileInputStream myInput = new
FileInputStream("E:\\doc\\Plan.xls");
/** Create a POIFSFileSystem object**/
POIFSFileSystem myFileSystem = new
POIFSFileSystem(myInput);
/** Create a workbook using the File System**/
HSSFWorkbook myWorkBook = new
HSSFWorkbook(myFileSystem);
/** Get the first sheet from workbook**/
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
/** We now need something to
iterate through the cells.**/
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext())
{
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector=new Vector();
while(cellIter.hasNext()){
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
System.out.println("Data is "+cellVectorHolder);
}
Example For CopyExcel File To Another Excel File
Example For CopyExcel File To Another Excel File
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.openxml4j.exceptions
.InvalidFormatException;
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.usermodel.WorkbookFactory;
public class CopyExcel
{
public static void main(String args[])
throws IOException, InvalidFormatException
{
FileInputStream inp = new
FileInputStream(new File("e:\\Plan.xls"));
//InputStream inp = new
//FileInputStream("workbook.xlsx");
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(2);
Cell cell = row.getCell(3);
if (cell == null)
cell = row.createCell(3);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("a test");
// Write the output to a file
FileOutputStream fileOut = new
FileOutputStream("D:\\Fourth.xls");
wb.write(fileOut);
fileOut.close();
}
}
Creating Power Point file using Apache POI
Example For Creating PPT FileExample For Creating PPT File
import org.apache.poi.hslf.HSLFSlideShow;
import org.apache.poi.hslf.model.Slide;
import org.apache.poi.hslf.usermodel.SlideShow;
import java.io.*;
public class CreateNewPresentation
{
public static void main(String str[])
{
try
{
SlideShow slideShow = new SlideShow();
Slide slide = slideShow.createSlide();
FileOutputStream out = new
FileOutputStream("slideshow.ppt");
slideShow.write(out);
System.out.println("File Created...");
out.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
Creating MS-Word file using Apache POI
Example For Creating MS-Word FileExample For Creating MS-Word File
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import java.util.Vector;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class ExcelRead
{
public ExcelRead()
{}
public static void main(String[] args)
throws FileNotFoundException,IOException
{
Vector cellVectorHolder = new Vector();
FileInputStream myInput = new
FileInputStream("E:\\doc\\Plan.xls");
/** Create a POIFSFileSystem object**/
POIFSFileSystem myFileSystem = new
POIFSFileSystem(myInput);
/** Create a workbook using the File System**/
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
/** Get the first sheet from workbook**/
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
/** We now need something to
iterate through the cells.**/
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext())
{
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector=new Vector();
while(cellIter.hasNext()){
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
System.out.println("Data is "+cellVectorHolder);
}
Example For Creating Table In MS-Word File
Example For Creating Table In MS-Word File
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.openxml4j.exceptions
.InvalidFormatException;
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.usermodel.WorkbookFactory;
public class CopyExcel
{
public static void main(String args[])
throws IOException, InvalidFormatException
{
FileInputStream inp = new
FileInputStream(new File("e:\\Plan.xls"));
//InputStream inp = new FileInputStream("workbook.xlsx");
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(2);
Cell cell = row.getCell(3);
if (cell == null)
cell = row.createCell(3);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("a test");
// Write the output to a file
FileOutputStream fileOut = new
FileOutputStream("D:\\Fourth.xls");
wb.write(fileOut);
fileOut.close();
}
}
