You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@metamodel.apache.org by ka...@apache.org on 2013/07/19 11:32:54 UTC
[11/61] [partial] Hard rename of all 'org/eobjects' folders to
'org/apache'.
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/e2e2b37a/excel/src/test/java/org/apache/metamodel/excel/ExcelDataContextTest.java
----------------------------------------------------------------------
diff --git a/excel/src/test/java/org/apache/metamodel/excel/ExcelDataContextTest.java b/excel/src/test/java/org/apache/metamodel/excel/ExcelDataContextTest.java
new file mode 100644
index 0000000..8710d52
--- /dev/null
+++ b/excel/src/test/java/org/apache/metamodel/excel/ExcelDataContextTest.java
@@ -0,0 +1,741 @@
+/**
+ * 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.eobjects.metamodel.excel;
+
+import java.io.File;
+import java.util.Arrays;
+import java.util.List;
+
+import junit.framework.TestCase;
+
+import org.eobjects.metamodel.DataContext;
+import org.eobjects.metamodel.MetaModelHelper;
+import org.eobjects.metamodel.UpdateCallback;
+import org.eobjects.metamodel.UpdateScript;
+import org.eobjects.metamodel.data.DataSet;
+import org.eobjects.metamodel.data.Row;
+import org.eobjects.metamodel.data.Style;
+import org.eobjects.metamodel.data.StyleBuilder;
+import org.eobjects.metamodel.query.Query;
+import org.eobjects.metamodel.schema.Column;
+import org.eobjects.metamodel.schema.Schema;
+import org.eobjects.metamodel.schema.Table;
+import org.eobjects.metamodel.util.DateUtils;
+import org.eobjects.metamodel.util.FileHelper;
+import org.eobjects.metamodel.util.Month;
+
+public class ExcelDataContextTest extends TestCase {
+
+ public void testErrornousConstructors() throws Exception {
+ try {
+ new ExcelDataContext(null);
+ fail("Exception expected");
+ } catch (IllegalArgumentException e) {
+ assertEquals("File cannot be null", e.getMessage());
+ }
+
+ File file = new File("src/test/resources/empty_file.xls");
+ try {
+ new ExcelDataContext(file, null);
+ fail("Exception expected");
+ } catch (IllegalArgumentException e) {
+ assertEquals("ExcelConfiguration cannot be null", e.getMessage());
+ }
+ }
+
+ @SuppressWarnings("deprecation")
+ public void testEmptyFile() throws Exception {
+ File file = new File("src/test/resources/empty_file.xls");
+ ExcelDataContext dc = new ExcelDataContext(file);
+
+ assertNull(dc.getSpreadsheetReaderDelegateClass());
+ assertEquals(1, dc.getDefaultSchema().getTableCount());
+
+ Table table = dc.getDefaultSchema().getTables()[0];
+ assertEquals("sheet", table.getName());
+ assertEquals(0, table.getColumnCount());
+
+ assertSame(file, dc.getFile());
+ }
+
+ public void testEmptyFileNoHeaderLine() throws Exception {
+ DataContext dc = new ExcelDataContext(new File("src/test/resources/empty_file.xls"), new ExcelConfiguration(
+ ExcelConfiguration.NO_COLUMN_NAME_LINE, false, false));
+ assertEquals(1, dc.getDefaultSchema().getTableCount());
+
+ Table table = dc.getDefaultSchema().getTables()[0];
+ assertEquals("sheet", table.getName());
+ assertEquals(0, table.getColumnCount());
+ }
+
+ public void testUnexistingHeaderLine() throws Exception {
+ DataContext dc = new ExcelDataContext(new File("src/test/resources/xls_people.xls"), new ExcelConfiguration(20, true,
+ false));
+ assertEquals(1, dc.getDefaultSchema().getTableCount());
+
+ Table table = dc.getDefaultSchema().getTables()[0];
+ assertEquals("xls_people", table.getName());
+ assertEquals(0, table.getColumnCount());
+ }
+
+ public void testSkipEmptyColumns() throws Exception {
+ ExcelConfiguration conf = new ExcelConfiguration(ExcelConfiguration.DEFAULT_COLUMN_NAME_LINE, true, true);
+ ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/skipped_lines.xlsx"), conf);
+ Table table = dc.getDefaultSchema().getTables()[0];
+ assertEquals("[hello, world]", Arrays.toString(table.getColumnNames()));
+
+ DataSet ds = dc.executeQuery(dc.query().from(table).select("hello").toQuery());
+ assertTrue(ds.next());
+ assertEquals("1", ds.getRow().getValue(0));
+ }
+
+ public void testDontSkipEmptyLinesNoHeader() throws Exception {
+ ExcelConfiguration conf = new ExcelConfiguration(ExcelConfiguration.NO_COLUMN_NAME_LINE, false, true);
+ ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/skipped_lines.xlsx"), conf);
+ Table table = dc.getDefaultSchema().getTables()[0];
+ assertEquals("[G, H]", Arrays.toString(table.getColumnNames()));
+
+ assertEquals(6, table.getColumnByName("G").getColumnNumber());
+ assertEquals(7, table.getColumnByName("H").getColumnNumber());
+
+ DataSet ds = dc.executeQuery(dc.query().from(table).select("G").toQuery());
+
+ // 5 empty lines
+ for (int i = 0; i < 5; i++) {
+ assertTrue(ds.next());
+ Object value = ds.getRow().getValue(0);
+ assertNull("Values was: " + value + " at row " + i, value);
+ }
+
+ assertTrue(ds.next());
+ assertEquals("hello", ds.getRow().getValue(0));
+ assertTrue(ds.next());
+ assertEquals("1", ds.getRow().getValue(0));
+ }
+
+ public void testDontSkipEmptyLinesAbsoluteHeader() throws Exception {
+ ExcelConfiguration conf = new ExcelConfiguration(6, false, true);
+ ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/skipped_lines.xlsx"), conf);
+ Table table = dc.getDefaultSchema().getTables()[0];
+ assertEquals("[hello, world]", Arrays.toString(table.getColumnNames()));
+ assertEquals(6, table.getColumnByName("hello").getColumnNumber());
+ assertEquals(7, table.getColumnByName("world").getColumnNumber());
+
+ DataSet ds = dc.executeQuery(dc.query().from(table).select("hello").toQuery());
+ assertTrue(ds.next());
+ assertEquals("1", ds.getRow().getValue(0));
+ }
+
+ public void testInvalidFormula() throws Exception {
+ ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/invalid_formula.xls"));
+ Table table = dc.getDefaultSchema().getTables()[0];
+
+ assertEquals("[name]", Arrays.toString(table.getColumnNames()));
+
+ Query q = dc.query().from(table).select("name").toQuery();
+
+ DataSet ds = dc.executeQuery(dc.query().from(table).selectCount().toQuery());
+ assertTrue(ds.next());
+ assertEquals(3, Integer.parseInt(ds.getRow().getValue(0).toString()));
+ assertFalse(ds.next());
+ assertFalse(ds.next());
+ ds.close();
+
+ ds = dc.executeQuery(q);
+
+ Row row;
+
+ assertTrue(ds.next());
+ row = ds.getRow();
+ assertEquals("TismmerswerskisMFSTLandsmeers ", row.getValue(0)
+ .toString());
+
+ assertTrue(ds.next());
+ row = ds.getRow();
+ assertEquals("-\"t\" \"houetismfsthueiss\"", row.getValue(0).toString());
+
+ assertTrue(ds.next());
+ row = ds.getRow();
+ assertEquals("TismmerswerskisMFSTLandsmeers ", row.getValue(0)
+ .toString());
+
+ assertFalse(ds.next());
+ ds.close();
+ }
+
+ public void testEvaluateFormula() throws Exception {
+ ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/xls_formulas.xls"));
+
+ Table table = dc.getDefaultSchema().getTables()[0];
+ Column[] columns = table.getColumns();
+
+ assertEquals("[some number, some mixed formula, some int only formula]", Arrays.toString(table.getColumnNames()));
+
+ Query q = dc.query().from(table).select(columns).toQuery();
+ DataSet ds = dc.executeQuery(q);
+ Object value;
+
+ assertTrue(ds.next());
+ assertEquals("1", ds.getRow().getValue(columns[0]));
+ value = ds.getRow().getValue(columns[1]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("1", value);
+
+ value = ds.getRow().getValue(columns[2]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("1", value);
+
+ assertTrue(ds.next());
+ assertEquals("2", ds.getRow().getValue(columns[0]));
+ value = ds.getRow().getValue(columns[1]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("3", value);
+
+ value = ds.getRow().getValue(columns[2]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("3", value);
+
+ assertTrue(ds.next());
+ assertEquals("3", ds.getRow().getValue(columns[0]));
+ value = ds.getRow().getValue(columns[1]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("8", value);
+
+ value = ds.getRow().getValue(columns[2]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("8", value);
+
+ assertTrue(ds.next());
+ assertEquals("4", ds.getRow().getValue(columns[0]));
+ value = ds.getRow().getValue(columns[1]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("12", value);
+
+ value = ds.getRow().getValue(columns[2]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("12", value);
+
+ assertTrue(ds.next());
+ assertEquals("5", ds.getRow().getValue(columns[0]));
+ value = ds.getRow().getValue(columns[1]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("yes", value);
+
+ value = ds.getRow().getValue(columns[2]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("5", value);
+
+ assertTrue(ds.next());
+ assertEquals("6", ds.getRow().getValue(columns[0]));
+ value = ds.getRow().getValue(columns[1]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("no", value);
+
+ value = ds.getRow().getValue(columns[2]);
+ assertEquals(String.class, value.getClass());
+ assertEquals("6", value);
+
+ assertFalse(ds.next());
+ }
+
+ public void testSingleCellSheet() throws Exception {
+ ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/xls_single_cell_sheet.xls"));
+
+ Table table = dc.getDefaultSchema().getTableByName("Sheet1");
+
+ assertNotNull(table);
+
+ assertEquals("[[Column 1], hello]", Arrays.toString(table.getColumnNames()));
+
+ Query q = dc.query().from(table).select(table.getColumns()).toQuery();
+ DataSet ds = dc.executeQuery(q);
+ assertFalse(ds.next());
+ }
+
+ public void testOpenXlsxFormat() throws Exception {
+ ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/Spreadsheet2007.xlsx"));
+ Schema schema = dc.getDefaultSchema();
+ assertEquals("Schema[name=Spreadsheet2007.xlsx]", schema.toString());
+
+ assertEquals("[Sheet1, Sheet2, Sheet3]", Arrays.toString(schema.getTableNames()));
+
+ assertEquals(0, schema.getTableByName("Sheet2").getColumnCount());
+ assertEquals(0, schema.getTableByName("Sheet3").getColumnCount());
+
+ Table table = schema.getTableByName("Sheet1");
+
+ assertEquals("[string, number, date]", Arrays.toString(table.getColumnNames()));
+
+ Query q = dc.query().from(table).select(table.getColumns()).orderBy(table.getColumnByName("number")).toQuery();
+ DataSet ds = dc.executeQuery(q);
+ List<Object[]> objectArrays = ds.toObjectArrays();
+ assertEquals(4, objectArrays.size());
+ assertEquals("[hello, 1, 2010-01-01 00:00:00]", Arrays.toString(objectArrays.get(0)));
+ assertEquals("[world, 2, 2010-01-02 00:00:00]", Arrays.toString(objectArrays.get(1)));
+ assertEquals("[foo, 3, 2010-01-03 00:00:00]", Arrays.toString(objectArrays.get(2)));
+ assertEquals("[bar, 4, 2010-01-04 00:00:00]", Arrays.toString(objectArrays.get(3)));
+ }
+
+ public void testConfigurationWithoutHeader() throws Exception {
+ File file = new File("src/test/resources/xls_people.xls");
+ DataContext dc = new ExcelDataContext(file, new ExcelConfiguration(ExcelConfiguration.NO_COLUMN_NAME_LINE, true, true));
+ Table table = dc.getDefaultSchema().getTables()[0];
+
+ String[] columnNames = table.getColumnNames();
+ assertEquals("[A, B, C, D]", Arrays.toString(columnNames));
+
+ Query q = dc.query().from(table).select(table.getColumnByName("A")).toQuery();
+ assertEquals("SELECT xls_people.A FROM xls_people.xls.xls_people", q.toSql());
+
+ DataSet dataSet = dc.executeQuery(q);
+ assertTrue(dataSet.next());
+ assertEquals("id", dataSet.getRow().getValue(0));
+ for (int i = 1; i <= 9; i++) {
+ assertTrue(dataSet.next());
+ assertEquals(i + "", dataSet.getRow().getValue(0));
+ }
+
+ assertFalse(dataSet.next());
+ }
+
+ public void testConfigurationNonDefaultColumnNameLineNumber() throws Exception {
+ File file = new File("src/test/resources/xls_people.xls");
+ DataContext dc = new ExcelDataContext(file, new ExcelConfiguration(2, true, true));
+ Table table = dc.getDefaultSchema().getTables()[0];
+
+ String[] columnNames = table.getColumnNames();
+ assertEquals("[1, mike, male, 18]", Arrays.toString(columnNames));
+
+ Query q = dc.query().from(table).select(table.getColumnByName("1")).toQuery();
+ assertEquals("SELECT xls_people.1 FROM xls_people.xls.xls_people", q.toSql());
+
+ DataSet dataSet = dc.executeQuery(q);
+ assertTrue(dataSet.next());
+ assertEquals("2", dataSet.getRow().getValue(0));
+ for (int i = 3; i <= 9; i++) {
+ assertTrue(dataSet.next());
+ assertEquals(i + "", dataSet.getRow().getValue(0));
+ }
+ assertFalse(dataSet.next());
+ }
+
+ public void testGetSchemas() throws Exception {
+ File file = new File("src/test/resources/xls_people.xls");
+ DataContext dc = new ExcelDataContext(file);
+ Schema[] schemas = dc.getSchemas();
+ assertEquals(2, schemas.length);
+ Schema schema = schemas[1];
+ assertEquals("xls_people.xls", schema.getName());
+ assertEquals(1, schema.getTableCount());
+ Table table = schema.getTables()[0];
+ assertEquals("xls_people", table.getName());
+
+ assertEquals(4, table.getColumnCount());
+ assertEquals(0, table.getRelationshipCount());
+
+ Column[] columns = table.getColumns();
+ assertEquals("id", columns[0].getName());
+ assertEquals("name", columns[1].getName());
+ assertEquals("gender", columns[2].getName());
+ assertEquals("age", columns[3].getName());
+ }
+
+ public void testMaterializeTable() throws Exception {
+ File file = new File("src/test/resources/xls_people.xls");
+ ExcelDataContext dc = new ExcelDataContext(file);
+ Table table = dc.getDefaultSchema().getTables()[0];
+ DataSet dataSet = dc.materializeMainSchemaTable(table, table.getColumns(), -1);
+ assertTrue(dataSet.next());
+ assertEquals("Row[values=[1, mike, male, 18]]", dataSet.getRow().toString());
+ assertTrue(dataSet.next());
+ assertEquals("Row[values=[2, michael, male, 19]]", dataSet.getRow().toString());
+ assertTrue(dataSet.next());
+ assertEquals("Row[values=[3, peter, male, 18]]", dataSet.getRow().toString());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertEquals("Row[values=[9, carrie, female, 17]]", dataSet.getRow().toString());
+ assertFalse(dataSet.next());
+ assertNull(dataSet.getRow());
+ }
+
+ public void testMissingValues() throws Exception {
+ File file = new File("src/test/resources/xls_missing_values.xls");
+ DataContext dc = new ExcelDataContext(file);
+ Schema schema = dc.getDefaultSchema();
+ assertEquals(1, schema.getTableCount());
+
+ Table table = schema.getTables()[0];
+ assertEquals("[Column[name=a,columnNumber=0,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
+ + "Column[name=b,columnNumber=1,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
+ + "Column[name=c,columnNumber=2,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
+ + "Column[name=d,columnNumber=3,type=VARCHAR,nullable=true,nativeType=null,columnSize=null]]",
+ Arrays.toString(table.getColumns()));
+
+ Query q = new Query().select(table.getColumns()).from(table);
+ DataSet ds = dc.executeQuery(q);
+ assertTrue(ds.next());
+ assertEquals("[1, 2, 3, null]", Arrays.toString(ds.getRow().getValues()));
+ assertTrue(ds.next());
+ assertEquals("[5, null, 7, 8]", Arrays.toString(ds.getRow().getValues()));
+ assertTrue(ds.next());
+ assertEquals("[9, 10, 11, 12]", Arrays.toString(ds.getRow().getValues()));
+ assertFalse(ds.next());
+ }
+
+ public void testMissingColumnHeader() throws Exception {
+ File file = new File("src/test/resources/xls_missing_column_header.xls");
+ DataContext dc = new ExcelDataContext(file);
+ Schema schema = dc.getDefaultSchema();
+ assertEquals(1, schema.getTableCount());
+
+ Table table = schema.getTables()[0];
+ assertEquals("[Column[name=a,columnNumber=0,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
+ + "Column[name=b,columnNumber=1,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
+ + "Column[name=[Column 3],columnNumber=2,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
+ + "Column[name=d,columnNumber=3,type=VARCHAR,nullable=true,nativeType=null,columnSize=null]]",
+ Arrays.toString(table.getColumns()));
+
+ Query q = new Query().select(table.getColumns()).from(table);
+ DataSet ds = dc.executeQuery(q);
+ assertTrue(ds.next());
+ assertEquals("[1, 2, 3, 4]", Arrays.toString(ds.getRow().getValues()));
+ assertTrue(ds.next());
+ assertEquals("[5, 6, 7, 8]", Arrays.toString(ds.getRow().getValues()));
+ assertTrue(ds.next());
+ assertEquals("[9, 10, 11, 12]", Arrays.toString(ds.getRow().getValues()));
+ assertFalse(ds.next());
+ }
+
+ public void testXlsxFormulas() throws Exception {
+ File file = new File("src/test/resources/formulas.xlsx");
+ ExcelDataContext dc = new ExcelDataContext(file);
+
+ assertEquals("[sh1]", Arrays.toString(dc.getDefaultSchema().getTableNames()));
+ assertEquals(XlsxSpreadsheetReaderDelegate.class, dc.getSpreadsheetReaderDelegateClass());
+
+ Table table = dc.getDefaultSchema().getTableByName("sh1");
+ assertEquals("[Foo, Bar]", Arrays.toString(table.getColumnNames()));
+
+ Query q = dc.query().from(table).select("Foo").toQuery();
+ DataSet ds = dc.executeQuery(q);
+
+ assertTrue(ds.next());
+ assertEquals("1", ds.getRow().getValue(0).toString());
+ assertEquals("", ds.getRow().getStyle(0).toString());
+ assertTrue(ds.next());
+ assertEquals("2", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("3", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("4", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("5", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("6", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("7", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("8", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("9", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("10", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("11", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("12", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("13", ds.getRow().getValue(0).toString());
+ assertFalse(ds.next());
+
+ q = dc.query().from(table).select("Bar").toQuery();
+ ds = dc.executeQuery(q);
+
+ assertTrue(ds.next());
+ assertEquals("lorem", ds.getRow().getValue(0).toString());
+ assertEquals("", ds.getRow().getStyle(0).toString());
+ assertTrue(ds.next());
+ assertEquals("ipsum", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("21", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("foo", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("bar", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("baz", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals(null, ds.getRow().getValue(0));
+ assertNotNull(null, ds.getRow().getStyle(0));
+ assertTrue(ds.next());
+ assertEquals("!\"#¤%&/()<>=?", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("here are", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("some invalid", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("formulas:", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("#DIV/0!", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("0", ds.getRow().getValue(0).toString());
+ assertFalse(ds.next());
+ }
+
+ public void testTicket99defect() throws Exception {
+ File file = new File("src/test/resources/ticket_199_inventory.xls");
+ DataContext dc = new ExcelDataContext(file);
+ Schema schema = dc.getDefaultSchema();
+ assertEquals(
+ "[Table[name=Sheet1,type=null,remarks=null], Table[name=Sheet2,type=null,remarks=null], Table[name=Sheet3,type=null,remarks=null]]",
+ Arrays.toString(schema.getTables()));
+
+ assertEquals(0, schema.getTableByName("Sheet2").getColumnCount());
+ assertEquals(0, schema.getTableByName("Sheet3").getColumnCount());
+
+ Table table = schema.getTableByName("Sheet1");
+ assertEquals(
+
+ "[Column[name=Pkg No.,columnNumber=0,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
+ + "Column[name=Description,columnNumber=1,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
+ + "Column[name=Room,columnNumber=2,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
+ + "Column[name=Level,columnNumber=3,type=VARCHAR,nullable=true,nativeType=null,columnSize=null]]",
+ Arrays.toString(table.getColumns()));
+ }
+
+ public void testInsertInto() throws Exception {
+ File file = new File("target/xls_people_modified.xls");
+
+ if (file.exists()) {
+ assertTrue(file.delete());
+ }
+
+ FileHelper.copy(new File("src/test/resources/xls_people.xls"), file);
+
+ assertTrue(file.exists());
+
+ ExcelDataContext dc = new ExcelDataContext(file);
+ final Table table = dc.getDefaultSchema().getTables()[0];
+ final Column nameColumn = table.getColumnByName("name");
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Style clownStyle = new StyleBuilder().bold().foreground(255, 0, 0).background(0, 0, 255).create();
+
+ Style thirtyStyle = new StyleBuilder().italic().underline().centerAligned().foreground(10, 10, 200).create();
+
+ cb.insertInto(table).value("id", 1000).value(nameColumn, "pennywise the [clown]", clownStyle)
+ .value("gender", "male").value("age", 30, thirtyStyle).execute();
+ }
+ });
+
+ DataSet ds = dc.query().from(table).select(nameColumn).orderBy(nameColumn).execute();
+ assertTrue(ds.next());
+ assertEquals("barbara", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("bob", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("carrie", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("charlotte", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("hillary", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("michael", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("mike", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("pennywise the [clown]", ds.getRow().getValue(0).toString());
+ assertEquals("font-weight: bold;color: rgb(255,0,0);background-color: rgb(0,0,255);", ds.getRow().getStyle(0).toString());
+ assertTrue(ds.next());
+ assertEquals("peter", ds.getRow().getValue(0).toString());
+ assertTrue(ds.next());
+ assertEquals("vera", ds.getRow().getValue(0).toString());
+ assertFalse(ds.next());
+ ds.close();
+
+ ds = dc.query().from(table).select("age").where("age").eq(30).execute();
+ assertTrue(ds.next());
+ assertEquals("30", ds.getRow().getValue(0));
+ assertEquals("font-style: italic;text-decoration: underline;text-align: center;color: rgb(0,0,255);", ds.getRow()
+ .getStyle(0).toCSS());
+ assertFalse(ds.next());
+ }
+
+ public void testCreateTable() throws Exception {
+ // run the same test with both XLS and XLSX (because of different
+ // workbook implementations)
+ runCreateTableTest(new File("target/xls_people_created.xls"));
+ runCreateTableTest(new File("target/xls_people_created.xlsx"));
+ }
+
+ private void runCreateTableTest(File file) {
+ if (file.exists()) {
+ assertTrue(file.delete());
+ }
+ final ExcelDataContext dc = new ExcelDataContext(file);
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Schema schema = dc.getDefaultSchema();
+ Table table1 = cb.createTable(schema, "my_table_1").withColumn("foo").withColumn("bar").withColumn("baz")
+ .execute();
+
+ assertEquals(1, schema.getTableCount());
+ assertSame(table1.getSchema(), schema);
+ assertSame(table1, schema.getTables()[0]);
+
+ Table table2 = cb.createTable(schema, "my_table_2").withColumn("foo").withColumn("bar").withColumn("baz")
+ .execute();
+
+ assertSame(table2.getSchema(), schema);
+ assertSame(table2, schema.getTables()[1]);
+ assertEquals(2, schema.getTableCount());
+
+ cb.insertInto(table1).value("foo", 123.0).value("bar", "str 1").value("baz", true).execute();
+ }
+ });
+
+ dc.refreshSchemas();
+
+ Schema schema = dc.getDefaultSchema();
+ assertEquals(2, schema.getTableCount());
+ assertEquals("[my_table_1, my_table_2]", Arrays.toString(schema.getTableNames()));
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ cb.insertInto(dc.getTableByQualifiedLabel("my_table_1")).value("foo", 456.2)
+ .value("bar", "парфюмерия +и косметика").value("baz", false).execute();
+ }
+ });
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ cb.insertInto("my_table_1").value("foo", 789).value("bar", DateUtils.get(2011, Month.JULY, 8))
+ .value("baz", false).execute();
+ }
+ });
+
+ DataSet ds = dc.query().from("my_table_1").select("foo").and("bar").and("baz").execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[123, str 1, true]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[456.2, парфюмерия +и косметика, false]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[789, 2011-07-08 00:00:00, false]]", ds.getRow().toString());
+ assertFalse(ds.next());
+ ds.close();
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback callback) {
+ callback.deleteFrom("my_table_1").where("foo").greaterThan("124").execute();
+ }
+ });
+
+ assertEquals("1", MetaModelHelper.executeSingleRowQuery(dc, dc.query().from("my_table_1").selectCount().toQuery())
+ .getValue(0).toString());
+
+ ds = dc.query().from("my_table_1").select("foo").and("bar").and("baz").execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[123, str 1, true]]", ds.getRow().toString());
+ assertFalse(ds.next());
+ ds.close();
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback callback) {
+ callback.dropTable("my_table_1").execute();
+ }
+ });
+
+ assertEquals("[my_table_2]", Arrays.toString(schema.getTableNames()));
+
+ dc.refreshSchemas();
+
+ assertEquals("[my_table_2]", Arrays.toString(schema.getTableNames()));
+
+ assertEquals(1, dc.getDefaultSchema().getTableCount());
+ }
+
+ public void testGetStyles() throws Exception {
+ DataContext dc = new ExcelDataContext(new File("src/test/resources/styles.xlsx"));
+ Table table = dc.getDefaultSchema().getTables()[0];
+ assertEquals("[style name, example]", Arrays.toString(table.getColumnNames()));
+
+ DataSet ds = dc.query().from(table).select(table.getColumns()).execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[bold, foo]]", ds.getRow().toString());
+ assertEquals("", ds.getRow().getStyle(0).toCSS());
+ assertEquals("font-weight: bold;", ds.getRow().getStyle(1).toCSS());
+
+ assertTrue(ds.next());
+ assertEquals("Row[values=[italic, foo]]", ds.getRow().toString());
+ assertEquals("", ds.getRow().getStyle(0).toCSS());
+ assertEquals("font-style: italic;", ds.getRow().getStyle(1).toCSS());
+
+ assertTrue(ds.next());
+ assertEquals("Row[values=[underline, foo]]", ds.getRow().toString());
+ assertEquals("", ds.getRow().getStyle(0).toCSS());
+ assertEquals("text-decoration: underline;", ds.getRow().getStyle(1).toCSS());
+
+ assertTrue(ds.next());
+ assertEquals("Row[values=[custom text col, foo]]", ds.getRow().toString());
+ assertEquals("", ds.getRow().getStyle(0).toCSS());
+ assertEquals("color: rgb(138,67,143);", ds.getRow().getStyle(1).toCSS());
+
+ assertTrue(ds.next());
+ assertEquals("Row[values=[yellow text col, foo]]", ds.getRow().toString());
+ assertEquals("", ds.getRow().getStyle(0).toCSS());
+ assertEquals("color: rgb(255,255,0);", ds.getRow().getStyle(1).toCSS());
+
+ assertTrue(ds.next());
+ assertEquals("Row[values=[custom bg, foo]]", ds.getRow().toString());
+ assertEquals("", ds.getRow().getStyle(0).toCSS());
+ assertEquals("background-color: rgb(136,228,171);", ds.getRow().getStyle(1).toCSS());
+
+ assertTrue(ds.next());
+ assertEquals("Row[values=[yellow bg, foo]]", ds.getRow().toString());
+ assertEquals("", ds.getRow().getStyle(0).toCSS());
+ assertEquals("background-color: rgb(255,255,0);", ds.getRow().getStyle(1).toCSS());
+
+ assertTrue(ds.next());
+ assertEquals("Row[values=[center align, foo]]", ds.getRow().toString());
+ assertEquals("", ds.getRow().getStyle(0).toCSS());
+ assertEquals("text-align: center;", ds.getRow().getStyle(1).toCSS());
+
+ assertTrue(ds.next());
+ assertEquals("Row[values=[font size 8, foo]]", ds.getRow().toString());
+ assertEquals("", ds.getRow().getStyle(0).toCSS());
+ assertEquals("font-size: 8pt;", ds.getRow().getStyle(1).toCSS());
+
+ assertTrue(ds.next());
+ assertEquals("Row[values=[font size 16, foo]]", ds.getRow().toString());
+ assertEquals("", ds.getRow().getStyle(0).toCSS());
+ assertEquals("font-size: 16pt;", ds.getRow().getStyle(1).toCSS());
+
+ assertFalse(ds.next());
+ }
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/e2e2b37a/excel/src/test/java/org/apache/metamodel/excel/ExcelUpdateCallbackTest.java
----------------------------------------------------------------------
diff --git a/excel/src/test/java/org/apache/metamodel/excel/ExcelUpdateCallbackTest.java b/excel/src/test/java/org/apache/metamodel/excel/ExcelUpdateCallbackTest.java
new file mode 100644
index 0000000..d1b4e16
--- /dev/null
+++ b/excel/src/test/java/org/apache/metamodel/excel/ExcelUpdateCallbackTest.java
@@ -0,0 +1,103 @@
+/**
+ * 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.eobjects.metamodel.excel;
+
+import java.io.File;
+import java.lang.reflect.Field;
+import java.util.Arrays;
+import java.util.Map;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.xssf.streaming.SXSSFRow;
+import org.apache.poi.xssf.streaming.SXSSFSheet;
+import org.eobjects.metamodel.data.DataSet;
+import org.eobjects.metamodel.schema.Table;
+
+public class ExcelUpdateCallbackTest extends TestCase {
+
+ public void testStreamingAPI() throws Exception {
+ File file = new File("target/streaming-api-test.xlsx");
+ if (file.exists()) {
+ file.delete();
+ }
+
+ assertFalse(file.exists());
+
+ // write using streaming writer
+ {
+ ExcelDataContext dc = new ExcelDataContext(file);
+ ExcelUpdateCallback callback = new ExcelUpdateCallback(dc);
+
+ SXSSFSheet sheet = (SXSSFSheet) callback.createSheet("foobar");
+
+ Field windowSizeField = SXSSFSheet.class
+ .getDeclaredField("_randomAccessWindowSize");
+ windowSizeField.setAccessible(true);
+ int windowSize = windowSizeField.getInt(sheet);
+ assertEquals(1000, windowSize);
+
+ Field rowsField = SXSSFSheet.class.getDeclaredField("_rows");
+ rowsField.setAccessible(true);
+ @SuppressWarnings("unchecked")
+ Map<Integer, SXSSFRow> rows = (Map<Integer, SXSSFRow>) rowsField
+ .get(sheet);
+ assertEquals(0, rows.size());
+
+ // create 5x the amound of rows as the streaming sheet will hold in
+ // memory
+ for (int i = 0; i < windowSize * 5; i++) {
+ Row row = sheet.createRow(i);
+ Cell cell = row.createCell(0);
+ cell.setCellValue("value" + i);
+
+ assertTrue(rows.size() <= 1000);
+ }
+
+ assertEquals(1000, rows.size());
+
+ ExcelUtils.writeWorkbook(dc, sheet.getWorkbook());
+ }
+
+ assertTrue("Usually the file size will be circa 42000, but it was: "
+ + file.length(), file.length() > 40000 && file.length() < 45000);
+
+ // read to check results
+ {
+ ExcelDataContext dc = new ExcelDataContext(file);
+ assertEquals("[foobar]",
+ Arrays.toString(dc.getDefaultSchema().getTableNames()));
+
+ Table table = dc.getDefaultSchema().getTableByName("foobar");
+
+ assertEquals("[value0]", Arrays.toString(table.getColumnNames()));
+
+ DataSet ds = dc.query().from(table).select("value0").execute();
+ int recordNo = 1;
+ while (ds.next()) {
+ assertEquals("value" + recordNo, ds.getRow().getValue(0));
+ recordNo++;
+ }
+
+ assertEquals(5000, recordNo);
+ }
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/e2e2b37a/excel/src/test/java/org/apache/metamodel/excel/ZeroBasedRowIteratorTest.java
----------------------------------------------------------------------
diff --git a/excel/src/test/java/org/apache/metamodel/excel/ZeroBasedRowIteratorTest.java b/excel/src/test/java/org/apache/metamodel/excel/ZeroBasedRowIteratorTest.java
new file mode 100644
index 0000000..3e2852a
--- /dev/null
+++ b/excel/src/test/java/org/apache/metamodel/excel/ZeroBasedRowIteratorTest.java
@@ -0,0 +1,75 @@
+/**
+ * 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.eobjects.metamodel.excel;
+
+import java.io.FileInputStream;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.WorkbookFactory;
+import org.easymock.EasyMock;
+import org.eobjects.metamodel.excel.ZeroBasedRowIterator;
+
+public class ZeroBasedRowIteratorTest extends TestCase {
+
+ public void testHasNext() throws Exception {
+ Workbook workbook = WorkbookFactory.create(new FileInputStream(
+ "src/test/resources/xls_single_cell_sheet.xls"));
+ Sheet sheet = workbook.getSheetAt(0);
+
+ // POI's row numbers are 0-based also - the last cell in the sheet is
+ // actually A6.
+ assertEquals(5, sheet.getLastRowNum());
+
+ ZeroBasedRowIterator it = new ZeroBasedRowIterator(sheet);
+
+ assertTrue(it.hasNext());
+ assertNull(it.next());
+
+ assertTrue(it.hasNext());
+ assertNull(it.next());
+
+ assertTrue(it.hasNext());
+ assertNull(it.next());
+
+ assertTrue(it.hasNext());
+ assertNull(it.next());
+
+ assertTrue(it.hasNext());
+ assertNull(it.next());
+
+ assertTrue(it.hasNext());
+ assertNotNull(it.next());
+
+ assertFalse(it.hasNext());
+ }
+
+ public void testUnsupportedRemove() throws Exception {
+ ZeroBasedRowIterator it = new ZeroBasedRowIterator(EasyMock.createMock(Sheet.class));
+
+ try {
+ it.remove();
+ fail("Exception expected");
+ } catch (UnsupportedOperationException e) {
+ assertEquals("remove() is not supported", e.getMessage());
+ }
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/e2e2b37a/excel/src/test/java/org/eobjects/metamodel/excel/DefaultSpreadsheetReaderDelegateTest.java
----------------------------------------------------------------------
diff --git a/excel/src/test/java/org/eobjects/metamodel/excel/DefaultSpreadsheetReaderDelegateTest.java b/excel/src/test/java/org/eobjects/metamodel/excel/DefaultSpreadsheetReaderDelegateTest.java
deleted file mode 100644
index dc2e87c..0000000
--- a/excel/src/test/java/org/eobjects/metamodel/excel/DefaultSpreadsheetReaderDelegateTest.java
+++ /dev/null
@@ -1,244 +0,0 @@
-/**
- * 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.eobjects.metamodel.excel;
-
-import java.io.File;
-import java.lang.reflect.Field;
-
-import junit.framework.TestCase;
-
-import org.eobjects.metamodel.data.DataSet;
-import org.eobjects.metamodel.data.Row;
-import org.eobjects.metamodel.data.Style;
-import org.eobjects.metamodel.query.Query;
-import org.eobjects.metamodel.schema.Column;
-import org.eobjects.metamodel.schema.Schema;
-import org.eobjects.metamodel.schema.Table;
-
-public class DefaultSpreadsheetReaderDelegateTest extends TestCase {
-
- public void testReadAllTestResourceFiles() {
- File[] listFiles = new File("src/test/resources").listFiles();
- for (File file : listFiles) {
- if (file.isFile() && file.getName().indexOf(".xls") != -1) {
- try {
- runTest(file);
- } catch (Throwable e) {
- throw new IllegalStateException("Exception in file: "
- + file, e);
- }
- }
- }
- }
-
- private void runTest(File file) throws Exception {
- ExcelDataContext mainDataContext = new ExcelDataContext(file);
- applyReaderDelegate(mainDataContext);
-
- ExcelDataContext comparedDataContext = null;
- if (file.getName().endsWith(".xlsx")) {
- comparedDataContext = new ExcelDataContext(file);
- }
-
- Schema schema = mainDataContext.getDefaultSchema();
- assertNotNull(schema);
- assertEquals(file.getName(), schema.getName());
-
- if (comparedDataContext != null) {
- assertEquals(comparedDataContext.getDefaultSchema().getName(),
- schema.getName());
- }
-
- assertEquals(DefaultSpreadsheetReaderDelegate.class,
- mainDataContext.getSpreadsheetReaderDelegateClass());
-
- Table[] tables = schema.getTables();
- assertTrue(tables.length > 0);
-
- Table[] comparedTables = null;
- if (comparedDataContext != null) {
- assertEquals(XlsxSpreadsheetReaderDelegate.class,
- comparedDataContext.getSpreadsheetReaderDelegateClass());
- comparedTables = comparedDataContext.getDefaultSchema().getTables();
- assertEquals(comparedTables.length, tables.length);
- }
-
- for (int i = 0; i < tables.length; i++) {
- Table table = tables[i];
- Column[] columns = table.getColumns();
- Query query = mainDataContext.query().from(table).select(columns)
- .toQuery();
- DataSet dataSet = mainDataContext.executeQuery(query);
-
- DataSet comparedDataSet = null;
- if (comparedDataContext != null) {
- Table comparedTable = comparedTables[i];
- assertEquals(comparedTable.getName(), table.getName());
- assertEquals(comparedTable.getColumnCount(),
- table.getColumnCount());
-
- Column[] comparedColumns = comparedTable.getColumns();
- for (int j = 0; j < comparedColumns.length; j++) {
- assertEquals(columns[j].getColumnNumber(),
- comparedColumns[j].getColumnNumber());
- }
-
- Query comparedQuery = comparedDataContext.query()
- .from(comparedTable).select(comparedColumns).toQuery();
- comparedDataSet = comparedDataContext
- .executeQuery(comparedQuery);
- }
-
- while (dataSet.next()) {
- Row row = dataSet.getRow();
- assertNotNull(row);
- Object[] values = row.getValues();
-
- assertEquals(values.length, table.getColumnCount());
-
- if (comparedDataSet != null) {
- boolean next = comparedDataSet.next();
- assertTrue("No comparable row exists for: " + row, next);
- Row comparedRow = comparedDataSet.getRow();
- assertNotNull(comparedRow);
- Object[] comparedValues = comparedRow.getValues();
- assertEquals(comparedValues.length, table.getColumnCount());
-
- for (int j = 0; j < comparedValues.length; j++) {
- assertEquals(comparedValues[j], values[j]);
- }
-
- // compare styles
- for (int j = 0; j < comparedValues.length; j++) {
- Style style1 = comparedRow.getStyle(j);
- Style style2 = row.getStyle(j);
- assertEquals("Diff in style on row: " + row
- + " (value index = " + j + ")\nStyle 1: "
- + style1 + "\nStyle 2: " + style2 + ". ",
- style1, style2);
- }
- }
- }
- dataSet.close();
-
- if (comparedDataSet != null) {
- assertFalse(comparedDataSet.next());
- comparedDataSet.close();
- }
- }
- }
-
- /**
- * Applies the {@link DefaultSpreadsheetReaderDelegate} through reflection.
- *
- * @param dataContext
- * @throws NoSuchFieldException
- * @throws IllegalAccessException
- */
- private void applyReaderDelegate(ExcelDataContext dataContext)
- throws NoSuchFieldException, IllegalAccessException {
- Field field = ExcelDataContext.class
- .getDeclaredField("_spreadsheetReaderDelegate");
- assertNotNull(field);
- field.setAccessible(true);
- field.set(
- dataContext,
- new DefaultSpreadsheetReaderDelegate(dataContext
- .getConfiguration()));
- }
-
- public void testStylingOfDateCell() throws Exception {
- ExcelDataContext dc = new ExcelDataContext(new File(
- "src/test/resources/Spreadsheet2007.xlsx"));
- applyReaderDelegate(dc);
-
- Table table = dc.getDefaultSchema().getTables()[0];
-
- final String expectedStyling = "";
-
- DataSet dataSet = dc.query().from(table).select("date").execute();
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertFalse(dataSet.next());
- dataSet.close();
- }
-
- public void testStylingOfNullCell() throws Exception {
- ExcelDataContext dc = new ExcelDataContext(new File(
- "src/test/resources/formulas.xlsx"));
- applyReaderDelegate(dc);
-
- Table table = dc.getDefaultSchema().getTables()[0];
-
- DataSet dataSet = dc.query().from(table).select("Foo").and("Bar")
- .where("Foo").isEquals("7").execute();
- assertTrue(dataSet.next());
- Row row = dataSet.getRow();
- assertNotNull(row.getStyle(0));
-
- final String expectedStyling = "";
-
- assertEquals(expectedStyling, row.getStyle(0).toCSS());
- assertNotNull(row.getStyle(1));
- assertEquals(expectedStyling, row.getStyle(1).toCSS());
- assertFalse(dataSet.next());
- dataSet.close();
-
- dataSet = dc.query().from(table).select("Foo").and("Bar").execute();
- assertTrue(dataSet.next());
- row = dataSet.getRow();
- assertNotNull(row.getStyle(0));
- assertEquals(expectedStyling, row.getStyle(0).toCSS());
- assertNotNull(row.getStyle(1));
- assertEquals(expectedStyling, row.getStyle(1).toCSS());
-
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertTrue(dataSet.next());
- assertEquals(expectedStyling, dataSet.getRow().getStyle(0).toCSS());
- assertFalse(dataSet.next());
- dataSet.close();
- }
-}
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/e2e2b37a/excel/src/test/java/org/eobjects/metamodel/excel/ExcelConfigurationTest.java
----------------------------------------------------------------------
diff --git a/excel/src/test/java/org/eobjects/metamodel/excel/ExcelConfigurationTest.java b/excel/src/test/java/org/eobjects/metamodel/excel/ExcelConfigurationTest.java
deleted file mode 100644
index 5bc9b00..0000000
--- a/excel/src/test/java/org/eobjects/metamodel/excel/ExcelConfigurationTest.java
+++ /dev/null
@@ -1,42 +0,0 @@
-/**
- * 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.eobjects.metamodel.excel;
-
-import org.eobjects.metamodel.excel.ExcelConfiguration;
-
-import junit.framework.TestCase;
-
-public class ExcelConfigurationTest extends TestCase {
-
- public void testToString() throws Exception {
- ExcelConfiguration conf = new ExcelConfiguration(1, true, false);
- assertEquals(
- "ExcelConfiguration[columnNameLineNumber=1, skipEmptyLines=true, skipEmptyColumns=false]",
- conf.toString());
- }
-
- public void testEquals() throws Exception {
- ExcelConfiguration conf1 = new ExcelConfiguration(1, true, false);
- ExcelConfiguration conf2 = new ExcelConfiguration(1, true, false);
- ExcelConfiguration conf3 = new ExcelConfiguration(2, true, false);
-
- assertEquals(conf1, conf2);
- assertFalse(conf1.equals(conf3));
- }
-}
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/e2e2b37a/excel/src/test/java/org/eobjects/metamodel/excel/ExcelDataContextTest.java
----------------------------------------------------------------------
diff --git a/excel/src/test/java/org/eobjects/metamodel/excel/ExcelDataContextTest.java b/excel/src/test/java/org/eobjects/metamodel/excel/ExcelDataContextTest.java
deleted file mode 100644
index 8710d52..0000000
--- a/excel/src/test/java/org/eobjects/metamodel/excel/ExcelDataContextTest.java
+++ /dev/null
@@ -1,741 +0,0 @@
-/**
- * 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.eobjects.metamodel.excel;
-
-import java.io.File;
-import java.util.Arrays;
-import java.util.List;
-
-import junit.framework.TestCase;
-
-import org.eobjects.metamodel.DataContext;
-import org.eobjects.metamodel.MetaModelHelper;
-import org.eobjects.metamodel.UpdateCallback;
-import org.eobjects.metamodel.UpdateScript;
-import org.eobjects.metamodel.data.DataSet;
-import org.eobjects.metamodel.data.Row;
-import org.eobjects.metamodel.data.Style;
-import org.eobjects.metamodel.data.StyleBuilder;
-import org.eobjects.metamodel.query.Query;
-import org.eobjects.metamodel.schema.Column;
-import org.eobjects.metamodel.schema.Schema;
-import org.eobjects.metamodel.schema.Table;
-import org.eobjects.metamodel.util.DateUtils;
-import org.eobjects.metamodel.util.FileHelper;
-import org.eobjects.metamodel.util.Month;
-
-public class ExcelDataContextTest extends TestCase {
-
- public void testErrornousConstructors() throws Exception {
- try {
- new ExcelDataContext(null);
- fail("Exception expected");
- } catch (IllegalArgumentException e) {
- assertEquals("File cannot be null", e.getMessage());
- }
-
- File file = new File("src/test/resources/empty_file.xls");
- try {
- new ExcelDataContext(file, null);
- fail("Exception expected");
- } catch (IllegalArgumentException e) {
- assertEquals("ExcelConfiguration cannot be null", e.getMessage());
- }
- }
-
- @SuppressWarnings("deprecation")
- public void testEmptyFile() throws Exception {
- File file = new File("src/test/resources/empty_file.xls");
- ExcelDataContext dc = new ExcelDataContext(file);
-
- assertNull(dc.getSpreadsheetReaderDelegateClass());
- assertEquals(1, dc.getDefaultSchema().getTableCount());
-
- Table table = dc.getDefaultSchema().getTables()[0];
- assertEquals("sheet", table.getName());
- assertEquals(0, table.getColumnCount());
-
- assertSame(file, dc.getFile());
- }
-
- public void testEmptyFileNoHeaderLine() throws Exception {
- DataContext dc = new ExcelDataContext(new File("src/test/resources/empty_file.xls"), new ExcelConfiguration(
- ExcelConfiguration.NO_COLUMN_NAME_LINE, false, false));
- assertEquals(1, dc.getDefaultSchema().getTableCount());
-
- Table table = dc.getDefaultSchema().getTables()[0];
- assertEquals("sheet", table.getName());
- assertEquals(0, table.getColumnCount());
- }
-
- public void testUnexistingHeaderLine() throws Exception {
- DataContext dc = new ExcelDataContext(new File("src/test/resources/xls_people.xls"), new ExcelConfiguration(20, true,
- false));
- assertEquals(1, dc.getDefaultSchema().getTableCount());
-
- Table table = dc.getDefaultSchema().getTables()[0];
- assertEquals("xls_people", table.getName());
- assertEquals(0, table.getColumnCount());
- }
-
- public void testSkipEmptyColumns() throws Exception {
- ExcelConfiguration conf = new ExcelConfiguration(ExcelConfiguration.DEFAULT_COLUMN_NAME_LINE, true, true);
- ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/skipped_lines.xlsx"), conf);
- Table table = dc.getDefaultSchema().getTables()[0];
- assertEquals("[hello, world]", Arrays.toString(table.getColumnNames()));
-
- DataSet ds = dc.executeQuery(dc.query().from(table).select("hello").toQuery());
- assertTrue(ds.next());
- assertEquals("1", ds.getRow().getValue(0));
- }
-
- public void testDontSkipEmptyLinesNoHeader() throws Exception {
- ExcelConfiguration conf = new ExcelConfiguration(ExcelConfiguration.NO_COLUMN_NAME_LINE, false, true);
- ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/skipped_lines.xlsx"), conf);
- Table table = dc.getDefaultSchema().getTables()[0];
- assertEquals("[G, H]", Arrays.toString(table.getColumnNames()));
-
- assertEquals(6, table.getColumnByName("G").getColumnNumber());
- assertEquals(7, table.getColumnByName("H").getColumnNumber());
-
- DataSet ds = dc.executeQuery(dc.query().from(table).select("G").toQuery());
-
- // 5 empty lines
- for (int i = 0; i < 5; i++) {
- assertTrue(ds.next());
- Object value = ds.getRow().getValue(0);
- assertNull("Values was: " + value + " at row " + i, value);
- }
-
- assertTrue(ds.next());
- assertEquals("hello", ds.getRow().getValue(0));
- assertTrue(ds.next());
- assertEquals("1", ds.getRow().getValue(0));
- }
-
- public void testDontSkipEmptyLinesAbsoluteHeader() throws Exception {
- ExcelConfiguration conf = new ExcelConfiguration(6, false, true);
- ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/skipped_lines.xlsx"), conf);
- Table table = dc.getDefaultSchema().getTables()[0];
- assertEquals("[hello, world]", Arrays.toString(table.getColumnNames()));
- assertEquals(6, table.getColumnByName("hello").getColumnNumber());
- assertEquals(7, table.getColumnByName("world").getColumnNumber());
-
- DataSet ds = dc.executeQuery(dc.query().from(table).select("hello").toQuery());
- assertTrue(ds.next());
- assertEquals("1", ds.getRow().getValue(0));
- }
-
- public void testInvalidFormula() throws Exception {
- ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/invalid_formula.xls"));
- Table table = dc.getDefaultSchema().getTables()[0];
-
- assertEquals("[name]", Arrays.toString(table.getColumnNames()));
-
- Query q = dc.query().from(table).select("name").toQuery();
-
- DataSet ds = dc.executeQuery(dc.query().from(table).selectCount().toQuery());
- assertTrue(ds.next());
- assertEquals(3, Integer.parseInt(ds.getRow().getValue(0).toString()));
- assertFalse(ds.next());
- assertFalse(ds.next());
- ds.close();
-
- ds = dc.executeQuery(q);
-
- Row row;
-
- assertTrue(ds.next());
- row = ds.getRow();
- assertEquals("TismmerswerskisMFSTLandsmeers ", row.getValue(0)
- .toString());
-
- assertTrue(ds.next());
- row = ds.getRow();
- assertEquals("-\"t\" \"houetismfsthueiss\"", row.getValue(0).toString());
-
- assertTrue(ds.next());
- row = ds.getRow();
- assertEquals("TismmerswerskisMFSTLandsmeers ", row.getValue(0)
- .toString());
-
- assertFalse(ds.next());
- ds.close();
- }
-
- public void testEvaluateFormula() throws Exception {
- ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/xls_formulas.xls"));
-
- Table table = dc.getDefaultSchema().getTables()[0];
- Column[] columns = table.getColumns();
-
- assertEquals("[some number, some mixed formula, some int only formula]", Arrays.toString(table.getColumnNames()));
-
- Query q = dc.query().from(table).select(columns).toQuery();
- DataSet ds = dc.executeQuery(q);
- Object value;
-
- assertTrue(ds.next());
- assertEquals("1", ds.getRow().getValue(columns[0]));
- value = ds.getRow().getValue(columns[1]);
- assertEquals(String.class, value.getClass());
- assertEquals("1", value);
-
- value = ds.getRow().getValue(columns[2]);
- assertEquals(String.class, value.getClass());
- assertEquals("1", value);
-
- assertTrue(ds.next());
- assertEquals("2", ds.getRow().getValue(columns[0]));
- value = ds.getRow().getValue(columns[1]);
- assertEquals(String.class, value.getClass());
- assertEquals("3", value);
-
- value = ds.getRow().getValue(columns[2]);
- assertEquals(String.class, value.getClass());
- assertEquals("3", value);
-
- assertTrue(ds.next());
- assertEquals("3", ds.getRow().getValue(columns[0]));
- value = ds.getRow().getValue(columns[1]);
- assertEquals(String.class, value.getClass());
- assertEquals("8", value);
-
- value = ds.getRow().getValue(columns[2]);
- assertEquals(String.class, value.getClass());
- assertEquals("8", value);
-
- assertTrue(ds.next());
- assertEquals("4", ds.getRow().getValue(columns[0]));
- value = ds.getRow().getValue(columns[1]);
- assertEquals(String.class, value.getClass());
- assertEquals("12", value);
-
- value = ds.getRow().getValue(columns[2]);
- assertEquals(String.class, value.getClass());
- assertEquals("12", value);
-
- assertTrue(ds.next());
- assertEquals("5", ds.getRow().getValue(columns[0]));
- value = ds.getRow().getValue(columns[1]);
- assertEquals(String.class, value.getClass());
- assertEquals("yes", value);
-
- value = ds.getRow().getValue(columns[2]);
- assertEquals(String.class, value.getClass());
- assertEquals("5", value);
-
- assertTrue(ds.next());
- assertEquals("6", ds.getRow().getValue(columns[0]));
- value = ds.getRow().getValue(columns[1]);
- assertEquals(String.class, value.getClass());
- assertEquals("no", value);
-
- value = ds.getRow().getValue(columns[2]);
- assertEquals(String.class, value.getClass());
- assertEquals("6", value);
-
- assertFalse(ds.next());
- }
-
- public void testSingleCellSheet() throws Exception {
- ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/xls_single_cell_sheet.xls"));
-
- Table table = dc.getDefaultSchema().getTableByName("Sheet1");
-
- assertNotNull(table);
-
- assertEquals("[[Column 1], hello]", Arrays.toString(table.getColumnNames()));
-
- Query q = dc.query().from(table).select(table.getColumns()).toQuery();
- DataSet ds = dc.executeQuery(q);
- assertFalse(ds.next());
- }
-
- public void testOpenXlsxFormat() throws Exception {
- ExcelDataContext dc = new ExcelDataContext(new File("src/test/resources/Spreadsheet2007.xlsx"));
- Schema schema = dc.getDefaultSchema();
- assertEquals("Schema[name=Spreadsheet2007.xlsx]", schema.toString());
-
- assertEquals("[Sheet1, Sheet2, Sheet3]", Arrays.toString(schema.getTableNames()));
-
- assertEquals(0, schema.getTableByName("Sheet2").getColumnCount());
- assertEquals(0, schema.getTableByName("Sheet3").getColumnCount());
-
- Table table = schema.getTableByName("Sheet1");
-
- assertEquals("[string, number, date]", Arrays.toString(table.getColumnNames()));
-
- Query q = dc.query().from(table).select(table.getColumns()).orderBy(table.getColumnByName("number")).toQuery();
- DataSet ds = dc.executeQuery(q);
- List<Object[]> objectArrays = ds.toObjectArrays();
- assertEquals(4, objectArrays.size());
- assertEquals("[hello, 1, 2010-01-01 00:00:00]", Arrays.toString(objectArrays.get(0)));
- assertEquals("[world, 2, 2010-01-02 00:00:00]", Arrays.toString(objectArrays.get(1)));
- assertEquals("[foo, 3, 2010-01-03 00:00:00]", Arrays.toString(objectArrays.get(2)));
- assertEquals("[bar, 4, 2010-01-04 00:00:00]", Arrays.toString(objectArrays.get(3)));
- }
-
- public void testConfigurationWithoutHeader() throws Exception {
- File file = new File("src/test/resources/xls_people.xls");
- DataContext dc = new ExcelDataContext(file, new ExcelConfiguration(ExcelConfiguration.NO_COLUMN_NAME_LINE, true, true));
- Table table = dc.getDefaultSchema().getTables()[0];
-
- String[] columnNames = table.getColumnNames();
- assertEquals("[A, B, C, D]", Arrays.toString(columnNames));
-
- Query q = dc.query().from(table).select(table.getColumnByName("A")).toQuery();
- assertEquals("SELECT xls_people.A FROM xls_people.xls.xls_people", q.toSql());
-
- DataSet dataSet = dc.executeQuery(q);
- assertTrue(dataSet.next());
- assertEquals("id", dataSet.getRow().getValue(0));
- for (int i = 1; i <= 9; i++) {
- assertTrue(dataSet.next());
- assertEquals(i + "", dataSet.getRow().getValue(0));
- }
-
- assertFalse(dataSet.next());
- }
-
- public void testConfigurationNonDefaultColumnNameLineNumber() throws Exception {
- File file = new File("src/test/resources/xls_people.xls");
- DataContext dc = new ExcelDataContext(file, new ExcelConfiguration(2, true, true));
- Table table = dc.getDefaultSchema().getTables()[0];
-
- String[] columnNames = table.getColumnNames();
- assertEquals("[1, mike, male, 18]", Arrays.toString(columnNames));
-
- Query q = dc.query().from(table).select(table.getColumnByName("1")).toQuery();
- assertEquals("SELECT xls_people.1 FROM xls_people.xls.xls_people", q.toSql());
-
- DataSet dataSet = dc.executeQuery(q);
- assertTrue(dataSet.next());
- assertEquals("2", dataSet.getRow().getValue(0));
- for (int i = 3; i <= 9; i++) {
- assertTrue(dataSet.next());
- assertEquals(i + "", dataSet.getRow().getValue(0));
- }
- assertFalse(dataSet.next());
- }
-
- public void testGetSchemas() throws Exception {
- File file = new File("src/test/resources/xls_people.xls");
- DataContext dc = new ExcelDataContext(file);
- Schema[] schemas = dc.getSchemas();
- assertEquals(2, schemas.length);
- Schema schema = schemas[1];
- assertEquals("xls_people.xls", schema.getName());
- assertEquals(1, schema.getTableCount());
- Table table = schema.getTables()[0];
- assertEquals("xls_people", table.getName());
-
- assertEquals(4, table.getColumnCount());
- assertEquals(0, table.getRelationshipCount());
-
- Column[] columns = table.getColumns();
- assertEquals("id", columns[0].getName());
- assertEquals("name", columns[1].getName());
- assertEquals("gender", columns[2].getName());
- assertEquals("age", columns[3].getName());
- }
-
- public void testMaterializeTable() throws Exception {
- File file = new File("src/test/resources/xls_people.xls");
- ExcelDataContext dc = new ExcelDataContext(file);
- Table table = dc.getDefaultSchema().getTables()[0];
- DataSet dataSet = dc.materializeMainSchemaTable(table, table.getColumns(), -1);
- assertTrue(dataSet.next());
- assertEquals("Row[values=[1, mike, male, 18]]", dataSet.getRow().toString());
- assertTrue(dataSet.next());
- assertEquals("Row[values=[2, michael, male, 19]]", dataSet.getRow().toString());
- assertTrue(dataSet.next());
- assertEquals("Row[values=[3, peter, male, 18]]", dataSet.getRow().toString());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertEquals("Row[values=[9, carrie, female, 17]]", dataSet.getRow().toString());
- assertFalse(dataSet.next());
- assertNull(dataSet.getRow());
- }
-
- public void testMissingValues() throws Exception {
- File file = new File("src/test/resources/xls_missing_values.xls");
- DataContext dc = new ExcelDataContext(file);
- Schema schema = dc.getDefaultSchema();
- assertEquals(1, schema.getTableCount());
-
- Table table = schema.getTables()[0];
- assertEquals("[Column[name=a,columnNumber=0,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
- + "Column[name=b,columnNumber=1,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
- + "Column[name=c,columnNumber=2,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
- + "Column[name=d,columnNumber=3,type=VARCHAR,nullable=true,nativeType=null,columnSize=null]]",
- Arrays.toString(table.getColumns()));
-
- Query q = new Query().select(table.getColumns()).from(table);
- DataSet ds = dc.executeQuery(q);
- assertTrue(ds.next());
- assertEquals("[1, 2, 3, null]", Arrays.toString(ds.getRow().getValues()));
- assertTrue(ds.next());
- assertEquals("[5, null, 7, 8]", Arrays.toString(ds.getRow().getValues()));
- assertTrue(ds.next());
- assertEquals("[9, 10, 11, 12]", Arrays.toString(ds.getRow().getValues()));
- assertFalse(ds.next());
- }
-
- public void testMissingColumnHeader() throws Exception {
- File file = new File("src/test/resources/xls_missing_column_header.xls");
- DataContext dc = new ExcelDataContext(file);
- Schema schema = dc.getDefaultSchema();
- assertEquals(1, schema.getTableCount());
-
- Table table = schema.getTables()[0];
- assertEquals("[Column[name=a,columnNumber=0,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
- + "Column[name=b,columnNumber=1,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
- + "Column[name=[Column 3],columnNumber=2,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
- + "Column[name=d,columnNumber=3,type=VARCHAR,nullable=true,nativeType=null,columnSize=null]]",
- Arrays.toString(table.getColumns()));
-
- Query q = new Query().select(table.getColumns()).from(table);
- DataSet ds = dc.executeQuery(q);
- assertTrue(ds.next());
- assertEquals("[1, 2, 3, 4]", Arrays.toString(ds.getRow().getValues()));
- assertTrue(ds.next());
- assertEquals("[5, 6, 7, 8]", Arrays.toString(ds.getRow().getValues()));
- assertTrue(ds.next());
- assertEquals("[9, 10, 11, 12]", Arrays.toString(ds.getRow().getValues()));
- assertFalse(ds.next());
- }
-
- public void testXlsxFormulas() throws Exception {
- File file = new File("src/test/resources/formulas.xlsx");
- ExcelDataContext dc = new ExcelDataContext(file);
-
- assertEquals("[sh1]", Arrays.toString(dc.getDefaultSchema().getTableNames()));
- assertEquals(XlsxSpreadsheetReaderDelegate.class, dc.getSpreadsheetReaderDelegateClass());
-
- Table table = dc.getDefaultSchema().getTableByName("sh1");
- assertEquals("[Foo, Bar]", Arrays.toString(table.getColumnNames()));
-
- Query q = dc.query().from(table).select("Foo").toQuery();
- DataSet ds = dc.executeQuery(q);
-
- assertTrue(ds.next());
- assertEquals("1", ds.getRow().getValue(0).toString());
- assertEquals("", ds.getRow().getStyle(0).toString());
- assertTrue(ds.next());
- assertEquals("2", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("3", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("4", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("5", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("6", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("7", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("8", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("9", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("10", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("11", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("12", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("13", ds.getRow().getValue(0).toString());
- assertFalse(ds.next());
-
- q = dc.query().from(table).select("Bar").toQuery();
- ds = dc.executeQuery(q);
-
- assertTrue(ds.next());
- assertEquals("lorem", ds.getRow().getValue(0).toString());
- assertEquals("", ds.getRow().getStyle(0).toString());
- assertTrue(ds.next());
- assertEquals("ipsum", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("21", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("foo", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("bar", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("baz", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals(null, ds.getRow().getValue(0));
- assertNotNull(null, ds.getRow().getStyle(0));
- assertTrue(ds.next());
- assertEquals("!\"#¤%&/()<>=?", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("here are", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("some invalid", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("formulas:", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("#DIV/0!", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("0", ds.getRow().getValue(0).toString());
- assertFalse(ds.next());
- }
-
- public void testTicket99defect() throws Exception {
- File file = new File("src/test/resources/ticket_199_inventory.xls");
- DataContext dc = new ExcelDataContext(file);
- Schema schema = dc.getDefaultSchema();
- assertEquals(
- "[Table[name=Sheet1,type=null,remarks=null], Table[name=Sheet2,type=null,remarks=null], Table[name=Sheet3,type=null,remarks=null]]",
- Arrays.toString(schema.getTables()));
-
- assertEquals(0, schema.getTableByName("Sheet2").getColumnCount());
- assertEquals(0, schema.getTableByName("Sheet3").getColumnCount());
-
- Table table = schema.getTableByName("Sheet1");
- assertEquals(
-
- "[Column[name=Pkg No.,columnNumber=0,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
- + "Column[name=Description,columnNumber=1,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
- + "Column[name=Room,columnNumber=2,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
- + "Column[name=Level,columnNumber=3,type=VARCHAR,nullable=true,nativeType=null,columnSize=null]]",
- Arrays.toString(table.getColumns()));
- }
-
- public void testInsertInto() throws Exception {
- File file = new File("target/xls_people_modified.xls");
-
- if (file.exists()) {
- assertTrue(file.delete());
- }
-
- FileHelper.copy(new File("src/test/resources/xls_people.xls"), file);
-
- assertTrue(file.exists());
-
- ExcelDataContext dc = new ExcelDataContext(file);
- final Table table = dc.getDefaultSchema().getTables()[0];
- final Column nameColumn = table.getColumnByName("name");
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Style clownStyle = new StyleBuilder().bold().foreground(255, 0, 0).background(0, 0, 255).create();
-
- Style thirtyStyle = new StyleBuilder().italic().underline().centerAligned().foreground(10, 10, 200).create();
-
- cb.insertInto(table).value("id", 1000).value(nameColumn, "pennywise the [clown]", clownStyle)
- .value("gender", "male").value("age", 30, thirtyStyle).execute();
- }
- });
-
- DataSet ds = dc.query().from(table).select(nameColumn).orderBy(nameColumn).execute();
- assertTrue(ds.next());
- assertEquals("barbara", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("bob", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("carrie", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("charlotte", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("hillary", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("michael", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("mike", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("pennywise the [clown]", ds.getRow().getValue(0).toString());
- assertEquals("font-weight: bold;color: rgb(255,0,0);background-color: rgb(0,0,255);", ds.getRow().getStyle(0).toString());
- assertTrue(ds.next());
- assertEquals("peter", ds.getRow().getValue(0).toString());
- assertTrue(ds.next());
- assertEquals("vera", ds.getRow().getValue(0).toString());
- assertFalse(ds.next());
- ds.close();
-
- ds = dc.query().from(table).select("age").where("age").eq(30).execute();
- assertTrue(ds.next());
- assertEquals("30", ds.getRow().getValue(0));
- assertEquals("font-style: italic;text-decoration: underline;text-align: center;color: rgb(0,0,255);", ds.getRow()
- .getStyle(0).toCSS());
- assertFalse(ds.next());
- }
-
- public void testCreateTable() throws Exception {
- // run the same test with both XLS and XLSX (because of different
- // workbook implementations)
- runCreateTableTest(new File("target/xls_people_created.xls"));
- runCreateTableTest(new File("target/xls_people_created.xlsx"));
- }
-
- private void runCreateTableTest(File file) {
- if (file.exists()) {
- assertTrue(file.delete());
- }
- final ExcelDataContext dc = new ExcelDataContext(file);
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Schema schema = dc.getDefaultSchema();
- Table table1 = cb.createTable(schema, "my_table_1").withColumn("foo").withColumn("bar").withColumn("baz")
- .execute();
-
- assertEquals(1, schema.getTableCount());
- assertSame(table1.getSchema(), schema);
- assertSame(table1, schema.getTables()[0]);
-
- Table table2 = cb.createTable(schema, "my_table_2").withColumn("foo").withColumn("bar").withColumn("baz")
- .execute();
-
- assertSame(table2.getSchema(), schema);
- assertSame(table2, schema.getTables()[1]);
- assertEquals(2, schema.getTableCount());
-
- cb.insertInto(table1).value("foo", 123.0).value("bar", "str 1").value("baz", true).execute();
- }
- });
-
- dc.refreshSchemas();
-
- Schema schema = dc.getDefaultSchema();
- assertEquals(2, schema.getTableCount());
- assertEquals("[my_table_1, my_table_2]", Arrays.toString(schema.getTableNames()));
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- cb.insertInto(dc.getTableByQualifiedLabel("my_table_1")).value("foo", 456.2)
- .value("bar", "парфюмерия +и косметика").value("baz", false).execute();
- }
- });
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- cb.insertInto("my_table_1").value("foo", 789).value("bar", DateUtils.get(2011, Month.JULY, 8))
- .value("baz", false).execute();
- }
- });
-
- DataSet ds = dc.query().from("my_table_1").select("foo").and("bar").and("baz").execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[123, str 1, true]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[456.2, парфюмерия +и косметика, false]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[789, 2011-07-08 00:00:00, false]]", ds.getRow().toString());
- assertFalse(ds.next());
- ds.close();
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback callback) {
- callback.deleteFrom("my_table_1").where("foo").greaterThan("124").execute();
- }
- });
-
- assertEquals("1", MetaModelHelper.executeSingleRowQuery(dc, dc.query().from("my_table_1").selectCount().toQuery())
- .getValue(0).toString());
-
- ds = dc.query().from("my_table_1").select("foo").and("bar").and("baz").execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[123, str 1, true]]", ds.getRow().toString());
- assertFalse(ds.next());
- ds.close();
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback callback) {
- callback.dropTable("my_table_1").execute();
- }
- });
-
- assertEquals("[my_table_2]", Arrays.toString(schema.getTableNames()));
-
- dc.refreshSchemas();
-
- assertEquals("[my_table_2]", Arrays.toString(schema.getTableNames()));
-
- assertEquals(1, dc.getDefaultSchema().getTableCount());
- }
-
- public void testGetStyles() throws Exception {
- DataContext dc = new ExcelDataContext(new File("src/test/resources/styles.xlsx"));
- Table table = dc.getDefaultSchema().getTables()[0];
- assertEquals("[style name, example]", Arrays.toString(table.getColumnNames()));
-
- DataSet ds = dc.query().from(table).select(table.getColumns()).execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[bold, foo]]", ds.getRow().toString());
- assertEquals("", ds.getRow().getStyle(0).toCSS());
- assertEquals("font-weight: bold;", ds.getRow().getStyle(1).toCSS());
-
- assertTrue(ds.next());
- assertEquals("Row[values=[italic, foo]]", ds.getRow().toString());
- assertEquals("", ds.getRow().getStyle(0).toCSS());
- assertEquals("font-style: italic;", ds.getRow().getStyle(1).toCSS());
-
- assertTrue(ds.next());
- assertEquals("Row[values=[underline, foo]]", ds.getRow().toString());
- assertEquals("", ds.getRow().getStyle(0).toCSS());
- assertEquals("text-decoration: underline;", ds.getRow().getStyle(1).toCSS());
-
- assertTrue(ds.next());
- assertEquals("Row[values=[custom text col, foo]]", ds.getRow().toString());
- assertEquals("", ds.getRow().getStyle(0).toCSS());
- assertEquals("color: rgb(138,67,143);", ds.getRow().getStyle(1).toCSS());
-
- assertTrue(ds.next());
- assertEquals("Row[values=[yellow text col, foo]]", ds.getRow().toString());
- assertEquals("", ds.getRow().getStyle(0).toCSS());
- assertEquals("color: rgb(255,255,0);", ds.getRow().getStyle(1).toCSS());
-
- assertTrue(ds.next());
- assertEquals("Row[values=[custom bg, foo]]", ds.getRow().toString());
- assertEquals("", ds.getRow().getStyle(0).toCSS());
- assertEquals("background-color: rgb(136,228,171);", ds.getRow().getStyle(1).toCSS());
-
- assertTrue(ds.next());
- assertEquals("Row[values=[yellow bg, foo]]", ds.getRow().toString());
- assertEquals("", ds.getRow().getStyle(0).toCSS());
- assertEquals("background-color: rgb(255,255,0);", ds.getRow().getStyle(1).toCSS());
-
- assertTrue(ds.next());
- assertEquals("Row[values=[center align, foo]]", ds.getRow().toString());
- assertEquals("", ds.getRow().getStyle(0).toCSS());
- assertEquals("text-align: center;", ds.getRow().getStyle(1).toCSS());
-
- assertTrue(ds.next());
- assertEquals("Row[values=[font size 8, foo]]", ds.getRow().toString());
- assertEquals("", ds.getRow().getStyle(0).toCSS());
- assertEquals("font-size: 8pt;", ds.getRow().getStyle(1).toCSS());
-
- assertTrue(ds.next());
- assertEquals("Row[values=[font size 16, foo]]", ds.getRow().toString());
- assertEquals("", ds.getRow().getStyle(0).toCSS());
- assertEquals("font-size: 16pt;", ds.getRow().getStyle(1).toCSS());
-
- assertFalse(ds.next());
- }
-}
\ No newline at end of file