List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook
public XSSFWorkbook(PackagePart part) throws IOException
From source file:com.consensus.qa.framework.ExcelOperations.java
private XSSFWorkbook GetWorkBook(FileNames fileName) throws IOException { if (fileName.toString().toLowerCase().contains("verizonedgeup")) { fileInput = new FileInputStream(new File(VerizonEdgeUpSheet)); } else//w ww . ja v a 2s . c o m fileInput = new FileInputStream(new File(VerizonRegressionNumberPortSheet)); workBook = new XSSFWorkbook(fileInput); return workBook; }
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. *//*from w ww .j av a 2s . c o 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/*from w w w.j av a 2 s . c o m*/ * * @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.creditcloud.carinsurance.CarInsuranceFeeServiceBean.java
/** * ??// www . j a v a2 s . c om * * @param feeFileExcel */ public void bacthUpdateCarInsuranceFeeSatatus(File feeFileExcel) { try { FileInputStream file = new FileInputStream(feeFileExcel); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); String insuranceNum = ""; int currentPeriod = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: currentPeriod = (int) cell.getNumericCellValue(); System.out.print((int) cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: insuranceNum = cell.getStringCellValue().trim(); System.out.print(cell.getStringCellValue() + "\t"); break; } } CarInsuranceFee fee = carInsuranceFeeDAO.findByInSuranceNumAndCurrentPeriod(insuranceNum, currentPeriod); //? if (fee != null) { updateCarInsuranceFeeSatatus(fee.getId(), CarInsuranceStatus.CLEARED); } System.out.println(""); } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.ctb.importdata.ImportSFDataProcessor.java
public static ArrayList<SalesForceLicenseData> readDataFromXLSXFile(String fileName) { File sfDataFile = new File(fileName); FileInputStream fileInputStream = null; ArrayList<SalesForceLicenseData> sfLicenseDataList = null; //read the file in to stream if (sfDataFile.exists()) { //System.out.println("Reading data from .xlsx file started."); logger.info("Reading data from .xlsx file : Started :: Timestamp >> " + new Date(System.currentTimeMillis())); try {/*from www. j av a2s .c o m*/ fileInputStream = new FileInputStream(sfDataFile); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); sfLicenseDataList = new ArrayList<SalesForceLicenseData>(); SalesForceLicenseData sfld = null; 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 current data 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: //System.out.println(" "); 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 .xlsx file completed."); logger.info("Reading data from .xlsx 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.cx.test.FromHowTo.java
License:Apache License
public static void main(String[] args) throws Exception { Class[] clazz = new Class[] { String.class, String.class, String.class, String.class, Integer.class, String.class, String.class }; InputStream stream = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\menu.xlsx")); Workbook wb = new XSSFWorkbook(stream); Sheet sheet = wb.getSheetAt(0);//from www. j a v a 2 s.c o m int rows = sheet.getLastRowNum(); int cells = sheet.getRow(0).getPhysicalNumberOfCells(); for (int i = 0; i < rows; i++) { Row row = sheet.getRow(i + 1); for (int j = 0; j < cells; j++) { Cell cell = row.getCell(j); Object obj = null; if (cell != null) { obj = getCellValue(cell, clazz[j]); } switch (j) { case 0: System.out.println("000000000-----" + obj); break; case 1: System.out.println("1111111111111" + obj); break; default: break; } } } }
From source file:com.dao.DatabaseDao.java
public static void insertFromFile(MainForm mf, String sect, String destpath, String fname, Statement st, Connection c, long maxRecID, ArrayList<Long> arl) throws Exception { FileInputStream fin = new FileInputStream(destpath); XSSFWorkbook wb = new XSSFWorkbook(fin); XSSFSheet sheet = wb.getSheet("EmployeeInfo"); long lastrowno = sheet.getLastRowNum(); XSSFRow row;// ww w . ja v a 2 s . co m long i = 1; while (i <= lastrowno) { row = sheet.getRow((int) i); long id = (long) row.getCell(0).getNumericCellValue();//ID String name = row.getCell(1).getStringCellValue();//NAME long recp_no = (long) row.getCell(2).getNumericCellValue();//RECEIPT_NO Date edate = (Date) row.getCell(3).getDateCellValue();//ENTRY_DATE int subrate = (int) row.getCell(4).getNumericCellValue();//SUB_RATE int jan = (int) row.getCell(5).getNumericCellValue();//JAN int feb = (int) row.getCell(6).getNumericCellValue();//FEB int mar = (int) row.getCell(7).getNumericCellValue();//MAR int apr = (int) row.getCell(8).getNumericCellValue();//APR int may = (int) row.getCell(9).getNumericCellValue();//MAY int jun = (int) row.getCell(10).getNumericCellValue();//JUN int jul = (int) row.getCell(11).getNumericCellValue();//JUL int aug = (int) row.getCell(12).getNumericCellValue();//AUG int sep = (int) row.getCell(13).getNumericCellValue();//SEP int oct = (int) row.getCell(14).getNumericCellValue();//OCT int nov = (int) row.getCell(15).getNumericCellValue();//NOV int dec = (int) row.getCell(16).getNumericCellValue();//DECB long tot = (long) row.getCell(17).getNumericCellValue();//TOTAL String remark = row.getCell(18).getStringCellValue();//REMARK String sector = row.getCell(19).getStringCellValue();//SECTOR String sub_frm = row.getCell(20).getStringCellValue();//SUB_FROM String sub_to = row.getCell(21).getStringCellValue();//SUB_TO String place = row.getCell(22).getStringCellValue();//PLACE boolean isAlready = arl.contains(recp_no); Employee emp = new Employee(); emp.setName(name); emp.setEntry_date(edate); emp.setSub_rate(subrate); emp.setJan(jan); emp.setFeb(feb); emp.setMar(mar); emp.setApr(apr); emp.setMay(may); emp.setJun(jun); emp.setJul(jul); emp.setAug(aug); emp.setSep(sep); emp.setOct(oct); emp.setNov(nov); emp.setDecb(dec); emp.setTotal(tot); emp.setRemark(remark); emp.setSector(sector); emp.setSub_from(sub_frm); emp.setSub_to(sub_to); emp.setPlace(place); if (isAlready) { emp.setReceipt_no(maxRecID); maxRecID++; } else { emp.setReceipt_no(recp_no); } EmployeeDao.save(emp); i++; } st = c.createStatement(); st.execute("INSERT INTO IMPORTFILE_INFO(NAME) VALUES('" + fname + "')"); mf.initData(sect); JOptionPane.showMessageDialog(null, "Database Import Successfully...!!"); }
From source file:com.dataart.spreadsheetanalytics.demo.main.DependencyGraphDemo.java
License:Apache License
public static void main(String[] args) throws Exception { //input arguments: filename and list of cells to evaluate if (args.length < 2) { System.err.println("Excel file path and Cell Address, please!"); return;//from ww w . j ava2 s . com } final String excel = args[0]; final String graphCell = args[1]; //prepare DataModel to work with final IDataModel model = Converters.toDataModel(new XSSFWorkbook(excel)); final ICellAddress addr = new CellAddress(model.getDataModelId(), A1Address.fromA1Address(graphCell)); final IAuditor auditor = new SpreadsheetAuditor(model); final IExecutionGraph graph = auditor.buildDependencyGraph(addr.a1Address()); //print graph DemoUtil.generateVisJsData(graph); DemoUtil.plainprint(graph); }
From source file:com.dataart.spreadsheetanalytics.demo.main.Evaluation2ThreadsDemo.java
License:Apache License
public static void main(String[] args) throws Exception { //input arguments: filename and list of cells to evaluate if (args.length < 2) { System.err.println("Excel file path and Cell Address, please!"); return;/*from w w w . ja v a2 s . c o m*/ } final String excel = args[0]; final List<String> cellsToEvaluate = new ArrayList<>(Arrays.asList(args)); cellsToEvaluate.remove(0); //prepare DataModel to work with final IDataModel model = Converters.toDataModel(new XSSFWorkbook(excel)); DemoUtil.initCaches(model, excel); new SpreadsheetEvaluator(model); //TODO: static init? System.out.println("1"); new Thread(() -> { try { final IEvaluator evaluator1 = new SpreadsheetEvaluator(model); for (String cell : cellsToEvaluate) { IEvaluationResult<ICellValue> val = evaluator1.evaluate(A1Address.fromA1Address(cell)); System.out.println("[1] Result of " + cell + " is: " + val.getResult()); DataSet vds = (DataSet) val.getContext().get(ValidateFunction.DATASET_NAME); System.out.println(vds); } } catch (Exception e) { e.printStackTrace(); } }).start(); System.out.println("2"); new Thread(() -> { try { final IEvaluator evaluator2 = new SpreadsheetEvaluator(model); for (String cell : cellsToEvaluate) { IEvaluationResult<ICellValue> val = evaluator2.evaluate(A1Address.fromA1Address(cell)); System.out.println("[2] Result of " + cell + " is: " + val.getResult()); DataSet vds = (DataSet) val.getContext().get(ValidateFunction.DATASET_NAME); System.out.println(vds); } } catch (Exception e) { e.printStackTrace(); } }).start(); }
From source file:com.dataart.spreadsheetanalytics.demo.main.EvaluationWithExecutionGraphDemo.java
License:Apache License
public static void main(String[] args) throws Exception { //input arguments: filename and list of cells to evaluate if (args.length < 2) { System.err.println("Excel file path and Cell Address, please!"); return;/*from w ww . j a v a2 s.c o m*/ } final String excel = args[0]; final List<String> cellsToEvaluate = new ArrayList<>(Arrays.asList(args)); cellsToEvaluate.remove(0); //prepare DataModel to work with final IDataModel model = Converters.toDataModel(new XSSFWorkbook(excel)); DemoUtil.initCaches(model, excel); //create Evaluator final IEvaluator evaluator = new SpreadsheetEvaluator(model); //evaluate and save to map to print later Map<String, Object> values = new LinkedHashMap<>(); for (String cell : cellsToEvaluate) { values.put(cell, evaluator.evaluate(new CellAddress(model.getDataModelId(), A1Address.fromA1Address(cell)))); } //last cell final ICellAddress addr = new CellAddress(model.getDataModelId(), A1Address.fromA1Address(cellsToEvaluate.get(cellsToEvaluate.size() - 1))); //create Auditor final IAuditor auditor = new SpreadsheetAuditor(model); //build graph final IExecutionGraph graph = auditor.buildExecutionGraph(addr.a1Address(), ExecutionGraphConfig.DEFAULT); //print graph DemoUtil.generateVisJsData(graph); DemoUtil.plainprint(graph); //pring values\ System.out.println("\n\n***********"); for (String cell : values.keySet()) { System.out.println("Result of " + cell + " is: " + values.get(cell)); } }