package edu.georgetown.library.fileAnalyzer.importer;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelReader {
	static Pattern pInt = Pattern.compile("^(\\d+)\\.0$");
	public static Vector<Vector<String>> readExcel(File f) throws InvalidFormatException, IOException {		
		return readExcelWorksheet(f, 0, 1);
	}

	public static Vector<Vector<String>> readExcelWorksheet(File f, int sheetIndex) throws InvalidFormatException, IOException {
		return readExcelWorksheet(f, sheetIndex, 0);
	}
	public static Vector<Vector<String>> readExcelWorksheet(File f, int sheetIndex, int maxSheets) throws InvalidFormatException, IOException {		
		Vector<Vector<String>> data = new Vector<Vector<String>>();

		FileInputStream fs = new FileInputStream(f);
		try {
			Workbook w = WorkbookFactory.create(fs);
			if (w.getNumberOfSheets() <= sheetIndex) throw new InvalidFormatException("Cannot find worksheet "+ sheetIndex);
			if (maxSheets != 0) {
				if (w.getNumberOfSheets() > maxSheets) throw new InvalidFormatException("Workbook has more than " + maxSheets + " worksheet(s)");				
			}
			Sheet sheet = w.getSheetAt(sheetIndex);
			for(int r=0; r<sheet.getPhysicalNumberOfRows(); r++) {
				Vector<String> row = new Vector<String>();
				data.add(row);
				Row srow = sheet.getRow(r);
				if (srow == null) continue;
				for(int c=0; c<srow.getLastCellNum(); c++) {
					Cell cell = srow.getCell(c); 
					if (cell == null) {
						row.add(null);
					} else {
						String val = "";
						try {
							if (cell.getCellType() == 0) {
								val = ""+cell.getNumericCellValue();
								Matcher m = pInt.matcher(val);
								if (m.matches()) {
									val = m.group(1);
								}
							} else {
								val = cell.toString();
							}
						} catch (Exception e) {
							val = "N/A "+e.getMessage();
						}
						row.add(val);
					}
					
				}
			}
		} catch (InvalidFormatException e) {
			throw e;
		} catch (IOException e) {
			throw e;
		} finally {
			fs.close();
		}
		return data;
	}

}

@@ -1991,17 +1991,38 @@
 pe() == 
-0
+Cell.CELL_TYPE_NUMERIC
 ) %7B%0D%0A%09%09%09
@@ -2156,24 +2156,24 @@
 .group(1);%0D%0A
-
 %09%09%09%09%09%09%09%09%7D%0D%0A%09
@@ -2167,24 +2167,206 @@
 %0A%09%09%09%09%09%09%09%09%7D%0D%0A
+%09%09%09%09%09%09%09%7D else if (cell.getCellType() == Cell.CELL_TYPE_STRING) %7B%0D%0A%09%09%09%09%09%09%09%09val = cell.toString();%0D%0A%09%09%09%09%09%09%09%7D else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) %7B%0D%0A%09%09%09%09%09%09%09%09val = %22%22;%0D%0A
 %09%09%09%09%09%09%09%7D els

Clarify excel extract code