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