trunk/odfdom/src/test/java/org/odftoolkit/odfdom/doc/table/TableCellTest.java

/************************************************************************
* 
*  Licensed to the Apache Software Foundation (ASF) under one
*  or more contributor license agreements.  See the NOTICE file
*  distributed with this work for additional information
*  regarding copyright ownership.  The ASF licenses this file
*  to you 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.odftoolkit.odfdom.doc.table;

import java.text.DecimalFormatSymbols;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import junit.framework.Assert;

import org.junit.Before;
import org.junit.Test;
import org.odftoolkit.odfdom.doc.OdfSpreadsheetDocument;
import org.odftoolkit.odfdom.doc.OdfTextDocument;
import org.odftoolkit.odfdom.dom.OdfContentDom;
import org.odftoolkit.odfdom.dom.OdfStylesDom;
import org.odftoolkit.odfdom.dom.attribute.office.OfficeValueTypeAttribute;
import org.odftoolkit.odfdom.dom.element.draw.DrawFrameElement;
import org.odftoolkit.odfdom.dom.element.draw.DrawImageElement;
import org.odftoolkit.odfdom.dom.element.style.StyleParagraphPropertiesElement;
import org.odftoolkit.odfdom.dom.style.OdfStyleFamily;
import org.odftoolkit.odfdom.incubator.doc.number.OdfNumberDateStyle;
import org.odftoolkit.odfdom.incubator.doc.number.OdfNumberStyle;
import org.odftoolkit.odfdom.incubator.doc.office.OdfOfficeAutomaticStyles;
import org.odftoolkit.odfdom.incubator.doc.office.OdfOfficeStyles;
import org.odftoolkit.odfdom.incubator.doc.style.OdfStyle;
import org.odftoolkit.odfdom.type.Color;
import org.odftoolkit.odfdom.utils.ResourceUtilities;

public class TableCellTest {

	final static String SAMPLE_SPREADSHEET = "TestSpreadsheetTable";
	final static String SAMPLE_STYLE_SPREADSHEET = "TestSpreadsheetStyleTable";
	final static String SAMPLE_TEXT = "TestTextTable";
	OdfSpreadsheetDocument odsdoc, odsstyle;
	OdfTextDocument odtdoc;
	OdfTable odsTable, odtTable;

	@Before
	public void setUp() {
		try {
			odsdoc = (OdfSpreadsheetDocument) OdfSpreadsheetDocument.loadDocument(ResourceUtilities.getAbsolutePath(SAMPLE_SPREADSHEET
					+ ".ods"));
			odtdoc = (OdfTextDocument) OdfTextDocument.loadDocument(ResourceUtilities.getAbsolutePath(SAMPLE_TEXT + ".odt"));

		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
		}
	}

	private void saveods() {
		try {
			odsdoc.save(ResourceUtilities.newTestOutputFile(SAMPLE_SPREADSHEET
					+ "Output.ods"));

		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
		}
	}

	private void saveodt() {
		try {
			odtdoc.save(ResourceUtilities.newTestOutputFile(SAMPLE_TEXT
					+ "Output.odt"));
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
		}
	}

	@Test
	public void testGetIndexInRowColumn() {
		int rowindex = 2, columnindex = 1;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell cell = table.getCellByPosition(columnindex, rowindex);

		Assert.assertEquals(rowindex, cell.getRowIndex());
		Assert.assertEquals(columnindex, cell.getColumnIndex());

		OdfTable table3 = odtdoc.getTableByName("Table3");
		OdfTableCell cell1 = table.getCellByPosition(0, 1);
		Assert.assertEquals(1, cell1.getRowIndex());
		Assert.assertEquals(0, cell1.getColumnIndex());
	}

	@Test
	public void testGetSetHoriAlignment() {
		int rowindex = 3, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);

		String align = fcell.getHorizontalAlignment();
		Assert.assertEquals("center", align);

		fcell.setHorizontalAlignment(null);
		String newAlign = fcell.getHorizontalAlignment();
		Assert.assertEquals(null, newAlign);

		fcell.setHorizontalAlignment("start");
		align = fcell.getHorizontalAlignment();
		Assert.assertEquals("start", align);

		// "left" and "right" should be mapped as "start" and "end".
		fcell.setHorizontalAlignment("left");
		align = fcell.getHorizontalAlignment();
		Assert.assertEquals("start", align);
		fcell.setHorizontalAlignment("right");
		align = fcell.getHorizontalAlignment();
		Assert.assertEquals("end", align);
		saveods();

		OdfSpreadsheetDocument ods;
		try {
			ods = OdfSpreadsheetDocument.newSpreadsheetDocument();
			OdfTable tbl = ods.getTableByName("Sheet1");
			OdfTableCell cell = tbl.getCellByPosition(0, 0);
			String horizonAlignment = cell.getHorizontalAlignment();
			Assert.assertEquals(null, horizonAlignment);
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
	}

	@Test
	public void testGetSetVertAlignment() {
		int rowindex = 3, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);

		String align = fcell.getVerticalAlignment();
		Assert.assertEquals("top", align);

		fcell.setVerticalAlignment(null);
		String newAlign = fcell.getVerticalAlignment();
		Assert.assertEquals(null, newAlign);

		fcell.setVerticalAlignment("bottom");
		align = fcell.getVerticalAlignment();
		Assert.assertEquals("bottom", align);
		saveods();
		OdfSpreadsheetDocument ods;
		try {
			ods = OdfSpreadsheetDocument.newSpreadsheetDocument();
			OdfTable tbl = ods.getTableByName("Sheet1");
			OdfTableCell cell = tbl.getCellByPosition(0, 0);
			String verticalAlignment = cell.getVerticalAlignment();
			Assert.assertEquals(null, verticalAlignment);
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
	}

	@Test
	public void testGetSetValueType() {
		int rowindex = 3, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		boolean illegalArgumentFlag = false;
		try {
			fcell.setValueType(null);
		} catch (IllegalArgumentException ie) {
			if ("type shouldn't be null.".equals(ie.getMessage())) {
				illegalArgumentFlag = true;
			}
		}
		Assert.assertTrue(illegalArgumentFlag);

		fcell.setValueType("date");
		String valueType = fcell.getValueType();
		Assert.assertEquals("date", valueType);
		saveods();

		OdfSpreadsheetDocument ods;
		try {
			ods = OdfSpreadsheetDocument.newSpreadsheetDocument();
			OdfTable tbl = ods.getTableByName("Sheet1");
			OdfTableCell cell = tbl.getCellByPosition(0, 0);
			valueType = cell.getValueType();
			Assert.assertEquals(null, valueType);
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
	}

	@Test
	public void testGetSetWrapOption() {
		int rowindex = 5, columnindex = 8;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);

		boolean wrap = fcell.isTextWrapped();
		Assert.assertEquals(true, wrap);

		fcell.setTextWrapped(false);
		wrap = fcell.isTextWrapped();
		Assert.assertEquals(false, wrap);
		saveods();
	}

	@Test
	public void testGetSetTextValue() {
		int rowindex = 5, columnindex = 8;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);

		String text = fcell.getDisplayText();
		Assert.assertEquals("this is a big cell with a big table", text);

		fcell.setDisplayText("changed");
		text = fcell.getDisplayText();
		Assert.assertEquals("changed", text);
		// reproduce bug 150.
		fcell.setDisplayText(null);
		text = fcell.getDisplayText();
		Assert.assertEquals("", text);
		fcell.setDisplayText(null, "automatic7777");
		text = fcell.getDisplayText();
		Assert.assertEquals("", text);
		saveods();

		OdfTable table1 = odtdoc.getTableByName("Table1");
		OdfTableCell fcell2 = table1.getCellByPosition(0, 1);
		text = fcell2.getDisplayText();
		Assert.assertEquals("Aabbccddee", text);
	}

	@Test
	public void testSetGetFormat() {
		int rowindex = 3, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);

		fcell.setFormatString("#0.0");
		String displayvalue = fcell.getDisplayText();
		Assert.assertEquals("300"
				+ (new DecimalFormatSymbols()).getDecimalSeparator() + "0",
				displayvalue);
		String format = fcell.getFormatString();
		Assert.assertEquals("#0.0", format);

		OdfTableCell dcell = table.getCellByPosition(3, 2);
		format = dcell.getFormatString();
		Assert.assertEquals("MMM d, yy", format);

		dcell.setFormatString("yyyy-MM-dd");
		displayvalue = dcell.getDisplayText();
		Assert.assertEquals("2008-12-23", displayvalue);

		OdfTableCell pcell = table.getCellByPosition("B2");
		format = pcell.getFormatString();
		Assert.assertEquals("#0%", format);

		pcell.setFormatString("#0.00%");
		displayvalue = pcell.getDisplayText();
		Assert.assertEquals("200"
				+ (new DecimalFormatSymbols()).getDecimalSeparator() + "00%",
				displayvalue);
		try {
			OdfTableRow tablerow = table.getRowByIndex(6);
			OdfTableCell cell = tablerow.getCellByIndex(3);
			Calendar currenttime = Calendar.getInstance();
			cell.setDateValue(currenttime);
			cell.setFormatString("yyyy-MM-dd");
			tablerow = table.getRowByIndex(7);
			cell = tablerow.getCellByIndex(3);
			cell.setTimeValue(currenttime);
			cell.setFormatString("HH:mm:ss");
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
		saveods();

		//test value type adapt function.
		OdfSpreadsheetDocument ods;
		try {
			ods = OdfSpreadsheetDocument.newSpreadsheetDocument();
			OdfTable tbl = ods.getTableByName("Sheet1");
			OdfTableCell cell;
			for (int i = 1; i <= 10; i++) {
				cell = tbl.getCellByPosition("A" + i);
				cell.setDoubleValue(new Double(i));
			}
			cell = tbl.getCellByPosition("A11");
			cell.setFormula("=sum(A1:A10)");
			//contains '#' should be adapted as float.
			cell.setFormatString("#,###");
			Assert.assertEquals("float", cell.getValueType());
			cell = tbl.getCellByPosition("A12");
			cell.setFormula("=sum(A1:A10)");
			//contains '0' should be adapted as float.
			cell.setFormatString("0.00");
			Assert.assertEquals("float", cell.getValueType());
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
		try {
			ods = OdfSpreadsheetDocument.newSpreadsheetDocument();
			OdfTable tbl = ods.getTableByName("Sheet1");
			OdfTableCell cell;
			for (int i = 1; i <= 10; i++) {
				cell = tbl.getCellByPosition("A" + i);
				cell.setPercentageValue(0.1);
			}
			cell = tbl.getCellByPosition("A11");
			cell.setFormula("=sum(A1:A10)");
			//contains '%'should be adapted as percentage.
			cell.setFormatString("###.0%");
			Assert.assertEquals("percentage", cell.getValueType());
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
		try {
			ods = OdfSpreadsheetDocument.newSpreadsheetDocument();
			OdfTable tbl = ods.getTableByName("Sheet1");
			OdfTableCell cell;
			for (int i = 1; i <= 10; i++) {
				cell = tbl.getCellByPosition("A" + i);
				cell.setDateValue(Calendar.getInstance());
				cell.setFormatString("yyyy.MM.dd");
			}
			cell = tbl.getCellByPosition("A11");
			cell.setFormula("=max(A1:A10)");
			//contains 'y' 'M' 'd' should be adapted as date.
			cell.setFormatString("yyyy.MM.dd");
			Assert.assertEquals("date", cell.getValueType());
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
		try {
			ods = OdfSpreadsheetDocument.newSpreadsheetDocument();
			OdfTable tbl = ods.getTableByName("Sheet1");
			OdfTableCell cell;
			for (int i = 1; i <= 10; i++) {
				cell = tbl.getCellByPosition("A" + i);
				cell.setTimeValue(Calendar.getInstance());
				cell.setFormatString("yyyy.MM.dd HH:mm:ss");
			}
			cell = tbl.getCellByPosition("A11");
			cell.setFormula("=max(A1:A10)");
			//contains 'H' 'm' 's' should be adapted as time.
			cell.setFormatString("yyyy.MM.dd HH:mm:ss");
			Assert.assertEquals("time", cell.getValueType());
			cell = tbl.getCellByPosition("A12");
			cell.setFormula("=max(A1:A10)");
			//contains 'H' 'm' 's' should be adapted as time.
			cell.setFormatString("HH:mm:ss");
			Assert.assertEquals("time", cell.getValueType());
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
	}

	private void loadOutputSpreadsheet() {
		try {
			odsdoc = (OdfSpreadsheetDocument) OdfSpreadsheetDocument.loadDocument(ResourceUtilities.getAbsolutePath(SAMPLE_SPREADSHEET
					+ "Output.ods"));
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
		}
	}

	@Test
	public void testGetSetCellBackgroundColor() throws Exception {
		int rowindex = 2, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		fcell.setCellBackgroundColor("#ffffff");
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		// set color as DEFAULT_BACKGROUND_COLOR #FFFFFF
		Assert.assertEquals("#ffffff", fcell.getCellBackgroundColorString());

		Color expectedColor = Color.valueOf("#000000");
		fcell.setCellBackgroundColor(expectedColor);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expectedColor.toString(), fcell.getCellBackgroundColor().toString());

		OdfSpreadsheetDocument ods;
		try {
			ods = OdfSpreadsheetDocument.newSpreadsheetDocument();
			OdfTable tbl = ods.getTableByName("Sheet1");
			OdfTableCell cell = tbl.getCellByPosition(0, 0);
			Color actualBackColor = cell.getCellBackgroundColor();
			Assert.assertEquals("#ffffff", actualBackColor.toString());
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
	}

	@Test
	public void testGetSetColumnSpannedNumber() throws Exception {
		int rowindex = 2, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		fcell.setColumnSpannedNumber(-2);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		// set column spanned number as DEFAULT_COLUMN_SPANNED_NUMBER 1.
		Assert.assertEquals(1, fcell.getColumnSpannedNumber());

		fcell.setColumnSpannedNumber(0);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		// set column spanned number as DEFAULT_COLUMN_SPANNED_NUMBER 1.
		Assert.assertEquals(1, fcell.getColumnSpannedNumber());

		fcell.setColumnSpannedNumber(2);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(2, fcell.getColumnSpannedNumber());
	}

	@Test
	public void testGetSetRowSpannedNumber() throws Exception {
		int rowindex = 2, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		fcell.setRowSpannedNumber(-2);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		// set row spanned number as DEFAULT_ROW_SPANNED_NUMBER 1.
		Assert.assertEquals(1, fcell.getRowSpannedNumber());

		fcell.setRowSpannedNumber(0);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		// set row spanned number as DEFAULT_ROW_SPANNED_NUMBER 1.
		Assert.assertEquals(1, fcell.getRowSpannedNumber());

		fcell.setRowSpannedNumber(2);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(2, fcell.getRowSpannedNumber());

	}

	@Test
	public void testGetSetColumnsRepeatedNumber() throws Exception {
		int rowindex = 3, columnindex = 1;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		fcell.setColumnsRepeatedNumber(-2);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		// set columns repeated number as DEFAULT_COLUMNS_REPEATED_NUMBER 1.
		Assert.assertEquals(1, fcell.getColumnsRepeatedNumber());

		fcell.setColumnsRepeatedNumber(0);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		// set columns repeated number as DEFAULT_COLUMNS_REPEATED_NUMBER 1.
		Assert.assertEquals(1, fcell.getColumnsRepeatedNumber());

		fcell.setColumnsRepeatedNumber(2);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(2, fcell.getColumnsRepeatedNumber());
	}

	@Test
	public void testGetSetDateValue() {
		int rowindex = 7, columnindex = 7;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		boolean illegalArgumentFlag = false;
		try {
			fcell.setDateValue(null);
		} catch (IllegalArgumentException ie) {
			if ("date shouldn't be null.".equals(ie.getMessage())) {
				illegalArgumentFlag = true;
			}
		}
		Assert.assertTrue(illegalArgumentFlag);
		Calendar expectedCalendar = new GregorianCalendar(2010, 1, 30);
		fcell.setDateValue(expectedCalendar);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(0, fcell.getDateValue().compareTo(
				expectedCalendar));
	}

	@Test
	public void testGetSetStringValue() {
		int rowindex = 6, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		fcell.setStringValue(null);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals("", fcell.getStringValue());

		String expectedString = "hello world";
		fcell.setStringValue(expectedString);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expectedString, fcell.getStringValue());
	}

	@Test
	public void testGetSetBooleanValue() {
		int rowindex = 5, columnindex = 5;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		boolean expected = false;
		fcell.setBooleanValue(expected);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertFalse(fcell.getBooleanValue());
	}

	@Test
	public void testGetSetCurrencyValue() {
		int rowindex = 5, columnindex = 5;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		fcell.setValueType("currency");
		fcell.setCurrencyFormat("$", "#,##0.00");
		Double actualValue = fcell.getCurrencyValue();
		Assert.assertNull(actualValue);

		double expected = 100.00;
		boolean illegalArgumentFlag = false;
		try {
			fcell.setCurrencyValue(expected, null);
		} catch (IllegalArgumentException ie) {
			if ("currency shouldn't be null.".equals(ie.getMessage())) {
				illegalArgumentFlag = true;
			}
		}
		Assert.assertTrue(illegalArgumentFlag);

		fcell.setCurrencyValue(expected, "USD");
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expected, fcell.getCurrencyValue());
	}

	@Test
	public void testGetSetCurrencyDesc() {
		int rowindex = 1, columnindex = 2;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		boolean illegalArgumentFlag = false;
		try {
			fcell.setCurrencyCode(null);
		} catch (IllegalArgumentException ie) {
			if ("Currency code of cell should not be null.".equals(ie.getMessage())) {
				illegalArgumentFlag = true;
			}
		}
		Assert.assertTrue(illegalArgumentFlag);

		fcell = table.getCellByPosition(columnindex, rowindex);
		String expected = "USD";
		fcell.setCurrencyCode(expected);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expected, fcell.getCurrencyCode());
	}

	@Test
	public void testGetSetPercentageValue() {
		int rowindex = 5, columnindex = 5;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		double expected = 56.98;
		fcell.setPercentageValue(expected);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expected, fcell.getPercentageValue());
	}

	@Test
	public void testGetSetTimeValue() {
		int rowindex = 0, columnindex = 4;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		boolean illegalArgumentFlag = false;
		try {
			fcell.setTimeValue(null);
		} catch (IllegalArgumentException ie) {
			if ("time shouldn't be null.".equals(ie.getMessage())) {
				illegalArgumentFlag = true;
			}
		}
		Assert.assertTrue(illegalArgumentFlag);
		Calendar expected = Calendar.getInstance();
		fcell.setTimeValue(expected);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);

		SimpleDateFormat simpleFormat = new SimpleDateFormat(
				"'PT'HH'H'mm'M'ss'S'");
		String expectedString = simpleFormat.format(expected.getTime());
		String targetString = simpleFormat.format(fcell.getTimeValue().getTime());
		Assert.assertEquals(expectedString, targetString);
	}

	@Test
	public void testGetSetFormula() {
		int rowindex = 1, columnindex = 10;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		boolean illegalArgumentFlag = false;
		try {
			fcell.setFormula(null);
		} catch (IllegalArgumentException ie) {
			if ("formula shouldn't be null.".equals(ie.getMessage())) {
				illegalArgumentFlag = true;
			}
		}
		Assert.assertTrue(illegalArgumentFlag);

		String expected = "of:=[.I2]*4";
		fcell.setFormula(expected);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expected, fcell.getFormula());
	}

	/**
	 * This test case is used to check whether the new created cell uses correct
	 * style settings.</br>
	 * ODFDOM allows users to set if cell styles are inherited or not whenever a
	 * new cell is added to the table. The default setting is using inheritance.
	 * In this condition, the style of new column is same with the
	 * last column before the inserted position, while the style of new row
	 * is same with the last row before the inserted position.<br/>
	 * This feature setting will influence <code>appendRow()</code>,
	 * <code>appendColumn()</code>, <code>appendRows()</code>,
	 * <code>appendColumns()</code>, <code>insertRowsBefore()</code> and
	 * <code>insertColumnsBefore()</code>. In default setting condition, the
	 * style name of new created cells after these methods called should be
	 * "ce1" which is inherited from preceding cell. <br/>
	 * But after setting cell style inheritance false, these new created cells'
	 * style name should be "Default", which is not inherited from preceding
	 * one.<br/>
	 * For <code>getCellByPosition()</code>,
	 * <code>getCellRangeByPosition()</code>, <code>getCellRangeByName()</code>,
	 * <code>getRowByIndex()</code> and <code>getColumnByIndex()</code>, if need
	 * automatically expand cells, it will return empty cell(s) without any
	 * style settings. Inheritance setting have no effect on them, so for cells
	 * which created after these methods are called, should have "Default" style
	 * name.
	 */
	@Test
	public void testGetStyleName() {
		try {
			odsstyle = (OdfSpreadsheetDocument) OdfSpreadsheetDocument.loadDocument(ResourceUtilities.getAbsolutePath(SAMPLE_STYLE_SPREADSHEET
					+ ".ods"));
			int rowindex = 1, columnindex = 0;
			OdfTable table = odsstyle.getTableByName("Sheet1");
			OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
			String expected = "ce1";
			Assert.assertEquals(expected, fcell.getStyleName());
			// the default setting is inherited, so for new row, 
			// the cell style name should be "ce1".
			//test appendColumn
			table.appendColumn();
			int columnCount = table.getColumnCount();
			fcell = table.getCellByPosition(columnCount - 1, rowindex);
			Assert.assertEquals(expected, fcell.getStyleName());
			//test appendRow
			table.appendRow();
			int rowCount = table.getRowCount();
			fcell = table.getCellByPosition(columnindex, rowCount - 1);
			Assert.assertEquals(expected, fcell.getStyleName());
			//test insertRowsBefore
			table.insertRowsBefore(rowindex + 1, 1);
			fcell = table.getCellByPosition(columnindex, rowindex + 1);
			Assert.assertEquals(expected, fcell.getStyleName());
			//test insertColumnsBefore
			table.insertColumnsBefore(columnindex + 1, 1);
			fcell = table.getCellByPosition(columnindex + 1, rowindex);
			Assert.assertEquals(expected, fcell.getStyleName());
			//test appendColumns
			table.appendColumns(2);
			columnCount = table.getColumnCount();
			fcell = table.getCellByPosition(columnCount - 1, rowindex);
			Assert.assertEquals(expected, fcell.getStyleName());
			//test appendRows
			table.appendRows(2);
			rowCount = table.getRowCount();
			fcell = table.getCellByPosition(columnindex, rowCount - 1);
			Assert.assertEquals(expected, fcell.getStyleName());
			// for getCellByPosition the return cell style should be "Default".
			fcell = table.getCellByPosition(table.getColumnCount() + 1, table.getRowCount() + 1);
			Assert.assertEquals("Default", fcell.getStyleName());
			odsstyle.close();

			// change setting is not inherited, so for new row, 
			// the cell style name should be "Default".
			odsstyle = (OdfSpreadsheetDocument) OdfSpreadsheetDocument.loadDocument(ResourceUtilities.getAbsolutePath(SAMPLE_STYLE_SPREADSHEET
					+ ".ods"));
			rowindex = 1;
			columnindex = 0;
			table = odsstyle.getTableByName("Sheet1");
			table.setCellStyleInheritance(false);
			expected = "Default";
			table.appendColumn();
			columnCount = table.getColumnCount();
			fcell = table.getCellByPosition(columnCount - 1, rowindex);
			Assert.assertEquals(expected, fcell.getStyleName());
			//test appendRow
			table.appendRow();
			rowCount = table.getRowCount();
			fcell = table.getCellByPosition(columnindex, rowCount - 1);
			Assert.assertEquals(expected, fcell.getStyleName());
			//test insertRowsBefore
			table.insertRowsBefore(rowindex + 1, 1);
			fcell = table.getCellByPosition(columnindex, rowindex + 1);
			Assert.assertEquals(expected, fcell.getStyleName());
			//test insertColumnsBefore
			table.insertColumnsBefore(columnindex + 1, 1);
			fcell = table.getCellByPosition(columnindex + 1, rowindex);
			//Assert.assertEquals(expected, fcell.getStyleName());
			//test appendColumns
			table.appendColumns(2);
			columnCount = table.getColumnCount();
			fcell = table.getCellByPosition(columnCount - 1, rowindex);
			Assert.assertEquals(expected, fcell.getStyleName());
			//test appendRows
			table.appendRows(2);
			rowCount = table.getRowCount();
			fcell = table.getCellByPosition(columnindex, rowCount - 1);
			Assert.assertEquals(expected, fcell.getStyleName());
			// for getCellByPosition the return cell style should be "Default".
			fcell = table.getCellByPosition(table.getColumnCount(), table.getRowCount());
			Assert.assertEquals("Default", fcell.getStyleName());
			odsstyle.close();
		} catch (Exception e) {
			Assert.fail(e.toString());
		}
	}

	@Test
	public void testGetTableColumn() {
		int rowindex = 2, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);

		Assert.assertNotNull(fcell.getTableColumn());
		Assert.assertEquals(columnindex, fcell.getTableColumn().getColumnIndex());
	}

	@Test
	public void testGetTableRow() {
		int rowindex = 2, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);

		Assert.assertNotNull(fcell.getTableRow());
		Assert.assertEquals(rowindex, fcell.getTableRow().getRowIndex());
	}

	@Test
	public void testRemoveContent() {
		int rowindex = 5, columnindex = 8;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);

		Assert.assertTrue(fcell.mCellElement.getChildNodes().getLength() > 0);
		fcell.removeContent();
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(0, fcell.mCellElement.getChildNodes().getLength());
	}

	@Test
	public void testRemoveTextContent() throws Exception {
		int rowindex = 5, columnindex = 8;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);

		// how to test?
		fcell.removeContent();
		Assert.assertEquals(0, fcell.mCellElement.getChildNodes().getLength());

		fcell.setDisplayText("hello");
		DrawFrameElement drawEle = new DrawFrameElement(odsdoc.getContentDom());
		DrawImageElement imageEle = drawEle.newDrawImageElement();
		fcell.mCellElement.appendChild(drawEle);

		Assert.assertEquals(2, fcell.mCellElement.getChildNodes().getLength());

		fcell.removeTextContent();
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(1, fcell.mCellElement.getChildNodes().getLength());
	}

	@Test
	public void testGetSetDisplayText() {
		int rowindex = 5, columnindex = 5;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		String expected = "display text";

		// Assert.assertEquals(expected, fcell.getDisplayText());

		fcell.setDisplayText(expected);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expected, fcell.getDisplayText());
	}

	@Test
	public void testGetSetFormatString() {
		int rowindex = 3, columnindex = 0;
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell fcell = table.getCellByPosition(columnindex, rowindex);
		boolean illegalArgumentFlag = false;
		try {
			fcell.setFormatString(null);
		} catch (IllegalArgumentException ie) {
			if ("formatStr shouldn't be null.".equals(ie.getMessage())) {
				illegalArgumentFlag = true;
			}
		}
		Assert.assertTrue(illegalArgumentFlag);
		// float format string
		String expected = "#0.0";
		fcell.setFormatString(expected);
		// date format string
		// String expected="MMM d, yy";
		// String expected="yyyy-MM-dd";

		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expected, fcell.getFormatString());
	}

	@Test
	public void testGetCurrencySymbol() {
		OdfTable table = odsdoc.getTableByName("Sheet1");
		OdfTableCell cell1 = table.getCellByPosition("C2");
		Assert.assertEquals("$", cell1.getCurrencySymbol());
		OdfTableCell cell2 = table.getCellByPosition("C3");
		Assert.assertEquals("CNY", cell2.getCurrencySymbol());
	}

	@Test
	public void testGetSetCurrencyFormat() {
		OdfTable table = odsdoc.getTableByName("Sheet1");
		String[] formats = {"$#,##0.00", "#,##0.00 CNY", "$#,##0.0"};

		OdfTableCell cell = table.getCellByPosition("J1");
		boolean illegalArgumentFlag = false;
		try {
			cell.setCurrencyFormat(null, formats[0]);
		} catch (IllegalArgumentException ie) {
			if ("currencySymbol shouldn't be null.".equals(ie.getMessage())) {
				illegalArgumentFlag = true;
			}
		}
		Assert.assertTrue(illegalArgumentFlag);

		try {
			cell.setCurrencyFormat("$", null);
		} catch (IllegalArgumentException ie) {
			if ("format shouldn't be null.".equals(ie.getMessage())) {
				illegalArgumentFlag = true;
			}
		}
		Assert.assertTrue(illegalArgumentFlag);

		cell.setCurrencyValue(32.12, "USD");
		cell.setCurrencyFormat("$", formats[0]);

		cell = table.getCellByPosition("J2");
		cell.setCurrencyValue(new Double(32), "CNY");
		cell.setCurrencyFormat("CNY", formats[1]);

		cell = table.getCellByPosition("J3");
		cell.setCurrencyValue(-32.12, "USD");
		cell.setCurrencyFormat("$", formats[2]);

		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		for (int i = 1; i <= 3; i++) {
			OdfTableCell newcell = table.getCellByPosition("J" + i);
			Assert.assertEquals(formats[i - 1], newcell.getFormatString());
		}
	}

	@Test
	public void testSetDefaultCellStyle() {
		OdfSpreadsheetDocument outputDocument;
		OdfContentDom contentDom; // the document object model for content.xml
		OdfStylesDom stylesDom; // the document object model for styles.xml
		// the office:automatic-styles element in content.xml
		OdfOfficeAutomaticStyles contentAutoStyles;
		// the office:styles element in styles.xml
		OdfOfficeStyles stylesOfficeStyles;
		OdfStyle style;
		String noaaDateStyleName;
		String noaaTempStyleName;

		try {
			outputDocument = OdfSpreadsheetDocument.newSpreadsheetDocument();
			contentDom = outputDocument.getContentDom();
			contentAutoStyles = contentDom.getOrCreateAutomaticStyles();

			OdfNumberDateStyle dateStyle = new OdfNumberDateStyle(contentDom,
					"yyyy-MM-dd", "numberDateStyle", null);
			OdfNumberStyle numberStyle = new OdfNumberStyle(contentDom,
					"#0.00", "numberTemperatureStyle");

			contentAutoStyles.appendChild(dateStyle);
			contentAutoStyles.appendChild(numberStyle);

			style = contentAutoStyles.newStyle(OdfStyleFamily.TableCell);
			noaaDateStyleName = style.getStyleNameAttribute();
			style.setStyleDataStyleNameAttribute("numberDateStyle");

			// and for time cells
			style = contentAutoStyles.newStyle(OdfStyleFamily.TableCell);
			noaaTempStyleName = style.getStyleNameAttribute();
			style.setStyleDataStyleNameAttribute("numberTemperatureStyle");
			style.setProperty(StyleParagraphPropertiesElement.TextAlign, "end");

			OdfTable table = OdfTable.newTable(outputDocument);
			List<OdfTableColumn> columns = table.insertColumnsBefore(0, 3);
			OdfTableColumn column = columns.get(0);
			column.setDefaultCellStyle(
					contentAutoStyles.getStyle(noaaDateStyleName,
					OdfStyleFamily.TableCell));
			OdfTableCell aCell = column.getCellByIndex(0);
			aCell.setValueType("date");
			String format = aCell.getFormatString();
			Assert.assertEquals("yyyy-MM-dd", format);

			List<OdfTableRow> rows = table.insertRowsBefore(0, 1);
			OdfTableRow row = rows.get(0);
			row.setDefaultCellStyle(contentAutoStyles.getStyle(noaaTempStyleName,
					OdfStyleFamily.TableCell));
			OdfTableCell bCell = row.getCellByIndex(0);
			bCell.setValueType("float");
			String bformat = bCell.getFormatString();
			Assert.assertEquals("#0.00", bformat);
			Assert.assertEquals("end", bCell.getHorizontalAlignment());
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail();
		}
	}
        
        @Test
        public void testGetFromEmptyDateValue() throws Exception {
            OdfSpreadsheetDocument doc = OdfSpreadsheetDocument.newSpreadsheetDocument();
            OdfTable table = OdfTable.newTable(doc);
            OdfTableCell dateCell = table.appendRow().getCellByIndex(0);
            dateCell.setValueType(OfficeValueTypeAttribute.Value.DATE.toString());
            Assert.assertNull(dateCell.getDateValue());
        }
        
        @Test
        public void testGetFromEmptyTimeValue() throws Exception {
            OdfSpreadsheetDocument doc = OdfSpreadsheetDocument.newSpreadsheetDocument();
            OdfTable table = OdfTable.newTable(doc);
            OdfTableCell timeCell = table.appendRow().getCellByIndex(0);
            timeCell.setValueType(OfficeValueTypeAttribute.Value.TIME.toString());
            Assert.assertNull(timeCell.getTimeValue());
        }
        
}