simple/src/test/java/org/odftoolkit/simple/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.simple.table;

import java.awt.GraphicsEnvironment;
import java.text.DecimalFormatSymbols;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
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.dom.OdfContentDom;
import org.odftoolkit.odfdom.dom.attribute.office.OfficeValueTypeAttribute;
import org.odftoolkit.odfdom.dom.element.draw.DrawFrameElement;
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.style.OdfStyle;
import org.odftoolkit.odfdom.type.Color;
import org.odftoolkit.simple.SpreadsheetDocument;
import org.odftoolkit.simple.TextDocument;
import org.odftoolkit.simple.style.Border;
import org.odftoolkit.simple.style.Font;
import org.odftoolkit.simple.style.StyleTypeDefinitions;
import org.odftoolkit.simple.style.StyleTypeDefinitions.CellBordersType;
import org.odftoolkit.simple.style.StyleTypeDefinitions.FontStyle;
import org.odftoolkit.simple.style.StyleTypeDefinitions.HorizontalAlignmentType;
import org.odftoolkit.simple.style.StyleTypeDefinitions.TextLinePosition;
import org.odftoolkit.simple.style.StyleTypeDefinitions.VerticalAlignmentType;
import org.odftoolkit.simple.text.Paragraph;
import org.odftoolkit.simple.text.list.BulletDecorator;
import org.odftoolkit.simple.text.list.ImageDecorator;
import org.odftoolkit.simple.text.list.ListContainer;
import org.odftoolkit.simple.text.list.ListDecorator;
import org.odftoolkit.simple.text.list.NumberDecorator;
import org.odftoolkit.simple.utils.ResourceUtilities;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.odftoolkit.simple.Document;
import org.odftoolkit.simple.style.StyleTypeDefinitions.SupportedLinearMeasure;


public class TableCellTest {

	private static final Logger LOGGER =  Logger.getLogger(TableCellTest.class.getName());
	final static String SAMPLE_SPREADSHEET = "TestSpreadsheetTable";
	final static String SAMPLE_STYLE_SPREADSHEET = "TestSpreadsheetStyleTable";
	final static String SAMPLE_TEXT = "TestTextTable";
	static final String filename = "testGetCellAt.ods";
	
	SpreadsheetDocument odsdoc, odsstyle;
	TextDocument odtdoc;
	Table odsTable, odtTable;

	@Before
	public void setUp() {
		try {
			odsdoc = (SpreadsheetDocument) SpreadsheetDocument.loadDocument(ResourceUtilities
					.getTestResourceAsStream(SAMPLE_SPREADSHEET + ".ods"));
			odtdoc = (TextDocument) TextDocument.loadDocument(ResourceUtilities.getTestResourceAsStream(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);
		}
	}

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

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

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

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

		HorizontalAlignmentType align = fcell.getHorizontalAlignmentType();
		Assert.assertEquals(HorizontalAlignmentType.CENTER, align);

		fcell.setHorizontalAlignment(HorizontalAlignmentType.DEFAULT);
		HorizontalAlignmentType newAlign = fcell.getHorizontalAlignmentType();
		Assert.assertEquals(HorizontalAlignmentType.DEFAULT, newAlign);

		fcell.setHorizontalAlignment(HorizontalAlignmentType.LEFT);
		align = fcell.getHorizontalAlignmentType();
		Assert.assertEquals(HorizontalAlignmentType.LEFT, align);

		// "left" and "right" should be mapped as "start" and "end".
		fcell.setHorizontalAlignment(HorizontalAlignmentType.LEFT);
		// get string
		String aligns = fcell.getHorizontalAlignment();
		Assert.assertEquals("start", aligns);
		// get type
		align = fcell.getHorizontalAlignmentType();
		Assert.assertEquals(HorizontalAlignmentType.LEFT, align);
		fcell.setHorizontalAlignment(HorizontalAlignmentType.RIGHT);
		// get string
		aligns = fcell.getHorizontalAlignment();
		Assert.assertEquals("end", aligns);
		// get type
		align = fcell.getHorizontalAlignmentType();
		Assert.assertEquals(HorizontalAlignmentType.RIGHT, align);
		saveods();

		SpreadsheetDocument ods;
		try {
			ods = SpreadsheetDocument.newSpreadsheetDocument();
			Table tbl = ods.getTableByName("Sheet1");
			Cell cell = tbl.getCellByPosition(0, 0);
			HorizontalAlignmentType horizonAlignment = cell.getHorizontalAlignmentType();
			Assert.assertEquals(HorizontalAlignmentType.DEFAULT, horizonAlignment);

			table = ods.getTableByName("Sheet2");
			if (table != null) {
				table.remove();
			}
			table = Table.newTable(ods);
			table.setTableName("Sheet2");
			cell = table.getCellByPosition(1, 1);
			cell.setHorizontalAlignment(HorizontalAlignmentType.CENTER);
			horizonAlignment = cell.getHorizontalAlignmentType();
			Assert.assertEquals(HorizontalAlignmentType.CENTER, 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell fcell = table.getCellByPosition(columnindex, rowindex);

		StyleTypeDefinitions.VerticalAlignmentType align = fcell.getVerticalAlignmentType();
		Assert.assertEquals(StyleTypeDefinitions.VerticalAlignmentType.TOP, align);

		fcell.setVerticalAlignment(VerticalAlignmentType.DEFAULT);
		StyleTypeDefinitions.VerticalAlignmentType newAlign = fcell.getVerticalAlignmentType();
		Assert.assertEquals(StyleTypeDefinitions.VerticalAlignmentType.DEFAULT, newAlign);

		fcell.setVerticalAlignment(StyleTypeDefinitions.VerticalAlignmentType.BOTTOM);
		align = fcell.getVerticalAlignmentType();
		Assert.assertEquals(StyleTypeDefinitions.VerticalAlignmentType.BOTTOM, align);
		saveods();
		SpreadsheetDocument ods;
		try {
			ods = SpreadsheetDocument.newSpreadsheetDocument();
			Table tbl = ods.getTableByName("Sheet1");
			Cell cell = tbl.getCellByPosition(0, 0);
			StyleTypeDefinitions.VerticalAlignmentType verticalAlignment = cell.getVerticalAlignmentType();
			Assert.assertEquals(StyleTypeDefinitions.VerticalAlignmentType.DEFAULT, verticalAlignment);

			table = ods.getTableByName("Sheet2");
			if (table != null) {
				table.remove();
			}
			table = Table.newTable(ods);
			table.setTableName("Sheet2");
			cell = table.getCellByPosition(1, 1);
			cell.setVerticalAlignment(StyleTypeDefinitions.VerticalAlignmentType.TOP);
			verticalAlignment = cell.getVerticalAlignmentType();
			Assert.assertEquals(StyleTypeDefinitions.VerticalAlignmentType.TOP, 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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();

		SpreadsheetDocument ods;
		try {
			ods = SpreadsheetDocument.newSpreadsheetDocument();
			Table tbl = ods.getTableByName("Sheet1");
			Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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();

		Table table1 = odtdoc.getTableByName("Table1");
		Cell fcell2 = table1.getCellByPosition(0, 1);
		text = fcell2.getDisplayText();
		Assert.assertEquals("Aa\nbb\ncc\ndd\nee", text);
	}

	@Test
	public void testSetGetFormat() {
		int rowindex = 3, columnindex = 0;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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);

		Cell 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);

		Cell 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 {
			Row tablerow = table.getRowByIndex(6);
			Cell 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.
		SpreadsheetDocument ods;
		try {
			ods = SpreadsheetDocument.newSpreadsheetDocument();
			Table tbl = ods.getTableByName("Sheet1");
			Cell 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 = SpreadsheetDocument.newSpreadsheetDocument();
			Table tbl = ods.getTableByName("Sheet1");
			Cell 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 = SpreadsheetDocument.newSpreadsheetDocument();
			Table tbl = ods.getTableByName("Sheet1");
			Cell 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 = SpreadsheetDocument.newSpreadsheetDocument();
			Table tbl = ods.getTableByName("Sheet1");
			Cell 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 = (SpreadsheetDocument) SpreadsheetDocument.loadDocument(ResourceUtilities
					.getTestResourceAsStream(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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell fcell = table.getCellByPosition(columnindex, rowindex);
		fcell.setCellBackgroundColor(new Color("#ffffff"));
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		// set color as DEFAULT_BACKGROUND_COLOR #FFFFFF
		Assert.assertEquals("#ffffff", fcell.getCellBackgroundColor().toString());

		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());

		SpreadsheetDocument ods;
		try {
			ods = SpreadsheetDocument.newSpreadsheetDocument();
			Table tbl = ods.getTableByName("Sheet1");
			Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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 testDateTimeValue() {
		int rowindex = 7, columnindex = 7;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell fcell = table.getCellByPosition(columnindex, rowindex);
		try {
			fcell.setDateTimeValue(null);
			Assert.fail("Null date time value should throw IllegalArgumentException");
		} catch (IllegalArgumentException ie) {
			Assert.assertEquals("date shouldn't be null.", ie.getMessage());
		}

		Calendar expectedCalendar = new GregorianCalendar(2010, 1, 30, 23, 12, 56);
		fcell.setDateTimeValue(expectedCalendar);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expectedCalendar, fcell.getDateTimeValue());
	}
	

        @Test
        public void testGetFromEmptyDateValue() throws Exception {
            Table table = odsdoc.getTableByName("Sheet1");
            Cell cell = table.appendRow().getCellByIndex(1);
            cell.setValueType(OfficeValueTypeAttribute.Value.DATE.toString());
            Assert.assertNull(cell.getDateValue());
        }
        
        @Test
        public void testGetFromEmptyTimeValue() throws Exception {
            Table table = odsdoc.getTableByName("Sheet1");
            Cell cell = table.appendRow().getCellByIndex(1);
            cell.setValueType(OfficeValueTypeAttribute.Value.TIME.toString());
            Assert.assertNull(cell.getTimeValue());
        }

	@Test
	public void testGetSetStringValue() {
		int rowindex = 6, columnindex = 0;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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> SIMPLE 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 = (SpreadsheetDocument) SpreadsheetDocument.loadDocument(ResourceUtilities
					.getTestResourceAsStream(SAMPLE_STYLE_SPREADSHEET + ".ods"));
			int rowindex = 1, columnindex = 0;
			Table table = odsstyle.getTableByName("Sheet1");
			Cell 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 = (SpreadsheetDocument) SpreadsheetDocument.loadDocument(ResourceUtilities
					.getTestResourceAsStream(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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell fcell = table.getCellByPosition(columnindex, rowindex);

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

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

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

	@Test
	public void testRemoveContent() {
		int rowindex = 5, columnindex = 8;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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());
		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;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell fcell = table.getCellByPosition(columnindex, rowindex);
		String expected = "display text";
		fcell.setDisplayText(expected);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expected, fcell.getDisplayText());
		// if the value type is "float", cell value need to be updated, too.
		String cellAddress = "A4";
		fcell = table.getCellByPosition(cellAddress);
		expected = "400.0";
		int expectedValue = 400;
		fcell.setDisplayText(expected);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(cellAddress);
		Assert.assertEquals(expected, fcell.getDisplayText());
		Assert.assertEquals(expectedValue, fcell.getDoubleValue().intValue());
	}

	@Test
	public void testGetSetFormatString() {
		int rowindex = 3, columnindex = 0;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell 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() {
		Table table = odsdoc.getTableByName("Sheet1");
		Cell cell1 = table.getCellByPosition("C2");
		Assert.assertEquals("$", cell1.getCurrencySymbol());
		Cell cell2 = table.getCellByPosition("C3");
		Assert.assertEquals("CNY", cell2.getCurrencySymbol());
	}

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

		Cell 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++) {
			Cell newcell = table.getCellByPosition("J" + i);
			Assert.assertEquals(formats[i - 1], newcell.getFormatString());
		}
	}

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

		try {
			outputDocument = SpreadsheetDocument.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");

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

			List<Row> rows = table.insertRowsBefore(0, 1);
			Row row = rows.get(0);
			row.setDefaultCellStyle(contentAutoStyles.getStyle(noaaTempStyleName, OdfStyleFamily.TableCell));
			Cell 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 testSetGetFont() {
		try {
			SpreadsheetDocument document;
			document = SpreadsheetDocument.newSpreadsheetDocument();
			Table table1 = document.getTableByName("Sheet1");
			Cell cell1 = table1.getCellByPosition("A1");
			cell1.setStringValue("abcdefg");
			Font font1 = new Font("Arial", StyleTypeDefinitions.FontStyle.ITALIC, 12, Color.BLACK,
					StyleTypeDefinitions.TextLinePosition.THROUGH);
			cell1.setFont(font1);
			Font font11 = cell1.getFont();
			System.out.println(font11);
			if (!font11.equals(font1))
				Assert.fail();

			Cell cell2 = table1.getCellByPosition("A2");
			cell2.setStringValue("redstring");
			Font font2 = new Font("Arial", StyleTypeDefinitions.FontStyle.ITALIC, 12, Color.RED,
					StyleTypeDefinitions.TextLinePosition.UNDER);
			cell2.setFont(font2);
			Font font22 = cell2.getFont();
			System.out.println(font22);
			if (!font22.equals(font2))
				Assert.fail();
			document.save(ResourceUtilities.newTestOutputFile("TestSetGetFont.ods"));
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail();
		}
	}

	@Test
	public void testSetGetBorder() {
		try {
			SpreadsheetDocument document;
			document = SpreadsheetDocument.newSpreadsheetDocument();
			Table table1 = document.getTableByName("Sheet1");
			Cell cell1 = table1.getCellByPosition("A1");
			cell1.setStringValue("four border");
			Border border = new Border(Color.RED, 1, StyleTypeDefinitions.SupportedLinearMeasure.PT);
			cell1.setBorders(CellBordersType.ALL_FOUR, border);
			Border bottomBorder = cell1.getStyleHandler().getBorder(CellBordersType.BOTTOM);
			Assert.assertEquals(border, bottomBorder);

			Cell cell2 = table1.getCellByPosition("C2");
			cell2.setStringValue("top bottom");
			Border border2 = new Border(Color.BLUE, 5, 1, 2, StyleTypeDefinitions.SupportedLinearMeasure.PT);
			cell2.setBorders(CellBordersType.TOP_BOTTOM, border2);
			Border bottomBorder2 = cell2.getStyleHandler().getBorder(CellBordersType.BOTTOM);
			Assert.assertEquals(border2, bottomBorder2);
			Border bottomBorder22 = cell2.getStyleHandler().getBorder(CellBordersType.LEFT);
			Assert.assertEquals(Border.NONE, bottomBorder22);
			document.save(ResourceUtilities.newTestOutputFile("TestSetGetBorder.ods"));
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, e.getMessage(), e);
			Assert.fail();
		}
	}

	@Test
	public void testAddRemoveIterateList() {
		try {
			TextDocument doc = TextDocument.newTextDocument();
			Table table = Table.newTable(doc, 3, 3);
			ListDecorator bulletDecorator = new BulletDecorator(doc);
			ListDecorator numberDecorator = new NumberDecorator(doc);
			ListDecorator imageDecorator = new ImageDecorator(doc, ResourceUtilities.getURI("image_list_item.png"));
			String[] numberItemContents = { "number item 1", "number item 2", "number item 3" };

			// add list.
			ListContainer container = table.getCellByPosition(0, 0);
			org.odftoolkit.simple.text.list.List bulletList = container.addList(bulletDecorator);
			bulletList.addItems(numberItemContents);
			container = table.getCellByPosition(1, 1);
			org.odftoolkit.simple.text.list.List numberList = container.addList(numberDecorator);
			numberList.addItems(numberItemContents);
			container = table.getCellByPosition(2, 2);
			org.odftoolkit.simple.text.list.List imageList = container.addList(imageDecorator);
			imageList.addItems(numberItemContents);
			// iterate list
			Assert.assertTrue(container.getListIterator().hasNext());
			// remove list
			container.clearList();
			Assert.assertFalse(container.getListIterator().hasNext());
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, null, e);
			Assert.fail(e.getMessage());
		}
	}

	/**
	 * This test case check whether getNoteText and setNoteText work as
	 * expected. When test getNoteText, the cell note may be null, a text
	 * paragraph with style or a text list. getNoteText extracts the text
	 * content from them and return it as string. When test setNoteText, a text
	 * paragraph will be created to show text content. text list and style is
	 * not supported.
	 */
	@Test
	public void testGetSetNoteText() throws Exception {
		int rowindex = 10, columnindex = 12;
		Table table = odsdoc.getTableByName("Sheet1");
		Cell fcell = table.getCellByPosition(columnindex, rowindex);
		String note = fcell.getNoteText();
		Assert.assertNull(note);
		// cell A16 contains a text paragraph without style.
		fcell = table.getCellByPosition("A16");
		Assert.assertEquals("note1 ", fcell.getNoteText());
		// cell A16 contains a text paragraph with style.
		fcell = table.getCellByPosition("A17");
		Assert.assertEquals("note2 ", fcell.getNoteText());
		// cell A16 contains a text list with style. all of the list items
		// should be extracted.
		fcell = table.getCellByPosition("A18");
		Assert.assertEquals("note1note2note3", fcell.getNoteText());
		String expectedNote = "note test";
		fcell = table.getCellByPosition(columnindex, rowindex);
		fcell.setNoteText(expectedNote);
		saveods();
		// reload
		loadOutputSpreadsheet();
		table = odsdoc.getTableByName("Sheet1");
		fcell = table.getCellByPosition(columnindex, rowindex);
		Assert.assertEquals(expectedNote, fcell.getNoteText());
	}

	@Test
	public void testGetSetImage() {
		try {
			TextDocument doc = TextDocument.newTextDocument();
			Table table = Table.newTable(doc, 2, 2);
			table.setTableName("ImageTable");
			Cell cell = table.getCellByPosition(0, 0);
			cell.setImage(ResourceUtilities.getURI("image_list_item.png"));
			doc.save(ResourceUtilities.newTestOutputFile("ImageCellTable.odt"));

			// load the document again.
			doc = TextDocument.loadDocument(ResourceUtilities.getTestResourceAsStream("ImageCellTable.odt"));
			table = doc.getTableByName("ImageTable");
			cell = table.getCellByPosition(0, 0);
			// image height = 34 pixels.
			Assert.assertEquals(34, cell.getBufferedImage().getHeight(null));
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, null, e);
			Assert.fail(e.getMessage());
		}
	}

	@Test
	public void testCellParagraph() {
		String[] plainText = { "nospace", "one space", "two  spaces", "three   spaces", "   three leading spaces",
				"three trailing spaces   ", "one\ttab", "two\t\ttabs", "\tleading tab", "trailing tab\t",
				"mixed   \t   spaces and tabs", "line" + System.getProperty("line.separator") + "break" };

		String[][] elementResult = { { "nospace" }, { "one space" }, { "two ", "*s1", "spaces" },
				{ "three ", "*s2", "spaces" }, { " ", "*s2", "three leading spaces" },
				{ "three trailing spaces ", "*s2" }, { "one", "*t", "tab" }, { "two", "*t", "*t", "tabs" },
				{ "*t", "leading tab" }, { "trailing tab", "*t" },
				{ "mixed ", "*s2", "*t", " ", "*s2", "spaces and tabs" }, { "line", "*n", "break" } };
		try {
			// test append paragraph
			TextDocument doc = TextDocument.newTextDocument();
			Table table = Table.newTable(doc, 2, 2);
			Cell cell = table.getCellByPosition(0, 0);
			for (int i = 0; i < plainText.length; i++) {
				Paragraph para = cell.addParagraph(plainText[i]);
				compareResults(para.getOdfElement(), plainText[i], elementResult[i]);
			}

			// test set paragraph content
			cell = table.getCellByPosition(0, 1);
			for (int i = 0; i < plainText.length; i++) {
				Paragraph para = cell.addParagraph(plainText[i]);
				compareResults(para.getOdfElement(), plainText[i], elementResult[i]);
				String content = para.getTextContent();
				Assert.assertEquals(plainText[i], content);
			}

			// test remove paragraph content
			cell = table.getCellByPosition(1, 0);
			for (int i = 0; i < plainText.length; i++) {
				Paragraph para = cell.addParagraph(plainText[i]);
				String content = para.getTextContent();
				Assert.assertEquals(plainText[i], content);
				para.removeTextContent();
				content = para.getTextContent();
				Assert.assertEquals("", content);
			}

			// test get paragraph by index
			cell = table.getCellByPosition(1, 1);
			Paragraph paragraph1 = cell.addParagraph("paragraph1");
			Paragraph paragraphE = cell.addParagraph(null);
			Paragraph paragraph2 = cell.addParagraph("p2");

			Paragraph t1 = cell.getParagraphByIndex(0, false);
			Assert.assertEquals(t1, paragraph1);
			t1 = cell.getParagraphByIndex(2, false);
			Assert.assertEquals(t1, paragraph2);
			t1 = cell.getParagraphByIndex(1, true);
			Assert.assertEquals(t1, paragraph2);
			t1 = cell.getParagraphByReverseIndex(0, false);
			Assert.assertEquals(t1, paragraph2);
			t1 = cell.getParagraphByReverseIndex(2, false);
			Assert.assertEquals(t1, paragraph1);
			t1 = cell.getParagraphByReverseIndex(1, true);
			Assert.assertEquals(t1, paragraph1);
			doc.save(ResourceUtilities.newTestOutputFile("testCellParagraph.odt"));
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, null, e);
			Assert.fail(e.getMessage());
		}
	}

	@Test
	public void testCellSizeOptimal() {
		Font font1Base = new Font("Arial", FontStyle.REGULAR, 6, Color.GREEN, TextLinePosition.REGULAR);
		Font font2Base = new Font("Times New Roman", FontStyle.REGULAR, 13, Color.RED, TextLinePosition.REGULAR);
		Font font3Base = new Font("SimSun", FontStyle.REGULAR, 17, Color.BLUE, TextLinePosition.REGULAR);
		try {
			final SpreadsheetDocument doc = SpreadsheetDocument.newSpreadsheetDocument();
			final Table table = doc.addTable();
			final String contentStr = "This is a long text content.";
			
			// basically the default font is of Arial type, so assume the default family
			final String nfFamilyName = "Arial";
			
			if (isFontAvailable(nfFamilyName)) {
				checkCellWidth(table.getCellByPosition(0, 0), null, 44.0977, contentStr);
			}
			if (isFontAvailable(font1Base.getFamilyName())) {
				checkCellWidth(table.getCellByPosition(1, 1), font1Base, 24.3424, contentStr);
			}
			if (isFontAvailable(font2Base.getFamilyName())) {
				checkCellWidth(table.getCellByPosition(2, 2), font2Base, 49.7414, contentStr);
			}
			if (isFontAvailable(font3Base.getFamilyName())) {
				checkCellWidth(table.getCellByPosition(3, 3), font3Base, 88.899, contentStr);
			}
			doc.save(ResourceUtilities.newTestOutputFile("testCellSizeOptimal.ods"));
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, null, e);
			Assert.fail(e.getMessage());
		}
	}

	private boolean isFontAvailable(String name) {
		java.awt.Font[] fonts = GraphicsEnvironment.getLocalGraphicsEnvironment().getAllFonts();
		for (java.awt.Font font : fonts) {
			if (font.getName().equalsIgnoreCase(name)) {
				return true;
			}
		}
		return false;
	}

	private void checkCellWidth(Cell cell, Font font, double expectedOptimalWidth, String contentStr) {
		try {
			if (font != null) {
				cell.setFont(font);
			}
			cell.getTableColumn().setUseOptimalWidth(true);
			cell.setStringValue(contentStr);
			Assert.assertEquals(expectedOptimalWidth, cell.getTableColumn().getWidth());
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, null, e);
			Assert.fail(e.getMessage());
		}
	}

	@Test
	public void testSetValidityListAndInputHelpMessage() {
		try {
			int rowindex = 2, columnindex = 2;
			Table table = odsdoc.getTableByName("Sheet2");
			Cell fcell = table.getCellByPosition(columnindex, rowindex);
			List<String> values = new ArrayList<String>(Arrays.asList("Mon", "Tue", "Wed", "Thu", "Fri"));
			fcell.setValidityList(values);
			fcell.setInputHelpMessage("Tip", "This cell only allows working days.");
			fcell.setStringValue("Tue");
			Assert.assertNotNull(fcell.getOdfElement().getTableContentValidationNameAttribute());
			saveods();
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, null, e);
			Assert.fail(e.getMessage());
		}
	}

	@Test
	public void testSetValidityListWithReuseContentValidationEl() {
		try {
      SpreadsheetDocument ods = SpreadsheetDocument.newSpreadsheetDocument();
      Table table = ods.appendSheet("New sheet");

      List<String> values = Arrays.asList("Mon", "Tue", "Wed", "Thu", "Fri");
      for (int rowIndex = 0; rowIndex < 3; rowIndex++) {
        Cell fcell = table.getCellByPosition(0, rowIndex);
        fcell.setValidityList(values);
  			Assert.assertNotNull(fcell.getOdfElement().getTableContentValidationNameAttribute());
      }
		} catch (Exception e) {
			Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, null, e);
			Assert.fail(e.getMessage());
		}
	}

  @Test
  public void testSetValidityListAndInputErrorMessage() {
    try {
      int rowindex = 3, columnindex = 2;
      Table table = odsdoc.getTableByName("Sheet2");
      Cell fcell = table.getCellByPosition(columnindex, rowindex);
      List<String> values = new ArrayList<String>(Arrays.asList("Mon", "Tue", "Wed", "Thu", "Fri"));
      fcell.setValidityList(values);
      fcell.setInputErrorMessage("Error message", "This cell only allows working days.", null);
      fcell.setStringValue("Tue");
      Assert.assertNotNull(fcell.getOdfElement().getTableContentValidationNameAttribute());
      saveods();
    } catch (Exception e) {
      Logger.getLogger(TableCellTest.class.getName()).log(Level.SEVERE, null, e);
      Assert.fail(e.getMessage());
    }
  }

	private void compareResults(Element element, String input, String[] output) {
		int i;
		int nSpaces;
		int nSpacesInAttribute;
		Node node = element.getFirstChild();
		for (i = 0; i < output.length; i++) {
			if (output[i].startsWith("*")) {
				Assert.assertEquals(Node.ELEMENT_NODE, node.getNodeType());
				if (output[i].equals("*t")) {
					Assert.assertEquals("tab", node.getLocalName());
				} else if (output[i].equals("*n")) {
					Assert.assertEquals("line-break", node.getLocalName());
				} else {
					nSpaces = Integer.parseInt(output[i].substring(2));
					Assert.assertEquals(node.getLocalName(), "s");
					nSpacesInAttribute = Integer.parseInt(((Element) node).getAttribute("text:c"));
					Assert.assertEquals(nSpaces, nSpacesInAttribute);
				}
			} else {
				Assert.assertEquals(Node.TEXT_NODE, node.getNodeType());
				Assert.assertEquals(output[i], node.getTextContent());
			}
			node = node.getNextSibling();
		}
		Assert.assertEquals(node, null);
	}
	
	
	@Test
	public void testSetHorizontalAlignment() {
		try {
			Table table = odsdoc.getTableByName("Sheet1");
			Cell cell1 = table.getCellByPosition("C2");
			Assert.assertEquals("$", cell1.getCurrencySymbol());
			String right = cell1.getHorizontalAlignment();
			Assert.assertEquals(null, right);
			/**
			 *The parameter can be "center", "end", "justify", "left", "right", or 
			 * "start". Actually, "left" will be interpreted as "start", while "right" 
             * will be interpreted as "end". If argument is null, the explicit 
             * horizontal alignment setting is removed. 
			 */
			cell1.setHorizontalAlignment("left");
			
			//validate
			String left = cell1.getHorizontalAlignment();
			Assert.assertEquals("start", left);
			
			//save
			//odsdoc.save(ResourceUtilities.newTestOutputFile("testTableCell.ods"));

		} catch (Exception e) {
			LOGGER.log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
		
	}
	
	
	@Test
	public void testGetCountry() {
		try {
			Border borderbase = new Border(Color.LIME, 3.0701, 0.0208, 0.0346, SupportedLinearMeasure.CM);
			borderbase.setLineStyle(StyleTypeDefinitions.LineType.DOUBLE);
			SpreadsheetDocument doc = SpreadsheetDocument.newSpreadsheetDocument();
			Table table = doc.getTableByName("Sheet1");
			
			Cell cell = table.getCellByPosition(2, 2);

			CellStyleHandler cellHandler = cell.getStyleHandler();
			cellHandler.setCountry("English", Document.ScriptType.CJK);
			//validate
			String country = cellHandler.getCountry(Document.ScriptType.CJK);
			Assert.assertEquals("English", country);
			
			//save
			doc.save(ResourceUtilities.newTestOutputFile("testSupportedLinearMeasure.ods"));
		} catch (Exception e) {
			LOGGER.log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
		
	}
	
	
	@Test
	public void testGetLanguage() {
		try {
			Border borderbase = new Border(Color.LIME, 3.0701, 0.0208, 0.0346, SupportedLinearMeasure.CM);
			borderbase.setLineStyle(StyleTypeDefinitions.LineType.DOUBLE);
			SpreadsheetDocument doc = SpreadsheetDocument.newSpreadsheetDocument();
			Table table = doc.getTableByName("Sheet1");
			
			Cell cell = table.getCellByPosition(2, 2);

			CellStyleHandler cellHandler = cell.getStyleHandler();
			cellHandler.setLanguage("English", Document.ScriptType.WESTERN);
			//validate
			String language = cellHandler.getLanguage(Document.ScriptType.WESTERN);
			System.out.println(language);
			Assert.assertEquals("English", language);
			
			//save
			doc.save(ResourceUtilities.newTestOutputFile("testSupportedLinearMeasure.ods"));
		} catch (Exception e) {
			LOGGER.log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
		
	}
	
	
	@Test
	public void testSetColor() {
		try {
			Border borderbase = new Border(Color.LIME, 3.0701, 0.0208, 0.0346, SupportedLinearMeasure.CM);
			borderbase.setLineStyle(StyleTypeDefinitions.LineType.DOUBLE);
			SpreadsheetDocument doc = SpreadsheetDocument.newSpreadsheetDocument();
			Table table = doc.getTableByName("Sheet1");
			
			Cell cell = table.getCellByPosition(2, 2);

			CellStyleHandler cellHandler = cell.getStyleHandler();
			cellHandler.setBackgroundColor(Color.RED);
			//validate
			Color red = cellHandler.getBackgroundColor();
			Assert.assertEquals(Color.RED.toString(), red.toString());
			
			//save
			doc.save(ResourceUtilities.newTestOutputFile("testSupportedLinearMeasure.ods"));
		} catch (Exception e) {
			LOGGER.log(Level.SEVERE, e.getMessage(), e);
			Assert.fail(e.getMessage());
		}
		
	}
	
	
	@Test
	public void testSetBorder() {
		Border borderbase = new Border(new Color("#00ccff"), 0.0701, 0.0008, 0.0346, SupportedLinearMeasure.IN);
		try {
			SpreadsheetDocument doc = SpreadsheetDocument.loadDocument(ResourceUtilities
					.getTestResourceAsStream(filename));
			Table table = doc.getTableByName("A");
			
			borderbase.setLinearMeasure(StyleTypeDefinitions.SupportedLinearMeasure.CM);
			
			Cell cell = table.getCellByPosition("A14");
			cell.setBorders(CellBordersType.LEFT, borderbase);
			

			CellStyleHandler cellHandler = cell.getStyleHandler();
			Border border = cellHandler.getBorder(CellBordersType.LEFT);
			Assert.assertEquals(borderbase, border);
			//save
			//doc.save(ResourceUtilities.newTestOutputFile("testSetWidth.ods"));

		} catch (Exception e) {
			LOGGER.log(Level.SEVERE, e.getMessage(), e);
			Assert.fail();
		}
		
	}
	
	@Test
	public void testIsTextWrapped() {
		Border borderbase = new Border(new Color("#00ccff"), 0.0701, 0.0008, 0.0346, SupportedLinearMeasure.CM);
		try {
			SpreadsheetDocument doc = SpreadsheetDocument.loadDocument(ResourceUtilities
					.getTestResourceAsStream(filename));
			Table table = doc.getTableByName("A");
			Cell cell = table.getCellByPosition("A14");
			cell.setStringValue("testIsTextWrapped.");
			cell.setBorders(CellBordersType.ALL_FOUR, borderbase);

			CellStyleHandler cellHandler = cell.getStyleHandler();
			cellHandler.setTextWrapped(true);
			Assert.assertTrue(cellHandler.isTextWrapped());
			
			//save
			//doc.save(ResourceUtilities.newTestOutputFile("testSetWidth.ods"));
		} catch (Exception e) {
			LOGGER.log(Level.SEVERE, e.getMessage(), e);
			Assert.fail();
		}
		
	}
}