List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getBooleanCellValue
@Override public boolean getBooleanCellValue()
From source file:org.exoplatform.services.document.impl.MSExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xls file content. * /*from w w w . j a va2 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.StringCellMapper.java
License:Apache License
private String booleanToString(HSSFCell cell) { return cell.getBooleanCellValue() ? "VRAI" : "FAUX"; }
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 {/* w ww. j a v a2 s. co 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); } } }
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 {//from w w w .j a v a 2 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> * //from w ww . j a va 2 s . c o m * @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.XlsToHtml.java
/** * ??/*from w w w. j av a 2 s . com*/ * @param cell * @return */ private String getCellValue(HSSFCell cell) { String value = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { value = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { value = Double.toString(cell.getNumericCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { value = Boolean.toString(cell.getBooleanCellValue()); } else { value = ""; } if (value != null) { value = value.trim(); } else { value = ""; } return value; }
From source file:org.mcisb.excel.ExcelReader.java
License:Open Source License
/** * //from ww w .j a v a2s .c om * @param cell * @return Object */ private static Object getValue(HSSFCell cell) { if (cell == null) { return null; } switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: { return Boolean.valueOf(cell.getBooleanCellValue()); } case Cell.CELL_TYPE_NUMERIC: { return Double.valueOf(cell.getNumericCellValue()); } case Cell.CELL_TYPE_STRING: { return cell.getRichStringCellValue().getString().trim(); } default: { return null; } } }
From source file:org.ofbiz.tools.rest.FixOfcTools.java
/** * ?? /*from ww w . j a v a2 s . c o m*/ * @param cell * @return */ public static String convertCell(HSSFCell cell) { String cellValue = ""; if (cell == null) { return cellValue; } NumberFormat formater = NumberFormat.getInstance(); formater.setGroupingUsed(false); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: cellValue = formater.format(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: cellValue = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = Boolean.valueOf(cell.getBooleanCellValue()).toString(); break; case HSSFCell.CELL_TYPE_ERROR: cellValue = String.valueOf(cell.getErrorCellValue()); break; default: cellValue = ""; } return cellValue.replaceAll("\\s", "").trim(); }
From source file:org.opencms.search.extractors.CmsExtractorMsExcel.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * /* www .ja v a 2 s . c o m*/ * @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.opencrx.kernel.portal.wizard.ImportPropertiesFromXlsController.java
License:BSD License
/** * Create or update property./* w ww. j a v a2 s . c o m*/ * * @param prodConfTypeSet * @param productConfigurationTypeSetName * @param productConfigurationTypeSetDescription * @param prodConfType * @param productConfigurationTypeName * @param productConfigurationTypeDescription * @param crxObject * @param propSet * @param propertySetName * @param propertySetDescription * @param propertyType * @param propertyName * @param propertyDescription * @param propertyValue * @param productSegment * @return */ public Property createOrUpdatePropertyOfPropertySet(ProductConfigurationTypeSet prodConfTypeSet, String productConfigurationTypeSetName, String productConfigurationTypeSetDescription, ProductConfigurationType prodConfType, String productConfigurationTypeName, String productConfigurationTypeDescription, CrxObject crxObject, PropertySet propSet, String propertySetName, String propertySetDescription, String propertyType, String propertyName, String propertyDescription, HSSFCell propertyValue, org.opencrx.kernel.product1.jmi1.Segment productSegment, ApplicationContext app) { PersistenceManager pm = JDOHelper.getPersistenceManager(productSegment); ProductConfigurationTypeSet productConfigurationTypeSet = prodConfTypeSet; ProductConfigurationType productConfigurationType = prodConfType; PropertySet propertySet = propSet; Property property = null; if (prodConfTypeSet != null || productConfigurationTypeSetName != null || prodConfType != null || productConfigurationTypeName != null) { if (productConfigurationTypeSet == null && productConfigurationTypeSetName != null && !productConfigurationTypeSetName.isEmpty()) { // try to locate productConfigurationTypeSet with respective name (or create new productConfigurationTypeSet) ProductConfigurationTypeSetQuery productConfigurationTypeSetQuery = (ProductConfigurationTypeSetQuery) pm .newQuery(ProductConfigurationTypeSet.class); productConfigurationTypeSetQuery.name().equalTo(productConfigurationTypeSetName); try { pm.currentTransaction().begin(); Iterator<ProductConfigurationTypeSet> pcts = productSegment .getConfigurationTypeSet(productConfigurationTypeSetQuery).iterator(); if (pcts.hasNext()) { productConfigurationTypeSet = pcts.next(); } else { // create new ProductConfigurationTypeSet productConfigurationTypeSet = pm.newInstance(ProductConfigurationTypeSet.class); productConfigurationTypeSet.setName(productConfigurationTypeSetName); productSegment.addConfigurationTypeSet(getUidAsString(), productConfigurationTypeSet); } productConfigurationTypeSet.setDescription(productConfigurationTypeSetDescription); pm.currentTransaction().commit(); //System.out.println("productConfigurationTypeSet found/committed name=" + productConfigurationTypeSet.getName()); } catch (Exception e) { new ServiceException(e).log(); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } if (productConfigurationTypeSet != null && productConfigurationType == null && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { // try to locate productConfigurationType with respective name (or create new productConfigurationType) ProductConfigurationTypeQuery productConfigurationTypeFilter = (ProductConfigurationTypeQuery) pm .newQuery(ProductConfigurationType.class); productConfigurationTypeFilter.name().equalTo(productConfigurationTypeName); try { pm.currentTransaction().begin(); Iterator<ProductConfigurationType> pct = productConfigurationTypeSet .getConfigurationType(productConfigurationTypeFilter).iterator(); if (pct.hasNext()) { productConfigurationType = (ProductConfigurationType) pct.next(); } else { // create new ProductConfigurationType productConfigurationType = pm.newInstance(ProductConfigurationType.class); productConfigurationType.setName(productConfigurationTypeName); productConfigurationTypeSet.addConfigurationType(getUidAsString(), productConfigurationType); } productConfigurationType.setDescription(productConfigurationTypeDescription); pm.currentTransaction().commit(); //System.out.println("productConfigurationType found/committed name=" + productConfigurationTypeSet.getName()); } catch (Exception e) { new ServiceException(e).log(); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } } else if (crxObject != null) { // try to locate PropertySet with same parent and name (or create new PropertySet) PropertySetQuery propertySetFilter = (PropertySetQuery) pm.newQuery(PropertySet.class); propertySetFilter.name().equalTo(propertySetName); try { pm.currentTransaction().begin(); Iterator<PropertySet> ps = crxObject.getPropertySet(propertySetFilter).iterator(); if (ps.hasNext()) { propertySet = (PropertySet) ps.next(); } else { // create new PropertySet propertySet = pm.newInstance(PropertySet.class); propertySet.setName(propertySetName); crxObject.addPropertySet(getUidAsString(), propertySet); } propertySet.setDescription(propertySetDescription); pm.currentTransaction().commit(); } catch (Exception e) { new ServiceException(e).log(); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } if ((propertySet != null || productConfigurationType != null) && propertyType != null && !propertyType.isEmpty() && propertyName != null && !propertyName.isEmpty()) { // try to locate property with same parent and name (or create new property) PropertyQuery propertyQuery = (PropertyQuery) pm.newQuery(Property.class); propertyQuery.name().equalTo(propertyName); Iterator<Property> p = null; if (productConfigurationType != null) { p = productConfigurationType.getProperty(propertyQuery).iterator(); } else { p = propertySet.getProperty(propertyQuery).iterator(); } try { while (p.hasNext() && property == null) { property = p.next(); if (!((property instanceof StringProperty) && (propertyType.equals(PROPERTY_DTYPE_STRING)) || (property instanceof DecimalProperty) && (propertyType.equals(PROPERTY_DTYPE_DECIMAL)) || (property instanceof IntegerProperty) && (propertyType.equals(PROPERTY_DTYPE_INTEGER)) || (property instanceof BooleanProperty) && (propertyType.equals(PROPERTY_DTYPE_BOOLEAN)) || (property instanceof DateTimeProperty) && (propertyType.equals(PROPERTY_DTYPE_DATETIME)) || (property instanceof DateProperty) && (propertyType.equals(PROPERTY_DTYPE_DATE)) || (property instanceof ReferenceProperty) && (propertyType.equals(PROPERTY_DTYPE_REFERENCE)) || (property instanceof UriProperty) && (propertyType.equals(PROPERTY_DTYPE_URI)))) { property = null; } } pm.currentTransaction().begin(); if (propertyType.equals(PROPERTY_DTYPE_STRING)) { if (property == null) { // create new StringProperty property = pm.newInstance(StringProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((StringProperty) property).setStringValue( propertyValue != null ? propertyValue.getStringCellValue().trim() : null); } } else if (propertyType.equals(PROPERTY_DTYPE_DECIMAL)) { if (property == null) { // create new DecimalProperty property = pm.newInstance(DecimalProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((DecimalProperty) property).setDecimalValue( propertyValue != null ? new BigDecimal(propertyValue.getNumericCellValue()) : null); } } else if (propertyType.equals(PROPERTY_DTYPE_INTEGER)) { if (property == null) { // create new IntegerProperty property = pm.newInstance(IntegerProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((IntegerProperty) property).setIntegerValue(propertyValue != null ? (new BigDecimal(propertyValue.getNumericCellValue())).intValue() : null); } } else if (propertyType.equals(PROPERTY_DTYPE_BOOLEAN)) { if (property == null) { // create new BooleanProperty property = pm.newInstance(BooleanProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((BooleanProperty) property).setBooleanValue( propertyValue != null ? propertyValue.getBooleanCellValue() : null); } } else if (propertyType.equals(PROPERTY_DTYPE_DATETIME)) { if (property == null) { // create new DateTimeProperty property = pm.newInstance(DateTimeProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((DateTimeProperty) property).setDateTimeValue(propertyValue != null ? HSSFDateUtil.getJavaDate(propertyValue.getNumericCellValue()) : null); } } else if (propertyType.equals(PROPERTY_DTYPE_DATE)) { if (property == null) { // create new DateTimeProperty property = pm.newInstance(DateProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); if (propertyValue != null) { TimeZone timezone = TimeZone.getTimeZone(app.getCurrentTimeZone()); SimpleDateFormat dateonlyf = new SimpleDateFormat("yyyyMMdd", app.getCurrentLocale()); dateonlyf.setTimeZone(timezone); String date = dateonlyf .format(HSSFDateUtil.getJavaDate(propertyValue.getNumericCellValue())) .substring(0, 8); XMLGregorianCalendar cal = org.w3c.spi2.Datatypes.create(XMLGregorianCalendar.class, date); ((DateProperty) property).setDateValue(cal); } else { ((DateProperty) property).setDateValue(null); } } } else if (propertyType.equals(PROPERTY_DTYPE_REFERENCE)) { if (property == null) { // create new ReferenceProperty property = pm.newInstance(ReferenceProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); BasicObject basicObject = null; if (propertyValue != null) { try { String xri = propertyValue.getStringCellValue().trim(); basicObject = (BasicObject) pm.getObjectById(new Path(xri)); } catch (Exception e) { } } ((ReferenceProperty) property).setReferenceValue(basicObject); } } else if (propertyType.equals(PROPERTY_DTYPE_URI)) { if (property == null) { // create new UriProperty property = pm.newInstance(UriProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((UriProperty) property).setUriValue( propertyValue != null ? propertyValue.getStringCellValue().trim() : null); } } pm.currentTransaction().commit(); } catch (Exception e) { new ServiceException(e).log(); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } return property; }