List of usage examples for org.apache.poi.openxml4j.opc OPCPackage open
public static OPCPackage open(InputStream in) throws InvalidFormatException, IOException
From source file:com.mycompany.javaapplicaton3.ExampleEventUserModel.java
public void processOneSheet(String filename) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader r = new XSSFReader(pkg); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); // rId2 found by processing the Workbook // Seems to either be rId# or rSheet# InputStream sheet2 = r.getSheet("rId1"); InputSource sheetSource = new InputSource(sheet2); parser.parse(sheetSource);//from w w w .ja v a 2 s . co m sheet2.close(); }
From source file:com.mycompany.javaapplicaton3.ExcelWorkSheetHandlerTest.java
License:Open Source License
/** * @param args//from ww w .j a va 2 s . c o m * @throws Exception */ public static void main(String[] args) throws Exception { String SAMPLE_PERSON_DATA_FILE_PATH = "C:/Users/lprates/Documents/Sample-Person-Data.xlsx"; // Input File initialize File file = new File(SAMPLE_PERSON_DATA_FILE_PATH); InputStream inputStream = new FileInputStream(file); // Excel Cell Mapping Map<String, String> cellMapping = new HashMap<String, String>(); cellMapping.put("HEADER", "Person Id,Name,Height,Email Address,DOB,Salary"); cellMapping.put("A", "personId"); cellMapping.put("B", "name"); cellMapping.put("C", "height"); cellMapping.put("D", "emailId"); cellMapping.put("E", "dob"); cellMapping.put("F", "salary"); // The package open is instantaneous, as it should be. OPCPackage pkg = null; try { ExcelWorkSheetHandler<PersonVO> workSheetHandler = new ExcelWorkSheetHandler<PersonVO>(PersonVO.class, cellMapping); pkg = OPCPackage.open(inputStream); ExcelSheetCallback sheetCallback = new ExcelSheetCallback() { private int sheetNumber = 0; public void startSheet(int sheetNum, String sheetName) { this.sheetNumber = sheetNum; System.out.println("Started processing sheet number=" + sheetNumber + " and Sheet Name is '" + sheetName + "'"); } @Override public void endSheet() { System.out.println("Processing completed for sheet number=" + sheetNumber); } public void startSheet(int sheetNum) { System.out.println("Started processing sheet number=" + sheetNum); } }; System.out.println("Constructor: pkg, workSheetHandler, sheetCallback"); ExcelReader example1 = new ExcelReader(pkg, workSheetHandler, sheetCallback); example1.process("Lot 1 Data"); if (workSheetHandler.getValueList().isEmpty()) { // No data present LOG.error("sHandler.getValueList() is empty"); } else { LOG.info(workSheetHandler.getValueList().size() + " no. of records read from given excel worksheet successfully."); // Displaying data ead from Excel file displayPersonList(workSheetHandler.getValueList()); } /* System.out.println("\nConstructor: filePath, workSheetHandler, sheetCallback"); ExcelReader example2 = new ExcelReader(SAMPLE_PERSON_DATA_FILE_PATH, workSheetHandler, sheetCallback); example2.process(); System.out.println("\nConstructor: file, workSheetHandler, sheetCallback"); ExcelReader example3 = new ExcelReader(file, workSheetHandler, null); example3.process(); */ } catch (RuntimeException are) { LOG.error(are.getMessage(), are.getCause()); } catch (InvalidFormatException ife) { LOG.error(ife.getMessage(), ife.getCause()); } catch (IOException ioe) { LOG.error(ioe.getMessage(), ioe.getCause()); } finally { IOUtils.closeQuietly(inputStream); try { if (null != pkg) { pkg.close(); } } catch (IOException e) { // just ignore IO exception } } }
From source file:com.mycompany.javaapplicaton3.LerArquivo.java
public static void main(String args[]) { logger.info("Hello World!"); try {/* w w w. ja v a 2 s . c o m*/ //File excel = new File ("C:/Users/lprates/Documents/arquivo2013.xlsx"); //FileInputStream fis = new FileInputStream(excel); OPCPackage pkg = OPCPackage.open("C:/Users/lprates/Documents/arquivo2013.xlsx"); XSSFWorkbook myWorkBook = new XSSFWorkbook(pkg); // Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t"); break; default: System.out.print("Nada"); } } System.out.println(""); } } catch (Exception ex) { logger.error(ex.toString()); } }
From source file:com.mycompany.javaapplicaton3.LerArquivo2.java
/** * @param args//from ww w . ja v a 2 s . co m * @throws Exception */ public static void main(String[] args) throws Exception { String SAMPLE_PERSON_DATA_FILE_PATH = "C:/Users/lprates/Documents/arquivo2013.xlsx"; // Input File initialize File file = new File(SAMPLE_PERSON_DATA_FILE_PATH); InputStream inputStream = new FileInputStream(file); // Excel Cell Mapping Map<String, String> cellMapping0150 = new HashMap<String, String>(); cellMapping0150.put("HEADER", "REG,COD_PART,NOME,COD_PAIS,CNPJ,CPF,IE,COD_MUN,SUFRAMA,END,NUM,COMPL,BAIRRO,BPE_VIGENCIA_FIN"); cellMapping0150.put("A", "reg"); cellMapping0150.put("B", "codPart"); cellMapping0150.put("C", "nome"); cellMapping0150.put("D", "codPais"); cellMapping0150.put("E", "cnpj"); cellMapping0150.put("F", "cpf"); cellMapping0150.put("G", "ie"); cellMapping0150.put("H", "codMun"); cellMapping0150.put("I", "suframa"); cellMapping0150.put("J", "end"); cellMapping0150.put("K", "num"); cellMapping0150.put("L", "compl"); cellMapping0150.put("M", "bairro"); cellMapping0150.put("N", "bpeVigenciaFin"); // Excel Cell Mapping Map<String, String> cellMapping0200 = new HashMap<String, String>(); cellMapping0200.put("HEADER", "REG,COD_ITEM,DESCR_ITEM,COD_BARRA,COD_ANT_ITEM,UNID_INV,TIPO_ITEM,COD_NCM,EX_IPI,COD_GEN,COD_LST,ALIQ_ICMS"); cellMapping0200.put("A", "reg"); cellMapping0200.put("B", "codItem"); cellMapping0200.put("C", "descrItem"); cellMapping0200.put("D", "codBarra"); cellMapping0200.put("E", "codAntItem"); cellMapping0200.put("F", "unidInv"); cellMapping0200.put("G", "tipoItem"); cellMapping0200.put("H", "codNcm"); cellMapping0200.put("I", "exIpi"); cellMapping0200.put("J", "codGen"); cellMapping0200.put("K", "codLst"); cellMapping0200.put("L", "aliqIcms"); // Excel Cell Mapping Map<String, String> cellMappingC100_C170 = new HashMap<String, String>(); cellMappingC100_C170.put("A", "campo1"); cellMappingC100_C170.put("B", "campo2"); cellMappingC100_C170.put("C", "campo3"); cellMappingC100_C170.put("D", "campo4"); cellMappingC100_C170.put("E", "campo5"); cellMappingC100_C170.put("F", "campo6"); cellMappingC100_C170.put("G", "campo7"); cellMappingC100_C170.put("H", "campo8"); cellMappingC100_C170.put("I", "campo9"); cellMappingC100_C170.put("J", "campo10"); cellMappingC100_C170.put("K", "campo11"); cellMappingC100_C170.put("L", "campo12"); cellMappingC100_C170.put("M", "campo13"); cellMappingC100_C170.put("N", "campo14"); cellMappingC100_C170.put("O", "campo15"); cellMappingC100_C170.put("P", "campo16"); cellMappingC100_C170.put("Q", "campo17"); cellMappingC100_C170.put("R", "campo18"); cellMappingC100_C170.put("S", "campo19"); cellMappingC100_C170.put("T", "campo20"); cellMappingC100_C170.put("U", "campo21"); cellMappingC100_C170.put("V", "campo22"); cellMappingC100_C170.put("W", "campo23"); cellMappingC100_C170.put("X", "campo24"); cellMappingC100_C170.put("Y", "campo25"); cellMappingC100_C170.put("Z", "campo26"); cellMappingC100_C170.put("AA", "campo27"); cellMappingC100_C170.put("AB", "campo28"); cellMappingC100_C170.put("AC", "campo29"); cellMappingC100_C170.put("AD", "campo30"); cellMappingC100_C170.put("AE", "campo31"); cellMappingC100_C170.put("AF", "campo32"); cellMappingC100_C170.put("AG", "campo33"); cellMappingC100_C170.put("AH", "campo34"); cellMappingC100_C170.put("AI", "campo35"); cellMappingC100_C170.put("AJ", "campo36"); cellMappingC100_C170.put("AK", "campo37"); cellMappingC100_C170.put("AL", "campo38"); cellMappingC100_C170.put("AM", "campo39"); cellMappingC100_C170.put("AN", "campo40"); cellMappingC100_C170.put("AO", "campo41"); cellMappingC100_C170.put("AP", "campo42"); cellMappingC100_C170.put("AQ", "campo43"); cellMappingC100_C170.put("AR", "campo44"); cellMappingC100_C170.put("AS", "campo45"); cellMappingC100_C170.put("AT", "campo46"); cellMappingC100_C170.put("AU", "campo47"); cellMappingC100_C170.put("AV", "campo48"); cellMappingC100_C170.put("AW", "campo49"); cellMappingC100_C170.put("AX", "campo50"); cellMappingC100_C170.put("AY", "campo51"); cellMappingC100_C170.put("AZ", "campo52"); cellMappingC100_C170.put("BA", "campo53"); cellMappingC100_C170.put("BB", "campo54"); cellMappingC100_C170.put("BC", "campo55"); cellMappingC100_C170.put("BD", "campo56"); cellMappingC100_C170.put("BE", "campo57"); cellMappingC100_C170.put("BF", "campo58"); cellMappingC100_C170.put("BG", "campo59"); cellMappingC100_C170.put("BH", "campo60"); cellMappingC100_C170.put("BI", "campo61"); cellMappingC100_C170.put("BJ", "campo62"); cellMappingC100_C170.put("BK", "campo63"); cellMappingC100_C170.put("BL", "campo64"); cellMappingC100_C170.put("BM", "campo65"); cellMappingC100_C170.put("BN", "campo66"); cellMappingC100_C170.put("BO", "campo67"); cellMappingC100_C170.put("BP", "campo68"); cellMappingC100_C170.put("BQ", "campo69"); cellMappingC100_C170.put("BR", "campo70"); cellMappingC100_C170.put("BS", "campo71"); // The package open is instantaneous, as it should be. OPCPackage pkg = null; try { ExcelWorkSheetHandler<Reg0150> workSheetHandler = new ExcelWorkSheetHandler<Reg0150>(Reg0150.class, cellMapping0150); pkg = OPCPackage.open(inputStream); ExcelSheetCallback sheetCallback = new ExcelSheetCallback() { private int sheetNumber = 0; public void startSheet(int sheetNum, String sheetName) { this.sheetNumber = sheetNum; System.out.println("Started processing sheet number=" + sheetNumber + " and Sheet Name is '" + sheetName + "'"); } @Override public void endSheet() { System.out.println("Processing completed for sheet number=" + sheetNumber); } public void startSheet(int sheetNum) { System.out.println("Started processing sheet number=" + sheetNum); } }; /*** Leitura Registro 0150 ***/ System.out.println("Constructor: pkg, workSheetHandler, sheetCallback"); ExcelReader example1 = new ExcelReader(pkg, workSheetHandler, sheetCallback); example1.process("0150"); if (workSheetHandler.getValueList().isEmpty()) { // No data present LOG.error("sHandler.getValueList() is empty"); } else { LOG.info(workSheetHandler.getValueList().size() + " no. of records read from given excel worksheet successfully."); // Displaying data ead from Excel file displayPersonList(workSheetHandler.getValueList()); } /*** Leitura Registro 0200 ***/ ExcelWorkSheetHandler<Reg0200> workSheetHandler0200 = new ExcelWorkSheetHandler<Reg0200>(Reg0200.class, cellMapping0200); ExcelReader example2 = new ExcelReader(pkg, workSheetHandler0200, null); example2.process("0200"); if (workSheetHandler0200.getValueList().isEmpty()) { LOG.error("sHandler.getValueList() is empty"); } else { LOG.info(workSheetHandler0200.getValueList().size() + " no. of records read from given excel worksheet successfully."); displayPersonList0200(workSheetHandler0200.getValueList()); } /*** Leitura Registro C100 e C170 ***/ ExcelWorkSheetHandler<RegC100_C170> workSheetHandlerC100_C170 = new ExcelWorkSheetHandler<RegC100_C170>( RegC100_C170.class, cellMappingC100_C170, 4); workSheetHandlerC100_C170.setVerifiyHeader(false); ExcelReader example3 = new ExcelReader(pkg, workSheetHandlerC100_C170, null); example3.process("201302"); if (workSheetHandlerC100_C170.getValueList().isEmpty()) { LOG.error("sHandler.getValueList() is empty"); } else { LOG.info(workSheetHandlerC100_C170.getValueList().size() + " no. of records read from given excel worksheet successfully."); displayPersonListC100_C170(workSheetHandlerC100_C170.getValueList()); } } catch (RuntimeException are) { LOG.error(are.getMessage(), are.getCause()); } catch (InvalidFormatException ife) { LOG.error(ife.getMessage(), ife.getCause()); } catch (IOException ioe) { LOG.error(ioe.getMessage(), ioe.getCause()); } finally { IOUtils.closeQuietly(inputStream); try { if (null != pkg) { pkg.close(); } } catch (IOException e) { // just ignore IO exception } } }
From source file:com.myjeeva.poi.demo.Excel2JavaDemo.java
License:Open Source License
/** * @param args/*from w w w . j av a 2s.co m*/ * @throws FileNotFoundException */ public static void main(String[] args) throws FileNotFoundException { String SAMPLE_PERSON_DATA_FILE_PATH = "src/main/resources/Sample-Person-Data.xlsx"; // Input File initialize File file = new File(SAMPLE_PERSON_DATA_FILE_PATH); InputStream inputStream = new FileInputStream(file); // Excel Cell Mapping Map<String, String> cellMapping = new HashMap<String, String>(); cellMapping.put("HEADER", "Person Id,Name,Height,Email Address,DOB,Salary"); cellMapping.put("A", "personId"); cellMapping.put("B", "name"); cellMapping.put("C", "height"); cellMapping.put("D", "emailId"); cellMapping.put("E", "dob"); cellMapping.put("F", "salary"); // The package open is instantaneous, as it should be. OPCPackage pkg = null; try { ExcelWorkSheetHandler<PersonVO> workSheetHandler = new ExcelWorkSheetHandler<PersonVO>(PersonVO.class, cellMapping); pkg = OPCPackage.open(inputStream); ExcelReader excelReader = new ExcelReader(pkg, workSheetHandler); excelReader.process(); if (workSheetHandler.getValueList().isEmpty()) { // No data present LOG.error("sHandler.getValueList() is empty"); } else { LOG.info(workSheetHandler.getValueList().size() + " no. of records read from given excel worksheet successfully."); // Displaying data ead from Excel file displayPersonList(workSheetHandler.getValueList()); } } catch (RuntimeException are) { LOG.error(are.getMessage(), are.getCause()); } catch (InvalidFormatException ife) { LOG.error(ife.getMessage(), ife.getCause()); } catch (IOException ioe) { LOG.error(ioe.getMessage(), ioe.getCause()); } finally { IOUtils.closeQuietly(inputStream); try { if (null != pkg) { pkg.close(); } } catch (IOException e) { // just ignore IO exception } } }
From source file:com.myjeeva.poi.ExcelReader.java
License:Open Source License
private static OPCPackage getOPCPackage(File file) throws Exception { if (null == file || !file.canRead()) { throw new Exception("File object is null or cannot have read permission"); }//w ww. j a va2s .co m return OPCPackage.open(new FileInputStream(file)); }
From source file:com.myjeeva.poi.ExcelWorkSheetHandlerTest.java
License:Open Source License
/** * @param args//from www . java 2 s . c om * @throws Exception */ public static void main(String[] args) throws Exception { String SAMPLE_PERSON_DATA_FILE_PATH = "src/test/resources/Sample-Person-Data.xlsx"; // Input File initialize File file = new File(SAMPLE_PERSON_DATA_FILE_PATH); InputStream inputStream = new FileInputStream(file); // Excel Cell Mapping Map<String, String> cellMapping = new HashMap<String, String>(); cellMapping.put("HEADER", "Person Id,Name,Height,Email Address,DOB,Salary"); cellMapping.put("A", "personId"); cellMapping.put("B", "name"); cellMapping.put("C", "height"); cellMapping.put("D", "emailId"); cellMapping.put("E", "dob"); cellMapping.put("F", "salary"); // The package open is instantaneous, as it should be. OPCPackage pkg = null; try { ExcelWorkSheetHandler<PersonVO> workSheetHandler = new ExcelWorkSheetHandler<PersonVO>(PersonVO.class, cellMapping); pkg = OPCPackage.open(inputStream); ExcelSheetCallback sheetCallback = new ExcelSheetCallback() { private int sheetNumber = 0; @Override public void startSheet(int sheetNum, String sheetName) { this.sheetNumber = sheetNum; System.out.println("Started processing sheet number=" + sheetNumber + " and Sheet Name is '" + sheetName + "'"); } @Override public void endSheet() { System.out.println("Processing completed for sheet number=" + sheetNumber); } }; System.out.println("Constructor: pkg, workSheetHandler, sheetCallback"); ExcelReader example1 = new ExcelReader(pkg, workSheetHandler, sheetCallback); example1.process(); if (workSheetHandler.getValueList().isEmpty()) { // No data present LOG.error("sHandler.getValueList() is empty"); } else { LOG.info(workSheetHandler.getValueList().size() + " no. of records read from given excel worksheet successfully."); // Displaying data ead from Excel file displayPersonList(workSheetHandler.getValueList()); } System.out.println("\nConstructor: filePath, workSheetHandler, sheetCallback"); ExcelReader example2 = new ExcelReader(SAMPLE_PERSON_DATA_FILE_PATH, workSheetHandler, sheetCallback); example2.process(); System.out.println("\nConstructor: file, workSheetHandler, sheetCallback"); ExcelReader example3 = new ExcelReader(file, workSheetHandler, null); example3.process(); } catch (RuntimeException are) { LOG.error(are.getMessage(), are.getCause()); } catch (InvalidFormatException ife) { LOG.error(ife.getMessage(), ife.getCause()); } catch (IOException ioe) { LOG.error(ioe.getMessage(), ioe.getCause()); } finally { IOUtils.closeQuietly(inputStream); try { if (null != pkg) { pkg.close(); } } catch (IOException e) { // just ignore IO exception } } }
From source file:com.myjeeva.poi.ExcelWorkSheetRowCallbackHandlerTest.java
License:Open Source License
public static void main(String[] args) throws Exception { String SAMPLE_PERSON_DATA_FILE_PATH = "src/test/resources/Sample-Person-Data.xlsx"; File file = new File(SAMPLE_PERSON_DATA_FILE_PATH); InputStream inputStream = new FileInputStream(file); // The package open is instantaneous, as it should be. OPCPackage pkg = null;/*from w ww. j a v a 2 s.c om*/ try { ExcelWorkSheetRowCallbackHandler sheetRowCallbackHandler = new ExcelWorkSheetRowCallbackHandler( new ExcelRowContentCallback() { @Override public void processRow(int rowNum, Map<String, String> map) { // Do any custom row processing here, such as save // to database // Convert map values, as necessary, to dates or // parse as currency, etc System.out.println("rowNum=" + rowNum + ", map=" + map); } }); pkg = OPCPackage.open(inputStream); ExcelSheetCallback sheetCallback = new ExcelSheetCallback() { private int sheetNumber = 0; @Override public void startSheet(int sheetNum, String sheetName) { this.sheetNumber = sheetNum; System.out.println("Started processing sheet number=" + sheetNumber + " and Sheet Name is '" + sheetName + "'"); } @Override public void endSheet() { System.out.println("Processing completed for sheet number=" + sheetNumber); } }; System.out.println("Constructor: pkg, sheetRowCallbackHandler, sheetCallback"); ExcelReader example1 = new ExcelReader(pkg, sheetRowCallbackHandler, sheetCallback); example1.process(); System.out.println("\nConstructor: filePath, sheetRowCallbackHandler, sheetCallback"); ExcelReader example2 = new ExcelReader(SAMPLE_PERSON_DATA_FILE_PATH, sheetRowCallbackHandler, sheetCallback); example2.process(); System.out.println("\nConstructor: file, sheetRowCallbackHandler, sheetCallback"); ExcelReader example3 = new ExcelReader(file, sheetRowCallbackHandler, null); example3.process(); } catch (RuntimeException are) { LOG.error(are.getMessage(), are.getCause()); } catch (InvalidFormatException ife) { LOG.error(ife.getMessage(), ife.getCause()); } catch (IOException ioe) { LOG.error(ioe.getMessage(), ioe.getCause()); } finally { IOUtils.closeQuietly(inputStream); try { if (null != pkg) { pkg.close(); } } catch (IOException e) { // just ignore IO exception } } }
From source file:com.myjeeva.poi.ExcelXSSFRowCallbackHandler.java
License:Apache License
public ExcelXSSFRowCallbackHandler(InputStream inputStream, ExcelRowContentCallback rowCallback) throws InvalidFormatException, IOException { this(OPCPackage.open(inputStream), rowCallback); }
From source file:com.myjeeva.poi.ExcelXSSFRowCallbackHandler.java
License:Apache License
public ExcelXSSFRowCallbackHandler(File file, ExcelRowContentCallback rowCallback) throws InvalidFormatException { this(OPCPackage.open(file), rowCallback); }