List of usage examples for org.apache.poi.ss.usermodel Sheet getRow
Row getRow(int rownum);
From source file:com.simopuve.helper.ReadPVDFromFile.java
private static List<PDVRow> getRowsFromWorkbook(Sheet sheet) { Map rowMap = getPDVRowPropertiesCoordinatesByName(); List<PDVRow> PDVRowList = new ArrayList<>(); DataFormatter formatter = new DataFormatter(); PropertyCoordinates tmpCoordinate;/*from w ww . j a va2 s. c om*/ String text; sheet.getPhysicalNumberOfRows(); //TODO revisar si es siempre as int longitud = (sheet.getPhysicalNumberOfRows() - FIRST_ROW) - 2; int endRows = FIRST_ROW + longitud; int i = FIRST_ROW; String personNum = getTextFromCell(FIRST_ROW, 0, formatter, sheet, sheet.getRow(FIRST_ROW)); boolean isPersonNumberEmpty = personNum.isEmpty(); while (!isPersonNumberEmpty) { Row row = sheet.getRow(i); PDVRow PDVRow = new PDVRow(); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceBrand"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceBrand(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceModel"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceModel(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("contractType"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setContractType(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceMode"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceMode(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("additionalCharacteristics"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setAdditionalCharacteristics(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("planRating"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPlanRating(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceRating"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceRating(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("portabilityChange"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPortabilityChange(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("portabilityChangeReason"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPortabilityChangeReason(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("personNumber"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPersonNumber(Integer.parseInt(text)); isPersonNumberEmpty = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, sheet.getRow(i + 1)).isEmpty(); tmpCoordinate = (PropertyCoordinates) rowMap.get("expressRefillValue"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); String tmpTxt = text.replace("$", ""); tmpTxt = tmpTxt.replaceAll("\\s+", ""); tmpTxt = tmpTxt.replaceAll(",", ""); tmpTxt = tmpTxt.replaceAll(".", ""); Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.INFO, "valor a convertir:" + tmpTxt); if (tmpTxt.isEmpty()) PDVRow.setExpressRefillValue(0); else if (tmpTxt == "" || tmpTxt == null) PDVRow.setExpressRefillValue(0); else PDVRow.setExpressRefillValue(Integer.parseInt(tmpTxt)); tmpCoordinate = (PropertyCoordinates) rowMap.get("boughtCard"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setBoughtCard(!("No".equals(text))); tmpCoordinate = (PropertyCoordinates) rowMap.get("boughtChip"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setBoughtChip(!("No".equals(text))); tmpCoordinate = (PropertyCoordinates) rowMap.get("boughtAccessory"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setBoughtAccessory(!("No".equals(text))); PDVRowList.add(PDVRow); Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.INFO, PDVRow.toString()); i++; } Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.INFO, "Tama\u00f1o de rows: {0}", PDVRowList.size()); return PDVRowList; }
From source file:com.smanempat.view.ExcelReading.java
public static void echoAsCSV(Sheet sheet) { Row row = null;// ww w . j a v a2 s .co m for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getLastCellNum(); j++) { System.out.print("\"" + row.getCell(j) + "\";"); } System.out.println(); } }
From source file:com.smegi.bonusi.model.Excel.java
public void getExcelBonuses(int month) { Calendar calendar = Calendar.getInstance(); calendar.set(2016, month - 1, Calendar.DAY_OF_MONTH); try {/* www. ja va2 s. com*/ FileInputStream inputStream = new FileInputStream(new File(path)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(month - 1); for (int i = 2; i < sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); String username = ""; int bonuses = 0; for (int j = 1; j < row.getLastCellNum(); j++) { //getting username Cell cell = row.getCell(j); if (j == 1) { username = cell.toString(); continue; } if (cell.toString() != "") { bonuses++; } } // updating user in userslist for (User user : users) { if (user.getName().equalsIgnoreCase(username)) { // System.out.println("Found username in base: " + username); // System.out.println(" - adding bonuses: " + bonuses); StringBuilder sb = new StringBuilder(); sb.append(calendar.get(Calendar.YEAR)).append("-").append((calendar.get(Calendar.MONTH))); user.addExcelBonusi(sb.toString(), bonuses); break; } } } } catch (FileNotFoundException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.softtek.mdm.web.institution.UserController.java
/** * //from w ww . ja v a2 s. c o m * * @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.solidmaps.webapp.report.TimesheetDemo.java
License:Apache License
public void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);// w ww .jav a2s.co m sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } //row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); //set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } //finally set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 30 * 256); //30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); //6 characters wide } sheet.setColumnWidth(10, 10 * 256); //10 characters wide // Write the output to a file String file = "/home/brunorocca/Desktop/mapa_teste.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java
License:Open Source License
public HashMap<String, String> listColumnNames(InputStream is) throws IOException, UnsupportedOperationException { HashMap<String, String> hm = new LinkedHashMap<>(); Workbook wb = createWorkbook(is);/* www. j av a 2 s .c o m*/ if (wb.getNumberOfSheets() == 0) throw new UnsupportedOperationException("At least one sheet is required"); Sheet sh = getSheet(wb); if (sh == null) throw new UnsupportedOperationException("Unable to find desired sheet"); String name = null; Row hrow = sh.getRow(headersRow - 1); for (Cell cell : hrow) { if (headersRow == firstDataRow) { name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex()); } else { name = fmt.formatCellValue(cell); if (StringUtils.isBlank(name)) name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex()); } hm.put(name.toLowerCase(), name); } return hm; }
From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java
License:Open Source License
public HashMap<String, Integer> listColumnIndexes(InputStream is) throws IOException, UnsupportedOperationException { HashMap<String, Integer> hm = new LinkedHashMap<>(); Workbook wb = createWorkbook(is);/*from ww w .j ava 2 s. co m*/ if (wb.getNumberOfSheets() == 0) throw new UnsupportedOperationException("At least one sheet is required"); Sheet sh = getSheet(wb); if (sh == null) throw new UnsupportedOperationException("Unable to find desired sheet"); String name = null; Row hrow = sh.getRow(headersRow - 1); for (Cell cell : hrow) { if (headersRow == firstDataRow) { name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex()); } else { name = fmt.formatCellValue(cell); if (StringUtils.isBlank(name)) name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex()); } hm.put(name.toLowerCase(), cell.getColumnIndex()); } return hm; }
From source file:com.ssic.education.provider.controller.ProSupplierController.java
/** * ??// www . ja v a2 s. co m * * @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 www . java 2 s .com*/ /** * ?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;/*www. j av a2s . c om*/ 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; }