List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create
public static Workbook create(File file) throws IOException, EncryptedDocumentException
From source file:com.rodrigodev.xgen4j_table_generator.test.common.assertion.excel.conditions.ExcelFile.java
License:Open Source License
@Override public boolean matches(InputStream actualInputStream) { boolean result = true; try {// ww w.ja v a 2 s . c o m try (Workbook expectedWb = WorkbookFactory.create(expectedInputStream)) { Sheet expectedSheet = expectedWb.getSheetAt(0); try (Workbook actualWb = WorkbookFactory.create(actualInputStream)) { Sheet actualSheet = actualWb.getSheetAt(0); int expectedRowCount = expectedSheet.getLastRowNum(); for (int r = 0; r <= expectedRowCount; r++) { Row expectedRow = expectedSheet.getRow(r); Row actualRow = actualSheet.getRow(r); if (actualRow == null) { actualRow = actualSheet.createRow(r); } int expectedCellCount = expectedRow.getLastCellNum(); for (int c = 0; c < expectedCellCount; c++) { Cell expectedCell = expectedRow.getCell(c, Row.CREATE_NULL_AS_BLANK); Cell actualCell = actualRow.getCell(c, Row.CREATE_NULL_AS_BLANK); if (expectedCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { assertThat(actualCell.getNumericCellValue()) .isEqualTo(expectedCell.getNumericCellValue(), offset(0.00001)); } else { expectedCell.setCellType(Cell.CELL_TYPE_STRING); actualCell.setCellType(Cell.CELL_TYPE_STRING); assertThat(actualCell.getStringCellValue()) .isEqualTo(expectedCell.getStringCellValue()); } } } } } } catch (AssertionError error) { describedAs(error.getMessage()); result = false; } catch (Exception e) { throw new RuntimeException(e); } return result; }
From source file:com.sfs.ucm.controller.HelpContentAction.java
License:Open Source License
/** * Extract help content//from w w w. j ava2s . com * * @param buf * byte array representing help content file * @throws UCMException */ private void extractHelpContent(byte[] buf) throws UCMException { try { InputStream inp = new ByteArrayInputStream(buf); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); Iterator<Row> iter = sheet.iterator(); Cell cell = null; Row row = null; // header rows if (iter.hasNext()) { row = iter.next(); // table title row = iter.next(); // column headers } if (iter.hasNext()) { while (iter.hasNext()) { // process records row = iter.next(); // help key cell = row.getCell(0); if (cell != null) { String key = cell.getStringCellValue(); // help content cell = row.getCell(1); String contents = cell.getStringCellValue(); // log it Object[] values = new Object[3]; values[0] = row.getRowNum() + 1; // display as one-based values[1] = key; values[2] = StringUtils.abbreviate(contents, 20); logger.info("Processing row {}; contents: {};{}", values); // construct the help content object Help theHelpItem = new Help(key, contents); // if help item already exists then just update its contents otherwise add record int ndx = this.helpItems.indexOf(theHelpItem); if (ndx == -1) { this.helpItems.add(theHelpItem); logger.info("Added Help Item {}", theHelpItem.getKeyword()); // persist the object em.persist(theHelpItem); } else { Help tmp = this.helpItems.get(ndx); tmp.setContent(contents); // persist the object em.persist(tmp); logger.info("Updated Help Item {}", tmp.getKeyword()); } } } } // done inp.close(); } catch (InvalidFormatException e) { logger.error(e.getMessage()); throw new UCMException(e); } catch (IOException e) { logger.error(e.getMessage()); throw new UCMException(e); } }
From source file:com.smanempat.view.ExcelReading.java
/** * @param args the command line arguments *///from w w w. j a v a 2 s. c o m public static void main(String[] args) { InputStream inp = null; try { inp = new FileInputStream("C:\\Users\\Zakaria\\Documents\\Test\\2009_15112015_1615.xlsx\\"); Workbook wb = WorkbookFactory.create(inp); for (int i = 0; i < wb.getNumberOfSheets(); i++) { System.out.println(wb.getSheetAt(i).getSheetName()); echoAsCSV(wb.getSheetAt(i)); } } catch (InvalidFormatException ex) { Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex); } finally { try { inp.close(); } catch (IOException ex) { Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:com.softtek.mdm.web.institution.UserController.java
/** * /*from w w w. j ava 2 s. c om*/ * * @param request * @param response * @return * @throws Exception */ @Log(operateType = "logs.usercontroller.member.type.import", operateContent = "logs.usercontroller.content.member.import") @RequestMapping(value = "/importusers", method = RequestMethod.POST) @ResponseBody public Map<String, Object> importvirmember(MultipartFile files, HttpServletRequest request, HttpServletResponse response, HttpSession session) throws Exception { Map<String, Object> messages = new HashMap<String, Object>(); // xlsxlsx Integer filetype = 0; if (files.getOriginalFilename().endsWith("xls")) { filetype = 03; } if (files.getOriginalFilename().endsWith("xlsx")) { filetype = 07; } // ?id OrganizationModel organization = (OrganizationModel) session .getAttribute(SessionStatus.SOFTTEK_ORGANIZATION.toString()); Integer orgid = organization.getId(); // ??? ManagerModel managerModel = (ManagerModel) session.getAttribute(SessionStatus.SOFTTEK_MANAGER.toString()); List<Integer> idlist = new ArrayList<Integer>(); // createById Integer managerId = managerModel.getId(); @SuppressWarnings("unchecked") List<StructureModel> list = (List<StructureModel>) session .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString()); NodeModel nodes = treeManager.bulidTreeContainUser(list, organization.getId(), managerModel.getUser()); // ?? if (managerModel.getUser() != null) { @SuppressWarnings("unchecked") List<StructureModel> list1 = (List<StructureModel>) session .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString()); for (StructureModel s : list1) { idlist.add(s.getId()); } } else { idlist = null; } InputStream ins = files.getInputStream(); Workbook wb = null; wb = WorkbookFactory.create(ins); ins.close(); Sheet sheet = null; // 3.Excel if (filetype == 03) { sheet = (HSSFSheet) wb.getSheetAt(0); } if (filetype == 07) { sheet = (XSSFSheet) wb.getSheetAt(0); } // int trLength = sheet.getLastRowNum(); Row rowtest = sheet.getRow(0); Cell celltest1 = rowtest.getCell(0); Cell celltest2 = rowtest.getCell(1); Cell celltest3 = rowtest.getCell(2); if (celltest1 != null && celltest2 != null && celltest3 != null) { celltest1.setCellType(Cell.CELL_TYPE_STRING); celltest2.setCellType(Cell.CELL_TYPE_STRING); celltest3.setCellType(Cell.CELL_TYPE_STRING); if (trLength > 0) { if (celltest1.getStringCellValue() .equals(messageSource.getMessage("web.institution.usercontroller.export.excel.label1", null, LocaleContextHolder.getLocale())) && celltest2.getStringCellValue() .equals(messageSource.getMessage( "web.institution.usercontroller.export.excel.label2", null, LocaleContextHolder.getLocale())) && celltest3.getStringCellValue() .equals(messageSource.getMessage( "web.institution.usercontroller.export.excel.label3", null, LocaleContextHolder.getLocale()))) { List<ExcelInsertUserModel> excelList = new ArrayList<ExcelInsertUserModel>(); List<String> usertlist = (List<String>) userService.findAllMember(orgid); String erromessages = ""; int rownumber = 0; for (int i = 1; i <= trLength; i++) { // Excel Row row = sheet.getRow(i); ExcelInsertUserModel excelInsertUserModel = new ExcelInsertUserModel(); for (int j = 0; j <= 10; j++) { // Excel? if (row != null) { Cell newcell = row.getCell(j); String cell = ""; if (newcell != null) { newcell.setCellType(Cell.CELL_TYPE_STRING); if (StringUtils.isNotBlank(StringUtils.trim(newcell.getStringCellValue()))) { // cell = // StringUtils.trim(newcell.getStringCellValue()); cell = newcell.getStringCellValue().replaceAll(" ", ""); } } if (j == 0) { // ??? if (StringUtils.isNotBlank(cell)) { String groupname[] = cell.split("/"); if (null != groupname) { /* */ int deep = 0; List<NodeModel> nodeList = null; StructureModel structure = null; NodeModel temp = nodes; do { if (temp.getTags().getParent() == null) { nodeList = temp.getNodes(); for (NodeModel n : nodeList) { if (n.getTags().getName().equals(groupname[deep])) { deep++; temp = n; break; } } if (deep == 0) { break; } } else { int deep1 = deep; for (NodeModel n : nodeList) { if (n.getTags().getName().equals(groupname[deep])) { deep++; temp = n; break; } } if (deep1 == deep) { break; } } if (deep == groupname.length) { structure = temp.getTags(); break; } } while ((nodeList = temp.getNodes()) != null); if (structure == null) { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.nullgroup", args, LocaleContextHolder.getLocale()); } else { if (idlist != null) { int idlistsize1 = idlist.size(); idlist.remove(structure.getId()); int idlistsize2 = idlist.size(); if (idlistsize1 != idlistsize2) { excelInsertUserModel.setGroup_id(structure.getId()); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.group2", args, LocaleContextHolder.getLocale()); } idlist.add(structure.getId()); } else { excelInsertUserModel.setGroup_id(structure.getId()); // excelList.add(excelInsertUserModel); } } } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.group3", args, LocaleContextHolder.getLocale()); } } // ?? if (j == 1) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 20) { boolean reuser = usertlist.remove(cell); if (reuser == true) { excelInsertUserModel.setUser_name(cell); rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.username", args, LocaleContextHolder.getLocale()); usertlist.add(cell); } else { excelInsertUserModel.setUser_name(cell); } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.username1", args, LocaleContextHolder.getLocale()); } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.username2", args, LocaleContextHolder.getLocale()); } } // ?? if (j == 2) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 20) { excelInsertUserModel.setReal_name(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.realname", args, LocaleContextHolder.getLocale()); } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.realname1", args, LocaleContextHolder.getLocale()); } } // if (j == 3) { if (StringUtils.isNotBlank(cell)) { String regex = "^\\s*\\w+(?:\\.{0,1}[\\w-]+)*@[a-zA-Z0-9]+(?:[-.][a-zA-Z0-9]+)*\\.[a-zA-Z]+\\s*$"; if (cell.matches(regex) == true) { excelInsertUserModel.setEmail(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.email", args, LocaleContextHolder.getLocale()); } } } // if (j == 4) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setMark(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.mark", args, LocaleContextHolder.getLocale()); } } } // ?? if (j == 5) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setPhone((cell)); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.phone", args, LocaleContextHolder.getLocale()); } } } // if (j == 6) { if (StringUtils.isNotBlank(cell)) { if (cell.equals( messageSource.getMessage("web.institution.usercontroller.sex.woman", null, LocaleContextHolder.getLocale()))) { excelInsertUserModel.setGender(0); } else { excelInsertUserModel.setGender(1); } } else { excelInsertUserModel.setGender(1); } } // ?? if (j == 7) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setSign(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.sign", args, LocaleContextHolder.getLocale()); } } } // ?? if (j == 8) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setAddress(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.address", args, LocaleContextHolder.getLocale()); } } } // ? if (j == 9) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setOffice_phone(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.office_phone", args, LocaleContextHolder.getLocale()); } } } // ?? if (j == 10) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setPosition(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.position", args, LocaleContextHolder.getLocale()); } } } } } excelList.add(excelInsertUserModel); } // ?? ???? List<String> yqs = new ArrayList<String>(); Set<String> testreusername = new HashSet<String>(yqs); for (int k = 0; k < excelList.size(); k++) { if (excelList.get(k).getUser_name() != null) { testreusername.add(excelList.get(k).getUser_name()); yqs.add(excelList.get(k).getUser_name()); } } if (testreusername.size() < yqs.size()) { erromessages = messageSource.getMessage( "web.institution.usercontroller.excel.erro.erromessages", null, LocaleContextHolder.getLocale()); messages.put("messages", erromessages); } else { // ? if (erromessages.length() > 0) { erromessages.substring(0, erromessages.length() - 1); messages.put("messages", erromessages); } else { Md5PasswordEncoder md5PasswordEncoder = new Md5PasswordEncoder(); String password = md5PasswordEncoder.encodePassword("123456", null); if (excelList.size() > 0) { for (int q = 0; q < excelList.size(); q++) { Integer policyId = structureService .queryPolicyIdById(excelList.get(q).getGroup_id()); excelList.get(q).setPolicy_id(policyId); excelList.get(q).setOrgid(orgid); excelList.get(q).setPassword(password); excelList.get(q).setCreate_by(managerId); } userService.importUsers(excelList); messages.put("success", "success"); } } } } else { String nullmessages = messageSource.getMessage( "web.institution.usercontroller.excel.erromodel.erromessages", null, LocaleContextHolder.getLocale()); messages.put("messages", nullmessages); } } else { String nullmessages = messageSource.getMessage( "web.institution.usercontroller.excel.erro.nullmessages", null, LocaleContextHolder.getLocale()); messages.put("messages", nullmessages); } } else { String nullmessages = messageSource.getMessage( "web.institution.usercontroller.excel.erromodel.erromessages", null, LocaleContextHolder.getLocale()); messages.put("messages", nullmessages); } return messages; }
From source file:com.ssic.education.provider.controller.ProSupplierController.java
/** * ??//from ww w .ja v a 2s.c om * * @param file * @param request * @param response * @return * @author chenminghai * @throws IOException */ @RequestMapping("/supplierImport") @ResponseBody public Json supplierImport(MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws IOException { Json j = new Json(); SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME); // ???id String supplierId = info.getSupplierId(); String errorMsg = null; Map<String, Map<ProSupplierReceiver, ProSupplier>> map = new HashMap(); Set<String> set = new HashSet(); try (Workbook wb = WorkbookFactory.create(file.getInputStream());) { Sheet sheet = wb.getSheetAt(0); Date now = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d"); if (sheet == null) { return null; } for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) { if (errorMsg != null) { break; } Map<ProSupplierReceiver, ProSupplier> suppliers = new HashMap(); ProSupplier supplier = null; ProSupplierReceiver psr = null; Row row = sheet.getRow(rowNum); int n = 0; for (int i = 0; i < row.getLastCellNum(); i++) { if (errorMsg != null) { break; } Cell cell = row.getCell(i); String value = ParseExcelUtil.getStringCellValue(cell); if (value != null) { value = value.trim(); } if (i == 0) { // ?? if (StringUtils.isBlank(value)) { errorMsg = "" + (rowNum + 1) + "???????"; break; } ProSupplier s = supplierService.findProSupplierByName(value, supplierId); if (s != null) { errorMsg = "" + (rowNum + 1) + "??????"; break; } if (map.get(value) != null) { errorMsg = "" + (rowNum + 1) + "????????"; break; } supplier = new ProSupplier(); supplier.setSupplierName(value); supplier.setCreateTime(now); supplier.setUpdater(info.getId()); supplier.setLastUpdateTime(now); supplier.setStat(1); } else if (i == 1) { // ??? if (StringUtils.isBlank(value)) { errorMsg = "" + (rowNum + 1) + "??????"; break; } supplier.setAddress(value); // } else if (i == 2) { // // ? // if (StringUtils.isBlank(value)) { // n += 1; // break; // } // supplier.setFoodServiceCode(value); // } else if (i == 2) { // if (StringUtils.isBlank(value)) { // n += 1; // break; // } // supplier.setFoodBusinessCode(value); } else if (i == 2) { if (StringUtils.isBlank(value)) { n += 1; continue; } supplier.setFoodCirculationCode(value); } else if (i == 3) { if (StringUtils.isBlank(value)) { if (n == 1) { errorMsg = "" + (rowNum + 1) + "???"; } break; } supplier.setFoodProduceCode(value); // } else if (i == 6) { // if (StringUtils.isBlank(value)) { // if (n == 4) { // errorMsg = "" + (rowNum + 1) // + "?????"; // } // break; // } // supplier.setBusinessLicense(value); // } else if (i == 7 && !StringUtils.isBlank(value)) { // // ??? // int x = srService.findBySupplierCode(value, supplierId); // if (x != 0) { // errorMsg = "" + (rowNum + 1) + "?????"; // break; // } // if(value!=null&& !StringUtils.isBlank(value)){ // int s=set.size(); // set.add(value); // if(s==set.size()){ // errorMsg = "" + (rowNum + 1) + "???????"; // break; // } // } // psr = new ProSupplierReceiver(); // psr.setSupplierCode(value); } else if (i == 4) { // ? if (StringUtils.isBlank(value)) { errorMsg = "" + (rowNum + 1) + "????"; break; } supplier.setCorporation(value); } else if (i == 5) { // ?? if (StringUtils.isBlank(value)) { errorMsg = "" + (rowNum + 1) + "????"; break; } supplier.setContactWay(value); } } if (supplier != null && errorMsg == null) { supplier.setId(UUID.randomUUID().toString()); supplier.setSupplierType(0); supplier.setReviewed((byte) 1); if (psr == null) { psr = new ProSupplierReceiver(); } psr.setId(UUID.randomUUID().toString()); psr.setSupplierId(supplier.getId()); psr.setReceiverId(supplierId); psr.setCreateTime(new Date()); psr.setLastUpdateTime(psr.getCreateTime()); suppliers.put(psr, supplier); map.put(supplier.getSupplierName(), suppliers); } } } catch (EncryptedDocumentException | InvalidFormatException e) { errorMsg = "Excel??"; } if (errorMsg != null) { j.setMsg(errorMsg); j.setSuccess(false); } else { int r = supplierService.importSupplier(map); j.setMsg("?" + r + "??"); j.setSuccess(true); } return j; }
From source file:com.ssic.education.provider.controller.WaresController.java
@RequestMapping(value = "/import") @ResponseBody/*from w w w . j a v a 2 s .c o m*/ /** * ?excel * * @param file * @param request * @param response * @return * @author zhangjiwei * @since 2016.5.21 */ public Json importExcel(@RequestParam("filename") MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws IOException { Json j = new Json(); SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME); // ?id String supplierId = info.getSupplierId(); String errorMsg = null; // ?excellist List<ProWares> list = new ArrayList(); Set<String> set = new HashSet(); // ?excel try (Workbook wb = WorkbookFactory.create(file.getInputStream());) { Sheet sheet = wb.getSheetAt(0); if (sheet == null) { return null; } Date now = new Date(); for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) { if (errorMsg != null) { break; } ProWares dto = new ProWares(); Row row = sheet.getRow(rowNum); for (int i = 0; i < row.getLastCellNum(); i++) { if (errorMsg != null) { break; } Cell cell = row.getCell(i); String value = ParseExcelUtil.getStringCellValue(cell); if (value != null) { value = value.trim(); } if (i == 0) { if (StringUtils.isBlank(value)) { errorMsg = "" + (rowNum + 1) + "?????"; break; } // ??? dto.setWaresName(value); } else if (i == 1) { if (StringUtils.isBlank(value)) { errorMsg = "" + (rowNum + 1) + "??????"; break; } // ? dto.setAmountUnit(value); } else if (i == 2) { // ? dto.setSpec(value); } else if (i == 3) { if (StringUtils.isBlank(value)) { errorMsg = "" + (rowNum + 1) + "???"; break; } // ? try { dto.setWaresType(ProductClass.fromName(value)); if (dto.getWaresType() == null) { errorMsg = "" + (rowNum + 1) + "???"; break; } } catch (Exception e) { errorMsg = "" + (rowNum + 1) + "???"; break; } } else if (i == 4) { // ? if (StringUtils.isBlank(value)) { errorMsg = "" + (rowNum + 1) + "????"; break; } dto.setManufacturer(value); // ??? ProWares pw = waresService.findProWarsByNameSpecManu(dto.getWaresName(), dto.getAmountUnit(), dto.getManufacturer(), supplierId); if (pw != null) { errorMsg = "" + (rowNum + 1) + "???"; break; } String mark = dto.getWaresName() + "," + dto.getAmountUnit() + "," + dto.getManufacturer(); int m = set.size(); set.add(mark); if (m == set.size()) { errorMsg = "" + (rowNum + 1) + "?????"; break; } // } else if (i == 4 && StringUtils.isNotBlank(value)) { // // ?? // dto.setEnName(value); // } else if (i == 5 && StringUtils.isNotBlank(value)) { // // ??? // dto.setBarCode(value); // } else if (i == 6 && StringUtils.isNotBlank(value)) { // // ?? // dto.setCustomCode(value); } else if (i == 5 && StringUtils.isNotBlank(value)) { // ? try { dto.setShelfLife(Integer.parseInt(value)); } catch (Exception e) { errorMsg = "" + (rowNum + 1) + "?????"; break; } } else if (i == 6) { // ??? if (dto.getShelfLife() != null) { if (StringUtils.isBlank(value)) { errorMsg = "" + (rowNum + 1) + "????"; break; } dto.setUnit(value); } else { if (StringUtils.isNotBlank(value)) { dto.setShelfLife(0); dto.setUnit(value); } } } else if (i == 7 && StringUtils.isNotBlank(value)) { // dto.setPlace(value); } } if (errorMsg != null) { break; } dto.setSupplierId(supplierId); dto.setWay(0); dto.setDishes(false); dto.setCreator(info.getId()); dto.setCreateTime(now); dto.setUpdater(info.getId()); dto.setLastUpdateTime(now); dto.setStat(1); list.add(dto); } } catch (EncryptedDocumentException | InvalidFormatException e) { errorMsg = "Excel??"; } if (errorMsg != null) { // TODO ??? j.setMsg(errorMsg); j.setSuccess(false); return j; } else { // ? waresService.addProWares(list); j.setMsg("??"); j.setSuccess(true); return j; } }
From source file:com.ssy.havefun.f3d.F3DDaoImpl.java
@Override public List<F3DEntity> doImport() throws IOException { List<F3DEntity> list = new ArrayList<>(); Workbook wb;//ww w. j a va 2s . com try { wb = WorkbookFactory.create(new File("C:/Users/jsun/Downloads/2015.xls")); Sheet sheet = wb.getSheetAt(2); for (int i = 0; i < sheet.getLastRowNum() + 1; i++) { Row row = sheet.getRow(i); F3DEntity f3d = new F3DEntity(); for (int j = 0; j < 10; j++) { Cell cell = row.getCell(j); String value = getCellValue(cell); switch (j) { case 0: f3d.setsId(Integer.parseInt(value)); break; case 1: f3d.setDate(value.replaceAll("-", "").substring(0, 8)); break; case 2: char[] values = value.toCharArray(); f3d.setFirst(Integer.parseInt(String.valueOf(values[0]))); f3d.setSecond(Integer.parseInt(String.valueOf(values[1]))); f3d.setThird(Integer.parseInt(String.valueOf(values[2]))); break; case 3: break; case 4: f3d.setSale(Integer.parseInt(value)); break; case 5: f3d.setDirect(Integer.parseInt(value)); break; case 6: break; case 7: f3d.setThirdCombination(Integer.parseInt(value)); break; case 8: break; case 9: f3d.setSixCombination(Integer.parseInt(value)); break; } } System.out.println(f3d.toString()); list.add(f3d); } } catch (InvalidFormatException ex) { Logger.getLogger(F3DDaoImpl.class.getName()).log(Level.SEVERE, null, ex); } return list; }
From source file:com.stam.excellatin.ExcelLatin.java
public static void main(String[] args) { List<String> options = new ArrayList<>(); int startIndex = 0; for (String arg : args) { if (validOptions.contains(arg)) { options.add(arg);//from w ww. java 2 s . c o m startIndex++; } } if (args[0].equals("-h") || args.length < 3) { System.out.println("usage: ExcelLatin [options] filenameIn filenameOut columnNames..."); System.out.println("options:"); System.out.println("\t-L\tto Latin (default)"); System.out.println("\t-G\tto Greek"); System.out.println("\t-d\tdon't deaccent"); System.out.println("\t-h\thelp"); } else { boolean greekToLatin = false; boolean latinToGreek = false; Transliterator transliterator = null; if ((!options.contains("-L") && !options.contains("-G")) || options.contains("-L")) { transliterator = Transliterator.getInstance("Greek-Latin/UNGEGN"); System.out.println("\nTransliterating Greek to Latin"); greekToLatin = true; } else if (options.contains("-G")) { transliterator = Transliterator.getInstance("Latin-Greek/UNGEGN"); System.out.println("\nTransliterating Latin to Greek"); latinToGreek = true; } if (transliterator == null) { System.out.println("Not a valid option for the transliteration language"); return; } boolean deAccent = true; if (options.contains("-d")) { deAccent = false; System.out.println("Will not deaccent"); } String fileNameIn = args[startIndex]; String fileNameOut = args[startIndex + 1]; List<String> columnNames = new ArrayList<>(); System.out.println("\nColumns to transliterate\n---------------------------"); for (int i = startIndex + 2; i < args.length; i++) { columnNames.add(args[i]); System.out.println(args[i]); } System.out.println("\n"); try { File file = new File(fileNameIn); if (!file.exists()) { System.out.println("The file " + fileNameIn + " was not found"); return; } Map<String, String> mapTransformations = new HashMap<>(); Scanner sc = new Scanner(new FileReader("map.txt")); while (sc.hasNextLine()) { String greekEntry = sc.next(); String latinEntry = sc.next(); if (greekToLatin) { mapTransformations.put(greekEntry, latinEntry); } else if (latinToGreek) { mapTransformations.put(latinEntry, greekEntry); } } DataFormatter formatter = new DataFormatter(); Workbook wb = WorkbookFactory.create(file); Workbook newWb = null; if (wb instanceof HSSFWorkbook) { newWb = new HSSFWorkbook(); } else if (wb instanceof XSSFWorkbook) { newWb = new XSSFWorkbook(); } FileOutputStream fileOut = new FileOutputStream(fileNameOut); if (newWb != null) { Sheet sheetOut = newWb.createSheet(); Sheet sheet = wb.getSheetAt(0); List<Integer> idxs = new ArrayList<>(); Row row = sheet.getRow(0); for (Cell cell : row) { String cellVal = formatter.formatCellValue(cell); if (cellVal == null || cellVal.trim().equals("")) { break; } if (columnNames.contains(cell.getStringCellValue())) { idxs.add(cell.getColumnIndex()); } } for (Row rowIn : sheet) { Row rowOut = sheetOut.createRow(rowIn.getRowNum()); if (rowIn.getRowNum() == 0) { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cell.getStringCellValue()); } } else { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); String cellVal = formatter.formatCellValue(cell); String cellNewVal = cellVal; if (idxs.contains(cell.getColumnIndex()) && cellVal != null) { if (mapTransformations.containsKey(cellVal)) { cellNewVal = mapTransformations.get(cellVal); } else { if (deAccent) { cellNewVal = deAccent(transliterator.transform(cellVal)); } else { cellNewVal = transliterator.transform(cellVal); } } } Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cellNewVal); } } } System.out.println("Finished!"); newWb.write(fileOut); fileOut.close(); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(ExcelLatin.class.toString()).log(Level.SEVERE, null, ex); } } }
From source file:com.streamsets.pipeline.lib.parser.excel.TestWorkbookParser.java
License:Apache License
private Workbook createWorkbook(String filePath) throws IOException, InvalidFormatException { return WorkbookFactory.create(getFile(filePath)); }
From source file:com.streamsets.pipeline.lib.parser.excel.TestWorkbookParser.java
License:Apache License
@Test public void testParseHandlesStartingFromANonZeroOffset() throws IOException, InvalidFormatException, DataParserException { InputStream file = getFile("/excel/TestOffset.xlsx"); Workbook workbook = WorkbookFactory.create(file); WorkbookParserSettings settings = WorkbookParserSettings.builder().withHeader(ExcelHeader.IGNORE_HEADER) .build();//w ww . jav a 2s . c o m WorkbookParser parser = new WorkbookParser(settings, getContext(), workbook, "Sheet2::2"); Record firstContentRow = parser.parse(); LinkedHashMap<String, Field> contentMap = new LinkedHashMap<>(); for (int i = 0; i <= 2; i++) { contentMap.put(String.valueOf(i), Field.create(new BigDecimal(i + 4))); } Field expected = Field.createListMap(contentMap); assertEquals(expected, firstContentRow.get()); }