List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:com.ibm.ioes.actions.NewOrderAction.java
public ActionForward uploadPlanExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {//from w w w .ja va 2s . c om NewOrderBean formBean = (NewOrderBean) form; ActionMessages messages = new ActionMessages(); ActionForward forward = null; boolean validation_error = false; FormFile uploadedFile = formBean.getUploadedFile(); if (uploadedFile == null || "".equals(uploadedFile.getFileName())) { messages.add("", new ActionMessage("uploadPlan.error.attachment.notSelected")); validation_error = true; } else if (!(uploadedFile.getFileName().substring(uploadedFile.getFileName().lastIndexOf(".") + 1)) .equalsIgnoreCase("xls")) { messages.add("", new ActionMessage("uploadPlan.error.attachment.notXls")); validation_error = true; } /*else if(uploadedFile.getFileSize()>SessionObjectsDto.getInstance().getAttachmentSize()) { messages.add("attachment",new ActionMessage("error.attachment.size")); validation_error = true; }*/ if (validation_error) { saveMessages(request, messages); //TODO : forward = mapping.findForward("DisplayUploadPage"); /*uploadPlanExcelInitPage(mapping, form, request, response); forwardMapping="uploadPlanExcel"; return mapping.findForward(forwardMapping);*/ } int isValid = 1; int status = 0; ExcelValidator validator = new ExcelValidator(); String templateFilePath = AppConstants.UploadPrdCatelogExcelTemplateFilePath; try { //ArrayList<String> dao.getAllowedSections(); status = validator.validateUploadedExcel(uploadedFile, templateFilePath, messages); } catch (IOESException e) { // TODO Auto-generated catch block e.printStackTrace(); } if (status == 1) { isValid = 0; messages.add("sheet_mismatch", new ActionMessage("errors.excel.sheet.mismatch")); AppConstants.IOES_LOGGER.info("errors.excel.sheet.mismatch"); } else if (status == 2) { isValid = 0; messages.add("sheetName_mismatch", new ActionMessage("errors.excel.sheetname.mismatch")); AppConstants.IOES_LOGGER.info("errors.excel.sheetname.mismatch"); } else if (status == 3) { isValid = 0; messages.add("sheet_blank", new ActionMessage("errors.excel.sheet.blank")); AppConstants.IOES_LOGGER.info("errors.excel.sheet.blank"); } else if (status == 4) { isValid = 0; messages.add("colNum_mismatch", new ActionMessage("errors.excel.columnnumber.mismatch")); AppConstants.IOES_LOGGER.info("errors.excel.columnnumber.mismatch"); } else if (status == 5) { isValid = 0; messages.add("colNanme_mismatch", new ActionMessage("errors.excel.columnname.mismatch")); AppConstants.IOES_LOGGER.info("errors.excel.columnname.mismatch"); } else if (status == 7) { isValid = 0; messages.add("invalid_excel", new ActionMessage("errors.excel.invalid.excel")); AppConstants.IOES_LOGGER.info("errors.excel.invalid.excel"); } else if (status == 8) { isValid = 0; messages.add("invalidObjInFile", new ActionMessage("errors.excel.invalid.filehasobject")); AppConstants.IOES_LOGGER.info("errors.excel.invalid.filehasobject"); } else if (status == 9) { isValid = 0; AppConstants.IOES_LOGGER.info("errors.excel.column.length"); } else if (status == 10) { isValid = 0; AppConstants.IOES_LOGGER.info("errors.excel.row.name.mismatch"); } else if (status == 11) { isValid = 0; AppConstants.IOES_LOGGER.info("errors.excel.row.count.mismatch"); } if (isValid == 0) { saveMessages(request, messages); formBean.setLoadExcelProductConfig_status(AppConstants.loadExcelProductConfig_status_ValidationError); //TODO forward = mapping.findForward("DisplayUploadPage"); } else { try { int statusSave = 0; HSSFWorkbook uploadedWorkBook = new HSSFWorkbook(uploadedFile.getInputStream()); int noOfSheets = uploadedWorkBook.getNumberOfSheets(); HSSFSheet lastSheet = uploadedWorkBook.getSheetAt(noOfSheets - 1); //Last sheet of uploaded file have product ID on second row's first cell; //int productID = new Double(lastSheet.getRow(1).getCell(0).getNumericCellValue()).intValue(); int productID = 28; if (productID > 0) { statusSave = saveUploadedFileInfo(uploadedFile, productID, templateFilePath); } } catch (IOESException ex) { //TODO: } catch (FileNotFoundException es) { } catch (IOException ex) { } } return forward; }
From source file:com.ibm.ioes.actions.NewOrderAction.java
/** * @param templateStream // w w w . j a va2 s. c o m * @method saveUploadedFileInfo * @purpose save uploaded file data in staging table in database * @param FormFile, * filepath, userName * @param excel_uploadPath, * uploadedFilePath * @return * @throws NpdException */ public int saveUploadedFileInfo(FormFile uploadedFile, int productID, String templateFilePath) throws IOESException { // AppConstants.IOES_LOGGER.info("saveUploadedFileInfo() started"); int sheetCol, sheetRow; ArrayList<Object[][]> excelDataList = new ArrayList<Object[][]>(); int thisSaveCode = 0; int saveStatusCode = 0; try { String fileName = null; if (uploadedFile != null) { fileName = uploadedFile.getFileName(); } if (fileName != null) { HSSFWorkbook workbook = null; HSSFSheet sheet = null; HSSFRow rowInSheet = null; HSSFCell cellInSheet = null; workbook = new HSSFWorkbook(uploadedFile.getInputStream()); for (int count = 0; count < workbook.getNumberOfSheets() - 1; count++) { sheet = workbook.getSheetAt(count); sheetRow = sheet.getLastRowNum(); sheetCol = sheet.getRow(0).getLastCellNum(); Object excelData[][] = new Object[sheetRow][sheetCol]; for (int r = 1; r <= sheetRow; r++) { rowInSheet = sheet.getRow(r); int columIndex = 0; for (int c = 1; c < sheetCol + 1; c++) { if (rowInSheet != null) { cellInSheet = rowInSheet.getCell(c - 1); if (cellInSheet != null) { if (cellInSheet.getCellType() == 0) { excelData[r - 1][columIndex++] = Utility.convertWithOutE_WithOutDotZero( String.valueOf(cellInSheet.getNumericCellValue())); /*NumberFormat formatter = new DecimalFormat("0"); excelData[r - 1][columIndex++] = formatter .format(cellInSheet.getNumericCellValue());*/ } else { excelData[r - 1][columIndex++] = cellInSheet.toString().trim(); } } else { excelData[r - 1][columIndex++] = ""; } } else { excelData[r - 1][columIndex++] = ""; } } } excelDataList.add(excelData); } } //if (checkCode == 1) { NewOrderModel model = new NewOrderModel(); saveStatusCode = model.saveUploadedFileToTemporaryTable(excelDataList, productID, fileName); if (saveStatusCode > 0) { thisSaveCode = 1; } else { thisSaveCode = 0; } /*} else { thisSaveCode= 0; }*/ // AppConstants.IOES_LOGGER.info("Completed.."); return thisSaveCode; } catch (Exception ed) { ed.printStackTrace(); AppConstants.IOES_LOGGER.error("Error while getting saveUploadedFileInfo " + ed.getMessage()); throw new IOESException(ed); } finally { AppConstants.IOES_LOGGER.info("saveUploadedFileInfo() completed"); } }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
@SuppressWarnings("deprecation") public String getErrorExcel(String filePath, int fileID) throws IOESException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); int colCount, ctr = 0; String fileName;/*from w w w . j a v a 2s. c om*/ HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList errVal = new ArrayList(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ErrorLogDto dtoObj; BillingTriggerValidation validateDto = null; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.errors") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); errVal = objDao.getErrorLog(fileID); System.out.println(errVal.toString()); System.out.println(wb.getNumberOfSheets()); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); wr = ws.getRow(1); colCount = wr.getLastCellNum(); wc = wr.createCell(colCount); wc.setCellValue("ERROR LOG"); for (int r = 2; r <= ws.getLastRowNum(); r++) { if ((ctr < errVal.size())) { dtoObj = (ErrorLogDto) errVal.get(ctr); wr = ws.getRow(r); if (wr != null) { int chk = 0; for (int col = 0; col < colCount; col++) { wc = wr.getCell(col); if (wc != null) { if (!(wc.toString().trim().equals(""))) { chk = 1; } } } if (chk == 1) { wc = wr.createCell(colCount); System.out.println(dtoObj.getErrorLogValue()); if (dtoObj.getErrorLogValue() == null) { wc.setCellValue("No Errors"); } else { wc.setCellValue(dtoObj.getErrorLogValue().toString()); } ctr++; } } } } } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); throw new IOESException("SQL Exception : " + ex.getMessage(), ex); } catch (IOException ioExp) { //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp); Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getResultExcel(String filePath, int fileID) throws IOESException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); int colCount, ctr = 0; String fileName;//from w w w . j a v a 2s . com HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList errVal = new ArrayList(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ErrorLogDto dtoObj; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.success") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); errVal = objDao.getResultLog(fileID); System.out.println(errVal.toString()); System.out.println(wb.getNumberOfSheets()); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); wr = ws.getRow(1); colCount = wr.getLastCellNum(); wc = wr.createCell(colCount); wc.setCellValue("RESULT LOG" + "_" + "ORDERNO"); for (int r = 2; r <= ws.getLastRowNum(); r++) { if ((ctr < errVal.size())) { dtoObj = (ErrorLogDto) errVal.get(ctr); wr = ws.getRow(r); if (wr != null) { int chk = 0; for (int col = 0; col < colCount; col++) { wc = wr.getCell(col); if (wc != null) { if (!(wc.toString().trim().equals(""))) { chk = 1; } } } if (chk == 1) { wc = wr.createCell(colCount); System.out.println(dtoObj.getResultLogValue()); if (dtoObj.getResultLogValue() == null) { wc.setCellValue("SUCCESS"); } else { if (dtoObj.getOrderNo() != null) wc.setCellValue(dtoObj.getResultLogValue().toString() + "_(" + dtoObj.getOrderNo().toString() + ")"); } ctr++; } } } } } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getResultExcel method of " + this.getClass().getSimpleName()); throw new IOESException("SQL Exception : " + ex.getMessage(), ex); } catch (IOException ioExp) { //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp); Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getResultExcel method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getFilledTemplate(String filePath, int templateId, int flag, String logicalLSI) throws IOESException, ParseException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); String fileName;/* ww w .ja v a 2 s . c o m*/ HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList filledTemplateData = new ArrayList(); ArrayList<ViewOrderDto> filledTemplateDataLineDetails = new ArrayList<ViewOrderDto>(); ArrayList<ViewOrderDto> filledTemplateDataChargeDetails = new ArrayList<ViewOrderDto>(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ViewOrderModel objviewmodel = new ViewOrderModel(); //ErrorLogDto dtoObj ; TransactionTemplateDto dtoObj; ViewOrderDto dtoObj1; DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); int totalRowsOfSheet = 0; ViewOrderDto objdto = null; String str = null; BillingTriggerValidation validateDto = null; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.filledTemplate") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); HSSFCellStyle whiteFG_yellow = wb.createCellStyle(); HSSFColor yellow = setColor(wb, (byte) 0xFF, (byte) 0xFF, (byte) 0x00); //whiteFG_yellow.setFillBackgroundColor(HSSFColor.YELLOW.index); whiteFG_yellow.setFillBackgroundColor(yellow.getIndex()); HSSFCellStyle whiteFG_green = wb.createCellStyle(); HSSFColor green = setColor(wb, (byte) 0x00, (byte) 0xFF, (byte) 0x00); //whiteFG_green.setFillBackgroundColor(HSSFColor.GREEN.index); whiteFG_green.setFillBackgroundColor(green.getIndex()); //System.out.println(filledTemplateData.toString()); //System.out.println(wb.getNumberOfSheets()); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); if (s == 0 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) { int ctr = 0, totalRowsOfSheetMain = 0, totalRowsOfSheetAtt = 0; totalRowsOfSheetMain = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheetMain + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(dtoObj.getOrderNo()); wr.createCell(2).setCellValue(new HSSFRichTextString((dtoObj.getAccountID()))); wr.createCell(3).setCellValue(new HSSFRichTextString((dtoObj.getSource()))); wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getCurrencyID()))); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getOpportunityId()))); wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getQuoteNo()))); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getProjectMangerID()))); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getZoneId()))); } ctr++; } totalRowsOfSheetAtt = objDao.getTotalRowsOfSheet(11, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(11, templateId, flag, logicalLSI); ctr = 0; for (int r = 2; r <= (totalRowsOfSheetMain + 1); r++) { wr = ws.getRow(r); dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); if (wr != null) { wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getRFSDate()))); wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getActMngrPhoneNo()))); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getActMngrEmailID()))); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getIRUOrderYN()))); wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getFreePeriodYN()))); wr.createCell(14).setCellValue(new HSSFRichTextString((dtoObj.getOrdExclusiveTax()))); wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getCAFDate()))); } ctr++; } } //GAM Sheet if (s == 1 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) { //to do nothing } //Contact Sheet if (s == 2 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) { int ctr = 0, rownum = 1; totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getContactType()); wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getSalutation()))); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getFirstName()))); wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getLastName()))); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getEmail()))); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getCellno()))); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getFaxno()))); wr.createCell(10).setCellValue(rownum); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getAddress1()))); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getAddress2()))); wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getAddress3()))); wr.createCell(14).setCellValue(new HSSFRichTextString((dtoObj.getCountrycode()))); wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getStateid()))); wr.createCell(16).setCellValue(new HSSFRichTextString((dtoObj.getCityid()))); wr.createCell(17).setCellValue(new HSSFRichTextString((dtoObj.getPincode()))); } ctr++; rownum++; } } if (s == 3 && (templateId == 1 || templateId == 22 || templateId == 21)) { int ctr = 0, rownum = 1; if (templateId == 21) { totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo()); wr.createCell(4).setCellValue(dtoObj.getServiceid()); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getServiceName()))); wr.createCell(6).setCellValue(dtoObj.getLineItemID()); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getLineItemName()))); } ctr++; rownum++; } } else { totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo()); wr.createCell(4).setCellValue(dtoObj.getServiceid()); wr.createCell(5).setCellValue(dtoObj.getLineItemID()); } ctr++; rownum++; } } } if (s == 3 && templateId == 41) { /*int ctr=0,rownum=1; totalRowsOfSheet=objDao.getTotalRowsOfSheet(s+1,templateId,flag,logicalLSI); filledTemplateData = objDao.getFilledTemplate(s+1,templateId,flag,logicalLSI); for(int r = 2; r <= (totalRowsOfSheet+1); r++) { dtoObj = (TransactionTemplateDto)filledTemplateData.get(ctr); wr=ws.createRow(r); if(wr!=null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(new HSSFRichTextString((dtoObj.getCustPONumber()))); wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getCustPODate()))); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getIsDefaultPO()))); wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getLegalEntity()))); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getTotalPOAmount()))); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getPeriodInMonths()))); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getContractStartDate()))); wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getContractEndDate()))); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getPoRemarks()))); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getPoEmailId()))); } ctr++;rownum++; }*/ } if (s == 4 && templateId == 41) { int ctr = 0, rownum = 1; totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(dtoObj.getOrderNo()); wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo()); wr.createCell(4).setCellValue(dtoObj.getServiceid()); wr.createCell(5).setCellValue(dtoObj.getLineItemID()); wr.createCell(6).setCellValue(dtoObj.getChargeID()); wr.createCell(7).setCellValue(dtoObj.getChargeAmount()); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getChargeFrequency()))); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getStrChargeType()))); wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getStrChargeName()))); wr.createCell(11).setCellValue(dtoObj.getFrequncyAmount()); wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getAnnotation()))); } ctr++; rownum++; } } if (s == 5 && templateId == 41) { int ctr = 0, rownum = 1; totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI); filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI); for (int r = 2; r <= (totalRowsOfSheet + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(rownum); wr.createCell(2).setCellValue(new HSSFRichTextString("")); wr.createCell(3).setCellValue(dtoObj.getLineItemID()); wr.createCell(4).setCellValue(dtoObj.getCreditPeriodID()); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getCreditPeriodName()))); wr.createCell(6).setCellValue(dtoObj.getLegealEntityID()); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getLegalEntityName()))); wr.createCell(8).setCellValue(dtoObj.getLicenseCompanyID()); wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getLicenseCompanyName()))); wr.createCell(10).setCellValue(dtoObj.getBillingModeID()); wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getBillingModeName()))); wr.createCell(12).setCellValue(dtoObj.getBillingFormatID()); wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getBillingFormatName()))); wr.createCell(14).setCellValue(dtoObj.getBillingTypeID()); wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getBillingTypeName()))); wr.createCell(16).setCellValue(dtoObj.getTaxationID()); wr.createCell(17).setCellValue(new HSSFRichTextString(dtoObj.getTaxationName())); wr.createCell(18).setCellValue(dtoObj.getBillingLevelID()); wr.createCell(19).setCellValue(new HSSFRichTextString(dtoObj.getBillingLevelName())); wr.createCell(20).setCellValue(dtoObj.getNoticePeriod()); wr.createCell(21).setCellValue(new HSSFRichTextString(dtoObj.getPenaltyClause())); wr.createCell(22).setCellValue(dtoObj.getCommitPeriod()); wr.createCell(23).setCellValue(dtoObj.getIsNfa()); wr.createCell(24).setCellValue(dtoObj.getBcpID()); wr.createCell(25).setCellValue(new HSSFRichTextString(dtoObj.getBcpName())); wr.createCell(26).setCellValue(new HSSFRichTextString(dtoObj.getStandardReasonId())); wr.createCell(27).setCellValue(new HSSFRichTextString(dtoObj.getStandardReasonName())); } ctr++; rownum++; } } // billing trigger bulkupload sheet 1 start if (s == 0 && templateId == 61) { int ctr = 0, rownum = 1; filledTemplateDataLineDetails = objviewmodel.getFilledTemplateforBillingLineSectionBulkUpload(); for (int r = 2; r <= (filledTemplateDataLineDetails.size() + 1); r++) { dtoObj1 = (ViewOrderDto) filledTemplateDataLineDetails.get(ctr); wr = ws.createRow(r); if (wr != null) { HSSFCell cell0 = wr.createCell(0); cell0.setCellValue(rownum); HSSFCell cell1 = wr.createCell(1); cell1.setCellValue(new HSSFRichTextString(dtoObj1.getLineNumber())); HSSFCellStyle sty1 = ws.getRow(1).getCell(1).getCellStyle(); wr.createCell(2).setCellValue(new HSSFRichTextString(dtoObj1.getLineName())); wr.createCell(3).setCellValue(new HSSFRichTextString(dtoObj1.getLogicalSino())); wr.createCell(4).setCellValue(new HSSFRichTextString(dtoObj1.getCustLogicalSino())); wr.createCell(5).setCellValue(dtoObj1.getOrderno()); wr.createCell(6).setCellValue(new HSSFRichTextString(dtoObj1.getOrdertype())); wr.createCell(7).setCellValue(new HSSFRichTextString(dtoObj1.getOrder_subtype())); wr.createCell(8).setCellValue(new HSSFRichTextString(dtoObj1.getSiid())); wr.createCell(9).setCellValue(new HSSFRichTextString(dtoObj1.getAccountid())); wr.createCell(10).setCellValue(new HSSFRichTextString(dtoObj1.getFx_status())); wr.createCell(11).setCellValue(new HSSFRichTextString(dtoObj1.getLine_status())); wr.createCell(12).setCellValue(new HSSFRichTextString(dtoObj1.getChallen_No())); wr.createCell(13).setCellValue(new HSSFRichTextString(dtoObj1.getChallen_date())); validateDto = dtoObj1.getBillingTriggerAllowDenyLogic(); HSSFCell cell14 = wr.createCell(14); cell14.setCellValue(new HSSFRichTextString(dtoObj1.getLocNo())); HSSFCellStyle sty = ws.getRow(1).getCell(14).getCellStyle(); if ("allow".equals(validateDto.getLocNoForEdit())) { cell14.setCellStyle(sty); } else { cell14.setCellStyle(sty1); } HSSFCell cell15 = wr.createCell(15); if (!(dtoObj1.getLocDate() == null || "".equals(dtoObj1.getLocDate()))) { cell15.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getLocDate())))); } else { cell15.setCellValue(new HSSFRichTextString(dtoObj1.getLocDate())); } if ("allow".equals(validateDto.getLocDateForEdit())) { cell15.setCellStyle(sty); } else { cell15.setCellStyle(sty1); } HSSFCell cell16 = wr.createCell(16); if (!(dtoObj1.getLocRecDate() == null || "".equals(dtoObj1.getLocRecDate()))) { cell16.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getLocRecDate())))); } else { cell16.setCellValue(new HSSFRichTextString(dtoObj1.getLocRecDate())); } if ("allow".equals(validateDto.getLocRecDateForEdit())) { cell16.setCellStyle(sty); } else { cell16.setCellStyle(sty1); } HSSFCell cell17 = wr.createCell(17); if (!(dtoObj1.getBillingTriggerDate() == null || "".equals(dtoObj1.getBillingTriggerDate()))) { cell17.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getBillingTriggerDate())))); } else { cell17.setCellValue(new HSSFRichTextString(dtoObj1.getBillingTriggerDate())); } if ("allow".equals(validateDto.getBtdForEdit())) { cell17.setCellStyle(sty); } else { cell17.setCellStyle(sty1); } wr.createCell(18) .setCellValue(new HSSFRichTextString(dtoObj1.getBillingTriggerProcess())); } ctr++; rownum++; } } // billing trigger bulkupload sheet 1 end //billing trigger bulkupload sheet 2 start if (s == 1 && templateId == 61) { int ctr = 0, rownum = 1; if (filledTemplateDataLineDetails.size() > 0) { filledTemplateDataChargeDetails = objviewmodel .getFilledTemplateforBillingChargeSectionBulkUpload(); for (int r = 2; r <= (filledTemplateDataChargeDetails.size() + 1); r++) { dtoObj1 = (ViewOrderDto) filledTemplateDataChargeDetails.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(0).setCellValue(rownum); HSSFCell cell1 = wr.createCell(1); cell1.setCellValue(dtoObj1.getChargeInfoId()); HSSFCellStyle sty1 = ws.getRow(1).getCell(1).getCellStyle(); wr.createCell(2).setCellValue(new HSSFRichTextString(dtoObj1.getLineNumber())); wr.createCell(3).setCellValue(new HSSFRichTextString(dtoObj1.getChargeType())); wr.createCell(4).setCellValue(new HSSFRichTextString(dtoObj1.getChargeName())); wr.createCell(5).setCellValue(dtoObj1.getChargePeriod()); wr.createCell(6).setCellValue(dtoObj1.getChargeAmt()); wr.createCell(7).setCellValue(new HSSFRichTextString(dtoObj1.getChargeStatus())); HSSFCell cell8 = wr.createCell(8); HSSFCellStyle sty8 = ws.getRow(1).getCell(8).getCellStyle(); if (!(dtoObj1.getDisconnectiondate() == null || "".equals(dtoObj1.getDisconnectiondate()))) { cell8.setCellValue(new HSSFRichTextString( Utility.showDate_Report4(df.parse(dtoObj1.getDisconnectiondate())))); } else { cell8.setCellValue(new HSSFRichTextString(dtoObj1.getDisconnectiondate())); } if ("Changed".equalsIgnoreCase(dtoObj1.getChargeStatus())) { cell8.setCellStyle(sty8); } wr.createCell(9) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeCreatedOnOrder())); wr.createCell(10) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndedOnOrder())); wr.createCell(11).setCellValue(new HSSFRichTextString(dtoObj1.getBillPeriod())); wr.createCell(12).setCellValue(new HSSFRichTextString(dtoObj1.getStartdatelogic())); wr.createCell(13).setCellValue(dtoObj1.getStart_date_days()); wr.createCell(14).setCellValue(dtoObj1.getStart_date_month()); wr.createCell(15).setCellValue(new HSSFRichTextString(dtoObj1.getEnddatelogic())); wr.createCell(16).setCellValue(dtoObj1.getEnd_date_days()); wr.createCell(17).setCellValue(dtoObj1.getEnd_date_month()); wr.createCell(18) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndDate_String())); wr.createCell(19).setCellValue(new HSSFRichTextString(dtoObj1.getAnnualRate())); wr.createCell(20).setCellValue(new HSSFRichTextString(dtoObj1.getAnnotation())); wr.createCell(21).setCellValue(new HSSFRichTextString(dtoObj1.getStartTokenNo())); wr.createCell(22).setCellValue(new HSSFRichTextString(dtoObj1.getStartFxStatus())); wr.createCell(23).setCellValue(new HSSFRichTextString(dtoObj1.getStartFxNo())); wr.createCell(24).setCellValue(new HSSFRichTextString(dtoObj1.getEndTokenNo())); wr.createCell(25).setCellValue(new HSSFRichTextString(dtoObj1.getEndFxStatus())); wr.createCell(26).setCellValue(new HSSFRichTextString(dtoObj1.getEndFxNo())); wr.createCell(27) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeStartStatus())); wr.createCell(28) .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndStatus())); wr.createCell(29) .setCellValue(new HSSFRichTextString(dtoObj1.getChargefrequency())); wr.createCell(30).setCellValue(new HSSFRichTextString(dtoObj1.getFxViewId())); } ctr++; rownum++; } } } // billing trigger bulkupload sheet 2 end } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getFilledTemplate method of " + this.getClass().getSimpleName()); throw new IOESException("SQL Exception : " + ex.getMessage(), ex); } catch (IOException ioExp) { //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp); Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getFilledTemplate method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getTemplate(String filePath, int templateId) throws IOESException, ParseException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); String fileName;//from w w w. j a v a 2 s. c om HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList filledTemplateData = new ArrayList(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ViewOrderModel objviewmodel = new ViewOrderModel(); //ErrorLogDto dtoObj ; TransactionTemplateDto dtoObj; ViewOrderDto dtoObj1; DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); int totalRowsOfSheet = 0; ViewOrderDto objdto = null; String str = null; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.filledTemplate") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); HSSFCellStyle whiteFG_yellow = wb.createCellStyle(); HSSFColor yellow = setColor(wb, (byte) 0xFF, (byte) 0xFF, (byte) 0x00); //whiteFG_yellow.setFillBackgroundColor(HSSFColor.YELLOW.index); whiteFG_yellow.setFillBackgroundColor(yellow.getIndex()); HSSFCellStyle whiteFG_green = wb.createCellStyle(); HSSFColor green = setColor(wb, (byte) 0x00, (byte) 0xFF, (byte) 0x00); //whiteFG_green.setFillBackgroundColor(HSSFColor.GREEN.index); whiteFG_green.setFillBackgroundColor(green.getIndex()); //System.out.println(filledTemplateData.toString()); //System.out.println(wb.getNumberOfSheets()); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); if (s == 0 && (templateId == 1)) { int ctr = 0, totalRowsOfSheetMain = 0, totalRowsOfSheetAtt = 0; //filledTemplateData = objDao.getFilledTemplate(s+1,templateId,flag); for (int r = 2; r <= (totalRowsOfSheetMain + 1); r++) { dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr); wr = ws.createRow(r); if (wr != null) { wr.createCell(1).setCellValue(dtoObj.getOrderNo()); wr.createCell(2).setCellValue(new HSSFRichTextString((dtoObj.getAccountID()))); wr.createCell(3).setCellValue(new HSSFRichTextString((dtoObj.getSource()))); wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getCurrencyID()))); wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getOpportunityId()))); wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getQuoteNo()))); wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getProjectMangerID()))); wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getZoneId()))); } ctr++; } } wb.write(fileOut); fileOut.close(); filePath = newFile; } } catch (IOException ioExp) { //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp); Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR::Exception occured in getTemplate method of " + this.getClass().getSimpleName()); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public String getResultErrorMixLog(String filePath, int fileID) throws IOESException { //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName()); int colCount, ctr = 0; String fileName;//www . j a va 2 s . c om HSSFWorkbook wb; HSSFSheet ws; HSSFRow wr; HSSFCell wc; ArrayList errVal = new ArrayList(); ErrorFileDaoImpl objDao = new ErrorFileDaoImpl(); ErrorLogDto dtoObj; try { ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources"); fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length()); String newFile = bundle.getString("excel.success") + "/" + fileName; FileOutputStream fileOut = new FileOutputStream(newFile); wb = new HSSFWorkbook(new FileInputStream(filePath)); errVal = objDao.getResultErrorMixLog(fileID); System.out.println(errVal.toString()); System.out.println(wb.getNumberOfSheets()); HSSFCellStyle styleErr = wb.createCellStyle(); HSSFCellStyle styleSuccess = wb.createCellStyle(); HSSFFont fontSuccess = wb.createFont(); HSSFFont fontErr = wb.createFont(); for (int s = 0; s < wb.getNumberOfSheets(); s++) { ws = wb.getSheetAt(s); wr = ws.getRow(1); colCount = wr.getLastCellNum(); wc = wr.createCell(colCount); wc.setCellValue("RESULT LOG" + "_" + "ORDERNO"); for (int r = 2; r <= ws.getLastRowNum(); r++) { if ((ctr < errVal.size())) { dtoObj = (ErrorLogDto) errVal.get(ctr); wr = ws.getRow(r); if (wr != null) { int chk = 0; for (int col = 0; col < colCount; col++) { wc = wr.getCell(col); if (wc != null) { if (!(wc.toString().trim().equals(""))) { chk = 1; } } } if (chk == 1) { wc = wr.createCell(colCount); if ((dtoObj.getResultLogValue() == null || dtoObj.getResultLogValue().length() == 0) && dtoObj.getErrorLogValue() != null) { fontErr.setColor(HSSFColor.RED.index); styleErr.setFont(fontErr); wc.setCellStyle(styleErr); wc.setCellValue(dtoObj.getErrorLogValue().toString()); } else { fontSuccess.setColor(HSSFColor.BLACK.index); styleSuccess.setFont(fontSuccess); wc.setCellStyle(styleSuccess); wc.setCellValue(dtoObj.getResultLogValue().toString()); } ctr++; } } } } } wb.write(fileOut); fileOut.close(); filePath = newFile; } catch (IOESException ex) { logger.error(ex.getMessage() + " Exception occured in getResultExcel method of " + this.getClass().getSimpleName()); throw new IOESException("SQL Exception : " + ex.getMessage(), ex); } catch (IOException ioExp) { //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName()); //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp); return filePath = "NOTFOUND"; } return filePath; }
From source file:com.isotrol.impe3.idx.oc.extractors.ExtractorMsExcel.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * //from w w w. ja va 2 s .co m * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring * @deprecated */ 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 (excelWb.getSheetName(i) != null && !excelWb.getSheetName(i).trim().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.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:com.krawler.esp.fileparser.excel.MsExcelParser.java
License:Open Source License
public String extractText(String filepath) throws Exception { InputStream input = new BufferedInputStream(new FileInputStream(filepath)); String resultText = ""; HSSFWorkbook wb = new HSSFWorkbook(input); if (wb == null) { return resultText; }/*from w w w . j a va 2s . c o m*/ HSSFSheet sheet; HSSFRow row; HSSFCell cell; int sNum = 0; int rNum = 0; int cNum = 0; sNum = wb.getNumberOfSheets(); for (int i = 0; i < sNum; i++) { if ((sheet = wb.getSheetAt(i)) == null) { continue; } rNum = sheet.getLastRowNum(); for (int j = 0; j <= rNum; j++) { if ((row = sheet.getRow(j)) == null) { continue; } cNum = row.getLastCellNum(); for (int k = 0; k < cNum; k++) { try { if ((cell = row.getCell((short) k)) != null) { /* * if(HSSFDateUtil.isCellDateFormatted(cell) == * true) { resultText += * cell.getDateCellValue().toString() + " "; } else */ if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { resultText += " "; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { resultText += cell.getRichStringCellValue().toString() + " "; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double d = new Double(cell.getNumericCellValue()); resultText += d.toString() + " "; } /* * else if(cell.getCellType() == * HSSFCell.CELL_TYPE_FORMULA){ resultText += * cell.getCellFormula() + " "; } */ } } catch (Exception ex) { } } resultText += "\n"; } } if (input != null) { input.close(); } return resultText; }
From source file:com.krawler.esp.fileparser.excel.MsExcelParser.java
License:Open Source License
public String getFormatedJSON(String filepath) throws Exception { InputStream input = new BufferedInputStream(new FileInputStream(filepath)); JSONObject jobj = new JSONObject(); HSSFWorkbook wb = new HSSFWorkbook(input); if (wb == null) { return ("\"data\":[]"); }/*from www . ja v a 2 s . c o m*/ HSSFSheet sheet; HSSFRow row; HSSFCell cell; int sNum = 0; int rNum = 0; int cNum = 0; sNum = wb.getNumberOfSheets(); for (int i = 0; i < sNum; i++) { if ((sheet = wb.getSheetAt(i)) == null) { continue; } rNum = sheet.getLastRowNum(); for (int j = 0; j <= rNum; j++) { if ((row = sheet.getRow(j)) == null) { continue; } cNum = row.getLastCellNum(); JSONObject temp = new JSONObject(); for (int k = 0; k < cNum; k++) { try { if ((cell = row.getCell((short) k)) != null) { if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { temp.put("cell" + cell.getCellNum(), ""); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { temp.put("cell" + cell.getCellNum(), cell.getRichStringCellValue().toString()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double d = new Double(cell.getNumericCellValue()); temp.put("cell" + cell.getCellNum(), d.toString()); } } } catch (Exception ex) { } } jobj.append("data", temp); temp = null; } } if (input != null) { input.close(); } return jobj.toString(); }