Below are the two methods which are used to read data from excel file and write data back to excel file.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.eclipse.jdt.internal.compiler.lookup.UpdatedMethodBinding;
// Reading data from excel
public static void excelRead(String sPath,String sheetName) throws IOException //sPath-- path where excel is placed , sheetName--The sheet which you want to read
{
File xlFile= new File(sPath);
FileInputStream fin = new FileInputStream(xlFile);
HSSFWorkbook wBook= new HSSFWorkbook(fin);
HSSFSheet sheetObj=wBook.getSheet(sheetName);
MissingCellPolicy mcpObj= HSSFRow.CREATE_NULL_AS_BLANK; //Missing Cell Policy
HSSFRow rowObj;
HSSFCell cellObj;
xRows= sheetObj.getLastRowNum()+1;
xCols= sheetObj.getRow(0).getLastCellNum();
xData= new String[xRows][xCols];
System.out.println("No of rows are "+xRows +" No of Cols are "+xCols);
for (int i=0;i<xRows;i++)
{
//System.out.println("inside loop");
rowObj=sheetObj.getRow(i);
for (int j=0;j<xCols;j++)
{
cellObj=rowObj.getCell(j, mcpObj);
String Value=cellToString(cellObj);
//System.out.print(Value+"@@@@");
xData[i][j]=Value;
}
//System.out.println("----------------------------------------------------------------");
}
}
public static String cellToString(HSSFCell cell)
{
int type=cell.getCellType();
Object result;
switch(type)
{
case HSSFCell.CELL_TYPE_NUMERIC ://0
//result=cell.getNumericCellValue();-----original
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
result=cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_STRING: //1
result=cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA: //2
throw new RuntimeException("We can't evluate Formula in java");
case HSSFCell.CELL_TYPE_BLANK: //3
result="";
break;
case HSSFCell.CELL_TYPE_BOOLEAN: //4
result=cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_ERROR: //4
throw new RuntimeException("This cell has an error");
default:
throw new RuntimeException("We had not yet handled this type "+type);
}
return result.toString().trim();
}
// Write data back to excel
public static void excelWrite(String sPath , String SheetName,String xlData[][] ,int xRows,int xCols) throws IOException
{
System.out.println("in write Excel");
File outXlFile= new File(sPath);
HSSFSheet outSheet=null;
HSSFWorkbook outBook= null;
if (outXlFile.exists()) {
try {
outBook = (HSSFWorkbook)WorkbookFactory.create(outXlFile); // If the sheet already exists then this method re-writes the sheet
} catch (InvalidFormatException e) {
e.printStackTrace();
}
outSheet = outBook.createSheet(SheetName);
}
else{
outBook = new HSSFWorkbook();
outSheet = outBook.createSheet(SheetName);
}
//HSSFSheet outSheet= outBook.createSheet(SheetName);
System.out.println("Rows "+xRows+" Cols "+xCols);
for (int i=0;i<xRows;i++)
{
HSSFRow outRow= outSheet.createRow(i);
for(int j=0;j<xCols;j++)
{
HSSFCell outCell= outRow.createCell(j);
outCell.setCellType(outCell.CELL_TYPE_STRING);
outCell.setCellValue(xlData[i][j]);
}
}
FileOutputStream fout=new FileOutputStream(outXlFile);
outBook.write(fout);
fout.flush();
fout.close();
outBook=new HSSFWorkbook(new FileInputStream(sPath));
}
No comments:
Post a Comment