List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook(InputStream s) throws IOException
From source file:com.appspot.backstreetfoodies.server.XLSParser.java
License:Apache License
public XLSParser(InputStream inputStream, int sheetIndex, int numColumnsExpected) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(sheetIndex); Iterator<Row> rowIterator = sheet.iterator(); Iterator<Cell> cellIterator; numColumns = numColumnsExpected;/* w ww . j a v a 2 s . c o m*/ while (rowIterator.hasNext()) { int numCellsInRow = 0; Row row = rowIterator.next(); cellIterator = row.cellIterator(); ArrayList<String> temp = new ArrayList<String>(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (!String.valueOf(cell.getNumericCellValue()).isEmpty()) { numCellsInRow++; temp.add(String.valueOf(cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_STRING: if (!cell.getStringCellValue().isEmpty()) { numCellsInRow++; temp.add(cell.getStringCellValue().trim()); } break; default: break; } } if (numCellsInRow == numColumnsExpected) { xlsData.add(temp); } } }
From source file:com.asakusafw.testdata.generator.excel.ExcelTesterRoot.java
License:Apache License
/** * Opens the workbook./*from w w w . j ava 2 s . c om*/ * @param file the target workbook * @return the opened workbook * @throws IOException if failed */ protected Workbook openWorkbook(File file) throws IOException { try (InputStream in = new FileInputStream(file)) { if (file.getName().endsWith(".xls")) { return new HSSFWorkbook(in); } else if (file.getName().endsWith(".xlsx")) { return new XSSFWorkbook(in); } else { throw new IOException(file.getPath()); } } }
From source file:com.asakusafw.testdriver.excel.legacy.LegacyExcelRuleExtractorTest.java
License:Apache License
private Sheet sheet(String name) { try (InputStream in = getClass().getResourceAsStream(name)) { assertThat(name, in, not(nullValue())); Workbook book = new HSSFWorkbook(in); return book.getSheetAt(0); } catch (IOException e) { throw new AssertionError(e); }//from ww w. ja va 2 s. co m }
From source file:com.asakusafw.testdriver.excel.Util.java
License:Apache License
static Workbook openWorkbookFor(String path, InputStream input) throws IOException { if (isHssf(path)) { return new HSSFWorkbook(input); } else if (isXssf(path)) { return new XSSFWorkbook(input); } else {/*from w w w . j a v a2 s . co m*/ return new HSSFWorkbook(input); } }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ?Excel????/*from w ww . j a v a 2 s .c o m*/ * @param filename Excel??? * @throws IOException Excel??????? */ public ExcelUtils(String filename) throws IOException { this.filename = filename; InputStream is = new FileInputStream(filename); workbook = new HSSFWorkbook(is); inputDataSheet = workbook.getSheet(Constants.INPUT_DATA_SHEET_NAME); if (inputDataSheet == null) { throw new IOException( "Excel: " + filename + "??????"); } outputDataSheet = workbook.getSheet(Constants.OUTPUT_DATA_SHEET_NAME); if (outputDataSheet == null) { throw new IOException( "Excel: " + filename + "??????"); } testConditionSheet = workbook.getSheet(Constants.TEST_CONDITION_SHEET_NAME); if (testConditionSheet == null) { throw new IOException("Excel: " + filename + "???????"); } HSSFCell tableNameCell = getCell(testConditionSheet, ConditionSheetItem.TABLE_NAME.getRow(), ConditionSheetItem.TABLE_NAME.getCol() + 1); tablename = tableNameCell.getStringCellValue(); if (tablename == null || tablename.length() == 0) { throw new IOException( "Excel: " + filename + "?????????"); } HSSFCell rowMatchingConditionCell = getCell(testConditionSheet, ConditionSheetItem.ROW_MATCHING_CONDITION.getRow(), ConditionSheetItem.ROW_MATCHING_CONDITION.getCol() + 1); String rowMatchingConditionStr = rowMatchingConditionCell.getStringCellValue(); if (rowMatchingConditionStr == null || rowMatchingConditionStr.length() == 0) { throw new IOException("Excel: " + filename + "?????????"); } rowMatchingCondition = RowMatchingCondition.getConditonByJapanseName(rowMatchingConditionStr); if (rowMatchingCondition == null) { throw new IOException("Excel: " + filename + "????????????"); } columnInfos = createColumnInfos(); }
From source file:com.asakusafw.testtools.excel.ExcelUtilsTest.java
License:Apache License
/** * getXXXoption()?/*from w w w. ja v a 2 s . c o m*/ * @throws Exception */ @SuppressWarnings("deprecation") @Test public void testGetXXXOption() throws Exception { String filename = "src/test/data/Excel/ExcelUtils/ALLT_TYPES.xls"; // ?EXCEL? InputStream is = new FileInputStream(filename); HSSFWorkbook workbook = new HSSFWorkbook(is); HSSFSheet sheet = workbook.getSheet(Constants.OUTPUT_DATA_SHEET_NAME); // ?ExcelUtils? ExcelUtils excelUtils = new ExcelUtils(filename); // getLongOption? LongOption longOption = new LongOption(); longOption.modify(0); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_0, TYPES.BIGINT, longOption); longOption.modify(1); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_1, TYPES.BIGINT, longOption); longOption.modify(-1); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MINUS1, TYPES.BIGINT, longOption); longOption.modify(ExcelUtils.EXCEL_MAX_LONG); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MAX, TYPES.BIGINT, longOption); longOption.modify(ExcelUtils.EXCEL_MIN_LONG); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MIN, TYPES.BIGINT, longOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DECIMAL, TYPES.BIGINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_OVER_MAX, TYPES.BIGINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_UNDER_MIN, TYPES.BIGINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_BIG_VALUE, TYPES.BIGINT, new NumberFormatException("????")); longOption.modify(0); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_0, TYPES.BIGINT, longOption); longOption.modify(1); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_1, TYPES.BIGINT, longOption); longOption.modify(-1); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MINUS1, TYPES.BIGINT, longOption); longOption.modify(Long.MAX_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MAX, TYPES.BIGINT, longOption); longOption.modify(Long.MIN_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MIN, TYPES.BIGINT, longOption); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DECIMAL, TYPES.BIGINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_OVER_MAX, TYPES.BIGINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_UNDER_MIN, TYPES.BIGINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_BIG_VALUE, TYPES.BIGINT, new CellTypeMismatchException("")); longOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.BLANK, TYPES.BIGINT, longOption); longOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.NULL_STRING, TYPES.BIGINT, new CellTypeMismatchException("")); longOption.modify(1); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_TRUE, TYPES.BIGINT, longOption); longOption.modify(0); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_FALSE, TYPES.BIGINT, longOption); longOption.modify(40179); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATE, TYPES.BIGINT, longOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT1, TYPES.BIGINT, longOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT2, TYPES.BIGINT, longOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT1, TYPES.BIGINT, longOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT2, TYPES.BIGINT, longOption); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATE, TYPES.BIGINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATETIME, TYPES.BIGINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT1, TYPES.BIGINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT2, TYPES.BIGINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT1, TYPES.BIGINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT2, TYPES.BIGINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATETIME, TYPES.BIGINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.ERROR, TYPES.BIGINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.FORMULA, TYPES.BIGINT, new CellTypeMismatchException("")); // getIntOption? IntOption intOption = new IntOption(); intOption.modify(0); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_0, TYPES.INT, intOption); intOption.modify(1); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_1, TYPES.INT, intOption); intOption.modify(-1); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MINUS1, TYPES.INT, intOption); intOption.modify(Integer.MAX_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MAX, TYPES.INT, intOption); intOption.modify(Integer.MIN_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MIN, TYPES.INT, intOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DECIMAL, TYPES.INT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_OVER_MAX, TYPES.INT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_UNDER_MIN, TYPES.INT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_BIG_VALUE, TYPES.INT, new NumberFormatException("????")); intOption.modify(0); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_0, TYPES.INT, intOption); intOption.modify(1); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_1, TYPES.INT, intOption); intOption.modify(-1); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MINUS1, TYPES.INT, intOption); intOption.modify(Integer.MAX_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MAX, TYPES.INT, intOption); intOption.modify(Integer.MIN_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MIN, TYPES.INT, intOption); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DECIMAL, TYPES.INT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_OVER_MAX, TYPES.INT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_UNDER_MIN, TYPES.INT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_BIG_VALUE, TYPES.INT, new CellTypeMismatchException("")); intOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.BLANK, TYPES.INT, intOption); longOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.NULL_STRING, TYPES.INT, new CellTypeMismatchException("")); intOption.modify(1); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_TRUE, TYPES.INT, intOption); intOption.modify(0); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_FALSE, TYPES.INT, intOption); intOption.modify(40179); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATE, TYPES.INT, intOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT1, TYPES.INT, intOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT2, TYPES.INT, intOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT1, TYPES.INT, intOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT2, TYPES.INT, intOption); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATE, TYPES.INT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATETIME, TYPES.INT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT1, TYPES.INT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT2, TYPES.INT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT1, TYPES.INT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT2, TYPES.INT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATETIME, TYPES.INT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.ERROR, TYPES.INT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.FORMULA, TYPES.INT, new CellTypeMismatchException("")); // getShortOption? ShortOption shortOption = new ShortOption(); shortOption.modify((short) 0); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_0, TYPES.SMALLINT, shortOption); shortOption.modify((short) 1); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_1, TYPES.SMALLINT, shortOption); shortOption.modify((short) -1); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MINUS1, TYPES.SMALLINT, shortOption); shortOption.modify(Short.MAX_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MAX, TYPES.SMALLINT, shortOption); shortOption.modify(Short.MIN_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MIN, TYPES.SMALLINT, shortOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DECIMAL, TYPES.SMALLINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_OVER_MAX, TYPES.SMALLINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_UNDER_MIN, TYPES.SMALLINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_BIG_VALUE, TYPES.SMALLINT, new NumberFormatException("????")); shortOption.modify((short) 0); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_0, TYPES.SMALLINT, shortOption); shortOption.modify((short) 1); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_1, TYPES.SMALLINT, shortOption); shortOption.modify((short) -1); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MINUS1, TYPES.SMALLINT, shortOption); shortOption.modify(Short.MAX_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MAX, TYPES.SMALLINT, shortOption); shortOption.modify(Short.MIN_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MIN, TYPES.SMALLINT, shortOption); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DECIMAL, TYPES.SMALLINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_OVER_MAX, TYPES.SMALLINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_UNDER_MIN, TYPES.SMALLINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_BIG_VALUE, TYPES.SMALLINT, new CellTypeMismatchException("")); shortOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.BLANK, TYPES.SMALLINT, shortOption); longOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.NULL_STRING, TYPES.SMALLINT, new CellTypeMismatchException("")); shortOption.modify((short) 1); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_TRUE, TYPES.SMALLINT, shortOption); shortOption.modify((short) 0); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_FALSE, TYPES.SMALLINT, shortOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATE, TYPES.SMALLINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT1, TYPES.SMALLINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT2, TYPES.SMALLINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT1, TYPES.SMALLINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT2, TYPES.SMALLINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATE, TYPES.SMALLINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATETIME, TYPES.SMALLINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT1, TYPES.SMALLINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT2, TYPES.SMALLINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT1, TYPES.SMALLINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT2, TYPES.SMALLINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATETIME, TYPES.SMALLINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.ERROR, TYPES.SMALLINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.FORMULA, TYPES.SMALLINT, new CellTypeMismatchException("")); // getByteOption? ByteOption byteOption = new ByteOption(); byteOption.modify((byte) 0); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_0, TYPES.TINYINT, byteOption); byteOption.modify((byte) 1); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_1, TYPES.TINYINT, byteOption); byteOption.modify((byte) -1); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MINUS1, TYPES.TINYINT, byteOption); byteOption.modify(Byte.MAX_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MAX, TYPES.TINYINT, byteOption); byteOption.modify(Byte.MIN_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MIN, TYPES.TINYINT, byteOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DECIMAL, TYPES.TINYINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_OVER_MAX, TYPES.TINYINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_UNDER_MIN, TYPES.TINYINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_BIG_VALUE, TYPES.TINYINT, new NumberFormatException("????")); byteOption.modify((byte) 0); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_0, TYPES.TINYINT, byteOption); byteOption.modify((byte) 1); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_1, TYPES.TINYINT, byteOption); byteOption.modify((byte) -1); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MINUS1, TYPES.TINYINT, byteOption); byteOption.modify(Byte.MAX_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MAX, TYPES.TINYINT, byteOption); byteOption.modify(Byte.MIN_VALUE); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MIN, TYPES.TINYINT, byteOption); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DECIMAL, TYPES.TINYINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_OVER_MAX, TYPES.TINYINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_UNDER_MIN, TYPES.TINYINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_BIG_VALUE, TYPES.TINYINT, new CellTypeMismatchException("")); byteOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.BLANK, TYPES.TINYINT, byteOption); longOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.NULL_STRING, TYPES.TINYINT, new CellTypeMismatchException("")); byteOption.modify((byte) 1); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_TRUE, TYPES.TINYINT, byteOption); byteOption.modify((byte) 0); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_FALSE, TYPES.TINYINT, byteOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATE, TYPES.TINYINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT1, TYPES.TINYINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT2, TYPES.TINYINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT1, TYPES.TINYINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT2, TYPES.TINYINT, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATE, TYPES.TINYINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATETIME, TYPES.TINYINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT1, TYPES.TINYINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT2, TYPES.TINYINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT1, TYPES.TINYINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT2, TYPES.TINYINT, new NumberFormatException("??????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATETIME, TYPES.TINYINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.ERROR, TYPES.TINYINT, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.FORMULA, TYPES.TINYINT, new CellTypeMismatchException("")); // getStringOption? StringOption stringOption = new StringOption(); stringOption.modify("0"); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_0, TYPES.CHAR, stringOption); stringOption.modify("1"); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_1, TYPES.CHAR, stringOption); stringOption.modify("-1"); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MINUS1, TYPES.CHAR, stringOption); stringOption.modify("6.54321"); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DECIMAL, TYPES.CHAR, stringOption); stringOption.modify("9.2233720368547697E18"); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_OVER_MAX, TYPES.CHAR, stringOption); stringOption.modify("-9.2233720368547697E18"); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_UNDER_MIN, TYPES.CHAR, stringOption); stringOption.modify("1.23456789012345E19"); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_BIG_VALUE, TYPES.CHAR, stringOption); stringOption.modify("0"); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_0, TYPES.CHAR, stringOption); stringOption.modify("1"); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_1, TYPES.CHAR, stringOption); stringOption.modify("-1"); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MINUS1, TYPES.CHAR, stringOption); stringOption.modify("6.54321"); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DECIMAL, TYPES.CHAR, stringOption); stringOption.modify("9223372036854775808"); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_OVER_MAX, TYPES.CHAR, stringOption); stringOption.modify("-9223372036854775809"); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_UNDER_MIN, TYPES.CHAR, stringOption); stringOption.modify("12345678901234567890"); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_BIG_VALUE, TYPES.CHAR, stringOption); stringOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.BLANK, TYPES.CHAR, stringOption); stringOption.modify(""); testGetXXXOptionDo(excelUtils, sheet, DATA.NULL_STRING, TYPES.CHAR, stringOption); stringOption.modify("1"); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_TRUE, TYPES.CHAR, stringOption); stringOption.modify("0"); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_FALSE, TYPES.CHAR, stringOption); stringOption.modify("40179"); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATE, TYPES.CHAR, stringOption); stringOption.modify("2010-01-01 00:00:00"); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT1, TYPES.CHAR, stringOption); stringOption.modify("2010-01-01 00:00:00"); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT2, TYPES.CHAR, stringOption); stringOption.modify("2010-01-01 00:00:00"); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT1, TYPES.CHAR, stringOption); stringOption.modify("2010-01-01 00:00:00"); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT2, TYPES.CHAR, stringOption); stringOption.modify("2010-01-01"); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATE, TYPES.CHAR, stringOption); stringOption.modify("40452.50090277778"); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATETIME, TYPES.CHAR, stringOption); stringOption.modify("2010-10-01 12:01:18"); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT1, TYPES.CHAR, stringOption); stringOption.modify("2010-10-01 12:01:18"); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT2, TYPES.CHAR, stringOption); stringOption.modify("2010-10-01 12:01:18"); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT1, TYPES.CHAR, stringOption); stringOption.modify("2010-10-01 12:01:18"); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT2, TYPES.CHAR, stringOption); stringOption.modify("2010-10-01 12:01:18"); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATETIME, TYPES.CHAR, stringOption); testGetXXXOptionDo(excelUtils, sheet, DATA.ERROR, TYPES.CHAR, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.FORMULA, TYPES.CHAR, new CellTypeMismatchException("")); // getDateOption? DateOption dateOption = new DateOption(); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_0, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_1, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MINUS1, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DECIMAL, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_OVER_MAX, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_UNDER_MIN, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_BIG_VALUE, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_0, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_1, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MINUS1, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DECIMAL, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_OVER_MAX, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_UNDER_MIN, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_BIG_VALUE, TYPES.DATE, new CellTypeMismatchException("")); dateOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.BLANK, TYPES.DATE, dateOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NULL_STRING, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_TRUE, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_FALSE, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATE, TYPES.DATE, new CellTypeMismatchException("")); dateOption.modify(DateUtil.getDayFromDate(2010, 1, 1)); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT1, TYPES.DATE, dateOption); dateOption.modify(DateUtil.getDayFromDate(2010, 1, 1)); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT2, TYPES.DATE, dateOption); dateOption.modify(DateUtil.getDayFromDate(2010, 1, 1)); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT1, TYPES.DATE, dateOption); dateOption.modify(DateUtil.getDayFromDate(2010, 1, 1)); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT2, TYPES.DATE, dateOption); dateOption.modify(DateUtil.getDayFromDate(2010, 1, 1)); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATE, TYPES.DATE, dateOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATETIME, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT1, TYPES.DATE, new CellTypeMismatchException("?0???????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT2, TYPES.DATE, new CellTypeMismatchException("?0???????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT1, TYPES.DATE, new CellTypeMismatchException("?0???????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT2, TYPES.DATE, new CellTypeMismatchException("?0???????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATETIME, TYPES.DATE, new CellTypeMismatchException("?0???????????")); testGetXXXOptionDo(excelUtils, sheet, DATA.ERROR, TYPES.DATE, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.FORMULA, TYPES.DATE, new CellTypeMismatchException("")); // getDateTimeOption? DateTimeOption dateTimeOption = new DateTimeOption(); DateTime dateTime = new DateTime(); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_0, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_1, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MINUS1, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DECIMAL, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_OVER_MAX, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_UNDER_MIN, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_BIG_VALUE, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_0, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_1, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MINUS1, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DECIMAL, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_OVER_MAX, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_UNDER_MIN, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_BIG_VALUE, TYPES.DATETIME, new CellTypeMismatchException("")); dateTimeOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.BLANK, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NULL_STRING, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_TRUE, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_FALSE, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATE, TYPES.DATETIME, new CellTypeMismatchException("")); dateTime.setElapsedSeconds(DateUtil.getDayFromDate(2010, 1, 1) * 86400L); dateTimeOption.modify(dateTime); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT1, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT2, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT1, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT2, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATE, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATETIME, TYPES.DATETIME, new CellTypeMismatchException("")); dateTime.setElapsedSeconds( DateUtil.getDayFromDate(2010, 10, 1) * 86400L + DateUtil.getSecondFromTime(12, 1, 18)); dateTimeOption.modify(dateTime); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT1, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT2, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT1, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT2, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATETIME, TYPES.DATETIME, dateTimeOption); testGetXXXOptionDo(excelUtils, sheet, DATA.ERROR, TYPES.DATETIME, new CellTypeMismatchException("")); testGetXXXOptionDo(excelUtils, sheet, DATA.FORMULA, TYPES.DATETIME, new CellTypeMismatchException("")); // getDecimalOption? DecimalOption decimalOption = new DecimalOption(); decimalOption.modify(new BigDecimal("0")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_0, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("1")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_1, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("-1")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MINUS1, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("999999999999999")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MAX, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("-999999999999999")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_MIN, TYPES.DECIMAL, decimalOption); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DECIMAL, TYPES.DECIMAL, new NumberFormatException("???DECIMAL???????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_OVER_MAX, TYPES.DECIMAL, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_UNDER_MIN, TYPES.DECIMAL, new NumberFormatException("????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_BIG_VALUE, TYPES.DECIMAL, new NumberFormatException("????")); decimalOption.modify(new BigDecimal("0")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_0, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("1")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_1, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("-1")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_MINUS1, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("6.54321")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DECIMAL, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("9223372036854775808")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_OVER_MAX, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("-9223372036854775809")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_UNDER_MIN, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("12345678901234567890")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_BIG_VALUE, TYPES.DECIMAL, decimalOption); decimalOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.BLANK, TYPES.DECIMAL, decimalOption); longOption.setNull(); testGetXXXOptionDo(excelUtils, sheet, DATA.NULL_STRING, TYPES.DECIMAL, new NumberFormatException("DECIMAL???????")); decimalOption.modify(new BigDecimal("1")); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_TRUE, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("0")); testGetXXXOptionDo(excelUtils, sheet, DATA.BOOL_FALSE, TYPES.DECIMAL, decimalOption); decimalOption.modify(new BigDecimal("40179")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATE, TYPES.DECIMAL, decimalOption); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT1, TYPES.DECIMAL, new CellTypeMismatchException("DECIMAL")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATE_FMT2, TYPES.DECIMAL, new CellTypeMismatchException("DECIMAL")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT1, TYPES.DECIMAL, new CellTypeMismatchException("DECIMAL")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATE_DATETIME_FIMT2, TYPES.DECIMAL, new CellTypeMismatchException("DECIMAL")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATE, TYPES.DECIMAL, new NumberFormatException("DECIMAL???????")); testGetXXXOptionDo(excelUtils, sheet, DATA.NUMERIC_DATETIME, TYPES.DECIMAL, new NumberFormatException("???DECIMAL???????")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT1, TYPES.DECIMAL, new CellTypeMismatchException("DECIMAL")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATE_FMT2, TYPES.DECIMAL, new CellTypeMismatchException("DECIMAL")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT1, TYPES.DECIMAL, new CellTypeMismatchException("DECIMAL")); testGetXXXOptionDo(excelUtils, sheet, DATA.DATETIME_DATETIME_FIMT2, TYPES.DECIMAL, new CellTypeMismatchException("DECIMAL")); testGetXXXOptionDo(excelUtils, sheet, DATA.STRING_DATETIME, TYPES.DECIMAL, new NumberFormatException("DECIMAL???????")); testGetXXXOptionDo(excelUtils, sheet, DATA.ERROR, TYPES.DECIMAL, new CellTypeMismatchException("DECIMAL")); testGetXXXOptionDo(excelUtils, sheet, DATA.FORMULA, TYPES.DECIMAL, new CellTypeMismatchException("DECIMAL")); }
From source file:com.assentisk.controller.OrganizationController.java
private ModelAndView importEmploye(HttpServletRequest req, HttpServletResponse res) throws Exception { System.out.println("hiiii heree......."); MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; MultipartFile multipartFile = multipartRequest.getFile("myexcel"); String fileName = multipartFile.getOriginalFilename(); map = new ModelMap(); String name = req.getParameter("fileName"); int status = 0; java.util.Date dt = new java.util.Date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String currentTime = sdf.format(dt); InetAddress ip = InetAddress.getLocalHost(); String userId = (String) req.getSession().getAttribute("UserRecordID"); byte[] b = multipartFile.getBytes(); FileOutputStream fout = new FileOutputStream(new File(fileName)); fout.write(b);/*from w w w .j a va2 s .c o m*/ fout.flush(); fout.close(); try { String DepartmentID = "", LocationID = "", EmpName = "", Email1 = "", Email2 = "", address = ""; String city = "", zip = "", phone1 = "", locCoun = "", locstate = "", loccity = "", locContact = "", locEmail = ""; String phone2 = "", divisions = "", email2 = "", username = "", reporting = "", photo = ""; int isLicense = 0; int isactive = 1; int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; String[] data = null; List<String> dataStatus = new ArrayList<String>(); int val = 0; //Reading .xls files if (fileName.toLowerCase().endsWith(".xls")) { // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { while (rowIterator.hasNext()) { short cellIndex = 0; HSSFRow hsrow = (HSSFRow) rowIterator.next(); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { EmpName = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { EmpName = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); val++; } try { DepartmentID = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { DepartmentID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); val++; } try { LocationID = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { LocationID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Location -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); val++; } try { locCoun = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { locCoun = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Country - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country- " + e.getMessage()); val++; } try { locstate = hsrow.getCell((short) 4).getStringCellValue(); } catch (Exception e) { locstate = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location State - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State- " + e.getMessage()); val++; } try { loccity = hsrow.getCell((short) 5).getStringCellValue(); } catch (Exception e) { loccity = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location City -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City- " + e.getMessage()); val++; } try { locContact = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { locContact = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Contact Name -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name- " + e.getMessage()); val++; } try { locEmail = hsrow.getCell((short) 7).getStringCellValue(); boolean isValid = false; try { // // Create InternetAddress object and validated the supplied // address which is this case is an email address. InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { locEmail = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Email -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); val++; } try { divisions = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { divisions = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); val++; } try { address = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { address = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); val++; } try { city = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); val++; } try { zip = String.valueOf(hsrow.getCell((short) 11).getNumericCellValue()); zip = String.valueOf(zip).split("\\.")[0]; } catch (Exception e) { zip = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone1 = String.valueOf(hsrow.getCell((short) 12).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 12)); phone1 = df2; phone1 = String.valueOf(phone1).split("\\.")[0]; } catch (Exception e) { phone1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone2 = String.valueOf(hsrow.getCell((short) 13).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 13)); phone2 = df2; System.out.println("check phone1" + phone2); phone2 = String.valueOf(phone2).split("\\.")[0]; } catch (Exception e) { phone2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 - " + e.getMessage()); val++; } try { boolean isValid = false; Email1 = hsrow.getCell((short) 14).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); val++; } try { boolean isValid = false; Email2 = hsrow.getCell((short) 15).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); val++; } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + hsrow.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + hsrow.getRowNum() + "Not Inserted Row : " + EmpName); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } } //Reading .xlsx files else if (fileName.toLowerCase().endsWith(".xlsx")) { // Get the workbook instance for XLS file XSSFWorkbook wBook = new XSSFWorkbook(file); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cite = row.cellIterator(); if (row.getRowNum() != 0) { while (cite.hasNext()) { Cell c = cite.next(); if (c.getColumnIndex() == 0) { try { EmpName = c.toString(); } catch (Exception e) { EmpName = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": EmpName - " + e.getMessage()); } } if (c.getColumnIndex() == 1) { try { DepartmentID = c.toString(); } catch (Exception e) { DepartmentID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Department - " + e.getMessage()); } } if (c.getColumnIndex() == 2) { try { LocationID = c.toString(); } catch (Exception e) { LocationID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Location -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { locCoun = c.toString(); } catch (Exception e) { locCoun = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Country - " + e.getMessage()); } } if (c.getColumnIndex() == 4) { try { locstate = c.toString(); } catch (Exception e) { locstate = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location State - " + e.getMessage()); } } if (c.getColumnIndex() == 5) { try { loccity = c.toString(); } catch (Exception e) { loccity = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location City -" + e.getMessage()); } } if (c.getColumnIndex() == 6) { try { locContact = c.toString(); } catch (Exception e) { locContact = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Contact Name -" + e.getMessage()); } } if (c.getColumnIndex() == 7) { try { locEmail = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": locEmail - " + e.getMessage()); } } catch (Exception e) { locEmail = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Email -" + e.getMessage()); } } if (c.getColumnIndex() == 8) { try { divisions = c.toString(); } catch (Exception e) { divisions = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Divisions - " + e.getMessage()); } } if (c.getColumnIndex() == 9) { try { address = c.toString(); } catch (Exception e) { address = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": address - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { city = c.toString(); } catch (Exception e) { city = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": city - " + e.getMessage()); } } if (c.getColumnIndex() == 11) { try { zip = c.toString(); } catch (Exception e) { zip = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": zip - " + e.getMessage()); } } if (c.getColumnIndex() == 12) { try { DataFormatter formatter = new DataFormatter(); phone1 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone1 = df2; } catch (Exception e) { phone1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone1 - " + e.getMessage()); } } if (c.getColumnIndex() == 13) { try { DataFormatter formatter = new DataFormatter(); phone2 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone2 = df2; } catch (Exception e) { phone2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone2 -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { Email1 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { Email2 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + row.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + row.getRowNum() + "Not Inserted Row EmpName : " + EmpName); notcount = notcount + 1; } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { dataStatus.add(notcount + " Rows are not inserted"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } if (dataStatus.size() > 0) { map.addObject("dataStatus", dataStatus); map.addObject("datasize", "true"); } else { map.addObject("datasize", "false"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } catch (FileNotFoundException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (IOException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (Exception e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } return new ModelAndView("organization/employees", map); }
From source file:com.assentisk.controller.OrganizationController.java
private ModelAndView importAssets(HttpServletRequest req, HttpServletResponse res) throws Exception { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; MultipartFile multipartFile = multipartRequest.getFile("myexcel"); String fileName = multipartFile.getOriginalFilename(); map = new ModelMap(); String name = req.getParameter("fileName"); int status = 0; java.util.Date dt = new java.util.Date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String currentTime = sdf.format(dt); InetAddress ip = InetAddress.getLocalHost(); String userId = (String) req.getSession().getAttribute("UserRecordID"); byte[] b = multipartFile.getBytes(); FileOutputStream fout = new FileOutputStream(new File(fileName)); fout.write(b);//from w w w . j a va 2 s. c o m fout.flush(); fout.close(); try { String Assets = "", category = "", location = "", application = "", business = ""; String initial = "", quantity = "", country = "", state = "", city = "", contact = "", email = ""; List<String> dataStatus = new ArrayList<String>(); int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; // Reading .xls files if (fileName.toLowerCase().endsWith(".xls")) { // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { while (rowIterator.hasNext()) { short cellIndex = 0; HSSFRow hsrow = (HSSFRow) rowIterator.next(); System.out.println("check this...hsrow." + hsrow.getRowNum()); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { Assets = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { Assets = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": AssetName - " + e.getMessage()); } } try { category = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { category = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Category - " + e.getMessage()); } } try { application = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { application = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Application - " + e.getMessage()); } } try { business = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { business = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": BusinessProcess - " + e.getMessage()); } } try { initial = String.valueOf(hsrow.getCell((short) 4).getNumericCellValue()); initial = String.valueOf(initial).split("\\.")[0]; } catch (Exception e) { initial = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": InitialValue - " + e.getMessage()); } } try { quantity = String.valueOf(hsrow.getCell((short) 5).getNumericCellValue()); quantity = String.valueOf(quantity).split("\\.")[0]; } catch (Exception e) { quantity = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Quantity - " + e.getMessage()); } } try { location = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { location = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); } } try { country = hsrow.getCell((short) 7).getStringCellValue(); } catch (Exception e) { country = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country - " + e.getMessage()); } } try { state = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { state = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State - " + e.getMessage()); } } try { city = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City - " + e.getMessage()); } } try { contact = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { contact = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name - " + e.getMessage()); } } try { email = hsrow.getCell((short) 11).getStringCellValue(); boolean isValid = false; try { // // Create InternetAddress object and // validated the supplied // address which is this case is an email // address. InternetAddress internetAddress = new InternetAddress(email); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { email = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email - " + e.getMessage()); } } if (!location.equals("")) { location = organizationDao.getLocIdByName(location, currentTime, Integer.parseInt(userId), country, state, city, contact, email); } else { location = "0"; } if (!application.equals("")) { application = organizationDao.getAppByName(application, currentTime, Integer.parseInt(userId)); } else { application = "0"; } if (!business.equals("")) { business = organizationDao.getBusinessByName(business, currentTime, Integer.parseInt(userId)); } else { business = "0"; } if (!category.equals("")) { category = organizationDao.getCategoryByName(category, currentTime, Integer.parseInt(userId)); } else { category = ""; } if (!category.equals("") && !Assets.equals("")) { String duplicate = organizationDao.saveAssetData(Assets, category, location, application, business, "", currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), "", "", initial, quantity); masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime, Integer.parseInt(userId), ip.getHostAddress()); if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + hsrow.getRowNum()); } else { count = count + 1; } } else { System.out.println("Error Row : " + hsrow.getRowNum() + "Not Inserted Row Asset Name : " + Assets); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } } // Reading .xlsx files else if (fileName.toLowerCase().endsWith(".xlsx")) { // Get the workbook instance for XLS file XSSFWorkbook wBook = new XSSFWorkbook(file); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cite = row.cellIterator(); if (row.getRowNum() != 0) { while (cite.hasNext()) { Cell c = cite.next(); if (c.getColumnIndex() == 0) { try { Assets = c.toString(); } catch (Exception e) { Assets = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": AssetName - " + e.getMessage()); } } } if (c.getColumnIndex() == 1) { try { category = c.toString(); } catch (Exception e) { category = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Category - " + e.getMessage()); } } } if (c.getColumnIndex() == 2) { try { application = c.toString(); } catch (Exception e) { application = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Application - " + e.getMessage()); } } } if (c.getColumnIndex() == 3) { try { business = c.toString(); } catch (Exception e) { business = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": BusinessProcess - " + e.getMessage()); } } } if (c.getColumnIndex() == 4) { try { initial = c.toString(); } catch (Exception e) { initial = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": InitialValue - " + e.getMessage()); } } } if (c.getColumnIndex() == 5) { try { quantity = c.toString(); } catch (Exception e) { quantity = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Quantity - " + e.getMessage()); } } } if (c.getColumnIndex() == 6) { try { location = c.toString(); } catch (Exception e) { location = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location - " + e.getMessage()); } } } if (c.getColumnIndex() == 7) { try { country = c.toString(); } catch (Exception e) { country = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Country - " + e.getMessage()); } } } if (c.getColumnIndex() == 8) { try { state = c.toString(); } catch (Exception e) { state = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location State - " + e.getMessage()); } } } if (c.getColumnIndex() == 9) { try { city = c.toString(); } catch (Exception e) { city = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location City - " + e.getMessage()); } } } if (c.getColumnIndex() == 10) { try { contact = c.toString(); } catch (Exception e) { contact = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Contact Name - " + e.getMessage()); } } } if (c.getColumnIndex() == 11) { try { email = c.toString(); boolean isValid = false; try { // // Create InternetAddress object and // validated the supplied // address which is this case is an // email address. InternetAddress internetAddress = new InternetAddress(email); internetAddress.validate(); isValid = true; } catch (AddressException e) { if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Email- " + e.getMessage()); } } } catch (Exception e) { email = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Email- " + e.getMessage()); } } } } if (!location.equals("")) { location = organizationDao.getLocIdByName(location, currentTime, Integer.parseInt(userId), country, state, city, contact, email); } else { location = "0"; } if (!application.equals("")) { application = organizationDao.getAppByName(application, currentTime, Integer.parseInt(userId)); } else { application = "0"; } if (!business.equals("")) { business = organizationDao.getBusinessByName(business, currentTime, Integer.parseInt(userId)); } else { business = "0"; } if (!category.equals("")) { category = organizationDao.getCategoryByName(category, currentTime, Integer.parseInt(userId)); } else { category = ""; } if (!category.equals("") && !Assets.equals("")) { String duplicate = organizationDao.saveAssetData(Assets, category, location, application, business, "", currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), "", "", initial, quantity); masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime, Integer.parseInt(userId), ip.getHostAddress()); if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + row.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + row.getRowNum() + "Not Inserted Row Asset Name : " + Assets); } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } if (dataStatus.size() > 0) { map.addObject("dataStatus", dataStatus); map.addObject("datasize", "true"); } else { map.addObject("datasize", "false"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } catch (FileNotFoundException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (IOException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (Exception e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } return new ModelAndView("assests/assets", map); }
From source file:com.aurel.track.exchange.excel.ExcelFieldMatchBL.java
License:Open Source License
/** * Get the workbook and returns its sheets * @param excelMappingsDirectory/*from w w w. j a va2s. c o m*/ * @param fileName * @return Map<SheetID, SheetName> */ static Workbook loadWorkbook(String excelMappingsDirectory, String fileName) { FileInputStream inputStream = null; try { inputStream = new FileInputStream(new File(excelMappingsDirectory, fileName)); } catch (FileNotFoundException e) { LOGGER.warn("Loading the workbook from directory " + excelMappingsDirectory + " and file " + fileName + " failed with " + e.getMessage()); LOGGER.debug(ExceptionUtils.getStackTrace(e)); return null; } try { if (fileName.endsWith("xls") || fileName.endsWith("XLS")) { return new HSSFWorkbook(inputStream); } else { if (fileName.endsWith("xlsx") || fileName.endsWith("XLSX")) { return new XSSFWorkbook(inputStream); } } } catch (IOException e) { LOGGER.warn("Getting the excel sheets failed with " + e.getMessage()); LOGGER.debug(ExceptionUtils.getStackTrace(e)); } return null; }
From source file:com.aurel.track.lucene.util.poi.XLSTextStripper.java
License:Open Source License
public XLSTextStripper(FileInputStream fis, String fileExtension) { try {/*from w ww . j a va2 s . c om*/ StringBuffer sb = new StringBuffer(); Workbook workbook = null; if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLS.equalsIgnoreCase(fileExtension)) { workbook = new HSSFWorkbook(fis); } else { if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLSX.equalsIgnoreCase(fileExtension)) { workbook = new XSSFWorkbook(fis); } } if (workbook != null) { int numOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellStringValue = null; if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { boolean booleanValue = cell.getBooleanCellValue(); cellStringValue = Boolean.toString(booleanValue); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { double doubleValue = cell.getNumericCellValue(); cellStringValue = Double.toString(doubleValue); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellStringValue = cell.getStringCellValue(); } if (cellStringValue != null) { sb.append(cellStringValue); sb.append("\t"); } } sb.append("\n"); } } } _text = sb.toString(); } catch (Exception e) { LOGGER.error(ExceptionUtils.getStackTrace(e)); } }