/*
    * The baseCode project
    * 
    * Copyright (c) 2007-2019 University of British Columbia
    * 
    * 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 ubic.basecode.io.excel;
  
  import org.apache.poi.hssf.usermodel.*;
  import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  import org.apache.poi.ss.usermodel.CellType;
  
  import java.io.FileInputStream;
  import java.io.IOException;
  import java.util.HashSet;
  import java.util.LinkedList;
  import java.util.List;
  import java.util.Set;
  
  /**
   * Utilities for dealign with Microsoft Excel spreadsheets as implemented in commons-poi.
   * 
   * @author lfrench
   * 
   */
  public class ExcelUtil {
  
      /**
       * @param filename
       * @param sheetName
       * @return
       * @throws IOException
       */
      @SuppressWarnings("resource")
      public static HSSFSheet getSheetFromFile( String filename, String sheetName ) throws IOException {
          POIFSFileSystem fs = new POIFSFileSystem( new FileInputStream( filename ) );
          HSSFWorkbook wb = new HSSFWorkbook( fs );
          return wb.getSheet( sheetName );
      }
  
      /**
       * @param sheet
       * @param row
       * @param col
       * @return
       */
      public static String getValue( HSSFSheet sheet, int row, int col ) {
          if ( col > 255 ) {
              throw new RuntimeException( "Column position is over 255" );
          }
          if ( sheet.getRow( row ) == null ) return null;
          HSSFCell cell = sheet.getRow( row ).getCell( col );
          if ( cell == null ) {
              return null;
          }
  
          if ( cell.getCellType() == CellType.STRING ) return cell.getRichStringCellValue().getString();
  
          if ( cell.getCellType() == CellType.NUMERIC ) {
              // WARNING not ideal for numbers.
              return Double.toString( cell.getNumericCellValue() );
          }
          if ( cell.getCellType() == CellType.FORMULA ) return cell.getCellFormula();
  
          return "";
      }
  
      /**
       * @param sheet
       * @param column
       * @param header
       * @param clean
       * @return
       */
      public static Set<String> grabColumnValues( HSSFSheet sheet, int column, boolean header, boolean clean ) {
          return new HashSet<String>( grabColumnValuesList( sheet, column, header, clean ) );
      }
  
      /**
       * Gets all the strings from a column, possibly excluding header and possibly trimming and lowercasing
       * 
       * @param sheet
       * @param column
       * @param header true if it has a header
       * @param clean if true it will trim and lowercase the strings
       * @return
       */
     public static Set<String> grabColumnValues( HSSFSheet sheet, int column, boolean header, boolean clean,
             SpreadSheetFilter f ) {
         return new HashSet<String>( grabColumnValuesList( sheet, column, header, clean, f ) );
     }
 
     /**
      * @param sheet
      * @param column
      * @param header
      * @param clean
      * @return
      */
     public static List<String> grabColumnValuesList( HSSFSheet sheet, int column, boolean header, boolean clean ) {
         return grabColumnValuesList( sheet, column, header, clean, new SpreadSheetFilter() {
             @Override
             public boolean accept( HSSFSheet s, int row ) {
                 return true;
             }
         } );
     }
 
     /**
      * @param sheet
      * @param column the index of the column to get
      * @param header if there is a header row to be skipped
      * @param clean lower case
      * @param f
      * @return
      */
     public static List<String> grabColumnValuesList( HSSFSheet sheet, int column, boolean header, boolean clean,
             SpreadSheetFilter f ) {
         List<String> result = new LinkedList<String>();
 
         int rows = sheet.getLastRowNum() + 1;
         for ( int i = 0; i < rows; i++ ) {
             if ( header && i == 0 ) continue;
             String term = ExcelUtil.getValue( sheet, i, column );
             if ( term == null ) continue;
 
             if ( f.accept( sheet, i ) ) {
                 term = term.trim();
                 if ( clean ) term = term.toLowerCase();
                 result.add( term );
             }
         }
         return result;
     }
 
     public static void main( String args[] ) {
         HSSFWorkbook workbook = new HSSFWorkbook();
         HSSFSheet spreadsheet = workbook.createSheet();
         ExcelUtil.setFormula( spreadsheet, 1, 1, "HYPERLINK(\"x\",\"x\")" );
 
     }
 
     /**
      * @param sheet
      * @param row
      * @param col
      * @param value
      */
     public static void setFormula( HSSFSheet sheet, int row, int col, String value ) {
         HSSFRow r = sheet.getRow( row );
         if ( r == null ) {
             r = sheet.createRow( row );
         }
         HSSFCell c = r.createCell( col );
         c.setCellFormula( value );
     }
 
     /**
      * @param sheet
      * @param row
      * @param col
      * @param value
      */
     public static void setValue( HSSFSheet sheet, int row, int col, double value ) {
         HSSFRow r = sheet.getRow( row );
         if ( r == null ) {
             r = sheet.createRow( row );
         }
         HSSFCell c = r.createCell( col );
         c.setCellType( CellType.NUMERIC );
         c.setCellValue( value );
 
     }
 
     /**
      * @param sheet
      * @param row
      * @param col
      * @param value
      */
     public static void setValue( HSSFSheet sheet, int row, int col, int value ) {
         setValue( sheet, row, col, ( double ) value );
     }
 
     /**
      * @param sheet
      * @param row
      * @param col
      * @param value
      */
     public static void setValue( HSSFSheet sheet, int row, int col, String value ) {
         HSSFRow r = sheet.getRow( row );
         if ( r == null ) {
             r = sheet.createRow( row );
         }
         HSSFCell c = r.createCell( col );
         c.setCellType( CellType.STRING );
         c.setCellValue( new HSSFRichTextString( value ) );
     }
 }