Sunday, June 16, 2013

Reading and Writing in an existing Excel Sheet

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

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.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class Excel_reading {

/**
* @param args
* @throws IOException
* @throws InvalidFormatException
*/
public static void main(String[] args) throws IOException, InvalidFormatException {
Excel_reading Exred =new Excel_reading();
Exred.excel_read();


}

public void excel_read() throws IOException, InvalidFormatException
{
String cell_val;
InputStream FIS= new FileInputStream("Testdata.xls");
HSSFWorkbook WorkBookObj=(HSSFWorkbook) new WorkbookFactory().create(FIS);
HSSFSheet SheetObj = WorkBookObj.getSheet("Testdata");
HSSFRow RowObj=SheetObj.getRow(0);
HSSFCell CellObj=RowObj.getCell(0);

for (int k=0; k<=10;k++)
{
RowObj=SheetObj.getRow(k);
RowObj.getCell(1,RowObj.CREATE_NULL_AS_BLANK);
System.out.println(RowObj.getCell(0).getStringCellValue());

for (int j=0; j<=10; j++)
{
if ( RowObj.getCell(1).getStringCellValue().equalsIgnoreCase(""))
{
HSSFCell CellObj1;
RowObj=SheetObj.getRow(k);
CellObj=RowObj.createCell(1);
CellObj.setCellValue("Used");
FileOutputStream FOS =new FileOutputStream("Testdata.xls");
WorkBookObj.write(FOS);
FOS.close();
}
FIS.close();
   }
}
   }
}




Out Put
Fig.1 Shows Initial Excel File.
Fig 2 Shows Console Output of reading excel
Fig 3 Shows Excel Write output









Fig 2




















Fig. 3 Excel Write output