_Csandeep

Reading Excel using Java/Apache POI

Jun 8th, 2015 (edited)
249
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 2.75 KB | None | 0 0
  1. package miscellaneous;
  2.  
  3. import org.apache.poi.hssf.usermodel.HSSFCell;
  4. import org.apache.poi.hssf.usermodel.HSSFRow;
  5. import org.apache.poi.hssf.usermodel.HSSFSheet;
  6. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  7. import org.junit.Test;
  8.  
  9. import java.io.FileInputStream;
  10. import java.io.FileNotFoundException;
  11. import java.io.IOException;
  12. import java.io.InputStream;
  13. import java.util.Iterator;
  14. import java.util.LinkedHashMap;
  15. import java.util.Map;
  16.  
  17. import static org.junit.Assert.assertTrue;
  18.  
  19. /**
  20.  * Created by Sandeep on 8/6/15.
  21.  */
  22. public class ProcessExcelFile {
  23.  
  24.     /**
  25.      * Open an excel file, iterate the rows of a worksheet and calculate the total of numeric values of each row.
  26.      *
  27.      * @return the Map to return
  28.      */
  29.     public Map ReadExcelFile() {
  30.  
  31.         InputStream fileInputStream = null;
  32.         HSSFWorkbook hssfWorkbook;
  33.         HSSFSheet sheet;
  34.         HSSFRow row;
  35.         HSSFCell cell;
  36.         Iterator rowIterator, cellIterator;
  37.         String employeeName = null;
  38.         double total = 0;
  39.         Map<String, Double> empMonthlyProdStat = new LinkedHashMap<String, Double>();
  40.  
  41.         try {
  42.             fileInputStream = new FileInputStream("/home/sandeep/Desktop/MyDocs/repos/git-repos/public/MAVEN-BASE/src/test/resources/TestExcel.xls");
  43.             hssfWorkbook = new HSSFWorkbook(fileInputStream);
  44.             sheet = hssfWorkbook.getSheetAt(0);
  45.             rowIterator = sheet.rowIterator();
  46.  
  47.             while (rowIterator.hasNext()) {
  48.                 row = (HSSFRow) rowIterator.next();
  49.                 cellIterator = row.cellIterator();
  50.                 while (cellIterator.hasNext()) {
  51.                     cell = (HSSFCell) cellIterator.next();
  52.  
  53.                     if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
  54.                         employeeName = cell.getStringCellValue();
  55.                     } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
  56.                         total = total + cell.getNumericCellValue();
  57.                     } else {
  58.                         //System.out.println("");// Handle other types
  59.                     }
  60.                 }
  61.                 empMonthlyProdStat.put(employeeName, total);
  62.                 total = 0;
  63.             }
  64.         } catch (FileNotFoundException e) {
  65.             e.printStackTrace();
  66.         } catch (IOException e) {
  67.             e.printStackTrace();
  68.         } finally {
  69.             try {
  70.                 fileInputStream.close();
  71.             } catch (IOException e) {
  72.                 e.printStackTrace();
  73.             }
  74.         }
  75.         return empMonthlyProdStat;
  76.     }
  77.  
  78.     @Test
  79.     public void testReadExcelFile() throws Exception {
  80.         assertTrue("This will succeed....", ReadExcelFile().size()==3);
  81.     }
  82. }
Add Comment
Please, Sign In to add comment