List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex
int getColumnIndex();
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 a v a2 s . c om 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 om 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.SheetToSqlParser.java
License:Open Source License
/** * ?//from w w w. j av a 2 s . c om * * @param sheet * @param tagCell ??? * @param data TransProcessor?processBook, processSheet?<BR> * ????TagParser.parse??????<BR> * @return ? * @throws ParseException */ @Override public List<SheetToSqlParseInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ? int tagRowIdx = tagCell.getRowIndex(); int tagColIdx = tagCell.getColumnIndex(); int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); // ???? String settingTagName = getTag() + DEFAULT_SETTING_SUFFIX; try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM, DEFAULT_DATA_ROW_FROM_ADJUST); if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM); } // ? valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx); if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ??? if (valueRowFromIdx > valueRowToIdx) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO); } // ???? if (paramDef.containsKey(PARAM_SETTING_TAG_NAME)) { settingTagName = paramDef.get(PARAM_SETTING_TAG_NAME); } // ? if (paramDef.containsKey(PARAM_RESULT_KEY)) { // ??????? throw new ParseException(tagCell, PARAM_RESULT_KEY + "????????"); } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } List<SheetToSqlParseInfo> sheetInfoList = new ArrayList<SheetToSqlParseInfo>(); // ?? int sheetNameColIdx = tagColIdx++; // ???No int logicalRowColIdx = tagColIdx++; // No int dataRowColIdx = tagColIdx; // ????? Workbook workbook = sheet.getWorkbook(); // ??? for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { // ?? Cell sheetNameCell = row.getCell(sheetNameColIdx); Cell logicalRowNumCell = row.getCell(logicalRowColIdx); Cell valueRowNumCell = row.getCell(dataRowColIdx); // ? if ((sheetNameCell == null) && (logicalRowNumCell == null) && (valueRowNumCell == null)) { // ????null?? continue; } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null) || ("".equals(sheetNameCell.getStringCellValue()))) { // ????????? continue; } else { // ???? Cell requiredErrorCell = null; if (logicalRowNumCell == null) { requiredErrorCell = row.createCell(logicalRowColIdx); } else if (valueRowNumCell == null) { requiredErrorCell = row.createCell(dataRowColIdx); } // ?????? if (requiredErrorCell != null) { throw new ParseException(requiredErrorCell, "?null??"); } } // ???No? int logicalRowNum; try { logicalRowNum = (Integer) PoiUtil.getCellValue(logicalRowNumCell, Integer.class); } catch (Exception e) { throw new ParseException(logicalRowNumCell, e); } // No? int valueRowNum; try { valueRowNum = (Integer) PoiUtil.getCellValue(valueRowNumCell, Integer.class); } catch (Exception e) { throw new ParseException(valueRowNumCell, e); } // ??? String sheetName = sheetNameCell.getStringCellValue(); if (workbook.getSheet(sheetName) == null) { throw new ParseException(sheetNameCell, "[" + sheetName + "]????"); } // SheetToSqlParseInfo sheetInfo = new SheetToSqlParseInfo(); sheetInfo.setSettingTagName(settingTagName); sheetInfo.setSheetName(sheetName); sheetInfo.setLogicalNameRowNum(logicalRowNum); sheetInfo.setValueRowNum(valueRowNum); sheetInfoList.add(sheetInfo); } } return sheetInfoList; }
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();//from w ww . j a va 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 = "@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.SheetToSqlSettingParser.java
License:Open Source License
/** * ?// w w w. j a va2 s .co m * * @param sheet * @param tagCell ??? * @param data TransProcessor?processBook, processSheet?<BR> * ????TagParser.parse??????<BR> * @return ? * @throws ParseException */ @Override public List<SheetToSqlSettingInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ? int tagRowIdx = tagCell.getRowIndex(); int tagColIdx = tagCell.getColumnIndex(); // int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM, DEFAULT_DATA_ROW_FROM_ADJUST); if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ? valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx); if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ??? if (valueRowFromIdx > valueRowToIdx) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO); } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } List<SheetToSqlSettingInfo> sheetSettingInfoList = new ArrayList<SheetToSqlSettingInfo>(); // ?? int sheetNameColIdx = tagColIdx++; // int valueColIdx = tagColIdx++; // int tableColIdx = tagColIdx++; // int columnNameColIdx = tagColIdx++; // ??? int uniqueColIdx = tagColIdx++; // int dataTypeColIdx = tagColIdx++; // ????? Workbook workbook = sheet.getWorkbook(); // ??? for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { // ?? Cell sheetNameCell = row.getCell(sheetNameColIdx); Cell valueCell = row.getCell(valueColIdx); Cell tableNameCell = row.getCell(tableColIdx); Cell columnNameCell = row.getCell(columnNameColIdx); Cell uniqueCell = row.getCell(uniqueColIdx); Cell dataTypeCell = row.getCell(dataTypeColIdx); // ? if ((sheetNameCell == null) && (valueCell == null) && (tableNameCell == null) && (columnNameCell == null) && (uniqueCell == null) && (dataTypeCell == null)) { // ????null?? continue; } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null) || ("".equals(sheetNameCell.getStringCellValue()))) { // ????????? continue; } else { // ???? Cell requiredErrorCell = null; if (tableNameCell == null) { // ?null?? requiredErrorCell = row.createCell(tableColIdx); } else if (columnNameCell == null) { // ?null?? requiredErrorCell = row.createCell(columnNameColIdx); } if (requiredErrorCell != null) { // ?? throw new ParseException(requiredErrorCell, "?null??"); } } // ?? SheetToSqlSettingInfo settingInfo = new SheetToSqlSettingInfo(); // ???? String sheetName = sheetNameCell.getStringCellValue(); if (workbook.getSheet(sheetName) == null) { throw new ParseException(sheetNameCell, "[" + sheetName + "]????"); } // ?? settingInfo.setSheetName(sheetName); settingInfo.setSheetNameCell(sheetNameCell); // ?? settingInfo.setTableName(tableNameCell.getStringCellValue()); settingInfo.setTableNameCell(tableNameCell); // settingInfo.setColumnName(columnNameCell.getStringCellValue()); settingInfo.setColumnNameCell(columnNameCell); // if (valueCell != null) { Object value = PoiUtil.getCellValue(valueCell); settingInfo.setValue(value); settingInfo.setValueCell(valueCell); } // ??? if (uniqueCell != null) { if (uniqueCell.getStringCellValue() != null && uniqueCell.getStringCellValue().equals(UNIQUE_PROPERTY_MARK)) { settingInfo.setUnique(true); settingInfo.setUniqueCell(uniqueCell); } } // if (dataTypeCell != null) { settingInfo.setDataType(dataTypeCell.getStringCellValue()); settingInfo.setDataTypeCell(dataTypeCell); } // ???? sheetSettingInfoList.add(settingInfo); } } return sheetSettingInfoList; }
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 ww.ja v a 2 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 = "@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();/* ww w . j a v a 2s . com*/ 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.codelabor.example.crud.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(MultipartFile file, List<String> failureMessages) throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD // by/* w w w. j a v a 2 s .com*/ // "SHIN Sang-jae" Workbook wb = WorkbookFactory.create(file.getInputStream()); int numberOfSheets = wb.getNumberOfSheets(); logger.debug("numberOfSheets: {}", numberOfSheets); // prepare model List<EmpDto> empDtoList = new ArrayList<EmpDto>(); // set effective position int effectiveFirstSheetIndex = 0; int effectiveLastSheetIndex = numberOfSheets - 1; // traverse sheet StringBuilder sb = new StringBuilder(); for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) { Sheet sheet = wb.getSheetAt(i); String sheetName = sheet.getSheetName(); logger.debug("sheetName: {}", sheetName); int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); logger.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum); // set effective position int effectiveFirstRowIndex = 1; // header row: 0 int effectiveLastRowIndex = lastRowNum; // traverse row for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) { // prepare model EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae" Row row = sheet.getRow(j); int rowNum = row.getRowNum(); int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getLastCellNum(); logger.debug("rowNum: {}, firstCellNum: {}, lastCellNum: {}", rowNum, firstCellNum, lastCellNum); // set effective position int effectiveFirstCellIndex = firstCellNum; int effectiveLastCellIndex = lastCellNum - 1; // traverse cell for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) { Cell cell = row.getCell(k); if (cell != null) { int rowIndex = cell.getRowIndex(); int columnIndex = cell.getColumnIndex(); CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae" logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex); // populate dto switch (k) { case 0: // EMPNO empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue()); break; case 1: // ENAME empDto.setEname(cell.getRichStringCellValue().toString()); break; case 2: // JOB empDto.setJob(cell.getRichStringCellValue().toString()); break; case 3: // MGR empDto.setMgr(((Double) cell.getNumericCellValue()).intValue()); break; case 4: // HIREDATE empDto.setHireDate(cell.getDateCellValue()); break; case 5: // SAL // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 6: // COMM // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 7: // DEPTNO empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue()); break; default: break; } } } logger.debug("empDto: {}", empDto); // validate Validator validator = Validation.buildDefaultValidatorFactory().getValidator(); Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto); if (violations.isEmpty()) { // do all or nothing empDtoList.add(empDto); } else { // add failure message sb.setLength(0); // init StringBuilder for reuse for (ConstraintViolation<EmpDto> violation : violations) { String propertyPath = violation.getPropertyPath().toString(); String message = violation.getMessage(); sb.append(message); sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')'); failureMessages.add(sb.toString()); logger.error(sb.toString()); sb.setLength(0); } } } } return empDtoList; }
From source file:org.codelabor.example.crud.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(Part file, List<String> failureMessages) throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD // by//from w w w .j a v a 2 s . c om // "SHIN Sang-jae" Workbook wb = WorkbookFactory.create(file.getInputStream()); int numberOfSheets = wb.getNumberOfSheets(); logger.debug("numberOfSheets: {}", numberOfSheets); // prepare model List<EmpDto> empDtoList = new ArrayList<EmpDto>(); // set effective position int effectiveFirstSheetIndex = 0; int effectiveLastSheetIndex = numberOfSheets - 1; // traverse sheet StringBuilder sb = new StringBuilder(); for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) { Sheet sheet = wb.getSheetAt(i); String sheetName = sheet.getSheetName(); logger.debug("sheetName: {}", sheetName); int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); logger.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum); // set effective position int effectiveFirstRowIndex = 1; // header row: 0 int effectiveLastRowIndex = lastRowNum; // traverse row for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) { // prepare model EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae" Row row = sheet.getRow(j); int rowNum = row.getRowNum(); int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getLastCellNum(); logger.debug("rowNum: {}, firstCellNum: {}, lastCellNum: {}", rowNum, firstCellNum, lastCellNum); // set effective position int effectiveFirstCellIndex = firstCellNum; int effectiveLastCellIndex = lastCellNum - 1; // traverse cell for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) { Cell cell = row.getCell(k); if (cell != null) { int rowIndex = cell.getRowIndex(); int columnIndex = cell.getColumnIndex(); CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae" logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex); // populate dto switch (k) { case 0: // EMPNO empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue()); break; case 1: // ENAME empDto.setEname(cell.getRichStringCellValue().toString()); break; case 2: // JOB empDto.setJob(cell.getRichStringCellValue().toString()); break; case 3: // MGR empDto.setMgr(((Double) cell.getNumericCellValue()).intValue()); break; case 4: // HIREDATE empDto.setHireDate(cell.getDateCellValue()); break; case 5: // SAL // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 6: // COMM // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 7: // DEPTNO empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue()); break; default: break; } } } logger.debug("empDto: {}", empDto); // validate Validator validator = Validation.buildDefaultValidatorFactory().getValidator(); Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto); if (violations.isEmpty()) { // do all or nothing empDtoList.add(empDto); } else { // add failure message sb.setLength(0); // init StringBuilder for reuse for (ConstraintViolation<EmpDto> violation : violations) { String propertyPath = violation.getPropertyPath().toString(); String message = violation.getMessage(); sb.append(message); sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')'); failureMessages.add(sb.toString()); logger.error(sb.toString()); sb.setLength(0); } } } } return empDtoList; }
From source file:org.codelabor.example.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(MultipartFile file, List<String> failureMessages) throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD by "SHIN Sang-jae" Workbook wb = WorkbookFactory.create(file.getInputStream()); int numberOfSheets = wb.getNumberOfSheets(); logger.debug("numberOfSheets: {}", numberOfSheets); // prepare model List<EmpDto> empDtoList = new ArrayList<EmpDto>(); // set effective position int effectiveFirstSheetIndex = 0; int effectiveLastSheetIndex = numberOfSheets - 1; // traverse sheet StringBuilder sb = new StringBuilder(); for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) { Sheet sheet = wb.getSheetAt(i);//from w w w . j av a 2 s. c o m String sheetName = sheet.getSheetName(); logger.debug("sheetName: {}", sheetName); int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); logger.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum); // set effective position int effectiveFirstRowIndex = 1; // header row: 0 int effectiveLastRowIndex = lastRowNum; // traverse row for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) { // prepare model EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae" Row row = sheet.getRow(j); int rowNum = row.getRowNum(); int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getLastCellNum(); logger.debug("rowNum: {}, firstCellNum: {}, lastCellNum: {}", rowNum, firstCellNum, lastCellNum); // set effective position int effectiveFirstCellIndex = firstCellNum; int effectiveLastCellIndex = lastCellNum - 1; // traverse cell for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) { Cell cell = row.getCell(k); if (cell != null) { int rowIndex = cell.getRowIndex(); int columnIndex = cell.getColumnIndex(); CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae" logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex); // populate dto switch (k) { case 0: // EMPNO empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue()); break; case 1: // ENAME empDto.setEname(cell.getRichStringCellValue().toString()); break; case 2: // JOB empDto.setJob(cell.getRichStringCellValue().toString()); break; case 3: // MGR empDto.setMgr(((Double) cell.getNumericCellValue()).intValue()); break; case 4: // HIREDATE empDto.setHireDate(cell.getDateCellValue()); break; case 5: // SAL // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 6: // COMM // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 7: // DEPTNO empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue()); break; default: break; } } } logger.debug("empDto: {}", empDto); // validate Validator validator = Validation.buildDefaultValidatorFactory().getValidator(); Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto); if (violations.isEmpty()) { // do all or nothing empDtoList.add(empDto); } else { // add failure message sb.setLength(0); // init StringBuilder for reuse for (ConstraintViolation<EmpDto> violation : violations) { String propertyPath = violation.getPropertyPath().toString(); String message = violation.getMessage(); sb.append(message); sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')'); failureMessages.add(sb.toString()); logger.error(sb.toString()); sb.setLength(0); } } } } return empDtoList; }