List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
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 w 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); } } }
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 www .j ava2 s .co 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.hsh.bfr.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
public void mergeIDs() { System.err.println("Merging..."); try {//from w ww. ja va 2 s. com //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\mergeList.xls"); FileInputStream is = new FileInputStream(DBKernel.HSHDB_PATH + "mergeList.xls"); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet mergeSheet = wb.getSheet("mergeList"); int numRows = mergeSheet.getLastRowNum() + 1; for (int i = 1; i < numRows; i++) { try { HSSFRow row = mergeSheet.getRow(i); if (row != null) { HSSFCell cell = row.getCell(0); if (cell != null) { Integer oldEfsaID = (int) cell.getNumericCellValue(); if (cell != null) { cell = row.getCell(1); Integer newEfsaID = (int) cell.getNumericCellValue(); DBKernel.mergeIDs(DBKernel.getDBConnection(), "Station", oldEfsaID, newEfsaID); } } } } catch (Exception e) { System.err.println(e.getMessage() + "\t" + i); } } } catch (Exception e) { } System.err.println("Merging...Fin!"); }
From source file:org.hsh.bfr.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private HashMap<String, Integer> loadNodeIDs10000() { System.err.println("loadNodeIDs10000..."); nodeIds = new HashMap<String, Integer>(); try {/* w w w . j a v a 2 s. co m*/ //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\nodesids10000.xls"); FileInputStream is = new FileInputStream(DBKernel.HSHDB_PATH + "nodesids10000.xls"); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet defaultSheet = wb.getSheet("default"); int numRows = defaultSheet.getLastRowNum() + 1; for (int i = 1; i < numRows; i++) { try { HSSFRow row = defaultSheet.getRow(i); if (row != null) { HSSFCell cell = row.getCell(0); Integer id = (int) cell.getNumericCellValue(); if (id > maxNodeID) maxNodeID = id; cell = row.getCell(1); String name = cell.getStringCellValue(); nodeIds.put(name, id); } } catch (Exception e) { System.err.println(e.getMessage() + "\t" + i); } } } catch (Exception e) { } System.err.println("loadNodeIDs10000...Fin!"); return nodeIds; }
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 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.jtotus.database.FileSystemFromHex.java
License:Open Source License
public BigDecimal omxNordicFile(String fileName, DateTime calendar, int row) { BigDecimal result = null;/*from w w w.j a v a 2 s. co m*/ try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathToDataBaseDir + fileName)); HSSFWorkbook workbook = new HSSFWorkbook(fs); HSSFSheet worksheet = workbook.getSheetAt(0); //HSSFRow row1 = worksheet.getRow(0); String correctTime = dateFormatter.print(calendar); Iterator rowIter = worksheet.rowIterator(); while (rowIter.hasNext()) { HSSFRow rows = (HSSFRow) rowIter.next(); HSSFCell cell = rows.getCell(0); String dateString = null; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { dateString = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { Date date = cell.getDateCellValue(); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); dateString = format.format(date); // System.err.printf("File (%s) is corrucped ? type:%s\n", fileName, dateString); } else { System.err.printf("File (%s) is corrucped ? type:%d formula:%d\n", fileName, cell.getCellType(), Cell.CELL_TYPE_FORMULA); return null; } // help.debug("FileSystemFromHex","Searching:%s from:%s\n", correctTime, temp); if (correctTime.compareTo(dateString) == 0) { HSSFCell closingPrice = rows.getCell(row); if (closingPrice == null) return null; float floatTemp = (float) closingPrice.getNumericCellValue(); System.out.printf("FileSystemFromHex", "Closing price at:%d f:%.4f Time:%s\n", cell.getRowIndex(), floatTemp, correctTime); return new BigDecimal(floatTemp); } } } catch (IOException ex) { Logger.getLogger(FileSystemFromHex.class.getName()).log(Level.SEVERE, null, ex); } return result; }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * //from ww w . j av 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.XlsToHtml.java
/** * ??//from www .j a v a 2s . c o m * @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.lamsfoundation.lams.admin.service.ImportService.java
License:Open Source License
private boolean parseBooleanCell(HSSFCell cell) { if (cell != null) { String value;/* w ww .j a va 2s .c om*/ try { cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (cell.getStringCellValue() != null) { if (cell.getStringCellValue().trim().length() != 0) { emptyRow = false; } } else { return false; } value = cell.getStringCellValue().trim(); } catch (Exception e) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); double d = cell.getNumericCellValue(); emptyRow = false; value = new Long(new Double(d).longValue()).toString(); } if (StringUtils.equals(value, "1") || StringUtils.equalsIgnoreCase(value, "true")) { return true; } } return false; }
From source file:org.lamsfoundation.lams.admin.service.ImportService.java
License:Open Source License
private String parseStringCell(HSSFCell cell) { if (cell != null) { try {/*w w w. ja va2 s .co m*/ cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (cell.getStringCellValue() != null) { if (cell.getStringCellValue().trim().length() != 0) { emptyRow = false; } } else { return null; } // log.debug("string cell value: '"+cell.getStringCellValue().trim()+"'"); return cell.getStringCellValue().trim(); } catch (Exception e) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); double d = cell.getNumericCellValue(); emptyRow = false; // log.debug("numeric cell value: '"+d+"'"); return (new Long(new Double(d).longValue()).toString()); } } return null; }