首页 > 代码库 > Excel导入模板
Excel导入模板
public class ExcelImport { public static AjaxMsg importPerson(HttpServletRequest request, String fileId, File file, IFileService fileService, IPersonService psersonService) throws IOException { style = null;//每次导入都将style置为null InputStream is = new FileInputStream(file); Workbook workbook = new HSSFWorkbook(is); Sheet sheet = workbook.getSheetAt(0); boolean error = false;// 标记excel格式是否有误 AjaxMsg msg = new AjaxMsg(true, ""); DecimalFormat df = new DecimalFormat("#"); // 防止号码变成数值类型 Map<String, List<TPerson>> maps = new HashMap<String, List<TPerson>>(); if (true) { List<TPerson> personList = new ArrayList<TPerson>(); for (int i = 3; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); TPerson person = new TPerson(); // name Cell cell1 = row.getCell(0); if (cell1 != null) { if (cell1.getCellType() == Cell.CELL_TYPE_STRING) { person.setName(cell1.getStringCellValue()); } else if (cell1.getCellType() == Cell.CELL_TYPE_NUMERIC) { person.setName(cell1.getNumericCellValue() + ""); } } else { person.setName(""); } if (person.getName() == null || person.getName().trim() == "") {//为空,则将单元格标红 cell1.setCellStyle(createRedStyle(workbook,cell1.getCellStyle())); error = true; } // oldname Cell cell2 = row.getCell(1); if (cell2 != null) { if (cell2.getCellType() == Cell.CELL_TYPE_STRING) { person.setOldName(cell2.getStringCellValue()); } else if (cell2.getCellType() == Cell.CELL_TYPE_NUMERIC) { person.setOldName(cell2.getNumericCellValue() + ""); } } else { person.setOldName(""); } // shortname Cell cell3 = row.getCell(2); if (cell3 != null) { if (cell3.getCellType() == Cell.CELL_TYPE_STRING) { person.setShortName(cell3.getStringCellValue()); } else if (cell3.getCellType() == Cell.CELL_TYPE_NUMERIC) { person.setShortName(cell3.getNumericCellValue()+ ""); } } else { person.setShortName(""); } personList.add(person); } maps.put(person.getName(), personList); } if (is != null) { is.close(); } try { if (error) { maps.clear(); msg.setSuccess(false); String fUUID = createErrorFile(fileId, file, fileService, workbook); msg.setResult(fUUID);//文件的唯一标识,用于下载 msg.setMsg("valiError"); } else { AjaxMsg result = personService.insertPersonData(request, maps);//将导入的数据写入数据库 if (!result.isSuccess()) { msg.setMsg("synError"); msg.setSuccess(false); } } } catch (Exception e) { e.printStackTrace(); msg.setMsg("导入数据出错"); msg.setSuccess(false); } return msg; } private static CellStyle style; // 单元格标红 private static CellStyle createRedStyle(Workbook workbook, CellStyle oldStyle) { if(style!=null){ return style; } style = workbook.createCellStyle(); style.cloneStyleFrom(oldStyle); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(IndexedColors.RED.index); return style; } private static boolean isPhoneNumber(String input) { Pattern p = null; Matcher m = null; boolean b = false; p = Pattern.compile("^[1][3,4,5,8][0-9]{9}$"); // 验证手机号 m = p.matcher(input); b = m.matches(); return b; } private static boolean isEmail(String input) { boolean a = false; a = input .matches("^[a-z0-9A-Z]+[- | a-z0-9A-Z . _]+@([a-z0-9A-Z]+(-[a-z0-9A-Z]+)?\\.)+[a-z]{2,}$"); return a; } /** * Excel格式有错,则创建含错误信息的Excel供下载 */ private static String createErrorFile(String fileId, File file, IFileService fileService, Workbook workbook) throws IOException, FileNotFoundException { TFile oldFile = fileService.find(fileId); OutputStream os = new FileOutputStream(file); workbook.write(os); if (os != null) { os.flush(); os.close(); } return oldFile.getUuid(); } }
Excel导入模板
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。