Wednesday, 14 August 2013

Assistance required in writing list of data to a particular column in an excel file using java

Assistance required in writing list of data to a particular column in an
excel file using java

I have written a code which reads data in formula cells of an excel and
returns a string and now i want to write the list of string values to a
particular sheet and a particular column. Could anybody provide me an
sample code snippet. Ex: Write the list of data to column "C" of an excel
file. My code looks like:
@Test
public void SampleCustNumFormat() throws Exception {
String [][] myXL = getExcelData();
//See what has been read from Excel
for (int i=0; i<xRows; i++)
{
for (int j=3; j<xCols; j++)
{
System.out.println (" Cust Num " + myXL[i][j]);
}
}
}
public String [][] getExcelData() throws Exception{
String [][] tabArray=null;
FileInputStream fi = new FileInputStream("C:\\SCE
docs\\Automation\\CustomerAccount_Information.xls");
HSSFWorkbook myWB = new HSSFWorkbook(fi);
HSSFSheet mySheet = myWB.getSheetAt(0);
FormulaEvaluator evaluator =
myWB.getCreationHelper().createFormulaEvaluator();
xRows = mySheet.getLastRowNum()+1;
xCols = mySheet.getRow(0).getLastCellNum();
tabArray = new String [xRows][xCols];
for (int i=0;i<xRows;i++)
{
HSSFRow row = mySheet.getRow(i);
for (int j=3;j<xCols;j++)
{
HSSFCell cell = row.getCell(j);
CellValue cellValue = evaluator.evaluate(cell);
String value = evaluateFormula(cellValue);
tabArray[i][j]=value;
}
}
return tabArray;
}
private String evaluateFormula(CellValue cellValue) throws Exception{
// TODO Auto-generated method stub
int type = cellValue.getCellType();
Object result=null;
switch (type) {
case HSSFCell.CELL_TYPE_BOOLEAN:
result = cellValue.getBooleanValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
result = cellValue.getNumberValue();
break;
case HSSFCell.CELL_TYPE_STRING:
result = cellValue.getStringValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
// CELL_TYPE_FORMULA will never happen
case HSSFCell.CELL_TYPE_FORMULA:
break;
}
return result.toString();
}
}
My Output list of string values is :
Cust Num CustAccNum
Cust Num 2-23-456-7891
Cust Num 2-00-006-7891
Cust Num 2-03-456-7891
Cust Num 2-00-234-5678
Cust Num 2-00-023-4891
Cust Num 2-00-234-7891
Cust Num 2-00-345-6781

No comments:

Post a Comment