List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:mindbodymerge.Parser.java
/** * Parses the membership.XLSX file to make a dictionary of student names and student numbers * Format is membershipDictionary.get(i) is the student number and membershipDictionary.get(i+1) is the student name for that student number * This is only because in the excel file the student number comes first, if this changes you will have to change the logic in the parseMemberList method * Almost the exact same logic as parseSales method, so look at that if this is confusing * * @param membershipDictionary: dictionary of student numbers/names where the student number membershipDictionary.get(i) correlates to the student name membershipDictionary.get(i+1) *///from w w w . j a v a 2 s . co m private void parseMembership(ArrayList<String> membershipDictionary) { //IOException try { int iterationCount; Double studentNumber; String studentString; FileInputStream fis = new FileInputStream(membership); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); XSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator<Row> rowIterator = mySheet.iterator(); rowIterator.next(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); iterationCount = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //This is the only part in which this is different from parseSales method //In the current format of the excel sheet, there are some rows in the middle of the sheet which contains column headers, not the data we want //In order to get rid of this data, we check if the data in column A is a number, //If it isn't, it's column header data and we skip over it by setting the iterationCount to 2 (which is past any data we need) if (iterationCount == 0) { //Check if the cellType is numeric by comparing it to 0 because Cell.CELL_TYPE_NUMERIC == 0) if (cell.getCellType() == 0) { studentNumber = cell.getNumericCellValue(); studentString = String.format("%.0f", studentNumber); membershipDictionary.add(studentString); } else iterationCount = 2; } //Get the student name that correlates to the student number we just parsed else if (iterationCount == 1) { //Have to replace some super weird unicode garbage from the data membershipDictionary .add(cell.getStringCellValue().replaceAll("\u00a0", "").replaceAll(" ", "")); } iterationCount++; } } //Testing block, should have a student number followed by a student name for each person in the excel sheet // System.out.println(membershipDictionary.size()); // System.out.println(membershipDictionary); } catch (IOException e) { } }
From source file:Model.AccountImport.java
public String excelReader(String path_file, List excelColumn) { String extension = FilenameUtils.getExtension(path_file); if (extension.equalsIgnoreCase(".xlsx")) { try {/*ww w. j a v a 2 s . c om*/ FileInputStream file = new FileInputStream(new File(path_file)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); 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: System.out.print(cell.getNumericCellValue() + " | "); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + " | "); break; } } System.out.println(""); } file.close(); } catch (Exception e) { e.printStackTrace(); } } else if (extension.equalsIgnoreCase(".xls")) { try { FileInputStream file = new FileInputStream(new File(path_file)); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); 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: System.out.print(cell.getNumericCellValue() + " | "); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + " | "); break; } } System.out.println(""); } file.close(); } catch (Exception e) { e.printStackTrace(); } } return null; }
From source file:model.converter.ConvertToInstances.java
public void convertXlsxToCSV(File inputFile, File outputFile) { // For storing data into CSV files StringBuffer data = new StringBuffer(); try {/*w w w.j av a 2s .c o m*/ FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook object for XLSX file XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile)); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: data.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: data.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: data.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: data.append("" + ","); break; default: data.append(cell + ","); } } data.append("\r\n"); } String csvData = data.toString(); csvData = csvData.replaceAll(",\r\n", "\r\n"); fos.write(csvData.getBytes()); fos.close(); } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:Model.LeerCaminos.java
public LeerCaminos() { boolean bandera = true; JFileChooser Examinar = new JFileChooser(); Examinar.setFileFilter(new FileNameExtensionFilter("Archivos excel", "xls", "xlsx")); int opcion = Examinar.showOpenDialog(Examinar); File fileName = null;//from ww w. j a v a 2 s .c om List cellData = new ArrayList(); if (opcion == JFileChooser.APPROVE_OPTION) { fileName = Examinar.getSelectedFile().getAbsoluteFile(); try { FileInputStream fileInputStream = new FileInputStream(fileName); XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream); XSSFSheet hssfSheet = workBook.getSheetAt(0); Iterator rowIterator = hssfSheet.rowIterator(); while (rowIterator.hasNext()) { XSSFRow hssfRow = (XSSFRow) rowIterator.next(); Iterator iterator = hssfRow.cellIterator(); if (iterator != null) { List cellTemp = new ArrayList(); while (iterator.hasNext()) { XSSFCell hssfCell = (XSSFCell) iterator.next(); cellTemp.add(hssfCell); } cellData.add(cellTemp); } } } catch (Exception e) { e.printStackTrace(); } Llenarmatriz(cellData); // ImprimirMatriz(); } }
From source file:Model.Picture.java
private static ArrayList<String> enterUploadedData(String fileName) { ArrayList<String> errors = new ArrayList<String>(); try {/*from w w w .ja va 2 s.c o m*/ FileInputStream file = new FileInputStream(Constants.TEMP_DIR + fileName); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum() + 1; int colStart = sheet.getRow(rowStart).getFirstCellNum(); int colEnd = sheet.getRow(rowStart).getLastCellNum(); int[] indices = ExcelTools.getColumnIndices(colStart, colEnd, sheet.getRow(rowStart)); if (Tools.arrayContains(indices, -1)) { errors.add(Constants.IMPROPER_EXCEL_FORMAT); return errors; } errors.addAll(ExcelTools.readFile(indices, sheet, rowStart + 1, rowEnd)); } catch (IOException e) { e.printStackTrace(System.out); } return errors; }
From source file:mpqq.MPQQ.java
private static XSSFWorkbook procTab1(XSSFWorkbook referenceWB, XSSFWorkbook mpqqWB, int referenceFirstRow) { XSSFSheet trackerTab = referenceWB.getSheetAt(USE_FOR_TAB1); XSSFSheet tab1 = mpqqWB.getSheetAt(1); //Iterator<Row> rowIterator = trackerTab.iterator(); DataFormatter df = new DataFormatter(); //MPQQ first row int rowIdx = 11; for (int refCurRow = referenceFirstRow; refCurRow <= trackerTab.getLastRowNum(); refCurRow++) { Row row = trackerTab.getRow(refCurRow); //Check if row is visible if (!row.getZeroHeight() || (row.isFormatted() && row.getRowStyle().getHidden())) { int colIdx = 1; //Iterate trough the Columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getColumnIndex()) { case 3: Cell currentCell = checkRowCellExists(tab1, rowIdx, colIdx); currentCell.setCellValue(df.formatCellValue(row.getCell(T2PEPSICO_STOCK_CODE))); //Go to next Column colIdx++;/*ww w . ja va 2 s. com*/ break; case 4: break; default: } } //Jump Next Row rowIdx++; } } return mpqqWB; }
From source file:mpqq.MPQQ.java
/** * @param args the command line arguments *///from w ww .ja v a 2 s. co m public static void main(String[] args) { try { //Read Reference File FileInputStream referenceFile = new FileInputStream( new File("C:\\Personal\\09168336\\Documents\\iRef.xlsx")); XSSFWorkbook reference = new XSSFWorkbook(referenceFile); reference.close(); FileInputStream mpqqFile = new FileInputStream( new File("C:\\Personal\\09168336\\Documents\\mpqq.xlsm")); XSSFWorkbook mpqq = new XSSFWorkbook(mpqqFile); mpqqFile.close(); int referenceStartRow = 156; //Create a MPQQ for each supplier in the reference document String currentSupplier = reference.getSheetAt(USE_FOR_TAB1).getRow(referenceStartRow) .getCell(T2PEPSICO_SUPPLIER_SITE_NAME).getStringCellValue(); System.out.println(currentSupplier); mpqq = procTab1(reference, mpqq, referenceStartRow); FileOutputStream outFile = new FileOutputStream( new File("C:\\Personal\\09168336\\Documents\\mpqq.xlsm")); mpqq.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } System.out.println("Testing"); }
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;//w ww. j a v a2s. c o m 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:mx.infotec.dads.arq.excel.ImplementExcel.java
public List<XSSFSheet> getSheet() throws ExcelException { InputStream excelFileToRead = null; List<XSSFSheet> lst = new ArrayList<>(); try {/*from w w w .j a v a2 s .co m*/ excelFileToRead = new FileInputStream(this.path); XSSFWorkbook wb = new XSSFWorkbook(excelFileToRead); int numberOfSheet = wb.getNumberOfSheets(); for (int i = 0; i < numberOfSheet; i++) { lst.add(wb.getSheetAt(i)); } } catch (IOException ex) { throw new ExcelException("Error a obtener los libros del excel ", ex); } finally { try { excelFileToRead.close(); } catch (IOException ex) { Logger.getLogger(ImplementExcel.class.getName()).log(Level.SEVERE, null, ex); } } return lst; }
From source file:nc.noumea.mairie.appock.services.impl.ImportExcelServiceImpl.java
License:Open Source License
@Override public List<String> importCatalogue(Media media, Catalogue catalogue) throws IOException { List<String> messages = new ArrayList<>(); XSSFWorkbook workbook = new XSSFWorkbook(media.getStreamData()); XSSFSheet firstSheet = workbook.getSheetAt(0); int nbLigneTraite = 0; for (int ligne = 1; ligne < firstSheet.getPhysicalNumberOfRows(); ligne++) { try {/*from w w w. j av a 2 s .com*/ traiterLigne(firstSheet, ligne, media, catalogue); nbLigneTraite++; } catch (ImportExcelException e) { messages.add(e.getMessage()); } } if (!messages.isEmpty()) { messages.add(0, "Certaines rfrences n'ont pas pu tre importes : "); } StringBuilder statistiques = new StringBuilder("\nStatistiques :"); statistiques.append("\n - Nombre de lignes total : " + (firstSheet.getPhysicalNumberOfRows() - 1)); statistiques.append("\n - Nombre de rfrences importes : " + nbLigneTraite); statistiques.append("\n - Nombre de rfrences ignores : " + (firstSheet.getPhysicalNumberOfRows() - 1 - nbLigneTraite)); messages.add(statistiques.toString()); workbook.close(); return messages; }