import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;

import jxl.NumberFormulaCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.biff.formula.FormulaException;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.junit.Ignore;
import org.junit.Test;

import schmitzm.junit.TestingClass;
import schmitzm.swing.TestingUtil;

public class JxlKloseTest extends TestingClass {

	@Test
	@Ignore
	public void testJxl() throws IOException, WriteException, BiffException,
			FormulaException {
		InputStream xlsUrl = JxlKloseTest.class
				.getResourceAsStream("/schmitzm/jxl/gwbalIWEGS_blm_2122010.xls");
		assertNotNull(xlsUrl);

		Workbook wb_vorlage = Workbook.getWorkbook(xlsUrl);
		assertNotNull(wb_vorlage);

		File td = TestingUtil.getNewTempDir();
		File file = new File(td, "new.xls");
		WritableWorkbook wb = Workbook.createWorkbook(file, wb_vorlage);

		WritableSheet sheet = wb.getSheet("domestic extraction");
		assertNotNull(sheet);

		// D3
		{
			jxl.write.Number cellD3 = (jxl.write.Number) sheet.getCell("D3");
			assertNotNull(cellD3);
			log.info("orig value D3 = " + cellD3.getValue());
			assertEquals(0.8, cellD3.getValue(), 0.00001);

			// B32, B33 is fixed
			NumberFormulaCell cellB32 = (NumberFormulaCell) sheet
					.getCell("B32");
			assertNotNull(cellB32);
			log.info("orig value B32 = " + cellB32.getContents());
			assertEquals("B33+((-$D$3/100.0)*B33)", cellB32.getFormula());
			assertEquals(15160.736, cellB32.getValue(), 0.00001);
		}

		// // B7, B8 is dynamic formula
		// NumberFormulaCell cellB7 = (NumberFormulaCell) sheet.getCell("B7");
		// assertNotNull(cellB7);
		// log.info("orig value B7 = " + cellB7.getContents());
		// assertEquals("12402,581", cellB7.getContents());
		// assertEquals("B8+((-$D$3/100.0)*B8)", cellB7.getFormula());

		// D3
		{
			jxl.write.Number cellD3 = (jxl.write.Number) sheet.getCell("D3");
			assertNotNull(cellD3);
			cellD3.setValue(2.0);
			log.info("new value D3 = " + cellD3.getValue());
			assertEquals(2.0, cellD3.getValue(), 0.00001);

		}

		wb.write();
		wb.close();

		{

			// load new file

			Workbook wbnew = Workbook.getWorkbook(file);
			Sheet testsheet = wbnew.getSheet("domestic extraction");

			// B32, B33 is fixed
			NumberFormulaCell cellB32 = (NumberFormulaCell) testsheet
					.getCell("B32");
			assertNotNull(cellB32);
			log.info("new value B32 = " + cellB32.getContents());
			assertEquals("B33+((-$D$3/100.0)*B33)", cellB32.getFormula());
			assertEquals(14977.34, cellB32.getValue(), 0.00001);
		}

		assertTrue(file.exists());
		assertTrue(file.length() > 100);

		// Desktop.getDesktop().open(file);
	}

	@SuppressWarnings("deprecation")
	@Test
	public void testPoi() throws IOException, WriteException, BiffException,
			FormulaException {
		InputStream xlsUrl = JxlKloseTest.class
				.getResourceAsStream("/schmitzm/jxl/gwbalIWEGS_blm_2122010.xls");
		assertNotNull(xlsUrl);

		HSSFWorkbook wb = new HSSFWorkbook(xlsUrl);
		assertNotNull(wb);

		HSSFSheet sheet = wb.getSheet("domestic extraction");
		assertNotNull(sheet);

		// D3
		CellReference cellReference = new CellReference("D3");
		HSSFCell cellD3 = sheet.getRow(cellReference.getRow()).getCell(
				cellReference.getCol());
		assertNotNull(cellD3);
		log.info("orig value D3 = " + cellD3.getNumericCellValue());
		assertEquals(0.8, cellD3.getNumericCellValue(), 0.00001);

		// B32, B33 is fixed
		CellReference b32Ref = new CellReference("B32");
		HSSFCell cellB32 = sheet.getRow(b32Ref.getRow()).getCell(
				b32Ref.getCol());
		assertNotNull(cellB32);
		log.info("orig value B32 = " + cellB32.getNumericCellValue());
		assertEquals("B33+((-$D$3/100)*B33)", cellB32.getCellFormula());
		assertEquals(15160.736, cellB32.getNumericCellValue(), 0.00001);

		// D3
		cellD3.setCellValue(2.0);
		log.info("new value D3 = " + cellD3.getNumericCellValue());
		assertEquals(2.0, cellD3.getNumericCellValue(), 0.00001);

		HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

		// // B32, B33 is fixed
		log.info("new value B32 = " + cellB32.getNumericCellValue());
		assertEquals("B33+((-$D$3/100)*B33)", cellB32.getCellFormula());
		assertEquals(14977.34, cellB32.getNumericCellValue(), 0.00001);

		// B7, B8 is dynamic formula
		CellReference b7cellRef = new CellReference("B7");
		HSSFCell cellB7 = sheet.getRow(b7cellRef.getRow()).getCell(
				b7cellRef.getCol());
		assertNotNull(cellB7);
		log.info("new value B7 = " + cellB7.getNumericCellValue());
		assertEquals("B8+((-$D$3/100)*B8)", cellB7.getCellFormula());
		assertEquals(9038.2964, cellB7.getNumericCellValue(), 0.0001);
		
	}

}