Example usage for org.apache.poi.ss.usermodel Row getCell

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getCell.

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

/**
 * Import the sheet and return key-values if its a schema
 * @param ds/*from  w ww. ja  v a2s.  co m*/
 * @param sheet
 * @param schema
 * @param isSchemaSheet
 * @return
 */
private static void importSheetSubset(ODLTableAlterable table, Sheet sheet, SchemaIO schema,
        boolean isSchemaSheet, int firstRow, int lastRow, int nbCols) {

    // get column names
    Row header = sheet.getRow(firstRow);
    for (int col = 0; col < nbCols; col++) {

        // try getting schema definition for the column
        String name = null;
        SchemaColumnDefinition dfn = null;
        if (header != null) {
            name = getFormulaSafeTextValue(header.getCell(col));
            if (name != null && schema != null) {
                dfn = schema.findDefinition(sheet.getSheetName(), name);
            }
        }
        name = getValidNewColumnName(name, table);

        // use the schema column definition if we have one
        if (dfn != null) {
            addColumnFromDfn(dfn, name, col, table);
        } else {

            // analyse the other rows for a 'best guess' type
            ODLColumnType chosenType = ODLColumnType.STRING;
            if (isSchemaSheet == false) {
                ColumnTypeEstimator typeEstimator = new ColumnTypeEstimator();
                for (int rowIndx = firstRow + 1; rowIndx <= lastRow; rowIndx++) {
                    Row row = sheet.getRow(rowIndx);
                    String value = getFormulaSafeTextValue(row.getCell(col));
                    typeEstimator.processValue(value);
                }

                chosenType = typeEstimator.getEstimatedType();
            }
            table.addColumn(col, name, chosenType, 0);
        }
    }

    // load all other rows
    for (int rowIndx = firstRow + 1; rowIndx <= lastRow; rowIndx++) {
        Row row = sheet.getRow(rowIndx);
        int outRowIndx = table.createEmptyRow(rowIndx);
        for (int col = 0; col < nbCols; col++) {
            String value = getFormulaSafeTextValue(row.getCell(col));
            table.setValueAt(value, outRowIndx, col);
        }

    }

}

From source file:com.opendoorlogistics.studio.LoadedDatastore.java

License:Open Source License

private void updateWorkbookWithModifications(Workbook wb, ExecutionReport report) {
    // parse the original tables; these will be held in the datastore with the same index as the sheet
    int nbOriginal = originalLoadedDs.getTableCount();
    if (nbOriginal != wb.getNumberOfSheets()) {
        throw new RuntimeException();
    }/* w w w.ja  v  a 2  s  . com*/

    ArrayList<ODLTableReadOnly> oldOnesToReadd = new ArrayList<>();
    for (int i = nbOriginal - 1; i >= 0; i--) {
        ODLTableReadOnly originalTable = originalLoadedDs.getTableAt(i);
        ODLTableReadOnly newTable = ds.getTableByImmutableId(originalTable.getImmutableId());

        if (newTable == null) {
            // table was deleted
            wb.removeSheetAt(i);
        } else if (DatastoreComparer.isSame(originalTable, newTable, DatastoreComparer.CHECK_ALL) == false) {
            Sheet sheet = wb.getSheetAt(i);

            boolean sameStructure = DatastoreComparer.isSameStructure(originalTable, newTable,
                    DatastoreComparer.CHECK_ALL);
            if (sameStructure) {
                // re-write all values but skip the header row
                int nbOversized = 0;
                for (int iRow = 0; iRow < newTable.getRowCount(); iRow++) {
                    int iTargetRow = iRow + 1;
                    Row row = sheet.getRow(iTargetRow);
                    if (row == null) {
                        row = sheet.createRow(iTargetRow);
                    }

                    int nc = newTable.getColumnCount();
                    for (int col = 0; col < nc; col++) {
                        Cell cell = row.getCell(col);
                        if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                            // don't set the value of formula cells...
                            continue;
                        }
                        if (cell == null) {
                            cell = row.createCell(col);
                        }

                        String sval = TableUtils.getValueAsString(newTable, iRow, col);
                        if (sval != null && sval.length() > PoiIO.MAX_CHAR_COUNT_IN_EXCEL_CELL) {
                            nbOversized++;
                        }
                        cell.setCellValue(sval);
                    }
                }

                // delete any rows after the last row (including 1 for the header)
                int lastOKRow = newTable.getRowCount();
                while (sheet.getLastRowNum() > lastOKRow) {
                    sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
                }

                if (nbOversized > 0 && report != null) {
                    report.log(PoiIO.getOversizedWarningMessage(nbOversized, newTable.getName()));
                    ;
                }

            } else {
                // delete and replace. replace after parsing all original tables as we can get table name conflicts
                wb.removeSheetAt(i);
                oldOnesToReadd.add(newTable);
            }

        }

    }

    // re-add any totally replaced tables
    for (ODLTableReadOnly table : oldOnesToReadd) {
        Sheet sheet = wb.createSheet(table.getName());
        if (sheet != null) {
            PoiIO.exportTable(sheet, table, report);
        }
    }

    // add new tables at the end
    for (int i = 0; i < ds.getTableCount(); i++) {
        ODLTableReadOnly newTable = ds.getTableAt(i);
        if (originalLoadedDs.getTableByImmutableId(newTable.getImmutableId()) == null) {
            // new table...
            Sheet sheet = wb.createSheet(newTable.getName());
            if (sheet != null) {
                PoiIO.exportTable(sheet, newTable, report);
            }
        }

    }
}

From source file:com.openitech.db.model.ExcelDataSource.java

License:Apache License

@Override
public boolean loadData(boolean reload, int oldRow) {
    boolean result = false;

    if (isDataLoaded && !reload) {
        return false;
    }/*from w ww  .  j av a  2s  .  c o  m*/
    if (sourceFile != null) {
        try {
            Workbook workBook = WorkbookFactory.create(new FileInputStream(sourceFile));
            //        HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(sourceFile));
            Sheet sheet = workBook.getSheetAt(0);
            DataFormatter dataFormatter = new DataFormatter(Locale.GERMANY);
            FormulaEvaluator formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator();

            int lastRowNum = sheet.getLastRowNum();

            boolean isFirstLineHeader = true;

            //count = sheet. - (isFirstLineHeader ? 1 : 0);
            int tempCount = 0;
            for (int j = 0; j <= lastRowNum; j++) {
                //zane se z 0
                Row row = row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }

                // display row number in the console.
                System.out.println("Row No.: " + row.getRowNum());
                if (isFirstLineHeader && row.getRowNum() == 0) {
                    populateHeaders(row);
                    continue;
                }
                tempCount++;

                Map<String, DataColumn> values;
                if (rowValues.containsKey(row.getRowNum())) {
                    values = rowValues.get(row.getRowNum());
                } else {
                    values = new HashMap<String, DataColumn>();
                    rowValues.put(row.getRowNum(), values);
                }

                // once get a row its time to iterate through cells.
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i <= lastCellNum; i++) {
                    DataColumn dataColumn = new DataColumn();
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        continue;
                    }
                    System.out.println("Cell No.: " + cell.getColumnIndex());
                    System.out.println("Value: " + dataFormatter.formatCellValue(cell));
                    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator));
                    } else {
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                    }

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        // cell type numeric.
                        System.out.println("Numeric value: " + cell.getNumericCellValue());
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                        break;
                    }
                    case Cell.CELL_TYPE_STRING:
                        // cell type string.
                        System.out.println("String value: " + cell.getStringCellValue());
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        // cell type string.
                        System.out.println("String value: " + cell.getBooleanCellValue());
                        dataColumn.setValue(cell.getBooleanCellValue(), Boolean.class);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        // cell type string.
                        System.out.println(
                                "Formula value: " + dataFormatter.formatCellValue(cell, formulaEvaluator));
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator));
                        break;
                    default:
                        dataColumn.setValue(cell.getStringCellValue(), String.class);
                        break;
                    }

                    values.put(getColumnName(cell.getColumnIndex()).toUpperCase(), dataColumn);

                }
            }

            count = tempCount;

            isDataLoaded = true;
            //se postavim na staro vrstico ali 1
            if (oldRow > 0) {
                absolute(oldRow);
            } else {
                first();
            }

            result = true;
        } catch (Exception ex) {
            Logger.getLogger(ExcelDataSource.class.getName()).log(Level.SEVERE, null, ex);
            result = false;
        }
    }

    return result;
}

From source file:com.openitech.db.model.ExcelDataSource.java

License:Apache License

private void populateHeaders(Row row) {
    columnCount = 0;//from w w  w  .  j a  v a  2 s. co m
    int lastCellNum = row.getLastCellNum();
    for (int i = 0; i <= lastCellNum; i++) {
        Cell cell = row.getCell(i);
        if (cell == null) {
            continue;
        }

        System.out.println("String value: " + cell.getStringCellValue());

        String header = cell.getStringCellValue();
        columnMapping.put(header, cell.getColumnIndex());
        columnMappingIndex.put(cell.getColumnIndex(), header);
        columnCount++;
    }
}

From source file:com.ostrichemulators.semtool.poi.main.POIReader.java

License:Open Source License

public static ImportData readNonloadingSheet(Workbook workbook) {
    ImportData id = new ImportData();

    int sheets = workbook.getNumberOfSheets();
    for (int sheetnum = 0; sheetnum < sheets; sheetnum++) {
        Sheet sheet = workbook.getSheetAt(sheetnum);
        String sheetname = workbook.getSheetName(sheetnum);

        // we need to shoehorn the arbitrary data from a spreadsheet into our
        // ImportData class, which has restrictions on the data...we're going
        // to do it by figuring out the row with the most columns, and then
        // naming all the columns with A, B, C...AA, AB...
        // then load everything as if it was plain data
        // first, figure out our max number of columns
        int rows = sheet.getLastRowNum();
        int maxcols = Integer.MIN_VALUE;
        for (int r = 0; r <= rows; r++) {
            Row row = sheet.getRow(r);
            if (null != row) {
                int cols = (int) row.getLastCellNum();
                if (cols > maxcols) {
                    maxcols = cols;//from   www  .j a va  2s . c om
                }
            }
        }

        // second, make "properties" for each column
        LoadingSheetData nlsd = new LoadingSheetData(sheetname, "A");
        for (int c = 1; c < maxcols; c++) {
            nlsd.addProperty(Integer.toString(c));
        }

        // lastly, fill the sheets
        for (int r = 0; r <= rows; r++) {
            Row row = sheet.getRow(r);
            if (null != row) {
                Map<String, Value> propmap = new HashMap<>();

                int lastpropcol = row.getLastCellNum();
                for (int c = 1; c <= lastpropcol; c++) {
                    String val = getString(row.getCell(c));
                    if (!val.isEmpty()) {
                        propmap.put(Integer.toString(c), VF.createLiteral(val));
                    }
                }

                nlsd.add(getString(row.getCell(0)), propmap);
            }
        }

        if (!nlsd.isEmpty()) {
            id.add(nlsd);
        }
    }

    return id;
}

From source file:com.pe.nisira.movil.view.action.MultitablaAction.java

public void upExcel(FileUploadEvent event) throws ParseException {
    try {//from   w  ww.j a v  a  2 s. c  o  m
        upFile = event.getFile();
        XSSFWorkbook workBook = new XSSFWorkbook(event.getFile().getInputstream());
        XSSFSheet hssfSheet = workBook.getSheetAt(0);
        Iterator<Row> rowIterator = hssfSheet.rowIterator();
        boolean exist = false;
        int filaDuplicada = 0;
        boolean estado = false;
        boolean firstLinea = true;
        while (rowIterator.hasNext()) {
            Row hssfRow = rowIterator.next();
            if (firstLinea) {
                firstLinea = false;
            } else {
                Multitabla xls = new Multitabla();
                hssfRow.getCell(1).setCellType(XSSFCell.CELL_TYPE_STRING);
                xls.setDESCRIPCION(hssfRow.getCell(2).getStringCellValue());
                xls.setABREV(hssfRow.getCell(3).getStringCellValue());
                xls.setEMPRESA(Integer.valueOf(idempresa));
                xls.setESTADO(true);
                if (hssfRow.getCell(1) != null
                        && hssfRow.getCell(0).getStringCellValue().equalsIgnoreCase("Si")) {
                    xls.setPalias(hssfRow.getCell(1).getStringCellValue());
                    listDetalleMultitablaTablaUp.add(xls);
                } else {
                    listMultitablaTablaUp.add(xls);
                }
                exist = false;
                for (int i = 0; i < listMultitablaTablaUp.size() - 1; i++) {
                    if (listMultitablaTablaUp.get(i).getDESCRIPCION().equalsIgnoreCase(
                            hssfRow.getCell(2).getStringCellValue()) && hssfRow.getRowNum() > 1) {
                        exist = true;
                        break;
                    }
                }
                if (exist) {
                    filaDuplicada = hssfRow.getRowNum() + 1;
                    break;
                }
            }
        }
        if (exist) {
            WebUtil.MensajeAlerta("Registro Duplicado. Fila : " + filaDuplicada
                    + ". \n Verifique el Excel e Intntelo otra vez.");
            listMultitablaTablaUp.clear();
        }
    } catch (IOException e) {
        System.out.println("Error en el Procesamiento : " + e.getMessage());
    }
}

From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java

public void upExcel(FileUploadEvent event) throws ParseException, Exception {
    try {/*from   w  w  w . ja  v  a2  s .c om*/
        listPaletaUp = new ArrayList<Paleta>();
        listDPaletaUp = new ArrayList<Dpaleta>();
        listerros = new ArrayList<Object[]>();
        listderros = new ArrayList<Object[]>();
        upFile = event.getFile();
        XSSFWorkbook workBook = new XSSFWorkbook(event.getFile().getInputstream());
        XSSFSheet hssfSheet = workBook.getSheetAt(0);
        XSSFSheet hssfSheetD = workBook.getSheetAt(1);
        Iterator<Row> rowIterator = hssfSheet.rowIterator();
        boolean exist = false;
        int filaDuplicada = 0;
        int filaDuplicadaD = 0;
        boolean estado = false;
        boolean firstLinea = true;
        int k = 2;
        while (rowIterator.hasNext()) {
            Row hssfRow = rowIterator.next();

            if (firstLinea) {
                firstLinea = false;
            } else {
                Paleta xls = new Paleta();
                xls.setIdempresa(hssfRow.getCell(0).getStringCellValue());
                xls.setIdregistropaleta(hssfRow.getCell(1).getStringCellValue());
                xls.setIdemisor(hssfRow.getCell(2).getStringCellValue());
                xls.setIdoperacion(hssfRow.getCell(3).getStringCellValue());
                String tempN = null;
                if (hssfRow.getCell(4) != null) {
                    tempN = hssfRow.getCell(4).getStringCellValue();
                }
                xls.setNumoperacion(tempN);
                xls.setIdmotivopaleta(hssfRow.getCell(5).getStringCellValue());
                xls.setIddocumento(hssfRow.getCell(6).getStringCellValue());
                xls.setSerie(hssfRow.getCell(7).getStringCellValue());
                xls.setNumero(hssfRow.getCell(8).getStringCellValue());
                //de string a timestamp y a string denuevo
                DateFormat formatter;
                //                    formatter = new SimpleDateFormat("dd/MM/yyyy");
                //                    Date date = (Date) formatter.parse();
                Timestamp timeStampDate = new Timestamp(hssfRow.getCell(9).getDateCellValue().getTime());

                SimpleDateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
                String Fecha = dateFormat.format(timeStampDate);
                xls.setFecha(Fecha);
                xls.setPeriodo(String.valueOf(hssfRow.getCell(10).getNumericCellValue()));
                xls.setIdestado(hssfRow.getCell(11).getStringCellValue());
                xls.setIdclieprov(String.valueOf(hssfRow.getCell(12).getNumericCellValue()));
                xls.setNropaleta(hssfRow.getCell(13).getStringCellValue());
                xls.setIdenvase(hssfRow.getCell(14).getStringCellValue());
                hssfRow.getCell(15).setCellType(XSSFCell.CELL_TYPE_STRING);
                xls.setIdsucursal((String) hssfRow.getCell(15).getStringCellValue());
                String tempA = null;
                if (hssfRow.getCell(16) != null) {
                    tempA = hssfRow.getCell(16).getStringCellValue();
                }
                xls.setIdalmacen(tempA);
                String tempEm = null;
                if (hssfRow.getCell(17) != null) {
                    tempEm = hssfRow.getCell(17).getStringCellValue();
                }
                xls.setIdembalaje(tempEm);
                xls.setIdcultivo(hssfRow.getCell(18).getStringCellValue());
                xls.setIdvariedad(hssfRow.getCell(19).getStringCellValue());
                String tempO = null;
                if (hssfRow.getCell(20) != null) {
                    tempO = hssfRow.getCell(20).getStringCellValue();
                }
                xls.setObservaciones(tempO);
                xls.setVentana(hssfRow.getCell(21).getStringCellValue());
                xls.setCantidad(String.valueOf(hssfRow.getCell(22).getNumericCellValue()));
                xls.setCerrado(String.valueOf(hssfRow.getCell(23).getNumericCellValue()));
                xls.setSincroniza(hssfRow.getCell(24).getStringCellValue());
                //de string a timestamp y a string denuevo
                Timestamp timeStampDate2 = new Timestamp(hssfRow.getCell(25).getDateCellValue().getTime());
                String FechaC = dateFormat.format(timeStampDate2);
                xls.setFechacreacion(FechaC);
                String temp = null;
                if (hssfRow.getCell(26) != null) {
                    temp = hssfRow.getCell(26).getStringCellValue();
                }
                xls.setNromanual(temp);
                hssfRow.getCell(27).setCellType(XSSFCell.CELL_TYPE_STRING);
                System.out.print(
                        xls.getIdregistropaleta() + " " + (String) hssfRow.getCell(27).getStringCellValue());
                xls.setIdclieprov_destino((String) hssfRow.getCell(27).getStringCellValue());
                xls.setTipo(hssfRow.getCell(28).getStringCellValue());
                exist = false;
                boolean vali = validarPaleExcel(xls, k);
                if (vali) {
                    listPaletaUp.add(xls);
                }
                for (int i = 0; i < listPaletaUp.size() - 1; i++) {
                    if ((listPaletaUp.get(i).getIdempresa()
                            .equalsIgnoreCase(hssfRow.getCell(0).getStringCellValue())
                            && listPaletaUp.get(i).getIdregistropaleta()
                                    .equalsIgnoreCase(hssfRow.getCell(1).getStringCellValue()))
                            && hssfRow.getRowNum() > 1) {
                        exist = true;
                        break;
                    }
                }
                if (exist) {
                    filaDuplicada = hssfRow.getRowNum() + 1;
                    break;
                }
            }
            k++;
        }
        if (exist) {
            WebUtil.MensajeAlerta("Registro Duplicado. Fila : " + filaDuplicada
                    + ". \n Verifique el Excel e Intntelo otra vez.");
            listPaletaUp.clear();
        }
        Iterator<Row> rowIteratorD = hssfSheetD.rowIterator();
        firstLinea = true;
        exist = false;
        int l = 0;
        while (rowIteratorD.hasNext()) {
            Row hssfRow = rowIteratorD.next();
            if (firstLinea) {
                firstLinea = false;
            } else {
                Dpaleta xls = new Dpaleta();
                xls.setIdempresa((int) hssfRow.getCell(0).getNumericCellValue());
                xls.setIdregistropaleta(hssfRow.getCell(1).getStringCellValue());
                xls.setItem(hssfRow.getCell(2).getStringCellValue());
                xls.setIdclieprov(String.valueOf(hssfRow.getCell(3).getNumericCellValue()));
                xls.setIdlote(hssfRow.getCell(4).getStringCellValue());
                xls.setIdcondicion(hssfRow.getCell(5).getStringCellValue());
                xls.setIdtalla(hssfRow.getCell(6).getStringCellValue());
                xls.setIdcolor(hssfRow.getCell(7).getStringCellValue());
                xls.setCantidad(hssfRow.getCell(8).getNumericCellValue());
                xls.setIdembalaje(hssfRow.getCell(9).getStringCellValue());
                hssfRow.getCell(10).setCellType(XSSFCell.CELL_TYPE_STRING);
                xls.setIdproducto(hssfRow.getCell(10).getStringCellValue());
                hssfRow.getCell(11).setCellType(XSSFCell.CELL_TYPE_STRING);
                xls.setIdlotep(hssfRow.getCell(11).getStringCellValue());
                xls.setIdconsumidor(hssfRow.getCell(12).getStringCellValue());
                xls.setIdlotecampo(hssfRow.getCell(13).getStringCellValue());
                xls.setIdpresentacion(hssfRow.getCell(14).getStringCellValue());
                exist = false;
                boolean vali = ValidarDPaleExcel(xls, l);
                if (vali) {
                    listDPaletaUp.add(xls);
                }
                for (int i = 0; i < listDPaletaUp.size() - 1; i++) {
                    if ((listDPaletaUp.get(i).getIdempresa() == (int) hssfRow.getCell(0).getNumericCellValue()
                            && listDPaletaUp.get(i).getIdregistropaleta()
                                    .equalsIgnoreCase(hssfRow.getCell(1).getStringCellValue())
                            && listDPaletaUp.get(i).getItem()
                                    .equalsIgnoreCase(hssfRow.getCell(2).getStringCellValue()))
                            && hssfRow.getRowNum() > 1) {
                        exist = true;
                        break;
                    }
                }
                if (exist) {
                    filaDuplicada = hssfRow.getRowNum() + 1;
                    break;
                }
            }
            l++;
        }
        if (exist) {
            WebUtil.MensajeAlerta("Registro Duplicado. Fila : " + filaDuplicadaD
                    + ". \n Verifique el Excel De Detalle e Intntelo otra vez.");
            listPaletaUp.clear();
        }
        RequestContext.getCurrentInstance().update(":datos:tbas:tblRepet_data");
        RequestContext.getCurrentInstance().update(":datos:tbas:tblDRepet");
        RequestContext.getCurrentInstance().execute("PF('dlgIngrPro').show()");
    } catch (IOException e) {
        System.out.println("Error en el Procesamiento : " + e.getMessage());
    }
}

From source file:com.pe.nisira.movil.view.action.ZonaGeneralAction.java

public void upExcel(FileUploadEvent event) throws ParseException {
    try {/*from   w  w  w  . j a va  2s.  co  m*/
        if (getDatoEdicion().getANCHO() != 0 && getDatoEdicion().getLARGO() != 0) {
            ListO = new ArrayList<DiagExcel>();
            upFile = event.getFile();
            XSSFWorkbook workBook = new XSSFWorkbook(upFile.getInputstream());
            XSSFSheet hssfSheet = workBook.getSheetAt(0);
            Iterator<Row> rowIterator = hssfSheet.rowIterator();
            List<Double> maxX = new ArrayList<Double>();
            List<Double> maxY = new ArrayList<Double>();
            boolean firstLinea = true;
            boolean terminado = false;
            while (rowIterator.hasNext()) {
                Row hssfRow = rowIterator.next();
                if (firstLinea) {
                    firstLinea = false;
                } else {
                    DiagExcel O = new DiagExcel();
                    hssfRow.getCell(0).setCellType(XSSFCell.CELL_TYPE_STRING);//Color
                    hssfRow.getCell(1).setCellType(XSSFCell.CELL_TYPE_STRING);//Layer
                    hssfRow.getCell(2).setCellType(XSSFCell.CELL_TYPE_STRING);//Position X
                    hssfRow.getCell(3).setCellType(XSSFCell.CELL_TYPE_STRING);//Position Y
                    hssfRow.getCell(4).setCellType(XSSFCell.CELL_TYPE_STRING);//Value
                    hssfRow.getCell(5).setCellType(XSSFCell.CELL_TYPE_STRING);//Zona
                    hssfRow.getCell(6).setCellType(XSSFCell.CELL_TYPE_STRING);//Tipo
                    hssfRow.getCell(7).setCellType(XSSFCell.CELL_TYPE_STRING);//Proceso
                    hssfRow.getCell(8).setCellType(XSSFCell.CELL_TYPE_STRING);//nX
                    hssfRow.getCell(9).setCellType(XSSFCell.CELL_TYPE_STRING);//nY
                    hssfRow.getCell(10).setCellType(XSSFCell.CELL_TYPE_STRING);//Punto
                    hssfRow.getCell(11).setCellType(XSSFCell.CELL_TYPE_STRING);//Piso
                    hssfRow.getCell(12).setCellType(XSSFCell.CELL_TYPE_STRING);//TipoZona
                    O.setColor((String) hssfRow.getCell(0).getStringCellValue());
                    O.setLayer((String) hssfRow.getCell(1).getStringCellValue());
                    O.setPositionX(Double.parseDouble(hssfRow.getCell(2).getStringCellValue()));
                    O.setPositionY(Double.parseDouble(hssfRow.getCell(3).getStringCellValue()));
                    O.setValue(Double.parseDouble(hssfRow.getCell(4).getStringCellValue()));
                    O.setZona((String) hssfRow.getCell(5).getStringCellValue());
                    O.setTipo((String) hssfRow.getCell(6).getStringCellValue());
                    O.setProceso((String) hssfRow.getCell(7).getStringCellValue());
                    O.setnX(Double.parseDouble(hssfRow.getCell(8).getStringCellValue()));
                    O.setnY(Double.parseDouble(hssfRow.getCell(9).getStringCellValue()));
                    O.setPunto((String) hssfRow.getCell(10).getStringCellValue());
                    if (!hssfRow.getCell(11).getStringCellValue().equalsIgnoreCase("")) {
                        O.setPisos(Integer.parseInt(hssfRow.getCell(11).getStringCellValue()));
                    }
                    O.setTipoZona(Integer.parseInt(hssfRow.getCell(12).getStringCellValue()));
                    O.setChecked(false);
                    ListO.add(O);
                }
            }

            NuevoDDistribucionUbicacion();
            generaZonas();
            genDiagZonas();
            graZonaGen();
            System.out.println("list");
        } else {
            WebUtil.MensajeAdvertencia("Largo o Ancho Son Cero");
        }

    } catch (IOException e) {
        System.out.println("Error en el Procesamiento : " + e.getMessage());
    } catch (Exception ex) {
        System.out.println("Error en el Procesamiento : " + ex.getMessage());
    }
}

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

public FileInputStream generateAllEmployeeReport(File file) throws ExceptionWrapper {
    try {/* w  ww  .j a va 2 s.c  o m*/
        FileInputStream inp = new FileInputStream(file);
        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        int iRowCounter = 1;
        for (EmployeeBO employeeBO : employeeBL.getAllEmployees().values()) {
            Row row = sheet.getRow(iRowCounter);
            if (row == null)
                row = sheet.createRow(iRowCounter);
            Cell cell = row.getCell(0);
            if (cell == null)
                cell = row.createCell(0);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmployeeId());

            cell = row.getCell(1);
            if (cell == null)
                cell = row.createCell(1);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmployeeUid());

            cell = row.getCell(2);
            if (cell == null)
                cell = row.createCell(2);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmployeeName());

            cell = row.getCell(3);
            if (cell == null)
                cell = row.createCell(3);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getEmail());

            cell = row.getCell(4);
            if (cell == null)
                cell = row.createCell(4);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getManager());

            cell = row.getCell(5);
            if (cell == null)
                cell = row.createCell(5);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getMobileNumber());

            cell = row.getCell(6);
            if (cell == null)
                cell = row.createCell(6);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getExtensionNum());

            cell = row.getCell(7);
            if (cell == null)
                cell = row.createCell(7);
            setCellBorder(wb, cell);
            cell.setCellValue(employeeBO.getWorkspace());

            iRowCounter = iRowCounter + 1;
        }
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        return new FileInputStream(file);
    } catch (Exception ex) {
        throw new ExceptionWrapper(ex);
    }

}

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

public FileInputStream generateAllEmployeeTeamWiseReport(File file, Boolean isScrumTeam)
        throws ExceptionWrapper {
    try {/*from w  ww . j  av a2  s  .  c o m*/
        FileInputStream inp = new FileInputStream(file);
        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        int iSheetCounter = 1;
        for (Integer groupID : employeeBL.getAllEmployeesKeyedByGroupId().keySet()) {
            GroupBO groupBO = groupsBL.getAllGroups().get(groupID);
            if (isScrumTeam && !groupBO.getRallyGroup())
                continue;
            if (!isScrumTeam && groupBO.getRallyGroup())
                continue;

            Sheet sheet = wb.cloneSheet(0);
            wb.setSheetName(wb.getSheetIndex(sheet), groupBO.getGroupName());
            //wb.setSheetName(iSheetCounter,groupBO.getGroupName());

            int iRowCounter = 1;
            for (EmployeeBO employeeBO : employeeBL.getAllEmployeesKeyedByGroupId().get(groupID)) {
                Row row = sheet.getRow(iRowCounter);
                if (row == null)
                    row = sheet.createRow(iRowCounter);
                Cell cell = row.getCell(0);
                if (cell == null)
                    cell = row.createCell(0);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeId());

                cell = row.getCell(1);
                if (cell == null)
                    cell = row.createCell(1);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeUid());

                cell = row.getCell(2);
                if (cell == null)
                    cell = row.createCell(2);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmployeeName());

                cell = row.getCell(3);
                if (cell == null)
                    cell = row.createCell(3);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getEmail());

                cell = row.getCell(4);
                if (cell == null)
                    cell = row.createCell(4);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getManager());

                cell = row.getCell(5);
                if (cell == null)
                    cell = row.createCell(5);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getMobileNumber());

                cell = row.getCell(6);
                if (cell == null)
                    cell = row.createCell(6);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getExtensionNum());

                cell = row.getCell(7);
                if (cell == null)
                    cell = row.createCell(7);
                setCellBorder(wb, cell);
                cell.setCellValue(employeeBO.getWorkspace());

                if (isScrumTeam) {
                    cell = row.getCell(8);
                    if (cell == null)
                        cell = row.createCell(8);
                    setCellBorder(wb, cell);
                    cell.setCellValue(employeeBO.getSpecificRoleInScrumTeam());
                }

                iRowCounter = iRowCounter + 1;
            }
        }
        iSheetCounter = iSheetCounter + 1;
        wb.removeSheetAt(0);
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        return new FileInputStream(file);
    } catch (Exception ex) {
        throw new ExceptionWrapper(ex);
    }

}