Example usage for org.apache.poi.ss.usermodel Cell getColumnIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getColumnIndex

Introduction

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

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

From source file:org.wandora.application.tools.extractors.excel.ExcelTopicOccurrenceExtractor.java

License:Open Source License

public Topic getDefaultOccurrenceTypeTopic(Cell cell, TopicMap tm) {
    if (cell != null && tm != null) {
        int i = cell.getColumnIndex();
        Topic typeTopic = getOrCreateTopic(tm, DEFAULT_OCCURRENCE_TYPE_SI + "/" + i,
                "Excel occurrence type " + i);
        return typeTopic;
    }/* w w  w  . ja v a2s.  co  m*/
    return null;
}

From source file:org.waterforpeople.mapping.app.harness.DeleteSurveyInstanceHarness.java

License:Open Source License

public void processSheet(String spreadsheetName, String serviceUrl) {
    InputStream inp;//  w ww . ja v a  2  s. c o m

    Sheet sheet1 = null;

    try {
        inp = new FileInputStream(spreadsheetName);
        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
        int i = 0;
        sheet1 = wb.getSheetAt(0);
        for (Row row : sheet1) {
            if (row.getRowNum() >= 1) {
                StringBuilder sb = new StringBuilder();
                sb.append("?action=deleteSurveyInstance&");
                for (Cell cell : row) {
                    switch (cell.getColumnIndex()) {
                    case 0:
                        sb.append("instanceId=" + new Double(cell.getNumericCellValue()).intValue());
                        break;
                    }
                }

                URL url = new URL(serviceUrl + sb.toString());
                System.out.println(i++ + " : " + serviceUrl + sb.toString());
                HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                conn.setRequestMethod("GET");
                conn.setDoOutput(true);
                String line;
                BufferedReader reader = new BufferedReader(new InputStreamReader(conn.getInputStream()));
                while ((line = reader.readLine()) != null) {
                    System.out.println(line);
                }
                // writer.close();
                reader.close();
            }
        }
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:org.waterforpeople.mapping.dataexport.FixedFormatRawDataImporter.java

License:Open Source License

@Override
public void executeImport(File file, String serverBase, Map<String, String> criteria) {
    try {/*from  w ww.j  ava2 s  .c o  m*/
        DateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss z");
        setSurveyId(criteria);
        Sheet sheet1 = getDataSheet(file);
        for (Row row : sheet1) {
            String localeId = null;
            String dateString = null;
            StringBuilder sb = new StringBuilder();
            StringBuilder valueBuilder = new StringBuilder();
            int valueCount = 0;
            sb.append("action=" + RawDataImportRequest.SAVE_FIXED_FIELD_SURVEY_INSTANCE_ACTION + "&"
                    + RawDataImportRequest.SURVEY_ID_PARAM + "=" + getSurveyId() + "&");
            for (Cell cell : row) {

                if (cell.getColumnIndex() == 0 && cell.getRowIndex() > 0) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        localeId = new Double(cell.getNumericCellValue()).intValue() + "";
                        sb.append(RawDataImportRequest.LOCALE_ID_PARAM + "=" + localeId + "&");
                    }
                }
                if (cell.getColumnIndex() == 1 && cell.getRowIndex() > 0) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        dateString = cell.getStringCellValue();
                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                        dateString = df.format(date);
                    }
                    if (dateString != null) {
                        sb.append(RawDataImportRequest.COLLECTION_DATE_PARAM + "="
                                + URLEncoder.encode(dateString, "UTF-8") + "&");
                    }
                }
                String value = null;
                boolean hasValue = false;

                if (cell.getRowIndex() > 0 && cell.getColumnIndex() > 1) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        value = cell.getStringCellValue().trim();
                        if (value.contains("|")) {
                            value = value.replaceAll("\\|", "^^");
                        }
                        sb.append(URLEncoder.encode(value, "UTF-8"));
                        hasValue = true;
                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        value = new Double(cell.getNumericCellValue()).toString().trim();
                        hasValue = true;
                    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                        value = new Boolean(cell.getBooleanCellValue()).toString().trim();
                        hasValue = true;
                    }
                }
                if (hasValue) {
                    if (valueCount > 0) {
                        valueBuilder.append(RawDataImportRequest.FIELD_VAL_DELIMITER);
                    }
                    valueBuilder.append(value);
                    valueCount++;
                }
            }
            if (valueCount > 0) {
                sb.append(RawDataImportRequest.FIXED_FIELD_VALUE_PARAM + "=" + valueBuilder.toString());
                invokeUrl(serverBase, sb.toString(), true, criteria.get(KEY_PARAM));
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        cleanup();
    }
}

From source file:org.waterforpeople.mapping.dataexport.RawDataSpreadsheetImporter.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override//from   ww  w .  j ava2s.  c  om
public void executeImport(File file, String serverBase, Map<String, String> criteria) {
    try {

        int rows = 0;
        errorIds = new ArrayList<String>();
        jobQueue = new LinkedBlockingQueue<Runnable>();
        threadPool = new ThreadPoolExecutor(5, 5, 10, TimeUnit.SECONDS, jobQueue);
        DateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss z");
        setSurveyId(criteria);

        Sheet sheet1 = getDataSheet(file);
        if (!GraphicsEnvironment.isHeadless()) {
            progressDialog = new ProgressDialog(sheet1.getLastRowNum(), locale);
            progressDialog.setVisible(true);
        }
        HashMap<Integer, String> questionIDColMap = new HashMap<Integer, String>();
        Object[] results = BulkDataServiceClient.loadQuestions(getSurveyId().toString(), serverBase,
                criteria.get("apiKey"));
        Map<String, QuestionDto> questionMap = null;

        if (results != null) {
            questionMap = (Map<String, QuestionDto>) results[1];

        }

        boolean hasDurationCol = true;
        boolean setFirstQuestionColumnIdx = true;
        int firstQuestionCol = 0;

        currentStep = 0;
        MessageDigest digest = MessageDigest.getInstance("MD5");
        for (Row row : sheet1) {
            rows++;
            if (row.getRowNum() == 0) {
                // Process headers
                for (Cell cell : row) {
                    if (cell.getStringCellValue().indexOf("|") > -1) {
                        if (setFirstQuestionColumnIdx) {
                            firstQuestionCol = cell.getColumnIndex();
                            setFirstQuestionColumnIdx = false;
                        }

                        String[] parts = cell.getStringCellValue().split("\\|");
                        if (parts[0].trim().length() > 0) {
                            questionIDColMap.put(cell.getColumnIndex(), parts[0].trim());
                        }
                    }
                }
                continue; // move to next row (data)
            }
            digest.reset();

            String instanceId = null;
            String dateString = null;
            String submitter = null;
            String duration = null;
            String durationSeconds = null;
            StringBuilder sb = new StringBuilder();

            // Monitoring headers
            // [identifier, displayName, instanceId, date, submitter, duration, questions...]

            // Non-monitoring headers
            // [instanceId, date, submitter, duration, questions...]

            int instanceIdx = firstQuestionCol - 4;
            int dateIdx = firstQuestionCol - 3;
            int submitterIdx = firstQuestionCol - 2;
            int durationIdx = firstQuestionCol - 1;

            sb.append("action=" + RawDataImportRequest.SAVE_SURVEY_INSTANCE_ACTION + "&"
                    + RawDataImportRequest.SURVEY_ID_PARAM + "=" + getSurveyId() + "&");
            boolean needUpload = true;

            for (Cell cell : row) {
                if (cell.getColumnIndex() == instanceIdx) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        instanceId = new Double(cell.getNumericCellValue()).intValue() + "";
                    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        instanceId = cell.getStringCellValue();

                    }
                    if (instanceId != null) {
                        sb.append(RawDataImportRequest.SURVEY_INSTANCE_ID_PARAM + "=" + instanceId + "&");
                    }
                }
                if (cell.getColumnIndex() == dateIdx) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        dateString = cell.getStringCellValue();
                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                        dateString = df.format(date);
                    }
                    if (dateString != null) {
                        sb.append(RawDataImportRequest.COLLECTION_DATE_PARAM + "="
                                + URLEncoder.encode(dateString, "UTF-8") + "&");
                    }
                }
                if (cell.getColumnIndex() == submitterIdx) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        submitter = cell.getStringCellValue();
                        sb.append("submitter=" + URLEncoder.encode(submitter, "UTF-8") + "&");
                    }
                }
                // Survey Duration
                if (cell.getColumnIndex() == durationIdx) {
                    if (hasDurationCol) {
                        switch (cell.getCellType()) {
                        // if the cell type is string, we expect hh:mm:ss format
                        case Cell.CELL_TYPE_STRING:
                            duration = cell.getStringCellValue();
                            durationSeconds = String.valueOf(durationToSeconds(duration));
                            digest.update(duration.getBytes());
                            break;
                        // if the cell type if numeric, we expect a single seconds value
                        case Cell.CELL_TYPE_NUMERIC:
                            durationSeconds = String.valueOf(cell.getNumericCellValue());
                            digest.update(durationSeconds.getBytes());
                            break;
                        default:
                            durationSeconds = "0";
                            // don't update the digest, because we want this value to be
                            // saved.
                            break;
                        }
                        sb.append("duration=" + URLEncoder.encode(durationSeconds, "UTF-8") + "&");
                    }
                }

                boolean hasValue = false;
                String qId = questionIDColMap.get(cell.getColumnIndex());

                if (cell.getColumnIndex() >= firstQuestionCol && qId != null && !qId.trim().equals("")) {
                    QuestionDto question = questionMap.get(questionIDColMap.get(cell.getColumnIndex()));
                    QuestionType type = null;
                    // VALUE is default, it is valid for NUMBER, FREE_TEXT, SCAN, OPTION
                    String typeString = "VALUE";
                    if (question != null) {
                        type = question.getType();
                        if (QuestionType.GEO == type) {
                            typeString = "GEO";
                        } else if (QuestionType.PHOTO == type) {
                            typeString = "IMAGE";
                        } else if (QuestionType.VIDEO == type) {
                            typeString = "VIDEO";
                        } else if (QuestionType.DATE == type) {
                            typeString = "DATE";
                        }
                    } else if (questionIDColMap.get(cell.getColumnIndex()).startsWith("--")) {
                        continue;
                    }

                    String cellVal = parseCellAsString(cell);
                    if (cellVal != null) {
                        cellVal = cellVal.trim();
                        // need to update digest before manipulating the
                        // data
                        digest.update(cellVal.getBytes());
                        if (cellVal.contains("|")) {
                            cellVal = cellVal.replaceAll("\\|", "^^");
                        }
                        if (cellVal.endsWith(".jpg")) {
                            if (cellVal.contains("/")) {
                                cellVal = cellVal.substring(cellVal.lastIndexOf("/"));
                            }
                            cellVal = "/sdcard" + cellVal;
                        }
                        if (cellVal.endsWith("UTC")) {
                            try {
                                cellVal = DATE_FMT.get().parse(cellVal).getTime() + "";
                            } catch (Exception e) {
                                log.error("bad date format: " + cellVal + "\n" + e.getMessage(), e);
                            }
                        }
                    }
                    if (cellVal == null) {
                        cellVal = "";
                    }

                    if (type != QuestionType.GEO) {
                        hasValue = true;
                        sb.append("questionId=" + questionIDColMap.get(cell.getColumnIndex()) + "|value=")
                                .append(cellVal != null ? URLEncoder.encode(cellVal, "UTF-8") : "");
                    } else {
                        hasValue = true;
                        sb.append("questionId=" + questionIDColMap.get(cell.getColumnIndex()) + "|value=");
                        if (questionIDColMap.get(cell.getColumnIndex() + 1) != null
                                && questionIDColMap.get(cell.getColumnIndex() + 1).startsWith("--")) {

                            for (int i = 1; i < 4; i++) {
                                String nextVal = parseCellAsString(row.getCell(cell.getColumnIndex() + i));
                                cellVal += "|" + (nextVal != null ? nextVal : "");
                            }
                            // if the length of the cellVal is too small, which means there is
                            // no valid info, skip.
                            if (cellVal.length() < 5) {
                                cellVal = "";
                            }
                            sb.append(cellVal != null ? URLEncoder.encode(cellVal, "UTF-8") : "");
                        } else {
                            sb.append(cellVal != null ? URLEncoder.encode(cellVal, "UTF-8") : "");
                        }
                    }

                    if (hasValue) {
                        sb.append("|type=").append(typeString).append("&");
                    }
                } else if (cell.getColumnIndex() >= firstQuestionCol) {
                    // we should only get here if we have a column that
                    // isn't in the header
                    // as long as the user hasn't messed up the sheet, this
                    // is the md5 digest of the original data
                    try {
                        String md5 = parseCellAsString(cell);
                        String digestVal = StringUtil.toHexString(digest.digest());
                        if (md5 != null && md5.equals(digestVal)) {
                            needUpload = false;
                        } else if (md5 != null && log.isDebugEnabled()) {
                            log.debug("Row: " + row.getRowNum() + " MD5: " + digestVal + " orig md5: " + md5);
                        }
                    } catch (Exception e) {
                        // if we can't handle the md5, then just assume we
                        // need to update the row
                        log.error("Couldn't process md5 for row: " + row.getRowNum() + " - " + e.getMessage(),
                                e);
                    }
                }
            }
            if (needUpload) {
                sendDataToServer(serverBase,
                        instanceId == null ? null
                                : getResetUrlString(instanceId, dateString, submitter, durationSeconds),
                        sb.toString(), criteria.get(KEY_PARAM));

            } else {
                // if we didn't need to upload, then just increment our
                // progress counter
                SwingUtilities.invokeLater(new StatusUpdater(currentStep++, SAVING_DATA.get(locale)));
            }
        }
        while (!jobQueue.isEmpty() && threadPool.getActiveCount() > 0) {
            Thread.sleep(5000);
        }
        if (errorIds.size() > 0) {
            log.error("There were ERRORS: ");
            for (String line : errorIds) {
                log.error(line);
            }
        }
        Thread.sleep(5000);
        log.debug("Updating summaries");
        // now update the summaries
        if ((questionIDColMap.size() * rows) < SIZE_THRESHOLD) {
            invokeUrl(serverBase,
                    "action=" + RawDataImportRequest.UPDATE_SUMMARIES_ACTION + "&"
                            + RawDataImportRequest.SURVEY_ID_PARAM + "=" + surveyId,
                    true, criteria.get(KEY_PARAM));
        }

        invokeUrl(serverBase, "action=" + RawDataImportRequest.SAVE_MESSAGE_ACTION + "&"
                + RawDataImportRequest.SURVEY_ID_PARAM + "=" + surveyId, true, criteria.get(KEY_PARAM));

        SwingUtilities.invokeLater(new StatusUpdater(currentStep++, COMPLETE.get(locale), true));
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        cleanup();
    }
}

From source file:org.waterforpeople.mapping.dataexport.RawDataSpreadsheetImporter.java

License:Open Source License

@Override
public Map<Integer, String> validate(File file) {
    Map<Integer, String> errorMap = new HashMap<Integer, String>();

    try {//from ww w.ja  va2s .  co  m
        Sheet sheet = getDataSheet(file);
        Row headerRow = sheet.getRow(0);
        boolean firstQuestionFound = false;

        for (Cell cell : headerRow) {
            String cellValue = cell.getStringCellValue();
            if (firstQuestionFound && !cellValue.matches(".+\\|.+")) {
                errorMap.put(cell.getColumnIndex(),
                        String.format("The header \"%s\" can not be imported", cellValue));
                break;
            } else {
                if (!firstQuestionFound && cellValue.matches("[0-9]+\\|.+")) {
                    firstQuestionFound = true;
                    int idx = cell.getColumnIndex();
                    if (!(idx == 4 || idx == 6)) {
                        errorMap.put(idx, "Found the first question at the wrong column index");
                        break;
                    }
                }
            }
        }
        if (!firstQuestionFound) {
            errorMap.put(-1, "A question could not be found");
        }

    } catch (Exception e) {
        errorMap.put(-1, e.getMessage());
    }

    return errorMap;
}

From source file:org.waterforpeople.mapping.dataexport.SurveySpreadsheetImporter.java

License:Open Source License

@Override
public void executeImport(File file, String serverBase, Map<String, String> criteria) {
    InputStream inp = null;//ww w. j  a va 2  s.c om
    Sheet sheet1 = null;
    Integer startRow = 1;
    Long beforeQuestionId = null;
    boolean isWholeSurvey = true;
    if (criteria != null) {
        if (criteria.get(BEFORE_QUESTION_ID_PARAM) != null) {
            beforeQuestionId = new Long(criteria.get(BEFORE_QUESTION_ID_PARAM));
        }
        if (criteria.get(WHOLE_SURVEY_PARAM) != null) {
            if ("false".equalsIgnoreCase(criteria.get(WHOLE_SURVEY_PARAM))) {
                isWholeSurvey = false;
            }
        }
    }
    try {
        inp = new FileInputStream(file);
        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
        sheet1 = wb.getSheetAt(0);
        String apiKey = criteria != null ? criteria.get("apiKey") : null;
        if (!isWholeSurvey) {
            // even though there is a header row, we want lastRowNum since
            // rows are 0 indexed
            int questionCount = sheet1.getLastRowNum();
            // figure out the starting order
            QuestionDto startingQuestion = BulkDataServiceClient.loadQuestionDetails(serverBase,
                    beforeQuestionId, apiKey);
            startRow = startingQuestion.getOrder();
            // now get all the questions
            List<QuestionDto> questionsInGroup = BulkDataServiceClient.fetchQuestions(serverBase,
                    startingQuestion.getQuestionGroupId(), apiKey);

            if (questionsInGroup != null) {
                // we only need to reorder the group into which we're
                // importing

                for (QuestionDto q : questionsInGroup) {
                    if (q.getOrder() >= startRow) {
                        StringBuilder reorderBuffer = new StringBuilder();
                        reorderBuffer.append("?").append(SurveyRestRequest.ACTION_PARAM).append("=")
                                .append(SurveyRestRequest.UPDATE_QUESTION_ORDER_ACTION).append("&")
                                .append(SurveyRestRequest.QUESTION_ID_PARAM).append("=").append(q.getKeyId())
                                .append("&").append(SurveyRestRequest.QUESTION_ORDER_PARAM).append("=")
                                .append((q.getOrder() + questionCount));
                        String result = BulkDataServiceClient.fetchDataFromServer(serverBase + SERVLET_URL,
                                reorderBuffer.toString(), true, criteria.get(KEY_PARAM));
                        log.debug(result);
                    }
                }
            }
        }

        for (Row row : sheet1) {
            if (row.getRowNum() >= 1) {
                StringBuilder sb = new StringBuilder();
                sb.append("?").append(SurveyRestRequest.ACTION_PARAM).append("=")
                        .append(SurveyRestRequest.SAVE_QUESTION_ACTION).append("&");
                for (Cell cell : row) {
                    switch (cell.getColumnIndex()) {
                    case 0:
                        sb.append(SurveyRestRequest.SURVEY_GROUP_NAME_PARAM).append("=")
                                .append(URLEncoder.encode(parseCellAsString(cell).trim(), "UTF-8")).append("&");
                        break;
                    case 1:
                        sb.append(SurveyRestRequest.SURVEY_NAME_PARAM).append("=")
                                .append(URLEncoder.encode(parseCellAsString(cell).trim(), "UTF-8")).append("&");
                        break;
                    case 2:
                        sb.append(SurveyRestRequest.QUESTION_GROUP_ORDER_PARAM).append("=")
                                .append(new Double(cell.getNumericCellValue()).intValue()).append("&");
                        break;

                    case 3:
                        sb.append(SurveyRestRequest.QUESTION_GROUP_NAME_PARAM).append("=")
                                .append(URLEncoder.encode(parseCellAsString(cell).trim(), "UTF-8")).append("&");
                        break;

                    case 4:
                        int order = new Double(cell.getNumericCellValue()).intValue();
                        if (!isWholeSurvey) {
                            order += (startRow - 1);
                        }
                        sb.append(SurveyRestRequest.QUESTION_ORDER_PARAM).append("=").append(order).append("&");
                        break;

                    case 5:
                        sb.append(SurveyRestRequest.QUESTION_TEXT_PARAM).append("=")
                                .append(URLEncoder.encode(parseCellAsString(cell).trim(), "UTF-8")).append("&");
                        break;
                    case 6:
                        sb.append(SurveyRestRequest.QUESTION_TYPE_PARAM).append("=")
                                .append(URLEncoder.encode(parseCellAsString(cell).trim(), "UTF-8")).append("&");
                        break;
                    case 7:
                        sb.append(SurveyRestRequest.OPTIONS_PARAM).append("=")
                                .append(URLEncoder.encode(parseCellAsString(cell).trim(), "UTF-8")).append("&");
                        break;
                    case 8:
                        String valString = parseCellAsString(cell);
                        if (valString != null && valString.trim().length() > 0) {
                            String[] parts = valString.split("\\|");
                            int depOrder = new Integer(parts[0].trim());
                            if (!isWholeSurvey) {
                                depOrder += (startRow - 1);
                            }
                            sb.append(SurveyRestRequest.DEPEND_QUESTION_PARAM).append("=")
                                    .append(URLEncoder.encode(depOrder + "|" + parts[1], "UTF-8")).append("&");
                        }
                        break;
                    case 9:
                        sb.append(SurveyRestRequest.ALLOW_OTHER_PARAM).append("=")
                                .append(parseCellAsString(cell)).append("&");
                        break;
                    case 10:
                        sb.append(SurveyRestRequest.ALLOW_MULTIPLE_PARAM).append("=")
                                .append(parseCellAsString(cell)).append("&");
                        break;
                    case 11:
                        sb.append(SurveyRestRequest.MANDATORY_PARAM).append("=").append(parseCellAsString(cell))
                                .append("&");
                        break;
                    case 12:
                        sb.append(SurveyRestRequest.SCORING_PARAM).append("=").append(parseCellAsString(cell));
                        break;
                    case 13:
                        // min val
                        String minVal = parseCellAsString(cell);
                        if (minVal != null && minVal.trim().length() > 0) {
                            sb.append("&").append(SurveyRestRequest.VALIDATION_MIN_PARAM).append("=")
                                    .append(minVal);
                        }
                        break;
                    case 14:
                        // max val
                        String maxVal = parseCellAsString(cell);
                        if (maxVal != null && maxVal.trim().length() > 0) {
                            sb.append("&").append(SurveyRestRequest.VALIDATION_MAX_PARAM).append("=")
                                    .append(maxVal);
                        }
                        break;
                    case 15:
                        // allow sign
                        String signVal = parseCellAsString(cell);
                        if (signVal != null && signVal.trim().length() > 0) {
                            sb.append("&").append(SurveyRestRequest.VALIDATION_ALLOW_SIGN_PARAM).append("=")
                                    .append(signVal);
                        }
                        break;
                    case 16:
                        // allow decimal
                        String decimalVal = parseCellAsString(cell);
                        if (decimalVal != null && decimalVal.trim().length() > 0) {
                            sb.append("&").append(SurveyRestRequest.VALIDATION_ALLOW_DECIMAL_PARAM).append("=")
                                    .append(decimalVal);
                        }
                        break;
                    case 17:
                        // is name
                        String isNameVal = parseCellAsString(cell);
                        if (isNameVal != null && isNameVal.trim().length() > 0) {
                            sb.append("&").append(SurveyRestRequest.VALIDATION_IS_NAME_PARAM).append("=")
                                    .append(isNameVal);
                        }
                        break;

                    case 18:
                        String metricName = parseCellAsString(cell);
                        if (metricName != null && metricName.trim().length() > 0) {
                            sb.append("&").append(SurveyRestRequest.METRIC_NAME_PARAM).append("=")
                                    .append(metricName);
                        }
                        break;
                    case 19:
                        String metricGroup = parseCellAsString(cell);
                        if (metricGroup != null && metricGroup.trim().length() > 0) {
                            sb.append("&").append(SurveyRestRequest.METRIC_GROUP_PARAM).append("=")
                                    .append(metricGroup);
                        }
                        break;
                    }
                }
                try {
                    String result = BulkDataServiceClient.fetchDataFromServer(serverBase + SERVLET_URL,
                            sb.toString(), true, criteria.get(KEY_PARAM));
                    log.debug(result);
                } catch (Throwable t) {
                    log.error("Error: " + t.getMessage(), t);
                    log.info("Trying again");
                    try {
                        BulkDataServiceClient.fetchDataFromServer(serverBase + SERVLET_URL, sb.toString(), true,
                                criteria.get(KEY_PARAM));
                    } catch (Exception e) {
                        log.error("Error:" + e.getMessage(), e);
                        // giving up
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (inp != null) {
            try {
                inp.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:org.waterforpeople.mapping.dataexport.SurveySpreadsheetImporter.java

License:Open Source License

@Override
public Map<Integer, String> validate(File file) {
    InputStream inp = null;/* ww w. ja  v a 2 s  .  c o  m*/
    Sheet sheet1 = null;
    Map<Integer, String> errorMap = new TreeMap<Integer, String>();

    try {
        inp = new FileInputStream(file);
        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
        sheet1 = wb.getSheetAt(0);
        for (Row row : sheet1) {
            StringBuffer rowError = new StringBuffer();
            if (row.getRowNum() >= 1) {
                String type = null;
                for (Cell cell : row) {
                    try {
                        switch (cell.getColumnIndex()) {
                        case 0:
                            if (cell.getStringCellValue().trim().length() == 0) {
                                rowError.append("Survey Group Name is missing\n");
                            }
                            break;
                        case 1:
                            if (cell.getStringCellValue().trim().length() == 0) {
                                rowError.append("Survey Name is missing\n");
                            }
                            break;
                        case 2:
                            try {
                                if (cell.getNumericCellValue() < 0) {
                                    rowError.append("Question Group Order must be a positive integer\n");
                                }
                            } catch (Exception e) {
                                rowError.append("Question group order must be a number\n");
                            }
                            break;
                        case 3:
                            if (cell.getStringCellValue().trim().length() == 0) {
                                rowError.append("Question Group Name is missing\n");
                            }
                            break;
                        case 4:
                            try {
                                if (cell.getNumericCellValue() < 0) {
                                    rowError.append("Question Id Order must be a positive integer\n");
                                }
                            } catch (Exception e) {
                                rowError.append("Question Id order must be a number\n");
                            }
                            break;
                        case 5:
                            if (cell.getStringCellValue().trim().length() == 0) {
                                rowError.append("Question Text is missing\n");
                            }
                            break;
                        case 6:
                            type = cell.getStringCellValue().trim();
                            if (type.length() == 0) {
                                rowError.append("Question Type is missing\n");
                            } else {
                                if (!(type.equals(QuestionDto.QuestionType.FREE_TEXT.toString())
                                        || type.equals(QuestionDto.QuestionType.PHOTO.toString())
                                        || type.equals(QuestionDto.QuestionType.VIDEO.toString())
                                        || type.equals(QuestionDto.QuestionType.GEO.toString())
                                        || type.equals(QuestionDto.QuestionType.SCAN.toString())
                                        || type.equals(QuestionDto.QuestionType.TRACK.toString())
                                        || type.equals(QuestionDto.QuestionType.NAME.toString())
                                        || type.equals(QuestionDto.QuestionType.NUMBER.toString())
                                        || type.equals(QuestionDto.QuestionType.OPTION.toString()))
                                        || type.equals(QuestionDto.QuestionType.STRENGTH.toString())) {
                                    rowError.append(
                                            "Invalid question type. Must be either: FREE_TEXT, PHOTO, VIDEO, GEO, NUMBER, OPTION, SCAN, TRACK, NAME, STRENGTH\n");
                                }
                            }
                            break;
                        case 7:
                            if (QuestionType.OPTION.toString().equals(type)
                                    || QuestionType.STRENGTH.toString().equals(type)) {
                                if (cell.getStringCellValue().trim().length() == 0) {
                                    rowError.append("Options are missing\n");
                                }
                            }
                            // TODO: validate language codes
                            break;
                        case 8:
                            // TODO: validate dependency
                            break;
                        case 9:
                            if (!validateBooleanField(cell)) {
                                rowError.append("Allow Other must be either TRUE or FALSE\n");
                            }
                            break;
                        case 10:
                            if (!validateBooleanField(cell)) {
                                rowError.append("Allow Multiple must be either TRUE or FALSE\n");
                            }
                            break;
                        case 11:
                            if (!validateBooleanField(cell)) {
                                rowError.append("Manditory must be either TRUE or FALSE\n");
                            }
                            break;
                        }
                    } catch (Exception e) {
                        rowError.append(e.toString());
                    } finally {
                        if (rowError.toString().trim().length() > 0) {
                            errorMap.put(row.getRowNum() + 1, rowError.toString().trim());
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (inp != null) {
            try {
                inp.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    return errorMap;
}

From source file:org.works.batch.item.excel.poi.PoiSheet.java

License:Apache License

/**
 * {@inheritDoc}/* ww w  .  java2s . c om*/
 */
public String[] getRow(final int rowNumber) {

    int previousCell = -1;
    int currentCell = 0;

    if (rowNumber > this.delegate.getLastRowNum()) {
        return null;
    }
    final Row row = this.delegate.getRow(rowNumber);
    final List<String> cells = new LinkedList<String>();

    final Iterator<Cell> cellIter = row.iterator();

    while (cellIter.hasNext()) {
        final Cell cell = cellIter.next();

        currentCell = cell.getColumnIndex();

        if (previousCell == currentCell - 1) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                cells.add(String.valueOf(cell.getNumericCellValue()));
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cells.add(String.valueOf(cell.getBooleanCellValue()));
                break;
            case Cell.CELL_TYPE_STRING:
                cells.add(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                cells.add(cell.getStringCellValue());
                break;
            default:
                cells.add(StringUtils.EMPTY);
            }
        } else {
            cells.add(StringUtils.EMPTY);
        }

        previousCell = currentCell;
    }
    return cells.toArray(new String[cells.size()]);
}

From source file:org.wso2.carbon.dataservices.sql.driver.processor.reader.ExcelDataReader.java

License:Open Source License

/**
 * Extracts out the columns in the given excel sheet
 *
 * @param sheet Sheet instance corresponding to the desired Excel sheet
 * @return Array containing the column header data
 * @throws java.sql.SQLException SQLException
 *//*from  www.  j a va  2  s  . c om*/
private ColumnInfo[] extractColumnHeaders(Sheet sheet) throws SQLException {
    List<ColumnInfo> headers = new ArrayList<ColumnInfo>();

    /* If hasHeader property is set to false, populate header map with column names following
     * the format 'COLUMN' + 'i' where i corresponds to the column id */
    if (!((TConnection) getConnection()).hasHeader()) {
        int maxColumns = ((TConnection) getConnection()).getMaxColumns();
        for (int i = 0; i < maxColumns; i++) {
            headers.add(new ColumnInfo(i + 1, Constants.COLUMN + (i + 1), sheet.getSheetName(), -1, i + 1));
        }
        return headers.toArray(new ColumnInfo[headers.size()]);
    }
    // Retrieving the first row of the sheet as the header row.
    Row row = sheet.getRow(0);
    if (row != null) {
        Iterator<Cell> itr = row.cellIterator();
        while (itr.hasNext()) {
            Cell cell = itr.next();
            if (cell != null) {
                int cellType = cell.getCellType();
                switch (cellType) {
                case Cell.CELL_TYPE_STRING:
                    headers.add(new ColumnInfo(cell.getColumnIndex() + 1, cell.getStringCellValue(),
                            sheet.getSheetName(), Types.VARCHAR, cell.getColumnIndex() + 1));
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    headers.add(new ColumnInfo(cell.getColumnIndex() + 1,
                            String.valueOf(cell.getNumericCellValue()), sheet.getSheetName(), Types.INTEGER,
                            cell.getColumnIndex() + 1));
                    break;
                default:
                    throw new SQLException("Invalid column type");
                }
            }
        }
    }
    return headers.toArray(new ColumnInfo[headers.size()]);
}

From source file:org.wso2.carbon.dataservices.sql.driver.TDriverUtil.java

License:Open Source License

private static ColumnInfo[] getExcelHeaders(Connection connection, String tableName) throws SQLException {
    List<ColumnInfo> columns = new ArrayList<ColumnInfo>();
    if (!(connection instanceof TExcelConnection)) {
        throw new SQLException("Invalid connection type");
    }/*from  w  w  w .  j  a  va 2s.  co  m*/
    Workbook workbook = ((TExcelConnection) connection).getWorkbook();
    Sheet sheet = workbook.getSheet(tableName);
    if (sheet == null) {
        throw new SQLException("Sheet '" + tableName + "' does not exist");
    }
    Iterator<Cell> cellItr = sheet.getRow(0).cellIterator();
    while (cellItr.hasNext()) {
        Cell header = cellItr.next();
        ColumnInfo column = new ColumnInfo(header.getStringCellValue());
        column.setTableName(tableName);
        column.setSqlType(header.getCellType());
        column.setId(header.getColumnIndex());

        columns.add(column);
    }
    return columns.toArray(new ColumnInfo[columns.size()]);
}