/**
 * 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.apache.metamodel.excel;

import java.io.File;
import java.util.Arrays;
import java.util.List;

import junit.framework.TestCase;

import org.apache.metamodel.DataContext;
import org.apache.metamodel.MetaModelHelper;
import org.apache.metamodel.UpdateCallback;
import org.apache.metamodel.UpdateScript;
import org.apache.metamodel.data.DataSet;
import org.apache.metamodel.data.Row;
import org.apache.metamodel.data.Style;
import org.apache.metamodel.data.StyleBuilder;
import org.apache.metamodel.query.Query;
import org.apache.metamodel.schema.Column;
import org.apache.metamodel.schema.Schema;
import org.apache.metamodel.schema.Table;
import org.apache.metamodel.util.DateUtils;
import org.apache.metamodel.util.FileHelper;
import org.apache.metamodel.util.Month;

public class ExcelDataContextTest extends TestCase {

    /**
     * Creates a copy of a particular file - to avoid changing of Excel files
     * under source control
     * 
     * @param path
     * @return
     */
    private File copyOf(String path) {
        final File srcFile = new File(path);
        final File destFile = new File("target/" + getName() + "-" + srcFile.getName());
        FileHelper.copy(srcFile, destFile);
        return destFile;
    }

    public void testErrornousConstructors() throws Exception {
        try {
            new ExcelDataContext(null);
            fail("Exception expected");
        } catch (IllegalArgumentException e) {
            assertEquals("File cannot be null", e.getMessage());
        }

        File file = copyOf("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 = copyOf("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(copyOf("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(copyOf("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(copyOf("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(copyOf("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(copyOf("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(copyOf("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(copyOf("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(copyOf("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(copyOf("src/test/resources/Spreadsheet2007.xlsx"));
        Schema schema = dc.getDefaultSchema();
        assertEquals("Schema[name=testOpenXlsxFormat-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 = copyOf("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 testConfigurationWithoutHeader-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 = copyOf("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 testConfigurationNonDefaultColumnNameLineNumber-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 = copyOf("src/test/resources/xls_people.xls");
        DataContext dc = new ExcelDataContext(file);
        Schema[] schemas = dc.getSchemas();
        assertEquals(2, schemas.length);
        Schema schema = schemas[1];
        assertEquals("testGetSchemas-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 = copyOf("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 = copyOf("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 = copyOf("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 = copyOf("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 = copyOf("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 {
        final File file = copyOf("src/test/resources/xls_people.xls");

        final 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 testCreateTableXls() throws Exception {
        // run the same test with both XLS and XLSX (because of different
        // workbook implementations)
        runCreateTableTest(new File("target/xls_people_created.xls"));
    }

    public void testCreateTableXlsx() throws Exception {
        // run the same test with both XLS and XLSX (because of different
        // workbook implementations)
        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(dc.getDefaultSchema().getTableNames()));
        assertEquals(1, dc.getDefaultSchema().getTableCount());
    }

    public void testGetStyles() throws Exception {
        DataContext dc = new ExcelDataContext(copyOf("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());
    }

    /**
     * Tests that you can execute a query on a ExcelDataContext even though the
     * schema has not yet been (explicitly) loaded.
     */
    public void testExecuteQueryBeforeLoadingSchema() throws Exception {
        // first use one DataContext to retreive the schema/table/column objects
        ExcelDataContext dc1 = new ExcelDataContext(copyOf("src/test/resources/Spreadsheet2007.xlsx"));
        Schema schema = dc1.getDefaultSchema();
        Table table = schema.getTable(0);
        Column column = table.getColumn(0);

        // query another DataContext using the schemas of the one above
        ExcelDataContext dc2 = new ExcelDataContext(copyOf("src/test/resources/Spreadsheet2007.xlsx"));
        DataSet ds = dc2.executeQuery(new Query().from(table).select(column));

        // previously we would crash at this point!

        assertNotNull(ds);
        ds.close();
    }
}