org.jopendocument.dom.spreadsheet.SheetTest Maven / Gradle / Ivy
/*
* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
*
* Copyright 2008 jOpenDocument, by ILM Informatique. All rights reserved.
*
* The contents of this file are subject to the terms of the GNU
* General Public License Version 3 only ("GPL").
* You may not use this file except in compliance with the License.
* You can obtain a copy of the License at http://www.gnu.org/licenses/gpl-3.0.html
* See the License for the specific language governing permissions and limitations under the License.
*
* When distributing the software, include this License Header Notice in each file.
*
*/
package org.jopendocument.dom.spreadsheet;
import static java.util.Arrays.asList;
import org.jopendocument.dom.ODDocument;
import org.jopendocument.dom.ODPackage;
import org.jopendocument.dom.ODPackage.RootElement;
import org.jopendocument.dom.OOXML;
import org.jopendocument.dom.Style;
import org.jopendocument.dom.XMLFormatVersion;
import org.jopendocument.dom.spreadsheet.SheetTableModel.MutableTableModel;
import org.jopendocument.dom.style.SideStyleProperties.Side;
import org.jopendocument.dom.text.Paragraph;
import org.jopendocument.dom.text.ParagraphStyle;
import org.jopendocument.dom.text.TextDocument;
import org.jopendocument.util.Tuple2;
import org.jopendocument.util.SimpleXMLPath;
import org.jopendocument.util.Validator;
import java.awt.Color;
import java.awt.Point;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.Map;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import junit.framework.TestCase;
import org.jdom.Attribute;
import org.jdom.Element;
public class SheetTest extends TestCase {
static final DefaultTableModel tm;
static {
tm = new DefaultTableModel();
tm.addColumn("col1");
tm.addColumn("col2");
tm.addColumn("col3");
// trailing 0 to test precision (i.e. not equals to -5.32)
tm.addRow(new Object[] { "un1", new BigDecimal("-5.320"), new Integer(123) });
}
private Sheet sheet;
private Sheet realSheet;
protected void setUp() throws Exception {
this.sheet = SpreadSheet.createEmpty(tm, XMLFormatVersion.getOOo()).getSheet(0);
this.realSheet = new ODPackage(this.getClass().getResourceAsStream("test.ods")).getSpreadSheet().getSheet(0);
}
protected void tearDown() throws Exception {
this.sheet = null;
this.realSheet = null;
}
public void testCreate() throws IOException {
final DefaultTableModel empty = new DefaultTableModel();
for (final OOXML vers : OOXML.values()) {
if (vers.canValidate()) {
final SpreadSheet emptySpread = SpreadSheet.createEmpty(empty, vers.getFormatVersion());
final Validator validator = emptySpread.getPackage().getContent().getValidator();
assertValid(emptySpread);
assertValid(validator);
assertEquals(0, validator.validateCompletely().size());
// Should fail since we removed the body
emptySpread.getSheet(0).getElement().removeContent();
assertValid(emptySpread.getPackage(), false);
assertValid(validator, false);
assertTrue(validator.validateCompletely().size() > 0);
}
}
// save
final TableModel model = new DefaultTableModel(new Object[][] { { "Data", "DataCol2" } }, new Object[] { "Col1", "Col2" });
final SpreadSheet created = SpreadSheet.createEmpty(model);
assertValid(created);
final File tempFile = File.createTempFile("testCreate", "." + created.getPackage().getContentType().getExtension());
final File saved = created.saveAs(tempFile);
saved.deleteOnExit();
// assert that saveAs() didn't create a new file (otherwise the first one won't get deleted)
if (!tempFile.equals(saved)) {
tempFile.delete();
fail("Wrong extension : " + tempFile);
}
// then read
final Sheet sheetRead = SpreadSheet.createFromFile(saved).getSheet(0);
// and check that the data is correct
assertEquals(2, sheetRead.getRowCount());
assertEquals("Col1", sheetRead.getValueAt("A1"));
// data w/o column names
final SheetTableModel tmRead = sheetRead.getTableModel(0, 1);
assertEquals(2, tmRead.getColumnCount());
assertEquals(1, tmRead.getRowCount());
assertEquals("DataCol2", tmRead.getValueAt(0, 1));
final ODPackage realPkg = this.realSheet.getSpreadSheet().getPackage();
try {
realPkg.rmFile(RootElement.CONTENT.getZipEntry());
fail("Cannot rm a file used by the ODDocument");
} catch (Exception e) {
// ok
}
// settings on the other hand is fine
realPkg.putFile(RootElement.SETTINGS.getZipEntry(), RootElement.SETTINGS.createDocument(realPkg.getFormatVersion()));
// OK since no ODDocument exists
new ODPackage(realPkg).rmFile(RootElement.CONTENT.getZipEntry());
}
public void testName() throws Exception {
final String n = "users";
final SpreadSheet realSpread = this.realSheet.getSpreadSheet();
assertEquals(n, realSpread.getSheet(n).getName());
// the sheet can be accessed with a changed name
final String newName = "newName";
assertFalse(newName.equals(this.realSheet.getName()));
this.realSheet.setName(newName);
assertTrue(newName.equals(this.realSheet.getName()));
assertSame(this.realSheet, realSpread.getSheet(newName));
// test add
final int startSheetCount = 4;
assertEquals(startSheetCount, realSpread.getSheetCount());
final Sheet sheet0 = realSpread.getSheet(0);
final Sheet sheet1 = realSpread.getSheet(1);
// add at the end since we used to have a bug that added table:table at the very end of
// body, e.g. after table:named-expressions.
final Sheet addSheet = realSpread.addSheet(2, "newSheet");
assertEquals(startSheetCount + 1, realSpread.getSheetCount());
assertSame(addSheet, realSpread.getSheet("newSheet"));
assertSame(sheet0, realSpread.getSheet(0));
assertSame(sheet1, realSpread.getSheet(1));
assertSame(addSheet, realSpread.getSheet(2));
// assert that the new sheet is functional
addSheet.ensureRowCount(8);
// (was throwing a NPE in Table.updateWidth())
addSheet.ensureColumnCount(10);
for (int i = 0; i < 10; i++) {
for (int j = 0; j < 8; j++) {
addSheet.setValueAt(i + "," + j, i, j);
}
}
assertValid(this.realSheet.getSpreadSheet());
// test move
for (int i = 0; i < 3; i++) {
addSheet.move(i);
// test index
assertSame(addSheet, realSpread.getSheet(i));
}
addSheet.move(0);
assertEquals(startSheetCount + 1, realSpread.getSheetCount());
assertSame(addSheet, realSpread.getSheet("newSheet"));
assertSame(addSheet, realSpread.getSheet(0));
assertSame(sheet0, realSpread.getSheet(1));
assertSame(sheet1, realSpread.getSheet(2));
assertValid(this.realSheet.getSpreadSheet());
// test copy
final Sheet copiedSheet = addSheet.copy(2, "copiedSheet");
assertEquals(startSheetCount + 2, realSpread.getSheetCount());
assertSame(copiedSheet, realSpread.getSheet("copiedSheet"));
assertSame(addSheet, realSpread.getSheet(0));
assertSame(sheet0, realSpread.getSheet(1));
assertSame(copiedSheet, realSpread.getSheet(2));
assertSame(sheet1, realSpread.getSheet(3));
assertValid(this.realSheet.getSpreadSheet());
// test rm
final int count = realSpread.getSheetCount();
assertSame(sheet0, this.realSheet);
this.realSheet.detach();
assertNull(realSpread.getSheet(this.realSheet.getName()));
assertEquals(count - 1, realSpread.getSheetCount());
assertSame(addSheet, realSpread.getSheet(0));
assertSame(copiedSheet, realSpread.getSheet(1));
assertSame(sheet1, realSpread.getSheet(2));
assertValid(this.realSheet.getSpreadSheet());
// test quoting
{
final String simple = "aposAndTab";
assertTrue(simple.equals(SpreadSheet.formatSheetName(simple)));
final String needQuote = "apos ' & tab \t";
assertFalse(needQuote.equals(SpreadSheet.formatSheetName(needQuote)));
assertEquals("'apos '' & tab \t'", SpreadSheet.formatSheetName(needQuote));
assertEquals(needQuote, SpreadSheet.parseSheetName(SpreadSheet.formatSheetName(needQuote)));
try {
SpreadSheet.parseSheetName("'foo");
fail("should have failed since quote is not closed");
} catch (IllegalArgumentException e) {
// ok
}
}
}
/*
* Test method for 'org.jopendocument.dom.spreadsheet.Sheet.setValueAt(Object, int, int)'
*/
public void testSetValueAt() throws Exception {
assertEquals("col1", this.sheet.getValueAt(0, 0));
this.sheet.setValueAt("test", 0, 0);
assertEquals("test", this.sheet.getValueAt(0, 0));
this.sheet.setValueAt(60001399.1523, 0, 0);
assertEquals(new BigDecimal("60001399.1523"), this.sheet.getValueAt(0, 0));
final Calendar now = Calendar.getInstance();
this.sheet.setValueAt(now.getTime(), 0, 0);
final Object valueAt = this.sheet.getValueAt(0, 0);
// OpenDocument can encode time up to the ms
assertEquals(now.getTime(), valueAt);
assertValid(this.sheet.getSpreadSheet());
try {
this.realSheet.getCellAt("C3");
fail("should have failed since C3 is covered");
} catch (IllegalArgumentException e) {
// ok
}
this.realSheet.getCellAt("B3").unmerge();
// should now succeed
this.realSheet.getCellAt("C3").setValue(new Date());
// preserve style
{
// test with odt (it has always worked in ods since text:p cannot have style, it is on
// the cell)
final ODPackage doc = new ODPackage(this.getClass().getResourceAsStream("../test.odt"));
final Table t = new Table(doc.getTextDocument(), doc.getContent().getDescendantByName("table:table", "JODTestTable"));
final MutableCell cell = t.getCellAt(2, 1);
assertEquals("end", getFirstPStyle(cell).getAlignment());
cell.setValue("somethingElse");
assertEquals("end", getFirstPStyle(cell).getAlignment());
}
// test white space encoding and decoding
{
final String spacesAndTab = "spaces and tab\t.";
this.sheet.setValueAt(spacesAndTab, 0, 0);
assertEquals(spacesAndTab, this.sheet.getValueAt(0, 0));
}
assertValid(this.realSheet.getSpreadSheet());
}
private final ParagraphStyle getFirstPStyle(final Cell cell) {
final Paragraph p = new Paragraph(cell.getElement().getChild("p", cell.getNS().getTEXT()));
p.setDocument(cell.getODDocument());
return p.getStyle();
}
/*
* Test method for 'org.jopendocument.dom.spreadsheet.Sheet.getValueAt(int, int)'
*/
public void testGetValueAt() {
assertEquals(new BigDecimal(123), this.sheet.getValueAt(2, 1));
// testGetValueAtString()
assertEquals(new BigDecimal(123), this.sheet.getValueAt("C2"));
final Calendar c = Calendar.getInstance();
c.clear();
c.set(2008, 11, 25);
final Cell dateCell = this.realSheet.getImmutableCellAt("F3");
assertEquals(c.getTime(), dateCell.getValue());
assertEquals("25 déc. 2008", dateCell.getTextValue());
final Cell textCell = this.realSheet.getImmutableCellAt("B3");
assertEquals("Gestion\tdes droits\nnouvelle arborescence ", textCell.getValue());
assertEquals("Gestion\tdes droits\nnouvelle arborescence ", textCell.getTextValue(true));
// test std mode
//
//
// Paragraph
//
// |
textCell.getElement().removeContent();
final Element p = new Element("p", textCell.getElement().getNamespace("text"));
p.addContent("\n\t\tGestion \t des droits");
textCell.getElement().addContent("\n\t").addContent(p).addContent("\n");
assertEquals("Gestion des droits", textCell.getTextValue(false));
assertSame(this.realSheet.getCellAt("F3"), this.realSheet.getSpreadSheet().getCellAt(this.realSheet.getName() + ".$F$3"));
{
final SheetTableModel tm = this.realSheet.getSpreadSheet().getSheet(1).getTableModel(1, 1);
// = NA()
assertNotNull(tm.getImmutableCellAt(0, 0).getError());
// = "#N" & "/D"
// we can't be right
// assertNull(tm.getImmutableCellAt(0, 1).getError());
// =A0
assertNotNull(tm.getImmutableCellAt(1, 0).getError());
// #NOM ? : right since there's no formula
assertNull(tm.getImmutableCellAt(1, 1).getError());
// =3/0
assertNotNull(tm.getImmutableCellAt(2, 0).getError());
// empty cell
assertTrue(tm.getImmutableCellAt(2, 1).isEmpty());
assertNull(tm.getImmutableCellAt(2, 1).getError());
}
}
/*
* Test method for 'org.jopendocument.dom.spreadsheet.Sheet.getRowCount()'
*/
public void testGetCount() {
// col labels + 1 row
assertEquals(2, this.sheet.getRowCount());
assertEquals(3, this.sheet.getColumnCount());
}
/*
* Test method for 'org.jopendocument.dom.spreadsheet.Sheet.ensureColumnCount(int)'
*/
public void testEnsureColumnCount() {
final int before = this.sheet.getColumnCount();
final int newSize = before - 2;
this.sheet.ensureColumnCount(newSize);
assertEquals(before, this.sheet.getColumnCount());
}
public void testSetColumnCount() throws Exception {
// test in-memory
final int newSize = this.sheet.getColumnCount() + 5;
try {
this.sheet.setValueAt("test", newSize - 1, 0);
fail("should have thrown IndexOutOfBoundsException");
} catch (IndexOutOfBoundsException exn) {
// normal
}
this.sheet.setColumnCount(newSize);
this.sheet.setValueAt("test", newSize - 1, 0);
// test with real file
final int origCount = this.realSheet.getColumnCount();
try {
this.realSheet.setValueAt("over", origCount + 10 - 1, 1);
fail("should throw exn since we try to write past the limit");
} catch (RuntimeException e) {
// ok
}
this.realSheet.setColumnCount(origCount + 10);
this.realSheet.setValueAt("over", origCount + 10 - 1, 1);
final ByteArrayOutputStream out = new ByteArrayOutputStream(1024 * 15);
this.realSheet.getSpreadSheet().getPackage().save(out);
final SpreadSheet reRead = new ODPackage(new ByteArrayInputStream(out.toByteArray())).getSpreadSheet();
assertEquals("over", reRead.getSheet(0).getValueAt(origCount + 10 - 1, 1));
assertValid(this.realSheet.getSpreadSheet());
}
public void testRemoveCol() throws Exception {
this.realSheet.setColumnCount(9, -1, true);
assertEquals(9, this.realSheet.getColumnCount());
assertFalse(this.realSheet.getImmutableCellAt("C3").isValid());
this.realSheet.removeColumn(1, true);
assertEquals(8, this.realSheet.getColumnCount());
// B3 was covering C3, but since the B column is gone C3 is incovered
assertTrue(this.realSheet.getImmutableCellAt("C3").isValid());
final ODPackage doc = new ODPackage(this.getClass().getResourceAsStream("../test.odt"));
final Table t = new Table(doc.getODDocument(), doc.getContent().getDescendantByName("table:table", "JODTestTable"));
final float colW = t.getColumn(1).getWidth();
float tableW = t.getWidth();
t.removeColumn(1, false);
// normal float error
assertTrue(Math.abs(tableW - colW - t.getWidth()) <= Math.ulp(t.getWidth()));
tableW = t.getWidth();
t.removeColumn(1, true);
assertEquals(tableW, t.getWidth());
}
public void testSetRowCount() throws IOException {
this.realSheet.setRowCount(3);
// java model OK
assertEquals(3, this.realSheet.getRowCount());
final SpreadSheet reParsed = new ODPackage(this.realSheet.getSpreadSheet().getPackage()).getSpreadSheet();
// XML model OK
assertEquals(3, reParsed.getSheet(0).getRowCount());
assertValid(this.realSheet.getSpreadSheet());
}
/*
* Test method for 'org.jopendocument.dom.spreadsheet.Sheet.getTableModel(int, int)'
*/
public void testGetTableModel() {
SheetTableModel t = this.sheet.getTableModel(1, 0);
assertEquals(tm.getColumnCount() - 1, t.getColumnCount());
// +1: col labels
assertEquals(tm.getRowCount() + 1, t.getRowCount());
assertEquals(new BigDecimal("-5.320"), t.getValueAt(1, 0));
// empty hence no style
assertNull(t.getImmutableCellAt(0, 0).getStyle());
try {
t.getValueAt(1, t.getColumnCount());
fail("should have thrown exn");
} catch (IndexOutOfBoundsException e) {
// ok
}
}
/*
* Test method for 'org.jopendocument.dom.spreadsheet.Sheet.getMutableTableModel(int, int)'
*/
public void testGetMutableTableModel() {
this.sheet.getMutableTableModel(1, 0).setValueAt("test", 0, 1);
assertEquals("test", this.sheet.getCellAt("C1").getValue());
final MutableTableModel tm = this.realSheet.getSpreadSheet().getTableModel("rights");
assertEquals("K1", tm.getValueAt(13, 6));
assertEquals(Color.CYAN, tm.getCellAt(0, 0).getStyle().getBackgroundColor());
assertEquals("#ffcc99", tm.getCellAt(1, 2).getStyle().getTableCellProperties().getRawBackgroundColor());
try {
tm.setValueAt("foo", 0, tm.getColumnCount());
fail("should have thrown exn");
} catch (IndexOutOfBoundsException e) {
// ok
}
}
public void testMerge() throws IOException {
{
final Table t = this.realSheet.getSpreadSheet().getSheet(2);
assertNull(t.getCoverOrigin(0, 0));
final Point orig = new Point(1, 11);
for (int x = 0; x <= 5; x++) {
for (int y = 10; y <= 15; y++) {
if (x < 1 || x > 4 || y < 11 || y > 14)
assertNull(t.getCoverOrigin(x, y));
else
assertEquals(orig, t.getCoverOrigin(x, y));
}
}
// test bounds
assertEquals(new Point(0, 16), t.getCoverOrigin(1, 16));
}
final MutableCell b3 = this.realSheet.getCellAt("B3");
assertEquals(2, b3.getColumnsSpanned());
assertEquals(1, b3.getRowsSpanned());
b3.unmerge();
assertEquals(1, b3.getColumnsSpanned());
assertEquals(1, b3.getRowsSpanned());
assertFalse(this.realSheet.getImmutableCellAt("C3").isCovered());
assertFalse(this.realSheet.getImmutableCellAt("B4").isCovered());
assertNull(this.realSheet.getCoverOrigin(b3.getX(), b3.getY()));
b3.merge(3, 2);
assertEquals(3, b3.getColumnsSpanned());
assertEquals(2, b3.getRowsSpanned());
assertFalse(b3.isCovered());
assertTrue(this.realSheet.getImmutableCellAt("C3").isCovered());
assertTrue(this.realSheet.getImmutableCellAt("B4").isCovered());
// we cannot have overlapping merge since we cannot get to a covered cell
try {
this.realSheet.getCellAt("C3");
fail("should have thrown an exn");
} catch (Exception e) {
// ok : we can't modify a covered cell
}
assertEquals(b3.getPoint(), this.realSheet.getCoverOrigin(b3.getX(), b3.getY()));
assertEquals(b3.getPoint(), this.realSheet.getCoverOrigin(b3.getX() + 3 - 1, b3.getY() + 2 - 1));
// merge more
b3.merge(3, 3);
assertEquals(3, b3.getColumnsSpanned());
assertEquals(3, b3.getRowsSpanned());
// if we call merge() with less cells it un-merge the rest
b3.merge(1, 3);
assertEquals(1, b3.getColumnsSpanned());
assertEquals(3, b3.getRowsSpanned());
assertFalse(b3.isCovered());
assertFalse(this.realSheet.getImmutableCellAt("C3").isCovered());
assertTrue(this.realSheet.getImmutableCellAt("B4").isCovered());
try {
this.realSheet.getCellAt(0, 0).merge(2, 4);
fail("Allowed overlapping merge");
} catch (Exception e) {
// ok
}
// the document was unchanged by the failed attempt
assertFalse(this.realSheet.getImmutableCellAt(0, 0).coversOtherCells());
assertFalse(this.realSheet.getImmutableCellAt(0, 1).isCovered());
assertValid(this.realSheet.getSpreadSheet());
}
public void testResolve() {
testResolve(new Point(0, 22), "A23");
testResolve(new Point(26, 33), "AA34");
testResolve(new Point(106, 11), "DC12");
testResolve(new Point(25, 37), "Z38");
// to verify just input 0 into A1 and drag to the right
// you can also use COLONNE(ABC1) - 1
testResolve(new Point(701, 0), "ZZ1");
testResolve(new Point(702, 0), "AAA1");
testResolve(new Point(730, 0), "ABC1");
assertNull(Sheet.resolve("A23A"));
assertNull(Sheet.resolve("test"));
assertNull(Sheet.resolve("23"));
assertEquals(77, Sheet.toInt("BZ"));
final Range range = Range.parse("Sheet1.A23:.AA34");
assertFalse(range.spanSheets());
assertEquals("Sheet1", range.getStartSheet());
assertEquals("Sheet1", range.getEndSheet());
assertEquals(Sheet.resolve("A23"), range.getStartPoint());
assertEquals(Sheet.resolve("AA34"), range.getEndPoint());
assertEquals(range, Range.parse(range.toString()));
}
private void testResolve(Point p, String addr) {
assertEquals(p, Sheet.resolve(addr));
assertEquals(addr, Sheet.getAddress(p));
}
public void testDuplicateRows() throws Exception {
this.realSheet.duplicateRows(8, 3, 2);
assertEquals(this.realSheet.getValueAt("B11"), this.realSheet.getValueAt("B14"));
assertEquals("Comptabilité", this.realSheet.getValueAt("H9"));
// two copies
assertEquals(this.realSheet.getValueAt("H9"), this.realSheet.getValueAt("H12"));
assertEquals(this.realSheet.getValueAt("H9"), this.realSheet.getValueAt("H15"));
assertValid(this.realSheet.getSpreadSheet());
assertEquals(2, this.sheet.getRowCount());
// test cloning of last row
this.sheet.duplicateRows(1, 1, 3);
assertEquals(5, this.sheet.getRowCount());
assertEquals(this.sheet.getValueAt("C2"), this.sheet.getValueAt("C4"));
// test quoting of table name
final Sheet sheet2 = this.realSheet.getSpreadSheet().getSheet("end'Cell & Address");
try {
sheet2.duplicateRows(2, 2, 1, false);
fail("should have failed since a merged cell ends in the middle");
} catch (IllegalArgumentException e) {
// ok
}
try {
sheet2.duplicateRows(10, 2, 1, false);
fail("should have failed since a merged cell starts in the middle");
} catch (IllegalArgumentException e) {
// ok
}
// Nothing to do
testShapeSpan(sheet2, "rect", "A1", "A3");
// Just offset
testShapeSpan(sheet2, "custom-shape", "A4", "B8");
// Offset and copied
testShapeSpan(sheet2, "frame", "E3", "E3");
// use height but isn't copied
testShapeSpan(sheet2, "line", "B1", "B6");
// use height and copied
testShapeSpan(sheet2, "g", "B2", "D5");
assertEquals(3, sheet2.getCellAt("F1").getRowsSpanned());
// repeat the end of a merged cell
sheet2.duplicateRows(1, 2, 2, true);
// Nothing to do
testShapeSpan(sheet2, "rect", "A1", "A3");
// Just offset
testShapeSpan(sheet2, "custom-shape", "A8", "B12");
// Offset and copied
testShapeSpan(sheet2, "frame", "E3", "E3");
testShapeSpan(sheet2, "frame", "E5", "E5");
testShapeSpan(sheet2, "frame", "E7", "E7");
// use height but isn't copied
testShapeSpan(sheet2, "line", "B1", "B5");
// use height and copied
testShapeSpan(sheet2, "g", "B2", "D5");
testShapeSpan(sheet2, "g", "B4", "D7");
testShapeSpan(sheet2, "g", "B6", "D9");
assertEquals(7, sheet2.getCellAt("F1").getRowsSpanned());
// repeat a contained merged cell
assertEquals(4, sheet2.getCellAt("B16").getRowsSpanned());
sheet2.duplicateRows(14, 6, 1, false);
assertEquals(4, sheet2.getCellAt("B16").getRowsSpanned());
assertEquals(4, sheet2.getCellAt("B22").getRowsSpanned());
}
private void testShapeSpan(final Sheet sheet2, final String elemName, final String startCell, final String endCell) {
final Element justOffset = SimpleXMLPath.allElements(elemName, "draw").selectSingleNode(sheet2.getImmutableCellAt(startCell).getElement());
final Tuple2 end = sheet2.getSpreadSheet().resolve(justOffset.getAttributeValue("end-cell-address", sheet2.getTABLE()));
assertSame(sheet2, end.get0());
assertEquals(Table.resolve(endCell), end.get1());
}
public void testXY() throws Exception {
final int y = 35;
{
final MutableCell c = this.realSheet.getCellAt(2, y);
assertEquals("LECTURE SEULE", c.getValue());
assertEquals(2, c.getX());
assertEquals(y, c.getY());
}
// offset by 6
this.realSheet.duplicateRows(8, 3, 2);
{
final MutableCell c = this.realSheet.getCellAt(2, y + 6);
assertEquals("LECTURE SEULE", c.getValue());
assertEquals(2, c.getX());
assertEquals(y + 6, c.getY());
}
}
public void testUsedRange() throws Exception {
final Sheet emptySheet = this.sheet.getSpreadSheet().addSheet("empty");
assertNull(emptySheet.getUsedRange());
// test for content
assertEquals(new Range(this.realSheet.getName(), Table.resolve("B3"), Table.resolve("J38")), this.realSheet.getUsedRange());
// also test for style
assertEquals(new Range(this.realSheet.getName(), Table.resolve("B3"), Table.resolve("Z38")), this.realSheet.getUsedRange(true));
assertEquals(new Range("empty", Table.resolve("A1")), emptySheet.getCurrentRegion("A1"));
// A1 is empty and bounded by empty cells
assertEquals(new Range(this.realSheet.getName(), Table.resolve("A1")), this.realSheet.getCurrentRegion("A1"));
// C is covered
assertEquals(new Range(this.realSheet.getName(), Table.resolve("D3"), Table.resolve("F4")), this.realSheet.getCurrentRegion("D4"));
// test for content
assertEquals(new Range(this.realSheet.getName(), Table.resolve("B9"), Table.resolve("I27")), this.realSheet.getCurrentRegion("C11"));
assertEquals(new Range(this.realSheet.getName(), Table.resolve("E18")), this.realSheet.getCurrentRegion("E18"));
// also test for style
assertEquals(new Range(this.realSheet.getName(), Table.resolve("B9"), Table.resolve("Z32")), this.realSheet.getCurrentRegion("C11", true));
assertEquals(this.realSheet.getCurrentRegion("C11", true), this.realSheet.getCurrentRegion("E18", true));
}
public void testGroups() throws Exception {
final Sheet sheet = this.realSheet.getSpreadSheet().getSheet("Groups");
assertEquals(0, sheet.getColumnGroup().getChildren().size());
final TableGroup rowsGroup = sheet.getRowGroup();
assertNull(rowsGroup.getParent());
assertSame(sheet, rowsGroup.getTable());
assertEquals(sheet.getRowCount(), rowsGroup.getSize());
assertEquals(1, rowsGroup.getChildren().size());
final TableGroup childGroup = rowsGroup.getChildren().get(0);
assertEquals(1, childGroup.getFirst());
assertEquals(13, childGroup.getSize());
// the table has 2 header rows in 2 different groups
assertEquals(2, sheet.getHeaderRowCount());
assertEquals(1, rowsGroup.getHeaderCount());
assertEquals(1, childGroup.getHeaderCount());
assertEquals(2, childGroup.getChildren().size());
final TableGroup group1_1 = childGroup.getChildren().get(0);
assertSame(childGroup, group1_1.getParent());
assertEquals(0, group1_1.getChildren().size());
assertEquals(2, group1_1.getFirst());
assertEquals(2, group1_1.getSize());
assertEquals("A3", sheet.getValueAt(0, group1_1.getFirst()));
final TableGroup group1_2 = childGroup.getChildren().get(1);
assertSame(childGroup, group1_2.getParent());
assertEquals(0, group1_2.getChildren().size());
assertEquals(6, group1_2.getFirst());
assertEquals(4, group1_2.getSize());
assertEquals("group1,2", sheet.getValueAt(0, group1_2.getFirst()));
// groupAt
assertSame(sheet.getRowGroup(), sheet.getRowGroupAt(0));
assertSame(childGroup, sheet.getRowGroupAt(1));
assertSame(group1_1, sheet.getRowGroupAt(2));
assertSame(group1_1, sheet.getRowGroupAt(3));
assertSame(childGroup, sheet.getRowGroupAt(4));
assertSame(childGroup, sheet.getRowGroupAt(5));
assertSame(group1_2, sheet.getRowGroupAt(6));
// table-rows
{
// http://plugtest.opendocsociety.org/doku.php?id=scenarios:20110715:tabletablewrapper
final Sheet plural = new ODPackage(getClass().getResourceAsStream("./rowscolumns-gnumeric.ods")).getSpreadSheet().getSheet(0);
assertEquals(128, plural.getRowCount());
assertEquals(128, plural.getColumnCount());
final Integer[][] data = new Integer[][] { { 1, 2, 3 }, { 10, 20, 30 }, { 15, 5, 30 } };
for (int i = 0; i < 3; i++) {
for (int j = 0; j < 3; j++) {
assertEquals(BigDecimal.valueOf(data[i][j]), plural.getValueAt(i, j));
}
}
}
}
public void testRepeated() throws Exception {
final Row row26 = this.realSheet.getRow(26);
assertEquals(1, row26.getRepeated());
assertEquals(26, row26.getY());
assertEquals(26, row26.getLastY());
final Row row27 = this.realSheet.getRow(27);
assertEquals(5, row27.getRepeated());
assertEquals(27, row27.getY());
assertEquals(31, row27.getLastY());
assertSame(row27, this.realSheet.getRow(29));
assertSame(row27, this.realSheet.getRow(31));
assertNotSame(row27, this.realSheet.getRow(26));
assertNotSame(row27, this.realSheet.getRow(32));
// if we're just reading,
assertEquals("", this.realSheet.getValueAt(0, 27));
// the row is still repeated
assertSame(row27, this.realSheet.getRow(27));
assertEquals(5, row27.getRepeated());
// if we're writing,
this.realSheet.setValueAt("Break repeated", 0, 27);
assertEquals("Break repeated", this.realSheet.getValueAt(0, 27));
assertEquals("", this.realSheet.getValueAt(0, 28));
// the row is no longer repeated
assertNotSame(row27, this.realSheet.getRow(27));
assertEquals(1, this.realSheet.getRow(27).getRepeated());
assertEquals(4, this.realSheet.getRow(28).getRepeated());
}
public void testStyle() throws Exception {
// even though the style-name is null
assertNull(this.realSheet.getImmutableCellAt(2, 10).getStyleAttr());
final String style = this.realSheet.getStyleNameAt(2, 10);
assertNotNull(style);
// the correct style can still be found
assertEquals(Color.CYAN, this.realSheet.getCellAt(2, 10).getStyle().getBackgroundColor());
// also from MutableCell
assertEquals(style, this.realSheet.getCellAt(2, 10).getStyleName());
// conditional style
{
final CellStyle headingStyle = Style.getStyle(this.realSheet.getSpreadSheet().getPackage(), CellStyle.class, "Heading");
final CellStyle heading1Style = Style.getStyle(this.realSheet.getSpreadSheet().getPackage(), CellStyle.class, "Heading1");
// ==5 : Titre ; >=15 Titre1
final MutableCell relationAndMultiple = this.realSheet.getCellAt("J3");
CellStyle elseStyle = relationAndMultiple.getStyle();
assertFalse(elseStyle.equals(headingStyle) || elseStyle.equals(heading1Style));
relationAndMultiple.setValue(5);
// however we ask, we get the same result
assertEquals(relationAndMultiple.getStyle(), this.realSheet.getStyleAt(relationAndMultiple.getX(), relationAndMultiple.getY()));
// condition was evaluated
assertEquals(headingStyle, relationAndMultiple.getStyle());
// if we only use the name, the condition cannot be evaluated
final String styleName = this.realSheet.getStyleNameAt(relationAndMultiple.getX(), relationAndMultiple.getY());
assertEquals(elseStyle, Style.getStyle(this.realSheet.getSpreadSheet().getPackage(), CellStyle.class, styleName));
relationAndMultiple.setValue(6);
assertEquals(elseStyle, relationAndMultiple.getStyle());
relationAndMultiple.setValue(15);
assertEquals(heading1Style, relationAndMultiple.getStyle());
relationAndMultiple.setValue(16);
assertEquals(heading1Style, relationAndMultiple.getStyle());
// between c and f
final char lowerChar = 'c', upperChar = 'f';
final MutableCell between = this.realSheet.getCellAt("J4");
elseStyle = between.getStyle();
assertFalse(elseStyle.equals(headingStyle) || elseStyle.equals(heading1Style));
for (int i = lowerChar - 1; i <= upperChar + 1; i++) {
between.setValue(String.valueOf((char) i));
assertEquals(i >= lowerChar && i <= upperChar ? heading1Style : elseStyle, between.getStyle());
}
// not between 1 and 3
final int lower = 1, upper = 3;
final MutableCell notBetween = this.realSheet.getCellAt("J5");
elseStyle = notBetween.getStyle();
assertFalse(elseStyle.equals(headingStyle) || elseStyle.equals(heading1Style));
for (int i = lower - 1; i <= upper + 1; i++) {
notBetween.setValue(i);
assertEquals(i < lower || i > upper ? heading1Style : elseStyle, notBetween.getStyle());
}
}
// ATTN getStyle().getReferences() is low-level so if the style of a cell is not directly
// referenced (i.e. it uses the default for its row/column) it won't get found
final MutableCell cellAt00 = this.realSheet.getCellAt(0, 0);
assertNull(cellAt00.getStyleAttr());
assertFalse(cellAt00.getStyle().getReferences().contains(cellAt00.getElement()));
// unless getStyleReferences() is used
assertTrue(this.realSheet.getStyleReferences(this.realSheet.getStyleNameAt(0, 0)).contains(Tuple2.create(0, 0)));
{
final CellStyle centeredInBold = this.realSheet.getCellAt("B9").getStyle();
assertTrue(centeredInBold.getReferences().contains(this.realSheet.getCellAt("B9").getElement()));
assertEquals("center", centeredInBold.getParagraphProperties().getAlignment());
assertEquals("bold", centeredInBold.getTextProperties().getWeight());
assertEquals("0.002cm solid #000000", centeredInBold.getTableCellProperties().getBorder(Side.LEFT));
assertEquals(null, centeredInBold.getTableCellProperties().getBorderLineWidth(Side.LEFT));
assertEquals(0, centeredInBold.getTableCellProperties().getRotationAngle());
assertEquals(true, centeredInBold.getTableCellProperties().isContentPrinted());
assertEquals(false, centeredInBold.getTableCellProperties().isContentRepeated());
assertEquals(false, centeredInBold.getTableCellProperties().isShrinkToFit());
}
{
final CellStyle notPrinted = this.realSheet.getCellAt("B6").getStyle();
assertTrue(notPrinted.getReferences().contains(this.realSheet.getCellAt("B6").getElement()));
assertEquals("start", notPrinted.getParagraphProperties().getAlignment());
assertEquals(null, notPrinted.getTextProperties().getWeight());
assertEquals("0.105cm double #000080", notPrinted.getTableCellProperties().getBorder(Side.RIGHT));
assertEquals("none", notPrinted.getTableCellProperties().getBorder(Side.TOP));
assertEquals(asList("0.035cm", "0.035cm", "0.035cm"), asList(notPrinted.getTableCellProperties().getBorderLineWidth(Side.RIGHT)));
assertEquals(333, notPrinted.getTableCellProperties().getRotationAngle());
assertEquals(false, notPrinted.getTableCellProperties().isContentPrinted());
assertEquals(false, notPrinted.getTableCellProperties().isContentRepeated());
assertEquals(true, notPrinted.getTableCellProperties().isShrinkToFit());
}
// assert that we don't crash if we ask for an attribute whose namespace is not in our
// element (here "fo")
assertNull(CellStyle.DESC.createAutoStyle(this.sheet.getSpreadSheet().getPackage()).getBackgroundColor());
// column styles
assertEquals(54.38f, this.realSheet.getColumn(1).getWidth());
final SpreadSheet sxc = new ODPackage(this.getClass().getResourceAsStream("test.sxc")).getSpreadSheet();
assertEquals(33.85f, sxc.getSheet(0).getColumn(1).getWidth());
// change style
{
final Column col1 = this.realSheet.getColumn(9);
final Column col2 = this.realSheet.getColumn(10);
// even with shared style
assertEquals(col1.getStyle().getName(), col2.getStyle().getName());
checkStyleElements(col1, col2, true);
assertNotNull(col1.getStyle().getTableColumnProperties().getBreakBefore());
final float initialWidth = col2.getWidth();
// if we change the width of col1
col1.setWidth(initialWidth + 15);
// it only change that
assertEquals(initialWidth + 15, col1.getWidth());
assertEquals(initialWidth, col2.getWidth());
// and nothing else
assertEquals(col2.getStyle().getTableColumnProperties().getBreakBefore(), col1.getStyle().getTableColumnProperties().getBreakBefore());
// new style is only created if needed
final String newName = col1.getStyle().getName();
assertFalse(newName.equals(col2.getStyle().getName()));
checkStyleElements(col1, col2, false);
col1.setWidth(initialWidth + 17);
assertEquals(newName, col1.getStyle().getName());
// assert that getPrivateStyle() works even w/o a style
final Table addSheet = this.realSheet.getSpreadSheet().addSheet("testEmptyStyle");
assertNull(addSheet.getColumn(0).getStyle());
addSheet.getColumn(0).setWidth(20);
final ColumnStyle createdStyle = addSheet.getColumn(0).getStyle();
assertNotNull(createdStyle);
assertEquals(createdStyle.getName(), addSheet.getColumn(0).getPrivateStyle().getName());
assertEquals(20.0f, addSheet.getColumn(0).getWidth());
}
// change shared style referenced by one XML element
{
// both cells have the same style
assertEquals("Default", this.sheet.getStyleNameAt(0, 0));
assertEquals("Default", this.sheet.getStyleNameAt(0, 1));
// create a new cell style and set it as the default for the first column
final CellStyle cellStyle = CellStyle.DESC.createAutoStyle(this.sheet.getSpreadSheet().getPackage(), "defaultCell");
final Element col0Elem = this.sheet.getColumn(0).getElement();
col0Elem.setAttribute("default-cell-style-name", cellStyle.getName(), col0Elem.getNamespace());
// both cells have thus the new style
assertEquals(cellStyle.getName(), this.sheet.getStyleNameAt(0, 0));
assertEquals(cellStyle.getName(), this.sheet.getStyleNameAt(0, 1));
// although the style is only referenced by the column
assertEquals(Collections.singletonList(col0Elem), cellStyle.getReferences());
// changing the background of the first cell from nothing to CYAN
assertNull(cellStyle.getBackgroundColor());
this.sheet.getCellAt(0, 0).setBackgroundColor(Color.CYAN);
// imply that the first cell changes style
final CellStyle newStyle = this.sheet.getStyleAt(0, 0);
assertFalse(cellStyle.getName().equals(newStyle.getName()));
// but not the second one
assertEquals(cellStyle.getName(), this.sheet.getStyleNameAt(0, 1));
// the first cell has indeed changed colour
assertEquals(Color.CYAN, newStyle.getTableCellProperties().getBackgroundColor());
// but not the second one
assertNull(this.sheet.getStyleAt(0, 1).getBackgroundColor());
// changing a second time doesn't create a new style
this.sheet.getCellAt(0, 0).setBackgroundColor(Color.YELLOW);
assertEquals(newStyle.getName(), this.sheet.getStyleNameAt(0, 0));
}
// test default style
final CellStyle defaultCellStyle = Style.getStyleStyleDesc(CellStyle.class, this.realSheet.getODDocument().getVersion()).findDefaultStyle(this.realSheet.getODDocument().getPackage());
assertEquals("Arial", defaultCellStyle.getTextProperties().getFontName());
final CellStyle a1Style = this.realSheet.getCellAt(0, 0).getStyle();
// even though the style doesn't contain the font name, it can find it in the default style
assertNull(a1Style.getTextProperties().getElement().getAttribute("font-name", a1Style.getElement().getNamespace("style")));
assertEquals("Arial", a1Style.getTextProperties().getFontName());
assertValid(this.sheet.getSpreadSheet());
assertValid(this.realSheet.getSpreadSheet());
}
private void checkStyleElements(final Column col1, final Column col2, final boolean same) {
final Attribute col1StyleAttr = col1.getElement().getAttribute("style-name", col1.getElement().getNamespace());
assertSame(col1.getStyle().getElement(), Style.getReferencedStyleElement(this.realSheet.getSpreadSheet().getPackage(), col1StyleAttr));
final Attribute col2StyleAttr = col2.getElement().getAttribute("style-name", col2.getElement().getNamespace());
assertSame(col2.getStyle().getElement(), Style.getReferencedStyleElement(this.realSheet.getSpreadSheet().getPackage(), col2StyleAttr));
if (same)
assertSame(col1.getStyle().getElement(), col2.getStyle().getElement());
else
assertNotSame(col1.getStyle().getElement(), col2.getStyle().getElement());
}
private void assertValid(SpreadSheet ssheet) {
assertValid(ssheet.getPackage());
}
static public void assertValid(final ODPackage pkg) {
assertValid(pkg, true);
}
static public void assertValid(final ODPackage pkg, final boolean expected) {
final Map areSubDocumentsValid = pkg.validateSubDocuments();
assertEquals(areSubDocumentsValid + "", expected, areSubDocumentsValid.isEmpty());
final String checkStyles = pkg.checkStyles();
assertNull(checkStyles, checkStyles);
}
private void assertValid(Validator val) {
assertValid(val, true);
}
private void assertValid(Validator val, final boolean expected) {
final String isValid = val.isValid();
if (expected)
assertNull(isValid, isValid);
else
assertNotNull(isValid, isValid);
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy