/**
 * $Id$
 *
 * Copyright (C) 2006 - 2009 iba Consulting Gesellschaft mbH & Co. KG
 * All Rights Reserved.
 *
 */
package de.ibacg.xls;

import java.io.FileInputStream;
import java.io.IOException;
import java.net.URL;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

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.poifs.filesystem.POIFSFileSystem;

/**
 * Simple static wrapper to read a Excel file as List<List<String>>.
 * 
 * @author Alf Richter
 * 
 */
public class XlsReader {
	private static final int DATA_START_FROM_ROW = 1;
	private static final int WORKSHEET_NR = 0;

	/**
	 * Read the first sheet of the given Excel filename.
	 * 
	 * If an IO Exception occurs an emptyList is returned and the exception is dumped to stdout.
	 * 
	 * @param filename
	 *            , absolute path of Excel file
	 * @return List<List<String>> with the contents of the first sheet.
	 */
	public static List<List<String>> readSheet(String filename) {
		POIFSFileSystem fs = null;
		try {

			fs = new POIFSFileSystem(new FileInputStream(filename));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(WORKSHEET_NR);
			int r = DATA_START_FROM_ROW;
			List<List<String>> sheetAsList = new LinkedList<List<String>>();
			for (; r <= sheet.getLastRowNum(); ++r) {
				HSSFRow row = sheet.getRow(r);
				if (row != null) {
					List<String> rowList = new LinkedList<String>();
					for (short c = 0; c <= row.getLastCellNum(); ++c) {
						if (row.getCell(c) != null) {
							rowList.add(getCellAsString(c, row));
						}
					}
					sheetAsList.add(rowList);
				}
			}

			// System.out.println("Success converted " + r + " Records.");
			return sheetAsList;

		} catch (IOException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	/**
	 * Get the cell contents from a row.
	 * 
	 * @param columnIndex
	 * @param row
	 * @return cell contents as String
	 */
	private static String getCellAsString(int columnIndex, HSSFRow row) {
		String cellString = "";
		if (row.getCell((short) columnIndex) != null) {
			cellString = row.getCell((short) columnIndex).toString().trim();
		} else {
			String msg = "Error Reading Cell from XLS: column=" + columnIndex
					+ " , row=" + row.getRowNum() + "\n" + row.toString();
			throw new RuntimeException(msg);

		}
		return cellString;
	}

	/**
	 * Lookup a filename in classpath and return the absolute path to this file.
	 * 
	 * @param filename
	 * @return absolute path in a singletonList, if filename not found in classpath an emptyList is returned.
	 */
	public static List<String> findFilenameInClassPath(String filename) {
		URL url = XlsReader.class.getClassLoader().getResource(filename);
		if (url == null)
			return Collections.emptyList();
		return Collections.singletonList(url.getPath());
	}

}