List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt
Sheet getSheetAt(int index);
From source file:com.lw.common.utils.ExcelUtil.java
public <T> Workbook batchImportFailList(String modelPath, List<T> objectList, Class<T> modelClass, Map<String, String> columnMap) throws Exception { //??excel// w w w. ja va 2 s. c om File localfile = new File("D:\\lw7068\\Desktop\\ (4)\\??.xlsx"); InputStream in = new FileInputStream(localfile); // ? Workbook book = new XSSFWorkbook(in); // ?sheet Sheet sheet = book.getSheetAt(0); // Row titleRow = sheet.getRow(0); //???index Map<String, Integer> rowIndex = getTitleRowIndex(titleRow); //???method Map<Integer, Method> methodsIndex = getRowIndexAndGetMethod(titleRow, rowIndex, modelClass, columnMap); for (int i = 0; i < objectList.size(); i++) { //excelindex ??? Map<Integer, String> dataMap = getRowIndexAndData(titleRow, objectList.get(i), methodsIndex); // Row row = sheet.createRow(i + 1); // for (Map.Entry<Integer, String> entry : dataMap.entrySet()) { Cell cell = row.createCell(entry.getKey()); cell.setCellValue(entry.getValue()); } } return book; }
From source file:com.mec.DAO.Superior.SuperiorDAO.java
public Map<String, List<String>> getAll() throws IOException { Map<String, List<String>> s = new HashMap<>(); ClassLoader classloader = Thread.currentThread().getContextClassLoader(); InputStream is = classloader.getResourceAsStream("superior.xlsx"); Workbook workbook = new XSSFWorkbook(is); Sheet datatypeSheet = workbook.getSheetAt(0); for (Row row : datatypeSheet) { Cell cue = row.getCell(0);/*from w w w.java 2 s. co m*/ Cell carrera = row.getCell(1); if (cue != null && carrera != null && !cue.toString().isEmpty()) { cue.setCellType(Cell.CELL_TYPE_STRING); if (s.containsKey(carrera.toString())) { s.get(carrera.toString()).add(cue.toString()); } else { List<String> aux = new ArrayList<>(); aux.add(cue.toString()); s.put(carrera.toString(), aux); } //list.add(new Superior(cue.toString(), carrera.toString())); } } return s; }
From source file:com.mec.DAO.Superior.SuperiorDAO.java
public Map<String, List<String>> getByFiltro(String filtro) throws IOException { Map<String, List<String>> s = new HashMap<>(); ClassLoader classloader = Thread.currentThread().getContextClassLoader(); InputStream is = classloader.getResourceAsStream("superior.xlsx"); Workbook workbook = new XSSFWorkbook(is); Sheet datatypeSheet = workbook.getSheetAt(0); for (Row row : datatypeSheet) { Cell cue = row.getCell(0);// ww w.j a v a2 s . co m Cell carrera = row.getCell(1); if (cue != null && carrera != null && !cue.toString().isEmpty() && clean((carrera.toString())).contains(clean(filtro))) { cue.setCellType(Cell.CELL_TYPE_STRING); if (s.containsKey(carrera.toString())) { s.get(carrera.toString()).add(cue.toString()); } else { List<String> aux = new ArrayList<>(); aux.add(cue.toString()); s.put(carrera.toString(), aux); } } } return s; }
From source file:com.mec.Services.VoteroService.java
private Map<String, List<Establecimiento>> getFromExcel() throws IOException { Map<String, List<Establecimiento>> s = new HashMap<>(); ClassLoader classloader = Thread.currentThread().getContextClassLoader(); InputStream is = classloader.getResourceAsStream("votero.xlsx"); Workbook workbook = new XSSFWorkbook(is); Sheet datatypeSheet = workbook.getSheetAt(0); for (Row row : datatypeSheet) { Cell cue = row.getCell(0);/*w ww .j a v a 2 s .c om*/ Cell anexo = row.getCell(1); Cell circuito = row.getCell(3); Cell desde = row.getCell(4); Cell hasta = row.getCell(5); Cell total = row.getCell(6); if (cue != null && anexo != null && !cue.toString().isEmpty()) { cue.setCellType(Cell.CELL_TYPE_STRING); anexo.setCellType(Cell.CELL_TYPE_STRING); circuito.setCellType(Cell.CELL_TYPE_STRING); desde.setCellType(Cell.CELL_TYPE_STRING); hasta.setCellType(Cell.CELL_TYPE_STRING); total.setCellType(Cell.CELL_TYPE_STRING); if (s.containsKey(circuito.toString())) { EstablecimientoPost est = establecimientosDAO.getByCueAnexo(Integer.parseInt(cue.toString()), Integer.parseInt(anexo.toString())); setGeo(est); s.get(circuito.toString()).add(new Establecimiento(est, Integer.parseInt(desde.toString()), Integer.parseInt(hasta.toString()), Integer.parseInt(total.toString()))); } else { List<Establecimiento> aux = new ArrayList<>(); EstablecimientoPost est = establecimientosDAO.getByCueAnexo(Integer.parseInt(cue.toString()), Integer.parseInt(anexo.toString())); setGeo(est); aux.add(new Establecimiento(est, Integer.parseInt(desde.toString()), Integer.parseInt(hasta.toString()), Integer.parseInt(total.toString()))); s.put(circuito.toString(), aux); } } } return s; }
From source file:com.mimp.controllers.reporte.java
@RequestMapping("/Reportes/OrganismosAcreditados") public void ReporteOrganismo(ModelMap map, HttpSession session, HttpServletResponse response) { Personal usuario = (Personal) session.getAttribute("usuario"); Workbook wb = new XSSFWorkbook(); try {//from w ww . j a v a2 s . c om //Se llama a la plantilla localizada en la ruta // InputStream inp = new FileInputStream("C:\\Plantillas\\OrgAcred.xlsx"); InputStream inp = new FileInputStream("/opt/Plantillas/OrgAcred.xlsx"); wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); //Aqu va el query que consigue los datos de la tabla //ArrayList<Organismo> listaorg = ServicioPersonal.ListaOrganismos(); ArrayList<Organismo> listaorg = ServicioReporte.ReporteOrganismo2(); int i = 1; for (Organismo org : listaorg) { Row row = sheet.createRow(i); Cell cell = row.createCell(0); cell.setCellValue(i); cell = row.createCell(1); cell.setCellValue(org.getEntidad().getNombre()); cell = row.createCell(2); cell.setCellValue(org.getCompetencia()); cell = row.createCell(3); cell.setCellValue(org.getEntidad().getResolAuto()); cell = row.createCell(4); String fechaVenc = ""; try { fechaVenc = format.dateToString(org.getEntidad().getFechaVenc()); } catch (Exception ex) { } cell.setCellValue(fechaVenc); cell = row.createCell(5); for (Iterator iter = org.getRepresentantes().iterator(); iter.hasNext();) { Representante rep = (Representante) iter.next(); cell.setCellValue(rep.getNombre() + " " + rep.getApellidoP()); } cell = row.createCell(6); cell.setCellValue(org.getEntidad().getObs()); i++; } } catch (Exception e) { //e.printStackTrace(); } try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-Disposition", "attachment; filename=Registro del nmero Organismos Acreditados.xlsx"); OutputStream fileOut = response.getOutputStream(); wb.write(fileOut); fileOut.flush(); fileOut.close(); } catch (Exception ex) { //ex.printStackTrace(); } String mensaje_log = "El usuario: " + usuario.getNombre() + " " + usuario.getApellidoP() + " con ID: " + usuario.getIdpersonal() + ". Descarg el Reporte 'Organismos Acreditados' "; String Tipo_registro = "Personal"; try { String Numero_registro = String.valueOf(usuario.getIdpersonal()); ServicioPersonal.InsertLog(usuario, Tipo_registro, Numero_registro, mensaje_log); } catch (Exception ex) { } }
From source file:com.movielabs.availslib.AvailSS.java
License:Open Source License
/** * Add a sheet from an Excel spreadsheet to a spreadsheet object * @param sheetNumber zero-based index of sheet to add * @return created sheet object// w w w . j a va 2 s . co m * @throws IllegalArgumentException if the sheet does not exist in the Excel spreadsheet * @throws Exception other error conditions may also throw exceptions */ public AvailsSheet addSheet(int sheetNumber) throws Exception { Workbook wb = new XSSFWorkbook(new FileInputStream(file)); Sheet sheet; try { sheet = wb.getSheetAt(sheetNumber); } catch (IllegalArgumentException e) { wb.close(); throw new IllegalArgumentException(file + ": sheet number " + sheetNumber + " not found"); } AvailsSheet as = addSheetHelper(wb, sheet); wb.close(); return as; }
From source file:com.movielabs.availslib.AvailSS.java
License:Open Source License
/** * Dump the contents (sheet-by-sheet) of an Excel spreadsheet * @param file name of the Excel .xlsx spreadsheet * @throws Exception if any error is encountered (e.g. non-existant or corrupt file) *//*from www . ja v a2s. c o m*/ public static void dumpFile(String file) throws Exception { Workbook wb = new XSSFWorkbook(new FileInputStream(file)); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); System.out.println("Sheet <" + wb.getSheetName(i) + ">"); for (Row row : sheet) { System.out.println("rownum: " + row.getRowNum()); for (Cell cell : row) { System.out.println(" | " + cell.toString()); } } } wb.close(); }
From source file:com.mycompany.bandaru_exam.ReadfromExcel.java
public List getAccountListFromExcel() { List accountList = new ArrayList(); FileInputStream fis = null;//from ww w. j a v a 2 s. com try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { Account account = new Account(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 0 contains first name */ if (cell.getColumnIndex() == 0) { account.setFirstName(cell.getStringCellValue()); } /* Cell with index 1 contains last name */ if (cell.getColumnIndex() == 1) { account.setLastName(cell.getStringCellValue()); } /* Cell with index 2 contains account number */ if (cell.getColumnIndex() == 2) { account.setAccNumber(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 3 contains account number */ if (cell.getColumnIndex() == 3) { account.setBalance(cell.getNumericCellValue()); } } } /* end iterating a row, add all the elements of a row in list */ accountList.add(account); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //Remove Headers accountList.remove(0); return accountList; }
From source file:com.mycompany.gannaraputakehomeexam.ReadingFromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;//from www.j a v a 2 s .c o m try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { SongsList song = new SongsList(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 1 contains Album name */ if (cell.getColumnIndex() == 1) { song.setAlbumname(cell.getStringCellValue()); } /* Cell with index 2 contains Genre */ if (cell.getColumnIndex() == 2) { song.setGenre(cell.getStringCellValue()); } /* Cell with index 3 contains Artist name */ if (cell.getColumnIndex() == 3) { song.setArtist(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 0 contains Sno */ if (cell.getColumnIndex() == 0) { song.setSno((int) cell.getNumericCellValue()); } /* Cell with index 5 contains Critic score. */ else if (cell.getColumnIndex() == 5) { song.setCriticscore((int) cell.getNumericCellValue()); } /* Cell with index 4 contains Release date */ else if (cell.getColumnIndex() == 4) { Date dateValue = null; if (DateUtil.isCellDateFormatted(cell)) { dateValue = cell.getDateCellValue(); } song.setReleasedate(dateValue); } } } /* end iterating a row, add all the elements of a row in list */ songList.add(song); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return songList; }
From source file:com.mycompany.gayamtakehomeexam.ReadfromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;//ww w . ja v a 2 s . com try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { Song song = new Song(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 1 contains Album name */ if (cell.getColumnIndex() == 1) { song.setAlbumname(cell.getStringCellValue()); } /* Cell with index 2 contains Genre */ if (cell.getColumnIndex() == 2) { song.setGenre(cell.getStringCellValue()); } /* Cell with index 3 contains Artist name */ if (cell.getColumnIndex() == 3) { song.setArtist(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 0 contains Sno */ if (cell.getColumnIndex() == 0) { song.setSno((int) cell.getNumericCellValue()); } /* Cell with index 5 contains Critic score. */ else if (cell.getColumnIndex() == 5) { song.setCriticscore((int) cell.getNumericCellValue()); } /* Cell with index 4 contains Release date */ else if (cell.getColumnIndex() == 4) { Date dateValue = null; if (DateUtil.isCellDateFormatted(cell)) { dateValue = cell.getDateCellValue(); } song.setReleasedate(dateValue); } } } /* end iterating a row, add all the elements of a row in list */ songList.add(song); } } fis.close(); } catch (FileNotFoundException e) { } catch (IOException e) { } return songList; }