List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell
@Override public XSSFCell getCell(int cellnum)
From source file:mx.edu.um.mateo.activos.dao.impl.ActivoDaoHibernate.java
License:Open Source License
@Override @SuppressWarnings("unchecked") public void sube(byte[] datos, Usuario usuario, OutputStream out, Integer codigoInicial) { Date inicio = new Date(); int idx = 5;//from w ww . j av a 2 s . c om int i = 0; SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat sdf2 = new SimpleDateFormat("dd/MM/yy"); SimpleDateFormat sdf3 = new SimpleDateFormat("dd-MM-yy"); MathContext mc = new MathContext(16, RoundingMode.HALF_UP); NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); nf.setMaximumFractionDigits(0); nf.setMinimumIntegerDigits(5); Transaction tx = null; try { String ejercicioId = "001-2013"; Map<String, CentroCosto> centrosDeCosto = new HashMap<>(); Map<String, TipoActivo> tipos = new HashMap<>(); Query tipoActivoQuery = currentSession() .createQuery("select ta from TipoActivo ta " + "where ta.empresa.id = :empresaId " + "and ta.cuenta.id.ejercicio.id.idEjercicio = :ejercicioId " + "and ta.cuenta.id.ejercicio.id.organizacion.id = :organizacionId"); log.debug("empresaId: {}", usuario.getEmpresa().getId()); log.debug("ejercicioId: {}", ejercicioId); log.debug("organizacionId: {}", usuario.getEmpresa().getOrganizacion().getId()); tipoActivoQuery.setLong("empresaId", usuario.getEmpresa().getId()); tipoActivoQuery.setString("ejercicioId", ejercicioId); tipoActivoQuery.setLong("organizacionId", usuario.getEmpresa().getOrganizacion().getId()); List<TipoActivo> listaTipos = tipoActivoQuery.list(); for (TipoActivo tipoActivo : listaTipos) { tipos.put(tipoActivo.getCuenta().getId().getIdCtaMayor(), tipoActivo); } log.debug("TIPOS: {}", tipos); Query proveedorQuery = currentSession().createQuery( "select p from Proveedor p where p.empresa.id = :empresaId and p.nombre = :nombreEmpresa"); proveedorQuery.setLong("empresaId", usuario.getEmpresa().getId()); proveedorQuery.setString("nombreEmpresa", usuario.getEmpresa().getNombre()); Proveedor proveedor = (Proveedor) proveedorQuery.uniqueResult(); Query codigoDuplicadoQuery = currentSession() .createQuery("select a from Activo a where a.empresa.id = :empresaId and a.codigo = :codigo"); XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(datos)); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ccostoFantasma = wb.createSheet("CCOSTO-FANTASMAS"); int ccostoFantasmaRow = 0; XSSFSheet sinCCosto = wb.createSheet("SIN-CCOSTO"); int sinCCostoRow = 0; XSSFSheet codigoAsignado = wb.createSheet("CODIGO-ASIGNADO"); int codigoAsignadoRow = 0; XSSFSheet fechaInvalida = wb.createSheet("FECHA-INVALIDA"); int fechaInvalidaRow = 0; XSSFSheet sinCosto = wb.createSheet("SIN-COSTO"); int sinCostoRow = 0; //tx = currentSession().beginTransaction(); for (idx = 5; idx <= 5; idx++) { XSSFSheet sheet = workbook.getSheetAt(idx); int rows = sheet.getPhysicalNumberOfRows(); for (i = 2; i < rows; i++) { log.debug("Leyendo pagina {} renglon {}", idx, i); XSSFRow row = sheet.getRow(i); if (row.getCell(0) == null) { break; } String nombreGrupo = row.getCell(0).getStringCellValue().trim(); switch (row.getCell(0).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: nombreGrupo = row.getCell(0).toString().trim(); break; case XSSFCell.CELL_TYPE_STRING: nombreGrupo = row.getCell(0).getStringCellValue().trim(); break; } TipoActivo tipoActivo = tipos.get(nombreGrupo); if (tipoActivo != null) { String cuentaCCosto = row.getCell(2).toString().trim(); if (StringUtils.isNotBlank(cuentaCCosto)) { CentroCosto centroCosto = centrosDeCosto.get(cuentaCCosto); if (centroCosto == null) { Query ccostoQuery = currentSession().createQuery("select cc from CentroCosto cc " + "where cc.id.ejercicio.id.idEjercicio = :ejercicioId " + "and cc.id.ejercicio.id.organizacion.id = :organizacionId " + "and cc.id.idCosto like :idCosto"); ccostoQuery.setString("ejercicioId", ejercicioId); ccostoQuery.setLong("organizacionId", usuario.getEmpresa().getOrganizacion().getId()); ccostoQuery.setString("idCosto", "1.01." + cuentaCCosto); ccostoQuery.setMaxResults(1); List<CentroCosto> listaCCosto = ccostoQuery.list(); if (listaCCosto != null && listaCCosto.size() > 0) { centroCosto = listaCCosto.get(0); } if (centroCosto == null) { XSSFRow renglon = ccostoFantasma.createRow(ccostoFantasmaRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } centrosDeCosto.put(cuentaCCosto, centroCosto); } String poliza = null; switch (row.getCell(4).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: poliza = row.getCell(4).toString(); poliza = StringUtils.removeEnd(poliza, ".0"); log.debug("POLIZA-N: {}", poliza); break; case XSSFCell.CELL_TYPE_STRING: poliza = row.getCell(4).getStringCellValue().trim(); log.debug("POLIZA-S: {}", poliza); break; } Boolean seguro = false; if (row.getCell(5) != null && StringUtils.isNotBlank(row.getCell(5).toString())) { seguro = true; } Boolean garantia = false; if (row.getCell(6) != null && StringUtils.isNotBlank(row.getCell(6).toString())) { garantia = true; } Date fechaCompra = null; if (row.getCell(7) != null) { log.debug("VALIDANDO FECHA"); XSSFCell cell = row.getCell(7); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: log.debug("ES NUMERIC"); if (DateUtil.isCellDateFormatted(cell)) { log.debug("ES FECHA"); fechaCompra = cell.getDateCellValue(); } else if (DateUtil.isCellInternalDateFormatted(cell)) { log.debug("ES FECHA INTERNAL"); fechaCompra = cell.getDateCellValue(); } else { BigDecimal bd = new BigDecimal(cell.getNumericCellValue()); bd = stripTrailingZeros(bd); log.debug("CONVIRTIENDO DOUBLE {} - {}", DateUtil.isValidExcelDate(bd.doubleValue()), bd); fechaCompra = HSSFDateUtil.getJavaDate(bd.longValue(), true); log.debug("Cal: {}", fechaCompra); } break; case Cell.CELL_TYPE_FORMULA: log.debug("ES FORMULA"); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { fechaCompra = DateUtil.getJavaDate(cellValue.getNumberValue(), true); } } } } if (row.getCell(7) != null && fechaCompra == null) { String fechaCompraString; if (row.getCell(7).getCellType() == Cell.CELL_TYPE_STRING) { fechaCompraString = row.getCell(7).getStringCellValue(); } else { fechaCompraString = row.getCell(7).toString().trim(); } try { fechaCompra = sdf.parse(fechaCompraString); } catch (ParseException e) { try { fechaCompra = sdf2.parse(fechaCompraString); } catch (ParseException e2) { try { fechaCompra = sdf3.parse(fechaCompraString); } catch (ParseException e3) { // no se pudo convertir } } } } if (fechaCompra == null) { XSSFRow renglon = fechaInvalida.createRow(fechaInvalidaRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } String codigo = null; switch (row.getCell(8).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: codigo = row.getCell(8).toString(); break; case XSSFCell.CELL_TYPE_STRING: codigo = row.getCell(8).getStringCellValue().trim(); break; } if (StringUtils.isBlank(codigo)) { codigo = "SIN CODIGO" + nf.format(codigoInicial); XSSFRow renglon = codigoAsignado.createRow(codigoAsignadoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue("SIN CODIGO" + codigoInicial); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); codigoInicial++; } else { // busca codigo duplicado if (codigo.contains(".")) { codigo = codigo.substring(0, codigo.lastIndexOf(".")); log.debug("CODIGO: {}", codigo); } codigoDuplicadoQuery.setLong("empresaId", usuario.getEmpresa().getId()); codigoDuplicadoQuery.setString("codigo", codigo); Activo activo = (Activo) codigoDuplicadoQuery.uniqueResult(); if (activo != null) { XSSFRow renglon = codigoAsignado.createRow(codigoAsignadoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9) .setCellValue(codigo + "-" + "SIN CODIGO" + nf.format(codigoInicial)); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); codigo = "SIN CODIGO" + nf.format(codigoInicial); codigoInicial++; } } String descripcion = null; if (row.getCell(9) != null) { switch (row.getCell(9).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: descripcion = row.getCell(9).toString(); descripcion = StringUtils.removeEnd(descripcion, ".0"); break; case XSSFCell.CELL_TYPE_STRING: descripcion = row.getCell(9).getStringCellValue().trim(); break; default: descripcion = row.getCell(9).toString().trim(); } } String marca = null; if (row.getCell(10) != null) { switch (row.getCell(10).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: marca = row.getCell(10).toString(); marca = StringUtils.removeEnd(marca, ".0"); break; case XSSFCell.CELL_TYPE_STRING: marca = row.getCell(10).getStringCellValue().trim(); break; default: marca = row.getCell(10).toString().trim(); } } String modelo = null; if (row.getCell(11) != null) { switch (row.getCell(11).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: modelo = row.getCell(11).toString(); modelo = StringUtils.removeEnd(modelo, ".0"); break; case XSSFCell.CELL_TYPE_STRING: modelo = row.getCell(11).getStringCellValue().trim(); break; default: modelo = row.getCell(11).toString().trim(); } } String serie = null; if (row.getCell(12) != null) { switch (row.getCell(12).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: serie = row.getCell(12).toString(); serie = StringUtils.removeEnd(serie, ".0"); break; case XSSFCell.CELL_TYPE_STRING: serie = row.getCell(12).getStringCellValue().trim(); break; default: serie = row.getCell(12).toString().trim(); } } String responsable = null; if (row.getCell(13) != null) { switch (row.getCell(13).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: responsable = row.getCell(13).toString(); responsable = StringUtils.removeEnd(responsable, ".0"); break; case XSSFCell.CELL_TYPE_STRING: responsable = row.getCell(13).getStringCellValue().trim(); break; default: responsable = row.getCell(13).toString().trim(); } } String ubicacion = null; if (row.getCell(14) != null) { switch (row.getCell(14).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: ubicacion = row.getCell(14).toString(); ubicacion = StringUtils.removeEnd(ubicacion, ".0"); break; case XSSFCell.CELL_TYPE_STRING: ubicacion = row.getCell(14).getStringCellValue().trim(); break; default: ubicacion = row.getCell(14).toString().trim(); } } BigDecimal costo = null; switch (row.getCell(15).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: costo = new BigDecimal(row.getCell(15).getNumericCellValue(), mc); log.debug("COSTO-N: {} - {}", costo, row.getCell(15).getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: costo = new BigDecimal(row.getCell(15).toString(), mc); log.debug("COSTO-S: {} - {}", costo, row.getCell(15).toString()); break; case XSSFCell.CELL_TYPE_FORMULA: costo = new BigDecimal( evaluator.evaluateInCell(row.getCell(15)).getNumericCellValue(), mc); log.debug("COSTO-F: {}", costo); } if (costo == null) { XSSFRow renglon = sinCosto.createRow(sinCostoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } Activo activo = new Activo(fechaCompra, seguro, garantia, poliza, codigo, descripcion, marca, modelo, serie, responsable, ubicacion, costo, tipoActivo, centroCosto, proveedor, usuario.getEmpresa()); this.crea(activo, usuario); } else { XSSFRow renglon = sinCCosto.createRow(sinCCostoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } } else { throw new RuntimeException( "(" + idx + ":" + i + ") No se encontro el tipo de activo " + nombreGrupo); } } } //tx.commit(); log.debug("################################################"); log.debug("################################################"); log.debug("TERMINO EN {} MINS", (new Date().getTime() - inicio.getTime()) / (1000 * 60)); log.debug("################################################"); log.debug("################################################"); wb.write(out); } catch (IOException | RuntimeException e) { //if (tx != null && tx.isActive()) { //tx.rollback(); //} log.error("Hubo problemas al intentar pasar datos de archivo excel a BD (" + idx + ":" + i + ")", e); throw new RuntimeException( "Hubo problemas al intentar pasar datos de archivo excel a BD (" + idx + ":" + i + ")", e); } }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporterImporterTest.java
License:Open Source License
@Test public void test() throws Exception { AppUser appUser = authHelper.getCurrentUser(); Service service = ajouterServiceToAppUser(appUser); List<ArticleStock> listeArticleStock = genererListeArticleStock(service); File exportFile = File.createTempFile("stock_test_export-", ".xlsx"); logger.info("Export pour inventaire : " + exportFile); // Export/*from w w w. ja va 2s .c o m*/ StockSpreadsheetExporter.exportToXls(service, listeArticleStock, catalogueService, new FileOutputStream(exportFile)); XSSFWorkbook workbookExport = new XSSFWorkbook(exportFile); XSSFSheet worksheet = workbookExport.getSheet("Inventaire"); Assert.assertNotNull(worksheet); // Verif for (int i = 0; i < worksheet.getLastRowNum(); i++) { XSSFRow row = worksheet.getRow(i); int col = 0; if (i == 0) { Assert.assertEquals("Photo", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Rfrence", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Libell", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Stock\n Appock", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Stock\n rel", row.getCell(col++).getStringCellValue()); } else { col++; Assert.assertEquals("REF_" + (i - 1), row.getCell(col++).getStringCellValue()); Assert.assertEquals("ARTICLE CATALOGUE " + (i - 1), row.getCell(col++).getStringCellValue()); Assert.assertEquals(i, (long) row.getCell(col++).getNumericCellValue()); } } // Import : toutes les quantits ont t augments de 1 File importFile1 = File.createTempFile("stock_test_import1-", ".xlsx"); logger.info("Import de l'inventaire : " + importFile1); OutputStream outputStream1 = new FileOutputStream(importFile1); XSSFWorkbook workbookImport = new XSSFWorkbook(exportFile); XSSFSheet worksheetImport = workbookImport.getSheet("Inventaire"); for (int i = 1; i < worksheetImport.getLastRowNum() + 1; i++) { XSSFRow row = worksheetImport.getRow(i); if (i == 5) { // Cellule en erreur row.getCell(4).setCellValue(" 05 "); } else { row.getCell(4).setCellValue(row.getCell(3).getNumericCellValue() + 1); } } workbookImport.write(outputStream1); outputStream1.flush(); outputStream1.close(); List<String> warnings = StockSpreadsheetImporter.importFromXls(service, stockService, new FileInputStream(importFile1)); // Verif Stock stock = stockService.findOne(service.getStock().getId()); Assert.assertEquals(10, stock.getListeArticleStock().size()); Assert.assertEquals(1, warnings.size()); logger.info("Warning message : " + warnings.get(0)); for (int i = 0; i < stock.getListeArticleStock().size(); i++) { ArticleStock articleStock = stock.getListeArticleStock().get(i); int oldQunatite = i + 1; if (i == 4) { // Quantit inchange Assert.assertEquals(oldQunatite, (int) articleStock.getQuantiteStock()); } else { Assert.assertEquals(oldQunatite + 1, (int) articleStock.getQuantiteStock()); } } }
From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadXlsxExcelJob.java
License:Open Source License
private void loadExcel(final String file) { final File fil = new File(file); if (fil.exists()) { canRead = true;// www . jav a 2 s . c o m if (grid != null) { try { InputStream inp = new FileInputStream(file); try { wb = new XSSFWorkbook(inp); } catch (Exception e) { MsgDialog.message("Wrong format!\nOnly Excel *.xlsx (2007-2010) is supported!"); canRead = false; e.printStackTrace(); } // wb = new HSSFWorkbook(inp); } catch (IOException ex) { ex.printStackTrace(); } if (canRead) { for (s = 0; s < wb.getNumberOfSheets(); s++) { Display display = PlatformUI.getWorkbench().getDisplay(); display.syncExec(new Runnable() { public void run() { String name = fil.getName(); grid = new Spread().spread(SampleView.getTabFolder(), 0, 0, name); SampleView.setGrid(grid); XSSFSheet sheet = wb.getSheetAt(s); int colCount = grid.getColumnCount(); int rowCount = grid.getItemCount(); int exelRow = endOfRow(sheet); int exelColumn = endOfColumn(sheet); // System.out.println(exelRow + " " + exelColumn // + "---" + sheet.getPhysicalNumberOfRows() + // " " + // sheet.getRow(0).getPhysicalNumberOfCells()); if (colCount < exelColumn) { int diff = exelColumn - colCount; for (int i = 0; i < diff; i++) { GridColumn column = new GridColumn(grid, SWT.NONE); column.setText("C " + (i + 1 + colCount)); column.setWidth(50); } } if (rowCount < exelRow) { int diff = exelRow - rowCount; for (int i = 0; i < diff; i++) { new GridItem(grid, SWT.NONE).setHeight(16); } } // Iterate over each row in the sheet int rows = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < exelRow; i++) { XSSFRow row = sheet.getRow(i); if (row == null) { for (int u = 0; u < exelColumn; u++) { grid.getItem(i).setText(u, " "); } } else { for (int u = 0; u < exelColumn; u++) { XSSFCell cell = row.getCell(u); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: String val = String.valueOf(cell.getNumericCellValue()); grid.getItem(i).setText(u, val); break; case XSSFCell.CELL_TYPE_STRING: XSSFRichTextString st = cell.getRichStringCellValue(); String val2 = st.getString(); grid.getItem(i).setText(u, val2); break; case XSSFCell.CELL_TYPE_FORMULA: try { String val3 = String.valueOf(cell.getRawValue()); grid.getItem(i).setText(u, val3); } catch (Exception e) { // System.out.println(e.getMessage()); String s2 = cell.getCellFormula(); grid.getItem(i).setText(u, s2); } break; case XSSFCell.CELL_TYPE_BLANK: grid.getItem(i).setText(u, " "); break; case XSSFCell.CELL_TYPE_BOOLEAN: boolean s4 = cell.getBooleanCellValue(); if (s4) { grid.getItem(i).setText(u, "TRUE"); } else { grid.getItem(i).setText(u, "FALSE"); } break; default: break; } } else { grid.getItem(i).setText(u, " "); } } } } } }); } wb = null; } } } else { MsgDialog.message("File not found!"); } }
From source file:nl.architolk.ldt.processors.ExcelConverter.java
License:Open Source License
public void generateData(PipelineContext context, ContentHandler contentHandler) throws SAXException { try {/* ww w.ja v a 2 s.c o m*/ // Read binary content of Excel file ByteArrayOutputStream os = new ByteArrayOutputStream(); Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os); readInputAsSAX(context, INPUT_DATA, base64ContentHandler); final byte[] fileContent = os.toByteArray(); final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); // Create workbook XSSFWorkbook workbook = new XSSFWorkbook(bais); DataFormatter formatter = new DataFormatter(); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); contentHandler.startDocument(); contentHandler.startElement("", "workbook", "workbook", new AttributesImpl()); for (int s = 0; s < workbook.getNumberOfSheets(); s++) { XSSFSheet sheet = workbook.getSheetAt(s); AttributesImpl sheetAttr = new AttributesImpl(); sheetAttr.addAttribute("", "name", "name", "CDATA", sheet.getSheetName()); contentHandler.startElement("", "sheet", "sheet", sheetAttr); for (int r = 0; r <= sheet.getLastRowNum(); r++) { XSSFRow row = sheet.getRow(r); if (row != null) { AttributesImpl rowAttr = new AttributesImpl(); rowAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(r)); contentHandler.startElement("", "row", "row", rowAttr); for (int c = 0; c < row.getLastCellNum(); c++) { XSSFCell cell = row.getCell(c); if (cell != null) { try { String cellvalue = formatter.formatCellValue(cell, evaluator); if (cellvalue != "") { AttributesImpl columnAttr = new AttributesImpl(); columnAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(cell.getColumnIndex())); contentHandler.startElement("", "column", "column", columnAttr); contentHandler.characters(cellvalue.toCharArray(), 0, cellvalue.length()); contentHandler.endElement("", "column", "column"); } } catch (Exception e) { } } } contentHandler.endElement("", "row", "row"); } } contentHandler.endElement("", "sheet", "sheet"); } contentHandler.endElement("", "workbook", "workbook"); contentHandler.endDocument(); } catch (IOException e) { throw new OXFException(e); } }
From source file:nl.detoren.ijc.io.OutputExcel.java
License:Open Source License
private Cell getCell(XSSFSheet sheet, int row, int col) { Cell cell = null;//from w ww .ja v a 2 s .c o m // Retrieve the row and create when not valid XSSFRow sheetrow = sheet.getRow(row); if (sheetrow == null) { sheetrow = sheet.createRow(row); } // Retrieve the correct cell from the column cell = sheetrow.getCell(col); if (cell == null) { cell = sheetrow.createCell(col); } return cell; }
From source file:offishell.excel.Excel.java
License:MIT License
/** * <p>//from w w w. j a va2 s . c om * ?????????? * </p> * * @param cellName ?? * @return */ public Signal<Row> takeBy(String cellName) { int index = indexOfHeader(cellName); return new Signal<Row>((observer, disposer) -> { for (int i = 1; i < sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); if (row != null) { XSSFCell cell = row.getCell(index); if (cell != null) { switch (cell.getCellTypeEnum()) { case BLANK: break; case STRING: String value = cell.getStringCellValue(); if (value != null && !value.isEmpty()) { observer.accept(new Row(row)); } break; default: observer.accept(new Row(row)); break; } } } } return disposer; }); }
From source file:offishell.excel.Excel.java
License:MIT License
/** * <p>/*from w ww . j a v a2s . c om*/ * ?????????????? * </p> * * @param columnIndex zero-based index. * @return */ public Signal<XSSFRow> rowsWithCellAt(int columnIndex) { return new Signal<XSSFRow>((observer, disposer) -> { for (int i = 1; i < sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); if (row == null) { continue; } XSSFCell cell = row.getCell(columnIndex); if (cell != null) { observer.accept(row); } else { break; } } return Disposable.empty(); }); }
From source file:offishell.excel.Excel.java
License:MIT License
private XSSFRow findFirstBlankRow() { XSSFRow head = sheet.getRow(0); // compute head size int headerSize = 0; for (; headerSize < head.getLastCellNum(); headerSize++) { Cell cell = head.getCell(headerSize); if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) { headerSize--;/*from ww w. ja v a 2s .co m*/ break; } } row: for (int i = 1; i < sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); if (row == null) { row = sheet.createRow(i); row.setHeightInPoints(30f); } for (int j = 0; j < headerSize; j++) { XSSFCell cell = row.getCell(j); if (cell == null) { XSSFCell created = row.createCell(j); created.setCellStyle(baseStyle); } else if (cell.getCellTypeEnum() != CellType.BLANK) { continue row; } } return row; } XSSFRow row = sheet.getRow(sheet.getLastRowNum()); if (row == null) { row = sheet.createRow(sheet.getLastRowNum()); row.setHeightInPoints(30f); } for (int j = 0; j < headerSize; j++) { XSSFCell cell = row.getCell(j); if (cell == null) { XSSFCell created = row.createCell(j); created.setCellStyle(baseStyle); } } return row; }
From source file:org.addition.epanet.network.io.input.ExcelParser.java
License:Open Source License
private int parseWorksheet(Network net, List<XSSFSheet> sheets, Pattern tagPattern, int errSum) throws ENException { for (XSSFSheet sheet : sheets) { boolean lastRowNull = true; boolean lastRowHeader = false; Network.SectType lastType = null; for (int rowCount = 0, tRowId = 0; rowCount < sheet.getPhysicalNumberOfRows(); tRowId++) { XSSFRow row = sheet.getRow(tRowId); if (row != null) { List<String> tokens = new ArrayList<String>(); String comments = ""; boolean allAreBold = true; for (int cellCount = 0, tCellId = 0; cellCount < row.getPhysicalNumberOfCells(); tCellId++) { XSSFCell cell = row.getCell(tCellId); if (cell != null) { String value = convertCell(cell); if (value.startsWith(";")) { comments += value; } else tokens.add(value); allAreBold = allAreBold & cell.getCellStyle().getFont().getBold(); // TODO remover cellCount++;//from w w w .j a v a 2s . co m } } if (tokens.size() > 0) { if (lastRowNull && tagPattern.matcher(tokens.get(0)).matches()) { lastType = Network.SectType.parse(tokens.get(0)); lastRowHeader = true; } else { String[] tokArray = tokens.toArray(new String[tokens.size()]); if (lastRowHeader && allAreBold) { //System.out.println("Formating Header : " + tokens.toArray(new String[tokens.size()])); } else { try { parseSect(net, lastType, comments, tokArray); } catch (ENException e) { String line = ""; for (String tk : tokArray) line += tk + " "; logException(lastType, e, line, tokArray); errSum++; } } } } lastRowNull = false; rowCount++; } if (row == null || row != null && row.getPhysicalNumberOfCells() == 0) { lastRowNull = true; continue; } } } return errSum; }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
/** * Creates a new line with values in the sheet. * /*from w w w . jav a 2 s .c om*/ * @param workbook * (XSSFWorkbook, required) workbook to create the row in * @param sheetRow * (XSSFSheetRow, required) sheet to create the data row in * @param sheetName * (String, required) name of the sheet * @param values * (String [], optional) if null or empty no work will be done, else the values written to the next line * @param bold * (boolean) true: the values will be set in bold font face, else normal * * @since 2.0.10 */ private void createSheetRow(XSSFWorkbook workbook, XSSFSheetRow sheetRow, String sheetName, List<String> values, boolean bold) { if (null != values && values.size() > 0) { // check if sheet exists and create if not if (null == sheetRow.sheet) { sheetRow.sheet = workbook.createSheet(sheetName); } // create cell style XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); if (bold) { // Create bold font Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(fontBold); } // create row XSSFRow row = sheetRow.sheet.createRow(sheetRow.rowCount++); // set values for (int i = 0; i < values.size(); i++) { row.getCell(i).setCellValue(values.get(i)); row.getCell(i).setCellStyle(cellStyle); } } }