List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getPhysicalNumberOfRows
@Override public int getPhysicalNumberOfRows()
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
public void importExcel() { String name = SecurityContextHolder.getContext().getAuthentication().getName(); log.debug("Start import..."); String fileToBeRead = "/home/hieu/DKTC-2011_Hieu.xls"; try {/* www . ja va2 s .c o m*/ HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead)); HSSFSheet sheet = workbook.getSheetAt(1); Vaccination vvgb = vaccinationDao.get((long) 1); Vaccination vbcg = vaccinationDao.get((long) 2); Vaccination vdpt1 = vaccinationDao.get((long) 3); Vaccination vopv1 = vaccinationDao.get((long) 4); Vaccination vdpt2 = vaccinationDao.get((long) 5); Vaccination vopv2 = vaccinationDao.get((long) 6); Vaccination vdpt3 = vaccinationDao.get((long) 7); Vaccination vopv3 = vaccinationDao.get((long) 8); Vaccination vmeasles = vaccinationDao.get((long) 9); SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); for (int r = 1; r < sheet.getPhysicalNumberOfRows(); r++) { log.debug("--------------------------------------" + r); HSSFRow row = sheet.getRow(r); Children child = new Children(); Date bcg = null; Date opv1 = null; Date opv2 = null; Date opv3 = null; Date dpt1 = null; Date dpt2 = null; Date dpt3 = null; Date measles = null; Date helpb1 = null; Boolean helpb1ontime = null; Date finishedDate = null; String villageName = ""; long communeId = 0; for (int c = 0; c < 20; c++) { HSSFCell cell = row.getCell(c); // Type the content Date tmpDate = null; int tmpInt = 0; String tmpStr = ""; Boolean tmpboolean = null; if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(cell)) { tmpDate = cell.getDateCellValue(); } else tmpInt = (int) cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { tmpStr = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { tmpInt = (int) cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { tmpboolean = cell.getBooleanCellValue(); } } if (c == 0) { communeId = (long) tmpInt; } else if (c == 1) { child.setFullName(tmpStr); } else if (c == 2) { if (tmpInt == 2) child.setGender(true); else child.setGender(false); } else if (c == 3) { // if (cell==null) // child.setDateOfBirth(null); // else // child.setDateOfBirth(tmpDate); if (tmpStr == null || tmpStr.equalsIgnoreCase("")) child.setDateOfBirth(null); else { child.setDateOfBirth(format.parse(tmpStr)); } } else if (c == 4) { child.setFatherName(tmpStr); } else if (c == 5) { child.setMotherName(tmpStr); } else if (c == 6) { //village villageName = tmpStr; } else if (c == 7) { // if (cell != null) // bcg = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) bcg = null; else { bcg = format.parse(tmpStr); } } else if (c == 8) { // if (cell != null) // opv1 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) opv1 = null; else { opv1 = format.parse(tmpStr); } } else if (c == 9) { // if (cell != null) // opv2 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) opv2 = null; else { opv2 = format.parse(tmpStr); } } else if (c == 10) { // if (cell != null) // opv3 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) opv3 = null; else { opv3 = format.parse(tmpStr); } } else if (c == 11) { // if (cell != null) // dpt1 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) dpt1 = null; else { dpt1 = format.parse(tmpStr); } } else if (c == 12) { // if (cell != null) // dpt2 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) dpt2 = null; else { dpt2 = format.parse(tmpStr); } } else if (c == 13) { // if (cell != null) // dpt3 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) dpt3 = null; else { dpt3 = format.parse(tmpStr); } } else if (c == 14) { // if (cell != null) // measles = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) measles = null; else { measles = format.parse(tmpStr); } } else if (c == 15) { // if (cell != null) { // helpb1 = tmpDate; // helpb1ontime = true; // } if (tmpStr == null || tmpStr.equalsIgnoreCase("")) helpb1 = null; else { helpb1 = format.parse(tmpStr); helpb1ontime = true; } } else if (c == 16 && helpb1 == null) { // if (cell != null) { // helpb1 = tmpDate; // helpb1ontime = false; // } if (tmpStr == null || tmpStr.equalsIgnoreCase("")) helpb1 = null; else { helpb1 = format.parse(tmpStr); helpb1ontime = false; } } else if (c == 19) { // if (cell != null) // finishedDate = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) finishedDate = null; else { finishedDate = format.parse(tmpStr); } } } if (child.getDateOfBirth() != null) { log.debug(child.getFullName() + " - " + child.getDateOfBirth() + " - " + child.isGender()); Commune commune = communeDao.get(communeId); if (villageName.equalsIgnoreCase("")) child.setVillage(villageDao.findByCommune(commune).get(0)); else { List<Village> vl = villageDaoExt.findByCommuneIdAndVillageName(communeId, villageName); if (vl != null && vl.size() > 0) child.setVillage(vl.get(0)); else { child.setVillage(villageDao.findByCommune(commune).get(0)); child.setAddress(villageName); } } child.setCurrentCaretaker((short) 0); child.setCreationDate(new Date()); child.setAuthor(name); child.setBarcodeDate(null); child.setLocked(false); if (finishedDate != null) { child.setFinishedDate(finishedDate); } else if (bcg != null && dpt1 != null && dpt2 != null && dpt3 != null && opv1 != null && opv2 != null && opv3 != null && measles != null) { Date maxDate = dpt3.getTime() > opv3.getTime() ? dpt3 : opv3; maxDate = maxDate.getTime() > measles.getTime() ? maxDate : measles; child.setFinishedDate(maxDate); } else child.setFinishedDate(null); int year = child.getDateOfBirth().getYear() + 1900; if (child.getFullName() != null && !child.getFullName().equalsIgnoreCase("")) child.setFirstName(child.getFullName().substring(child.getFullName().lastIndexOf(" ") + 1)); if (child.getMotherName() != null && !child.getMotherName().equalsIgnoreCase("")) child.setMotherFirstName( child.getMotherName().substring(child.getMotherName().lastIndexOf(" ") + 1)); child.setChildCode(commune.getDistrict().getProvince().getProvinceId() + commune.getDistrict().getDistrictId() + commune.getCommuneId() + "-" + year); child = childrenDao.save(child); String code = childrenDaoExt.generateChildCode(child); child.setChildCode(code); log.debug("child code: " + code); child = childrenDao.save(child); ChildrenVaccinationHistory newVH = new ChildrenVaccinationHistory(); if (helpb1 != null) { log.debug("VGB " + helpb1); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); if (helpb1ontime != null) newVH.setOverdue(helpb1ontime); newVH.setVaccinated((short) 1); newVH.setVaccination(vvgb); newVH.setDateOfImmunization(helpb1); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vvgb); childrenVaccinationHistoryDao.save(newVH); } if (bcg != null) { log.debug("BCG " + bcg); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vbcg); newVH.setDateOfImmunization(bcg); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vbcg); childrenVaccinationHistoryDao.save(newVH); } if (dpt1 != null) { log.debug("DPT1 " + dpt1); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vdpt1); newVH.setDateOfImmunization(dpt1); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vdpt1); childrenVaccinationHistoryDao.save(newVH); } if (opv1 != null) { log.debug("OPV1 " + opv1); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vopv1); newVH.setDateOfImmunization(opv1); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vopv1); childrenVaccinationHistoryDao.save(newVH); } if (dpt2 != null) { log.debug("DPT2 " + dpt2); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vdpt2); newVH.setDateOfImmunization(dpt2); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vdpt2); childrenVaccinationHistoryDao.save(newVH); } if (opv2 != null) { log.debug("OPV2 " + opv2); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vopv2); newVH.setDateOfImmunization(opv2); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vopv2); childrenVaccinationHistoryDao.save(newVH); } if (dpt3 != null) { log.debug("DPT3 " + dpt3); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vdpt3); newVH.setDateOfImmunization(dpt3); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vdpt3); childrenVaccinationHistoryDao.save(newVH); } if (opv3 != null) { log.debug("OPV3 " + opv1); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vopv3); newVH.setDateOfImmunization(opv3); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vopv3); childrenVaccinationHistoryDao.save(newVH); } if (measles != null) { log.debug("Measles " + measles); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vmeasles); newVH.setDateOfImmunization(measles); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vmeasles); childrenVaccinationHistoryDao.save(newVH); } log.debug("--------------------------------------"); } } } catch (Exception e) { } }
From source file:org.jlibrary.core.search.extraction.ExcelExtractor.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * /* w w w . j av a2 s . c om*/ * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuilder result = new StringBuilder(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if ((excelWb.getSheetName(i) != null) && !excelWb.getSheetName(i).equals("")) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if ((text != null) && !text.equals("")) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * // w w w . jav a 2 s . c o m * @param mainSheet -- ? * @param subSheet -- ? * @param tempRow -- ????? * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet, int tempRow) { if (mainSheet == null || subSheet == null) return null; //?? if (!isAllowOut(mainSheet)) return mainSheet; //? int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; //? copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); //?? CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; //int k = 0; //? mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext();) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); /** * ??????orgcell = mainSheet.getRow(row).getCell(column); * ?? * ??orgcell.getCellStyle()????sheet?? * This Style does not belong to the supplied Workbook. * ?descell.getCellStyle().cloneStyleFrom(sourcell.getCellStyle());???excel * HSSFCellStyle cs = mainSheet.getWorkbook().createCellStyle(); * cs.cloneStyleFrom(sourcell.getCellStyle()); * descell.setCellStyle(cs);//excel? * tempRow???? */ //???????? int row = sourcell.getRowIndex(); if (tempRow > 0 && row > tempRow) { row = tempRow; } orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { //orgcell.getCellType()???0 descell.setCellType(HSSFCell.CELL_TYPE_STRING); //??? descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ???1SHEET1SHEET/*from w ww . ja va 2 s .c om*/ * * @param destBook -- * @param srcBook -- ?? */ private static void copySheetImage(HSSFWorkbook destBook, HSSFWorkbook srcBook) { //??? HSSFSheet srcSheet = srcBook.getSheetAt(0); //? HSSFSheet destSheet = destBook.getSheetAt(0); //??? int endRowNum = destSheet.getPhysicalNumberOfRows(); //???? List<HSSFPicture> lsSrcPicture = getAllPicture(srcSheet); _log.showDebug("----------source picture size:" + lsSrcPicture.size()); if (lsSrcPicture.isEmpty()) return; //????? List<HSSFPictureData> lsPicData = null; try { lsPicData = srcBook.getAllPictures(); } catch (Exception e) { _log.showWarn( "book?getAllPictures?book??"); HSSFWorkbook tmpBook = copyWorkbook(srcBook); if (tmpBook != null) { lsPicData = tmpBook.getAllPictures(); tmpBook = null; } /* ???? //??? lsPicData = destBook.getAllPictures(); if (lsPicData == null || lsPicData.isEmpty()) return; //??1? List<HSSFPictureData> destData = FactoryUtil.newList(); for (int i = 0, n = lsSrcPicture.size(); i < n; i++) { destData.add(lsPicData.get(0)); } lsPicData = destData;*/ } if (lsPicData == null || lsPicData.isEmpty()) return; _log.showDebug("----------source data size:" + lsPicData.size()); //???? //????sheet???book if (lsSrcPicture.size() > lsPicData.size()) { _log.showWarn("?????"); return; } //?? HSSFPatriarch destDraw = destSheet.getDrawingPatriarch(); if (destDraw == null) { destDraw = destSheet.createDrawingPatriarch(); } //?? List<HSSFPicture> lsDestPicture = getAllPicture(destSheet); int index = lsDestPicture.size(); for (int i = 0, n = lsSrcPicture.size(); i < n; i++) { //? HSSFPicture picture = lsSrcPicture.get(i); //????? HSSFPictureData picdata = lsPicData.get(i); //?? byte[] datas = picdata.getData(); //??? HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor(); //?? anchor.setRow1(anchor.getRow1() + endRowNum); anchor.setRow2(anchor.getRow2() + endRowNum); //??? destBook.addPicture(datas, picdata.getFormat()); //???????+1?? index++; _log.showDebug("---------copy new image index=" + index); destDraw.createPicture(anchor, index); } }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ??/*from ww w .j a va 2 s . c o m*/ * * @param sheet -- * @return */ public static boolean isAllowOut(HSSFSheet sheet) { boolean ret = true; String maxXlsNum = SystemVar.getValue("report.xls.num", "50000"); if (sheet.getPhysicalNumberOfRows() > Integer.parseInt(maxXlsNum)) { _log.showWarn("EXCEL{0}?", maxXlsNum); ret = false; } return ret; }
From source file:org.opencms.search.extractors.CmsExtractorMsExcel.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * // w w w . j av a 2s . c om * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuffer result = new StringBuffer(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if (CmsStringUtil.isNotEmpty(excelWb.getSheetName(i))) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if ((text != null) && (text.length() != 0)) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }
From source file:org.openelis.bean.WorksheetExcelHelperBean.java
License:Open Source License
@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED) public WorksheetManager1 exportToExcel(WorksheetManager1 manager) throws Exception { boolean isEditable; int r, i, a, o, aCount, aTotal; ArrayList<WorksheetAnalysisViewDO> waList; ArrayList<WorksheetResultViewDO> wrList; ArrayList<WorksheetQcResultViewDO> wqrList; String cellNameIndex, posNum, outFileName; File outFile;/*from w w w . j a v a 2 s.c o m*/ FileInputStream in; FileOutputStream out; HashMap<Integer, ArrayList<WorksheetAnalysisViewDO>> waMap; HashMap<Integer, ArrayList<WorksheetResultViewDO>> wrMap; HashMap<Integer, ArrayList<WorksheetQcResultViewDO>> wqrMap; HashMap<Integer, WorksheetAnalysisViewDO> waLinkMap; HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>> apMap; HashMap<String, String> tCellNames; Cell cell; CellRangeAddressList /*statusCells, */ reportableColumn; DVConstraint /*statusConstraint, */ reportableConstraint; HSSFDataValidation /*statusValidation, */ reportableValidation; HSSFSheet resultSheet, overrideSheet; HSSFWorkbook wb; Name cellName; Row row, oRow, tRow; DictionaryDO formatDO; ReportStatus status; SimpleDateFormat dateTimeFormat; WorksheetAnalysisDO waLinkDO; status = new ReportStatus(); status.setMessage("Exporting to Excel: Initializing"); status.setPercentComplete(0); session.setAttribute("ExportToExcelStatus", status); dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm"); outFileName = getWorksheetOutputFileName(manager.getWorksheet().getId(), manager.getWorksheet().getSystemUserId()); outFile = new File(outFileName); if (outFile.exists()) throw new Exception( "An Excel file for this worksheet already exists, please delete it before trying to export"); try { formatDO = dictionaryCache.getById(manager.getWorksheet().getFormatId()); } catch (NotFoundException nfE) { formatDO = new DictionaryDO(); formatDO.setEntry("DefaultTotal"); formatDO.setSystemName("wf_total"); } catch (Exception anyE) { throw new Exception("Error retrieving worksheet format: " + anyE.getMessage()); } try { in = new FileInputStream(getWorksheetTemplateFileName(formatDO)); wb = new HSSFWorkbook(in, true); createStyles(wb); } catch (FileNotFoundException fnfE) { try { wb = buildTemplate(formatDO); createStyles(wb); } catch (FileNotFoundException fnfE2) { throw new Exception("Error loading template file: " + fnfE2.getMessage()); } catch (IOException ioE) { throw new Exception("Error loading workbook from template file: " + ioE.getMessage()); } } loadStatuses(); // statusCells = new CellRangeAddressList(); tCellNames = loadNamesByCellReference(wb); resultSheet = wb.getSheet("Worksheet"); tRow = resultSheet.getRow(1); resultSheet.removeRow(tRow); overrideSheet = wb.getSheet("Overrides"); status.setPercentComplete(5); session.setAttribute("ExportToExcelStatus", status); r = 1; o = 1; i = 0; aCount = 0; apMap = new HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>>(); waMap = new HashMap<Integer, ArrayList<WorksheetAnalysisViewDO>>(); waLinkMap = new HashMap<Integer, WorksheetAnalysisViewDO>(); wrMap = new HashMap<Integer, ArrayList<WorksheetResultViewDO>>(); wqrMap = new HashMap<Integer, ArrayList<WorksheetQcResultViewDO>>(); loadMaps(manager, waMap, waLinkMap, wrMap, wqrMap); aTotal = getAnalyses(manager).size(); if (getItems(manager) != null) { for (WorksheetItemDO wiDO : getItems(manager)) { a = 0; waList = waMap.get(wiDO.getId()); if (waList != null && waList.size() > 0) { for (WorksheetAnalysisViewDO waVDO : waList) { aCount++; status.setMessage("Exporting to Excel: Analysis " + aCount + " of " + aTotal); status.setPercentComplete((int) (((double) (aCount - 1) / aTotal) * 90) + 5); session.setAttribute("ExportToExcelStatus", status); waLinkDO = waLinkMap.get(waVDO.getWorksheetAnalysisId()); row = resultSheet.createRow(r); // position number posNum = wiDO.getPosition().toString(); cell = row.createCell(0); cell.setCellStyle(styles.get("row_no_edit")); if (a == 0) cell.setCellValue(posNum); // accession number cell = row.createCell(1); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getAccessionNumber()); cellNameIndex = i + "." + a; if (waVDO.getAnalysisId() != null) { isEditable = (waVDO.getFromOtherId() == null && !Constants.dictionary().ANALYSIS_INPREP.equals(waVDO.getStatusId()) && !Constants.dictionary().ANALYSIS_RELEASED.equals(waVDO.getStatusId()) && !Constants.dictionary().ANALYSIS_CANCELLED.equals(waVDO.getStatusId())); // description cell = row.createCell(2); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getDescription()); // qc link cell = row.createCell(3); cell.setCellStyle(styles.get("row_no_edit")); if (waLinkDO != null) cell.setCellValue(waLinkDO.getAccessionNumber()); else cell.setCellValue(""); // test name cell = row.createCell(4); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getTestName()); // method name cell = row.createCell(5); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getMethodName()); // analysis status cell = row.createCell(6); // if (isEditable) // cell.setCellStyle(styles.get("row_edit")); // else cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(statusIdNameMap.get(waVDO.getStatusId())); cellName = wb.createName(); cellName.setNameName("analysis_status." + i + "." + a); cellName.setRefersToFormula("Worksheet!$" + CellReference.convertNumToColString(6) + "$" + (row.getRowNum() + 1)); // statusCells.addCellRangeAddress(r, 6, r, 6); wrList = wrMap.get(waVDO.getId()); if (wrList == null || wrList.size() == 0) { // analyte cell = row.createCell(7); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue("NO ANALYTES DEFINED"); // reportable cell = row.createCell(8); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue("N"); createEmptyCellsForFormat(row, tRow); r++; } else { r = createResultCellsForFormat(resultSheet, row, tRow, cellNameIndex, tCellNames, manager.getWorksheet(), waVDO, wrList, isEditable, apMap); } // // Add override row to override sheet // oRow = overrideSheet.createRow(o); // position number cell = oRow.createCell(0); cell.setCellStyle(styles.get("row_no_edit")); if (a == 0) cell.setCellValue(posNum); // accession number cell = oRow.createCell(1); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getAccessionNumber()); // description (override) cell = oRow.createCell(2); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getDescription()); // test name (overrride) cell = oRow.createCell(3); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getTestName()); // method name (override) cell = oRow.createCell(4); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getMethodName()); // users (override) cell = oRow.createCell(5); if (isEditable) cell.setCellStyle(styles.get("row_edit")); else cell.setCellStyle(styles.get("row_no_edit")); if (waVDO.getSystemUsers() != null) cell.setCellValue(waVDO.getSystemUsers()); cellName = wb.createName(); cellName.setNameName("analysis_users." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(5) + "$" + (oRow.getRowNum() + 1)); // started (override) cell = oRow.createCell(6); if (isEditable) cell.setCellStyle(styles.get("datetime_edit")); else cell.setCellStyle(styles.get("datetime_no_edit")); if (waVDO.getStartedDate() != null) cell.setCellValue(dateTimeFormat.format(waVDO.getStartedDate().getDate())); cellName = wb.createName(); cellName.setNameName("analysis_started." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(6) + "$" + (oRow.getRowNum() + 1)); // completed (override) cell = oRow.createCell(7); if (isEditable) cell.setCellStyle(styles.get("datetime_edit")); else cell.setCellStyle(styles.get("datetime_no_edit")); if (waVDO.getCompletedDate() != null) cell.setCellValue(dateTimeFormat.format(waVDO.getCompletedDate().getDate())); cellName = wb.createName(); cellName.setNameName("analysis_completed." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(7) + "$" + (oRow.getRowNum() + 1)); o++; } else if (waVDO.getQcLotId() != null) { // description cell = row.createCell(2); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getDescription()); // qc link cell = row.createCell(3); cell.setCellStyle(styles.get("row_no_edit")); if (waLinkDO != null) cell.setCellValue(waLinkDO.getAccessionNumber()); else cell.setCellValue(""); // test name cell = row.createCell(4); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(""); // method name cell = row.createCell(5); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(""); // analysis status cell = row.createCell(6); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(""); wqrList = wqrMap.get(waVDO.getId()); if (wqrList == null || wqrList.size() == 0) { // analyte cell = row.createCell(7); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue("NO ANALYTES DEFINED"); // reportable cell = row.createCell(8); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue("N"); createEmptyCellsForFormat(row, tRow); r++; } else { cellNameIndex = i + "." + a; r = createQcResultCellsForFormat(resultSheet, row, tRow, cellNameIndex, tCellNames, manager.getWorksheet(), waVDO.getQcId(), wqrList, apMap); } // // Add override row to override sheet // oRow = overrideSheet.createRow(o); // position number cell = oRow.createCell(0); cell.setCellStyle(styles.get("row_no_edit")); if (a == 0) cell.setCellValue(posNum); // accession number cell = oRow.createCell(1); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getAccessionNumber()); // description (override) cell = oRow.createCell(2); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getDescription()); // test name (overrride) cell = oRow.createCell(3); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(""); // method name (override) cell = oRow.createCell(4); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(""); // users (override) cell = oRow.createCell(5); cell.setCellStyle(styles.get("row_edit")); if (waVDO.getSystemUsers() != null) cell.setCellValue(waVDO.getSystemUsers()); cellName = wb.createName(); cellName.setNameName("analysis_users." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(5) + "$" + (oRow.getRowNum() + 1)); // started (override) cell = oRow.createCell(6); cell.setCellStyle(styles.get("datetime_edit")); if (waVDO.getStartedDate() != null) cell.setCellValue(dateTimeFormat.format(waVDO.getStartedDate().getDate())); cellName = wb.createName(); cellName.setNameName("analysis_started." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(6) + "$" + (oRow.getRowNum() + 1)); // completed (override) cell = oRow.createCell(7); cell.setCellStyle(styles.get("datetime_no_edit")); cellName = wb.createName(); cellName.setNameName("analysis_completed." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(7) + "$" + (oRow.getRowNum() + 1)); o++; } a++; } } i++; } } status.setMessage("Exporting to Excel: Finalizing"); status.setPercentComplete(95); session.setAttribute("ExportToExcelStatus", status); // // Create validators // // statusConstraint = DVConstraint.createExplicitListConstraint(statuses); // statusValidation = new HSSFDataValidation(statusCells, statusConstraint); // statusValidation.setEmptyCellAllowed(true); // statusValidation.setSuppressDropDownArrow(false); // statusValidation.createPromptBox("Statuses", formatTooltip(statuses)); // statusValidation.setShowPromptBox(false); // resultSheet.addValidationData(statusValidation); reportableColumn = new CellRangeAddressList(1, resultSheet.getPhysicalNumberOfRows() - 1, 8, 8); reportableConstraint = DVConstraint.createExplicitListConstraint(new String[] { "Y", "N" }); reportableValidation = new HSSFDataValidation(reportableColumn, reportableConstraint); reportableValidation.setSuppressDropDownArrow(false); resultSheet.addValidationData(reportableValidation); // // Auto resize columns on result sheet and override sheet // resultSheet.autoSizeColumn(2, true); // Description resultSheet.autoSizeColumn(4, true); // Test resultSheet.autoSizeColumn(5, true); // Method resultSheet.autoSizeColumn(7, true); // Analyte overrideSheet.autoSizeColumn(2, true); // Description overrideSheet.autoSizeColumn(3, true); // Test overrideSheet.autoSizeColumn(4, true); // Method try { out = new FileOutputStream(outFileName); wb.write(out); out.close(); Runtime.getRuntime().exec("chmod go+rw " + outFileName); } catch (Exception anyE) { throw new Exception("Error writing Excel file: " + anyE.getMessage()); } status.setMessage("Exporting to Excel: Done"); status.setPercentComplete(100); session.setAttribute("ExportToExcelStatus", status); return manager; }
From source file:org.riotfamily.dbmsgsrc.riot.ImportMessageEntriesCommand.java
License:Apache License
private void updateMessages(byte[] data, boolean addNewMessages) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(data)); HSSFSheet sheet = wb.getSheet("Translations"); if (isValid(sheet)) { for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { HSSFRow row = sheet.getRow(i); if (row.getCell(1) != null && row.getCell(2) != null) { String code = row.getCell(1).getRichStringCellValue().getString(); String defaultMessage = row.getCell(2).getRichStringCellValue().getString(); String comment = null; if (row.getCell(3) != null) { comment = row.getCell(3).getRichStringCellValue().getString(); }/*from www. j a v a 2s.c om*/ if (StringUtils.hasText(defaultMessage) || StringUtils.hasText(comment)) { MessageBundleEntry entry = MessageBundleEntry.loadByBundleAndCode(bundle, code); if (entry != null) { entry.getDefaultMessage().setText(defaultMessage); entry.setComment(comment); entry.save(); } else if (addNewMessages) { entry = new MessageBundleEntry(bundle, code, defaultMessage); entry.setComment(comment); entry.save(); } else { log.info("Message Code does not exist and creation not allowed - " + code); } } } else { log.info("Skipping invalid row {}", i); } } } }
From source file:org.riotfamily.dbmsgsrc.riot.ImportMessagesCommand.java
License:Apache License
private void updateMessages(byte[] data, Site site) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(data)); HSSFSheet sheet = wb.getSheet("Translations"); if (isValid(sheet)) { for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { HSSFRow row = sheet.getRow(i); if (row.getCell(1) != null && row.getCell(3) != null) { String code = row.getCell(1).getRichStringCellValue().getString(); String translation = row.getCell(3).getRichStringCellValue().getString(); if (StringUtils.hasText(translation)) { MessageBundleEntry entry = MessageBundleEntry.loadByBundleAndCode(bundle, code); if (entry != null) { entry.addTranslation(site.getLocale(), translation); entry.save();/*from w w w. j ava2s .c o m*/ } else { log.info("Message Code does not exist - " + code); } } } else { log.info("Skipping invalid row {}", i); } } } }
From source file:org.rti.zcore.dar.utils.PoiUtils.java
License:Apache License
/** * This utility is a version of HSSF.main that does not use deprecated methods. * It is helpful in figuring out what row a filed is on when outputting Excel files via POI. * @param pathExcelMaster/*w w w . j av a 2 s. c o m*/ */ public static void testExcelOutput(String pathExcelMaster) { try { //HSSF hssf = new HSSF(args[ 0 ]); System.out.println("Data dump:\n"); //HSSFWorkbook wb = hssf.hssfworkbook; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathExcelMaster)); HSSFWorkbook wb = new HSSFWorkbook(fs); for (int k = 0; k < wb.getNumberOfSheets(); k++) { System.out.println("Sheet " + k); HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { //HSSFRow row = sheet.getPhysicalRowAt(r); HSSFRow row = sheet.getRow(r); if (row != null) { int cells = row.getPhysicalNumberOfCells(); System.out.println("ROW " + row.getRowNum()); for (int c = 0; c < cells; c++) { //HSSFCell cell = row.getPhysicalCellAt(c); HSSFCell cell = row.getCell(c); String value = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA "; value = "FORMULA " + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: //value = "STRING value=" + cell.getStringCellValue(); HSSFRichTextString str = cell.getRichStringCellValue(); value = "STRING value=" + str; break; default: } //System.out.println("CELL col=" + cell.getCellNum() + " VALUE=" + value); System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } } } catch (Exception e) { e.printStackTrace(); } }