# 3
this is sample code which creates an excel sheet and reads excel values from the cell and modify ,depending on u r requirement change u r application
import java.io.IOException;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
/**
* File for HSSF testing/examples
*
* THIS IS NOT THE MAIN HSSF FILE!! This is a util for testing functionality.
* It does contain sample API usage that may be educational to regular API users.
*
* @see #main
* @author Andrew Oliver (acoliver at apache dot org)
*/
public class modify
{
Stringfilename =null;
HSSFWorkbook hssfworkbook=null;
/**
* Constructor HSSF - creates an HSSFStream from an InputStream. The HSSFStream
* reads in the records allowing modification.
*
*
* @param filename
*
* @exception IOException
*
*/
public modify(String filename)
throws IOException
{
this.filename = filename;
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream(filename));
hssfworkbook = new HSSFWorkbook(fs);
// records = RecordFactory.createRecords(stream);
}
/**
* Constructor HSSF - given a filename this outputs a sample sheet with just
* a set of rows/cells.
*
*
* @param filename
* @param write
*
* @exception IOException
*
*/
public modify(String filename, boolean write)
throws IOException
{
shortrownum = 0;
FileOutputStream out= new FileOutputStream(filename);
HSSFWorkbookwb= new HSSFWorkbook();
HSSFSheets= wb.createSheet();
HSSFRow r= null;
HSSFCell c= null;
/*/**
* Constructor HSSF - takes in file - attempts to read it then reconstruct it
*
*
* @param infile
* @param outfile
* @param write
*
* @exception IOException
*
*/
public modify(String infile, String outfile, boolean write)
throws IOException
{
this.filename = infile;
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream(filename));
hssfworkbook = new HSSFWorkbook(fs);
// HSSFWorkbook book = hssfstream.getWorkbook();
}
/**
* Method main
*
* Given 1 argument takes that as the filename, inputs it and dumps the
* cell values/types out to sys.out
*
* given 2 arguments where the second argument is the word "write" and the
* first is the filename - writes out a sample (test) spreadsheet (see
* public HSSF(String filename, boolean write)).
*
* given 2 arguments where the first is an input filename and the second
* an output filename (not write), attempts to fully read in the
* spreadsheet and fully write it out.
*
* given 3 arguments where the first is an input filename and the second an
* output filename (not write) and the third is "modify1", attempts to read in the
* spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to
* "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you
* take the output from the write test, you'll have a valid scenario.
*
* @param args
*
*/
public static void main(String args[])
{
if (args.length < 2)
{
try
{
modify hssf = new modify(args[ 0 ]);
System.out.println("Data dump:\n");
HSSFWorkbook wb = hssf.hssfworkbook;
for (int k = 0; k < wb.getNumberOfSheets(); k++)
{
System.out.println("Sheet " + k);
HSSFSheet sheet = wb.getSheetAt(k);
introws = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++)
{
HSSFRow row= sheet.getPhysicalRowAt(r);
intcells = row.getPhysicalNumberOfCells();
System.out.println("ROW " + row.getRowNum());
for (int c = 0; c < cells; c++)
{
HSSFCell cell = row.getPhysicalCellAt(c);
Stringvalue = null;
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_FORMULA :
value = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_NUMERIC :
value = "NUMERIC value="
+ cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING :
value = "STRING value="
+ cell.getStringCellValue();
break;
default :
}
System.out.println("CELL col="
+ cell.getCellNum()
+ " VALUE=" + value);
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
else if (args.length == 2)
{
if (args[ 1 ].toLowerCase().equals("write"))
{
System.out.println("Write mode");
try
{
long time = System.currentTimeMillis();
modify hssf = new modify(args[ 0 ], true);
System.out
.println("" + (System.currentTimeMillis() - time)
+ " ms generation time");
}
catch (Exception e)
{
e.printStackTrace();
}
}
else
{
System.out.println("readwrite test");
try
{
modify hssf= new modify(args[ 0 ]);
// HSSFStreamhssfstream = hssf.hssfstream;
HSSFWorkbookwb= hssf.hssfworkbook;
FileOutputStream stream = new FileOutputStream(args[ 1 ]);
// HSSFCell cell = new HSSFCell();
// cell.setCellNum((short)3);
// cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
// cell.setCellValue(-8009.999);
// hssfstream.modifyCell(cell,0,(short)6);
wb.write(stream);
stream.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
/*else if ((args.length == 3)
&& args[ 2 ].toLowerCase().equals("modify1"))
{
try// delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!"
{
HSSF hssf= new HSSF(args[ 0 ]);
// HSSFStreamhssfstream = hssf.hssfstream;
HSSFWorkbookwb= hssf.hssfworkbook;
FileOutputStream stream = new FileOutputStream(args[ 1 ]);
HSSFSheetsheet = wb.getSheetAt(0);
for (int k = 0; k < 25; k++)
{
HSSFRow row = sheet.getRow(k);
sheet.removeRow(row);
}
for (int k = 74; k < 100; k++)
{
HSSFRow row = sheet.getRow(k);
sheet.removeRow(row);
}
HSSFRow row = sheet.getRow(39);
HSSFCell cell = row.getCell(( short ) 3);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("MODIFIED CELL!!!!!");
// HSSFCell cell = new HSSFCell();
// cell.setCellNum((short)3);
// cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
// cell.setCellValue(-8009.999);
// hssfstream.modifyCell(cell,0,(short)6);
wb.write(stream);
stream.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}*/
}
}
SETTING CLASSPATH :
Right click on mycomputer ->properites->advanced->environment variables->
in that create a user variable with poi and vlaue should be the path were u r there jar file are placed
