cdmlib-commons/src/main/java/eu/etaxonomy/cdm/common/ExcelUtils.java

   1 /**
   2 * Copyright (C) 2007 EDIT
   3 * European Distributed Institute of Taxonomy
   4 * http://www.e-taxonomy.eu
   5 *
   6 * The contents of this file are subject to the Mozilla Public License Version 1.1
   7 * See LICENSE.TXT at the top of this package for the full license terms.
   8 */
   9
  10 package eu.etaxonomy.cdm.common;
  11
  12 import java.io.FileInputStream;
  13 import java.util.ArrayList;
  14 import java.util.HashMap;
  15
  16 import org.apache.log4j.Logger;
  17 import org.apache.poi.hssf.usermodel.HSSFCell;
  18 import org.apache.poi.hssf.usermodel.HSSFRow;
  19 import org.apache.poi.hssf.usermodel.HSSFSheet;
  20 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  21 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  22
  23 /**
  24  * @author n.hoffmann
  25  * @created 18.11.2008
  26  * @version 1.0
  27  */
  28 public class ExcelUtils {
  29         private static final Logger logger = Logger.getLogger(ExcelUtils.class);
  30
  31     /** Reads all rows of an Excel worksheet */
  32     public static ArrayList<HashMap<String, String>> parseXLS(String fileName) {
  33
  34         ArrayList<HashMap<String, String>> recordList = new ArrayList<HashMap<String, String>>();
  35
  36         try {
  37                 POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName));
  38                 HSSFWorkbook wb = new HSSFWorkbook(fs);
  39                 HSSFSheet sheet = wb.getSheetAt(0);
  40                 HSSFRow row;
  41                 HSSFCell cell;
  42
  43                 int rows; // No of rows
  44                 rows = sheet.getPhysicalNumberOfRows();
  45
  46                 int cols = 0; // No of columns
  47                 int tmp = 0;
  48
  49                 // This trick ensures that we get the data properly even if it doesn't start from first few rows
  50                 for(int i = 0; i < 10 || i < rows; i++) {
  51                         row = sheet.getRow(i);
  52                         if(row != null) {
  53                                 tmp = sheet.getRow(i).getPhysicalNumberOfCells();
  54                                 if(tmp > cols) cols = tmp;
  55                         }
  56                 }
  57                 HashMap<String, String> headers = null;
  58                 ArrayList<String> columns = new ArrayList<String>();
  59                 row = sheet.getRow(0);
  60                 for (int c = 0; c < cols; c++){
  61                         cell = row.getCell(c);
  62                         columns.add(cell.toString());
  63                 }
  64                 for(int r = 1; r < rows; r++) {
  65                         row = sheet.getRow(r);
  66                         headers = new HashMap<String, String>();
  67                         if(row != null) {
  68                                 for(int c = 0; c < cols; c++) {
  69                                         cell = row.getCell((short)c);
  70                                         if(cell != null) {
  71                                                 headers.put(columns.get(c), cell.toString());
  72                                         }
  73                                 }
  74                         }
  75                         recordList.add(headers);
  76                 }
  77
  78         } catch(Exception ioe) {
  79                 ioe.printStackTrace();
  80         }
  81         return recordList;
  82     }
  83
  84 }