List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum
@Override public int getLastRowNum()
From source file:org.etudes.mneme.impl.AttachmentServiceImpl.java
License:Apache License
/** * Creates the Order tab for answers Item Analysis * * @param or_questions List of Order questions * @param workbook Workbook object//from w w w. j a v a 2s.com * @param assessment Assessment object */ void createOrderTab(List<Question> or_questions, HSSFWorkbook workbook, Assessment assessment) { if (or_questions == null || or_questions.size() == 0) return; String assmtId = assessment.getId(); HSSFSheet sheet = null; HSSFRow row; String[] choiceLabels = new String[] { "1.", "2.", "3.", "4.", "5.", "6.", "7.", "8.", "9.", "10." }; int[] choiceCount = new int[10]; //Determine which question has most number of choices so as to determine header column count int headerSize = getMaxChoices(or_questions); boolean headerRowDone = false; //Iterate through each question for (Iterator it = or_questions.iterator(); it.hasNext();) { Question q = (Question) it.next(); //Fetch all submissions to the question List<Answer> answers = this.submissionService.findSubmissionAnswers(assessment, q, FindAssessmentSubmissionsSort.userName_a, null, null); if (answers == null || answers.size() == 0) return; //Create header row once if (!headerRowDone) { sheet = workbook.createSheet("Order"); HSSFRow headerRow = sheet.createRow((short) 0); HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); // Printing header row HSSFCell cell0 = headerRow.createCell((short) (0)); cell0.setCellStyle(style); cell0.setCellValue(this.messages.getFormattedMessage("item_analysis_question", null)); for (int i = 1; i <= headerSize; i++) { HSSFCell cell1 = headerRow.createCell((short) (i)); cell1.setCellStyle(style); cell1.setCellValue(choiceLabels[i - 1]); } headerRowDone = true; } int choiceListSize = 0; Set<Integer> correctAnswers = ((OrderQuestionImpl) q.getTypeSpecificQuestion()).getCorrectAnswerSet(); List<OrderQuestionImpl.OrderQuestionChoice> choiceList = ((OrderQuestionImpl) q .getTypeSpecificQuestion()).getChoicesAsAuthored(); choiceListSize = choiceList.size(); //Iterate through each submission for (Answer answer : answers) { TypeSpecificAnswer a = answer.getTypeSpecificAnswer(); if (a instanceof OrderAnswerImpl) { OrderAnswerImpl oa = (OrderAnswerImpl) a; Map<String, Value> ansMap = oa.getAnswer(); int l = 0; for (OrderQuestionImpl.OrderQuestionChoice oqc : choiceList) { if (ansMap.get(oqc.getId()) != null) { Value value = ansMap.get(oqc.getId()); if (value.getValue() != null && value.getValue().equals(String.valueOf(l))) { choiceCount[l]++; } } l++; } } } int rowNum = sheet.getLastRowNum() + 1; row = sheet.createRow(rowNum); String[] choiceTextArray = new String[choiceList.size()]; int j = 0; for (OrderQuestionImpl.OrderQuestionChoice oqc : choiceList) { choiceTextArray[j] = (j + 1) + "." + stripHtml(oqc.getText()); j++; } String quest_desc = stripHtml(q.getDescription()) + getCommaAnswers(choiceTextArray); row.createCell((short) 0).setCellValue(quest_desc); for (int k = 1; k <= choiceListSize; k++) { row.createCell((short) k).setCellValue(choiceCount[k - 1]); } for (int k = 1; k <= choiceListSize; k++) { choiceCount[k - 1] = 0; } } }
From source file:org.etudes.mneme.impl.AttachmentServiceImpl.java
License:Apache License
/** * Iterates through all question types, captures responses and creates export summary spreadsheet * @param workbook Workbook object/*w ww . j a va2 s . com*/ * @param assessment Assessment object * @return Spreadsheet with Export summary */ HSSFSheet createResponsesSheet(HSSFWorkbook workbook, Assessment assessment) { boolean isSurvey; if (assessment.getType() == AssessmentType.survey) isSurvey = true; else isSurvey = false; Map<String, Integer> userRowMap = new HashMap(); HSSFSheet sheet = workbook.createSheet("Submission responses"); HSSFRow headerRow = sheet.createRow((short) 0); AssessmentParts part = assessment.getParts(); List<Part> parts = part.getParts(); if (parts == null || parts.size() == 0) return null; List<Question> questions = new ArrayList(); for (Iterator partIt = parts.iterator(); partIt.hasNext();) { Part partObj = (Part) partIt.next(); List<Question> questionsUsed = partObj.getQuestionsUsed(); questions.addAll(questionsUsed); } HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); // Printing header row and question text if (!isSurvey) { HSSFCell cell0 = headerRow.createCell((short) (0)); cell0.setCellStyle(style); cell0.setCellValue(this.messages.getFormattedMessage("export_lastname", null)); HSSFCell cell1 = headerRow.createCell((short) (1)); cell1.setCellStyle(style); cell1.setCellValue(this.messages.getFormattedMessage("export_firstname", null)); HSSFCell cell2 = headerRow.createCell((short) (2)); cell2.setCellStyle(style); cell2.setCellValue(this.messages.getFormattedMessage("export_username", null)); HSSFCell cell3 = headerRow.createCell((short) (3)); cell3.setCellStyle(style); cell3.setCellValue(this.messages.getFormattedMessage("export_score", null)); } else { HSSFCell cell0 = headerRow.createCell((short) (0)); cell0.setCellStyle(style); cell0.setCellValue(this.messages.getFormattedMessage("export_user", null)); } int i; if (isSurvey) i = 1; else i = 4; for (Iterator it = questions.iterator(); it.hasNext();) { Question q = (Question) it.next(); String quest_desc = null; TypeSpecificQuestion tsq = q.getTypeSpecificQuestion(); if (tsq instanceof FillBlanksQuestionImpl) { quest_desc = stripHtml(((FillBlanksQuestionImpl) tsq).getText()); } else if (tsq instanceof FillInlineQuestionImpl) { quest_desc = stripHtml(((FillInlineQuestionImpl) tsq).getText()); } else { if (tsq instanceof EssayQuestionImpl || tsq instanceof TaskQuestionImpl) { if (tsq instanceof EssayQuestionImpl) { EssayQuestionImpl eqi = (EssayQuestionImpl) tsq; if (eqi.getSubmissionType() == SubmissionType.inline) quest_desc = stripHtml(q.getDescription()); } if (tsq instanceof TaskQuestionImpl) { TaskQuestionImpl tqi = (TaskQuestionImpl) tsq; if (tqi.getSubmissionType() == SubmissionType.inline) quest_desc = stripHtml(q.getDescription()); } } else { quest_desc = stripHtml(q.getDescription()); } } if (quest_desc != null) { HSSFCell cell = headerRow.createCell((short) (i++)); cell.setCellStyle(style); cell.setCellValue(quest_desc); } } int j; if (isSurvey) j = 1; else j = 4; boolean answersExist = false; for (Iterator it = questions.iterator(); it.hasNext();) { Question q = (Question) it.next(); TypeSpecificQuestion tsq = q.getTypeSpecificQuestion(); //Only captures inline submissions for essays and tasks if (tsq instanceof EssayQuestionImpl || tsq instanceof TaskQuestionImpl) { if (tsq instanceof EssayQuestionImpl) { EssayQuestionImpl eqi = (EssayQuestionImpl) tsq; if (eqi.getSubmissionType() != SubmissionType.inline) { continue; } } if (tsq instanceof TaskQuestionImpl) { TaskQuestionImpl tqi = (TaskQuestionImpl) tsq; if (tqi.getSubmissionType() != SubmissionType.inline) { continue; } } } List<Answer> answers = this.submissionService.findSubmissionAnswers(assessment, q, Boolean.TRUE, FindAssessmentSubmissionsSort.userName_a, null, null); if (answers == null || answers.size() == 0) continue; else answersExist = true; for (Answer answer : answers) { HSSFRow row; try { String userId = answer.getSubmission().getUserId(); String subId = answer.getSubmission().getId(); if (userRowMap == null || userRowMap.size() == 0 || (userRowMap.get(userId + subId) == null)) { int rowNum = sheet.getLastRowNum() + 1; row = sheet.createRow(rowNum); if (!isSurvey) { User user = this.userDirectoryService.getUser(userId); row.createCell((short) 0).setCellValue(user.getLastName()); row.createCell((short) 1).setCellValue(user.getFirstName()); row.createCell((short) 2).setCellValue(user.getDisplayId()); row.createCell((short) 3).setCellValue( roundTwoDecimals(answer.getSubmission().getTotalScore().floatValue())); } else { row.createCell((short) 0) .setCellValue(this.messages.getFormattedMessage("export_user", null)); } userRowMap.put(userId + subId, new Integer(rowNum)); } else { row = sheet.getRow(userRowMap.get(userId + subId).intValue()); } TypeSpecificAnswer a = answer.getTypeSpecificAnswer(); if (a instanceof EssayAnswerImpl) { EssayAnswerImpl essay = (EssayAnswerImpl) a; row.createCell((short) j).setCellValue(stripHtml(essay.getAnswerData())); } if (a instanceof TrueFalseAnswerImpl) { TrueFalseAnswerImpl tf = (TrueFalseAnswerImpl) a; if (!isSurvey && tf.getCompletelyCorrect() != null && tf.getCompletelyCorrect().booleanValue()) row.createCell((short) j).setCellValue("*" + tf.getAnswer() + "*"); else row.createCell((short) j).setCellValue(tf.getAnswer()); } if (a instanceof MultipleChoiceAnswerImpl) { MultipleChoiceAnswerImpl mc = (MultipleChoiceAnswerImpl) a; List<MultipleChoiceQuestionImpl.MultipleChoiceQuestionChoice> choiceList = ((MultipleChoiceQuestionImpl) mc .getAnswerObject().getQuestion().getTypeSpecificQuestion()).getChoicesAsAuthored(); String[] ansArray = mc.getAnswers(); String[] choiceArray = new String[mc.getAnswers().length]; Set<Integer> correctAnswers = ((MultipleChoiceQuestionImpl) mc.getAnswerObject() .getQuestion().getTypeSpecificQuestion()).getCorrectAnswerSet(); int l = 0; for (Iterator chIt = choiceList.iterator(); chIt.hasNext();) { MultipleChoiceQuestionImpl.MultipleChoiceQuestionChoice mq = (MultipleChoiceQuestionImpl.MultipleChoiceQuestionChoice) chIt .next(); if (Arrays.asList(ansArray).contains(mq.getId())) { if (!isSurvey && correctAnswers.contains(Integer.parseInt(mq.getId()))) { choiceArray[l] = "*" + stripHtml(mq.getText().trim()) + "*"; } else { choiceArray[l] = stripHtml(mq.getText().trim()); } l++; } } row.createCell((short) j).setCellValue(getCommaAnswers(choiceArray)); } if (a instanceof OrderAnswerImpl) { OrderAnswerImpl oa = (OrderAnswerImpl) a; List<OrderQuestionImpl.OrderQuestionChoice> choiceList = ((OrderQuestionImpl) oa .getAnswerObject().getQuestion().getTypeSpecificQuestion()).getChoicesAsAuthored(); Map<String, Value> ansMap = oa.getAnswer(); String[] choiceArray = new String[ansMap.size()]; int l = 0; for (Map.Entry entry : ansMap.entrySet()) { String entryId = (String) entry.getKey(); // Value value = ansMap.get(oqc.getId()); if (!isSurvey && entry.getValue() != null && ((Value) entry.getValue()).getValue() != null && ((Value) entry.getValue()).getValue().equals( entryId)/*((OrderQuestionImpl.OrderQuestionChoice) choiceList.get(l)).getId().equals(entryId)*/) { choiceArray[l] = "*" + stripHtml(getChoiceText(choiceList, entryId).trim()) + "*"; } else { if (((Value) entry.getValue()).getValue() == null) { choiceArray[l] = "Select"; } else { choiceArray[l] = stripHtml(getChoiceText(choiceList, entryId).trim()); } } l++; } row.createCell((short) j).setCellValue(getCommaAnswers(choiceArray)); } if (a instanceof FillBlanksAnswerImpl) { FillBlanksAnswerImpl fb = (FillBlanksAnswerImpl) a; row.createCell((short) j) .setCellValue(stripHtml(getCommaAnswers(checkCorrectFill(fb, isSurvey)))); } if (a instanceof FillInlineAnswerImpl) { FillInlineAnswerImpl fi = (FillInlineAnswerImpl) a; row.createCell((short) j) .setCellValue(stripHtml(getCommaAnswers(checkCorrectFillInline(fi, isSurvey)))); } if (a instanceof LikertScaleAnswerImpl) { LikertScaleAnswerImpl ls = (LikertScaleAnswerImpl) a; LikertScaleQuestionImpl lsq = (LikertScaleQuestionImpl) ls.getAnswerObject().getQuestion() .getTypeSpecificQuestion(); List<LikertScaleQuestionImpl.LikertScaleQuestionChoice> choiceList = lsq.getChoices(); for (Iterator chIt = choiceList.iterator(); chIt.hasNext();) { LikertScaleQuestionImpl.LikertScaleQuestionChoice lqc = (LikertScaleQuestionImpl.LikertScaleQuestionChoice) chIt .next(); if (lqc.getId().equals(ls.getAnswer())) { row.createCell((short) j).setCellValue(stripHtml(lqc.getText())); break; } } } if (a instanceof MatchAnswerImpl) { MatchAnswerImpl ma = (MatchAnswerImpl) a; Map matchMap = (LinkedHashMap) ma.getAnswer(); Iterator it2 = matchMap.entrySet().iterator(); StringBuffer matchStrBuf = new StringBuffer(); List choiceList = ((MatchQuestionImpl) ma.getAnswerObject().getQuestion() .getTypeSpecificQuestion()).getPairsForDelivery(); while (it2.hasNext()) { Map.Entry entry = (Map.Entry) it2.next(); String key = (String) entry.getKey(); String value = (String) ((Value) entry.getValue()).getValue(); String matchVal = fetchName(choiceList, key, true); boolean correctMatch = checkCorrectMatch(choiceList, key, value); if (!isSurvey && correctMatch) matchStrBuf.append("*"); matchStrBuf.append(stripHtml(matchVal.trim())); matchStrBuf.append("->"); String choiceVal = fetchName(choiceList, value, false); if (choiceVal == null) matchStrBuf.append(this.messages.getFormattedMessage("nosel_made", null)); else matchStrBuf.append(stripHtml(choiceVal.trim())); if (!isSurvey && correctMatch) matchStrBuf.append("*"); matchStrBuf.append(", "); } if (matchStrBuf.length() > 0 && matchStrBuf.charAt(matchStrBuf.length() - 2) == ',') { String matchStrBufTrim = matchStrBuf.substring(0, matchStrBuf.length() - 2); row.createCell((short) j).setCellValue(stripHtml(matchStrBufTrim)); } } if (a instanceof TaskAnswerImpl) { TaskAnswerImpl ta = (TaskAnswerImpl) a; row.createCell((short) j).setCellValue(stripHtml(ta.getAnswerData())); } } catch (UserNotDefinedException e) { M_log.warn("createResponsesSheet: " + e.toString()); } } j = j + 1; } if (!answersExist) return null; return sheet; }
From source file:org.etudes.mneme.impl.AttachmentServiceImpl.java
License:Apache License
/** * Creates True False tab for answers Item Analysis * * @param tf_questions List of True False questions * @param workbook Workbook object//from ww w . ja v a 2 s . com * @param assessment Assessment object */ void createTrueFalseTab(List<Question> tf_questions, HSSFWorkbook workbook, Assessment assessment) { if (tf_questions == null || tf_questions.size() == 0) return; String assmtId = assessment.getId(); HSSFSheet sheet = null; HSSFRow row; boolean headerRowDone = false; for (Iterator it = tf_questions.iterator(); it.hasNext();) { Question q = (Question) it.next(); List<Answer> answers = this.submissionService.findSubmissionAnswers(assessment, q, FindAssessmentSubmissionsSort.userName_a, null, null); if (answers == null || answers.size() == 0) return; if (!headerRowDone) { sheet = workbook.createSheet("TrueFalse"); HSSFRow headerRow = sheet.createRow((short) 0); HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); // Printing header row HSSFCell cell0 = headerRow.createCell((short) (0)); cell0.setCellStyle(style); cell0.setCellValue(this.messages.getFormattedMessage("item_analysis_question", null)); HSSFCell cell1 = headerRow.createCell((short) (1)); cell1.setCellStyle(style); cell1.setCellValue(this.messages.getFormattedMessage("true-header", null)); HSSFCell cell2 = headerRow.createCell((short) (2)); cell2.setCellStyle(style); cell2.setCellValue(this.messages.getFormattedMessage("false-header", null)); headerRowDone = true; } int trueCount = 0, falseCount = 0; for (Answer answer : answers) { TypeSpecificAnswer a = answer.getTypeSpecificAnswer(); if (a instanceof TrueFalseAnswerImpl) { TrueFalseAnswerImpl tf = (TrueFalseAnswerImpl) a; if (tf.getAnswer().equals("true")) trueCount++; if (tf.getAnswer().equals("false")) falseCount++; } } int rowNum = sheet.getLastRowNum() + 1; row = sheet.createRow(rowNum); String quest_desc = stripHtml(q.getDescription()); row.createCell((short) 0).setCellValue(quest_desc); if (((TrueFalseQuestionImpl) q.getTypeSpecificQuestion()).getCorrectAnswer().equals("true")) row.createCell((short) 1).setCellValue("*" + trueCount + "*"); else row.createCell((short) 1).setCellValue(trueCount); if (((TrueFalseQuestionImpl) q.getTypeSpecificQuestion()).getCorrectAnswer().equals("false")) row.createCell((short) 2).setCellValue("*" + falseCount + "*"); else row.createCell((short) 2).setCellValue(falseCount); } }
From source file:org.everit.jira.reporting.plugin.export.ExcelToCsvConverter.java
License:Apache License
private void convertToCSV() { HSSFSheet sheet; HSSFRow row;/*from w ww .j a v a 2s. com*/ int lastRowNum = 0; csvData = new ArrayList<>(); int numSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numSheets; i++) { sheet = workbook.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { lastRowNum = sheet.getLastRowNum(); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); rowToCSV(row); } } } }
From source file:org.exoplatform.addon.pulse.service.ws.RestActivitiesStatistic.java
License:Open Source License
private String buildCsvContent(HSSFWorkbook workbook) { HSSFSheet sheet = workbook.getSheetAt(0); StringBuffer buffer = new StringBuffer(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i);/*w ww . j av a 2s . c o m*/ for (int j = 0; j < row.getLastCellNum(); j++) { HSSFCell cell = row.getCell(j); int cellType = cell.getCellType(); if (cellType == HSSFCell.CELL_TYPE_STRING) { buffer.append(cell.getStringCellValue()); } else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) { buffer.append(new DecimalFormat("#").format(cell.getNumericCellValue())); } if (j < row.getLastCellNum() - 1) { buffer.append(','); } } buffer.append('\n'); } return buffer.toString(); }
From source file:org.exoplatform.services.document.impl.MSExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xls file content. * //from www . j ava 2 s .c o m * @param is an input stream with .xls file content. * @return The string only with text from file content. */ public String getContentAsText(InputStream is) throws IOException, DocumentReadException { if (is == null) { throw new IllegalArgumentException("InputStream is null."); } final StringBuilder builder = new StringBuilder(""); SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT); try { if (is.available() == 0) { return ""; } HSSFWorkbook wb; try { wb = new HSSFWorkbook(is); } catch (IOException e) { throw new DocumentReadException("Can't open spreadsheet.", e); } for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { HSSFSheet sheet = wb.getSheetAt(sheetNum); if (sheet != null) { for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { HSSFRow row = sheet.getRow(rowNum); if (row != null) { int lastcell = row.getLastCellNum(); for (int k = 0; k < lastcell; k++) { final HSSFCell cell = row.getCell((short) k); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { double d = cell.getNumericCellValue(); if (isCellDateFormatted(cell)) { Date date = HSSFDateUtil.getJavaDate(d); String cellText = dateFormat.format(date); builder.append(cellText).append(" "); } else { builder.append(d).append(" "); } break; } case HSSFCell.CELL_TYPE_FORMULA: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getCellFormula().toString()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_BOOLEAN: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getBooleanCellValue()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_ERROR: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getErrorCellValue()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_STRING: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getStringCellValue().toString()).append(" "); return null; } }); break; default: break; } } } } } } } } finally { if (is != null) { try { is.close(); } catch (IOException e) { if (LOG.isTraceEnabled()) { LOG.trace("An exception occurred: " + e.getMessage()); } } } } return builder.toString(); }
From source file:org.gageot.excel.core.RowMapperSheetExtractor.java
License:Apache License
@Override public List<T> extractData(HSSFSheet sheet) throws IOException { List<T> rows = Lists.newArrayList(); int firstRowIndex = sheet.getFirstRowNum(); int lastRowIndex = sheet.getLastRowNum(); for (int i = firstRowIndex; i <= lastRowIndex; i++) { T row = rowMapper.mapRow(sheet.getRow(i), i); if (null != row) { rows.add(row);/*from www . j ava 2 s . c o m*/ } } return rows; }
From source file:org.gaixie.micrite.car.service.impl.DealWithCar.java
@Override public int doJob(File src, Map<String, String> res) throws Exception { getPartner1();//ww w. j ava 2s . c o m getPartner2(); getPartner3(); getPartner4(); getPartner5(); getPartner6(); getPartner7(); // TODO Auto-generated method stub if (log.isInfoEnabled()) { if (src == null) log.info("upload file is null."); } HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(src)); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(sheet.getFirstRowNum()); int colSize = row.getLastCellNum(); int rowSize = sheet.getLastRowNum(); if (log.isInfoEnabled()) log.info("this is colSize=" + rowSize); try { if (colSize == COLS_1_2) {// for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); save1_2(row); } } else if (colSize == COLS_3) {// paiSeTmp = DictionaryUtil.getDictionaryWhenNotGiveLast("", paiSe); for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); save3(row); } } else if (colSize > COLS_3) {// if (sheet.getRow(sheet.getFirstRowNum()) != null && sheet.getRow(sheet.getFirstRowNum()).getCell(PAISE_COL_1_2) != null && sheet.getRow(sheet.getFirstRowNum()).getCell(PAISE_COL_1_2).getStringCellValue() != null && PAISE_TITLE.equals(sheet.getRow(sheet.getFirstRowNum()).getCell(PAISE_COL_1_2) .getStringCellValue().trim())) { for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); save1_2(row); } } else { for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); if (row.getCell(PAISE_COL_3) != null && row.getCell(PAISE_COL_3).getStringCellValue() != null) { paiSeTmp = DictionaryUtil.getDictionaryWhenNotGiveLast( row.getCell(PAISE_COL_3).getStringCellValue(), paiSe); } else { paiSeTmp = DictionaryUtil.getDictionaryWhenNotGiveLast("", paiSe); } save3(row); } } } else { log.info("does not deal with colum size=" + colSize); } } catch (Exception e) { // TODO: handle exception e.printStackTrace(); log.info(e); } return IDealWith.OK; }
From source file:org.gaixie.micrite.enterprise.service.impl.DealWithEnterprise.java
@Override public int doJob(File src, Map<String, String> res) throws Exception { List<Dictionary> qualifications = dictionaryService.findALLDictionary(Enterprise.QUALIFICATION_TYPE); List<Dictionary> kinds = dictionaryService.findALLDictionary(Enterprise.KIND_TYPE); List<Dictionary> workTypes = dictionaryService.findALLDictionary(Enterprise.WORKTYPE_TYPE); // List<Dictionary> ranges=dictionaryService.findALLDictionary(11); // TODO Auto-generated method stub HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(src)); Enterprise enterprise = new Enterprise(); HSSFSheet sheet = wb.getSheetAt(0); String temp = null;/*w w w. j av a 2 s . co m*/ for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); HSSFCell cell = row.getCell(4); try { temp = cell.getStringCellValue(); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } if (enterpriseService.existEnterprise(null, temp)) continue; cell = row.getCell(1); enterprise.setUnitName(cell.getStringCellValue()); cell = row.getCell(2); enterprise.setLegalPerson(cell.getStringCellValue()); cell = row.getCell(3); enterprise.setTelephone1(cell.getStringCellValue()); cell = row.getCell(4); enterprise.setLicense(cell.getStringCellValue()); cell = row.getCell(5); enterprise.setQualification(DictionaryUtil.getDictionary(cell.getStringCellValue(), qualifications)); cell = row.getCell(6); enterprise.setHandleMan(cell.getStringCellValue()); cell = row.getCell(7); enterprise.setTelephone2(cell.getStringCellValue()); cell = row.getCell(8); enterprise.setTelephone3(cell.getStringCellValue()); cell = row.getCell(9); enterprise.setCommission(cell.getStringCellValue()); cell = row.getCell(10); enterprise.setTelephone4(cell.getStringCellValue()); cell = row.getCell(11); enterprise.setKind(DictionaryUtil.getDictionary(cell.getStringCellValue(), kinds)); cell = row.getCell(12); if (cell.getStringCellValue() != null) { try { enterprise.setLicenseDate(df.parse(cell.getStringCellValue())); } catch (Exception e) { } } cell = row.getCell(13); if (cell.getStringCellValue() != null) { try { enterprise.setDateBegin(df.parse(cell.getStringCellValue()));// HH:mm:ss.S } catch (Exception e) { } } cell = row.getCell(14); if (cell.getStringCellValue() != null) { try { enterprise.setDateEnd(df.parse(cell.getStringCellValue())); } catch (Exception e) { } } cell = row.getCell(15); enterprise.setAddress(cell.getStringCellValue()); cell = row.getCell(16); if (cell.getStringCellValue() != null) { try { enterprise.setEditDate(df.parse(cell.getStringCellValue())); } catch (Exception e) { } } cell = row.getCell(17); enterprise.setWorkArea(cell.getStringCellValue()); cell = row.getCell(18); enterprise.setWorkRemark(cell.getStringCellValue()); cell = row.getCell(19); enterprise.setWorkType(DictionaryUtil.getDictionary(cell.getStringCellValue(), workTypes)); //?cell20 ?? //--------------------------------- //? enterprise.setStation(DictionaryUtil.getDefaultDictionary()); enterprise.setStatus(IEnterpriseService.STATUS_NORMAL); enterpriseService.add(enterprise); } return IDealWith.OK; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow sourceRow = worksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {//from ww w. j a v a 2s . c o m newRow = worksheet.createRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell HSSFCell oldCell = sourceRow.getCell(i); HSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell HSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } }