Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:org.tiefaces.components.websheet.utility.ConfigurationUtility.java

License:MIT License

/**
 * Change index number in hidden column.
 *
 * @param configBuildRef/* w ww .  j ava  2s . c  o  m*/
 *            the config build ref
 * @param startRowIndex
 *            the start row index
 * @param fullName
 *            the full name
 * @param changeMap
 *            the change map
 * @param steps
 *            the steps ( 1 add -1 delete ).
 */
public static void changeIndexNumberInHiddenColumn(final ConfigBuildRef configBuildRef, final int startRowIndex,
        final String fullName, final Map<String, String> changeMap, final int steps) {
    String searchName = fullName.substring(0, fullName.lastIndexOf('.') + 1);
    Sheet sheet = configBuildRef.getSheet();
    for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        String fname = getFullNameFromRow(row);
        if ((fname != null) && (fname.indexOf(searchName) >= 0)) {
            int sindex = fname.indexOf(searchName);
            String snum = fname.substring(sindex + searchName.length());
            int sufindex = snum.indexOf(':');
            String suffix = "";
            if (sufindex > 0) {
                snum = snum.substring(0, sufindex);
                suffix = ":";
            }
            int increaseNum = Integer.parseInt(snum) + steps;
            String realFullName = fname.substring(sindex);
            String changeName = fname.replace(searchName + snum + suffix, searchName + increaseNum + suffix);
            if (changeMap.get(realFullName) == null) {
                changeMap.put(realFullName, changeName.substring(sindex));
            }
            setFullNameInHiddenColumn(row, changeName);
        } else {
            return;
        }
    }
}

From source file:org.tiefaces.components.websheet.utility.ConfigurationUtility.java

License:MIT License

/**
 * Decrease index number in hidden column.
 *
 * @param configBuildRef//from www. ja  v  a2  s.  c  om
 *            the config build ref
 * @param startRowIndex
 *            the start row index
 * @param fullName
 *            the full name
 * @param changeMap
 *            the change map
 */
public static void decreaseIndexNumberInHiddenColumn(final ConfigBuildRef configBuildRef,
        final int startRowIndex, final String fullName, final Map<String, String> changeMap) {
    String searchName = fullName.substring(0, fullName.lastIndexOf('.') + 1);
    Sheet sheet = configBuildRef.getSheet();
    for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        String fname = getFullNameFromRow(row);
        if ((fname != null) && (fname.indexOf(searchName) >= 0)) {
            int sindex = fname.indexOf(searchName);
            String snum = fname.substring(sindex + searchName.length());
            int sufindex = snum.indexOf(':');
            String suffix = "";
            if (sufindex > 0) {
                snum = snum.substring(0, sufindex);
                suffix = ":";
            }
            int increaseNum = Integer.parseInt(snum) - 1;
            String realFullName = fname.substring(sindex);
            String changeName = fname.replace(searchName + snum + suffix, searchName + increaseNum + suffix);
            if (changeMap.get(realFullName) == null) {
                changeMap.put(realFullName, changeName.substring(sindex));
            }
            setFullNameInHiddenColumn(row, changeName);
        }
    }
}

From source file:org.ualberta.xsl.ECCJIXSLAuthorExtractor.java

License:Creative Commons License

private void convertFile(File file) {
    try {//from w  ww.  ja  v  a  2s.  c o  m
        System.out.println("Reading File: " + file.getName());
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        Document doc = builder.newDocument();
        Element root = doc.createElement("cwrc");
        doc.appendChild(root);

        Workbook book;
        book = new XSSFWorkbook(new FileInputStream(file));
        Sheet sheet = book.getSheetAt(0);

        for (int i = 0; i <= sheet.getLastRowNum(); ++i) {
            Row row = sheet.getRow(i);

            String sname = convertName(row.getCell(2) == null ? null : row.getCell(2).toString());
            String fname = convertName(row.getCell(3) == null ? null : row.getCell(3).toString());

            if (!isAuthorAdded(fname, sname)) {
                Element entity = doc.createElement("entity");
                entity.appendChild(createPerson(doc, fname, sname));
                root.appendChild(entity);
            }
        }

        File output = new File(
                "./author_build/" + file.getName().substring(0, file.getName().length() - 5) + ".mgxml");
        System.out.println("Writing File: " + output.getName());
        transformDocument(doc, output);
    } catch (IOException ex) {
        System.err.println("Error reading file: " + ex.getMessage());
    } catch (ParserConfigurationException ex) {
        System.err.println("Error creating xml document: " + ex.getMessage());
    } catch (TransformerConfigurationException ex) {
        System.err.println("Error writing xml document: " + ex.getMessage());
    } catch (TransformerException ex) {
        System.err.println("Error writing xml document: " + ex.getMessage());
    }
}

From source file:org.ualberta.xsl.ECCJIXSLOrganizationExtractor.java

License:Creative Commons License

private void convertFile(File file) {
    try {/*  w w w.j a va 2 s .c  om*/
        System.out.println("Reading File: " + file.getName());
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        Document doc = builder.newDocument();
        Element root = doc.createElement("cwrc");
        doc.appendChild(root);

        Workbook book;
        book = new XSSFWorkbook(new FileInputStream(file));
        Sheet sheet = book.getSheetAt(0);

        for (int i = 1; i <= sheet.getLastRowNum(); ++i) {
            Row row = sheet.getRow(i);

            String orgName = convertName(row.getCell(7) == null ? null : row.getCell(7).toString());
            String orgLoc = convertName(row.getCell(6) == null ? null : row.getCell(6).toString());

            if (orgName != null) {
                List<String> orgNames = new ArrayList<String>();
                List<String> orgLocs = new ArrayList<String>();
                splitNames(orgName, orgLoc, orgNames, orgLocs);

                for (int j = 0; j < orgNames.size(); ++j) {
                    if (!isOrganizationAdded(orgNames.get(j), orgLocs.get(j))) {
                        Element entity = doc.createElement("entity");
                        entity.appendChild(createOrganization(doc, orgNames.get(j), orgLocs.get(j)));
                        root.appendChild(entity);
                    }
                }
            }
        }

        File output = new File(
                "./organization_build/" + file.getName().substring(0, file.getName().length() - 5) + ".mgxml");
        System.out.println("Writing File: " + output.getName());
        transformDocument(doc, output);
    } catch (IOException ex) {
        System.err.println("Error reading file: " + ex.getMessage());
    } catch (ParserConfigurationException ex) {
        System.err.println("Error creating xml document: " + ex.getMessage());
    } catch (TransformerConfigurationException ex) {
        System.err.println("Error writing xml document: " + ex.getMessage());
    } catch (TransformerException ex) {
        System.err.println("Error writing xml document: " + ex.getMessage());
    }
}

From source file:org.ualberta.xsl.ECCJIXSLTitleExtractor.java

License:Creative Commons License

private void convertFile(File file) {
    try {//w  w w  .j  a v  a2 s .c om
        System.out.println("Reading File: " + file.getName());
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        Document doc = builder.newDocument();

        Namespace namespace = Namespace.get("xsi", "http://www.w3.org/2001/XMLSchema-instance");

        Element root = doc.createElement("modsCollectionDefinition");
        doc.appendChild(root);

        Workbook book;
        book = new XSSFWorkbook(new FileInputStream(file));
        Sheet sheet = book.getSheetAt(0);

        for (int i = 1; i <= sheet.getLastRowNum(); ++i) {
            Row row = sheet.getRow(i);

            String JNL = row.getCell(0) == null ? null : row.getCell(0).toString();
            String NDX = row.getCell(1) == null ? null : getCellStringAsInt(row.getCell(1));
            String SNAME = convertToTitleCase(row.getCell(2) == null ? null : row.getCell(2).toString());
            String FNAME = convertToTitleCase(row.getCell(3) == null ? null : row.getCell(3).toString());
            String TI = convertToTitleCase(row.getCell(4) == null ? "---" : row.getCell(4).toString());
            String MAG = row.getCell(5) == null ? null : convertToTitleCase(row.getCell(5).toString());
            String VOL = row.getCell(6) == null ? null : getCellStringAsInt(row.getCell(6));
            String NUM = row.getCell(7) == null ? null : getCellStringAsInt(row.getCell(7));
            String MO = row.getCell(8) == null ? null : row.getCell(8).toString();
            String YR = row.getCell(9) == null ? null : getCellStringAsInt(row.getCell(9));
            String PG = row.getCell(10) == null ? null : row.getCell(10).toString();
            String S1 = row.getCell(11) == null ? null : convertToTitleCase(row.getCell(11).toString());
            String S2 = row.getCell(12) == null ? null : convertToTitleCase(row.getCell(12).toString());
            String GEN = row.getCell(13) == null ? null : row.getCell(13).toString();
            String ORG = row.getCell(14) == null ? null : row.getCell(14).toString();
            String FLINE = row.getCell(15) == null ? null
                    : row.getCell(15).toString().toLowerCase() + "[First line of poetry.]";
            String SP = row.getCell(16) == null ? null : row.getCell(16).toString();

            String id = JNL + NDX;

            //TODO: Check if entry exists;
            root.appendChild(createTitle(doc, GEN, TI, FNAME, SNAME, FLINE, S1, S2, MAG, ORG, VOL, NUM, PG, MO,
                    YR, id, SP));
        }

        File output = new File(
                "./title_build/" + file.getName().substring(0, file.getName().length() - 5) + ".mgxml");
        System.out.println("Writing File: " + output.getName());
        transformDocument(doc, output);
    } catch (IOException ex) {
        System.err.println("Error reading file: " + ex.getMessage());
    } catch (ParserConfigurationException ex) {
        System.err.println("Error creating xml document: " + ex.getMessage());
    } catch (TransformerConfigurationException ex) {
        System.err.println("Error writing xml document: " + ex.getMessage());
    } catch (TransformerException ex) {
        System.err.println("Error writing xml document: " + ex.getMessage());
    }
}

From source file:org.ujmp.poi.AbstractMatrixExcelImporter.java

License:Open Source License

public DenseObjectMatrix2D importFromSheet(final Sheet sheet) throws InvalidFormatException, IOException {
    final int rowCount = sheet.getLastRowNum();
    int columnCount = 0;

    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();/*  w  w  w.  ja va 2  s  . c om*/
        if (row.getLastCellNum() > columnCount) {
            columnCount = row.getLastCellNum();
        }
    }

    final DefaultDenseObjectMatrix2D matrix = new DefaultDenseObjectMatrix2D(rowCount, columnCount);
    matrix.setLabel(sheet.getSheetName());

    for (int r = 0; r < rowCount; r++) {
        Row row = sheet.getRow(r);
        if (row != null) {
            for (int c = 0; c < columnCount; c++) {
                Cell cell = row.getCell(c);
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        matrix.setAsBoolean(cell.getBooleanCellValue(), r, c);
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        matrix.setAsString(cell.getCellFormula(), r, c);
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        matrix.setAsDouble(cell.getNumericCellValue(), r, c);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        matrix.setAsString(cell.getStringCellValue(), r, c);
                        break;
                    default:
                        break;
                    }

                }
            }
        }
    }

    return matrix;
}

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

License:Open Source License

/**
 * finds or creates the row at the given index
 *
 * @param index/* w ww.jav a 2  s . co m*/
 * @param rowLocalMax
 * @param sheet
 * @return
 */
private synchronized Row getRow(int index, Sheet sheet) {
    Row row = null;
    if (index < sheet.getLastRowNum()) {
        row = sheet.getRow(index);
        if (row == null) {
            row = sheet.createRow(index);
        }
    } else {
        row = sheet.createRow(index);
    }
    log.debug("Row " + index); // debug printout to study backward jumps

    return row;

}

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

License:Open Source License

@SuppressWarnings("unchecked")
@Override/* ww w  . ja va2  s .  com*/
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.SurveySpreadsheetImporter.java

License:Open Source License

@Override
public void executeImport(File file, String serverBase, Map<String, String> criteria) {
    InputStream inp = null;//w  w w . jav  a2  s . c o  m
    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.wicketstuff.poi.excel.TableParserTest.java

License:Apache License

public void testTable1() throws IOException, ResourceStreamNotFoundException, ParseException {
    Sheet sheet = new HSSFWorkbook().createSheet();
    TableParser tableParser = new TableParser(sheet, new GeneralPurposeExporter());
    tableParser.parse(new Table1());
    assertEquals(9, sheet.getLastRowNum());
    assertEquals(3, sheet.getNumMergedRegions());
    assertEquals(0, sheet.getMergedRegion(0).getFirstColumn());
    assertEquals(1, sheet.getMergedRegion(0).getLastColumn());
    assertEquals(3, sheet.getMergedRegion(0).getFirstRow());
    assertEquals(3, sheet.getMergedRegion(0).getLastRow());
    assertEquals(0, sheet.getMergedRegion(1).getFirstColumn());
    assertEquals(0, sheet.getMergedRegion(1).getLastColumn());
    assertEquals(4, sheet.getMergedRegion(1).getFirstRow());
    assertEquals(5, sheet.getMergedRegion(1).getLastRow());
    assertEquals(0, sheet.getMergedRegion(2).getFirstColumn());
    assertEquals(2, sheet.getMergedRegion(2).getLastColumn());
    assertEquals(6, sheet.getMergedRegion(2).getFirstRow());
    assertEquals(6, sheet.getMergedRegion(2).getLastRow());
    assertEquals("04/01/2000", sheet.getRow(3).getCell(2).getStringCellValue());
    assertEquals("05/01/2000", sheet.getRow(4).getCell(2).getStringCellValue());
    assertEquals("06/01/2000", sheet.getRow(5).getCell(2).getStringCellValue());
    assertNull(sheet.getRow(6).getCell(2));
    assertEquals("08/01/2000", sheet.getRow(7).getCell(2).getStringCellValue());
    // tester.startResource(new ResourceStreamResource(new
    // XlsStream(sheet.getWorkbook())));
    // PoiTestUtil.openFileInResponse(tester);

}