List of usage examples for org.apache.poi.xssf.usermodel XSSFCell toString
@Override
public String toString()
Formula cells return the formula string, rather than the formula result.
From source file:leerArchivos.java
private void obtener(List cellDataList) { for (int i = 0; i < cellDataList.size(); i++) { List cellTempList = (List) cellDataList.get(i); for (int j = 0; j < cellTempList.size(); j++) { XSSFCell hssfCell = (XSSFCell) cellTempList.get(j); String stringCellValue = hssfCell.toString(); System.out.println(stringCellValue + " "); }/*from ww w .j av a 2s. c o m*/ System.out.println(); } }
From source file:com.cn.util.ExcelImport.java
/** * ?2007excel// www.j a v a 2 s.c om * * @param file * @return */ private static List<List<Object>> read2007Excel(InputStream inputStream) throws IOException { List<List<Object>> dataList = new ArrayList<>(); XSSFWorkbook xwb = new XSSFWorkbook(inputStream); XSSFSheet sheet = xwb.getSheetAt(0); XSSFRow row = null; XSSFCell cell = null; Object val = null; DecimalFormat df = new DecimalFormat("0");// ? SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ? for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); if (row == null) { continue; } List<Object> objList = new ArrayList<>(); for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null) { val = null; objList.add(val); continue; } switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { val = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { val = df.format(cell.getNumericCellValue()); } else { val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } break; case XSSFCell.CELL_TYPE_BOOLEAN: val = cell.getBooleanCellValue(); break; case XSSFCell.CELL_TYPE_BLANK: val = ""; break; default: val = cell.toString(); break; } objList.add(val); } dataList.add(objList); } return dataList; }
From source file:com.denimgroup.threadfix.csv2ssl.parser.FormatParser.java
License:Mozilla Public License
public static Option<String[]> getHeadersExcel(File file) { try {/* ww w . j av a 2 s . c o m*/ FileInputStream fis = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet ws = wb.getSheetAt(0); // read the first sheet int totalRows = ws.getLastRowNum(); if (totalRows == 0) { return Option.failure("No lines found in file " + file.getName()); } XSSFRow row = ws.getRow(0); String[] headers = new String[row.getLastCellNum()]; for (int index = 0; index < row.getLastCellNum(); index++) { XSSFCell cell = row.getCell(index); assert cell != null : "Got null cell at index " + index; headers[index] = cell.toString(); } return Option.success(headers); } catch (IOException e) { e.printStackTrace(); return Option.failure("Encountered IOException."); } }
From source file:com.denimgroup.threadfix.csv2ssl.serializer.RecordToXMLSerializer.java
License:Mozilla Public License
public static String getFromExcel(XSSFWorkbook wb, String... format) { StringBuilder builder = getStart(); int line = Configuration.CONFIG.shouldSkipFirstLine ? 1 : 0; XSSFSheet ws = wb.getSheetAt(0); // read the first sheet int totalColumns = ws.getRow(0).getLastCellNum(); int totalRows = ws.getLastRowNum(); Map<String, String> rowMap = map(); for (; line <= totalRows; line++) { // we want <= because the index returned from ws.getLastRowNum() is valid XSSFRow row = ws.getRow(line);/*from www. ja va2s. c o m*/ for (int column = 0; column < totalColumns; column++) { XSSFCell cell = row.getCell(column); if (cell == null) { // cells are null if there's no data in them; this is fine. continue; } String value = cell.toString(); if (format.length > column) { rowMap.put(format[column], value); } else { System.err.println("format wasn't long enough for column. Column length = " + totalColumns + ", format was " + format.length); } } addRecord(builder, line, rowMap); rowMap.clear(); } return writeEnd(builder); }
From source file:com.docdoku.server.esindexer.ESTools.java
License:Open Source License
private static String microsoftExcelDocumentToString(InputStream inputStream) throws IOException, OpenXML4JException, XmlException { StringBuilder sb = new StringBuilder(); try (InputStream excelStream = new BufferedInputStream(inputStream)) { if (POIFSFileSystem.hasPOIFSHeader(excelStream)) { // Before 2007 format files POIFSFileSystem excelFS = new POIFSFileSystem(excelStream); ExcelExtractor excelExtractor = new ExcelExtractor(excelFS); sb.append(excelExtractor.getText()); } else { // New format XSSFWorkbook workBook = new XSSFWorkbook(excelStream); int numberOfSheets = workBook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { XSSFSheet sheet = workBook.getSheetAt(0); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { XSSFRow row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { XSSFCell cell = (XSSFCell) cellIterator.next(); sb.append(cell.toString()); sb.append(" "); }//from w w w . j a v a2s . c o m sb.append("\n"); } sb.append("\n"); } } } return sb.toString(); }
From source file:com.rknowsys.eapp.DataImportAction.java
/** * This method saves uploaded file into the server folder.And stores the * file data into the database./* ww w . j a v a 2s. c o m*/ * * @param actionRequest * @param actionResponse * @throws IOException */ public void saveDataImport(ActionRequest actionRequest, ActionResponse actionResponse) throws IOException { System.out.println("saveDataImport method()..!!!!!!!!!!"); ThemeDisplay themeDisplay = (ThemeDisplay) actionRequest.getAttribute(WebKeys.THEME_DISPLAY); Properties properties = PortalUtil.getPortalProperties(); String uploadDirectory = properties.getProperty("liferay.home") + "/data/uploadedFiles"; UploadPortletRequest uploadRequest = PortalUtil.getUploadPortletRequest(actionRequest); byte[] bytes = null; try { // ==========Saving the uploaded file in server folder with uploaded // date and time as file filename prefix.=========== Date date = new Date(); SimpleDateFormat sd = new SimpleDateFormat("mm-dd-yyyy"); String d = sd.format(date); System.out.println("uploaded date = " + d); File uploadedFile = uploadRequest.getFile("fileName"); bytes = FileUtil.getBytes(uploadedFile); String fileName = uploadRequest.getFileName("fileName"); File newFile = null; File newDirectory = new File(uploadDirectory); if (!newDirectory.exists()) { System.out.println("directory does not exist"); Path directoryPath = Paths.get(uploadDirectory); Files.createDirectory(directoryPath.getParent()); } newFile = new File(uploadDirectory + "/" + d + Calendar.getInstance().getTimeInMillis() + fileName); // ============Creating the New file in server folder=========== if (!newFile.exists()) { System.out.println("file does not exist"); Path pathToFile = Paths .get(uploadDirectory + "/" + d + Calendar.getInstance().getTimeInMillis() + fileName); Files.createFile(pathToFile); } // =========Reading the uploaded file content and writing the // content to newly created file============== FileInputStream fileInputStream = new FileInputStream(uploadedFile); fileInputStream.read(bytes); FileOutputStream fileOutputStream = new FileOutputStream(newFile); fileOutputStream.write(bytes, 0, bytes.length); fileOutputStream.close(); fileInputStream.close(); String filePath = newFile.getAbsolutePath(); System.out.println("filePath = " + filePath); FileInputStream file1 = new FileInputStream(new File(filePath)); // Reading Excel file Rows and cells content using apache poi api // and saving the data in to the database. XSSFWorkbook workbook = new XSSFWorkbook(file1); // Create Workbook // instance // holding // reference to // .xlsx file XSSFSheet sheet = workbook.getSheetAt(0); // Get first/desired sheet // from the workbook @SuppressWarnings("rawtypes") Iterator rows = sheet.rowIterator(); // Iterate through each rows // one by one while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); if (row.getRowNum() != 0) { EmpPersonalDetails empPersonalDetails = EmpPersonalDetailsLocalServiceUtil .createEmpPersonalDetails(CounterLocalServiceUtil.increment()); Employee employee = EmployeeLocalServiceUtil .createEmployee(CounterLocalServiceUtil.increment()); JobTitle jobTitle = JobTitleLocalServiceUtil .createJobTitle(CounterLocalServiceUtil.increment()); SubUnit subUnit = SubUnitLocalServiceUtil.createSubUnit(CounterLocalServiceUtil.increment()); EmploymentStatus employmentStatus = EmploymentStatusLocalServiceUtil .createEmploymentStatus(CounterLocalServiceUtil.increment()); EmpJob empJob = EmpJobLocalServiceUtil.createEmpJob(CounterLocalServiceUtil.increment()); EmpSupervisor empSupervisor = EmpSupervisorLocalServiceUtil .createEmpSupervisor(CounterLocalServiceUtil.increment()); @SuppressWarnings("rawtypes") Iterator cells = row.cellIterator(); while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); if (cell.getColumnIndex() == 0) { empPersonalDetails.setFirstName(cell.toString()); } if (cell.getColumnIndex() == 1) { empPersonalDetails.setMiddleName(cell.toString()); } if (cell.getColumnIndex() == 2) { empPersonalDetails.setLastName(cell.toString()); } if (cell.getColumnIndex() == 3) { empPersonalDetails.setEmployeeNo(cell.getRawValue()); } if (cell.getColumnIndex() == 4) { empPersonalDetails.setLicenseNo(cell.getRawValue()); } if (cell.getColumnIndex() == 5) { jobTitle.setTitle(cell.toString()); } if (cell.getColumnIndex() == 6) { employmentStatus.setEmploymentstatus(cell.toString()); } if (cell.getColumnIndex() == 7) { subUnit.setName(cell.toString()); } } employee.setUserId(themeDisplay.getUserId()); employee.setGroupId(themeDisplay.getCompanyGroupId()); employee.setCompanyId(themeDisplay.getCompanyId()); employee.setCreateDate(date); employee.setModifiedDate(date); employee = EmployeeLocalServiceUtil.addEmployee(employee); empPersonalDetails.setUserId(themeDisplay.getUserId()); empPersonalDetails.setGroupId(themeDisplay.getCompanyGroupId()); empPersonalDetails.setCompanyId(themeDisplay.getCompanyId()); empPersonalDetails.setCreateDate(date); empPersonalDetails.setModifiedDate(date); empPersonalDetails.setEmployeeId(employee.getEmployeeId()); empPersonalDetails = EmpPersonalDetailsLocalServiceUtil .addEmpPersonalDetails(empPersonalDetails); jobTitle.setUserId(themeDisplay.getUserId()); jobTitle.setGroupId(themeDisplay.getCompanyGroupId()); jobTitle.setCompanyId(themeDisplay.getCompanyId()); jobTitle.setCreateDate(date); jobTitle.setModifiedDate(date); jobTitle = JobTitleLocalServiceUtil.addJobTitle(jobTitle); subUnit.setUserId(themeDisplay.getUserId()); subUnit.setGroupId(themeDisplay.getCompanyGroupId()); subUnit.setCompanyId(themeDisplay.getCompanyId()); subUnit.setCreateDate(date); subUnit.setModifiedDate(date); subUnit = SubUnitLocalServiceUtil.addSubUnit(subUnit); employmentStatus.setUserId(themeDisplay.getUserId()); employmentStatus.setGroupId(themeDisplay.getCompanyGroupId()); employmentStatus.setCompanyId(themeDisplay.getCompanyId()); employmentStatus.setCreateDate(date); employmentStatus.setModifiedDate(date); employmentStatus = EmploymentStatusLocalServiceUtil.addEmploymentStatus(employmentStatus); empJob.setJobTitleId(employee.getEmployeeId()); empJob.setEmploymentStatusId(employmentStatus.getEmploymentStatusId()); empJob.setSubUnitId(subUnit.getSubUnitId()); empJob.setUserId(themeDisplay.getUserId()); empJob.setGroupId(themeDisplay.getCompanyGroupId()); empJob.setCompanyId(themeDisplay.getCompanyId()); empJob.setCreateDate(date); empJob.setModifiedDate(date); empJob.setEmployeeId(employee.getEmployeeId()); empJob = EmpJobLocalServiceUtil.addEmpJob(empJob); empSupervisor.setUserId(themeDisplay.getUserId()); empSupervisor.setGroupId(themeDisplay.getCompanyGroupId()); empSupervisor.setCompanyId(themeDisplay.getCompanyId()); empSupervisor.setCreateDate(date); empSupervisor.setModifiedDate(date); empSupervisor.setEmployeeId(employee.getEmployeeId()); empSupervisor.setReporterEmployeeId(empPersonalDetails.getEmployeeId()); empSupervisor = EmpSupervisorLocalServiceUtil.addEmpSupervisor(empSupervisor); } } file1.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.sandstone_tech.lendfastselenium2.util.PoiFileReader.java
private String getStringValue(XSSFCell cell) { if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: return cell.toString(); case XSSFCell.CELL_TYPE_NUMERIC: return this.formatDouble(cell.getNumericCellValue()); case XSSFCell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case XSSFCell.CELL_TYPE_BLANK: return ""; default://from w w w . j a v a 2s .c o m return ""; } } return ""; }
From source file:com.seer.datacruncher.fileupload.Excel_2007_FileReadObject.java
License:Open Source License
@Override public String parseStream(long schemaId, InputStream ios) { List<SchemaFieldEntity> listSchemaFields = schemaFieldsDao.listSchemaFields(schemaId); StringBuffer sb = new StringBuffer(); try {/*from ww w .j a va2s. c o m*/ XSSFWorkbook myWorkBook = new XSSFWorkbook(ios); XSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator<Row> rowIter = mySheet.rowIterator(); sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>"); while (rowIter.hasNext()) { int j = 0; XSSFRow myRow = (XSSFRow) rowIter.next(); Iterator<Cell> cellIter = myRow.cellIterator(); sb.append("<" + Tag.TAG_ROOT + ">"); while (cellIter.hasNext()) { XSSFCell myCell = (XSSFCell) cellIter.next(); String fieldValue = myCell.toString().trim(); if (!listSchemaFields.get(j).getNillable() || fieldValue.length() > 0) { sb.append("<" + listSchemaFields.get(j).getName() + ">") .append(myCell.toString().replaceAll("&", "&")) .append("</" + listSchemaFields.get(j).getName() + ">"); } j++; if (j == listSchemaFields.size() && cellIter.hasNext()) { return I18n.getMessage("error.numberFieldsNoMatch"); } else if (!cellIter.hasNext() && j != listSchemaFields.size()) { return I18n.getMessage("error.numberFieldsNoMatch"); } } sb.append("</" + Tag.TAG_ROOT + ">\n"); } } catch (IOException e) { logger.error("Error occured during fetch records from excel file.", e); return "Could not able to parse Excel file. " + e.getMessage(); } return sb.toString(); }
From source file:com.ts.control.UploadFile.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); PrintWriter out = response.getWriter(); String campo1 = ""; String campo2 = ""; String campo3 = ""; String campo4 = ""; String campo5 = ""; String campo6 = ""; String campo7 = ""; int c = 1;// w ww . j a v a 2 s. com try { boolean ismultipart = ServletFileUpload.isMultipartContent(request); if (!ismultipart) { } else { FileItemFactory factory = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(factory); List items = null; try { items = upload.parseRequest(request); } catch (Exception e) { } Iterator itr = items.iterator(); while (itr.hasNext()) { FileItem item = (FileItem) itr.next(); if (item.isFormField()) { } else { String itemname = item.getName(); if ((itemname == null || itemname.equals(""))) { continue; } String filename = FilenameUtils.getName(itemname); System.out.println("ruta---------------" + saveFile + filename); File f = checkExist(filename); item.write(f); //////// List cellDataList = new ArrayList(); try { FileInputStream fileInputStream = new FileInputStream(f); XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream); XSSFSheet hssfSheet = workBook.getSheetAt(0); Iterator rowIterator = hssfSheet.rowIterator(); while (rowIterator.hasNext()) { XSSFRow hssfRow = (XSSFRow) rowIterator.next(); Iterator iterator = hssfRow.cellIterator(); List cellTempList = new ArrayList(); while (iterator.hasNext()) { XSSFCell hssfCell = (XSSFCell) iterator.next(); cellTempList.add(hssfCell); } cellDataList.add(cellTempList); } } catch (Exception e) { e.printStackTrace(); } out.println("<table border='3' class='table table-bordered table-striped table-hover'>"); for (int i = 1; i < cellDataList.size(); i++) { List cellTempList = (List) cellDataList.get(i); out.println("<tr>"); for (int j = 0; j < cellTempList.size(); j++) { XSSFCell hssfCell = (XSSFCell) cellTempList.get(j); String dato = hssfCell.toString(); out.print("<td>" + dato + "</td>"); switch (c) { case 1: campo1 = dato; c++; break; case 2: campo2 = dato; c++; break; case 3: campo3 = dato; c++; break; case 4: campo4 = dato; c++; break; case 5: campo5 = dato; c++; break; case 6: campo6 = dato; c++; break; case 7: campo7 = dato; c = 1; break; } } //model.Consulta.addRegistros(campo1,campo2,campo3,campo4,campo5,campo6,campo7); } out.println("</table><br>"); ///////// } } } } catch (Exception e) { } finally { out.close(); } }
From source file:com.viettel.hqmc.DAO.FilesDAO.java
/** * import du lieu tu excel/*from w w w. j av a 2 s .c o m*/ * * @return */ public String importFileFromExcel() throws FileNotFoundException, IOException, ParseException { List fileInfo = new ArrayList(); String strReturn = ERROR_PERMISSION; // TechnicalStandard ts = new TechnicalStandard(); TechnicalStandardDAOHE tshe = new TechnicalStandardDAOHE(); String err = ""; Long attachId = Long.parseLong(getRequest().getParameter("attachId"));//get attactId VoAttachs att = (VoAttachs) getSession().get("com.viettel.voffice.database.BO.VoAttachs", attachId);//Attachs BO if (att == null) { fileInfo.add("File not found"); err += "File not found"; } else { Category item; ResourceBundle rb = ResourceBundle.getBundle("config");//get link tuong doi String dir = rb.getString("directoryExcel"); String linkFile = att.getAttachPath(); linkFile = dir + linkFile; createForm.setPath(linkFile); InputStream myxls = new FileInputStream(linkFile);//get file excel XSSFWorkbook wb = new XSSFWorkbook(myxls); XSSFRow row = null; String matchingTarget = null; XSSFCell productName = null; XSSFCell businessTaxCode = null; XSSFCell manufactorAddress = null; XSSFCell manufactorName = null; XSSFCell manufactorTel = null; XSSFCell manufactorFax = null; XSSFCell manufactorEmail = null; XSSFCell nationName = null; XSSFCell signer = null; XSSFCell assessmentMethod = null; XSSFCell annoucementNo = null; XSSFCell pushlishDate = null; XSSFCell nationCompanyName = null; XSSFCell nationCompanyAddress = null; try { XSSFSheet sheet = wb.getSheetAt(0); try { // XSSFSheet sheet1 = wb.getSheetAt(1); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Khng tm thy Sheet Chi tit sn phm, "; } try { // XSSFSheet sheet2 = wb.getSheetAt(2); } catch (Exception ex) { // log.error(e.getMessage()); LogUtil.addLog(ex);//binhnt sonar a160901 err += "Khng tm thy Sheet Ch tiu cht lng ch yu, "; } try { // XSSFSheet sheet3 = wb.getSheetAt(3); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Khng tm thy Sheet Ch tiu vi sinh vt, "; } try { // XSSFSheet sheet4 = wb.getSheetAt(4); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Khng tm thy Sheet Hm lng kim loi nng, "; } try { // XSSFSheet sheet5 = wb.getSheetAt(5); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Khng tm thy Sheet Hm lng ha cht, "; } try { // XSSFSheet sheet6 = wb.getSheetAt(6); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Khng tm thy Sheet K hoch kim sot cht lng, "; } if (sheet == null) { err += "Khng tm thy Sheet Bn cng b, "; } else { String sheetName = sheet.getSheetName(); if (!"Ban_Cong_bo".equals(sheetName)) { err += "Sai tn sheet Bn cng b, "; } } // XSSFRow firstRow = sheet.getRow(1); int rowNums = sheet.getLastRowNum(); // UsersDAOHE sdhe = new UsersDAOHE(); // SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); row = sheet.getRow(1); businessTaxCode = row.getCell((short) 1); productName = row.getCell((short) 3); row = sheet.getRow(4); manufactorName = row.getCell((short) 1); manufactorAddress = row.getCell((short) 3); row = sheet.getRow(5); manufactorTel = row.getCell((short) 1); manufactorFax = row.getCell((short) 3); row = sheet.getRow(6); manufactorEmail = row.getCell((short) 1); nationName = row.getCell((short) 3); row = sheet.getRow(7); nationCompanyName = row.getCell((short) 1); nationCompanyAddress = row.getCell((short) 3); row = sheet.getRow(10); annoucementNo = row.getCell((short) 1); pushlishDate = row.getCell((short) 3); row = sheet.getRow(11); signer = row.getCell((short) 1); assessmentMethod = row.getCell((short) 3); matchingTarget = ""; String standardCode; for (int i = 12; i < rowNums; i++) { row = sheet.getRow(i); if (row.getCell((short) 1).toString() != "") { XSSFCell standardCodeCell = row.getCell((short) 1); standardCode = standardCodeCell.getRichStringCellValue().toString(); if (tshe.findStandardByCode(standardCode)) { XSSFCell matchingTargetCell = row.getCell((short) 2); matchingTarget += matchingTargetCell.getRichStringCellValue() + ";"; } else { err += "Quy chun (quy nh) " + standardCode + " khng chnh xc ! "; break; } } else { break; } } } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "li tab bn cng b hp quy "; } if (matchingTarget != "" && matchingTarget != null) { matchingTarget = matchingTarget.substring(0, matchingTarget.length() - 1); } //tab chi tiet san pham XSSFCell productNo = null; XSSFCell productStatus = null; XSSFCell productColor = null; XSSFCell productSmell = null; XSSFCell productOtherstatus = null; XSSFCell productType = null; XSSFCell otherTarget = null; XSSFCell component = null; XSSFCell timeinuse = null; XSSFCell useage = null; XSSFCell objectInUse = null; XSSFCell guideline = null; //XSSFCell packageRecipe = null; XSSFCell packageMaterial = null; XSSFCell productProcess = null; XSSFCell counterfeitDistinctive = null; XSSFCell origin = null; XSSFCell signDate = null; XSSFCell signer_productdetails = null; XSSFCell chemicalTargetUnwanted = null; try { XSSFSheet sheet1 = wb.getSheetAt(1); if (sheet1 == null) { err += "Khng tm thy Sheet Chi tit sn phm, "; } else { String sheetName = sheet1.getSheetName(); if (!"Chi_tiet_san_pham".equals(sheetName)) { err += "Sai tn Sheet Chi tit sn phm, "; } } row = sheet1.getRow(1); productType = row.getCell((short) 1); productNo = row.getCell((short) 3); row = sheet1.getRow(4); productStatus = row.getCell((short) 1); productColor = row.getCell((short) 3); row = sheet1.getRow(5); productSmell = row.getCell((short) 1); productOtherstatus = row.getCell((short) 3); row = sheet1.getRow(13); otherTarget = row.getCell((short) 1); row = sheet1.getRow(14); component = row.getCell((short) 1); timeinuse = row.getCell((short) 3); row = sheet1.getRow(15); useage = row.getCell((short) 1); objectInUse = row.getCell((short) 3); row = sheet1.getRow(16); guideline = row.getCell((short) 1); packageMaterial = row.getCell((short) 3); row = sheet1.getRow(17); productProcess = row.getCell((short) 3); //packageRecipe = row.getCell((short) 1); row = sheet1.getRow(18); counterfeitDistinctive = row.getCell((short) 1); origin = row.getCell((short) 3); row = sheet1.getRow(19); signDate = row.getCell((short) 1); signer_productdetails = row.getCell((short) 3); // bo sung ham luong hoa chat khong mong muon XSSFSheet sheet5 = wb.getSheetAt(5); int rowNums = sheet5.getLastRowNum(); do { row = sheet5.getRow(rowNums); chemicalTargetUnwanted = row.getCell((short) 2); rowNums--; } while (chemicalTargetUnwanted == null); // chemicalTargetUnwanted = row.getCell((short) 2); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Li tab chi tit sn phm "; } // do du lieu vao form Long fileId = getRequest().getParameter("fileId") == null ? 0L : Long.parseLong(getRequest().getParameter("fileId")); Long fileType = getRequest().getParameter("fileType") == null ? 0L : Long.parseLong(getRequest().getParameter("fileType")); if (fileType > 0L && fileId > 0L) { createForm = new FilesForm(); createForm.setFileType(fileType); createForm.setFileId(fileId); } UsersDAOHE udhe = new UsersDAOHE(); Users user = udhe.findById(getUserId()); BusinessDAOHE bdhe = new BusinessDAOHE(); Business bus = bdhe.findById(user.getBusinessId()); if (createForm.getFileId() != null && createForm.getFileId() > 0l) { FilesDAOHE fdhe = new FilesDAOHE(); createForm = fdhe.getFilesDetail(createForm.getFileId()); if (!createForm.getFileType().equals(0L)) { ProcedureDAOHE cdhe = new ProcedureDAOHE(); List lstTTHC = cdhe.getProcedureForChange(createForm.getFileType()); lstCategory = new ArrayList(); lstCategory.addAll(lstTTHC); lstCategory.add(0, new Procedure(Constants.COMBOBOX_HEADER_VALUE, Constants.COMBOBOX_HEADER_TEXT_SELECT)); getRequest().setAttribute("lstFileType", lstCategory); } } if (createForm.getFileType() != null && createForm.getFileType() > 0l) { ProcedureDAOHE pdhe = new ProcedureDAOHE(); CategoryDAOHE cdhe = new CategoryDAOHE(); TechnicalStandardDAOHE tdhe = new TechnicalStandardDAOHE(); FilesDAOHE fdhe = new FilesDAOHE(); if (!fileType.equals(0L)) { createForm.setFileType(fileType); } Procedure tthc = pdhe.findById(createForm.getFileType()); if (tthc != null) { lstProductType = cdhe.findAllCategory("SP"); lstUnit = cdhe.findAllCategory("DVI"); lstStandard = tdhe.findAllStandard(); String lstDepts = convertToJSONData(lstStandard, "vietnameseName", "vietnameseName"); getRequest().setAttribute("lstStandard", lstDepts); UserAttachsDAOHE uahe = new UserAttachsDAOHE(); lstUserAttach = uahe.findAllUserAttach(getUserId()); String lstUserAttachs = convertToJSONData(lstUserAttach, "attachName", "attachName"); getRequest().setAttribute("lstUserAttach", lstUserAttachs); if (lstUserAttachs.trim().length() > 0) { createForm.setCountUA(1L); } else { createForm.setCountUA(0L); } getRequest().setAttribute("lstProductType", lstProductType); getRequest().setAttribute("lstUnit", lstUnit); String fileLst = tthc.getFileList(); getRequest().setAttribute("fileList", com.viettel.common.util.StringUtils.removeHTML(fileLst)); getRequest().setAttribute("agencyName", getDepartmentName()); getRequest().setAttribute("fileNameFull", tthc.getName()); strReturn = tthc.getDescription(); if (createForm.getAnnouncement() != null) { if (createForm.getAnnouncement().getAnnouncementNo() != null && createForm.getAnnouncement().getAnnouncementNo().length() > 0l) { return strReturn; } } if (strReturn.equals(Constants.FILE_DESCRIPTION.ANNOUNCEMENT_FILE01) || strReturn.equals(Constants.FILE_DESCRIPTION.ANNOUNCEMENT_FILE03) || strReturn.equals(Constants.FILE_DESCRIPTION.CONFIRM_FUNC_IMP) || strReturn.equals(Constants.FILE_DESCRIPTION.CONFIRM_FUNC_VN) || strReturn.equals(Constants.FILE_DESCRIPTION.CONFIRM_NORMAL_IMP) || strReturn.equals(Constants.FILE_DESCRIPTION.CONFIRM_NORMAL_VN) || strReturn.equals(Constants.FILE_DESCRIPTION.REC_CONFIRM_NORMAL_IMP) || strReturn.equals(Constants.FILE_DESCRIPTION.RE_ANNOUNCEMENT) || strReturn.equals(Constants.FILE_DESCRIPTION.RE_CONFIRM_FUNC_IMP) || strReturn.equals(Constants.FILE_DESCRIPTION.RE_CONFIRM_FUNC_VN) || strReturn.equals(Constants.FILE_DESCRIPTION.RE_CONFIRM_NORMAL_VN)) { String announcementNoStr = fdhe.getReceiptNoNew(getUserId(), getUserLogin(), createForm.getFileType()); createForm.setAnnouncement(new AnnouncementForm()); createForm.getAnnouncement().setAnnouncementNo(announcementNoStr); // thong tin doanh nghiep createForm.getAnnouncement().setBusinessAddress(bus.getBusinessAddress()); createForm.getAnnouncement().setBusinessFax(bus.getBusinessFax()); createForm.getAnnouncement().setBusinessName(bus.getBusinessName()); createForm.getAnnouncement().setBusinessTelephone(bus.getBusinessTelephone()); createForm.getAnnouncement().setBusinessEmail(bus.getUserEmail()); createForm.getAnnouncement().setBusinessLicence(bus.getBusinessLicense()); // ho so cap lai 7-11 createForm.setReIssueForm(new ReIssueFormForm()); createForm.getReIssueForm().setBusinessName(bus.getBusinessName()); createForm.getReIssueForm().setIdentificationNumber(bus.getBusinessLicense()); createForm.getReIssueForm().setAddress(bus.getBusinessAddress()); createForm.getReIssueForm().setEmail(bus.getUserEmail()); createForm.getReIssueForm().setTelephone(bus.getBusinessTelephone()); createForm.getReIssueForm().setFax(bus.getBusinessFax()); //set thong tin tu excel try { if (businessTaxCode != null && user.getUserName().equals(businessTaxCode.toString())) { if (matchingTarget != "" && matchingTarget != null) { createForm.getAnnouncement().setMatchingTarget(matchingTarget.toString()); } createForm.getAnnouncement().setProductName(productName.toString()); createForm.getAnnouncement().setManufactureAddress(manufactorAddress.toString()); createForm.getAnnouncement().setManufactureName(manufactorName.toString()); createForm.getAnnouncement().setManufactureTel(manufactorTel.toString()); createForm.getAnnouncement().setManufactureFax(manufactorFax.toString()); createForm.getAnnouncement().setManufactureEmail(manufactorEmail.toString()); createForm.getAnnouncement().setNationName(nationName.toString()); createForm.getAnnouncement().setSigner(signer.toString()); createForm.getAnnouncement() .setNationCompanyAddress(nationCompanyAddress.toString()); createForm.getAnnouncement().setNationCompanyName(nationCompanyName.toString()); createForm.getAnnouncement().setAssessmentMethod(assessmentMethod.toString()); if (pushlishDate.toString() != null && pushlishDate.toString().length() > 0) { createForm.getAnnouncement().setPublishDate(DateTimeUtils .convertStringToTime(pushlishDate.toString(), "dd/MM/yyyy")); } createForm.getAnnouncement().setAnnouncementNo(annoucementNo.toString()); //tab thong tin chi tiet createForm.setDetailProduct(new DetailProductForm()); createForm.getDetailProduct().setProductNo(productNo.toString()); createForm.getDetailProduct().setProductStatus(productStatus.toString()); createForm.getDetailProduct().setProductColor(productColor.toString()); createForm.getDetailProduct().setProductSmell(productSmell.toString()); createForm.getDetailProduct().setProductOtherStatus(productOtherstatus.toString()); item = cdhe.findCategoryByName("SP", productType.toString()); if (item != null) { createForm.getDetailProduct().setProductType(item.getCategoryId()); } else { err += "Danh mc " + productType.toString() + " khng chnh xc, "; } createForm.getDetailProduct().setOtherTarget(otherTarget.toString()); createForm.getDetailProduct().setComponents(component.toString()); createForm.getDetailProduct().setTimeInUse(timeinuse.toString()); createForm.getDetailProduct().setUseage(useage.toString()); createForm.getDetailProduct().setObjectUse(objectInUse.toString()); createForm.getDetailProduct().setGuideline(guideline.toString()); //createForm.getDetailProduct().setPackageRecipe(packageRecipe.toString()); createForm.getDetailProduct().setPackateMaterial(packageMaterial.toString()); createForm.getDetailProduct().setProductionProcess(productProcess.toString()); createForm.getDetailProduct() .setCounterfeitDistinctive(counterfeitDistinctive.toString()); createForm.getDetailProduct().setOrigin(origin.toString()); if (signDate.toString() != null && signDate.toString().length() > 0) { createForm.getDetailProduct().setSignDate( DateTimeUtils.convertStringToTime(signDate.toString(), "dd/MM/yyyy")); } createForm.getDetailProduct().setSigner(signer_productdetails.toString()); createForm.getDetailProduct() .setChemicalTargetUnwanted(chemicalTargetUnwanted.toString()); createForm.setStatusExcel( err += "Thm mi bn cng b hp quy thnh cng "); } else { createForm.setStatusExcel(err += "M s thu khng chnh xc "); } } catch (Exception ex) { // log.error(parseException); LogUtil.addLog(ex);//binhnt sonar a160901 createForm.setStatusExcel( err += "Thm mi bn cng b hp quy khng thnh cng "); } } } } } CategoryDAOHE ctdhe = new CategoryDAOHE(); Category cate = ctdhe.findCategoryByTypeAndCode("SP", "TPCN"); Category cateTL = ctdhe.findCategoryByTypeAndCode("SP", "TL"); List<Category> cate1 = ctdhe.findCategoryByTypeAndCodeNew("SP", "DBT"); String dbtId = ""; for (int i = 0; i < cate1.size(); i++) { dbtId += cate1.get(i).getCategoryId().toString() + ";"; } Long tpcnId = cate.getCategoryId(); Long tlId = cateTL.getCategoryId(); FeeDAOHE fdhe1 = new FeeDAOHE(); Fee findfee1 = fdhe1.findFeeByCode("TPDB"); Long priceTPDB = findfee1.getPrice(); Fee findfee2 = fdhe1.findFeeByCode("TPCN"); Long priceTPCN = findfee2.getPrice(); Fee findfee3 = fdhe1.findFeeByCode("TPK"); Long priceETC = findfee3.getPrice(); getRequest().setAttribute("dbtId", dbtId); getRequest().setAttribute("tpcnId", tpcnId); getRequest().setAttribute("tlId", tlId); getRequest().setAttribute("priceTPCN", priceTPCN); getRequest().setAttribute("priceTPDB", priceTPDB); getRequest().setAttribute("priceETC", priceETC); return strReturn; }