Example usage for org.apache.poi.ss.usermodel Cell getRow

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getRow.

Prototype

Row getRow();

Source Link

Document

Returns the Row this cell belongs to

Usage

From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaExecuterTest.java

License:Open Source License

@Test
@SuppressWarnings("unchecked")
public final void testSheetToJavaExecuter() throws ParseException, java.text.ParseException {

    Workbook workbook = getWorkbook();//from www  .  ja v a2 s.c  om
    Sheet sheet = workbook.getSheetAt(0);
    SheetToJavaExecuter executer = new SheetToJavaExecuter();
    SheetData sheetData = new SheetData("SheetToJava");

    List<SheetToJavaParseInfo> sheet2JavaData = new ArrayList<SheetToJavaParseInfo>();
    List<SheetToJavaSettingInfo> sheet2JavaSettingData = new ArrayList<SheetToJavaSettingInfo>();

    // ===============================================
    // postParse( Sheet sheet, SheetParser sheetParser, SheetData sheetData)
    // ===============================================
    String sheetName = "testSheet";
    String tagName = "@SheetToJava";
    String settingTagName = tagName + "Setting";

    // SheetToJavaParseInfo
    SheetToJavaParseInfo parseInfo1 = new SheetToJavaParseInfo();
    parseInfo1.setSheetName(sheetName);
    parseInfo1.setLogicalNameRowNum(1);
    parseInfo1.setValueRowNum(2);
    parseInfo1.setSettingTagName(settingTagName);
    sheet2JavaData.add(parseInfo1);

    // SheetToJavaSettingInfo
    SheetToJavaSettingInfo settingInfo1 = new SheetToJavaSettingInfo();
    settingInfo1.setClazz(TestEntity1.class);
    settingInfo1.setPropertyName("propertyStr1");
    settingInfo1.setValue("@LNAME()");
    settingInfo1.setSheetName(sheetName);
    SheetToJavaSettingInfo settingInfo2 = new SheetToJavaSettingInfo();
    settingInfo2.setClazz(TestEntity1.class);
    settingInfo2.setPropertyName("propertyInt1");
    settingInfo2.setValue(10);
    settingInfo2.setSheetName(sheetName);
    sheet2JavaSettingData.add(settingInfo1);
    sheet2JavaSettingData.add(settingInfo2);

    // ???
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    // 
    SheetParser sheetParser = new SheetParser();
    sheetParser.addTagParser(new SheetToJavaParser());
    sheetParser.addTagParser(new SheetToJavaSettingParser());

    // No.1 postParse
    executer.postParse(sheet, sheetParser, sheetData);
    List<Object> results = (List<Object>) sheetData.get(tagName);
    assertEquals(3, results.size());
    assertEquals("String1", ((TestEntity1) results.get(0)).getPropertyStr1());
    assertEquals("String2", ((TestEntity1) results.get(1)).getPropertyStr1());
    assertEquals("String3", ((TestEntity1) results.get(2)).getPropertyStr1());
    assertEquals(new Integer(10), ((TestEntity1) results.get(0)).getPropertyInt1());
    assertEquals(new Integer(10), ((TestEntity1) results.get(1)).getPropertyInt1());
    assertEquals(new Integer(10), ((TestEntity1) results.get(2)).getPropertyInt1());

    // No.2 SheetToJavaSettingInfo????????
    assertNull(sheetData.get(settingTagName));

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    // No.3 ?null
    try {
        executer.postParse(null, sheetParser, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.4 ?null
    try {
        executer.postParse(sheet, null, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.5 ?null
    try {
        executer.postParse(sheet, sheetParser, null);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.6 ???
    sheetParser.addTagParser(new SheetToJavaParser("@UnusedSheetToJava")); /* ??? */
    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<Object>) sheetData.get(tagName);
    assertEquals(3, results.size());
    assertEquals("String1", ((TestEntity1) results.get(0)).getPropertyStr1());
    assertEquals("String2", ((TestEntity1) results.get(1)).getPropertyStr1());
    assertEquals("String3", ((TestEntity1) results.get(2)).getPropertyStr1());
    assertEquals(new Integer(10), ((TestEntity1) results.get(0)).getPropertyInt1());
    assertEquals(new Integer(10), ((TestEntity1) results.get(1)).getPropertyInt1());
    assertEquals(new Integer(10), ((TestEntity1) results.get(2)).getPropertyInt1());

    // No.7 ?????
    SheetToJavaParseInfo parseInfo2 = new SheetToJavaParseInfo();
    parseInfo2.setSheetName("nonExistentSheet"); /* ????? */
    parseInfo2.setLogicalNameRowNum(1);
    parseInfo2.setValueRowNum(2);
    parseInfo2.setSettingTagName(settingTagName);
    sheet2JavaData.add(parseInfo2);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (ParseException pe) {
        // ?
        System.out.println("No.7:" + pe);
    }

    // No.8 ????null
    String sheetName2 = "testSheet (2)";
    SheetToJavaParseInfo parseInfo3 = new SheetToJavaParseInfo();
    parseInfo3.setSheetName(sheetName2);
    parseInfo3.setLogicalNameRowNum(1); /* null */
    parseInfo3.setValueRowNum(2);
    parseInfo3.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo3);

    SheetToJavaSettingInfo settingInfo3 = new SheetToJavaSettingInfo();
    settingInfo3.setClazz(TestEntity1.class);
    settingInfo3.setPropertyName("propertyInt1");
    settingInfo3.setValue("@LNAME()");
    settingInfo3.setSheetName(sheetName2);
    sheet2JavaSettingData.clear();
    sheet2JavaSettingData.add(settingInfo3);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (ParseException pe) {
        // ?
        System.out.println("No.8:" + pe);
    }

    // No.9 ?null
    SheetToJavaParseInfo parseInfo4 = new SheetToJavaParseInfo();
    parseInfo4.setSheetName(sheetName2);
    parseInfo4.setLogicalNameRowNum(2);
    parseInfo4.setValueRowNum(4); /* null */
    parseInfo4.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo4);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<Object>) sheetData.get(tagName);
    assertEquals(1, results.size());
    assertEquals(new Integer(20), ((TestEntity1) results.get(0)).getPropertyInt1());

    // No.10 ?null?
    SheetToJavaParseInfo parseInfo5 = new SheetToJavaParseInfo();
    parseInfo5.setSheetName(sheetName2);
    parseInfo5.setLogicalNameRowNum(2);
    parseInfo5.setValueRowNum(3);
    parseInfo5.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo5);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<Object>) sheetData.get(tagName);
    assertEquals(2, results.size());
    assertEquals(new Integer(10), ((TestEntity1) results.get(0)).getPropertyInt1());
    assertEquals(new Integer(20), ((TestEntity1) results.get(1)).getPropertyInt1());

    // No.11 ?null?
    String sheetName3 = "testSheet (3)";
    SheetToJavaParseInfo parseInfo6 = new SheetToJavaParseInfo();
    parseInfo6.setSheetName(sheetName3);
    parseInfo6.setLogicalNameRowNum(1);
    parseInfo6.setValueRowNum(2);
    parseInfo6.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo6);

    SheetToJavaSettingInfo settingInfo4 = new SheetToJavaSettingInfo();
    settingInfo4.setClazz(TestEntity1.class);
    settingInfo4.setPropertyName("propertyDate1");
    settingInfo4.setValue("@LNAME()");
    settingInfo4.setSheetName(sheetName3);
    sheet2JavaSettingData.clear();
    sheet2JavaSettingData.add(settingInfo4);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        results.clear();
        results = (List<Object>) sheetData.get(tagName);
        assertEquals(3, results.size());
        assertEquals(DateFormat.getDateInstance().parse("2009/1/1"),
                ((TestEntity1) results.get(0)).getPropertyDate1());
        assertNull(((TestEntity1) results.get(1)).getPropertyDate1());
        assertEquals(DateFormat.getDateInstance().parse("2009/2/1"),
                ((TestEntity1) results.get(2)).getPropertyDate1());
    } catch (RuntimeException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }

    // No.12 ????
    String sheetName4 = "testSheet (4)";
    SheetToJavaParseInfo parseInfo7 = new SheetToJavaParseInfo();
    parseInfo7.setSheetName(sheetName4);
    parseInfo7.setLogicalNameRowNum(1);
    parseInfo7.setValueRowNum(2);
    parseInfo7.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo7);

    SheetToJavaSettingInfo settingInfo5 = new SheetToJavaSettingInfo();
    settingInfo5.setClazz(TestEntity1.class);
    settingInfo5.setPropertyName("propertyDate1");
    settingInfo5.setValue("@LNAME(2009/1/1)");
    settingInfo5.setSheetName(sheetName4);
    sheet2JavaSettingData.clear();
    sheet2JavaSettingData.add(settingInfo5);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(0, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.12:" + pe);
    }

    // No.13 ???null
    String sheetName5 = "testSheet (5)";
    SheetToJavaParseInfo parseInfo8 = new SheetToJavaParseInfo();
    parseInfo8.setSheetName(sheetName5);
    // parseInfo8.setLogicalNameRowNum( 1);
    parseInfo8.setValueRowNum(2);
    parseInfo8.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo8);

    SheetToJavaSettingInfo settingInfo6 = new SheetToJavaSettingInfo();
    settingInfo6.setClazz(TestEntity1.class);
    settingInfo6.setPropertyName("propertyStr1");
    settingInfo6.setValue("@LNAME()");
    settingInfo6.setSheetName(sheetName5);
    sheet2JavaSettingData.clear();
    sheet2JavaSettingData.add(settingInfo6);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.14 ???No?null
    SheetToJavaParseInfo parseInfo9 = new SheetToJavaParseInfo();
    parseInfo9.setSheetName(sheetName5);
    // parseInfo9.setLogicalNameRowNum( 1);
    parseInfo9.setValueRowNum(2);
    parseInfo9.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo9);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.15 No?null
    SheetToJavaParseInfo parseInfo10 = new SheetToJavaParseInfo();
    parseInfo10.setSheetName(sheetName5);
    parseInfo10.setLogicalNameRowNum(1);
    // parseInfo10.setValueRowNum( 2);
    parseInfo10.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo10);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.16 Setting???null
    SheetToJavaParseInfo parseInfo11 = new SheetToJavaParseInfo();
    parseInfo11.setSheetName(sheetName5);
    parseInfo11.setLogicalNameRowNum(1);
    parseInfo11.setValueRowNum(2);
    // parseInfo11.setSettingTagName( settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo11);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.17 SheetToJavaParseInfo??????
    sheet2JavaData.clear();

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<Object>) sheetData.get(tagName);
    assertEquals(0, results.size());

    // No.18 SheetToJavaSettingInfo??????
    SheetToJavaParseInfo parseInfo12 = new SheetToJavaParseInfo();
    parseInfo12.setSheetName(sheetName5);
    parseInfo12.setLogicalNameRowNum(1);
    parseInfo12.setValueRowNum(2);
    parseInfo12.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo12);

    sheet2JavaSettingData.clear();

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<Object>) sheetData.get(tagName);
    assertEquals(0, results.size());

    // No.19 ???
    String sheetName6 = "testSheet (6)";
    SheetToJavaParseInfo parseInfo13 = new SheetToJavaParseInfo();
    parseInfo13.setSheetName(sheetName6);
    parseInfo13.setLogicalNameRowNum(1);
    parseInfo13.setValueRowNum(2);
    parseInfo13.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo13);

    SheetToJavaSettingInfo settingInfo7 = new SheetToJavaSettingInfo();
    settingInfo7.setClazz(TestEntity1.class);
    settingInfo7.setPropertyName("propertyStr1");
    settingInfo7.setValue("@LNAME()");
    settingInfo7.setSheetName(sheetName6);
    settingInfo7.setUnique(true); /* ??? */
    SheetToJavaSettingInfo settingInfo8 = new SheetToJavaSettingInfo();
    settingInfo8.setClazz(TestEntity1.class);
    settingInfo8.setPropertyName("propertyInt1");
    settingInfo8.setValue("@LNAME()");
    settingInfo8.setUnique(true); /* ??? */
    settingInfo8.setSheetName(sheetName6);
    SheetToJavaSettingInfo settingInfo9 = new SheetToJavaSettingInfo();
    settingInfo9.setClazz(TestEntity1.class);
    settingInfo9.setPropertyName("propertyDate1");
    settingInfo9.setValue("@LNAME()");
    settingInfo9.setUnique(false); /* ?? */
    settingInfo9.setSheetName(sheetName6);

    sheet2JavaSettingData.clear();
    sheet2JavaSettingData.add(settingInfo7);
    sheet2JavaSettingData.add(settingInfo8);
    sheet2JavaSettingData.add(settingInfo9);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<Object>) sheetData.get(tagName);
    assertEquals(3, results.size());
    assertEquals("String1", ((TestEntity1) results.get(0)).getPropertyStr1());
    assertEquals("String2", ((TestEntity1) results.get(1)).getPropertyStr1());
    assertEquals("String3", ((TestEntity1) results.get(2)).getPropertyStr1());
    assertEquals(new Integer(100), ((TestEntity1) results.get(0)).getPropertyInt1());
    assertNull(((TestEntity1) results.get(1)).getPropertyInt1());
    assertEquals(new Integer(300), ((TestEntity1) results.get(2)).getPropertyInt1());
    assertEquals(DateFormat.getDateInstance().parse("2009/1/1"),
            ((TestEntity1) results.get(0)).getPropertyDate1());
    assertEquals(DateFormat.getDateInstance().parse("2009/3/1"),
            ((TestEntity1) results.get(1)).getPropertyDate1());
    assertEquals(DateFormat.getDateInstance().parse("2009/4/1"),
            ((TestEntity1) results.get(2)).getPropertyDate1());

    // No.20 
    // TestEntity2??
    SheetToJavaSettingInfo settingInfo10 = new SheetToJavaSettingInfo();
    settingInfo10.setClazz(TestEntity2.class);
    settingInfo10.setPropertyName("propertyStr2");
    settingInfo10.setValue("@LNAME()");
    settingInfo10.setSheetName(sheetName6);
    settingInfo10.setUnique(true); /* ??? */
    SheetToJavaSettingInfo settingInfo11 = new SheetToJavaSettingInfo();
    settingInfo11.setClazz(TestEntity2.class);
    settingInfo11.setPropertyName("propertyInt2");
    settingInfo11.setValue("@LNAME()");
    settingInfo11.setUnique(false); /* ?? */
    settingInfo11.setSheetName(sheetName6);
    SheetToJavaSettingInfo settingInfo12 = new SheetToJavaSettingInfo();
    settingInfo12.setClazz(TestEntity2.class);
    settingInfo12.setPropertyName("propertyDate2");
    settingInfo12.setValue("@LNAME()");
    settingInfo12.setUnique(true); /* ??? */
    settingInfo12.setSheetName(sheetName6);

    // settingInfo10, 11, 12
    sheet2JavaSettingData.add(settingInfo10);
    sheet2JavaSettingData.add(settingInfo11);
    sheet2JavaSettingData.add(settingInfo12);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<Object>) sheetData.get(tagName);
    assertEquals(7, results.size());
    assertEquals("String1", ((TestEntity1) results.get(0)).getPropertyStr1());
    assertEquals("String2", ((TestEntity1) results.get(1)).getPropertyStr1());
    assertEquals("String3", ((TestEntity1) results.get(2)).getPropertyStr1());
    assertEquals("String1", ((TestEntity2) results.get(3)).getPropertyStr2());
    assertEquals("String1", ((TestEntity2) results.get(4)).getPropertyStr2());
    assertEquals("String2", ((TestEntity2) results.get(5)).getPropertyStr2());
    assertEquals("String3", ((TestEntity2) results.get(6)).getPropertyStr2());
    assertEquals(new Integer(100), ((TestEntity1) results.get(0)).getPropertyInt1());
    assertNull(((TestEntity1) results.get(1)).getPropertyInt1());
    assertEquals(new Integer(300), ((TestEntity1) results.get(2)).getPropertyInt1());
    assertEquals(new Integer(100), ((TestEntity2) results.get(3)).getPropertyInt2());
    assertEquals(new Integer(100), ((TestEntity2) results.get(4)).getPropertyInt2());
    assertNull(((TestEntity2) results.get(5)).getPropertyInt2());
    assertEquals(new Integer(300), ((TestEntity2) results.get(6)).getPropertyInt2());
    assertEquals(DateFormat.getDateInstance().parse("2009/1/1"),
            ((TestEntity1) results.get(0)).getPropertyDate1());
    assertEquals(DateFormat.getDateInstance().parse("2009/3/1"),
            ((TestEntity1) results.get(1)).getPropertyDate1());
    assertEquals(DateFormat.getDateInstance().parse("2009/4/1"),
            ((TestEntity1) results.get(2)).getPropertyDate1());
    assertEquals(DateFormat.getDateInstance().parse("2009/1/1"),
            ((TestEntity2) results.get(3)).getPropertyDate2());
    assertEquals(DateFormat.getDateInstance().parse("2009/2/1"),
            ((TestEntity2) results.get(4)).getPropertyDate2());
    assertEquals(DateFormat.getDateInstance().parse("2009/3/1"),
            ((TestEntity2) results.get(5)).getPropertyDate2());
    assertEquals(DateFormat.getDateInstance().parse("2009/4/1"),
            ((TestEntity2) results.get(6)).getPropertyDate2());

    // ===============================================
    // addPropertyParser( SheetToJavaPropertyParser parser)
    // ===============================================
    // No.21 
    executer.addPropertyParser(new TestChildEntityParser());
    String sheetName7 = "testSheet (7)";
    SheetToJavaParseInfo parseInfo14 = new SheetToJavaParseInfo();
    parseInfo14.setSheetName(sheetName7);
    parseInfo14.setLogicalNameRowNum(1);
    parseInfo14.setValueRowNum(2);
    parseInfo14.setSettingTagName(settingTagName);
    sheet2JavaData.clear();
    sheet2JavaData.add(parseInfo14);

    SheetToJavaSettingInfo settingInfo13 = new SheetToJavaSettingInfo();
    settingInfo13.setClazz(TestEntity1.class);
    settingInfo13.setPropertyName("propertyStr1");
    settingInfo13.setValue("@LNAME()");
    settingInfo13.setSheetName(sheetName7);
    settingInfo13.setUnique(true); /* ??? */
    SheetToJavaSettingInfo settingInfo14 = new SheetToJavaSettingInfo();
    settingInfo14.setClazz(TestEntity1.class);
    settingInfo14.setPropertyName("child");
    settingInfo14.setValue("@TestChildEntity{childPropertyStr1=?1}");
    settingInfo14.setSheetName(sheetName7);

    sheet2JavaSettingData.clear();
    sheet2JavaSettingData.add(settingInfo13);
    sheet2JavaSettingData.add(settingInfo14);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<Object>) sheetData.get(tagName);
    assertEquals(3, results.size());
    assertEquals("String1", ((TestEntity1) results.get(0)).getPropertyStr1());
    assertEquals("String2", ((TestEntity1) results.get(1)).getPropertyStr1());
    assertEquals("String3", ((TestEntity1) results.get(2)).getPropertyStr1());
    assertEquals("?1", ((TestEntity1) results.get(0)).getChild().getChildPropertyStr1());
    assertEquals("?1", ((TestEntity1) results.get(1)).getChild().getChildPropertyStr1());
    assertEquals("?1", ((TestEntity1) results.get(2)).getChild().getChildPropertyStr1());

    // No.22 ????
    SheetToJavaSettingInfo settingInfo15 = new SheetToJavaSettingInfo();
    settingInfo15.setClazz(TestEntity1.class);
    settingInfo15.setPropertyName("child");
    settingInfo15.setValue("@TestChildEntity{childPropertyStr1=@LNAME(?)}");
    settingInfo15.setSheetName(sheetName7);
    sheet2JavaSettingData.clear();
    sheet2JavaSettingData.add(settingInfo13);
    sheet2JavaSettingData.add(settingInfo15);

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<Object>) sheetData.get(tagName);
    assertEquals(3, results.size());
    assertEquals("String1", ((TestEntity1) results.get(0)).getPropertyStr1());
    assertEquals("String2", ((TestEntity1) results.get(1)).getPropertyStr1());
    assertEquals("String3", ((TestEntity1) results.get(2)).getPropertyStr1());
    assertEquals("ChildString1", ((TestEntity1) results.get(0)).getChild().getChildPropertyStr1());
    assertEquals("ChildString3", ((TestEntity1) results.get(1)).getChild().getChildPropertyStr1());
    assertNull(((TestEntity1) results.get(2)).getChild().getChildPropertyStr1());

    // ===============================================
    // clearPropertyParsers()
    // ===============================================
    // No.23 
    executer.clearPropertyParsers();

    // ?
    sheetData = new SheetData("SheetToJava");
    sheetData.put(tagName, sheet2JavaData);
    sheetData.put(settingTagName, sheet2JavaSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<Object>) sheetData.get(tagName);
    assertEquals(3, results.size());
    assertEquals("String1", ((TestEntity1) results.get(0)).getPropertyStr1());
    assertEquals("String2", ((TestEntity1) results.get(1)).getPropertyStr1());
    assertEquals("String3", ((TestEntity1) results.get(2)).getPropertyStr1());
    assertNull(((TestEntity1) results.get(0)).getChild());
    assertNull(((TestEntity1) results.get(1)).getChild());
    assertNull(((TestEntity1) results.get(2)).getChild());
}

---

From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaParserTest.java

License:Open Source License

@Test
public final void testSheetToJavaParser() throws ParseException {
    Workbook workbook = getWorkbook();// w  w w.  j a v a  2 s .co  m
    Sheet sheet1 = workbook.getSheetAt(0);
    Sheet sheet2 = workbook.getSheetAt(1);
    Sheet sheet3 = workbook.getSheetAt(2);
    Sheet sheet4 = workbook.getSheetAt(3);
    Sheet sheet5 = workbook.getSheetAt(4);
    String tag = "@SheetToJava";
    SheetToJavaParser parser = new SheetToJavaParser(tag);
    Cell tagCell = null;
    Object data = null;
    List<SheetToJavaParseInfo> list = null;

    // ===============================================
    // parse( Sheet sheet, Cell tagCell, Object data)
    // ===============================================
    // No.1 ?
    tagCell = sheet1.getRow(0).getCell(0);
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals(tag + "Setting", list.get(0).getSettingTagName());
    assertEquals(tag + "Setting", list.get(1).getSettingTagName());
    assertEquals("sheetName2", list.get(0).getSheetName());
    assertEquals("sheetName3", list.get(1).getSheetName());
    assertEquals(new Integer(2), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(3), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(5), list.get(0).getValueRowNum());
    assertEquals(new Integer(6), list.get(1).getValueRowNum());

    // No.2 
    tagCell = sheet1.getRow(0).getCell(4);
    list.clear();
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("@SettingTagName", list.get(0).getSettingTagName());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(new Integer(7), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(8), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(10), list.get(0).getValueRowNum());
    assertEquals(new Integer(11), list.get(1).getValueRowNum());

    // No.3 ???null
    tagCell = sheet2.getRow(0).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("@SheetToJavaSetting", list.get(0).getSettingTagName());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(new Integer(2), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(3), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(5), list.get(0).getValueRowNum());
    assertEquals(new Integer(6), list.get(1).getValueRowNum());

    // No.4 ???No?null
    tagCell = sheet2.getRow(8).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(11, cell.getRow().getRowNum());
        assertEquals(1, cell.getColumnIndex());
        System.out.println("No.4:" + pe);
    }

    // No.5 No?null
    tagCell = sheet2.getRow(16).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(20, cell.getRow().getRowNum());
        assertEquals(2, cell.getColumnIndex());
        System.out.println("No.5:" + pe);
    }

    // No.6 ?null
    tagCell = sheet2.getRow(24).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(new Integer(17), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(18), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(19), list.get(0).getValueRowNum());
    assertEquals(new Integer(20), list.get(1).getValueRowNum());

    // No.7 ????null?
    tagCell = sheet2.getRow(32).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(new Integer(21), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(22), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(23), list.get(0).getValueRowNum());
    assertEquals(new Integer(24), list.get(1).getValueRowNum());

    // No.8 ???No?
    tagCell = sheet2.getRow(40).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(42, cell.getRow().getRowNum());
        assertEquals(1, cell.getColumnIndex());
        System.out.println("No.8:" + pe);
    }

    // No.9 No?
    tagCell = sheet2.getRow(46).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(48, cell.getRow().getRowNum());
        assertEquals(2, cell.getColumnIndex());
        System.out.println("No.9:" + pe);
    }

    // No.10 
    tagCell = sheet3.getRow(5).getCell(0);
    list.clear();
    list = parser.parse(sheet3, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("sheetName3", list.get(2).getSheetName());
    assertEquals(new Integer(1), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(2), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(3), list.get(2).getLogicalNameRowNum());
    assertEquals(new Integer(4), list.get(0).getValueRowNum());
    assertEquals(new Integer(5), list.get(1).getValueRowNum());
    assertEquals(new Integer(6), list.get(2).getValueRowNum());

    // No.11 DataRowFrom > DataRowTo
    tagCell = sheet3.getRow(9).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(9, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.11:" + pe);
    }

    // No.12 DataRowFrom??
    tagCell = sheet3.getRow(17).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(17, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.12:" + pe);
    }
    // No.13 DataRowTo??
    tagCell = sheet3.getRow(21).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(21, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.13:" + pe);
    }

    // No.14 SettingTagName??
    tagCell = sheet3.getRow(25).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(25, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.14:" + pe);
    }

    // No.15 ResultKey
    tagCell = sheet3.getRow(29).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(29, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.15:" + pe);
    }

    // No.16 DataRowFrom?1??
    tagCell = sheet4.getRow(0).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(0, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.16:" + pe);
    }

    // No.17 DataRowTo?1??
    tagCell = sheet4.getRow(0).getCell(4);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(0, cell.getRow().getRowNum());
        assertEquals(4, cell.getColumnIndex());
        System.out.println("No.17:" + pe);
    }

    // No.18 DataRowFrom???
    tagCell = sheet4.getRow(15).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(15, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.18:" + pe);
    }

    // No.19 DataRowTo???
    tagCell = sheet4.getRow(15).getCell(4);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(15, cell.getRow().getRowNum());
        assertEquals(4, cell.getColumnIndex());
        System.out.println("No.19:" + pe);
    }

    // No.20 
    parser = new SheetToJavaParser();
    tagCell = sheet1.getRow(0).getCell(0);
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals(tag + "Setting", list.get(0).getSettingTagName());
    assertEquals(tag + "Setting", list.get(1).getSettingTagName());
    assertEquals("sheetName2", list.get(0).getSheetName());
    assertEquals("sheetName3", list.get(1).getSheetName());
    assertEquals(new Integer(2), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(3), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(5), list.get(0).getValueRowNum());
    assertEquals(new Integer(6), list.get(1).getValueRowNum());

    // No.21 ?????
    tagCell = sheet5.getRow(0).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet5, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(4, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.21:" + pe);
    }

    // No.22 ???
    tagCell = sheet5.getRow(10).getCell(0);
    list.clear();
    list = parser.parse(sheet5, tagCell, data);
    assertEquals(2, list.size());
    assertEquals(tag + "Setting", list.get(0).getSettingTagName());
    assertEquals(tag + "Setting", list.get(1).getSettingTagName());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(new Integer(4), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(5), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(7), list.get(0).getValueRowNum());
    assertEquals(new Integer(8), list.get(1).getValueRowNum());
}

---

From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaSettingParserTest.java

License:Open Source License

@Test
public final void testSheetToJavaSettingParser() throws ParseException {
    Workbook workbook = getWorkbook();//from   www .  j  a v  a2  s  . c o m
    Sheet sheet1 = workbook.getSheetAt(0);
    Sheet sheet2 = workbook.getSheetAt(1);
    Sheet sheet3 = workbook.getSheetAt(2);
    Sheet sheet4 = workbook.getSheetAt(3);
    Sheet sheet5 = workbook.getSheetAt(4);
    String tag = "@SheetToJavaSetting";
    SheetToJavaSettingParser parser = new SheetToJavaSettingParser(tag);
    Cell tagCell = null;
    Object data = null;
    List<SheetToJavaSettingInfo> list = null;

    // ===============================================
    // parse( Sheet sheet, Cell tagCell, Object data)
    // ===============================================
    // No.1 ?
    tagCell = sheet1.getRow(0).getCell(0);
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName2", list.get(0).getSheetName());
    assertEquals("sheetName3", list.get(1).getSheetName());
    assertEquals(2, list.get(0).getValue());
    assertEquals("value3", list.get(1).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals(TestEntity2.class, list.get(1).getClazz());
    assertEquals("propertyInt1", list.get(0).getPropertyName());
    assertEquals("propertyStr2", list.get(1).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());

    // No.2 
    tagCell = sheet1.getRow(0).getCell(6);
    list.clear();
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value4", list.get(0).getValue());
    assertEquals(5, list.get(1).getValue());
    assertEquals(TestEntity2.class, list.get(0).getClazz());
    assertEquals(TestEntity2.class, list.get(1).getClazz());
    assertEquals("propertyStr2", list.get(0).getPropertyName());
    assertEquals("propertyInt2", list.get(1).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertFalse(list.get(1).isUnique());

    // No.3 ???null
    tagCell = sheet2.getRow(0).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(2, list.get(0).getValue());
    assertEquals("value3", list.get(1).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals(TestEntity2.class, list.get(1).getClazz());
    assertEquals("propertyInt1", list.get(0).getPropertyName());
    assertEquals("propertyStr2", list.get(1).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());

    // No.4 ?null
    tagCell = sheet2.getRow(8).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("sheetName3", list.get(2).getSheetName());
    assertEquals("value4", list.get(0).getValue());
    assertNull(list.get(1).getValue());
    assertEquals("value5", list.get(2).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals(TestEntity1.class, list.get(1).getClazz());
    assertEquals(TestEntity2.class, list.get(2).getClazz());
    assertEquals("propertyStr1", list.get(0).getPropertyName());
    assertEquals("propertyInt1", list.get(1).getPropertyName());
    assertEquals("propertyStr2", list.get(2).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());
    assertTrue(list.get(2).isUnique());

    // No.5 ?null
    tagCell = sheet2.getRow(16).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(20, cell.getRow().getRowNum());
        assertEquals(2, cell.getColumnIndex());
        System.out.println("No.5:" + pe);
    }

    // No.6 ?null
    tagCell = sheet2.getRow(24).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(26, cell.getRow().getRowNum());
        assertEquals(3, cell.getColumnIndex());
        System.out.println("No.6:" + pe);
    }

    // No.7 ????null
    tagCell = sheet2.getRow(32).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("sheetName3", list.get(2).getSheetName());
    assertEquals("value12", list.get(0).getValue());
    assertEquals(13, list.get(1).getValue());
    assertEquals("value14", list.get(2).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals(TestEntity1.class, list.get(1).getClazz());
    assertEquals(TestEntity2.class, list.get(2).getClazz());
    assertEquals("propertyStr1", list.get(0).getPropertyName());
    assertEquals("propertyInt1", list.get(1).getPropertyName());
    assertEquals("propertyStr2", list.get(2).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertFalse(list.get(1).isUnique());
    assertTrue(list.get(2).isUnique());

    // No.8 ?null
    tagCell = sheet2.getRow(40).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value15", list.get(0).getValue());
    assertEquals("value16", list.get(1).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals(TestEntity2.class, list.get(1).getClazz());
    assertEquals("propertyStr1", list.get(0).getPropertyName());
    assertEquals("propertyStr2", list.get(1).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertFalse(list.get(1).isUnique());

    // No.9 ????null?
    tagCell = sheet2.getRow(48).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value17", list.get(0).getValue());
    assertEquals("value18", list.get(1).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals(TestEntity2.class, list.get(1).getClazz());
    assertEquals("propertyStr1", list.get(0).getPropertyName());
    assertEquals("propertyStr2", list.get(1).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertFalse(list.get(1).isUnique());

    // No.10 ????
    tagCell = sheet2.getRow(56).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(1, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("@LNAME(1)", list.get(0).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals("propertyStr1", list.get(0).getPropertyName());
    assertFalse(list.get(0).isUnique());

    // No.11 ??????null
    tagCell = sheet2.getRow(62).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(64, cell.getRow().getRowNum());
        assertEquals(3, cell.getColumnIndex());
        System.out.println("No.11:" + pe);
    }

    // No.12 ?
    tagCell = sheet2.getRow(68).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(1, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals(
            "@TestChildEntity{childPropertyStr1=@LNAME(?1),childPropertyInt1=@LNAME(?1)}",
            list.get(0).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertNull(list.get(0).getPropertyName());
    assertFalse(list.get(0).isUnique());

    // No.13 ???null
    tagCell = sheet2.getRow(74).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(1, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals(
            "@TestChildEntity{childPropertyStr1=@LNAME(?1),childPropertyInt1=@LNAME(?1)}",
            list.get(0).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertNull(list.get(0).getPropertyName());
    assertFalse(list.get(0).isUnique());

    // No.14 ??????
    tagCell = sheet2.getRow(80).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(1, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals(
            "@TestChildEntity{childPropertyStr1=@LNAME(?1),childPropertyInt1=@LNAME(?1)}",
            list.get(0).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertNull(list.get(0).getPropertyName());
    assertFalse(list.get(0).isUnique());

    // No.15 ????
    tagCell = sheet2.getRow(86).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(88, cell.getRow().getRowNum());
        assertEquals(1, cell.getColumnIndex());
    }

    // No.16 ?
    tagCell = sheet2.getRow(92).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(1, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("10", list.get(0).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals("propertyStr1", list.get(0).getPropertyName());
    assertFalse(list.get(0).isUnique());

    // No.17 ?
    tagCell = sheet2.getRow(98).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(100, cell.getRow().getRowNum());
        assertEquals(1, cell.getColumnIndex());
        System.out.println("No.17:" + pe);
    }

    // No.18 ?
    tagCell = sheet2.getRow(104).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(106, cell.getRow().getRowNum());
        assertEquals(1, cell.getColumnIndex());
        System.out.println("No.18:" + pe);
    }

    // No.19 ???
    tagCell = sheet2.getRow(110).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(112, cell.getRow().getRowNum());
        assertEquals(2, cell.getColumnIndex());
        System.out.println("No.19:" + pe);
    }

    // No.20 ???
    tagCell = sheet2.getRow(116).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(118, cell.getRow().getRowNum());
        assertEquals(3, cell.getColumnIndex());
        System.out.println("No.20:" + pe);
    }

    // No.21 ?
    tagCell = sheet2.getRow(122).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(124, cell.getRow().getRowNum());
        assertEquals(3, cell.getColumnIndex());
        System.out.println("No.21:" + pe);
    }

    // No.22 
    tagCell = sheet3.getRow(5).getCell(0);
    list.clear();
    list = parser.parse(sheet3, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("sheetName3", list.get(2).getSheetName());
    assertEquals("value1", list.get(0).getValue());
    assertEquals(2, list.get(1).getValue());
    assertEquals("value3", list.get(2).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals(TestEntity1.class, list.get(1).getClazz());
    assertEquals(TestEntity2.class, list.get(2).getClazz());
    assertEquals("propertyStr1", list.get(0).getPropertyName());
    assertEquals("propertyInt1", list.get(1).getPropertyName());
    assertEquals("propertyStr2", list.get(2).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());
    assertTrue(list.get(2).isUnique());

    // No.23 DataRowFrom > DataRowTo
    tagCell = sheet3.getRow(9).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(9, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.23:" + pe);
    }

    // No.24 DataRowFrom??
    tagCell = sheet3.getRow(17).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(17, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.24:" + pe);
    }

    // No.25 DataRowTo??
    tagCell = sheet3.getRow(21).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(21, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.25:" + pe);
    }

    // No.26 ResultKey
    tagCell = sheet3.getRow(25).getCell(0);
    list.clear();
    list = parser.parse(sheet3, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value7", list.get(0).getValue());
    assertEquals(8, list.get(1).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals(TestEntity1.class, list.get(1).getClazz());
    assertEquals("propertyStr1", list.get(0).getPropertyName());
    assertEquals("propertyInt1", list.get(1).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());

    // No.27 DataRowFrom?1??
    tagCell = sheet4.getRow(0).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(0, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.27:" + pe);
    }

    // No.28 DataRowTo?1??
    tagCell = sheet4.getRow(0).getCell(4);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(0, cell.getRow().getRowNum());
        assertEquals(4, cell.getColumnIndex());
        System.out.println("No.28:" + pe);
    }

    // No.29 DataRowFrom???
    tagCell = sheet4.getRow(15).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(15, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.29:" + pe);
    }

    // No.30 DataRowTo???
    tagCell = sheet4.getRow(15).getCell(4);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(15, cell.getRow().getRowNum());
        assertEquals(4, cell.getColumnIndex());
        System.out.println("No.30:" + pe);
    }

    // No.31 
    parser = new SheetToJavaSettingParser();
    tagCell = sheet1.getRow(0).getCell(0);
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName2", list.get(0).getSheetName());
    assertEquals("sheetName3", list.get(1).getSheetName());
    assertEquals(2, list.get(0).getValue());
    assertEquals("value3", list.get(1).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals(TestEntity2.class, list.get(1).getClazz());
    assertEquals("propertyInt1", list.get(0).getPropertyName());
    assertEquals("propertyStr2", list.get(1).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());

    // No.32 ?????
    tagCell = sheet5.getRow(0).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet5, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(4, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.21:" + pe);
    }

    // No.33 ???
    tagCell = sheet5.getRow(10).getCell(0);
    list.clear();
    list = parser.parse(sheet5, tagCell, data);
    assertEquals(2, list.size());
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value4", list.get(0).getValue());
    assertEquals(5, list.get(1).getValue());
    assertEquals(TestEntity1.class, list.get(0).getClazz());
    assertEquals(TestEntity1.class, list.get(1).getClazz());
    assertEquals("propertyStr1", list.get(0).getPropertyName());
    assertEquals("propertyInt1", list.get(1).getPropertyName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());
}

---

From source file:org.bbreak.excella.trans.tag.sheet2sql.RecreateSequenceParserTest.java

License:Open Source License

@Test
public final void testRecreateSequenceParser() throws ParseException {
    Workbook workbook = getWorkbook();/* w ww.  j  av  a 2 s. co  m*/
    Sheet sheet1 = workbook.getSheetAt(0);
    Sheet sheet2 = workbook.getSheetAt(1);
    Sheet sheet3 = workbook.getSheetAt(2);
    RecreateSequenceParser recreateSequenceParser = new RecreateSequenceParser("@RecreateSequence");
    Cell tagCell = null;
    Object data = null;
    List<String> list = null;

    // ===============================================
    // parse( Sheet sheet, Cell tagCell, Object data)
    // ===============================================
    // No.1 ?
    tagCell = sheet1.getRow(10).getCell(0);
    list = recreateSequenceParser.parse(sheet1, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("drop sequence table_name1;\ncreate sequence table_name1 start with 100;", list.get(0));
    assertEquals("drop sequence table_name2;\ncreate sequence table_name2 start with 200;", list.get(1));
    assertEquals("drop sequence  ;\ncreate sequence   start with 300;", list.get(2));

    // No.2 ?null?
    tagCell = sheet1.getRow(2).getCell(4);
    list.clear();
    list = recreateSequenceParser.parse(sheet1, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("drop sequence table_name3;\ncreate sequence table_name3 start with 100;", list.get(0));
    assertEquals("drop sequence table_name4;\ncreate sequence table_name4 start with 200;", list.get(1));
    assertEquals("drop sequence table_name5;\ncreate sequence table_name5 start with 300;", list.get(2));

    // No.3 ???null?
    tagCell = sheet2.getRow(5).getCell(0);
    list.clear();
    try {
        list = recreateSequenceParser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(5, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.3:" + pe);
    }

    // No.4 ??null?
    tagCell = sheet2.getRow(16).getCell(0);
    list.clear();
    try {
        list = recreateSequenceParser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(16, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.3:" + pe);
    }

    // No.5 ????null
    tagCell = sheet2.getRow(28).getCell(0);
    list.clear();
    try {
        list = recreateSequenceParser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(28, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.3:" + pe);
    }

    // No.6 ???
    tagCell = sheet2.getRow(41).getCell(0);
    list.clear();
    try {
        list = recreateSequenceParser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(41, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.3:" + pe);
    }

    // No.7 ???
    tagCell = sheet2.getRow(53).getCell(0);
    list.clear();
    try {
        list = recreateSequenceParser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(53, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.3:" + pe);
    }

    // No.8 ???
    tagCell = sheet2.getRow(66).getCell(0);
    list.clear();
    list = recreateSequenceParser.parse(sheet2, tagCell, data);
    assertEquals(1, list.size());
    assertEquals("drop sequence table_name11;\ncreate sequence table_name11 start with 1;", list.get(0));

    // No.9 ??
    tagCell = sheet2.getRow(78).getCell(0);
    list.clear();
    list = recreateSequenceParser.parse(sheet2, tagCell, data);
    assertEquals(1, list.size());
    assertEquals("drop sequence table_name12;\ncreate sequence table_name12 start with 10;", list.get(0));

    // No.10 ??
    tagCell = sheet2.getRow(90).getCell(0);
    list.clear();
    try {
        list = recreateSequenceParser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(90, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.3:" + pe);
    }

    // No.11 ?
    tagCell = sheet2.getRow(102).getCell(0);
    list.clear();
    try {
        list = recreateSequenceParser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(102, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.3:" + pe);
    }

    // No.12 
    tagCell = sheet3.getRow(5).getCell(0);
    RecreateSequenceParser recreateSequenceParser2 = new RecreateSequenceParser();
    list.clear();
    list = recreateSequenceParser2.parse(sheet3, tagCell, data);
    assertEquals("drop sequence table_name1;\ncreate sequence table_name1 start with 1000;", list.get(0));
    assertEquals(1, list.size());
}

---

From source file:org.bbreak.excella.trans.tag.sheet2sql.SheetToSqlExecuterTest.java

License:Open Source License

@Test
@SuppressWarnings("unchecked")
public final void testSheetToSqlExecuter() throws ParseException, java.text.ParseException {

    Workbook workbook = getWorkbook();//from   w  w w  .  ja v  a  2 s  . c  o m
    Sheet sheet = workbook.getSheetAt(0);
    SheetToSqlExecuter executer = new SheetToSqlExecuter();

    SheetData sheetData = new SheetData("SheetToSql");

    List<SheetToSqlParseInfo> sheet2SqlData = new ArrayList<SheetToSqlParseInfo>();
    List<SheetToSqlSettingInfo> sheet2SqlSettingData = new ArrayList<SheetToSqlSettingInfo>();

    // ===============================================
    // setDataConverter( SheetToSqlDataConverter dataConverter)
    // ===============================================
    DefaultSheetToSqlDataConverter converter = new DefaultSheetToSqlDataConverter();
    executer.setDataConverter(converter);

    // ===============================================
    // getDataConverter()
    // ===============================================
    assertEquals(converter, executer.getDataConverter());

    // ===============================================
    // postParse( Sheet sheet, SheetParser sheetParser, SheetData sheetData)
    // ===============================================
    String sheetName = "testSheet";
    String tagName = "@SheetToSql";
    String settingTagName = tagName + "Setting";

    // SheetToSqlParseInfo
    SheetToSqlParseInfo parseInfo1 = new SheetToSqlParseInfo();
    parseInfo1.setSheetName(sheetName);
    parseInfo1.setLogicalNameRowNum(1);
    parseInfo1.setValueRowNum(2);
    parseInfo1.setSettingTagName(settingTagName);
    sheet2SqlData.add(parseInfo1);

    // SheetToSqlSettingInfo
    SheetToSqlSettingInfo settingInfo1 = new SheetToSqlSettingInfo();
    settingInfo1.setTableName("test_table1");
    settingInfo1.setColumnName("col_char");
    settingInfo1.setValue("@LNAME()");
    settingInfo1.setDataType("");
    settingInfo1.setSheetName(sheetName);
    SheetToSqlSettingInfo settingInfo2 = new SheetToSqlSettingInfo();
    settingInfo2.setTableName("test_table1");
    settingInfo2.setColumnName("columnName2");
    settingInfo2.setValue(10);
    settingInfo2.setDataType("");
    settingInfo2.setSheetName(sheetName);
    sheet2SqlSettingData.add(settingInfo1);
    sheet2SqlSettingData.add(settingInfo2);

    // ???
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    // 
    SheetParser sheetParser = new SheetParser();
    sheetParser.addTagParser(new SheetToSqlParser());
    sheetParser.addTagParser(new SheetToSqlSettingParser());

    // No.1 postParse
    executer.postParse(sheet, sheetParser, sheetData);
    List<String> results = (List<String>) sheetData.get(tagName);
    String sql1 = "insert into test_table1 (col_char,columnName2) values ('String1',10);";
    String sql2 = "insert into test_table1 (col_char,columnName2) values ('String2',10);";
    String sql3 = "insert into test_table1 (col_char,columnName2) values ('String3',10);";
    assertEquals(3, results.size());
    assertEquals(sql1, results.get(0));
    assertEquals(sql2, results.get(1));
    assertEquals(sql3, results.get(2));

    // No.2 SheetToSqlSettingInfo????????
    assertNull(sheetData.get(settingTagName));

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    // No.3 ?null
    try {
        executer.postParse(null, sheetParser, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.4 ?null
    try {
        executer.postParse(sheet, null, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.5 ?null
    try {
        executer.postParse(sheet, sheetParser, null);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.6 ???
    sheetParser.addTagParser(new SheetToSqlParser("@UnusedSheetToSql")); /* ??? */
    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<String>) sheetData.get(tagName);
    assertEquals(3, results.size());
    assertEquals(sql1, results.get(0));
    assertEquals(sql2, results.get(1));
    assertEquals(sql3, results.get(2));

    // No.7 ?????
    SheetToSqlParseInfo parseInfo2 = new SheetToSqlParseInfo();
    parseInfo2.setSheetName("nonExistentSheet"); /* ????? */
    parseInfo2.setLogicalNameRowNum(1);
    parseInfo2.setValueRowNum(2);
    parseInfo2.setSettingTagName(settingTagName);
    sheet2SqlData.add(parseInfo2);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (ParseException pe) {
        // ?
        System.out.println("No.7:" + pe);
    }

    // No.8 ????null
    String sheetName2 = "testSheet (2)";
    SheetToSqlParseInfo parseInfo3 = new SheetToSqlParseInfo();
    parseInfo3.setSheetName(sheetName2);
    parseInfo3.setLogicalNameRowNum(1); /* null */
    parseInfo3.setValueRowNum(2);
    parseInfo3.setSettingTagName(settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo3);

    SheetToSqlSettingInfo settingInfo3 = new SheetToSqlSettingInfo();
    settingInfo3.setTableName("test_table1");
    settingInfo3.setColumnName("columnName1");
    settingInfo3.setValue("@LNAME()");
    settingInfo3.setDataType("");
    settingInfo3.setSheetName(sheetName2);
    sheet2SqlSettingData.clear();
    sheet2SqlSettingData.add(settingInfo3);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (ParseException pe) {
        // ?
        System.out.println("No.8:" + pe);
    }

    // No.9 ?null
    SheetToSqlParseInfo parseInfo4 = new SheetToSqlParseInfo();
    parseInfo4.setSheetName(sheetName2);
    parseInfo4.setLogicalNameRowNum(2);
    parseInfo4.setValueRowNum(4); /* null */
    parseInfo4.setSettingTagName(settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo4);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<String>) sheetData.get(tagName);
    assertEquals(1, results.size());
    sql1 = "insert into test_table1 (columnName1) values ('String2');";
    assertEquals(sql1, results.get(0));

    // No.10 ?null?
    SheetToSqlParseInfo parseInfo5 = new SheetToSqlParseInfo();
    parseInfo5.setSheetName(sheetName2);
    parseInfo5.setLogicalNameRowNum(2);
    parseInfo5.setValueRowNum(3);
    parseInfo5.setSettingTagName(settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo5);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<String>) sheetData.get(tagName);
    assertEquals(2, results.size());
    sql1 = "insert into test_table1 (columnName1) values ('String1');";
    sql2 = "insert into test_table1 (columnName1) values ('String2');";
    assertEquals(sql1, results.get(0));
    assertEquals(sql2, results.get(1));

    // No.11 ?null?
    String sheetName3 = "testSheet (3)";
    SheetToSqlParseInfo parseInfo6 = new SheetToSqlParseInfo();
    parseInfo6.setSheetName(sheetName3);
    parseInfo6.setLogicalNameRowNum(1);
    parseInfo6.setValueRowNum(2);
    parseInfo6.setSettingTagName(settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo6);

    SheetToSqlSettingInfo settingInfo4 = new SheetToSqlSettingInfo();
    settingInfo4.setTableName("test_table1");
    settingInfo4.setColumnName("columnName1");
    settingInfo4.setValue("@LNAME()");
    settingInfo4.setDataType("");
    settingInfo4.setSheetName(sheetName3);
    sheet2SqlSettingData.clear();
    sheet2SqlSettingData.add(settingInfo4);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<String>) sheetData.get(tagName);
    assertEquals(3, results.size());
    sql1 = "insert into test_table1 (columnName1) values ('String1');";
    sql2 = "insert into test_table1 (columnName1) values (null);";
    sql3 = "insert into test_table1 (columnName1) values ('String2');";
    assertEquals(sql1, results.get(0));
    assertEquals(sql2, results.get(1));
    assertEquals(sql3, results.get(2));

    // No.12 ????
    String sheetName4 = "testSheet (4)";
    SheetToSqlParseInfo parseInfo7 = new SheetToSqlParseInfo();
    parseInfo7.setSheetName(sheetName4);
    parseInfo7.setLogicalNameRowNum(1);
    parseInfo7.setValueRowNum(2);
    parseInfo7.setSettingTagName(settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo7);

    SheetToSqlSettingInfo settingInfo5 = new SheetToSqlSettingInfo();
    settingInfo5.setTableName("test_table1");
    settingInfo5.setColumnName("propertyName3");
    settingInfo5.setValue("@LNAME(2009/1/1)");
    settingInfo5.setDataType("");
    settingInfo5.setSheetName(sheetName4);
    sheet2SqlSettingData.clear();
    sheet2SqlSettingData.add(settingInfo5);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(0, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.12:" + pe);
    }

    // No.13 ???null
    String sheetName5 = "testSheet (5)";
    SheetToSqlParseInfo parseInfo8 = new SheetToSqlParseInfo();
    parseInfo8.setSheetName(sheetName5);
    //        parseInfo8.setLogicalNameRowNum( 1);
    parseInfo8.setValueRowNum(2);
    parseInfo8.setSettingTagName(settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo8);

    SheetToSqlSettingInfo settingInfo6 = new SheetToSqlSettingInfo();
    settingInfo6.setTableName("test_table1");
    settingInfo6.setColumnName("propertyName1");
    settingInfo6.setValue("@LNAME()");
    settingInfo6.setDataType("");
    settingInfo6.setSheetName(sheetName5);
    sheet2SqlSettingData.clear();
    sheet2SqlSettingData.add(settingInfo6);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.14 ???No?null
    SheetToSqlParseInfo parseInfo9 = new SheetToSqlParseInfo();
    parseInfo9.setSheetName(sheetName5);
    //        parseInfo9.setLogicalNameRowNum( 1);
    parseInfo9.setValueRowNum(2);
    parseInfo9.setSettingTagName(settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo9);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.15 No?null
    SheetToSqlParseInfo parseInfo10 = new SheetToSqlParseInfo();
    parseInfo10.setSheetName(sheetName5);
    parseInfo10.setLogicalNameRowNum(1);
    //        parseInfo10.setValueRowNum( 2);
    parseInfo10.setSettingTagName(settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo10);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.16 Setting???null
    SheetToSqlParseInfo parseInfo11 = new SheetToSqlParseInfo();
    parseInfo11.setSheetName(sheetName5);
    parseInfo11.setLogicalNameRowNum(1);
    parseInfo11.setValueRowNum(2);
    //        parseInfo11.setSettingTagName( settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo11);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    try {
        executer.postParse(sheet, sheetParser, sheetData);
        fail();
    } catch (NullPointerException e) {
        // ?
    }

    // No.17 SheetToSqlParseInfo??????
    sheet2SqlData.clear();

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<String>) sheetData.get(tagName);
    assertEquals(0, results.size());

    // No.18 SheetToSqlSettingInfo??????
    SheetToSqlParseInfo parseInfo12 = new SheetToSqlParseInfo();
    parseInfo12.setSheetName(sheetName5);
    parseInfo12.setLogicalNameRowNum(1);
    parseInfo12.setValueRowNum(2);
    parseInfo12.setSettingTagName(settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo12);

    sheet2SqlSettingData.clear();

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<String>) sheetData.get(tagName);
    assertEquals(0, results.size());

    // No.19 ???
    String sheetName6 = "testSheet (6)";
    SheetToSqlParseInfo parseInfo13 = new SheetToSqlParseInfo();
    parseInfo13.setSheetName(sheetName6);
    parseInfo13.setLogicalNameRowNum(1);
    parseInfo13.setValueRowNum(2);
    parseInfo13.setSettingTagName(settingTagName);
    sheet2SqlData.clear();
    sheet2SqlData.add(parseInfo13);

    SheetToSqlSettingInfo settingInfo7 = new SheetToSqlSettingInfo();
    settingInfo7.setTableName("test_table1");
    settingInfo7.setColumnName("columnName1");
    settingInfo7.setValue("@LNAME()");
    settingInfo7.setDataType("");
    settingInfo7.setSheetName(sheetName6);
    settingInfo7.setUnique(true); /* ??? */
    SheetToSqlSettingInfo settingInfo8 = new SheetToSqlSettingInfo();
    settingInfo8.setTableName("test_table1");
    settingInfo8.setColumnName("columnName2");
    settingInfo8.setValue("@LNAME()");
    settingInfo8.setDataType("");
    settingInfo8.setUnique(true); /* ??? */
    settingInfo8.setSheetName(sheetName6);
    SheetToSqlSettingInfo settingInfo9 = new SheetToSqlSettingInfo();
    settingInfo9.setTableName("test_table1");
    settingInfo9.setColumnName("columnName3");
    settingInfo9.setValue("@LNAME()");
    settingInfo9.setDataType("");
    settingInfo9.setUnique(false); /* ?? */
    settingInfo9.setSheetName(sheetName6);
    sheet2SqlSettingData.clear();
    sheet2SqlSettingData.add(settingInfo7);
    sheet2SqlSettingData.add(settingInfo8);
    sheet2SqlSettingData.add(settingInfo9);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<String>) sheetData.get(tagName);
    assertEquals(3, results.size());
    sql1 = "insert into test_table1 (columnName1,columnName2,columnName3) values ('String1',100,'2009-01-01');";
    sql2 = "insert into test_table1 (columnName1,columnName2,columnName3) values ('String2',null,'2009-03-01');";
    sql3 = "insert into test_table1 (columnName1,columnName2,columnName3) values ('String3',300,'2009-04-01');";
    assertEquals(sql1, results.get(0));
    assertEquals(sql2, results.get(1));
    assertEquals(sql3, results.get(2));

    // No.20 
    // test_table2??
    SheetToSqlSettingInfo settingInfo10 = new SheetToSqlSettingInfo();
    settingInfo10.setTableName("test_table2");
    settingInfo10.setColumnName("columnName1");
    settingInfo10.setValue("@LNAME()");
    settingInfo10.setDataType("");
    settingInfo10.setSheetName(sheetName6);
    settingInfo10.setUnique(true); /* ??? */
    SheetToSqlSettingInfo settingInfo11 = new SheetToSqlSettingInfo();
    settingInfo11.setTableName("test_table2");
    settingInfo11.setColumnName("columnName2");
    settingInfo11.setValue("@LNAME()");
    settingInfo11.setDataType("");
    settingInfo11.setUnique(false); /* ?? */
    settingInfo11.setSheetName(sheetName6);
    SheetToSqlSettingInfo settingInfo12 = new SheetToSqlSettingInfo();
    settingInfo12.setTableName("test_table2");
    settingInfo12.setColumnName("columnName3");
    settingInfo12.setValue("@LNAME()");
    settingInfo12.setDataType("");
    settingInfo12.setUnique(true); /* ??? */
    settingInfo12.setSheetName(sheetName6);

    // settingInfo10, 11, 12
    sheet2SqlSettingData.add(settingInfo10);
    sheet2SqlSettingData.add(settingInfo11);
    sheet2SqlSettingData.add(settingInfo12);

    // ?
    sheetData = new SheetData("SheetToSql");
    sheetData.put(tagName, sheet2SqlData);
    sheetData.put(settingTagName, sheet2SqlSettingData);

    executer.postParse(sheet, sheetParser, sheetData);
    results.clear();
    results = (List<String>) sheetData.get(tagName);
    assertEquals(7, results.size());
    sql1 = "insert into test_table1 (columnName1,columnName2,columnName3) values ('String1',100,'2009-01-01');";
    sql2 = "insert into test_table1 (columnName1,columnName2,columnName3) values ('String2',null,'2009-03-01');";
    sql3 = "insert into test_table1 (columnName1,columnName2,columnName3) values ('String3',300,'2009-04-01');";
    String sql4 = "insert into test_table2 (columnName1,columnName2,columnName3) values ('String1',100,'2009-01-01');";
    String sql5 = "insert into test_table2 (columnName1,columnName2,columnName3) values ('String1',100,'2009-02-01');";
    String sql6 = "insert into test_table2 (columnName1,columnName2,columnName3) values ('String2',null,'2009-03-01');";
    String sql7 = "insert into test_table2 (columnName1,columnName2,columnName3) values ('String3',300,'2009-04-01');";
    assertEquals(sql1, results.get(0));
    assertEquals(sql2, results.get(1));
    assertEquals(sql3, results.get(2));
    assertEquals(sql4, results.get(3));
    assertEquals(sql5, results.get(4));
    assertEquals(sql6, results.get(5));
    assertEquals(sql7, results.get(6));
}

---

From source file:org.bbreak.excella.trans.tag.sheet2sql.SheetToSqlParserTest.java

License:Open Source License

@Test
public final void testSheetToSqlParser() throws ParseException {
    Workbook workbook = getWorkbook();//w  w w  .java2  s. c  om
    Sheet sheet1 = workbook.getSheetAt(0);
    Sheet sheet2 = workbook.getSheetAt(1);
    Sheet sheet3 = workbook.getSheetAt(2);
    Sheet sheet4 = workbook.getSheetAt(3);
    Sheet sheet5 = workbook.getSheetAt(4);
    String tag = "@SheetToSql";
    SheetToSqlParser parser = new SheetToSqlParser(tag);
    Cell tagCell = null;
    Object data = null;
    List<SheetToSqlParseInfo> list = null;

    // ===============================================
    // parse( Sheet sheet, Cell tagCell, Object data)
    // ===============================================
    // No.1 ?
    tagCell = sheet1.getRow(0).getCell(0);
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals(tag + "Setting", list.get(0).getSettingTagName());
    assertEquals(tag + "Setting", list.get(1).getSettingTagName());
    assertEquals("sheetName2", list.get(0).getSheetName());
    assertEquals("sheetName3", list.get(1).getSheetName());
    assertEquals(new Integer(2), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(3), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(5), list.get(0).getValueRowNum());
    assertEquals(new Integer(6), list.get(1).getValueRowNum());

    // No.2 
    tagCell = sheet1.getRow(0).getCell(4);
    list.clear();
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("@SettingTagName", list.get(0).getSettingTagName());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(new Integer(7), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(8), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(10), list.get(0).getValueRowNum());
    assertEquals(new Integer(11), list.get(1).getValueRowNum());

    // No.3 ???null
    tagCell = sheet2.getRow(0).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("@SheetToSqlSetting", list.get(0).getSettingTagName());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(new Integer(2), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(3), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(5), list.get(0).getValueRowNum());
    assertEquals(new Integer(6), list.get(1).getValueRowNum());

    // No.4 ???No?null
    tagCell = sheet2.getRow(8).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(11, cell.getRow().getRowNum());
        assertEquals(1, cell.getColumnIndex());
        System.out.println("No.4:" + pe);
    }

    // No.5 No?null
    tagCell = sheet2.getRow(16).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(20, cell.getRow().getRowNum());
        assertEquals(2, cell.getColumnIndex());
        System.out.println("No.5:" + pe);
    }

    // No.6 ?null
    tagCell = sheet2.getRow(24).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(new Integer(17), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(18), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(19), list.get(0).getValueRowNum());
    assertEquals(new Integer(20), list.get(1).getValueRowNum());

    // No.7 ????null?
    tagCell = sheet2.getRow(32).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(new Integer(21), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(22), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(23), list.get(0).getValueRowNum());
    assertEquals(new Integer(24), list.get(1).getValueRowNum());

    // No.8 
    tagCell = sheet2.getRow(40).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(42, cell.getRow().getRowNum());
        assertEquals(1, cell.getColumnIndex());
        System.out.println("No.8:" + pe);
    }

    // No.9 
    tagCell = sheet2.getRow(46).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(48, cell.getRow().getRowNum());
        assertEquals(2, cell.getColumnIndex());
        System.out.println("No.9:" + pe);
    }

    // No.10 
    tagCell = sheet3.getRow(5).getCell(0);
    list.clear();
    list = parser.parse(sheet3, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("sheetName3", list.get(2).getSheetName());
    assertEquals(new Integer(1), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(2), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(3), list.get(2).getLogicalNameRowNum());
    assertEquals(new Integer(4), list.get(0).getValueRowNum());
    assertEquals(new Integer(5), list.get(1).getValueRowNum());
    assertEquals(new Integer(6), list.get(2).getValueRowNum());

    // No.11 DataRowFrom > DataRowTo
    tagCell = sheet3.getRow(9).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(9, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.11:" + pe);
    }

    // No.12 DataRowFrom?
    tagCell = sheet3.getRow(17).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(17, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.12:" + pe);
    }

    // No.13 DataRowTo?
    tagCell = sheet3.getRow(21).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(21, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.13:" + pe);
    }

    // No.14 SettingTagName?
    tagCell = sheet3.getRow(25).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(25, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.14:" + pe);
    }

    // No.15 ResultKey
    tagCell = sheet3.getRow(29).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(29, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.15:" + pe);
    }

    // No.16 DataRowFrom?1??
    tagCell = sheet4.getRow(0).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(0, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.16:" + pe);
    }

    // No.17 DataRowTo?1??
    tagCell = sheet4.getRow(0).getCell(4);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(0, cell.getRow().getRowNum());
        assertEquals(4, cell.getColumnIndex());
        System.out.println("No.17:" + pe);
    }

    // No.18 DataRowFrom???
    tagCell = sheet4.getRow(15).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(15, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.18:" + pe);
    }

    // No.19 DataRowTo???
    tagCell = sheet4.getRow(15).getCell(4);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(15, cell.getRow().getRowNum());
        assertEquals(4, cell.getColumnIndex());
        System.out.println("No.19:" + pe);
    }

    // No.20 
    parser = new SheetToSqlParser();
    tagCell = sheet1.getRow(0).getCell(0);
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals(tag + "Setting", list.get(0).getSettingTagName());
    assertEquals(tag + "Setting", list.get(1).getSettingTagName());
    assertEquals("sheetName2", list.get(0).getSheetName());
    assertEquals("sheetName3", list.get(1).getSheetName());
    assertEquals(new Integer(2), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(3), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(5), list.get(0).getValueRowNum());
    assertEquals(new Integer(6), list.get(1).getValueRowNum());

    // No.21 ?????
    tagCell = sheet5.getRow(0).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet5, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(4, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.21:" + pe);
    }

    // No.22 ???
    tagCell = sheet5.getRow(10).getCell(0);
    list.clear();
    list = parser.parse(sheet5, tagCell, data);
    assertEquals(2, list.size());
    assertEquals(tag + "Setting", list.get(0).getSettingTagName());
    assertEquals(tag + "Setting", list.get(1).getSettingTagName());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals(new Integer(4), list.get(0).getLogicalNameRowNum());
    assertEquals(new Integer(5), list.get(1).getLogicalNameRowNum());
    assertEquals(new Integer(7), list.get(0).getValueRowNum());
    assertEquals(new Integer(8), list.get(1).getValueRowNum());
}

---

From source file:org.bbreak.excella.trans.tag.sheet2sql.SheetToSqlSettingParserTest.java

License:Open Source License

@Test
public final void testSheetToSqlSettingParser() throws ParseException {
    Workbook workbook = getWorkbook();/*from w  w w. j av  a2s .c o m*/
    Sheet sheet1 = workbook.getSheetAt(0);
    Sheet sheet2 = workbook.getSheetAt(1);
    Sheet sheet3 = workbook.getSheetAt(2);
    Sheet sheet4 = workbook.getSheetAt(3);
    Sheet sheet5 = workbook.getSheetAt(4);
    String tag = "@SheetToSqlSetting";
    SheetToSqlSettingParser parser = new SheetToSqlSettingParser(tag);
    Cell tagCell = null;
    Object data = null;
    List<SheetToSqlSettingInfo> list = null;

    // ===============================================
    // parse( Sheet sheet, Cell tagCell, Object data)
    // ===============================================
    // No.1 ?
    tagCell = sheet1.getRow(0).getCell(0);
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName2", list.get(0).getSheetName());
    assertEquals("sheetName3", list.get(1).getSheetName());
    assertEquals("value2", list.get(0).getValue());
    assertEquals("value3", list.get(1).getValue());
    assertEquals("tableName2", list.get(0).getTableName());
    assertEquals("tableName3", list.get(1).getTableName());
    assertEquals("columnName2", list.get(0).getColumnName());
    assertEquals("columnName3", list.get(1).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());
    assertEquals("dataType2", list.get(0).getDataType());
    assertEquals("dataType3", list.get(1).getDataType());

    // No.2 
    tagCell = sheet1.getRow(0).getCell(7);
    list.clear();
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value4", list.get(0).getValue());
    assertEquals("value5", list.get(1).getValue());
    assertEquals("tableName4", list.get(0).getTableName());
    assertEquals("tableName5", list.get(1).getTableName());
    assertEquals("columnName4", list.get(0).getColumnName());
    assertEquals("columnName5", list.get(1).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertFalse(list.get(1).isUnique());
    assertEquals("dataType4", list.get(0).getDataType());
    assertEquals("dataType5", list.get(1).getDataType());

    // No.3 ???null
    tagCell = sheet2.getRow(0).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value2", list.get(0).getValue());
    assertEquals("value3", list.get(1).getValue());
    assertEquals("tableName2", list.get(0).getTableName());
    assertEquals("tableName3", list.get(1).getTableName());
    assertEquals("columnName2", list.get(0).getColumnName());
    assertEquals("columnName3", list.get(1).getColumnName());
    assertEquals("dataType2", list.get(0).getDataType());
    assertEquals("dataType3", list.get(1).getDataType());

    // No.4 ?null
    tagCell = sheet2.getRow(8).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("sheetName3", list.get(2).getSheetName());
    assertEquals("value4", list.get(0).getValue());
    assertNull(list.get(1).getValue());
    assertEquals("value5", list.get(2).getValue());
    assertEquals("tableName4", list.get(0).getTableName());
    assertEquals("tableName5", list.get(1).getTableName());
    assertEquals("tableName6", list.get(2).getTableName());
    assertEquals("columnName4", list.get(0).getColumnName());
    assertEquals("columnName5", list.get(1).getColumnName());
    assertEquals("columnName6", list.get(2).getColumnName());
    assertEquals("dataType4", list.get(0).getDataType());
    assertEquals("dataType5", list.get(1).getDataType());
    assertEquals("dataType6", list.get(2).getDataType());

    // No.5 ?null
    tagCell = sheet2.getRow(16).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(20, cell.getRow().getRowNum());
        assertEquals(2, cell.getColumnIndex());
        System.out.println("No.5:" + pe);
    }

    // No.6 ?null
    tagCell = sheet2.getRow(24).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet2, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(26, cell.getRow().getRowNum());
        assertEquals(3, cell.getColumnIndex());
        System.out.println("No.6:" + pe);
    }

    // No.7 ????null
    tagCell = sheet2.getRow(32).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("sheetName3", list.get(2).getSheetName());
    assertEquals("value12", list.get(0).getValue());
    assertEquals("value13", list.get(1).getValue());
    assertEquals("value14", list.get(2).getValue());
    assertEquals("tableName12", list.get(0).getTableName());
    assertEquals("tableName13", list.get(1).getTableName());
    assertEquals("tableName14", list.get(2).getTableName());
    assertEquals("columnName12", list.get(0).getColumnName());
    assertEquals("columnName13", list.get(1).getColumnName());
    assertEquals("columnName14", list.get(2).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertFalse(list.get(1).isUnique());
    assertTrue(list.get(2).isUnique());
    assertEquals("dataType12", list.get(0).getDataType());
    assertEquals("dataType13", list.get(1).getDataType());
    assertEquals("dataType14", list.get(2).getDataType());

    // No.8 ?null
    tagCell = sheet2.getRow(40).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("sheetName3", list.get(2).getSheetName());
    assertEquals("value15", list.get(0).getValue());
    assertEquals("value16", list.get(1).getValue());
    assertEquals("value17", list.get(2).getValue());
    assertEquals("tableName15", list.get(0).getTableName());
    assertEquals("tableName16", list.get(1).getTableName());
    assertEquals("tableName17", list.get(2).getTableName());
    assertEquals("columnName15", list.get(0).getColumnName());
    assertEquals("columnName16", list.get(1).getColumnName());
    assertEquals("columnName17", list.get(2).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());
    assertTrue(list.get(2).isUnique());
    assertEquals("dataType15", list.get(0).getDataType());
    assertEquals("dataType16", list.get(1).getDataType());
    assertNull(list.get(2).getDataType());

    // No.9 ?null
    tagCell = sheet2.getRow(48).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value18", list.get(0).getValue());
    assertEquals("value19", list.get(1).getValue());
    assertEquals("tableName18", list.get(0).getTableName());
    assertEquals("tableName19", list.get(1).getTableName());
    assertEquals("columnName18", list.get(0).getColumnName());
    assertEquals("columnName19", list.get(1).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertFalse(list.get(1).isUnique());
    assertEquals("dataType17", list.get(0).getDataType());
    assertEquals("dataType18", list.get(1).getDataType());

    // No.10 ????null?
    tagCell = sheet2.getRow(56).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value20", list.get(0).getValue());
    assertEquals("value21", list.get(1).getValue());
    assertEquals("tableName20", list.get(0).getTableName());
    assertEquals("tableName21", list.get(1).getTableName());
    assertEquals("columnName20", list.get(0).getColumnName());
    assertEquals("columnName21", list.get(1).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertFalse(list.get(1).isUnique());
    assertEquals("dataType19", list.get(0).getDataType());
    assertEquals("dataType20", list.get(1).getDataType());

    // No.11 ????
    tagCell = sheet2.getRow(64).getCell(0);
    list.clear();
    list = parser.parse(sheet2, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("@LNAME(??22)", list.get(0).getValue());
    assertEquals("@LNAME(??23)", list.get(1).getValue());
    assertEquals("tableName22", list.get(0).getTableName());
    assertEquals("tableName23", list.get(1).getTableName());
    assertEquals("columnName22", list.get(0).getColumnName());
    assertEquals("columnName23", list.get(1).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertFalse(list.get(1).isUnique());
    assertEquals("dataType21", list.get(0).getDataType());
    assertEquals("dataType22", list.get(1).getDataType());

    // No.12 
    tagCell = sheet3.getRow(5).getCell(0);
    list.clear();
    list = parser.parse(sheet3, tagCell, data);
    assertEquals(3, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("sheetName3", list.get(2).getSheetName());
    assertEquals("value1", list.get(0).getValue());
    assertEquals("value2", list.get(1).getValue());
    assertEquals("value3", list.get(2).getValue());
    assertEquals("tableName1", list.get(0).getTableName());
    assertEquals("tableName2", list.get(1).getTableName());
    assertEquals("tableName3", list.get(2).getTableName());
    assertEquals("columnName1", list.get(0).getColumnName());
    assertEquals("columnName2", list.get(1).getColumnName());
    assertEquals("columnName3", list.get(2).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());
    assertTrue(list.get(2).isUnique());
    assertEquals("dataType1", list.get(0).getDataType());
    assertEquals("dataType2", list.get(1).getDataType());
    assertEquals("dataType3", list.get(2).getDataType());

    // No.13 DataRowFrom > DataRowTo
    tagCell = sheet3.getRow(9).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(9, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.13:" + pe);
    }

    // No.14 DataRowFrom??
    tagCell = sheet3.getRow(17).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(17, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.14:" + pe);
    }

    // No.15 DataRowTo??
    tagCell = sheet3.getRow(21).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet3, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(21, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.15:" + pe);
    }

    // No.16 ResultKey
    tagCell = sheet3.getRow(25).getCell(0);
    list.clear();
    list = parser.parse(sheet3, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value7", list.get(0).getValue());
    assertEquals("value8", list.get(1).getValue());
    assertEquals("tableName7", list.get(0).getTableName());
    assertEquals("tableName8", list.get(1).getTableName());
    assertEquals("columnName7", list.get(0).getColumnName());
    assertEquals("columnName8", list.get(1).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());
    assertEquals("dataType7", list.get(0).getDataType());
    assertEquals("dataType8", list.get(1).getDataType());

    // No.17 DataRowFrom?1??
    tagCell = sheet4.getRow(0).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(0, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.17:" + pe);
    }

    // No.18 DataRowTo?1??
    tagCell = sheet4.getRow(0).getCell(4);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(0, cell.getRow().getRowNum());
        assertEquals(4, cell.getColumnIndex());
        System.out.println("No.18:" + pe);
    }

    // No.19 DataRowFrom???
    tagCell = sheet4.getRow(15).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(15, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.19:" + pe);
    }

    // No.20 DataRowTo???
    tagCell = sheet4.getRow(15).getCell(4);
    list.clear();
    try {
        list = parser.parse(sheet4, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(15, cell.getRow().getRowNum());
        assertEquals(4, cell.getColumnIndex());
        System.out.println("No.20:" + pe);
    }

    // No.21 
    parser = new SheetToSqlSettingParser();
    tagCell = sheet1.getRow(0).getCell(0);
    list = parser.parse(sheet1, tagCell, data);
    assertEquals(2, list.size());
    assertEquals("sheetName2", list.get(0).getSheetName());
    assertEquals("sheetName3", list.get(1).getSheetName());
    assertEquals("value2", list.get(0).getValue());
    assertEquals("value3", list.get(1).getValue());
    assertEquals("tableName2", list.get(0).getTableName());
    assertEquals("tableName3", list.get(1).getTableName());
    assertEquals("columnName2", list.get(0).getColumnName());
    assertEquals("columnName3", list.get(1).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());
    assertEquals("dataType2", list.get(0).getDataType());
    assertEquals("dataType3", list.get(1).getDataType());

    // No.22 ?????
    tagCell = sheet5.getRow(0).getCell(0);
    list.clear();
    try {
        list = parser.parse(sheet5, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(4, cell.getRow().getRowNum());
        assertEquals(0, cell.getColumnIndex());
        System.out.println("No.21:" + pe);
    }

    // No.23 ???
    tagCell = sheet5.getRow(10).getCell(0);
    list.clear();
    list = parser.parse(sheet5, tagCell, data);
    assertEquals(2, list.size());
    assertEquals(2, list.size());
    assertEquals("sheetName1", list.get(0).getSheetName());
    assertEquals("sheetName2", list.get(1).getSheetName());
    assertEquals("value4", list.get(0).getValue());
    assertEquals("value5", list.get(1).getValue());
    assertEquals("tableName4", list.get(0).getTableName());
    assertEquals("tableName5", list.get(1).getTableName());
    assertEquals("columnName4", list.get(0).getColumnName());
    assertEquals("columnName5", list.get(1).getColumnName());
    assertTrue(list.get(0).isUnique());
    assertTrue(list.get(1).isUnique());
    assertEquals("dataType4", list.get(0).getDataType());
    assertEquals("dataType5", list.get(1).getDataType());

}

---

From source file:org.bbreak.excella.trans.tag.sheet2sql.SqlParserTest.java

License:Open Source License

@Test
public final void testSqlParser() throws ParseException {
    Workbook workbook = getWorkbook();/*from   ww  w  .  j a  va  2s.c o  m*/
    Sheet sheet = workbook.getSheetAt(0);
    SqlParser sqlParser = new SqlParser("@Sql");
    Cell tagCell = null;
    Object data = null;
    List<String> list = null;

    // ===============================================
    // parse( Sheet sheet, Cell tagCell, Object data) 
    // ===============================================
    // No.1 ?
    tagCell = sheet.getRow(10).getCell(0);
    list = sqlParser.parse(sheet, tagCell, data);
    assertEquals("update table_name1 set password='init';", list.get(0));
    assertEquals("update table_name2 set password='init';;", list.get(1));
    assertEquals(" ;", list.get(2));
    assertEquals(3, list.size());

    // No.2 ?null?
    tagCell = sheet.getRow(2).getCell(4);
    list = sqlParser.parse(sheet, tagCell, data);
    assertEquals("update table_name3 set password='init';", list.get(0));
    assertEquals("update table_name4 set password='init';", list.get(1));
    assertEquals("update table_name5 set password='init';", list.get(2));
    assertEquals(3, list.size());

    // No.3 ?null?
    tagCell = sheet.getRow(8).getCell(12);
    list = sqlParser.parse(sheet, tagCell, data);
    assertEquals("update table_name6 set password='init';", list.get(0));
    assertEquals("update table_name7 set password='init';", list.get(1));
    assertEquals(2, list.size());

    // No.4 ?
    tagCell = sheet.getRow(13).getCell(20);
    list.clear();
    try {
        list = sqlParser.parse(sheet, tagCell, data);
        fail();
    } catch (ParseException pe) {
        Cell cell = pe.getCell();
        assertEquals(13, cell.getRow().getRowNum());
        assertEquals(20, cell.getColumnIndex());
    }

    // No.5 
    SqlParser sqlParser2 = new SqlParser();
    tagCell = sheet.getRow(8).getCell(24);
    list.clear();
    list = sqlParser2.parse(sheet, tagCell, data);
    assertEquals("update table_name8 set password='init';", list.get(0));
    assertEquals("update table_name9 set password='init';", list.get(1));
    assertEquals(2, list.size());
}

---

From source file:org.drugepi.table.ExcelUtils.java

License:Mozilla Public License

public static boolean cellIsBold(Cell cell) {
    if (cell == null)
        return false;

    Row row = cell.getRow();
    Sheet sheet = row.getSheet();/*from   w ww.j  a  v  a  2  s  .  c  om*/
    Workbook workbook = sheet.getWorkbook();
    Font font = workbook.getFontAt(cell.getCellStyle().getFontIndex());

    if (font.getBoldweight() == Font.BOLDWEIGHT_BOLD)
        return true;

    return false;
}

---

From source file:org.drugepi.table.ExcelUtils.java

License:Mozilla Public License

public static void restyleCell(CellStyleLookup csl, Cell cell) {
    CellStyle origStyle = cell.getCellStyle();

    CellStyle newStyle = csl.getExistingStyle(origStyle);
    if (newStyle == null) {
        newStyle = cell.getRow().getSheet().getWorkbook().createCellStyle();
        newStyle.cloneStyleFrom(origStyle);
        newStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        csl.putNewStyle(origStyle, newStyle);
    }//  www  .j  a v  a  2  s.  com

    cell.setCellStyle(newStyle);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• »