package com.centit.tfzc.util.excelUtil; import jxl.Workbook; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.Field; import java.text.SimpleDateFormat; import java.util.*; import java.util.Map.Entry; public class ExcelUtil { public static List<String[]> readXLS(InputStream is) throws IOException { // InputStream is = new FileInputStream(filePath); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); List<String[]> xlsList = new ArrayList<String[]>(); String[] rowAry; // 循环工作表Sheet // for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); if (hssfSheet == null) { return null; } HSSFRow hssfRow = hssfSheet.getRow(0); HSSFCell hssfCell = hssfRow.getCell(0); // 循环行Row for (int rowNum = 1; rowNum <=hssfSheet.getLastRowNum(); rowNum++) {// 从一行开始读取 hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } rowAry = new String[10]; // 循环列Cell int[] cellNUm = { 0,1,2,3,4,5,6,7,8}; for (int i = 0; i < cellNUm.length; i++) { hssfCell = hssfRow.getCell(cellNUm[i]); if (hssfCell == null) { continue; } rowAry[i] = getValue(hssfCell).trim(); } // rowAry[9]=rowNum+""; xlsList.add(rowAry); } // } return xlsList; } public static List<String[]> readXLSX(InputStream is) throws IOException { // InputStream is = new FileInputStream(filePath); XSSFWorkbook hssfWorkbook = new XSSFWorkbook (is); List<String[]> xlsList = new ArrayList<String[]>(); String[] rowAry; // 循环工作表Sheet // for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); if (hssfSheet == null) { return null; } // 循环行Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { XSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } rowAry = new String[15]; // 循环列Cell for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) { XSSFCell hssfCell = hssfRow.getCell(cellNum); if (hssfCell == null) { continue; } rowAry[cellNum] = getValueXlsx(hssfCell).trim(); } // rowAry[9]=rowNum+""; xlsList.add(rowAry); } // } return xlsList; } @SuppressWarnings("static-access") public static String getValue(HSSFCell hssfCell) { if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { return String.valueOf(hssfCell.getNumericCellValue()); } else { return String.valueOf(hssfCell.toString()); } } @SuppressWarnings("static-access") public static String getValueXlsx(XSSFCell hssfCell) { if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { return String.valueOf(hssfCell.getNumericCellValue()); } else { return String.valueOf(hssfCell.getStringCellValue()); } } /** * @MethodName : listToExcel * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，可自定义工作表大小） * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 如果需要的是引用对象的属性，则英文属性使用类似于EL表达式的格式 * 如：list中存放的都是student，student中又有college属性，而我们需要学院名称，则可以这样写 * fieldMap.put("college.collegeName","学院名称") * @param sheetName * 工作表的名称 * @param sheetSize * 每个工作表中记录的最大个数 * @param out * 导出流 * @throws ExcelException */ public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, int sheetSize, OutputStream out) throws ExcelException { if (list == null || list.size() == 0) { throw new ExcelException("数据源中没有任何数据"); } if (sheetSize > 65535 || sheetSize < 1) { sheetSize = 65535; } // 创建工作簿并发送到OutputStream指定的地方 WritableWorkbook wwb; try { wwb = Workbook.createWorkbook(out); // 因为2003的Excel一个工作表最多可以有65536条记录，除去列头剩下65535条 // 所以如果记录太多，需要放到多个工作表中，其实就是个分页的过程 // 1.计算一共有多少个工作表 double sheetNum = Math.ceil(list.size() / new Integer(sheetSize).doubleValue()); // 2.创建相应的工作表，并向其中填充数据 for (int i = 0; i < sheetNum; i++) { // 如果只有一个工作表的情况 if (1 == sheetNum) { WritableSheet sheet = wwb.createSheet(sheetName, i); fillSheet(sheet, list, fieldMap, 0, list.size() - 1); // 有多个工作表的情况 } else { WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i); // 获取开始索引和结束索引 int firstIndex = i * sheetSize; int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list .size() - 1 : (i + 1) * sheetSize - 1; // 填充工作表 fillSheet(sheet, list, fieldMap, firstIndex, lastIndex); } } wwb.write(); wwb.close(); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /** * @MethodName : listToExcel * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，工作表大小为2003支持的最大值） * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param out * 导出流 * @throws ExcelException */ public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, OutputStream out) throws ExcelException { listToExcel(list, fieldMap, sheetName, 65535, out); } /** * @MethodName : listToExcel * @Description : 导出Excel（导出到浏览器，可以自定义工作表的大小） * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param sheetSize * 每个工作表中记录的最大个数 * @param response * 使用response可以导出到浏览器 * @throws ExcelException */ public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, int sheetSize, HttpServletResponse response) throws ExcelException { // 设置默认文件名为当前时间：年月日时分秒 String fileName = new SimpleDateFormat("yyyyMMddhhmm").format( new Date()).toString(); // 设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件 // try { // sheetName = java.net.URLEncoder.encode(sheetName, "UTF-8"); // } catch (UnsupportedEncodingException e1) { // // e1.printStackTrace(); // } sheetName = sheetName + fileName + ".xls"; try { response.addHeader("Content-Disposition", "attachment;filename="+ new String(sheetName.getBytes("GB2312"),"ISO-8859-1")); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } // response.setHeader("Content-disposition", "attachment; filename=" // + sheetName + fileName + ".xls"); // 创建工作簿并发送到浏览器 try { OutputStream out = response.getOutputStream(); listToExcel(list, fieldMap, sheetName, sheetSize, out); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } public static <T> void listToExcelNoTime(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, int sheetSize, HttpServletResponse response) throws ExcelException { // 设置默认文件名为当前时间：年月日时分秒 String fileName = new SimpleDateFormat("yyyyMMddhhmm").format( new Date()).toString(); // 设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件 sheetName = sheetName + ".xls"; try { response.addHeader("Content-Disposition", "attachment;filename="+ new String(sheetName.getBytes("GB2312"),"ISO-8859-1")); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } // response.setHeader("Content-disposition", "attachment; filename=" // + sheetName + fileName + ".xls"); // 创建工作簿并发送到浏览器 try { OutputStream out = response.getOutputStream(); listToExcel(list, fieldMap, sheetName, sheetSize, out); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /** * @MethodName : listToExcel * @Description : 导出Excel（导出到浏览器，工作表的大小是2003支持的最大值） * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param response * 使用response可以导出到浏览器 * @throws ExcelException */ public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, HttpServletResponse response) throws ExcelException { listToExcel(list, fieldMap, sheetName, 65535, response); } public static <T> void listToExcelNoTime(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, HttpServletResponse response) throws ExcelException { listToExcelNoTime(list, fieldMap, sheetName, 65535, response); } /* * <-------------------------List数组格式---------------------------------------- * -------> */ /** * @MethodName : listToExcelInArray List数组方式 * @Description : 导出Excel（导出到浏览器，工作表的大小是2003支持的最大值） * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param response * 使用response可以导出到浏览器 * @throws ExcelException */ public static <T> void listToExcelInListArray(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, HttpServletResponse response) throws ExcelException { listToExcelInMapArray(list, fieldMap, sheetName, 65535, response); } /** * @MethodName : listToExcelInMapArray判断表格大小 * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，可自定义工作表大小） * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 如果需要的是引用对象的属性，则英文属性使用类似于EL表达式的格式 * 如：list中存放的都是student，student中又有college属性，而我们需要学院名称，则可以这样写 * fieldMap.put("college.collegeName","学院名称") * @param sheetName * 工作表的名称 * @param sheetSize * 每个工作表中记录的最大个数 * @param out * 导出流 * @throws ExcelException */ public static <T> void listToExcelInMapArray(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, int sheetSize, OutputStream out) throws ExcelException { if (list.size() == 0 || list == null) { throw new ExcelException("数据源中没有任何数据"); } if (sheetSize > 65535 || sheetSize < 1) { sheetSize = 65535; } // 创建工作簿并发送到OutputStream指定的地方 WritableWorkbook wwb; try { wwb = Workbook.createWorkbook(out); // 因为2003的Excel一个工作表最多可以有65536条记录，除去列头剩下65535条 // 所以如果记录太多，需要放到多个工作表中，其实就是个分页的过程 // 1.计算一共有多少个工作表 double sheetNum = Math.ceil(list.size() / new Integer(sheetSize).doubleValue()); // 2.创建相应的工作表，并向其中填充数据 for (int i = 0; i < sheetNum; i++) { // 如果只有一个工作表的情况 if (1 == sheetNum) { WritableSheet sheet = wwb.createSheet(sheetName, i); fillSheetInListArry(sheet, list, fieldMap, 0, list.size() - 1); // 有多个工作表的情况 } else { WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i); // 获取开始索引和结束索引 int firstIndex = i * sheetSize; int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list .size() - 1 : (i + 1) * sheetSize - 1; // 填充工作表 fillSheetInListArry(sheet, list, fieldMap, firstIndex, lastIndex); } } wwb.write(); wwb.close(); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /** * @MethodName : listToExcelInMapArray输出到流的方法 * @Description : 导出Excel（导出到浏览器，可以自定义工作表的大小） * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param sheetSize * 每个工作表中记录的最大个数 * @param response * 使用response可以导出到浏览器 * @throws ExcelException */ public static <T> void listToExcelInMapArray(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, int sheetSize, HttpServletResponse response) throws ExcelException { // 设置默认文件名为当前时间：年月日时分秒 String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format( new Date()).toString(); // 设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件 try { sheetName = java.net.URLEncoder.encode(sheetName, "UTF-8"); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } response.setHeader("Content-disposition", "attachment; filename=" + sheetName + fileName + ".xls"); // 创建工作簿并发送到浏览器 try { OutputStream out = response.getOutputStream(); listToExcelInMapArray(list, fieldMap, sheetName, sheetSize, out); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /* * <-------------------------辅助的私有方法------------------------------------------ * -----> */ /** * @MethodName : getFieldValueByName * @Description : 根据字段名获取字段值 * @param fieldName * 字段名 * @param o * 对象 * @return 字段值 */ private static Object getFieldValueByName(String fieldName, Object o) throws Exception { Object value = null; Map map = (Map) o; value = map.get(fieldName); /* * Field field=getFieldByName(fieldName, o.getClass()); * * if(field !=null){ field.setAccessible(true); value=field.get(o); * }else{ throw new ExcelException(o.getClass().getSimpleName() + * "类不存在字段名 "+fieldName); } */ return value; } /** * @MethodName : getFieldByName * @Description : 根据字段名获取字段 * @param fieldName * 字段名 * @param clazz * 包含该字段的类 * @return 字段 */ private static Field getFieldByName(String fieldName, Class<?> clazz) { // 拿到本类的所有字段 Field[] selfFields = clazz.getDeclaredFields(); // 如果本类中存在该字段，则返回 for (Field field : selfFields) { if (field.getName().equals(fieldName)) { return field; } } // 否则，查看父类中是否存在此字段，如果有则返回 Class<?> superClazz = clazz.getSuperclass(); if (superClazz != null && superClazz != Object.class) { return getFieldByName(fieldName, superClazz); } // 如果本类和父类都没有，则返回空 return null; } /** * @MethodName : getFieldValueByNameSequence * @Description : 根据带路径或不带路径的属性名获取属性值 * 即接受简单属性名，如userName等，又接受带路径的属性名，如student.department.name等 * * @param fieldNameSequence * 带路径的属性名或简单属性名 * @param o * 对象 * @return 属性值 * @throws Exception */ private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception { Object value = null; // 将fieldNameSequence进行拆分 String[] attributes = fieldNameSequence.split("\\."); if (attributes.length == 1) { value = getFieldValueByName(fieldNameSequence, o); } else { // 根据属性名获取属性对象 Object fieldObj = getFieldValueByName(attributes[0], o); String subFieldNameSequence = fieldNameSequence .substring(fieldNameSequence.indexOf(".") + 1); value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj); // 递归 } return value; } /** * @MethodName : setFieldValueByName * @Description : 根据字段名给对象的字段赋值 * @param fieldName * 字段名 * @param fieldValue * 字段值 * @param o * 对象 */ private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception { Field field = getFieldByName(fieldName, o.getClass()); if (field != null) { field.setAccessible(true); // 获取字段类型 Class<?> fieldType = field.getType(); // 根据字段类型给字段赋值 if (String.class == fieldType) { field.set(o, String.valueOf(fieldValue)); } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) { field.set(o, Integer.parseInt(fieldValue.toString())); } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) { field.set(o, Long.valueOf(fieldValue.toString())); } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) { field.set(o, Float.valueOf(fieldValue.toString())); } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) { field.set(o, Short.valueOf(fieldValue.toString())); } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) { field.set(o, Double.valueOf(fieldValue.toString())); } else if (Character.TYPE == fieldType) { if ((fieldValue != null) && (fieldValue.toString().length() > 0)) { field.set(o, Character.valueOf(fieldValue.toString().charAt(0))); } } else if (Date.class == fieldType) { field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") .parse(fieldValue.toString())); } else { field.set(o, fieldValue); } } else { throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName); } } /** * @MethodName : setColumnAutoSize * @Description : 设置工作表自动列宽和首行加粗 * @param ws */ private static void setColumnAutoSize(WritableSheet ws, int extraWith) { // 获取本列的最宽单元格的宽度 for (int i = 0; i < ws.getColumns(); i++) { int colWith = 0; for (int j = 0; j < ws.getRows(); j++) { String content = ws.getCell(i, j).getContents().toString(); int cellWith = content.length(); if (colWith < cellWith) { colWith = cellWith; } } // 设置单元格的宽度为最宽宽度+额外宽度 ws.setColumnView(i, colWith + extraWith); } } /** * @MethodName : fillSheet * @Description : 向工作表中填充数据 * @param sheet * 工作表 * @param list * 数据源 * @param fieldMap * 中英文字段对应关系的Map * @param firstIndex * 开始索引 * @param lastIndex * 结束索引 */ private static <T> void fillSheet(WritableSheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, int firstIndex, int lastIndex) throws Exception { // 定义存放英文字段名和中文字段名的数组 String[] enFields = new String[fieldMap.size()]; String[] cnFields = new String[fieldMap.size()]; // 填充数组 int count = 0; for (Entry<String, String> entry : fieldMap.entrySet()) { enFields[count] = entry.getKey(); cnFields[count] = entry.getValue(); count++; } // 填充表头 for (int i = 0; i < cnFields.length; i++) { Label label = new Label(i, 0, cnFields[i]); sheet.addCell(label); } // 填充内容 int rowNo = 1; for (int index = firstIndex; index <= lastIndex; index++) { // 获取单个对象 T item = list.get(index); for (int i = 0; i < enFields.length; i++) { Object objValue = getFieldValueByNameSequence(enFields[i], item); String fieldValue = objValue == null ? "" : objValue.toString(); Label label = new Label(i, rowNo, fieldValue); sheet.addCell(label); } rowNo++; } // 设置自动列宽 setColumnAutoSize(sheet, 5); } /** * @MethodName : fillSheetInArry * @Description : 向工作表中填充数据 * @param sheet * 工作表 * @param list * 数据源 * @param fieldMap * 中英文字段对应关系的Map * @param firstIndex * 开始索引 * @param lastIndex * 结束索引 */ private static <T> void fillSheetInListArry(WritableSheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, int firstIndex, int lastIndex) throws Exception { // 定义存放英文字段名和中文字段名的数组 String[] enFields = new String[fieldMap.size()]; String[] cnFields = new String[fieldMap.size()]; // 填充数组 int count = 0; for (Entry<String, String> entry : fieldMap.entrySet()) { enFields[count] = entry.getKey(); cnFields[count] = entry.getValue(); count++; } // 填充表头 for (int i = 0; i < cnFields.length; i++) { Label label = new Label(i, 0, cnFields[i]); sheet.addCell(label); } // 填充内容 int rowNo = 1; for (int index = firstIndex; index <= lastIndex; index++) { // 获取单个对象 Object[] item = (Object[]) list.get(index); for (int i = 0; i < enFields.length; i++) { // Object objValue=getFieldValueByNameSequence(enFields[i], // item); // String fieldValue=objValue==null ? "" : objValue.toString(); Label label = new Label(i, rowNo, item[i].toString()); sheet.addCell(label); } rowNo++; } // 设置自动列宽 setColumnAutoSize(sheet, 5); } }
导出调用：
JSONArray listObjects = activityInfoManager.findObjectsAsJSonBySql(sql, field, params.toArray(), pageDesc); List objlist = new ArrayList(); if (null != listObjects && listObjects.size() > 0) { for (int i = 0; i < listObjects.size(); i++) { String usercode = String.valueOf(listObjects.getJSONObject(i).getString("usercode")); String nickname = String.valueOf(listObjects.getJSONObject(i).getString("nickname")); String num = String.valueOf(listObjects.getJSONObject(i).getString("num")); Map<Object, Object> objMap = new HashMap<Object, Object>(); objMap.put("usercode", usercode); objMap.put("nickname", nickname); objMap.put("num", num); objlist.add(objMap); } } LinkedHashMap excelMap = new LinkedHashMap(); excelMap.put("usercode", "编码"); excelMap.put("nickname", "昵称"); excelMap.put("num", "漏洞个数"); String name = competitionController.datadictionaryPhbName(issueEasyType); try {
//调用工具类 ExcelUtil.listToExcelNoTime(objlist, excelMap, "漏洞类型榜"+"_"+name, response); OperationLogCenter.log(cud.getUserCode(), "userInfo", "R", "生成" + "漏洞类型榜"); } catch (ExcelException e) { e.printStackTrace(); }
导入调用：
@RequestMapping(value = "/imp/{competitionid}", method = { RequestMethod.POST })
@Transactional
public @ResponseBody JSONObject getUp(@PathVariable Long competitionid,
@RequestParam(value = "file", required = false) MultipartFile file, @RequestParam String userType,
HttpServletResponse response, HttpServletRequest request) {
Map<String, Object> filterMap = new HashMap<String, Object>();
filterMap.put("N_competitionid", competitionid);
JSONObject json = new JSONObject();
json.put("issuccess", "F");
CentitUserDetails cud = getLoginUser(request);
if(!"U".equals(activityInfoManager.getuserTypeByUsercode(cud.getUserCode()))){
json.put("issuccess", "F");
return json;
}
String type = file.getOriginalFilename();
List<String[]> xlsList = null;
String info="";
//获得竞赛名
Competition competition = competitionMag.getObjectById(competitionid);
Map<String, Object> searchColumn1 = new HashMap<>();
searchColumn1.put("useforName","2");//数据字典useforName ，任务认领是2
MailUsefor mailUseforExist1 = mailUseforMag.getObjectByProperties(searchColumn1);
try {
if (type.endsWith(".xls")) {
xlsList = ExcelUtil.readXLS(file.getInputStream());// 从excel中读取数据
} else if (type.endsWith(".xlsx")) {
xlsList = ExcelUtil.readXLSX(file.getInputStream());// 从excel中读取数据
} else {
json.put("result", "请选择.xls或.xlsx文件");
return json;
}
List<UserInfo> userList = sysUserManager.listObjects();
Map<String, String> phone = new HashMap<>();
Map<String, String> loginName = new HashMap<>();
for (int i = 0; i < userList.size(); i++) {
UserInfo userInfo = userList.get(i);
phone.put(userInfo.getRegCellPhone(), "0");
loginName.put(userInfo.getLoginName(), "0");
}
//校验表数据是否合法
String str = "";
String check = "^([a-z0-9A-Z]+[-|_|\\.]?)+[a-z0-9A-Z]@([a-z0-9A-Z]+(-[a-z0-9A-Z]+)?\\.)+[a-zA-Z]{2,}$";//邮箱正则
for (int i = 0; i < xlsList.size(); i++) {
String[] xlsTr = xlsList.get(i);
//手机号校验
if( StringUtils.isNotBlank(xlsTr[2])&&xlsTr[2].length()>11){
str = "第"+(i+2)+"行数据的手机号超出了11位,请核对信息并设置为文本格式！";
json.put("result", str);
json.put("issuccess", "F");
return json;
}else if (!phone.containsKey(xlsTr[2])) {
phone.put(xlsTr[2],String.valueOf((i+2)));
}else{
if ("0".equals(phone.get(xlsTr[2]))) {
continue;
}else{
str = "第"+(i+2)+"行与第"+phone.get(xlsTr[2])+"行手机号重复，请核对后再尝试提交！";
json.put("result", str);
return json;
}
}
//姓名，登录名，身份证号为必填项
if(StringUtils.isBlank(xlsTr[0]) || StringUtils.isBlank(xlsTr[1])|| StringUtils.isBlank(xlsTr[3])|| StringUtils.isBlank(xlsTr[4])||StringUtils.isBlank(xlsTr[7])){
str = "第"+(i+2)+"行数据不完整（姓名，登录名，邮箱，身份证号，所属分公司/专业公司/研究院为必填项），请核对后再尝试提交！";
json.put("result", str);
return json;
}
//登录名校验
if (xlsList.get(i)[1].length()>16) {
str = "第"+(i+2)+"行数据的登录名过长，请控制在16位以内！！";
json.put("result", str);
return json;
}
if (!loginName.containsKey((xlsTr[1]))) {
loginName.put(xlsTr[1], String.valueOf((i+2)));
}else {
if("0".equals(loginName.get(xlsTr[1]))){
str = "第"+(i+2)+"行登录名已存在，请核对后再尝试提交！";
json.put("result", str);
return json;
}else {
str = "第"+(i+2)+"行与第"+loginName.get(xlsTr[2])+"行登录名重复，请核对后再尝试提交！";
json.put("result", str);
return json;
}
}
//身份证号校验
if (xlsTr[4].length()>18||xlsTr[4].length()<15) {
str = "第"+(i+2)+"行数据的身份证号位数不正确,请核对并设置为文本格式！";
json.put("result", str);
return json;
}
//邮箱格式验证
Matcher matcher = Pattern.compile(check).matcher(xlsTr[3]);
if(matcher.matches()==false){
str = "第"+(i+2)+"行数据的邮箱格式不正确,请核对并设置为文本格式！";
json.put("result", str);
return json;
}
}
for (int i = 0; i < xlsList.size(); i++) {
String[] xlsTr = xlsList.get(i);
Date d = new Date();
d.setYear(2015-1900);
//判断用户是已存在还是新增的，已存在更新，新增添加
if ("0".equals(phone.get(xlsTr[2]))){
// 获取已经导入或注册的用户信息并更新信息
UserInfo userinfo=userExinfoMag.getObjectByphone(xlsTr[2]);
filterMap.put("usercode", userinfo.getUserCode());
List<CompetitionUser> listComUser = competitionUserMag.listObjects(filterMap);
Map<String, String> roleMap = new HashMap<>();
if(listComUser != null && listComUser.size() >0){
for (CompetitionUser competitionUser2 : listComUser) {
if("P".equals(competitionUser2.getUsertype())){
roleMap.put("G-competitionUser", "G-competitionUser");
}else if (competitionUser2.getUsertype().equals("E")) {
if("T".equals(competitionUser2.getIsheadman())){
roleMap.put("G-competitionExpertLeader", "G-competitionExpertLeader");
}
}
}
}
UserExinfo userexinfo=userExinfoMag.findObjByLoginName(userinfo.getLoginName());
if(userexinfo.getUserstatus().equals("1")||userexinfo.getUserstatus().equals("2")||userexinfo.getUserstatus().equals("3")){
userExinfoMag.adduserrole(userexinfo.getUsercode(), "G-baimaozi", new Date(), null);
}
CompetitionUser competitionUser=null;
List<CompetitionUser> competitionuserList=competitionUserMag.getListByUsercode(userinfo.getUserCode());
// List<Long> competitionids=new ArrayList<Long>();
Map<String, Long> competitionids = new HashMap<>();
if(competitionuserList!=null){
for(int j=0;j<competitionuserList.size();j++){
competitionids.put(String.valueOf(competitionuserList.get(j).getCompetitionid()),competitionuserList.get(j).getCompetitionid());
}
}
if(competitionuserList==null||!competitionids.containsKey(competitionid)){
competitionUser=new CompetitionUser();
competitionUser.setCompetitionid(competitionid);
competitionUser.setUsercode(userinfo.getUserCode());
competitionUser.setCompetitionuserpoint(0f);
competitionUser.setIscomplete("T");
competitionUser.setIsremind("F");
competitionUser.setIssign("F");
competitionUser.setIsvalid("T");
}else{//竞赛用户表中已存在的用户，删除之前的权限
competitionUser=competitionUserMag.getObjectByCompetitionid(competitionid,userinfo.getUserCode());
if(!roleMap.containsKey("G-competitionUser")&&competitionUser.getUsertype().equals("P")){
userExinfoMag.removeuserrole(competitionUser.getUsercode(), "G-competitionUser");
}else if(competitionUser.getUsertype().equals("E")){
if(!roleMap.containsKey("G-competitionExpertLeader")&&competitionUser.getIsheadman().equals("T")){
userExinfoMag.removeuserrole(competitionUser.getUsercode(), "G-competitionExpertLeader");
}else if(!roleMap.containsKey("G-competitionExpert")){
userExinfoMag.removeuserrole(competitionUser.getUsercode(), "G-competitionExpert");
}
}
}
if(StringUtils.isNotBlank(xlsTr[7])){
competitionUser.setCompany(xlsTr[7]);
}
competitionUser.setUsertype(userType);
//设置竞赛人员类型，并赋予权限
if("P".endsWith(userType)){
competitionUser.setIsheadman("F");
userExinfoMag.adduserrole(userinfo.getUserCode(), "G-competitionUser",d, null);// 给角色添加权限
}else if("E".endsWith(userType)){
if ("是".equals(xlsTr[8])) {
competitionUser.setIsheadman("T");
userExinfoMag.adduserrole(userinfo.getUserCode(), "G-competitionExpertLeader",d, null);// 给角色添加权限
} else {
userExinfoMag.adduserrole(userinfo.getUserCode(), "G-competitionExpert",d, null);// 给角色添加权限
competitionUser.setIsheadman("F");
}
}
//更新用户拓展表中的一些用户信息
// if(StringUtils.isNotBlank(xlsTr[7])){
// userexinfo.setCompangname(xlsTr[7]);
// }
if(StringUtils.isNotBlank(xlsTr[0])){
userexinfo.setRealname(xlsTr[0]);
}
if(StringUtils.isNotBlank(xlsTr[4])){
userexinfo.setIdcardno(xlsTr[4]);
}
if(StringUtils.isNotBlank(xlsTr[5])){
userexinfo.setProvince(xlsTr[5]);
}
if(StringUtils.isNotBlank(xlsTr[6])){
userexinfo.setGoodaddress(xlsTr[6]);
}
/* competitionUser.setCompetitionuserpoint(0f);
competitionUser.setIscomplete("T");
competitionUser.setIsremind("F");*/
competitionUserMag.mergeObject(competitionUser);
userExinfoMag.mergeObject(userexinfo);
sysUserManager.mergeObject(userinfo);
//对于原存在用户，认领成功后邮件导入。
//推送开关打开的时候，进行邮件推送
if (null != mailUseforExist1 && "T".equals(mailUseforExist1.getPushOrnot()) && null != competition){
activityInfoManager.sendEmailUseFor(userexinfo.getEmail(), "尊敬的"+ userexinfo.getRealname() +"，恭喜您已经成功认领“"+competition.getCompetitionname()+"”。欢迎您的踊跃参与。登录名为"+userinfo.getLoginName()+"。","认领竞赛成功");
//邮件推送成功后，记入日志
OperationLogCenter.log("System", "edit", "U","导入用户时，已存在用户，认领竞赛成功(邮件提醒)" + userexinfo.getRealname(), "导入用户，已存在用户，认领竞赛成功(邮件提醒)" + userexinfo.getRealname());
}
}else{
//新增导入用户
UserInfo userInfo = new UserInfo();
String usercode = "U" + userExinfoMag.getObjectId();// 设置用户编码
// 保存userinfo信息
//String uuid = UUID.randomUUID().toString();
userInfo.setUserName(xlsList.get(i)[1]);
userInfo.setLoginName(xlsList.get(i)[1]);
userInfo.setUserCode(usercode);
userInfo.setCreateDate(new Date());
userInfo.setIsValid("T");// T代表该用户有效
//科学计数法 处理
/*Double phoneDouble = Double.parseDouble(xlsList.get(i)[2]);
DecimalFormat df = new DecimalFormat("0");*/
userInfo.setRegCellPhone(xlsList.get(i)[2]);
userInfo.setUserType("I");// 保存为白帽子
String pwd = passwordEncoder.encodePassword("test1212", null);
userInfo.setUserPin(passwordEncoder.encodePassword(pwd, userInfo.getUserCode()));
// 保存userexinfo
UserExinfo userExinfo = new UserExinfo();
userExinfo.setUserlogo("upload-headPortrait-defaultcjf.png");//设置默认头像
userExinfo.setGoodaddress(xlsList.get(i)[6]);
userExinfo.setCompangname(xlsList.get(i)[7]);
userExinfo.setNickname(xlsList.get(i)[1]);
userExinfo.setUsercode(usercode);
userExinfo.setPhone(xlsList.get(i)[2]);
userExinfo.setEmail(xlsList.get(i)[3]);
userExinfo.setEmailState("0");
userExinfo.setRealname(xlsList.get(i)[0]);
//防止身份证出现科学计数法的情况
//String idCardNum = xlsList.get(i)[4];
/*if(StringUtils.isBlank(idCardNum)||idCardNum.contains("X")||idCardNum.contains("x")){
userExinfo.setIdcardno(idCardNum);
}else{*/
// Double idCardDouble = Double.parseDouble(idCardNum);
userExinfo.setIdcardno(xlsList.get(i)[4]);
//}
userExinfo.setIslock("F");
userExinfo.setUserpoint(0l);
userExinfo.setUserpointbalance(0l);
userExinfo.setProvince(xlsList.get(i)[5]);
userExinfo.setUserExp(10L);// 默认:0
//导入用户 默认等级为0级。
userExinfo.setUserLevel("0");
userExinfo.setLastLevelChangeTime(new Date());
userExinfo.setUserstatus(SysConstant.USER_AUDIT_STATE_REGIST);// 后台管理员添加，默认审核通过
userExinfo.setWhitehatrank(SysConstant.WHITE_HAT_GRADE);
// 保存competitionUser
CompetitionUser competitionUser = new CompetitionUser();
competitionUser.setUsercode(usercode);
competitionUser.setCompetitionid(competitionid);
competitionUser.setUsertype(userType);
if("P".endsWith(userType)){
competitionUser.setIsheadman("F");
userExinfoMag.adduserrole(usercode, "G-competitionUser",d, null);// 给角色添加权限
}
else if("E".endsWith(userType)){
if ("是".equals(xlsList.get(i)[8])) {
competitionUser.setIsheadman("T");
userExinfoMag.adduserrole(usercode, "G-competitionExpertLeader",d, null);// 给角色添加权限
} else {
userExinfoMag.adduserrole(usercode, "G-competitionExpert",d, null);// 给角色添加权限
competitionUser.setIsheadman("F");
}
}
competitionUser.setCompetitionuserpoint(0f);
competitionUser.setIscomplete("F");
competitionUser.setIsremind("T");
competitionUser.setIssign("F");
competitionUser.setIsvalid("T");
competitionUser.setCompany(xlsList.get(i)[7]);
competitionUserMag.mergeObject(competitionUser);
sysUserManager.mergeObject(userInfo);
userExinfoMag.mergeObject(userExinfo);
//经验值变动，新增经验变更表记录
experienceExinfoMag.doAddExperience(userExinfo,10L);
userExinfoMag.adduserrole(usercode, "G-baimaozi",d, null);// 给角色添加权限
userExinfoMag.adduserrole(usercode, "G-NEWUSER",d, null);// 给角色添加权限
//导入用户，相当于注册用户，成功时候，进行邮件推送
//用户注册完成，邮件提醒；
Map<String, Object> searchColumn = new HashMap<>();
searchColumn.put("useforName","1");//数据字典useforName ，注册是1
MailUsefor mailUseforExist = mailUseforMag.getObjectByProperties(searchColumn);
//推送开关打开的时候，进行邮件推送
if (null != mailUseforExist && "T".equals(mailUseforExist.getPushOrnot())){
activityInfoManager.sendEmailUseFor(userExinfo.getEmail(), "尊敬的“"+ userExinfo.getRealname() +"”，恭喜您已经成功注册成为集团安全众测平台用户。","注册成功");
//邮件推送成功后，记入日志
OperationLogCenter.log("System", "edit", "U","导入用户，注册成功(邮件提醒)" + userExinfo.getRealname(), "导入用户，注册成功(邮件提醒)" + userExinfo.getRealname());
}
//新增加用户，成功导入后，认领竞赛邮件提醒
//推送开关打开的时候，进行邮件推送
if (null != mailUseforExist1 && "T".equals(mailUseforExist1.getPushOrnot()) && null != competition){
activityInfoManager.sendEmailUseFor(userExinfo.getEmail(), "尊敬的"+ userExinfo.getRealname() +"，恭喜您已经成功认领“"+competition.getCompetitionname()+"”，登录名："+ userInfo.getLoginName()+"；初始密码为“test1212”。欢迎您的踊跃参与。","认领竞赛成功");
//邮件推送成功后，记入日志
OperationLogCenter.log("System", "edit", "U","导入用户，认领竞赛成功(邮件提醒)" + userExinfo.getRealname(), "导入用户，认领竞赛成功(邮件提醒)" + userExinfo.getRealname());
}
}
}
} catch (Exception e) {
e.printStackTrace();
json.put("result", "导入失败");
json.put("issuccess", "F");
return json;
}
json.put("result", "导入成功!<br>"+info);
json.put("issuccess", "T");
return json;
}
原文：https://www.cnblogs.com/pan-my/p/12191948.html