package de.blb.kz.xlreader;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import java.util.Iterator;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;

/**
* A simple POI example of opening an Excel spreadsheet
* and writing its contents to the command line.
* @author y17993
*/
public class XLReader
{

	public static void main(String[] args)
	{
		InputStream input = null;
    	String filename = "C:/localKaBZaB/Testsheet.xls";
    	
    	if (args.length > 0)
    	{
    		filename = args[0];	
    	}
		try
		{
			//Open input stream
			input = new FileInputStream(filename);
			
			POIFSFileSystem fs = new POIFSFileSystem(input);
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			int numberOfSheets = wb.getNumberOfSheets();
			System.out.println("This workbook contains '" + numberOfSheets + "' sheets");
			
			// Iterate through the sheets
			for (int i=0;i<numberOfSheets;i++)
			{
				
				HSSFSheet sheet = wb.getSheetAt(i);
				String sheetName = wb.getSheetName(i);
				System.out.println("This name of the first sheet to parse is  '" + sheetName + "'.");
				
				// Iterate over each row in the sheet
				Iterator rows = sheet.rowIterator();
				while (rows.hasNext())
				{
					HSSFRow row = (HSSFRow) rows.next();
					int rowNumber = row.getRowNum();
					int lastCellNr = row.getLastCellNum();
					System.out.println("Rows #" + row.getRowNum() + " last cell number is " + lastCellNr + ".");
	
					// Iterate over each cell in the row and print out the cell's content
//					Iterator cells = row.cellIterator();
//					while (cells.hasNext())
					for(short cn=0;cn<lastCellNr;cn++)
					{
//						HSSFCell cell = (HSSFCell) cells.next();
						HSSFCell cell = row.getCell(cn);
						if (cell!=null)
						{
							int columnNumber = cn;//cell.getCellNum();
							System.out.println("Cell #" + cell.getCellNum());
							switch (cell.getCellType())
							{
								case HSSFCell.CELL_TYPE_NUMERIC :
									System.out.println("Cell("+columnNumber+"/"+rowNumber+") contains: " +cell.getNumericCellValue() + " date interpretation :" + cell.getDateCellValue());
									break;
								case HSSFCell.CELL_TYPE_STRING :
									System.out.println("Cell("+columnNumber+"/"+rowNumber+") contains: " +cell.getStringCellValue());
									break;
								case HSSFCell.CELL_TYPE_BLANK :
									System.out.println("empty");
									break;
								case HSSFCell.CELL_TYPE_FORMULA :
									System.out.println("Cell("+columnNumber+"/"+rowNumber+") contains formula: " + cell.getCellFormula() + " string representation " + cell.getStringCellValue());
//									System.out.println("Cell("+columnNumber+"/"+rowNumber+") contains formula. String representation " + cell.getStringCellValue());
									break;
								default :
									System.out.println("unsupported cell type: '" + cell.getCellType() + "'");
									break;
							}
						}
						else
						{
							System.out.println("Cell ("+cn+"/"+rowNumber+") empty");
						}
					}
				}
			}

		}
		catch (IOException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			try
			{
				input.close();
			}
			catch (Exception ex)
			{
				ex.printStackTrace();			
			}
		}
	}

}