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

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

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

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:invoiceapplication.CopyRowOriginal.java

private static void copyMergeRegion(Sheet worksheet, Row sourceRow, Row newRow, CellRangeAddress mergedRegion) {
    CellRangeAddress range = mergedRegion;
    if (range.getFirstRow() == sourceRow.getRowNum()) {
        //System.out.println(range.formatAsString());
        int lastRow = newRow.getRowNum() + (range.getFirstRow() - range.getLastRow());
        worksheet.addMergedRegion(new CellRangeAddress(newRow.getRowNum(), lastRow, range.getFirstColumn(),
                range.getLastColumn()));
    }//from  w w w.  jav  a  2s  .  co  m
}

From source file:IO.FILES.java

public void overWrite(Persona p, String texto) throws Exception {
    if (p == null)
        return;/*from   w w  w  .j  av a 2 s . c om*/
    Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta)));
    Sheet hoja = wb.getSheetAt(0);
    boolean encontrado = false;
    Row row = null;
    Iterator it = hoja.rowIterator();
    while (it.hasNext()) {
        row = (Row) it.next();
        if (row.getCell(0).getStringCellValue().equalsIgnoreCase(p.getId())) {
            encontrado = true;
            break;
        }
    }

    if (!encontrado)
        return;
    int aux = row.getRowNum();
    hoja.removeRow(row);
    row = hoja.createRow(aux);
    CentroEducativo centro = p.getCentro();
    Redaccion R = p.getRedaccion();
    for (int i = 0; i < cols.length; i++) {
        Cell cell = row.createCell(i);
        switch (i) {
        case 0:
            cell.setCellValue(p.getId());
            break;
        case 1:
            cell.setCellValue(p.getName());
            break;
        case 2:
            cell.setCellValue(p.getLastName());
            break;
        case 3:
            cell.setCellValue(p.getLastName2());
            break;
        case 4:
            cell.setCellValue(p.getNivel());
            break;
        case 5:
            cell.setCellValue(p.getEdad());
            break;
        case 6:
            cell.setCellValue(p.isMale() ? "M" : "F");
            break;
        case 7:
            cell.setCellValue(centro.getName());
            break;
        case 8:
            cell.setCellValue(centro.getSiglas());
            break;
        case 9:
            cell.setCellValue(centro.isPublic() ? "PUBLICO" : "PRIVADO");
            break;
        case 10:
            cell.setCellValue(p.isCCA() ? "CCA" : "SCA");
            break;
        case 11:
            cell.setCellValue(p.isCCA() ? p.getCCA_DETALLE() : "-");
            break;
        case 12:
            cell.setCellValue(p.isAdecuacion() ? "SI" : "NO");
            break;
        case 13:
            cell.setCellValue(p.isAdecuacion() ? modelo.Info.tipos[p.getTipo()] : "-");
            break;
        case 14:
            cell.setCellValue(R.getUT());
            break;
        case 15:
            cell.setCellValue(R.getCL());
            break;
        case 16:
            cell.setCellValue(R.getPAL());
            break;
        case 17:
            cell.setCellValue(R.getLPUT());
            break;
        case 18:
            cell.setCellValue(R.getLPCL());
            break;
        case 19:
            cell.setCellValue(R.getINSUB());
        }
    }
    REDACCIONES.overWrite(wb, p, texto);
    save(wb);
}

From source file:IO.FILES.java

public static void removeRow(Sheet sheet, Row row) {
    int rowIndex = row.getRowNum();
    sheet.removeRow(row);/*from w w w . j a v  a 2 s .  c om*/
    int lastRowNum = sheet.getLastRowNum();
    if ((rowIndex >= 0) && (rowIndex < lastRowNum))
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
}

From source file:IO.REDACCIONES.java

public static void overWrite(Workbook wb, Persona p, String texto) throws FileNotFoundException, IOException {
    if ((p == null) || (wb == null))
        return;/*from  w  ww . ja v a 2 s  . co  m*/
    Sheet h = wb.getSheetAt(1);
    boolean encontrado = false;
    Row row = null;
    Iterator it = h.rowIterator();
    while (it.hasNext()) {
        row = (Row) it.next();
        if (row.getCell(0).getStringCellValue().equalsIgnoreCase(p.getId())) {
            encontrado = true;
        }
    }

    if (!encontrado)
        return;
    int aux = row.getRowNum();
    h.removeRow(row);
    row = h.createRow(aux);
    Cell cell1 = row.createCell(0);
    cell1.setCellValue(p.getId());
    Cell cell2 = row.createCell(1);
    cell2.setCellValue(texto);
}

From source file:io.unravellingtechnologies.excalibur.Sheet.java

License:Open Source License

/**
 * Loads all the rows that have content into the Sheet structure.
 *//*from   w w w .j a  va  2 s . c  o m*/
private void loadRows(XSSFSheet sheet) {
    if (logger.isDebugEnabled()) {
        logger.debug("Loading sheet rows...");
    }

    if (sheet.getPhysicalNumberOfRows() < 2) {
        return;
    }

    for (Iterator<org.apache.poi.ss.usermodel.Row> rowIt = sheet.rowIterator(); rowIt.hasNext();) {
        org.apache.poi.ss.usermodel.Row tableRow = rowIt.next();

        if (tableRow.getRowNum() != sheet.getFirstRowNum()) {
            Row row = new Row(new HashMap<String, String>());

            for (Iterator<Cell> cellIt = tableRow.cellIterator(); cellIt.hasNext();) {
                Cell cell = cellIt.next();

                row.addCell(getColumnName(cell.getColumnIndex()), cell.getStringCellValue());
            }

            rows.add(row);
        }
    }

    if (logger.isDebugEnabled()) {
        logger.debug("Completed loading " + rows.size() + " rows.");
    }
}

From source file:it.cineca.pst.huborcid.service.AbstractFileService.java

License:Open Source License

@Transactional(propagation = Propagation.REQUIRES_NEW)
public void uploadFileOrcid(MultipartFile file, Application application, String typeEntity) throws IOException {
    log.debug(String.format("Method uploadFileOrcid START, file.name=[%s]", file.getOriginalFilename()));

    Integer maxColumn = 0;//  w  ww  .  j  a  va2s  . co m
    ResultUploadOrcidEntity resultUploadOrcid = new ResultUploadOrcidEntity();
    try {
        resultUploadOrcid.setApplication(application);
        resultUploadOrcid.setFileNameUpload(file.getOriginalFilename());
        resultUploadOrcid.setStatus("PROGRESS");
        resultUploadOrcid.setEntityType(typeEntity);
        resultUploadOrcidEntityRepository.save(resultUploadOrcid);

        OrcidAccessToken orcidAccessToken = new OrcidAccessToken();

        InputStream fileInputStream = new BufferedInputStream(file.getInputStream());
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);

        boolean withErrors = false;
        Iterator<Row> rowIterator = sheet.iterator();
        OrcidOAuthClient clientOrcid = new OrcidOAuthClient(orcidApiType);
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                maxColumn = row.getPhysicalNumberOfCells();
            } else {
                try {
                    String valueCellLocalId = "";
                    String valueCellOrcid = "";
                    Cell cell = row.getCell(0);
                    if (cell != null) {
                        valueCellLocalId = cell.getStringCellValue();
                    }
                    cell = row.getCell(1);
                    if (cell != null) {
                        valueCellOrcid = cell.getStringCellValue();
                    }

                    List<RelPersonApplication> listPersApp = relPersonApplicationRepository
                            .findAllByApplicationIsAndLastIsTrueAndOrcidIsOrLocalIdIs(application,
                                    valueCellOrcid, valueCellLocalId);
                    if (listPersApp.size() == 1) {
                        RelPersonApplication persApp = listPersApp.get(0);
                        orcidAccessToken.setAccess_token(persApp.getOauthAccessToken());
                        orcidAccessToken.setOrcid(persApp.getPerson().getOrcid());

                        createAppendEntity(clientOrcid, orcidAccessToken, sheet, row);

                        writeResultRow(row, maxColumn, "", true);
                    } else if (listPersApp.size() == 0) {
                        writeResultRow(row, maxColumn, "Utente non trovato", false);
                        withErrors = true;
                    } else if (listPersApp.size() > 1) {
                        writeResultRow(row, maxColumn, "Errore di sistema: anagrafiche duplicate", false);
                        withErrors = true;
                    }
                } catch (Exception e) {
                    writeResultRow(row, maxColumn, e.getMessage(), false);
                    withErrors = true;
                }
            }
        }

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        workbook.close();
        byte[] fileResult = baos.toByteArray();
        resultUploadOrcid.setStatus("COMPLETED");
        resultUploadOrcid.setWithErrors(withErrors);
        resultUploadOrcid.setFileResult(fileResult);
        resultUploadOrcidEntityRepository.save(resultUploadOrcid);
    } catch (Exception e) {
        log.debug(String.format("Method uploadFileOrcid, exception=[%s]", e.getMessage()));
        resultUploadOrcid.setStatus("ERROR");
        resultUploadOrcidEntityRepository.save(resultUploadOrcid);
    }

    log.debug("Method uploadFileOrcid END");
}

From source file:it.dontesta.liferay.example.portal.client.ImportUsersToLiferay.java

License:Open Source License

/**
 * Read users to import from excel file.
 * //from w  w w. j  a  va2  s  .c  o  m
 * @return Returns a list of users who are ready for import.
 */
private static List<UserToImport> getUsersToImportFromExcel() {
    String fileName = (System.getProperty("fileToImport") != null) ? System.getProperty("fileToImport")
            : FILE_TO_IMPORT_USERS;
    InputStream inp = null;
    List<UserToImport> usersList = new ArrayList<UserToImport>();
    UserToImport user = null;
    boolean readyForImport = true;

    try {
        inp = new FileInputStream(fileName);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        for (Row row : sheet) {
            LOGGER.debug("Processing row index {}...", row.getRowNum());
            if (row.getRowNum() == 0) {
                LOGGER.debug("First row is the header. Skip this row");
                continue;
            } else {
                user = new UserToImport();
            }
            for (Cell cell : row) {
                LOGGER.debug("Processing cell index {}...", cell.getColumnIndex());
                switch (cell.getColumnIndex()) {
                case 0:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setTitle(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 1:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setScreenName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    } else {
                        LOGGER.warn("The username attribute is not null. Row skipped");
                        LOGGER.warn("Processing cell index {}...[FAILED]", cell.getColumnIndex());
                        break;

                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 2:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setEmail(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    } else {
                        LOGGER.warn("The email attribute is not null. Row skipped");
                        LOGGER.warn("Processing cell index {}...[FAILED]", cell.getColumnIndex());
                        break;
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 3:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setFirstName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 4:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setMiddleName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 5:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setLastName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 6:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setGender(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 7:
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            Calendar calBirthDate = Calendar.getInstance();
                            calBirthDate.setTime(cell.getDateCellValue());

                            user.setBirthDate(calBirthDate);
                            LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                    cell.getDateCellValue());
                        } else {
                            LOGGER.warn("Value cell index {} {}", cell.getColumnIndex(),
                                    cell.getNumericCellValue());
                            LOGGER.warn("Value cell index {} not contain a date type format",
                                    cell.getColumnIndex());
                        }
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 8:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setJobTitle(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    break;
                case 9:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setSiteName(cell.getStringCellValue().split(","));
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                Arrays.toString(cell.getStringCellValue().split(",")));
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 10:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setRoleName(cell.getStringCellValue().split(","));
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                Arrays.toString(cell.getStringCellValue().split(",")));
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 11:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setLanguageId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 12:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setTimeZoneId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 13:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setAccountId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());

                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                default:
                    break;
                }
            }
            if (user.getScreenName() == null) {
                LOGGER.warn("The username attribute can not be null for rowId {}", row.getRowNum());
                readyForImport = false;
            }
            if (user.getEmail() == null) {
                LOGGER.warn("The email attribute can not be null for rowId {}", row.getRowNum());
                readyForImport = false;
            }
            if (readyForImport) {
                LOGGER.info("Add user object {} to user list...", user.toString());
                usersList.add(user);
            }
        }
    } catch (FileNotFoundException e) {
        LOGGER.error(e.getMessage());
    } catch (IllegalStateException e) {
        LOGGER.error(e.getMessage());
    } catch (InvalidFormatException e) {
        LOGGER.error(e.getMessage());
    } catch (IOException e) {
        LOGGER.error(e.getMessage());
    }

    return usersList;
}

From source file:it.eng.spagobi.engines.worksheet.services.export.ExportWorksheetAction.java

License:Mozilla Public License

public void exportMetadataToXLS(Workbook wb, WorkSheetXLSExporter exporter, CreationHelper createHelper,
        JSONArray metadataPropertiesJSON, JSONArray parametersJSON) throws Exception {

    int FIRST_ROW = 0;
    int FIRST_COLUMN = 0;
    int rowCount = 0;

    JSONArray technicalMetadataProperty;
    JSONArray shortBusinessMetadataProperty;
    JSONArray longBusinessMetadataProperty;

    org.apache.poi.ss.usermodel.Sheet sheet = wb
            .createSheet(EngineMessageBundle.getMessage("worksheet.export.metadata.title", this.getLocale()));

    sheet.setColumnWidth(FIRST_COLUMN, 256 * 25);
    sheet.setColumnWidth(FIRST_COLUMN + 1, 256 * 90);

    CellStyle headerCellStyle = exporter.buildMetadataTitleCellStyle(sheet);
    CellStyle metaNameCellStyle = exporter.buildMetadataNameCellStyle(sheet);
    CellStyle metaValueCellStyle = exporter.buildMetadataValueCellStyle(sheet);

    Row row;
    Cell nameCell;/*  w  w  w .ja va  2s  . c  om*/
    Cell valueCell;
    Cell headerCell;
    String text;

    technicalMetadataProperty = new JSONArray();
    shortBusinessMetadataProperty = new JSONArray();
    longBusinessMetadataProperty = new JSONArray();

    if (metadataPropertiesJSON != null) {
        for (int i = 0; i < metadataPropertiesJSON.length(); i++) {
            JSONObject metadataProperty = metadataPropertiesJSON.getJSONObject(i);
            String metadataPropertyType = metadataProperty.getString("meta_type");
            if ("SHORT_TEXT".equalsIgnoreCase(metadataPropertyType)) {
                shortBusinessMetadataProperty.put(metadataProperty);
                continue;
            } else if ("LONG_TEXT".equalsIgnoreCase(metadataPropertyType)) {
                longBusinessMetadataProperty.put(metadataProperty);
                continue;
            } else {
                technicalMetadataProperty.put(metadataProperty);
            }

        }

    }

    if (technicalMetadataProperty.length() > 0) {

        row = sheet.createRow((FIRST_ROW) + rowCount);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        text = EngineMessageBundle.getMessage("worksheet.export.metadata.technicalMetadata", this.getLocale());
        headerCell.setCellValue(createHelper.createRichTextString(text));
        headerCell.setCellType(exporter.getCellTypeString());
        headerCell.setCellStyle(headerCellStyle);

        rowCount++;

        for (int i = 0; i < technicalMetadataProperty.length(); i++) {
            JSONObject metadataProperty = technicalMetadataProperty.getJSONObject(i);

            String metadataPropertyName = metadataProperty.getString("meta_name");
            String metadataPropertyValue = metadataProperty.getString("meta_content");
            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);
            valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue));
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
            rowCount++;
        }

        rowCount = rowCount + 2;

    }

    if (shortBusinessMetadataProperty.length() + longBusinessMetadataProperty.length() > 0) {

        row = sheet.createRow((FIRST_ROW) + rowCount);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        text = EngineMessageBundle.getMessage("worksheet.export.metadata.businessMetadata", this.getLocale());
        headerCell.setCellValue(createHelper.createRichTextString(text));
        headerCell.setCellType(exporter.getCellTypeString());
        headerCell.setCellStyle(headerCellStyle);
        rowCount++;

        for (int i = 0; i < shortBusinessMetadataProperty.length(); i++, rowCount++) {

            JSONObject metadataProperty = shortBusinessMetadataProperty.getJSONObject(i);

            String metadataPropertyName = metadataProperty.getString("meta_name");
            String metadataPropertyValue = metadataProperty.getString("meta_content");
            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);
            valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue));
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
        }

        for (int i = 0; i < longBusinessMetadataProperty.length(); i++, rowCount++) {

            JSONObject metadataProperty = longBusinessMetadataProperty.getJSONObject(i);

            String metadataPropertyName = metadataProperty.getString("meta_name");
            String metadataPropertyValue = metadataProperty.getString("meta_content");

            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);
            valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue));
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
        }

        rowCount = rowCount + 2;

    }

    if (parametersJSON.length() > 0) {

        row = sheet.createRow((FIRST_ROW) + rowCount);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        text = EngineMessageBundle.getMessage("worksheet.export.metadata.analyticalDrivers", this.getLocale());
        headerCell.setCellValue(createHelper.createRichTextString(text));
        headerCell.setCellType(exporter.getCellTypeString());
        headerCell.setCellStyle(headerCellStyle);

        rowCount++;

        Drawing drawing = sheet.createDrawingPatriarch();

        for (int i = 0; i < parametersJSON.length(); i++) {
            JSONObject parameterJSON = parametersJSON.getJSONObject(i);
            String name = parameterJSON.getString("name");
            String value = parameterJSON.getString("value");
            String description = parameterJSON.optString("description");

            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(name));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);

            if (StringUtilities.isNotEmpty(description)) {

                valueCell.setCellValue(createHelper.createRichTextString(description));

                ClientAnchor anchor = createHelper.createClientAnchor();
                anchor.setCol1(valueCell.getColumnIndex());
                anchor.setCol2(valueCell.getColumnIndex() + 1);
                anchor.setRow1(row.getRowNum());
                anchor.setRow2(row.getRowNum() + 3);

                Comment comment = drawing.createCellComment(anchor);
                RichTextString str = createHelper.createRichTextString(value);
                comment.setString(str);
                comment.setAuthor("SpagoBI");

                valueCell.setCellComment(comment);
            } else {
                valueCell.setCellValue(createHelper.createRichTextString(value));
            }
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
            rowCount++;
        }

    }

}

From source file:it.redev.parco.job.AnagraficaParserJob.java

License:Open Source License

private boolean parseTipoMezzo(Row row) throws Exception {
    Cell cell = null;// w ww  . j a va  2 s.com
    int col = 0;
    try {
        cell = row.getCell(col++);
        if (ExcelUtils.isCellEmpty(cell)) {
            addErrorMessage("La riga " + row.getRowNum() + " del foglio '" + TIPO_MEZZI_SHEET
                    + "' verr ignorta in quanto ha la descrizione del tipo mezzo non definita.");
            return false;
        }
        TipoMezzo tipo = service.findTipoMezzo(ExcelUtils.getCellValue(cell));

        cell = row.getCell(col++);
        if (!ExcelUtils.isCellEmpty(cell)) {
            tipo.setSerbatoio(new BigDecimal(ExcelUtils.getCellValue(cell)));
        }

        cell = row.getCell(col++);
        if (!ExcelUtils.isCellEmpty(cell)) {
            tipo.setConsumoUrbano(new BigDecimal(ExcelUtils.getCellValue(cell)));
        }

        cell = row.getCell(col++);
        if (!ExcelUtils.isCellEmpty(cell)) {
            tipo.setConsumoExtraurbano(new BigDecimal(ExcelUtils.getCellValue(cell)));
        }

        cell = row.getCell(col++);
        if (!ExcelUtils.isCellEmpty(cell)) {
            Integer carb = TipoCarburante.parse(ExcelUtils.getCellValue(cell));
            if (carb != null) {
                tipo.setTipoCarburante(carb);
            } else {
                addErrorMessage("Tipo carburante '" + ExcelUtils.getCellValue(cell) + "' non riconosciuto");
            }
        }
    } catch (Exception e) {
        addErrorMessage("Error parsing: " + ExcelUtils.getCellValue(cell));
        throw e;
    }
    return true;
}

From source file:it.redev.parco.job.AnagraficaParserJob.java

License:Open Source License

private boolean parseMezzo(Row row) throws Exception {
    Cell cell = null;//w ww  . j  a v a  2  s  .  c  om
    int col = 0;
    try {
        // TARGA
        cell = row.getCell(col++);
        if (ExcelUtils.isCellEmpty(cell)) {
            addErrorMessage("La riga " + row.getRowNum() + " del foglio '" + MEZZI_SHEET
                    + "' verr ignorta in quanto ha la targa del mezzo non definita.");
            return false;
        }
        Mezzo mezzo = super.findMezzo(ExcelUtils.getCellValue(cell));
        mezziImportati.add(mezzo.getTarga());

        // CODICE RADIO
        cell = row.getCell(col++);
        if (!ExcelUtils.isCellEmpty(cell)) {
            String codice = StringUtils.cutTo(ExcelUtils.getCellValue(cell), '.');
            Mezzo mcodice = findMezzoByCodice(codice);
            if (mcodice != null) {
                if (!mcodice.equals(mezzo)) {
                    addErrorMessage("Il codice radio " + codice + " indicato per il mezzo " + mezzo.getTarga()
                            + "  associato anche al mezzo " + mcodice.getTarga());
                }
            }
            mezzo.setCodiceRadio(codice);
        }

        // SCAD BOLLO
        cell = row.getCell(col++);
        if (!ExcelUtils.isCellEmpty(cell)) {
            mezzo.setScadenzaBollo(ExcelUtils.getAsDate(cell, "dd/MM/yyyy"));
        }

        // SCAD REVISIONE
        cell = row.getCell(col++);
        if (!ExcelUtils.isCellEmpty(cell)) {
            mezzo.setScadenzaRevisione(ExcelUtils.getAsDate(cell, "dd/MM/yyyy"));
        }

        // NOTE
        cell = row.getCell(col++);
        if (!ExcelUtils.isCellEmpty(cell)) {
            mezzo.setNote(ExcelUtils.getCellValue(cell));
        }

        // TIPO MEZZO
        cell = row.getCell(col++);
        if (ExcelUtils.isCellEmpty(cell)) {
            addErrorMessage("La riga " + row.getRowNum() + " del foglio '" + MEZZI_SHEET
                    + "' verr ignorta in quanto ha il tipo mezzo non definito.");
            getMezzi().remove(mezzo.getTarga());
            mezziImportati.remove(mezzo.getTarga());
            return false;
        }
        TipoMezzo tipo = service.findTipoMezzo(ExcelUtils.getCellValue(cell));
        mezzo.setTipoMezzo(tipo);

        // ASSEGNAZIONE
        cell = row.getCell(col++);
        if (!ExcelUtils.isCellEmpty(cell)) {
            String npos = ExcelUtils.getCellValue(cell);
            Postazione pos = service.findPostazione(npos);
            if (pos == null && !postazioniImportate.contains(npos)) {
                addErrorMessage("La postazione indicata nella riga " + row.getRowNum() + " del foglio '"
                        + MEZZI_SHEET + "' non  stata trovata o non  unica");
                col++;
            } else {
                cell = row.getCell(col++);
                Date dataAss = ExcelUtils.getAsDate(cell, "dd/MM/yyyy");

                AssegnazioneMezzo last = mezzo.getLastAssegnazione();
                if (last == null || !last.getPostazione().equals(pos)) {
                    AssegnazioneMezzo am = new AssegnazioneMezzo();
                    am.setPostazione(pos);
                    am.setStatoSerbatoio(AssegnazioneMezzo.StatoSerbatoio.PIENO);
                    am.setDataInizio(dataAss);
                    am.setMezzo(mezzo);

                    if (last != null && !am.after(last)) {
                        addErrorMessage("La data sassegnazione " + DateUtils.format(dataAss, "dd/MM/yyyy")
                                + " indicata nella riga " + row.getRowNum() + " del foglio '" + MEZZI_SHEET
                                + "' " + " precedente o uguale alla data assegnazione corrente "
                                + DateUtils.format(last.getDataInizio(), "dd/MM/yyyy"));
                    } else {
                        mezzo.assegna(am);
                    }
                }
            }
        } else {
            col++;
        }

        // STATO MEZZO
        cell = row.getCell(col++);
        if (!ExcelUtils.isCellEmpty(cell)) {
            Integer stato = Mezzo.Stati.parse(ExcelUtils.getCellValue(cell));

            if (stato == null) {
                addErrorMessage("Stato mezzo '" + ExcelUtils.getCellValue(cell) + "' non riconosciuto");
            } else {
                cell = row.getCell(col++);
                Date dataStato = new Date();
                if (!ExcelUtils.isCellEmpty(cell)) {
                    dataStato = ExcelUtils.getAsDate(cell, "dd/MM/yyyy");
                }

                cell = row.getCell(col++);
                String noteStato = "";
                if (!ExcelUtils.isCellEmpty(cell)) {
                    noteStato = ExcelUtils.getCellValue(cell);
                }

                // se il mezzo e' nuovo aggiorno lo stato
                // se il mezzo e' presente nel db aggiorno lo stato solo se differente da quello attuale 
                if (!mezzo.isManaged()) {
                    mezzo.getStato().setStato(stato);
                    mezzo.getStato().setDataStato(dataStato);
                    mezzo.getStato().setNoteStato(noteStato);
                    mezzo.getStato().setUtenteStato(getUser());
                } else if (!mezzo.getStato().getStato().equals(stato)) {
                    if (dataStato.after(mezzo.getStato().getDataStato())) {
                        mezzo.cambiaStato(stato, dataStato, getUser(), noteStato);
                    } else {
                        addErrorMessage("Lo stato del mezzo " + mezzo.getTarga()
                                + " non pu essere modificato perch " + "la data stato indicata "
                                + DateUtils.format(dataStato, "dd/MM/yyyy")
                                + " non  successiva alla data stato attuale "
                                + DateUtils.format(mezzo.getStato().getDataStato(), "dd/MM/yyyy"));
                    }
                }
            }
        }
    } catch (Exception e) {
        addErrorMessage("Error parsing: " + ExcelUtils.getCellValue(cell));
        throw e;
    }
    return true;
}