List of usage examples for org.apache.poi.ss.usermodel Row getRowNum
int getRowNum();
From source file:org.tiefaces.components.websheet.utility.ConfigurationUtility.java
License:MIT License
/** * Whether the row is static.This check row after shifted. * * @param sourceConfigRange//from w w w . j av a2 s. co m * the source config range * @param row * the row for check. * @return true is static false is not. */ public static boolean isStaticRowRef(final ConfigRange sourceConfigRange, final Row row) { if (sourceConfigRange.getCommandList() != null) { for (int i = 0; i < sourceConfigRange.getCommandList().size(); i++) { Command command = sourceConfigRange.getCommandList().get(i); int rowIndex = row.getRowNum(); if ((rowIndex >= command.getTopRow()) && (rowIndex < (command.getTopRow() + command.getFinalLength()))) { return false; } } } return true; }
From source file:org.tiefaces.components.websheet.utility.SaveAttrsUtility.java
License:MIT License
/** * Sets the save attrs for sheet.//from w ww . j av a 2 s .co m * * @param sheet * the sheet * @param minRowNum * the min row num * @param maxRowNum * the max row num * @param saveCommentsMap * the save comments map */ public static void setSaveAttrsForSheet(final Sheet sheet, final int minRowNum, final int maxRowNum, final Map<String, String> saveCommentsMap) { for (Row row : sheet) { int rowIndex = row.getRowNum(); if ((rowIndex >= minRowNum) && (rowIndex <= maxRowNum)) { setSaveAttrsForRow(row, saveCommentsMap); } } }
From source file:org.tiefaces.components.websheet.utility.ShiftFormulaUtility.java
License:MIT License
/** * Fixup ref relative row one to one./*from w ww .j av a 2s.c o m*/ * * @param ptg * the ptg * @param newRow * the new row * @return the object */ protected static Object fixupRefRelativeRowOneToOne(final Object ptg, final Row newRow) { if (ptg instanceof RefPtgBase) { if (ptg instanceof Ref3DPxg) { Ref3DPxg ref3dPxg = (Ref3DPxg) ptg; Ref3DPxg new3dpxg = new Ref3DPxg(ref3dPxg.getExternalWorkbookNumber(), new SheetIdentifier(null, new NameIdentifier(ref3dPxg.getSheetName(), false)), new CellReference(newRow.getRowNum(), ref3dPxg.getColumn())); new3dpxg.setClass(ref3dPxg.getPtgClass()); new3dpxg.setColRelative(ref3dPxg.isColRelative()); new3dpxg.setRowRelative(ref3dPxg.isRowRelative()); new3dpxg.setLastSheetName(ref3dPxg.getLastSheetName()); return new3dpxg; } else { RefPtgBase refPtgBase = (RefPtgBase) ptg; return new RefPtg(newRow.getRowNum(), refPtgBase.getColumn(), refPtgBase.isRowRelative(), refPtgBase.isColRelative()); } } else { if (ptg instanceof Area3DPxg) { Area3DPxg area3dPxg = (Area3DPxg) ptg; Area3DPxg new3dpxg = new Area3DPxg(area3dPxg.getExternalWorkbookNumber(), new SheetIdentifier(null, new NameIdentifier(area3dPxg.getSheetName(), false)), area3dPxg.format2DRefAsString()); new3dpxg.setClass(area3dPxg.getPtgClass()); new3dpxg.setFirstColRelative(area3dPxg.isFirstColRelative()); new3dpxg.setLastColRelative(area3dPxg.isLastColRelative()); int shiftRow = newRow.getRowNum() - area3dPxg.getFirstRow(); new3dpxg.setFirstRow(area3dPxg.getFirstRow() + shiftRow); new3dpxg.setLastRow(area3dPxg.getLastRow() + shiftRow); new3dpxg.setFirstRowRelative(area3dPxg.isFirstRowRelative()); new3dpxg.setLastRowRelative(area3dPxg.isLastRowRelative()); new3dpxg.setLastSheetName(area3dPxg.getLastSheetName()); return new3dpxg; } else { AreaPtgBase areaPtgBase = (AreaPtgBase) ptg; int shiftRow = newRow.getRowNum() - areaPtgBase.getFirstRow(); return new AreaPtg(areaPtgBase.getFirstRow() + shiftRow, areaPtgBase.getLastRow() + shiftRow, areaPtgBase.getFirstColumn(), areaPtgBase.getLastColumn(), areaPtgBase.isFirstRowRelative(), areaPtgBase.isLastRowRelative(), areaPtgBase.isFirstColRelative(), areaPtgBase.isLastColRelative()); } } }
From source file:org.tiefaces.components.websheet.utility.ShiftFormulaUtility.java
License:MIT License
/** * Builds the dynamic row for ref ptg base. * * @param ptg//www .j a v a2s . c o m * the ptg * @param originalOperandClass * the original operand class * @param rowList * the row list * @param newPtg * the new ptg * @param includeParenthesis * the include parenthesis */ private static void buildDynamicRowForRefPtgBase(final Object ptg, final byte originalOperandClass, final List<SerialRow> rowList, final Ptg[] newPtg, final boolean includeParenthesis) { RefPtgBase refPtg = (RefPtgBase) ptg; int unitSize = 1; if (includeParenthesis) { unitSize = 2; } for (int i = 0; i < rowList.size(); i++) { Row row = rowList.get(i).getRow(); if (refPtg instanceof Ref3DPxg) { Ref3DPxg ref3dPxg = (Ref3DPxg) refPtg; Ref3DPxg new3dpxg = new Ref3DPxg(ref3dPxg.getExternalWorkbookNumber(), new SheetIdentifier(null, new NameIdentifier(ref3dPxg.getSheetName(), false)), new CellReference(row.getRowNum(), ref3dPxg.getColumn())); new3dpxg.setClass(originalOperandClass); new3dpxg.setColRelative(ref3dPxg.isColRelative()); new3dpxg.setRowRelative(ref3dPxg.isRowRelative()); new3dpxg.setLastSheetName(ref3dPxg.getLastSheetName()); newPtg[i * unitSize] = new3dpxg; } else { RefPtgBase refPtgBase = refPtg; newPtg[i * unitSize] = new RefPtg(row.getRowNum(), refPtgBase.getColumn(), refPtgBase.isRowRelative(), refPtgBase.isColRelative()); } if ((unitSize == 2) && (i < (rowList.size() - 1))) { newPtg[i * unitSize + 1] = ParenthesisPtg.instance; } } }
From source file:org.tiefaces.components.websheet.utility.ShiftFormulaUtility.java
License:MIT License
/** * Builds the dynamic row for area ptg base. * * @param ptg//w ww .j a v a 2 s.c o m * the ptg * @param originalOperandClass * the original operand class * @param rowList * the row list * @param newPtg * the new ptg */ private static void buildDynamicRowForAreaPtgBase(final Object ptg, final byte originalOperandClass, final List<SerialRow> rowList, final Ptg[] newPtg) { AreaPtgBase areaPtg = (AreaPtgBase) ptg; int originFirstRow = areaPtg.getFirstRow(); int originLastRow = areaPtg.getLastRow(); int unitSize = 2; for (int i = 0; i < rowList.size(); i++) { Row row = rowList.get(i).getRow(); int shiftRow = row.getRowNum() - originFirstRow; if (ptg instanceof Area3DPxg) { Area3DPxg area3dPxg = (Area3DPxg) ptg; Area3DPxg new3dpxg = new Area3DPxg(area3dPxg.getExternalWorkbookNumber(), new SheetIdentifier(null, new NameIdentifier(area3dPxg.getSheetName(), false)), area3dPxg.format2DRefAsString()); new3dpxg.setClass(originalOperandClass); new3dpxg.setFirstColRelative(area3dPxg.isFirstColRelative()); new3dpxg.setLastColRelative(area3dPxg.isLastColRelative()); new3dpxg.setFirstRow(originFirstRow + shiftRow); new3dpxg.setLastRow(originLastRow + shiftRow); new3dpxg.setFirstRowRelative(area3dPxg.isFirstRowRelative()); new3dpxg.setLastRowRelative(area3dPxg.isLastRowRelative()); new3dpxg.setLastSheetName(area3dPxg.getLastSheetName()); newPtg[i * unitSize] = new3dpxg; } else { AreaPtgBase areaPtgBase = (AreaPtgBase) ptg; newPtg[i * unitSize] = new AreaPtg(originFirstRow + shiftRow, originLastRow + shiftRow, areaPtgBase.getFirstColumn(), areaPtgBase.getLastColumn(), areaPtgBase.isFirstRowRelative(), areaPtgBase.isLastRowRelative(), areaPtgBase.isFirstColRelative(), areaPtgBase.isLastColRelative()); } if (i < (rowList.size() - 1)) { newPtg[i * unitSize + 1] = ParenthesisPtg.instance; } } }
From source file:org.waterforpeople.mapping.app.harness.DeleteSurveyInstanceHarness.java
License:Open Source License
public void processSheet(String spreadsheetName, String serviceUrl) { InputStream inp;// www. j a 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.RawDataSpreadsheetImporter.java
License:Open Source License
@SuppressWarnings("unchecked") @Override/*from w ww . j ava 2s.c o m*/ 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;//from w w w . j a v a 2 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.waterforpeople.mapping.dataexport.SurveySpreadsheetImporter.java
License:Open Source License
@Override public Map<Integer, String> validate(File file) { InputStream inp = null;// ww w . java2 s . co 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.whitley.object.handler.ImportHandler.java
/** * processImport method handling import of data source. * Process excel data source and record the data in database. * @param request: HTTP request//from w w w . j a v a2 s.com * @param response: HTTP response * @throws ServletException if forwarding to next page fails * @throws IOException if excel file parsing fails * @since 1.0 */ private void processImport(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); //Start processing try (PrintWriter out = response.getWriter()) { //Get file from user, upload to server for processing boolean isMultipartContent = ServletFileUpload.isMultipartContent(request); if (!isMultipartContent) { out.println("No file to be processed<br/>"); return; } FileItemFactory factory = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(factory); try { //Upload List items = upload.parseRequest(request); Iterator iter = items.iterator(); //Read file FileItem fileItem = (FileItem) iter.next(); //Counting variables int no_rows = 0, no_students = 0, no_subjects = 0, no_ss = 0; //Open Excel workbook Workbook wb = WorkbookFactory.create(fileItem.getInputStream()); Sheet studSheet = wb.getSheetAt(0); //Read each line for (Row row : studSheet) { //Skip first line as title if (row.getRowNum() == 0) { continue; } no_rows++; //Extract student,subject and their relation Student student = extractStudent(row); Subject subject = extractSubject(row); Studentsubject ss = extractStudentsubject(row); //Check for duplicates, if not duplicate, add to database if (studController.checkDuplicate(student)) { studController.addStudent(student); no_students++; } if (subjectController.checkDuplicate(subject)) { subjectController.addSubject(subject); no_subjects++; } else { subjectController.addToSize(subject); } if (ssController.checkDuplicate(ss)) { ssController.addStudentSubject(ss); no_ss++; } } //finish parsing, forward to result page request.setAttribute("rows", no_rows); request.setAttribute("students", no_students); request.setAttribute("subjects", no_subjects); request.setAttribute("ss", no_ss); request.getRequestDispatcher("/import/result.jsp").forward(request, response); } catch (FileUploadException e) { out.println("File upload Error"); } catch (InvalidFormatException e1) { out.println("Incorrect Data format given"); } } }