package net.w_horse.excelpojo.xml;

import static org.junit.Assert.assertEquals;

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

import net.w_horse.excelpojo.AbstractTest;
import net.w_horse.excelpojo.ExcelPOJOBridge;
import net.w_horse.excelpojo.ExcelPOJOException;
import net.w_horse.excelpojo.excel.CellNotFoundException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.junit.Test;
import org.springframework.beans.factory.BeanDefinitionStoreException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import test.xml.XMLTest;
import test.xml.XMLTestElement;

public class LabeledCellTest extends AbstractTest {
	/**
	 * ラベルによる位置検索に対し、データ取得位置（retrieveFrom=right）の動作テスト
	 *
	 */
	@Test
	public void testReadAndSaveLabeledCellRetrieveFromRight() throws ExcelPOJOException, IOException {
		ApplicationContext context = new ClassPathXmlApplicationContext("./config/testReadAndSaveLabeledCell.xml");
		ExcelPOJOBridge bridge = (ExcelPOJOBridge)context.getBean("testReadAndSaveLabeledCellRetrieveFromRight");
		XMLTest targetBean = (XMLTest) bridge.load("./files/upload/ParserTest.xls", "ラベル読み込み位置");

		assertEquals("基本（取得）：LabeledCell：String", "右のデータ", targetBean.getStringField4LabeledCell1());

		bridge.save(
				"./files/template/ParserTest_Template.xls",
				"ラベル読み込み位置",
				"./files/export/XMLParserTest_ラベル読み込み位置（右）.xls",
				targetBean);

		FileInputStream fileInputStream = null;
		try {
			fileInputStream = new FileInputStream("./files/export/XMLParserTest_ラベル読み込み位置（右）.xls");
			HSSFWorkbook workBook = new HSSFWorkbook(new POIFSFileSystem(fileInputStream));
			HSSFSheet sheet = workBook.getSheet("ラベル読み込み位置");

			assertEquals("基本（書き込み）：LabeledCell：String", "右のデータ", getCellValue(sheet, 4, 4, CELL_TYPE_STRING));
		} finally {
			try { fileInputStream.close(); } catch (Exception e) { }
		}
	}

	/**
	 * ラベルによる位置検索に対し、データ取得位置（retrieveFrom=down）の動作テスト
	 *
	 */
	@Test
	public void testReadAndSaveLabeledCellRetrieveFromBottom() throws ExcelPOJOException, IOException {
		ApplicationContext context = new ClassPathXmlApplicationContext("./config/testReadAndSaveLabeledCell.xml");
		ExcelPOJOBridge bridge = (ExcelPOJOBridge)context.getBean("testReadAndSaveLabeledCellRetrieveFromBottom");
		XMLTest targetBean = (XMLTest) bridge.load("./files/upload/ParserTest.xls", "ラベル読み込み位置");

		assertEquals("基本（取得）：LabeledCell：String", "下のデータ", targetBean.getStringField4LabeledCell1());

		bridge.save(
				"./files/template/ParserTest_Template.xls",
				"ラベル読み込み位置",
				"./files/export/XMLParserTest_ラベル読み込み位置（下）.xls",
				targetBean);

		FileInputStream fileInputStream = null;
		try {
			fileInputStream = new FileInputStream("./files/export/XMLParserTest_ラベル読み込み位置（下）.xls");
			HSSFWorkbook workBook = new HSSFWorkbook(new POIFSFileSystem(fileInputStream));
			HSSFSheet sheet = workBook.getSheet("ラベル読み込み位置");

			assertEquals("基本（書き込み）：LabeledCell：String", "下のデータ", getCellValue(sheet, 5, 3, CELL_TYPE_STRING));
		} finally {
			try { fileInputStream.close(); } catch (Exception e) { }
		}
	}

	/**
	 * ラベルによる位置検索に対し、データ取得位置（retrieveFrom=left）の動作テスト
	 *
	 */
	@Test
	public void testReadAndSaveLabeledCellRetrieveFromLeft() throws ExcelPOJOException, IOException {
		ApplicationContext context = new ClassPathXmlApplicationContext("./config/testReadAndSaveLabeledCell.xml");
		ExcelPOJOBridge bridge = (ExcelPOJOBridge)context.getBean("testReadAndSaveLabeledCellRetrieveFromLeft");
		XMLTest targetBean = (XMLTest) bridge.load("./files/upload/ParserTest.xls", "ラベル読み込み位置");

		assertEquals("基本（取得）：LabeledCell：String", "左のデータ", targetBean.getStringField4LabeledCell1());

		bridge.save(
				"./files/template/ParserTest_Template.xls",
				"ラベル読み込み位置",
				"./files/export/XMLParserTest_ラベル読み込み位置（左）.xls",
				targetBean);

		FileInputStream fileInputStream = null;
		try {
			fileInputStream = new FileInputStream("./files/export/XMLParserTest_ラベル読み込み位置（左）.xls");
			HSSFWorkbook workBook = new HSSFWorkbook(new POIFSFileSystem(fileInputStream));
			HSSFSheet sheet = workBook.getSheet("ラベル読み込み位置");

			assertEquals("基本（書き込み）：LabeledCell：String", "左のデータ", getCellValue(sheet, 4, 2, CELL_TYPE_STRING));
		} finally {
			try { fileInputStream.close(); } catch (Exception e) { }
		}
	}

	/**
	 * 結合されたラベルによるデータ取得（margedLabel）の動作テスト
	 *
	 */
	@Test
	public void testReadAndSaveLabeledCellMargedLabel() throws ExcelPOJOException, IOException {
		ApplicationContext context = new ClassPathXmlApplicationContext("./config/testReadAndSaveLabeledCell.xml");
		ExcelPOJOBridge bridge = (ExcelPOJOBridge)context.getBean("testReadAndSaveLabeledCellMargedLabel");
		XMLTest targetBean = (XMLTest) bridge.load("./files/upload/ParserTest.xls", "Users2");

		assertEquals("基本（取得）：LabeledCell：String", "会員リスト", targetBean.getStringField4LabeledCell1());
		assertEquals("基本（取得）：LabeledCell：String", "ExcelPOJOの会員", targetBean.getStringField4PointedCell1());
		assertEquals("基本（取得）：LabeledCell：String", "会員リスト（２）", targetBean.getStringField4ConstantValue1());

		bridge.save(
				"./files/template/ParserTest_Template.xls",
				"Users2",
				"./files/export/XMLParserTest_ラベル連結.xls",
				targetBean);

		FileInputStream fileInputStream = null;
		try {
			fileInputStream = new FileInputStream("./files/export/XMLParserTest_ラベル連結.xls");
			HSSFWorkbook workBook = new HSSFWorkbook(new POIFSFileSystem(fileInputStream));
			HSSFSheet sheet = workBook.getSheet("Users2");

			assertEquals("基本（書き込み）：LabeledCell：String", "会員リスト", getCellValue(sheet, 1, 2, CELL_TYPE_STRING));
			assertEquals("基本（書き込み）：LabeledCell：String", "ExcelPOJOの会員", getCellValue(sheet, 2, 2, CELL_TYPE_STRING));
			assertEquals("基本（書き込み）：LabeledCell：String", "会員リスト（２）", getCellValue(sheet, 1, 5, CELL_TYPE_STRING));
		} finally {
			try { fileInputStream.close(); } catch (Exception e) { }
		}
	}

	/**
	 * ラベルの読み込みに必須指定（use=required）を指定した際の動作テスト
	 * 必須だが該当する表が見つからない場合⇒例外があがる
	 */
	@Test(expected=CellNotFoundException.class)
	public void testReadAndSaveLabeledCellUseRequired() throws ExcelPOJOException, IOException {
		ApplicationContext context = new ClassPathXmlApplicationContext("./config/testReadAndSaveLabeledCell.xml");
		ExcelPOJOBridge bridge = (ExcelPOJOBridge)context.getBean("testReadAndSaveLabeledCellUseRequired");
		@SuppressWarnings("unused")
		XMLTest targetBean = (XMLTest) bridge.load("./files/upload/ParserTest.xls", "ラベル読み込み位置");
	}

	/**
	 * 範囲指定検索のテスト
	 * ラベルに対する指定の場合（必須にしているので検索不一致の場合は例外があがる）
	 * @throws ExcelPOJOException
	 */
	@Test(expected=CellNotFoundException.class)
	public void testRangeLabeledCell() throws ExcelPOJOException {
		ApplicationContext context = new ClassPathXmlApplicationContext("./config/testReadAndSaveLabeledCell.xml");
		ExcelPOJOBridge bridge = (ExcelPOJOBridge)context.getBean("testRangeLabeledCell");
		@SuppressWarnings("unused")
		XMLTest targetBean = (XMLTest) bridge.load("./files/upload/ParserTest.xls", "基本");
	}

	/**
	 * 範囲指定検索のテスト
	 * ラベルの指定が空白だった場合
	 * @throws Exception
	 */
	@Test(expected=ExcelPOJOException.class)
	public void testLabeledCellLabelBlank() throws Exception {
		try {
			ApplicationContext context = new ClassPathXmlApplicationContext("./config/testReadAndSaveLabeledCell.xml");
			ExcelPOJOBridge bridge = (ExcelPOJOBridge)context.getBean("testLabeledCellLabelBlank");
			@SuppressWarnings("unused")
			XMLTest targetBean = (XMLTest) bridge.load("./files/upload/ParserTest.xls", "基本");
		} catch (Exception e) {
			assertEquals("ラベルの指定が空白だった場合",
					"java.lang.IllegalArgumentException: The label is not specified.",
					e.getMessage());
			throw e;
		}
	}

	/**
	 * 不正な定義のテスト
	 * LabeledCell:retrieveFromに不正な値を指定
	 * XMLの書式エラー
	 * @throws ExcelPOJOException
	 */
	@Test(expected=BeanDefinitionStoreException.class)
	public void testLabeledCellInvalidRetrieveFrom() throws ExcelPOJOException {
		ApplicationContext context = new ClassPathXmlApplicationContext("./config/XMLTestsInvalidRangeInvalidRetrievFrom.xml");
		ExcelPOJOBridge bridge = (ExcelPOJOBridge)context.getBean("testLabeledCellInvalidRetrieveFrom");
		@SuppressWarnings("unused")
		XMLTestElement targetBean = (XMLTestElement) bridge.load("./files/upload/ParserTest.xls", "基本");
	}

}
