/*************************************************************************
 *
 * Copyright 2009 by bBreak Systems.
 *
 * ExCella Core - ExcelファイルをJavaから利用するための共通基盤
 *
 * $Id: PoiUtilTest.java 53 2009-05-21 02:03:24Z yuta-takahashi $
 * $Revision: 53 $
 *
 * This file is part of ExCella Core.
 *
 * ExCella Core is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License version 3
 * only, as published by the Free Software Foundation.
 *
 * ExCella Core is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License version 3 for more details
 * (a copy is included in the COPYING.LESSER file that accompanied this code).
 *
 * You should have received a copy of the GNU Lesser General Public License
 * version 3 along with ExCella Core.  If not, see
 * <http://www.gnu.org/licenses/lgpl-3.0-standalone.html>
 * for a copy of the LGPLv3 License.
 *
 ************************************************************************/
package org.bbreak.excella.core.util;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.io.IOException;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.values.XmlValueDisconnectedException;
import org.bbreak.excella.core.BookController;
import org.bbreak.excella.core.WorkbookTest;
import org.junit.Test;

/**
 * PoiUtilテストクラス
 * 
 * @since 1.0
 */
public class PoiUtilTest extends WorkbookTest {

    /**
     * コンストラクタ
     * 
     * @param version Excelファイルのバージョン
     */
    public PoiUtilTest( String version) {
        super( version);
    }

    @Test
    public void testPoiUtil() throws IOException, ParseException {

        Workbook workbook = getWorkbook();
        Sheet sheet_1 = workbook.getSheetAt( 0);
        Sheet sheet_2 = workbook.getSheetAt( 1);
        // Sheet sheet_3 = workbook.getSheetAt( 2);
        Sheet sheet_4 = workbook.getSheetAt( 3);
        Sheet sheet_5 = workbook.getSheetAt( 4);
        Sheet sheet_6 = workbook.getSheetAt( 5);

        Date expectedDate = DateFormat.getDateInstance().parse( "2009/4/16");
        String expectedString = "あああ";

        // ===============================================
        // isCellDateFormatted( Cell cell)
        // ===============================================
        assertEquals( Boolean.FALSE, PoiUtil.isCellDateFormatted( null));

        // ===============================================
        // getJavaDate( double excelDate)
        // ===============================================
        double excelDate = 39919; // 2009/4/16 --> 39919
        Date javaDate = PoiUtil.getJavaDate( excelDate);
        assertEquals( expectedDate, javaDate);

        // ===============================================
        // getCellValue(Cell cell)
        // ===============================================
        Object object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0));
        assertEquals( new Double( 10.0), object);

        // ===============================================
        // getCellValue(Cell cell, Class<?> propertyClass)
        // ===============================================
        // --> Short
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), Short.class);
        assertEquals( Short.class, object.getClass());

        // --> Integer
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), Integer.class);
        assertEquals( Integer.class, object.getClass());

        // --> Long
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), Long.class);
        assertEquals( Long.class, object.getClass());

        // --> Float
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), Float.class);
        assertEquals( Float.class, object.getClass());

        // --> Double
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), Double.class);
        assertEquals( Double.class, object.getClass());

        // --> BigDecimal
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), BigDecimal.class);
        assertEquals( BigDecimal.class, object.getClass());

        // --> Byte
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), Byte.class);
        assertEquals( Byte.class, object.getClass());

        // --> Date
        object = PoiUtil.getCellValue( sheet_1.getRow( 5).getCell( 0), Date.class);
        assertEquals( Date.class, object.getClass());

        // --> String
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), String.class);
        assertEquals( String.class, object.getClass());

        // --> Boolean
        object = PoiUtil.getCellValue( sheet_1.getRow( 3).getCell( 0), Boolean.class);
        assertEquals( Boolean.class, object.getClass());

        // --> boolean
        object = PoiUtil.getCellValue( sheet_1.getRow( 3).getCell( 0), boolean.class);
        assertEquals( Boolean.class, object.getClass());

        // --> byte
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), byte.class);
        assertEquals( Byte.class, object.getClass());

        // --> short
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), short.class);
        assertEquals( Short.class, object.getClass());

        // --> int
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), int.class);
        assertEquals( Integer.class, object.getClass());

        // --> long
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), long.class);
        assertEquals( Long.class, object.getClass());

        // --> float
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), float.class);
        assertEquals( Float.class, object.getClass());

        // --> double
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), double.class);
        assertEquals( Double.class, object.getClass());

        // --> other
        object = PoiUtil.getCellValue( sheet_1.getRow( 0).getCell( 0), Object.class);
        assertEquals( null, object);
        
        // ===============================================
        // getSheetName(Sheet sheet)
        // ===============================================
        String sheetname = PoiUtil.getSheetName( sheet_1.getRow( 0).getCell( 0));
        assertEquals( "Sheet1", sheetname);

        // ===============================================
        // getSheetName(Sheet sheet)
        // ===============================================
        sheetname = PoiUtil.getSheetName( workbook.getSheetAt( 0));
        assertEquals( "Sheet1", sheetname);

        // ===============================================
        // getCellValue(Sheet sheet, int rowIndex, int columnIndex)
        // ===============================================
        // CELL_TYPE_NUMERIC
        Object cellValue = PoiUtil.getCellValue( sheet_1, 0, 0);
        assertEquals( 10.0, cellValue);

        // CELL_TYPE_FORMULA
        cellValue = PoiUtil.getCellValue( sheet_1, 1, 0);
        assertEquals( 10000.0, cellValue);

        // CELL_TYPE_STRING
        cellValue = PoiUtil.getCellValue( sheet_1, 2, 0);
        assertEquals( expectedString, cellValue);

        // CELL_TYPE_BOOLEAN
        cellValue = PoiUtil.getCellValue( sheet_1, 3, 0);
        assertEquals( Boolean.TRUE, cellValue);

        // CELL_TYPE_ERROR
        cellValue = PoiUtil.getCellValue( sheet_1, 4, 0);
        if ( workbook instanceof HSSFWorkbook) {
            // #N/Aのエラーコード
            assertEquals( new Byte( "42"), cellValue);
        } else if ( workbook instanceof XSSFWorkbook) {
            // XSSF形式の場合はエラーの文字列を返却
            assertEquals( "#N/A", cellValue);
        }

        // CELL_TYPE_NUMERIC -> Date
        cellValue = PoiUtil.getCellValue( sheet_1, 5, 0);
        assertEquals( expectedDate, cellValue);

        // XXX
        // CELL_TYPE_BLANK
        cellValue = PoiUtil.getCellValue( sheet_1, 6, 0);
        assertEquals( null, cellValue);

        // CELL_TYPE_FORMULA -> CELL_TYPE_NUMERIC
        cellValue = PoiUtil.getCellValue( sheet_1, 7, 0);
        assertEquals( 10.0, cellValue);

        // CELL_TYPE_FORMULA -> CELL_TYPE_STRING
        cellValue = PoiUtil.getCellValue( sheet_1, 8, 0);
        assertEquals( expectedString, cellValue);

        // CELL_TYPE_FORMULA -> CELL_TYPE_BOOLEAN
        cellValue = PoiUtil.getCellValue( sheet_1, 9, 0);
        assertEquals( Boolean.TRUE, cellValue);

        // CELL_TYPE_FORMULA -> CELL_TYPE_ERROR
        cellValue = PoiUtil.getCellValue( sheet_1, 10, 0);
        // #N/Aを返却
        if ( workbook instanceof HSSFWorkbook) {
            // #N/Aのエラーコード
            assertEquals( new Byte( "42"), cellValue);
        } else if ( workbook instanceof XSSFWorkbook) {
            // XSSF形式の場合はエラーの文字列を返却
            assertEquals( "#N/A", cellValue);
        }

        // CELL_TYPE_FORMULA -> Date
        cellValue = PoiUtil.getCellValue( sheet_1, 11, 0);
        assertEquals( expectedDate, cellValue);

        // XXX
        // CELL_TYPE_FORMULA -> CELL_TYPE_BLANK
        cellValue = PoiUtil.getCellValue( sheet_1, 12, 0);
        assertEquals( new Double( 0.0), cellValue);

        // ===============================================
        // writeBook( Workbook workbook, String filename)
        // ===============================================
        String extension = BookController.HSSF_SUFFIX;
        if ( workbook instanceof XSSFWorkbook) {
            extension = BookController.XSSF_SUFFIX;
        }
        PoiUtil.writeBook( workbook, "c:\\result" + extension);

//        // ===============================================
//        // copyCell( Cell fromCell, Cell toCell)
//        // ===============================================
//        Cell fromCellBlank = sheet_2.getRow( 6).getCell( 0);
//        Cell toCellBlank = sheet_2.getRow( 6).getCell( 2);
//        Cell fromCellFormula = sheet_2.getRow( 1).getCell( 0);
//        Cell toCellFormula = sheet_2.getRow( 1).getCell( 2);
//        Cell fromCellBoolean = sheet_2.getRow( 3).getCell( 0);
//        Cell toCellBoolean = sheet_2.getRow( 3).getCell( 2);
//        Cell fromCellError = sheet_2.getRow( 4).getCell( 0);
//        Cell toCellError = sheet_2.getRow( 4).getCell( 2);
//        Cell fromCellNumeric  = sheet_2.getRow( 0).getCell( 0);
//        Cell toCellNumeric = sheet_2.getRow( 0).getCell( 2);
//        Cell fromCellString = sheet_2.getRow( 2).getCell( 0);
//        Cell toCellString = sheet_2.getRow( 2).getCell( 2);
//        
//        PoiUtil.copyCell( fromCellBlank, toCellBlank);
//        PoiUtil.copyCell( fromCellFormula, toCellFormula);
//        PoiUtil.copyCell( fromCellBoolean, toCellBoolean);
//        PoiUtil.copyCell( fromCellError, toCellError);
//        PoiUtil.copyCell( fromCellNumeric, toCellNumeric);
//        PoiUtil.copyCell( fromCellString, toCellString);
//
//        assertEquals( fromCellBlank.getStringCellValue(), toCellBlank.getStringCellValue());
//        assertEquals( fromCellFormula.getCellFormula(), toCellFormula.getCellFormula());
//        assertEquals( fromCellBoolean.getBooleanCellValue(), toCellBoolean.getBooleanCellValue());
//        assertEquals( PoiUtil.getCellValue( fromCellError), PoiUtil.getCellValue( toCellError));
//        assertEquals( (Double) fromCellNumeric.getNumericCellValue(), (Double) toCellNumeric.getNumericCellValue());
//        assertEquals( fromCellString.getStringCellValue(), toCellString.getStringCellValue());
//            
//        try {
//            PoiUtil.copyCell( fromCellBlank, null);
//            fail();    
//        } catch (NullPointerException e) {
//            // toセルがnullの場合は例外が発生
//        }
        
        
        // ===============================================
        // insertRangeDown( Sheet sheet, CellRangeAddress rangeAddress)
        // ===============================================
        // XXX
        // Error:
        // PoiUtil.insertRangeDown( sheet_2, new CellRangeAddress(0, 6, 0, 0));

        // ===============================================
        // insertRangeRight( Sheet sheet, CellRangeAddress rangeAddress)
        // ===============================================
        // XXX
        // Error:
        // PoiUtil.insertRangeRight( sheet_3, new CellRangeAddress(0, 14, 0, 1));

        // ===============================================
        // prepareCloneSheet( Sheet sheet)
        // ===============================================
        PoiUtil.prepareCloneSheet( sheet_2);

        // ===============================================
        // crossRangeAddress( CellRangeAddress baseAddress, CellRangeAddress targetAddress)
        // ===============================================
        CellRangeAddress rangeAddress1 = new CellRangeAddress( 0, 2, 0, 2);
        CellRangeAddress rangeAddress2 = new CellRangeAddress( 3, 4, 3, 4);
        CellRangeAddress rangeAddress3 = new CellRangeAddress( 0, 1, 0, 1);
        // 重なっていない
        assertFalse( PoiUtil.crossRangeAddress( rangeAddress1, rangeAddress2));
        // 重なっている
        assertTrue( PoiUtil.crossRangeAddress( rangeAddress1, rangeAddress3));

        // ===============================================
        // containCellRangeAddress( CellRangeAddress baseAddress, CellRangeAddress targetAddress)
        // ===============================================
        // 含まれていない
        assertFalse( PoiUtil.containCellRangeAddress( rangeAddress1, rangeAddress2));
        // 含まれてている
        assertTrue( PoiUtil.containCellRangeAddress( rangeAddress1, rangeAddress3));

        // ===============================================
        // setHyperlink(  Cell cell, int type, String address)
        // ===============================================
        Cell cellHyperlink = sheet_4.getRow( 0).getCell( 0);
        String address = "http://sourceforge.jp/projects/excella-core/";
        PoiUtil.setHyperlink( cellHyperlink, Hyperlink.LINK_URL, address);
        Hyperlink hyperLink = cellHyperlink.getHyperlink();
        if (hyperLink instanceof HSSFHyperlink) {
            assertEquals( address, ((HSSFHyperlink) hyperLink).getTextMark());
        } else if (hyperLink instanceof XSSFHyperlink) {
            assertEquals( address, ((XSSFHyperlink) hyperLink).getAddress());
        }
        
        // ===============================================
        // setCellValue( Cell cell, Object value)
        // ===============================================
        Cell cellString = sheet_4.getRow( 1).getCell( 0);
        Cell cellNumber = sheet_4.getRow( 1).getCell( 1);
        Cell cellFloat = sheet_4.getRow( 1).getCell( 2);
        Cell cellDate = sheet_4.getRow( 1).getCell( 3);
        Cell cellBoolean = sheet_4.getRow( 1).getCell( 4);
        Cell cellNull = sheet_4.getRow( 1).getCell( 5);
        
        String stringValue = "aaa";
        Number numberValue = new Double( 10);
        Float floatValue = new Float( 10f);
        Date dateValue = new Date();
        Boolean booleanValue = Boolean.TRUE;
        
        PoiUtil.setCellValue( cellString, stringValue);
        PoiUtil.setCellValue( cellNumber, numberValue);
        PoiUtil.setCellValue( cellFloat, floatValue);
        PoiUtil.setCellValue( cellDate, dateValue);
        PoiUtil.setCellValue( cellBoolean, booleanValue);
        PoiUtil.setCellValue( cellNull, null);
        
        assertEquals( stringValue, cellString.getStringCellValue());
        assertEquals( numberValue, cellNumber.getNumericCellValue());
        assertEquals( new Double( String.valueOf( floatValue)), (Double) cellFloat.getNumericCellValue());
        assertEquals( dateValue, cellDate.getDateCellValue());
        assertEquals( booleanValue, cellBoolean.getBooleanCellValue());
        assertEquals( "", PoiUtil.getCellValue( cellNull));

        try {
            PoiUtil.setCellValue( null, stringValue);
            fail();
        } catch (NullPointerException ex) {
            // セルがnullの場合は例外が発生
        }

        // ===============================================
        // getLastColNum( Sheet sheet)
        // ===============================================
        int lastColNum1 = PoiUtil.getLastColNum( sheet_5);
        int lastColNum2 = PoiUtil.getLastColNum( sheet_6);
        assertEquals( -1, lastColNum1);
        assertEquals( 10, lastColNum2);

    }

    @Test
    public void excelDate() throws ParseException {

        Workbook workbook = getWorkbook();
        Sheet sheet_1 = workbook.getSheetAt( 0);

        Date expectedDate = DateFormat.getDateInstance().parse( "2009/4/16");

        // ===============================================
        // 日付取得の調査用
        // ===============================================
        // CELL_TYPE_NUMERIC-Date:*yyyy年MM月dd日
        Object cellValue = PoiUtil.getCellValue( sheet_1, 0, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:yyyy年MM月dd日
        cellValue = PoiUtil.getCellValue( sheet_1, 1, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:yyyy年MM月
        cellValue = PoiUtil.getCellValue( sheet_1, 2, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:MM月dd日
        cellValue = PoiUtil.getCellValue( sheet_1, 3, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:yyyy/MM/dd
        cellValue = PoiUtil.getCellValue( sheet_1, 4, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:yyyy/MM/dd 12:00 AM
        cellValue = PoiUtil.getCellValue( sheet_1, 5, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:yyyy/MM/dd 0:00
        cellValue = PoiUtil.getCellValue( sheet_1, 6, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:MM/dd
        cellValue = PoiUtil.getCellValue( sheet_1, 7, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:MM/dd/yy（月日０埋め無）
        cellValue = PoiUtil.getCellValue( sheet_1, 8, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:MM/dd/yy（月日０埋め有）
        cellValue = PoiUtil.getCellValue( sheet_1, 9, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:dd-month
        cellValue = PoiUtil.getCellValue( sheet_1, 10, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:dd-month-yy
        cellValue = PoiUtil.getCellValue( sheet_1, 11, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:dd-month-yy
        cellValue = PoiUtil.getCellValue( sheet_1, 12, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:month-yy
        cellValue = PoiUtil.getCellValue( sheet_1, 13, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:month-yy
        cellValue = PoiUtil.getCellValue( sheet_1, 14, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:month
        cellValue = PoiUtil.getCellValue( sheet_1, 15, 5);
        assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:month-yy
        cellValue = PoiUtil.getCellValue( sheet_1, 16, 5);
        assertEquals( expectedDate, cellValue);

        // TODO : Localizeされたフォーマットに対応していない
        // CELL_TYPE_NUMERIC-Date:Hyy.MM.dd
        cellValue = PoiUtil.getCellValue( sheet_1, 17, 5);
        // assertEquals( expectedDate, cellValue);

        // CELL_TYPE_NUMERIC-Date:平成yy年MM月dd日
        cellValue = PoiUtil.getCellValue( sheet_1, 18, 5);
        // assertEquals( expectedDate, cellValue);

    }
}
