Sunday, 21 September 2014

Reading data from excel using Apache POI

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