List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook(InputStream s) throws IOException
From source file:com.codecrate.shard.transfer.excel.ExcelObjectImporter.java
License:Apache License
public Collection importObjects(File file, ProgressMonitor progress) { Collection results = new ArrayList(); try {//from w ww . j av a2 s . com POIFSFileSystem poifs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook workbook = new HSSFWorkbook(poifs); HSSFSheet sheet = workbook.getSheetAt(0); int firstRow = sheet.getFirstRowNum() + 1; int lastRow = sheet.getLastRowNum(); progress.startTask("Import objects from " + file.getName(), lastRow - firstRow); for (int currentRow = firstRow; currentRow <= lastRow; currentRow++) { HSSFRow row = sheet.getRow(currentRow); try { Object result = rowHandler.handleRow(row); results.add(result); } catch (Exception e) { LOG.error("Error importing row: " + currentRow, e); } progress.completeUnitOfWork(); } } catch (Exception e) { LOG.error("Error importing file: " + file, e); } progress.finish(); return results; }
From source file:com.codequicker.quick.templates.source.adapters.ExcelSourceAdapter.java
License:Apache License
private void readOleBasedData(BufferedInputStream bufferedStream, Map<String, List<Map<String, String>>> data) { try {/*from ww w . ja va 2s . c om*/ HSSFWorkbook workbook = new HSSFWorkbook(bufferedStream); int sheetCount = workbook.getNumberOfSheets(); for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { HSSFSheet sheet = workbook.getSheetAt(sheetIndex); Cell cell = null; List<Map<String, String>> sheetData = new ArrayList<Map<String, String>>(); int lastRowNumber = sheet.getLastRowNum(); for (int rowIndex = 0; rowIndex <= lastRowNumber; rowIndex++) { HSSFRow row = sheet.getRow(rowIndex); if (row == null) { continue; } Map<String, String> columnData = new HashMap<String, String>(); for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) { cell = row.getCell(cellIndex, Row.CREATE_NULL_AS_BLANK); columnData.put("column" + (cellIndex + 1), cell.toString()); } sheetData.add(columnData); } data.put("sheet" + (sheetIndex + 1), sheetData); } } catch (Exception e) { throw new RuntimeException(e); } }
From source file:com.codesnippets4all.jthunder.extension.plugins.input.excel.ExcelReaderPlugin.java
License:Apache License
@SuppressWarnings("rawtypes") private void readOLEbasedExcel(BufferedInputStream bufferedStream, List<Sheet> sheets) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(bufferedStream); int sheetCount = workbook.getNumberOfSheets(); for (int index = 0; index < sheetCount; index++) { HSSFSheet sheet = workbook.getSheetAt(index); Sheet s = new Sheet(); sheets.add(s);/*www . j a va2 s . c o m*/ int lastRowNumber = sheet.getLastRowNum(); for (int rowIndex = 0; rowIndex < lastRowNumber; rowIndex++) { HSSFRow row = sheet.getRow(rowIndex); Record record = new Record(); s.addRecord(record); Iterator it = row.cellIterator(); while (it.hasNext()) { record.addCellValue(it.next()); } } } }
From source file:com.compomics.cell_coord.parser.impl.XLSFileParser.java
@Override public Sample parseTrackFile(File trackFile) throws FileParserException { List<Track> list = new ArrayList<>(); // create a new sample object -- watch out to set the relationships! Sample sample = new Sample(trackFile.getName()); try {//from w w w . j ava 2s.c o m FileInputStream fileInputStream = new FileInputStream(trackFile); Workbook workbook = null; // xls extension if (trackFile.getName().endsWith("xls")) { workbook = new HSSFWorkbook(fileInputStream); } else if (trackFile.getName().endsWith("xlsx")) { // xlsx extension workbook = new XSSFWorkbook(fileInputStream); } if (workbook != null) { // check that at least one sheet is present if (workbook.getNumberOfSheets() > 0) { Track currentTrack = null; List<TrackSpot> currentTrackPointList = new ArrayList<>(); Long currentId = 0L; Sheet sheet = workbook.getSheetAt(0); // iterate through all the rows, starting from the second one to skip the header for (int i = 1; i < sheet.getLastRowNum() + 1; i++) { // get the row Row row = sheet.getRow(i); // check the track id Long trackid = (long) row.getCell(0).getNumericCellValue(); if (!Objects.equals(currentId, trackid)) { currentTrack = new Track(); currentTrack.setTrackid(trackid); list.add(currentTrack); currentId = trackid; currentTrackPointList = new ArrayList<>(); } // create new Track Spot object Long spotid = (long) row.getCell(1).getNumericCellValue(); double x = row.getCell(2).getNumericCellValue(); double y = row.getCell(3).getNumericCellValue(); double time = row.getCell(4).getNumericCellValue(); TrackSpot trackSpot = new TrackSpot(spotid, x, y, time, currentTrack); currentTrackPointList.add(trackSpot); currentTrack.setTrackSpots(currentTrackPointList); currentTrack.setSample(sample); } } else { throw new FileParserException( "It seems an Excel file does not have any sheets!\nPlease check your files!"); } } else { throw new FileParserException("The parser did not find a single workbook!\nCheck your files!!"); } } catch (IOException ex) { LOG.error(ex.getMessage(), ex); } catch (NumberFormatException ex) { LOG.error(ex.getMessage(), ex); throw new FileParserException( "It seems like a line does not contain a number!\nPlease check your files!"); } sample.setTracks(list); return sample; }
From source file:com.concursive.connect.web.modules.plans.utils.AssignmentExcelImporter.java
License:Open Source License
/** * Description of the Method//www . j av a2 s . c o m * * @param requirement Description of the Parameter * @param db Description of the Parameter * @param buffer Description of the Parameter * @return Description of the Return Value * @throws java.sql.SQLException Description of the Exception */ public static boolean parse(byte[] buffer, Requirement requirement, Connection db) throws SQLException { if (System.getProperty("DEBUG") != null) { System.out.println("AssignmentExcelImporter-> parseExcel"); } try { db.setAutoCommit(false); // stream the Excel Spreadsheet from the uploaded byte array POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(buffer)); HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); // get the first sheet HSSFSheet sheet = hssfworkbook.getSheetAt(0); // define objects for housing spreadsheet data HSSFRow currentRow = sheet.getRow(0); // parse each row, create and insert into a new requirement with a tree int rows = sheet.getPhysicalNumberOfRows(); if (System.getProperty("DEBUG") != null) { System.out.println("AssignmentExcelImporter-> Number of rows: " + rows); } // Columns int columnHeader = -1; int columnMax = -1; boolean columnItemComplete = false; short itemColumn = -1; short priorityColumn = -1; short assignedToColumn = -1; short effortColumn = -1; short startColumn = -1; short endColumn = -1; // parse for (int r = 0; r < rows; r++) { currentRow = sheet.getRow(r); if (currentRow != null) { // Search for header if (columnHeader == -1) { int cells = currentRow.getPhysicalNumberOfCells(); for (short c = 0; c < cells; c++) { HSSFCell cell = currentRow.getCell(c); if (cell != null) { if ("Item".equals(getValue(cell))) { columnHeader = r; itemColumn = c; columnMax = c; } else if (itemColumn > -1 && !columnItemComplete && c > itemColumn) { if ("".equals(getValue(cell))) { columnMax = c; } else if (!"".equals(getValue(cell))) { columnItemComplete = true; } } if ("Priority".equals(getValue(cell))) { columnHeader = r; priorityColumn = c; } else if ("Assigned To".equals(getValue(cell))) { columnHeader = r; assignedToColumn = c; } else if ("Lead".equals(getValue(cell))) { columnHeader = r; assignedToColumn = c; } else if ("Effort".equals(getValue(cell))) { columnHeader = r; effortColumn = c; } else if ("Start".equals(getValue(cell))) { columnHeader = r; startColumn = c; } else if ("End".equals(getValue(cell))) { columnHeader = r; endColumn = c; } } } } // Process each column if (columnHeader > -1 && r > columnHeader) { boolean gotOne = false; Assignment assignment = new Assignment(); assignment.setProjectId(requirement.getProjectId()); assignment.setRequirementId(requirement.getId()); // Activities and folders if (itemColumn > -1) { // Get the first indent level that has data for (short c = itemColumn; c <= columnMax; c++) { HSSFCell cell = currentRow.getCell(c); if (cell != null && !"".equals(getValue(cell))) { assignment.setRole(getValue(cell)); assignment.setIndent(c); gotOne = true; break; } } } if (gotOne) { // Priority if (priorityColumn > -1) { HSSFCell cell = currentRow.getCell(priorityColumn); if (cell != null) { assignment.setPriorityId(getValue(cell)); } } // Effort if (effortColumn > -1) { HSSFCell cell = currentRow.getCell(effortColumn); if (cell != null) { assignment.setEstimatedLoe(getValue(cell)); if (assignment.getEstimatedLoeTypeId() == -1) { assignment.setEstimatedLoeTypeId(2); } } } // Assigned To if (assignedToColumn > -1) { HSSFCell cell = currentRow.getCell(assignedToColumn); if (cell != null) { assignment.addUsers(getValue(cell)); } } // Start Date if (startColumn > -1) { HSSFCell cell = currentRow.getCell(startColumn); if (cell != null) { assignment.setEstStartDate(getDateValue(cell)); } } // Due Date if (endColumn > -1) { HSSFCell cell = currentRow.getCell(endColumn); if (cell != null) { assignment.setDueDate(getDateValue(cell)); } } assignment.setEnteredBy(requirement.getEnteredBy()); assignment.setModifiedBy(requirement.getModifiedBy()); assignment.setStatusId(1); // Make sure a valid priority is set if (assignment.getPriorityId() < 1 || assignment.getPriorityId() > 3) { assignment.setPriorityId(2); } // Make sure user is on team, before adding, else unset the field if (!assignment.hasValidTeam(db)) { assignment.getAssignedUserList().clear(); } // Insert the assignment assignment.insert(db); if (System.getProperty("DEBUG") != null) { System.out.println( "AssignmentExcelImporter-> Assignment Inserted: " + assignment.getId()); } } } } } db.commit(); } catch (Exception e) { db.rollback(); e.printStackTrace(System.out); return false; } finally { db.setAutoCommit(true); } return true; }
From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java
License:Apache License
/** * Validates the reader-config.xml with the Excel file * * @param vcConfig The validator configuration object. * @param filename Name of the Excel file. * @param dDoc Document conatins the request. * @param iResultNode The record XML structure root node, or zero, if only validation is needed. * @param sheetno Sheet index of the Excel file. * @param startrow row index from which data to be read. * @param endrow row index upto which data to be read. * @param lErrorList LinkedList contains all the errors. *//* www . java 2 s . co m*/ public static void validate(ValidatorConfig vcConfig, String filename, Document dDoc, int iResultNode, int sheetno, int startrow, int endrow, List<FileException> lErrorList) { try { setRecordsread(0); setEndoffile(false); Workbook book = null; Sheet sheet = null; Row row; FileInputStream fileinp = null; //String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName; int sheetindex; int noofsheets; if (filename == null) { throw new FileException(LogMessages.PLEASE_PROVIDE_FILE_NAME); } File file = new File(filename); fileinp = new FileInputStream(filename); if (file.exists()) { if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) { try { book = (Workbook) new HSSFWorkbook(fileinp); } catch (IOException ex) { Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex); } } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) { try { book = new XSSFWorkbook(fileinp); } catch (IOException ex) { Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex); } } else { //ERROR fileinp.close(); throw new FileException(LogMessages.INPUT_FILE_NOT_SUPPORTED); } } else { //ERROR fileinp.close(); throw new FileException(LogMessages.FILE_NOT_FOUND); } if (sheetno != -1) { sheetindex = sheetno; noofsheets = sheetindex + 1; } else { sheetindex = 0; noofsheets = book.getNumberOfSheets(); } //check whether the sheetindex exists or not for (; sheetindex < noofsheets; sheetindex++) { if (sheetindex >= book.getNumberOfSheets()) { //no sheet throw new FileException(LogMessages.NO_SHEET_FOUND, sheetindex); } sheet = book.getSheetAt(sheetindex); if (sheet == null) { throw new FileException(LogMessages.NO_SHEET_FOUND, sheetindex); } } //validate columns //get last column index for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); if (maxcol < row.getLastCellNum()) { maxcol = row.getLastCellNum(); } } //check column index in reader-config ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList.listIterator(); while (fieldslist.hasNext()) { FieldType excelfields = (FieldType) fieldslist.next(); try { if (Short.parseShort(excelfields.sColumnIndex) < 0 || Short.parseShort(excelfields.sColumnIndex) >= maxcol) { throw new FileException(LogMessages.COLUMN_INDEX_NOT_FOUND, excelfields.sColumnIndex, (maxcol - 1)); } } catch (NumberFormatException ex) { throw new FileException(ex, LogMessages.COLUMN_INDEX_NOT_VALID, excelfields.sColumnIndex); } } if (endrow == -1) { endrow = sheet.getLastRowNum(); if (startrow == -1) { startrow = 0; } } else { endrow = startrow + endrow - 1; if (endrow > sheet.getLastRowNum()) { endrow = sheet.getLastRowNum(); } } setRecordsread(endrow - startrow + 1); } catch (IOException ex) { lErrorList.add(new FileException(ex, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename)); } catch (FileException ex) { lErrorList.add(ex); } }
From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java
License:Apache License
/** * Read records from Excel file/* w w w . ja v a 2 s. com*/ * * @param vcConfig The validator configuration object. * @param bUseTupleOld * @param filename Name of the Excel file. * @param doc Document conatins the request. * @param iResponsenode The record XML structure root node, or zero, if only validation is needed. * @param sheetno Sheet index of the Excel file. * @param startrow row index from which data to be read. * @param endrow row index upto which data to be read. * @param startcolumn column index from which data to be read. * @param endcolumn column index upto which data to be read. */ public static void readall(ValidatorConfig vcConfig, Boolean bUseTupleOld, String filename, Document doc, int iResponsenode, int sheetno, int startrow, int endrow, int startcolumn, int endcolumn) throws FileException { Workbook book = null; Sheet sheet; Cell cell; Row row; FileInputStream fileinp = null; String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName; try { int iRow, iCol, sheetindex, noofsheets; File file = new File(filename); fileinp = new FileInputStream(filename); if (file.exists()) { if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) { book = (Workbook) new HSSFWorkbook(fileinp); } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) { book = new XSSFWorkbook(fileinp); } else { //ERROR fileinp.close(); } } else { //ERROR fileinp.close(); } if (sheetno != -1) { sheetindex = sheetno; noofsheets = sheetindex + 1; } else { sheetindex = 0; noofsheets = book.getNumberOfSheets(); } for (; sheetindex < noofsheets; sheetindex++) { sheet = book.getSheetAt(sheetindex); if (endrow == -1) { endrow = sheet.getLastRowNum(); if (startrow == -1) { startrow = 0; } } else { endrow = startrow + endrow - 1; if (endrow > sheet.getLastRowNum()) { endrow = sheet.getLastRowNum(); } } if (endcolumn == -1) { endcolumn = 30; if (startcolumn == -1) { startcolumn = 0; } } for (int i = startrow; i <= endrow; i++) { row = sheet.getRow(i); if (row == null) { int iTup = doc.createElement("tuple", iResponsenode); if (bUseTupleOld) { iTup = doc.createElement("old", iTup); } iRow = doc.createElement(sRecordName, iTup); //Node.setAttribute(iRow, "id", "" + i); ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList .listIterator(); while (fieldslist.hasNext()) { FieldType excelfields = (FieldType) fieldslist.next(); String sColumnName = excelfields.sFieldName; iCol = doc.createTextElement(sColumnName, "", iRow); } continue; } int iTup = doc.createElement("tuple", iResponsenode); if (bUseTupleOld) { iTup = doc.createElement("old", iTup); } iRow = doc.createElement(sRecordName, iTup); ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList .listIterator(); while (fieldslist.hasNext()) { FieldType excelfields = (FieldType) fieldslist.next(); int iColumnIndex = Integer.parseInt(excelfields.sColumnIndex); cell = row.getCell(iColumnIndex); String sColumnName = excelfields.sFieldName; if (cell == null) { iCol = doc.createTextElement(sColumnName, "", iRow); continue; } switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: iCol = doc.createTextElement(sColumnName, "", iRow); break; case Cell.CELL_TYPE_BOOLEAN: iCol = doc.createTextElement(sColumnName, "" + cell.getBooleanCellValue(), iRow); break; case Cell.CELL_TYPE_ERROR: iCol = doc.createTextElement(sColumnName, "", iRow); break; case Cell.CELL_TYPE_FORMULA: iCol = doc.createTextElement(sColumnName, "" + cell.getCellFormula(), iRow); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat simpledateformat = new SimpleDateFormat( "yyyy-MM-dd 'T' HH:mm:ss.S"); iCol = doc.createTextElement(sColumnName, "" + simpledateformat.format(cell.getDateCellValue()), iRow); } else { iCol = doc.createTextElement(sColumnName, "" + cell.getNumericCellValue(), iRow); } break; case Cell.CELL_TYPE_STRING: iCol = doc.createTextElement(sColumnName, "" + cell.getStringCellValue(), iRow); break; default: System.out.println("default"); } } } } } catch (FileNotFoundException e) { throw new FileException(e, LogMessages.FILE_NOT_FOUND); } catch (IOException e) { throw new FileException(e, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename); } finally { try { fileinp.close(); } catch (IOException ex) { Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:com.cseur.utils.ExcelReader.java
public ArrayList<ArrayList<String>> readToMap() throws IOException { FileInputStream file = new FileInputStream(xlsFile); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //get pol pod via1 via2 via3 via4 index in column and saved to array ArrayList<Integer> portIndexs = new ArrayList(Arrays.asList(null, null, null, null, null, null, null)); boolean isTableHeader = true; //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { //read file, find the columnindex for required order, and then insert the value to valueArray. Row row = rowIterator.next();//www .java 2 s . c o m // //escape empty lines Iterator<Cell> cellIterator = row.cellIterator(); if (isTableHeader) { while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //set absolute columnIndex in the order of POL, POD, VIA1.2.l.3.4 switch (cell.getStringCellValue().trim().toUpperCase()) { case "POL": portIndexs.set(0, cell.getColumnIndex()); break; case "POD": portIndexs.set(1, cell.getColumnIndex()); break; case "VIA1": portIndexs.set(2, cell.getColumnIndex()); break; case "VIA2": portIndexs.set(3, cell.getColumnIndex()); break; case "VIA3": portIndexs.set(4, cell.getColumnIndex()); break; case "VIA4": portIndexs.set(5, cell.getColumnIndex()); break; // case "VIA5": // portIndexs.add(cell.getColumnIndex()); // break; // case "VIA6": // portIndexs.add(cell.getColumnIndex()); // break; // case "VIA7": // portIndexs.add(cell.getColumnIndex()); // break; // case "VIA8": // portIndexs.add(cell.getColumnIndex()); // break; // case "VIA9": // portIndexs.add(cell.getColumnIndex()); // break; // case "VIA0": // portIndexs.add(cell.getColumnIndex()); // break; } // portIndexs.trimToSize(); } } if (!isTableHeader) { ArrayList<String> valueArrayList = new ArrayList<>(); for (int i = 0; portIndexs.get(i) != null; i++) { if (row.getCell(portIndexs.get(i)) != null && !row.getCell(portIndexs.get(i)).getStringCellValue().isEmpty()) { valueArrayList .add(row.getCell(portIndexs.get(i)).getStringCellValue().trim().toUpperCase()); } } System.out.println(valueArrayList.size()); valueArrayList_X.add(valueArrayList); //reference added to arraylist. clear the valueArrayList remove the values in valueArrayList_X Too!! //valueArrayList.clear(); } isTableHeader = false;//add contents to vector. } file.close(); return valueArrayList_X; }
From source file:com.ctb.importdata.ImportSFDataProcessor.java
public static ArrayList<SalesForceLicenseData> readDataFromXLSFile(String fileName) { File sfDataFile = new File(fileName); FileInputStream fileInputStream = null; ArrayList<SalesForceLicenseData> sfLicenseDataList = null; SalesForceLicenseData sfld = null;//from w ww .ja v a 2s . c o m if (sfDataFile.exists()) { //System.out.println("Reading data from .xls file started."); logger.info("Reading data from .xls file : Started :: Timestamp >> " + new Date(System.currentTimeMillis())); try { //read the file in to stream fileInputStream = new FileInputStream(sfDataFile); //Create Workbook instance holding reference to .xls file HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); //Get first/desired sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sfLicenseDataList = new ArrayList<SalesForceLicenseData>(); if (sheet != null) { int totalRows = sheet.getPhysicalNumberOfRows(); //System.out.println("Total no. of physical rows in file = "+ totalRows); logger.info("Total no. of physical rows in file = " + totalRows); Row headerRow = sheet.getRow(0); Cell headerCell; Cell dataCell; if (headerRow == null) { //System.out.println("No file header content found.") ; logger.info("No file header content found."); } else { int totalHeaderColumns = headerRow.getPhysicalNumberOfCells(); //System.out.println("Total no. of header cells = "+ totalHeaderColumns); logger.info("Total no. of header cells = " + totalHeaderColumns); for (int rowCtr = 1; rowCtr < totalRows; rowCtr++) { //System.out.println("Row No. >> "+rowCtr); Row dataRow = sheet.getRow(rowCtr); if (dataRow != null) { int totalRowColumns = dataRow.getPhysicalNumberOfCells(); //System.out.println("Total no. of current data row cells = "+ totalRowColumns); //logger.info("Total no. of current data row cells = "+ totalRowColumns); logger.info( "Row No. [" + rowCtr + "] :: Header Column Count = [" + totalHeaderColumns + "] :: Current Data Row Column Count = [" + totalRowColumns + "]"); //Discard dummy rows in spreadsheet if the count of row columns not equal to header columns if (totalHeaderColumns == totalRowColumns) { boolean isCustomerIdBlank = dataRow.getCell(0) .getCellType() == Cell.CELL_TYPE_BLANK ? true : false; boolean isOrgNodeIdBlank = dataRow.getCell(5) .getCellType() == Cell.CELL_TYPE_BLANK ? true : false; //System.out.println("isCustomerIdBlank >> "+isCustomerIdBlank+" :: isOrgNodeIdBlank >> "+isOrgNodeIdBlank); logger.info("Row No. [" + rowCtr + "] :: isCustomerIdBlank >> " + isCustomerIdBlank + " :: isOrgNodeIdBlank >> " + isOrgNodeIdBlank); //Condition to skip row for SF data object population if customer id or orgnode id is blank if (!isCustomerIdBlank && !isOrgNodeIdBlank) { sfld = new SalesForceLicenseData(); // For each row, loop through each column for (int colCtr = 0; colCtr < totalHeaderColumns; colCtr++) { //System.out.println("Column No. >> "+colCtr); headerCell = headerRow.getCell(colCtr); dataCell = dataRow.getCell(colCtr); if (dataCell != null) { //System.out.println("dataCell.getCellType() >> "+dataCell.getCellType()); switch (dataCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: //Do nothing //System.out.println(dataCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.println(dataCell.getNumericCellValue()); populateSFDataNumericColValue(sfld, dataCell, headerCell); break; case Cell.CELL_TYPE_STRING: //System.out.println(dataCell.getStringCellValue()); populateSFDataStrColValue(sfld, dataCell, headerCell); break; case Cell.CELL_TYPE_BLANK: populateSFDataBlankColValue(sfld, dataCell, headerCell); break; default: System.out.println(dataCell); break; } } } sfLicenseDataList.add(sfld); } } } } } } } catch (FileNotFoundException e) { logger.error("FileNotFoundException : occurred while procesing :: Filename >> [" + fileName + "]"); e.printStackTrace(); // unexpected } catch (IOException e) { logger.error("IOException : occurred while procesing :: Filename >> [" + fileName + "]"); e.printStackTrace(); } finally { try { if (fileInputStream != null) fileInputStream.close(); } catch (IOException e) { logger.error("IOException : occurred while closing file input stream."); e.printStackTrace(); } } //System.out.println("Reading data from .xls file completed."); logger.info("Reading data from .xls file : Completed :: Timestamp >> " + new Date(System.currentTimeMillis())); } else { //System.out.println("File does not exists"); logger.error("File does not exists :: Filename >> [" + fileName + "]"); } return sfLicenseDataList; }
From source file:com.ddt.driver.EXCELDriver.java
private void readOlderVersionExcelFile(String path, String sheetName) { try {/*ww w .ja v a2 s . com*/ HSSFWorkbook workbook; try (FileInputStream file = new FileInputStream(new File(path))) { workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { String line = ""; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: line += cell.getBooleanCellValue() + ";"; break; case Cell.CELL_TYPE_NUMERIC: line += String.valueOf(cell.getNumericCellValue()).split("\\.")[0] + ";"; break; case Cell.CELL_TYPE_STRING: line += cell.getStringCellValue() + ";"; break; } } linesOfDriver.add(line); } } } catch (FileNotFoundException e) { Logger.getLogger(EXCELDriver.class.getName()).log(Level.SEVERE, null, e); } catch (IOException e) { Logger.getLogger(EXCELDriver.class.getName()).log(Level.SEVERE, null, e); } }