List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook(InputStream s) throws IOException
From source file:com.github.lucapino.sheetmaker.parsers.mediainfo.MediaInfoExtractor.java
private void print(String rootPath) throws Exception { FileFilter filter = new FileFilter() { @Override// w ww . j av a 2s. com public boolean accept(File pathname) { String lowerCaseName = pathname.getName().toLowerCase(); return (lowerCaseName.endsWith("ifo") && lowerCaseName.startsWith("vts")) || lowerCaseName.endsWith("mkv") || lowerCaseName.endsWith("iso") || lowerCaseName.endsWith("avi"); } }; // parse all the tree under rootPath File rootFolder = new File(rootPath); File[] files = rootFolder.listFiles(); Arrays.sort(files); Map<File, List<File>> mediaMap = new TreeMap<>(); for (File file : files) { System.out.println(file.getName()); // name of the folder -> name of media List<File> fileList; if (file.isDirectory()) { fileList = recurseSubFolder(filter, file); if (!fileList.isEmpty()) { System.out.println("adding " + fileList); mediaMap.put(file, fileList); } } else { if (filter.accept(file)) { fileList = new ArrayList<>(); fileList.add(file); System.out.println("adding " + fileList); mediaMap.put(file, fileList); } } } Set<File> fileNamesSet = mediaMap.keySet(); File outputFile = new File("/home/tagliani/tmp/HD-report.xls"); Workbook wb = new HSSFWorkbook(new FileInputStream(outputFile)); Sheet sheet = wb.createSheet("Toshiba"); MediaInfo MI = new MediaInfo(); int j = 0; for (File mediaFile : fileNamesSet) { List<File> filesList = mediaMap.get(mediaFile); for (File fileInList : filesList) { List<String> audioTracks = new ArrayList<>(); List<String> videoTracks = new ArrayList<>(); List<String> subtitlesTracks = new ArrayList<>(); MI.Open(fileInList.getAbsolutePath()); String durationInt = MI.Get(MediaInfo.StreamKind.General, 0, "Duration", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); System.out.println(fileInList.getName() + " -> " + durationInt); if (StringUtils.isNotEmpty(durationInt) && Integer.valueOf(durationInt) >= 60 * 60 * 1000) { Row row = sheet.createRow(j); String duration = MI.Get(MediaInfo.StreamKind.General, 0, "Duration/String", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); // Create a cell and put a value in it. row.createCell(0).setCellValue(WordUtils.capitalizeFully(mediaFile.getName())); if (fileInList.getName().toLowerCase().endsWith("iso") || fileInList.getName().toLowerCase().endsWith("ifo")) { row.createCell(1).setCellValue("DVD"); } else { row.createCell(1) .setCellValue(FilenameUtils.getExtension(fileInList.getName()).toUpperCase()); } row.createCell(2).setCellValue(FileUtils.byteCountToDisplaySize(FileUtils.sizeOf(mediaFile))); // row.createCell(3).setCellValue(fileInList.getAbsolutePath()); row.createCell(3).setCellValue(duration); // MPEG-2 720x576 @ 25fps 16:9 String format = MI.Get(MediaInfo.StreamKind.Video, 0, "Format", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); row.createCell(4).setCellValue(format); String width = MI.Get(MediaInfo.StreamKind.Video, 0, "Width", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); String height = MI.Get(MediaInfo.StreamKind.Video, 0, "Height", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); row.createCell(5).setCellValue(width + "x" + height); String fps = MI.Get(MediaInfo.StreamKind.Video, 0, "FrameRate", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); row.createCell(6).setCellValue(fps); String aspectRatio = MI.Get(MediaInfo.StreamKind.Video, 0, "DisplayAspectRatio/String", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); row.createCell(7).setCellValue(aspectRatio); int audioStreamNumber = MI.Count_Get(MediaInfo.StreamKind.Audio); for (int i = 0; i < audioStreamNumber; i++) { String audioTitle = MI.Get(MediaInfo.StreamKind.Audio, i, "Title", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); String language = MI.Get(MediaInfo.StreamKind.Audio, i, "Language/String3", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); String codec = MI.Get(MediaInfo.StreamKind.Audio, i, "Codec", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); String channels = MI.Get(MediaInfo.StreamKind.Audio, i, "Channel(s)", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); String sampleRate = MI.Get(MediaInfo.StreamKind.Audio, i, "SamplingRate/String", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); // AC3 ITA 5.1 48.0KHz StringBuilder sb = new StringBuilder(); if (StringUtils.isEmpty(audioTitle)) { sb.append(codec).append(" ").append(language.toUpperCase()).append(" ") .append(channels); } else { sb.append(audioTitle); } sb.append(" ").append(sampleRate); audioTracks.add(sb.toString()); } int textStreamNumber = MI.Count_Get(MediaInfo.StreamKind.Text); for (int i = 0; i < textStreamNumber; i++) { String textLanguage = MI.Get(MediaInfo.StreamKind.Text, i, "Language/String", MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name); subtitlesTracks.add(textLanguage); } MI.Close(); row.createCell(8).setCellValue(audioTracks.toString()); row.createCell(9).setCellValue(subtitlesTracks.toString()); j++; } } // System.out.println(mediaName); } try (FileOutputStream fileOut = new FileOutputStream(outputFile)) { wb.write(fileOut); } }
From source file:com.github.s4ke.worktimegen.Main.java
License:BEER-WARE LICENSE
public static void generateExcelSheet(int year, int month, List<Work> workObjs) throws IOException { try (InputStream is = Main.class.getResourceAsStream("/template_urlaub.xls")) { HSSFWorkbook workbook = new HSSFWorkbook(is); HSSFSheet sheet = workbook.getSheetAt(0); GregorianCalendar calendar = new GregorianCalendar(); calendar.set(year, month - 1, 1); sheet.getRow(7).getCell(2).setCellValue(DATE_FORMAT.format(calendar.getTime())); calendar.set(year, month - 1, calendar.getActualMaximum(Calendar.DAY_OF_MONTH)); sheet.getRow(7).getCell(4).setCellValue(DATE_FORMAT.format(calendar.getTime())); int startRow = 11; int endRow = 33; if (workObjs.size() > (endRow - startRow)) { throw new AssertionError("template has too few rows"); }//from ww w .j a v a 2 s .co m int curRow = startRow; for (Work work : workObjs) { Row row = sheet.getRow(curRow); row.getCell(0).setCellValue(work.date); row.getCell(1).setCellValue(pad(work.startHours) + ":" + pad(work.startMinutes)); row.getCell(2).setCellValue(pad(work.endHours) + ":" + pad(work.endMinutes)); ++curRow; } try (FileOutputStream fos = new FileOutputStream( new File("zeiterfassung_braun_" + year + "_" + month + ".xls"))) { workbook.write(fos); } } }
From source file:com.github.xiilei.ecdiff.Processor.java
License:Apache License
private Workbook readExcelFileByext(File file) throws IOException { String ext = getExt(file.getName()); Workbook wb = null;//from www. ja v a 2 s . co m switch (ext) { case "xls": wb = new HSSFWorkbook(new FileInputStream(file)); break; case "xlsx": wb = new XSSFWorkbook(new FileInputStream(file)); break; default: throw new IOException("Does not recognize the extension:" + ext); } return wb; }
From source file:com.glaf.base.modules.todo.TodoXlsReader.java
License:Apache License
public List<Todo> readXls(java.io.InputStream inputStream) { List<Todo> todos = new java.util.ArrayList<Todo>(); HSSFWorkbook wb = null;/*from ww w.ja va 2 s . c o m*/ try { wb = new HSSFWorkbook(inputStream); } catch (Exception ex) { throw new RuntimeException(ex); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(1); Map<Integer, String> keyMap = new java.util.HashMap<Integer, String>(); Map<String, Object> dataMap = new java.util.HashMap<String, Object>(); int cells = row.getPhysicalNumberOfCells(); for (int colIndex = 0; colIndex < cells; colIndex++) { HSSFCell cell = row.getCell(colIndex); keyMap.put(colIndex, cell.getStringCellValue()); } Set<String> keys = new HashSet<String>(); for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) { HSSFRow rowx = sheet.getRow(rowIndex); if (rowx == null) { continue; } // System.out.println(); dataMap.clear(); for (int colIndex = 0; colIndex < cells; colIndex++) { String fieldName = keyMap.get(colIndex); HSSFCell cell = rowx.getCell(colIndex); if (cell == null) { continue; } Object cellValue = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue = cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: if (StringUtils.isNotEmpty(cell.getRichStringCellValue().getString())) { cellValue = cell.getRichStringCellValue().getString(); } break; default: if (StringUtils.isNotEmpty(cell.getStringCellValue())) { cellValue = cell.getStringCellValue(); } break; } if (cellValue != null) { dataMap.put(fieldName, cellValue); if ("id".equals(fieldName)) { cellValue = cellValue.toString().substring(0, cellValue.toString().indexOf(".")); dataMap.put(fieldName, cellValue); } } } if (dataMap.get("code") != null) { String id = ParamUtils.getString(dataMap, "id"); if (!keys.contains(ParamUtils.getString(dataMap, "code"))) { if (id != null && StringUtils.isNotEmpty(id)) { Todo model = new Todo(); Tools.populate(model, dataMap); if (ParamUtils.getInt(dataMap, "limitDay") > 0) { model.setLimitDay(ParamUtils.getInt(dataMap, "limitDay")); } todos.add(model); keys.add(model.getCode()); } } } } return todos; }
From source file:com.glaf.core.todo.util.TodoXlsReader.java
License:Apache License
public List<Todo> readXls(java.io.InputStream inputStream) { List<Todo> todos = new java.util.ArrayList<Todo>(); HSSFWorkbook wb = null;//ww w .j av a 2 s . c om try { wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(1); Map<Integer, String> keyMap = new java.util.HashMap<Integer, String>(); Map<String, Object> dataMap = new java.util.HashMap<String, Object>(); int cells = row.getPhysicalNumberOfCells(); for (int colIndex = 0; colIndex < cells; colIndex++) { HSSFCell cell = row.getCell(colIndex); keyMap.put(colIndex, cell.getStringCellValue()); } int sortNo = 1; Set<String> keys = new HashSet<String>(); for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) { HSSFRow rowx = sheet.getRow(rowIndex); if (rowx == null) { continue; } // System.out.println(); dataMap.clear(); for (int colIndex = 0; colIndex < cells; colIndex++) { String fieldName = keyMap.get(colIndex); HSSFCell cell = rowx.getCell(colIndex); if (cell == null) { continue; } Object cellValue = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue = cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: if (StringUtils.isNotEmpty(cell.getRichStringCellValue().getString())) { cellValue = cell.getRichStringCellValue().getString(); } break; default: if (StringUtils.isNotEmpty(cell.getStringCellValue())) { cellValue = cell.getStringCellValue(); } break; } if (cellValue != null) { dataMap.put(fieldName, cellValue); // System.out.print("\t" + fieldName + "=" + cellValue); } } if (dataMap.get("code") != null) { String id = ParamUtils.getString(dataMap, "id"); Todo model = new Todo(); dataMap.remove("id"); Tools.populate(model, dataMap); if (!keys.contains(model.getCode())) { model.setSortNo(sortNo++); if (id != null) { model.setId(Long.parseLong(id)); } if (ParamUtils.getDouble(dataMap, "limitDay") > 0) { model.setLimitDay(ParamUtils.getInt(dataMap, "limitDay")); } todos.add(model); keys.add(model.getCode()); } } } } catch (Exception ex) { throw new RuntimeException(ex); } finally { if (wb != null) { try { wb.close(); wb = null; } catch (IOException e) { } } } return todos; }
From source file:com.globalsight.util.ExcelUtil.java
License:Apache License
public static Workbook getWorkbook(String filename) { Workbook workbook = null;/*from w ww. j av a2s .com*/ if (StringUtil.isEmpty(filename) || !isExcel(filename)) return null; File file = null; try { file = new File(filename); if (!file.exists() || file.isDirectory()) return null; InputStream is = new FileInputStream(file); if (isXls(filename)) workbook = new HSSFWorkbook(is); else workbook = new XSSFWorkbook(OPCPackage.open(file)); } catch (Exception e) { logger.error("Cannot open Excel file correctly.", e); } return workbook; }
From source file:com.globalsight.util.ExcelUtil.java
License:Apache License
public static Workbook getWorkbook(String filename, InputStream is) { Workbook workbook = null;// w w w.j av a 2 s . c o m if (StringUtil.isEmpty(filename) || !isExcel(filename) || is == null) return null; File file = null; try { file = new File(filename); if (!file.exists() || file.isDirectory()) return null; if (isXls(filename)) workbook = new HSSFWorkbook(is); else workbook = new XSSFWorkbook(OPCPackage.open(is)); } catch (Exception e) { logger.error("Cannot open Excel file correctly.", e); } return workbook; }
From source file:com.gnadenheimer.mg.frames.admin.FrameEntidadesAdmin.java
private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed try {//from w w w .ja v a 2 s . co m JFileChooser fc = new JFileChooser(); Integer returnVal = fc.showOpenDialog(this); if (returnVal == JFileChooser.APPROVE_OPTION) { File file = fc.getSelectedFile(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; Integer rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); Integer cols = 0; // No of columns Integer tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (Integer i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } for (Integer r = 1; r <= rows; r++) { row = sheet.getRow(r); if (row != null) { if (!row.getCell(0).getStringCellValue().equals("")) { TblEntidades miembro = new TblEntidades(); miembro.setNombres(row.getCell(0).getStringCellValue()); if (row.getCell(1) != null) { miembro.setApellidos(row.getCell(1).getStringCellValue()); } else { miembro.setApellidos(""); } if (row.getCell(2).getCellType() == Cell.CELL_TYPE_STRING) { if (row.getCell(2).getStringCellValue().replaceAll("[^\\d.]", "").equals("")) { miembro.setCtacte(99999); } else { miembro.setCtacte(Integer.valueOf( row.getCell(2).getStringCellValue().replaceAll("[^\\d.]", ""))); } } else if (row.getCell(2).getCellType() == Cell.CELL_TYPE_NUMERIC) { miembro.setCtacte((int) (row.getCell(2).getNumericCellValue())); } List<TblEntidades> duplicadoList = entityManager.createQuery( "SELECT t FROM TblEntidades t where t.nombres = '" + miembro.getNombres() + "' and t.apellidos = '" + miembro.getApellidos() + "' and t.ctacte = " + miembro.getCtacte().toString(), TblEntidades.class).getResultList(); if (duplicadoList.size() > 0) { miembro = null; miembro = duplicadoList.get(0); } if (row.getCell(3) != null) { DecimalFormat df = new DecimalFormat("#0"); miembro.setRucSinDv(df.format(row.getCell(3).getNumericCellValue())); if (miembro.getRucSinDv().equals("0")) { miembro.setRucSinDv("44444401"); } } if (row.getCell(4) != null) { miembro.setDomicilio(row.getCell(4).getStringCellValue()); } if (row.getCell(5) != null) { miembro.setBox((int) row.getCell(5).getNumericCellValue()); } if (row.getCell(6) != null) { miembro.setFechaNacimiento( LocalDateTime.ofInstant(row.getCell(6).getDateCellValue().toInstant(), ZoneId.systemDefault()).toLocalDate()); } if (row.getCell(7) != null) { miembro.setFechaBautismo( LocalDateTime.ofInstant(row.getCell(7).getDateCellValue().toInstant(), ZoneId.systemDefault()).toLocalDate()); miembro.setIsMiembroActivo(true); } else { miembro.setIsMiembroActivo(false); } if (row.getCell(8) != null) { miembro.setFechaEntradaCongregacion( LocalDateTime.ofInstant(row.getCell(8).getDateCellValue().toInstant(), ZoneId.systemDefault()).toLocalDate()); } miembro.setAporteMensual(0); miembro.setIdFormaDePagoPreferida(listFormasDePago.get(0)); miembro.setIdUser(currentUser.getUser()); entityManager.persist(miembro); list.add(miembro); } } } } } catch (Exception ex) { JOptionPane.showMessageDialog(null, Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage()); LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex); } }
From source file:com.gnadenheimer.mg.frames.operaciones.ingresos.FrameColectasDetalle.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed try {//from w w w . j a v a2 s . co m JFileChooser fc = new JFileChooser(); Integer returnVal = fc.showOpenDialog(this); if (returnVal == JFileChooser.APPROVE_OPTION) { File file = fc.getSelectedFile(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; Integer rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); Integer cols = 0; // No of columns Integer tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (Integer i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } for (Integer r = 1; r <= rows; r++) { row = sheet.getRow(r); if (row != null) { TblEventoDetalle t = new TblEventoDetalle(); TblEventos currEvento = (TblEventos) cboFechaColecta.getSelectedItem(); t.setFechahora(currEvento.getFecha().atStartOfDay()); t.setIdCategoriaArticulo(entityManager.find(TblCategoriasArticulos.class, 1)); t.setIdEvento(currEvento); t.setIdUser(currentUser.getUser()); Integer ctaCte = 0; if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) { if (!row.getCell(0).getStringCellValue().replaceAll("[^\\d.]", "").equals("")) { ctaCte = Integer .valueOf(row.getCell(0).getStringCellValue().replaceAll("[^\\d.]", "")); } } else if (row.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC) { ctaCte = (int) (row.getCell(0).getNumericCellValue()); } if (ctaCte != 0) { final Integer cc = ctaCte; List<TblEntidades> list = listMiembros; Optional<TblEntidades> value = list.stream().filter(a -> a.getCtacte().equals(cc)) .findFirst(); if (value.isPresent()) { t.setIdEntidad(value.get()); t.setIdFormaDePagoPreferida(tblFormasDePagoList.get(0)); if (row.getCell(1).getCellType() == Cell.CELL_TYPE_NUMERIC) { t.setMonto((int) (row.getCell(1).getNumericCellValue())); entityManager.persist(t); listEventoDetalle.add(t); } } else { JOptionPane.showMessageDialog(null, "No hay entidad registrada con Cta.Cte N " + FormatCtaCte.format(ctaCte)); } } } } } } catch (Exception ex) { JOptionPane.showMessageDialog(null, Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage()); LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex); } }
From source file:com.googlecode.bdoc.testsupport.excel.ExcelExampleTables.java
License:Open Source License
public ExcelExampleTables(String xlsFilePath) { this.xlsFilePath = xlsFilePath; try {//www . ja va 2 s .c o m workbook = new HSSFWorkbook(new FileInputStream(xlsFilePath)); } catch (Exception e) { throw new IllegalArgumentException("Error reading xls from: " + xlsFilePath, e); } }