/*
    * Copyright 2002-2006 the original author or authors.
    *
    * Licensed under the Apache License, Version 2.0 (the "License");
    * you may not use this file except in compliance with the License.
    * You may obtain a copy of the License at
    *
    *      http://www.apache.org/licenses/LICENSE-2.0
    *
   * Unless required by applicable law or agreed to in writing, software
   * distributed under the License is distributed on an "AS IS" BASIS,
   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   * See the License for the specific language governing permissions and
   * limitations under the License.
   */
  
  package org.gageot.excel.core;
  
  import java.io.File;
  import java.io.IOException;
  import java.io.InputStream;
  import java.lang.reflect.Array;
  import java.util.ArrayList;
  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.springframework.beans.factory.InitializingBean;
  import org.springframework.core.io.ClassPathResource;
  import org.springframework.core.io.FileSystemResource;
  import org.springframework.core.io.Resource;
  import org.springframework.dao.CleanupFailureDataAccessException;
  import org.springframework.dao.DataAccessException;
  import org.springframework.dao.DataAccessResourceFailureException;
  import org.springframework.util.Assert;
  
  /***
   * <b>This is the central class in the Excel core package.</b>
   * It simplifies the use of Excel and helps to avoid common errors.
   * It executes core Excel parsing workflow, leaving application code extract results.
   *
   * <p>Code using this class need only implement callback interfaces, giving
   * them a clearly defined contract. The RowCallbackHandler
   * interface extracts values from each row of a HSSFSheet.
   *
   * <p>Can be used within a service implementation via direct instantiation
   * with a Resource reference, or get prepared in an application context
   * and given to services as bean reference.
   *
   * <p>The motivation and design of this class is inspired from JdbcTemplate.
   *
   * <p>Because this class is parameterizable by the callback interfaces,
   * it isn't necessary to subclass it.
   *
   * @author David Gageot
   * @see SheetExtractor
   * @see RowCallbackHandler
   * @see RowMapper
   */
  public class ExcelTemplate implements InitializingBean {
  	/*** Used to obtain Excel data throughout the lifecycle of this object */
  	private Resource resource;
  
  	/***
  	 * Construct a new ExcelTemplate for bean usage.
  	 * Note: The Resource has to be set before using the instance.
  	 * This constructor can be used to prepare a ExcelTemplate via a BeanFactory,
  	 * typically setting the Resource via setResource.
  	 * @see #setResource
  	 */
  	public ExcelTemplate() {
  	}
  
  	/***
  	 * Construct a new ExcelTemplate, given a Resource to obtain the Excel stream from.
  	 * @param aResource Resource to obtain the Excel stream from
  	 */
  	public ExcelTemplate (Resource aResource) {
  		setResource (aResource);
  		afterPropertiesSet();
  	}
  	
  	/***
  	 * Construct a new ExcelTemplate, given an Excel File.
  	 * @param aFile Excel file
  	 */
  	public ExcelTemplate (File aFile) {
  		setResource (new FileSystemResource (aFile));
  		afterPropertiesSet();
  	}
  	
  	/***
  	 * Construct a new ExcelTemplate, given a path to an Excel file.
  	 * The path can be relative to the given class,
  	 * or absolute within the classpath via a leading slash.
  	 * @param aPath relative or absolute path within the class path
  	 * @param aClass the class to load resources with
  	 * @see java.lang.Class#getResourceAsStream
 	 */
 	public ExcelTemplate (String aPath, Class aClass) {
 		setResource (new ClassPathResource (aPath, aClass));
 		afterPropertiesSet();
 	}
 	
 	/***
 	 * Read the content of an Excel file for a given sheet name.
 	 * The content of the sheet is extracted using SheetExtractor.
 	 * @param sheetName name of the excel sheet
 	 * @param sheetExtractor object that will extract results
 	 * @return an arbitrary result object, as returned by the ResultSetExtractor
 	 * @throws DataAccessException if there is any problem
 	 */
 	public Object read (String sheetName, SheetExtractor sheetExtractor) throws DataAccessException {
 		Assert.notNull (sheetExtractor, "SheetExtractor must not be null");
 		Assert.notNull (sheetName, "sheetName must not be null");
 		Assert.notNull (getResource(), "resource is required");
 
 		InputStream in = null;
 		try {
 			in = getResource().getInputStream();
 
 			HSSFWorkbook wb = new HSSFWorkbook (in);
 			HSSFSheet sheet = wb.getSheet (sheetName);
 
 			return sheetExtractor.extractData (sheet);
 		} catch (IOException e) {
 			throw new DataAccessResourceFailureException ("Problem reading file", e);
 		}
 		finally {
 			if (null != in) {
 				try {
 					in.close();
 				} catch (IOException e) {
 					throw new CleanupFailureDataAccessException ("Problem closing file", e);
 				}
 			}
 		}
 	}
 	
 	public String[][] read (String sheetName) throws DataAccessException {
 		return (String[][]) read (sheetName, new StringCellMapper(), String.class);
 	}
 
 	public Object[][] read (String sheetName, CellMapper cellMapper) throws DataAccessException {
 		return (Object[][]) read (sheetName, cellMapper, Object.class);
 	}
 
 	public Object[][] read (String sheetName, CellMapper cellMapper, Class clazz) throws DataAccessException {
 		return (Object[][]) read (sheetName, new ObjectArraySheetExtractor (cellMapper, clazz));
 	}
 
 	public List readList (String sheetName, RowMapper rowMapper) throws DataAccessException {
 		return (List) read (sheetName, new RowMapperSheetExtractor (rowMapper));
 	}
 	
 	public List readList (String sheetName) throws DataAccessException {
 		return readList (sheetName, new StringCellMapper());
 	}
 	
 	public List readList (String sheetName, CellMapper cellMapper) throws DataAccessException {
 		MapListRowCallbackHandler rowHandler = new MapListRowCallbackHandler (cellMapper);
 
 		read (sheetName, rowHandler);
 		
 		return rowHandler.getValues();
 	}
 	
 	public void read (String sheetName, RowCallbackHandler rowCallbackHandler) throws DataAccessException {
 		read (sheetName, new RowCallbackHandlerSheetExtractor (rowCallbackHandler));
 	}
 
 	public void read (String sheetName, CellCallbackHandler cellCallbackHandler) throws DataAccessException {
 		read (sheetName, new CellCallbackHandlerSheetExtractor (cellCallbackHandler));
 	}
 
 	public List readBeans (String sheetName, Class clazz) throws DataAccessException {
 		BeanCellCallbackHandler handler = new BeanCellCallbackHandler (clazz);
 		
 		read (sheetName, handler);
 
 		return handler.getBeans();
 	}
 	
 	/***
 	 * Set the Resource to obtain the Excel stream from.
 	 */
 	public Resource getResource() {
 		return resource;
 	}
 
 	/***
 	 * Return the Resource used by this template.
 	 */
 	public void setResource(Resource aResource) {
 		this.resource = aResource;
 	}
 
 	/***
 	 */
 	public void afterPropertiesSet() {
 		if (null == getResource()) {
 			throw new IllegalArgumentException("resource is required");
 		}
 	}
 
 	/***
 	 * RowMapper implementation that creates a <code>java.lang.Object</code> array
 	 * for each row.
 	 */
 	private static class ObjectArrayRowMapper implements RowMapper {
 		private final CellMapper cellMapper;
 		private final Class cellClass;
 		
 		public ObjectArrayRowMapper (CellMapper aCellMapper, Class aCellClass) {
 			cellMapper = aCellMapper;
 			cellClass = aCellClass;
 		}
 
 		public Object mapRow (HSSFRow row, int rowNum) throws IOException {
 			short lastColumnNum = row.getLastCellNum();
 			
 			if (lastColumnNum <= 0) {
 				lastColumnNum = 0;	// TODO
 			}
 			
 			Object[] rowValues = (Object[]) Array.newInstance(cellClass, lastColumnNum);
 
 			for (short columnNum = 0; columnNum < lastColumnNum; columnNum++) {
 				rowValues[columnNum] = cellMapper.mapCell (row.getCell(columnNum), rowNum, columnNum);
 			}
 			
 			return rowValues;
 		}
 	}
 
 	/***
 	 * Adapter to enable use of a ObjectArrayRowMapper inside a SheetExtractor.
 	 */
 	private static class ObjectArraySheetExtractor implements SheetExtractor {
 		private final CellMapper cellMapper;
 		private final Class cellClass;
 
 		public ObjectArraySheetExtractor (CellMapper aCellMapper, Class aCellClass) {
 			cellMapper = aCellMapper;
 			cellClass = aCellClass;
 		}
 
 		public Object extractData (HSSFSheet sheet) throws IOException {
 			List rowValues = new ArrayList();
 
 			ObjectArrayRowMapper rowMapper = new ObjectArrayRowMapper (cellMapper, cellClass);
 
 			int maxRowSize = 0;
 			int firstRowIndex = sheet.getFirstRowNum();
 			int lastRowIndex = sheet.getLastRowNum();
 			
 			for (int i = firstRowIndex; i <= lastRowIndex; i++) {
 				HSSFRow row = sheet.getRow (i);
 
 				if (null != row) {
 					Object[] currentRowValues = (Object[]) rowMapper.mapRow (row, i);
 					maxRowSize = Math.max (maxRowSize, currentRowValues.length);
 					rowValues.add (currentRowValues);
 				}
 			}
 
 			return rowValues.toArray ((Object[][]) Array.newInstance (cellClass,
 					new int[] {rowValues.size(), maxRowSize}));
 		}
 	}
 	
 	/***
 	 * RowCallbackHandler implementation that creates a <code>java.util.Map</code>
 	 * for each row and put all maps in a list.
 	 * The first line is used as keys for the maps.
 	 */
 	private static class MapListRowCallbackHandler implements RowCallbackHandler {
 		private final List values = new ArrayList();
 		private final CellMapper cellMapper;
 		private ColumnMapRowMapper rowMapper;
 
 		public MapListRowCallbackHandler (CellMapper aCellMapper) {
 			cellMapper = aCellMapper;
 		}
 
 		public void processRow (HSSFRow row, int rowIndex) throws IOException {
 			if (null == rowMapper) {	// First line, read keys
 				RowMapper firstRowMapper = new ObjectArrayRowMapper (
 						new StringCellMapper(), String.class);
 				
 				String[] keys = (String[]) firstRowMapper.mapRow (row, rowIndex);
 				
 				rowMapper = new ColumnMapRowMapper (keys) {
 					protected CellMapper createCellMapper (int rowNum) {
 						return cellMapper;
 					}
 				};
 			} else {					// Other lines, read values
 				values.add (rowMapper.mapRow (row, rowIndex));
 			}
 		}
 
 		public List getValues() {
 			return values;
 		}
 	}
 
 	/***
 	 * Adapter to enable use of a RowCallbackHandler inside a SheetExtractor.
 	 */
 	private static class RowCallbackHandlerSheetExtractor implements SheetExtractor {
 		private final RowCallbackHandler rowCallbackHandler;
 
 		public RowCallbackHandlerSheetExtractor (RowCallbackHandler aRowCallbackHandler) {
 			rowCallbackHandler = aRowCallbackHandler;
 		}
 
 		public Object extractData (HSSFSheet sheet) throws IOException {
 			int firstRowIndex = sheet.getFirstRowNum();
 			int lastRowIndex = sheet.getLastRowNum();
 
 			for (int i = firstRowIndex; i <= lastRowIndex; i++) {
 				rowCallbackHandler.processRow (sheet.getRow (i), i);
 			}
 
 			return null ;
 		}
 	}
 
 	/***
 	 * Adapter to enable use of a CellCallbackHandler inside a SheetExtractor.
 	 */
 	private static class CellCallbackHandlerSheetExtractor implements SheetExtractor {
 		private final CellCallbackHandler cellCallbackHandler;
 
 		public CellCallbackHandlerSheetExtractor (CellCallbackHandler aCellCallbackHandler) {
 			cellCallbackHandler = aCellCallbackHandler;
 		}
 
 		public Object extractData (HSSFSheet sheet) throws IOException {
 			int firstRowIndex = sheet.getFirstRowNum();
 			int lastRowIndex = sheet.getLastRowNum();
 
 			for (int i = firstRowIndex; i <= lastRowIndex; i++) {
 				HSSFRow row = sheet.getRow (i);
 				if (null != row) {
 					short firstColIndex = row.getFirstCellNum();
 					short lastColIndex = row.getLastCellNum();
 				
 					for (short j = firstColIndex; j < lastColIndex; j++) {
 						cellCallbackHandler.processCell (row.getCell(j), i, j);
 					}
 				}
 			}
 
 			return null ;
 		}
 	}
 }