Excel -导入、导出功能
导入功能：
MajorList = institutionBean.queryAllPerfessional( institutionIdString, basicDataBase);// 该学院下的专业 errorList = new ArrayList<>(); String companyNumber = (String) request.getSession().getAttribute( CloudContext.DatabaseName); // itoo // 查询该学院下的所有学生 List<EnrollStudent> existEnrollList = new ArrayList<>(); existEnrollList = enrollStudentBean.queryAllEnrollBycolleageName( freshmenDataBase, institutionName); // 创建一个通用的多部分解析器 CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver( request.getSession().getServletContext()); InputStream inExcelFile = null; // 判断 request 是否有文件上传,即多部分请求 if (multipartResolver.isMultipart(request)) { // 转换成多部分request MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request; // 取得request中的所有文件名 Iterator<String> iter = multiRequest.getFileNames(); while (iter.hasNext()) { // 记录上传过程起始时的时间，用来计算上传时间 // 取得上传文件 MultipartFile file = multiRequest.getFile(iter.next()); inExcelFile = file.getInputStream(); } } // 创建一个list 用来存储读取的内容 List list = new ArrayList(); Cell cell; String result = ERROR; // 获取Excel文件对象 Workbook rwb = Workbook.getWorkbook(inExcelFile); // 获取文件的指定工作表 默认的第一个 Sheet sheet = rwb.getSheet(0); // 行数(表头的目录不需要，从1开始) for (int i = 0; i < sheet.getRows(); i++) { // 创建一个数组 用来存储每一列的值 String[] str = new String[sheet.getColumns()]; // 列数 for (int j = 0; j < sheet.getColumns(); j++) { // 获取第i行，第j列的值 cell = sheet.getCell(j, i); str[j] = cell.getContents(); } // 把刚获取的列存入list list.add(str); } // ==========保存错误信息的List // enrollStudent为要验证的List，error为保存错误信息的 List<EnrollStudent> enrollStudentList = new ArrayList<>(); List<FreshStudent> freshStudentList = new ArrayList<>(); List<String> mapList = new ArrayList<>(); if (!list.isEmpty()) { for (int i = 1; i < list.size(); i++) { EnrollStudent importStudent = new EnrollStudent(); String[] str = (String[]) list.get(i); if (list.get(i) == null || isAllEmpty(str)) { continue; } // 向freshstudent表中添加数据 FreshStudent freshStudent = new FreshStudent(); String freshid = UuidUtils.base58Uuid().toString(); freshStudent.setId(freshid); freshStudent.setIdentityCardID(str[1]); freshStudent.setName(str[2]); freshStudent.setSex(str[3]); freshStudent.setNation(str[4]); freshStudent.setIsHere("否"); freshStudent.setColleageId(institutionIdString); freshStudent.setEntranceDate(entranceDate); // 根据专业名称查询专业ID（str[9]为专业名称） freshStudent.setMajorId(""); if (!"".equals(str[9]) && str[9] != null) { for (Institution Major : MajorList) { if (str[9].equals(Major.getInstitutionName())) { freshStudent.setMajorId(Major.getId()); break; } } } freshStudent.setVersionStartTime(new Date()); freshStudent.setDataBaseName(freshmenDataBase); mapList.add(str[0]); // 调用新生接口，保存新生实体，并返回新生实体 // 学生信息 importStudent.setCEECode(str[0]); // 考号 importStudent.setFreshStudentId(freshid); // 新生id // 此时不应该添加进去数据 importStudent.setIdentityCardID(str[1]); // 身份证 importStudent.setName(str[2]); // 姓名 importStudent.setSex(str[3]); // 性别 importStudent.setNation(str[4]); // 民族 importStudent.setCEEAspiration(str[5]); // 高考志愿 importStudent.setCEEScore(str[6]); // 高考分数 importStudent.setLevel(str[7]); // 层次 importStudent.setSecondaryCollege(str[8]); // 学院名称 importStudent.setMajor(str[9]); // 专业 importStudent.setComment(str[10]); // 备注 importStudent.setDataBaseName(freshmenDataBase); // ===判断格式是否正确================================== String errorInfo = Verification(importStudent, freshStudent, enrollStudentList, existEnrollList); if (!"".equals(errorInfo)) { importStudent.setErrorInfo(errorInfo); errorList.add(importStudent); } // 如果数据格式验证成功，则验证该数据的考生号或身份证号是否已经在要导入的list表中。 else { else {// 如果有错的话，不加入到enrollStudentList和freshStudentList中去 enrollStudentList.add(importStudent); freshStudentList.add(freshStudent); } } } List<FreshStudent> freshList = new ArrayList<>(); List<EnrollStudent> enrollList = new ArrayList<>(); // FreshStudent去重 for (FreshStudent freshStudent : freshStudentList) { if (!freshList.contains(freshStudent)) { freshList.add(freshStudent); } } // enrollStudentList去重 for (EnrollStudent enrollStudent : enrollStudentList) { if (!freshList.contains(enrollStudent)) { enrollList.add(enrollStudent); } } // 批量添加考生 Boolean it = enrollStudentBean.addEnrollListAndFreshList( freshmenDataBase, authorityDataBase, companyNumber, enrollList, freshList, mapList); StringBuilder sbResult = new StringBuilder(); if (it) {// 导入成功 sbResult.append("为您成功导入" + enrollList.size() + "条数据。"); if (errorList.size() > 0) sbResult.append("有" + errorList.size() + "条数据未成功导入，您可以点击下载错误信息来获得错误原因。"); } else { sbResult.append("未能为您导入任何信息,您可以下载错误信息来获得错误原因"); } String strResult1 = sbResult.toString(); // 将更新结果转成json输出 String result1 = String.valueOf(strResult1); response.setContentType("text/html;charset=UTF-8"); PrintWriter out = null; out = response.getWriter(); out.write(result1); } catch (Exception e) { logger.error(e.getMessage(), e); } finally { MajorList = Collections.emptyList();// 导入使用完该全局变量，将请内容清空。 } }
private String Verification(EnrollStudent student, FreshStudent freshStudent, List<EnrollStudent> enrollList, List<EnrollStudent> existList) { student.setComment(""); StringBuilder sbComment = new StringBuilder(); if ("".equals(student.getCEEAspiration()) || "".equals(student.getLevel()) || "".equals(student.getSecondaryCollege()) || "".equals(student.getMajor())) { sbComment.append("该记录信息填写不完整。"); } // 查询该数据是否已经存在 Boolean flag = false; for (EnrollStudent enrollStudent : existList) { if ((enrollStudent.getCEECode()).equals(student.getCEECode()) || (enrollStudent.getIdentityCardID()).equals(student .getIdentityCardID())) { flag = true; break; } }
导出功能：
/** * 导出学生信息 王金博 2016年6月21日09:35:14 * * @param request * @param response */ @RequestMapping("/student/exportStudent") public void exportInvigilate(HttpServletRequest request, HttpServletResponse response) { try { freshmenDataBase = (String) request.getSession().getAttribute( CloudContext.DatabaseName) + DataBaseClass.getFreshmenDataBase(); // 拿到选中行的数据 String sids = new String(request.getParameter("ids").getBytes( "iso-8859-1"), "utf-8"); // 拿到搜索框中的内容 // String searchContent = new // String(request.getParameter("searchContent").getBytes("iso-8859-1"), // "utf-8"); // 导出excel名 String sheetName = "新生系统导出考生"; // 获取需要转出的excle表头的map字段 LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>(); // 添加表头 fieldMap.put(CEECODE, "考生号"); fieldMap.put(IDENTITY_CAEDID, "身份证号"); fieldMap.put("name", "姓名"); fieldMap.put("sex", "性别"); fieldMap.put(NATION, "民族"); fieldMap.put("cEEAspiration", "高考志愿"); fieldMap.put(CEESCORE, "高考分数"); fieldMap.put("level", "层次（本科/专科/专接本）"); fieldMap.put(SECONDARY_COLLEGE, "学院名称"); fieldMap.put(MAJOR, "专业名称"); fieldMap.put("comment", "备注"); // 得到数据 StudentVO studentVO = new StudentVO(); studentVO.setFreshStudentId(sids); studentVO.setColleageId(institutionIdString); studentVO.setName(searchConditionString);// 使用Name属性传递查询条件 List<EnrollStudent> list = enrollStudentBean.queryExportAllStudent( studentVO, freshmenDataBase); // 导出，调用底层listToExcel()方法 ExcelUtil.listToExcel(list, fieldMap, sheetName, response); } catch (Exception e) { logger.error(e.getMessage(), e); } }
ExcelUtil
package com.tgb.itoo.tool.exceluntil; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.HashSet; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.List; import java.util.Map.Entry; import java.util.Set; import javax.servlet.http.HttpServletResponse; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; /** * 导入导出Excel工具类 * * @author kang * @version 1.0.0 , 2015年1月5日 下午8:33:09 */ public class ExcelUtil { /** * @MethodName : listToExcel * @Description : 导出Excel * @param response * 使用response可以导出到浏览器 * @param list * 数据源 * @param fieldMap * 中英文字段对应Map * @throws ExcelException */ public <T> void listToExcel(HttpServletResponse response, List<T> list, LinkedHashMap<String, String> fieldMap) throws ExcelException { // 设置默认文件名为当前时间：年月日时分秒 String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format( new Date()).toString(); // 设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件 response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls"); // 创建工作簿并发送到浏览器 try { OutputStream out = response.getOutputStream(); listToExcel(out, list, fieldMap); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /** * @MethodName : listToExcel * @Description : 导出Excel * @param out * 导出流 * @param list * 数据源 * @param fieldMap * 中英文字段对应Map * @throws ExcelException */ private <T> void listToExcel(OutputStream out, List<T> list, LinkedHashMap<String, String> fieldMap) throws ExcelException { // 定义存放英文字段名和中文字段名的数组 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++; } if (list == null || list.size() == 0) { throw new ExcelException("数据源中没有任何数据"); } // 创建工作簿并发送到OutputStream指定的地方 WritableWorkbook wwb; try { wwb = Workbook.createWorkbook(out); WritableSheet ws = wwb.createSheet("sheet1", 0); // 填充表头 for (int i = 0; i < cnFields.length; i++) { Label label = new Label(i, 0, cnFields[i]); ws.addCell(label); } // 填充内容 int index = 1; for (Iterator<T> iterator = list.iterator(); iterator.hasNext();) { // 获取单个对象 T item = iterator.next(); 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, index, fieldValue); ws.addCell(label); } index++; } // 设置自动列宽 setColumnAutoSize(ws, 5); wwb.write(); wwb.close(); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /** * 导出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失败"); } } } /** * 导出excel * * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param collectionName * 子集合的名称 * @param title * 标题 * @param content * 内容 * @param sheetName * 工作表的名称 * @param sheetSize * 每个工作表中记录的最大个数 * @param out * 导出流 * @throws ExcelException * 异常 */ public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String collectionName, String title, String content, 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, collectionName, title, content, 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, collectionName, title, content, 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失败"); } } } /** * 导出excel * * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param sheetName * 工作表的名称 * @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); } /** * 导出Excel（导出到浏览器，可以自定义工作表的大小） * * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param sheetName * 工作表的名称 * @param sheetSize * 每个工作表中记录的最大个数 * @param response * 使用response可以导出到浏览器 * @param fileName * Excel的文件名 * @throws ExcelException * 异常 */ public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, int sheetSize, HttpServletResponse response, String fileName) throws ExcelException { // 如果文件名没提供，则使用时间戳 if (fileName == null || fileName.trim().equals("")) { // 设置默认文件名为当前时间：年月日时分秒 fileName = new SimpleDateFormat("yyyyMMddhhmmss") .format(new Date()).toString(); } // 设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件 response.setHeader("Content-disposition", "attachment; filename=" + 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失败"); } } } /** * 导出Excel（导出到浏览器，可以自定义工作表的大小） * * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param collectionName * @param title * 标题 * @param content * 内容 * @param sheetName * 工作表的名称 * @param sheetSize * 每个工作表中记录的最大个数 * @param response * 使用response可以导出到浏览器 * @param fileName * Excel的文件名 * @throws ExcelException * 异常 */ public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String collectionName, String title, String content, String sheetName, int sheetSize, HttpServletResponse response, String fileName) throws ExcelException { // 如果文件名没提供，则使用时间戳 if (fileName == null || fileName.trim().equals("")) { // 设置默认文件名为当前时间：年月日时分秒 fileName = new SimpleDateFormat("yyyyMMddhhmmss") .format(new Date()).toString(); } // 设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件 response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls"); // 创建工作簿并发送到浏览器 try { OutputStream out = response.getOutputStream(); listToExcel(list, fieldMap, collectionName, title, content, sheetName, sheetSize, out); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /** * 导出Excel（导出到浏览器，可以自定义工作表的大小） * * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param sheetName * 工作表的名称 * @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("yyyyMMddhhmmss").format( new Date()).toString(); listToExcel(list, fieldMap, sheetName, sheetSize, response, fileName); } /** * 导出Excel（导出到浏览器，可以自定义工作表的大小） * * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param collectionName * @param title * 标题 * @param content * 内容 * @param sheetName * 工作表的名称 * @param response * 使用response可以导出到浏览器 * @throws ExcelException * 异常 */ public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String collectionName, String title, String content, String sheetName, HttpServletResponse response) throws ExcelException { listToExcel(list, fieldMap, collectionName, title, content, sheetName, 65535, response, ""); } /** * 导出Excel（导出到浏览器，可以自定义工作表的大小） * * @param list * 数据源 * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param sheetName * 工作表的名称 * @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); } /** * 导出Excel模板 考试专用（导出到浏览器，可以自定义工作表的大小） * * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param sheetName * 工作表的名称 * @param response * 使用response可以导出到浏览器 * @throws ExcelException * 异常 */ public static <T> void leadToExcelQuestionBankTemplet( LinkedHashMap<String, String> fieldMap, String sheetName, HttpServletResponse response) throws ExcelException { // 设置默认文件名为当前时间：年月日时分秒 String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format( new Date()).toString(); // 如果文件名没提供，则使用时间戳 if (fileName == null || fileName.trim().equals("")) { // 设置默认文件名为当前时间：年月日时分秒 fileName = new SimpleDateFormat("yyyyMMddhhmmss") .format(new Date()).toString(); } // 设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件 response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls"); // 创建工作簿并发送到浏览器 try { OutputStream out = response.getOutputStream(); int 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()); double sheetNum = 1; // 2.创建相应的工作表，并向其中填充数据 // 如果只有一个工作表的情况 if (1 == sheetNum) { WritableSheet sheet = wwb.createSheet(sheetName, 1); // 定义存放英文字段名和中文字段名的数组 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); } // 设置自动列宽 setColumnAutoSize(sheet, 5); } wwb.write(); wwb.close(); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /** * 将Excel转化为List * * @param in * ：承载着Excel的输入流 * @param sheetIndex * ：要导入的工作表序号 * @param entityClass * ：List中对象的类型（Excel中的每一行都要转化为该类型的对象） * @param fieldMap * ：Excel中的中文列头和类的英文属性的对应关系Map * @param uniqueFields * ：指定业务主键组合（即复合主键），这些列的组合不能重复 * @return list集合 * @throws ExcelException * 异常 */ public static <T> List<T> excelToList(InputStream in, String sheetName, Class<T> entityClass, LinkedHashMap<String, String> fieldMap, String[] uniqueFields) throws ExcelException { // 定义要返回的list List<T> resultList = new ArrayList<T>(); try { // 根据Excel数据源创建WorkBook Workbook wb = Workbook.getWorkbook(in); // 获取工作表 Sheet sheet = wb.getSheet(sheetName); // 获取工作表的有效行数 int realRows = 0; for (int i = 0; i < sheet.getRows(); i++) { int nullCols = 0; for (int j = 0; j < sheet.getColumns(); j++) { Cell currentCell = sheet.getCell(j, i); if (currentCell == null || "".equals(currentCell.getContents().toString())) { nullCols++; } } if (nullCols == sheet.getColumns()) { break; } else { realRows++; } } // 如果Excel中没有数据则提示错误 if (realRows <= 1) { throw new ExcelException("Excel文件中没有任何数据"); } Cell[] firstRow = sheet.getRow(0); String[] excelFieldNames = new String[firstRow.length]; // 获取Excel中的列名 for (int i = 0; i < firstRow.length; i++) { excelFieldNames[i] = firstRow[i].getContents().toString() .trim(); } // 判断需要的字段在Excel中是否都存在 boolean isExist = true; List<String> excelFieldList = Arrays.asList(excelFieldNames); for (String cnName : fieldMap.keySet()) { if (!excelFieldList.contains(cnName)) { isExist = false; break; } } // 如果有列名不存在，则抛出异常，提示错误 if (!isExist) { throw new ExcelException("Excel中缺少必要的字段，或字段名称有误"); } // 将列名和列号放入Map中,这样通过列名就可以拿到列号 LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>(); for (int i = 0; i < excelFieldNames.length; i++) { colMap.put(excelFieldNames[i], firstRow[i].getColumn()); } // 判断是否有重复行 // 1.获取uniqueFields指定的列 Cell[][] uniqueCells = new Cell[uniqueFields.length][]; for (int i = 0; i < uniqueFields.length; i++) { int col = colMap.get(uniqueFields[i]); uniqueCells[i] = sheet.getColumn(col); } // 2.从指定列中寻找重复行 for (int i = 1; i < realRows; i++) { int nullCols = 0; for (int j = 0; j < uniqueFields.length; j++) { String currentContent = uniqueCells[j][i].getContents(); Cell sameCell = sheet.findCell(currentContent, uniqueCells[j][i].getColumn(), uniqueCells[j][i].getRow() + 1, uniqueCells[j][i].getColumn(), uniqueCells[j][realRows - 1].getRow(), true); if (sameCell != null) { nullCols++; } } if (nullCols == uniqueFields.length) { throw new ExcelException("Excel中有重复行，请检查"); } } // 将sheet转换为list for (int i = 1; i < realRows; i++) { // 新建要转换的对象 T entity = entityClass.newInstance(); // 给对象中的字段赋值 for (Entry<String, String> entry : fieldMap.entrySet()) { // 获取中文字段名 String cnNormalName = entry.getKey(); // 获取英文字段名 String enNormalName = entry.getValue(); // 根据中文字段名获取列号 int col = colMap.get(cnNormalName); // 获取当前单元格中的内容 String content = sheet.getCell(col, i).getContents() .toString().trim(); // 给对象赋值 setFieldValueByName(enNormalName, content, entity); } resultList.add(entity); } } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { e.printStackTrace(); throw new ExcelException("导入Excel失败"); } } return resultList; } /** * 根据字段名获取字段值 * * @param fieldName * 字段名 * @param o * 对象 * @return 字段值 * @throws Exception * 异常 */ public static Object getFieldValueByName(String fieldName, Object o) throws Exception { Object value = null; 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; } /** * 根据字段名获取字段 * * @param fieldName * 字段名 * @param clazz * 包含该字段的类 * @return 字段 */ public 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; } /** * 根据实体拿到该实体的所有属性 * * @param clazz * 实体 * @return 返回属性的list集合 */ public static List getFieldByClass(Class<?> clazz) { List list = new ArrayList(); // 拿到本类的所有字段 Field[] selfFields = clazz.getDeclaredFields(); for (Field field : selfFields) { list.add(field.getName()); } // 否则，查看父类中是否存在此字段，如果有则返回 Class<?> superClazz = clazz.getSuperclass(); Field[] superFields = superClazz.getDeclaredFields(); for (Field field : superFields) { list.add(field.getName()); } // 如果本类和父类都没有，则返回空 return list; } /** * 根据实体拿到该实体的所有属性 * * @param clazz * 实体 * @return 返回属性的list集合 */ public static List getSuperClassFieldByClass(Class<?> clazz) { List list = new ArrayList(); // 否则，查看父类中是否存在此字段，如果有则返回 Class<?> superClazz = clazz.getSuperclass(); Field[] superFields = superClazz.getDeclaredFields(); for (Field field : superFields) { list.add(field.getName()); } // 如果父类没有，则返回空 return list; } /** * 根据带路径或不带路径的属性名获取属性值,即接受简单属性名，如userName等，又接受带路径的属性名，如student.department. * name等 * * @param fieldNameSequence * 带路径的属性名或简单属性名 * @param o * 对象 * @return 属性值 * @throws Exception * 异常 */ public 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; } /** * 根据字段名给对象的字段赋值 * * @param fieldName * 字段名 * @param fieldValue * 字段值 * @param o * 对象 * @throws Exception * 异常 */ public 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); } } /** * 设置工作表自动列宽和首行加粗 * * @param ws * 要设置格式的工作表 * @param extraWith * 额外的宽度 */ public 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); } } /** * 向工作表中填充数据 * * @param sheet * 工作表名称 * @param list * 数据源 * @param fieldMap * 中英文字段对应关系的Map * @param firstIndex * 开始索引 * @param lastIndex * 结束索引 * @throws Exception * 异常 */ public 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); } /** * 向工作表中填充数据 * * @param sheet * @param list * 数据源 * @param normalFieldMap * 普通中英文字段对应关系的Map * @param collectionFieldMap * 集合類中英文字段对应关系的Map * @param title * 标题 * @param content * 内容 * @param firstIndex * 开始索引 * @param lastIndex * 结束索引 * @throws Exception */ public static <T> void fillSheet(WritableSheet sheet, List<T> list, LinkedHashMap<String, String> normalFieldMap, String collectionFieldName, String title, String content, int firstIndex, int lastIndex) throws Exception { // 定义存放普通英文字段名和中文字段名的数组 String[] enFields = new String[normalFieldMap.size()]; String[] cnFields = new String[normalFieldMap.size()]; // 填充普通字段数组 int count = 0; for (Entry<String, String> entry : normalFieldMap.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); } // 填充表头（行转列字段） T firstItem = list.get(0); List childList = (List) getFieldValueByName(collectionFieldName, firstItem); int colCount = cnFields.length; for (Object obj : childList) { Object objValue = getFieldValueByNameSequence(title, obj); String fieldValue = objValue == null ? "" : objValue.toString(); Label label = new Label(colCount, 0, fieldValue); sheet.addCell(label); colCount++; } // 填充内容 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); } // 填充集合字段內容 if (collectionFieldName != null && !collectionFieldName.equals("")) { // 拿到集合对象 List currentList = (List) getFieldValueByName( collectionFieldName, item); // 将集合对象行转列 for (int i = 0; i < currentList.size(); i++) { Object objValue = getFieldValueByNameSequence(content, currentList.get(i)); String fieldValue = objValue == null ? "" : objValue .toString(); Label label = new Label(i + cnFields.length, rowNo, fieldValue); sheet.addCell(label); } } rowNo++; } // 设置自动列宽 setColumnAutoSize(sheet, 5); } /** * @MethodName : importExcel * @Description : 导入Excel * @param in * 导入文件流 * @param entityClass * 每一行数据最终要形成的实体类 * @param normalFieldMap * 普通字段中英文对应Map（key和value都是string） * @param referFieldMap * 引用字段中英文对应Map（key和value又都是Map，key中存放属性和类型的对应关系，value中存放中英文对应关系） * @param uniqueFields * 唯一确定一条记录的字段数组（其实就是业务主键） * @param response * 通过response可以将错误数据导出到浏览器 * @throws ExcelException */ // public <T> int importExcel( // InputStream in, // Class<T> entityClass, // LinkedHashMap<String, String> normalFieldMap, // LinkedHashMap<LinkedHashMap<String, Class<?>>,LinkedHashMap<String, // String>> referFieldMap, // LinkedHashMap<LinkedHashMap<String, Class<?>>,LinkedHashMap<String, // String>> thirdFieldMap, // String[] uniqueFields, // HttpServletResponse response // ) throws ExcelException{ // // // //定义要返回的错误行数 // int errorLines=0; // // //定义要插入的list // List<T> resultList=new ArrayList<T>(); // // //获取普通和引用字段的总和Map // LinkedHashMap<String, String> combineMap=combineFields(normalFieldMap, // referFieldMap); // // try { // // //根据Excel数据源创建WorkBook // Workbook wb=Workbook.getWorkbook(in); // // //判断cnFields中的中文列名是否和Excel数据源中的对应 // Sheet sheet=wb.getSheet(0); // // //获取工作表的有效行数 // int realRows=0; // for(int i=0;i<sheet.getRows();i++){ // // int nullCols=0; // for(int j=0;j<sheet.getColumns();j++){ // Cell currentCell=sheet.getCell(j,i); // if(currentCell==null || "".equals(currentCell.getContents().toString())){ // nullCols++; // } // } // // if(nullCols==sheet.getColumns()){ // break; // }else{ // realRows++; // } // } // // // // //如果Excel中没有数据则提示错误 // if(realRows<=1){ // throw new ExcelException("Excel文件中没有任何数据"); // } // // // Cell[] firstRow=sheet.getRow(0); // // System.out.println("表头长度是"+firstRow.length); // // String[] excelFieldNames=new String[firstRow.length]; // // //获取Excel中的列名 // for(int i=0;i<firstRow.length;i++){ // excelFieldNames[i]=firstRow[i].getContents().toString().trim(); // } // // //判断需要的字段在Excel中是否都存在 // boolean isExist=true; // List<String> excelFieldList=Arrays.asList(excelFieldNames); // for(String cnName : combineMap.keySet()){ // if(!excelFieldList.contains(cnName)){ // isExist=false; // break; // } // } // // //如果有列名不存在，则抛出异常，提示错误 // if(!isExist){ // throw new ExcelException("Excel中缺少必要的字段，或字段名称有误"); // } // // // //将列名和列号放入Map中,这样通过列名就可以拿到列号 // LinkedHashMap<String, Integer> colMap=new LinkedHashMap<String, // Integer>(); // for(int i=0;i<excelFieldNames.length;i++){ // colMap.put(excelFieldNames[i], firstRow[i].getColumn()); // } // // //如果定义了业务主键，则需要判断是否重复 // if(uniqueFields!=null && uniqueFields.length!=0){ // //判断是否有重复行 // //1.获取uniqueFields指定的列 // Cell[][] uniqueCells=new Cell[uniqueFields.length][]; // for(int i=0;i<uniqueFields.length;i++){ // int col=colMap.get(uniqueFields[i]); // uniqueCells[i]=sheet.getColumn(col); // } // // //2.从指定列中寻找重复行 // for(int i=1;i<realRows-1;i++){ // int nullCols=0; // //利用set元素的不重复特征来判断是否有重复行 // Set<Integer> sameSet = new HashSet<Integer>(); // for(int j=0;j<uniqueFields.length;j++){ // String currentContent=uniqueCells[j][i].getContents(); // Cell sameCell=sheet.findCell(currentContent, // uniqueCells[j][i].getColumn(), // uniqueCells[j][i].getRow()+1, // uniqueCells[j][i].getColumn(), // uniqueCells[j][realRows-1].getRow(), // true); // if(sameCell!=null){ // nullCols++; // sameSet.add(sameCell.getRow()); // } // } // // if(nullCols==uniqueFields.length && sameSet.size()==1){ // throw new ExcelException("Excel中有重复行，请检查"); // } // } // } // // //定义错误列表 // LinkedHashMap< Integer, String> errorMap=new LinkedHashMap<Integer, // String>(); // // //将sheet转换为list // for(int i=1;i<realRows;i++){ // //新建要转换的对象 // T entity=entityClass.newInstance(); // // //根据业务主键查询对象 // LinkedHashMap< Object,Object> uniqueMap =new LinkedHashMap<Object, // Object>(); // for(int m=0;m<uniqueFields.length;m++){ // // //获取英文字段名 // String enName=combineMap.get(uniqueFields[m]); // //获取英文字段值 // //1.获取列号 // int col=colMap.get(uniqueFields[m]); // //2.获取内容 // String enValue=sheet.getCell(col, i).getContents().toString().trim(); // // //放入map // uniqueMap.put(enName, enValue); // // } // //2.查询对象 // T queryEntity=getObjByFields(entityClass, uniqueMap); // // //如果对象已存在，在添加到错误列表，并continue // if(queryEntity!=null){ // //将该行添加到错误列表 // errorMap.put(i, "对象已存在"); // continue; // } // // //如果存在普通字段，则给普通字段赋值 // if(normalFieldMap!=null && normalFieldMap.size()!=0){ // //给对象中的普通字段赋值 // for(Entry<String, String> entry : normalFieldMap.entrySet()){ // //获取中文字段名 // String cnNormalName=entry.getKey(); // //获取英文字段名 // String enNormalName=entry.getValue(); // //根据中文字段名获取列号 // int col=colMap.get(cnNormalName); // // //获取当前单元格中的内容 // String content=sheet.getCell(col, i).getContents().toString().trim(); // // //给对象赋值 // setFieldValueByName(enNormalName, content, entity); // } // } // // //用来标记是否有为空的引用对象 // boolean isReferNull=false; // // //如果有引用字段，则给引用字段赋值 // if(referFieldMap!=null && referFieldMap.size()!=0){ // // //给对象的引用字段赋值 // for(Entry<LinkedHashMap<String, Class<?>>,LinkedHashMap<String, String>> // entry : referFieldMap.entrySet()){ // LinkedHashMap<String, Class<?>> keyMap=entry.getKey(); // LinkedHashMap<String, String> valueMap=entry.getValue(); // //获取引用字段名和类型 // String referField=""; // Class<?> type=null; // for(Entry<String, Class<?>> keyEntry:keyMap.entrySet()){ // referField=keyEntry.getKey(); // type=keyEntry.getValue(); // break; // } // // //组建查询字段Map // LinkedHashMap<Object, Object> equalFields=new LinkedHashMap<Object, // Object>(); // for(Entry<String, String> valueEntry:valueMap.entrySet()){ // String enField=valueEntry.getValue(); // String cnField=valueEntry.getKey(); // int col=colMap.get(cnField); // String content=sheet.getCell(col, i).getContents().toString().trim(); // equalFields.put(enField, content); // } // // //查询引用对象 // Object referObj=getObjByFields(type,equalFields); // // //如果引用对象不为空，则给对象赋值 // if(referObj!=null){ // //将referObj赋给entity对象 // // setFieldValueByName(referField, referObj, entity); // }else{ // isReferNull=true; // errorMap.put(i, "业务主键代表的引用对象不存在"); // break; // } // } // } // // //如果有引用字段，则给引用字段赋值 // if(thirdFieldMap!=null && thirdFieldMap.size()!=0){ // // //给对象的引用字段赋值 // for(Entry<LinkedHashMap<String, Class<?>>,LinkedHashMap<String, String>> // entry : thirdFieldMap.entrySet()){ // LinkedHashMap<String, Class<?>> keyMap=entry.getKey(); // LinkedHashMap<String, String> valueMap=entry.getValue(); // //获取引用字段名和类型 // String thirdField=""; // Class<?> type=null; // for(Entry<String, Class<?>> keyEntry:keyMap.entrySet()){ // thirdField=keyEntry.getKey(); // type=keyEntry.getValue(); // break; // } // // //组建查询字段Map // LinkedHashMap<Object, Object> equalFields=new LinkedHashMap<Object, // Object>(); // for(Entry<String, String> valueEntry:valueMap.entrySet()){ // String enField=valueEntry.getValue(); // String cnField=valueEntry.getKey(); // int col=colMap.get(cnField); // String content=sheet.getCell(col, i).getContents().toString().trim(); // equalFields.put(enField, content); // } // // //查询引用对象 // Object thirdObj=getObjByFields(type,equalFields); // // //如果引用对象不为空，则给对象赋值 // if(thirdObj!=null){ // //将referObj赋给entity对象 // Set<Object> thirdSetObj=new HashSet<Object>(); // thirdSetObj.add(thirdObj); // setFieldValueByName(thirdField,thirdSetObj, entity); // }else{ // isReferNull=true; // errorMap.put(i, "业务主键代表的引用对象不存在"); // break; // } // } // } // // //如果有引用为空的对象则继续循环，不将对象放入resultList // if(isReferNull){ // continue; // //否则将对象放入resultList // }else{ // resultList.add(entity); // } // // } // // // // //将数据保存到数据库 // //commonBean.saveEntitys(resultList); // // //如果有错误列表，则将其导出 // if(errorMap.size()>0){ // // errorLines=errorMap.size(); // // //创建错误列表工作簿 // WritableWorkbook // ewwb=Workbook.createWorkbook(response.getOutputStream()); // WritableSheet esheet=ewwb.createSheet("sheet1", 0); // // //1.给错误列表填充表头 // for(int i=0;i<excelFieldNames.length;i++){ // Label label=new Label(i,0,excelFieldNames[i]); // // esheet.addCell(label); // } // // //2.给表头另外添加两列，分别是“错误原因”和“所在行号” // esheet.addCell(new Label(excelFieldNames.length,0,"错误原因")); // esheet.addCell(new Label(excelFieldNames.length+1,0,"所在行号")); // // //将错误数据从源Excel复制到错误列表Excel // addErrorRow(sheet, esheet, errorMap); // // //1.设置默认文件名为当前时间 // String fileName=new SimpleDateFormat("yyyyMMddhhmmss").format(new // Date()).toString()+"ErrorData"; // //2.设置response头信息 // response.reset(); // response.setContentType("application/vnd.ms-excel"); //改成输出excel文件 // response.setHeader("Content-disposition","attachment; filename="+fileName+".xls" // ); // // //设置单元格格式 // setColumnAutoSize(esheet, 5); // //导出Excel // ewwb.write(); // ewwb.close(); // } // // // } catch(Exception e){ // e.printStackTrace(); // //如果是ExcelException，则直接抛出 // if(e instanceof ExcelException){ // throw (ExcelException)e; // // //否则将其它异常包装成ExcelException再抛出 // }else{ // e.printStackTrace(); // throw new ExcelException("导入Excel失败"); // } // } // // return errorLines; // // } /** * @MethodName : importExcel * @Description : 导入Excel * @param in * 导入文件流 * @param entityClass * 每一行数据最终要形成的实体类 * @param normalFieldMap * 普通字段中英文对应Map（key和value都是string） * @param referFieldMap * 引用字段中英文对应Map（key和value又都是Map，key中存放属性和类型的对应关系，value中存放中英文对应关系） * @param uniqueFields * 唯一确定一条记录的字段数组（其实就是业务主键） * @param response * 通过response可以将错误数据导出到浏览器 * @throws ExcelException */ public <T> List<T> importExcel( InputStream in, Class<T> entityClass, LinkedHashMap<String, String> normalFieldMap, LinkedHashMap<LinkedHashMap<String, Class<?>>, LinkedHashMap<String, String>> referFieldMap, String[] uniqueFields, HttpServletResponse response) throws ExcelException { // 定义要返回的错误行数 int errorLines = 0; // 定义要插入的list List<T> resultList = new ArrayList<T>(); // 获取普通和引用字段的总和Map LinkedHashMap<String, String> combineMap = combineFields( normalFieldMap, referFieldMap); try { // 根据Excel数据源创建WorkBook Workbook wb = Workbook.getWorkbook(in); // 判断cnFields中的中文列名是否和Excel数据源中的对应 Sheet sheet = wb.getSheet(0); // 获取工作表的有效行数 int realRows = 0; for (int i = 0; i < sheet.getRows(); i++) { int nullCols = 0; for (int j = 0; j < sheet.getColumns(); j++) { Cell currentCell = sheet.getCell(j, i); if (currentCell == null || "".equals(currentCell.getContents().toString())) { nullCols++; } } if (nullCols == sheet.getColumns()) { break; } else { realRows++; } } // 如果Excel中没有数据则提示错误 if (realRows <= 1) { throw new ExcelException("Excel文件中没有任何数据"); } Cell[] firstRow = sheet.getRow(0); System.out.println("表头长度是" + firstRow.length); String[] excelFieldNames = new String[firstRow.length]; // 获取Excel中的列名 for (int i = 0; i < firstRow.length; i++) { excelFieldNames[i] = firstRow[i].getContents().toString() .trim(); } // 判断需要的字段在Excel中是否都存在 boolean isExist = true; List<String> excelFieldList = Arrays.asList(excelFieldNames); for (String cnName : combineMap.keySet()) { if (!excelFieldList.contains(cnName)) { isExist = false; break; } } // 如果有列名不存在，则抛出异常，提示错误 if (!isExist) { throw new ExcelException("Excel中缺少必要的字段，或字段名称有误"); } // 将列名和列号放入Map中,这样通过列名就可以拿到列号 LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>(); for (int i = 0; i < excelFieldNames.length; i++) { colMap.put(excelFieldNames[i], firstRow[i].getColumn()); } // 如果定义了业务主键，则需要判断是否重复 if (uniqueFields != null && uniqueFields.length != 0) { // 判断是否有重复行 // 1.获取uniqueFields指定的列 Cell[][] uniqueCells = new Cell[uniqueFields.length][]; for (int i = 0; i < uniqueFields.length; i++) { int col = colMap.get(uniqueFields[i]); uniqueCells[i] = sheet.getColumn(col); } // 2.从指定列中寻找重复行 for (int i = 1; i < realRows - 1; i++) { int nullCols = 0; // 利用set元素的不重复特征来判断是否有重复行 Set<Integer> sameSet = new HashSet<Integer>(); for (int j = 0; j < uniqueFields.length; j++) { String currentContent = uniqueCells[j][i].getContents(); Cell sameCell = sheet.findCell(currentContent, uniqueCells[j][i].getColumn(), uniqueCells[j][i].getRow() + 1, uniqueCells[j][i].getColumn(), uniqueCells[j][realRows - 1].getRow(), true); if (sameCell != null) { nullCols++; sameSet.add(sameCell.getRow()); } } if (nullCols == uniqueFields.length && sameSet.size() == 1) { throw new ExcelException("Excel中有重复行，请检查"); } } } // 定义错误列表 LinkedHashMap<Integer, String> errorMap = new LinkedHashMap<Integer, String>(); // 将sheet转换为list for (int i = 1; i < realRows; i++) { // 新建要转换的对象 T entity = entityClass.newInstance(); // 根据业务主键查询对象 LinkedHashMap<Object, Object> uniqueMap = new LinkedHashMap<Object, Object>(); for (int m = 0; m < uniqueFields.length; m++) { // 获取英文字段名 String enName = combineMap.get(uniqueFields[m]); // 获取英文字段值 // 1.获取列号 int col = colMap.get(uniqueFields[m]); // 2.获取内容 String enValue = sheet.getCell(col, i).getContents() .toString().trim(); // 放入map uniqueMap.put(enName, enValue); } // 2.查询对象 T queryEntity = getObjByFields(entityClass, uniqueMap); // 如果对象已存在，在添加到错误列表，并continue if (queryEntity != null) { // 将该行添加到错误列表 errorMap.put(i, "对象已存在"); continue; } // 如果存在普通字段，则给普通字段赋值 if (normalFieldMap != null && normalFieldMap.size() != 0) { // 给对象中的普通字段赋值 for (Entry<String, String> entry : normalFieldMap .entrySet()) { // 获取中文字段名 String cnNormalName = entry.getKey(); // 获取英文字段名 String enNormalName = entry.getValue(); // 根据中文字段名获取列号 int col = colMap.get(cnNormalName); // 获取当前单元格中的内容 String content = sheet.getCell(col, i).getContents() .toString().trim(); // 给对象赋值 setFieldValueByName(enNormalName, content, entity); } } // 用来标记是否有为空的引用对象 boolean isReferNull = false; // 如果有引用字段，则给引用字段赋值 if (referFieldMap != null && referFieldMap.size() != 0) { // 给对象的引用字段赋值 for (Entry<LinkedHashMap<String, Class<?>>, LinkedHashMap<String, String>> entry : referFieldMap .entrySet()) { LinkedHashMap<String, Class<?>> keyMap = entry.getKey(); LinkedHashMap<String, String> valueMap = entry .getValue(); // 获取引用字段名和类型 String referField = ""; Class<?> type = null; for (Entry<String, Class<?>> keyEntry : keyMap .entrySet()) { referField = keyEntry.getKey(); type = keyEntry.getValue(); break; } // 组建查询字段Map LinkedHashMap<Object, Object> equalFields = new LinkedHashMap<Object, Object>(); for (Entry<String, String> valueEntry : valueMap .entrySet()) { String enField = valueEntry.getValue(); String cnField = valueEntry.getKey(); int col = colMap.get(cnField); String content = sheet.getCell(col, i) .getContents().toString().trim(); equalFields.put(enField, content); } // 查询引用对象 Object referObj = getObjByFields(type, equalFields); // 如果引用对象不为空，则给对象赋值 if (referObj != null) { // 将referObj赋给entity对象 setFieldValueByName(referField, referObj, entity); } else { isReferNull = true; errorMap.put(i, "业务主键代表的引用对象不存在"); break; } } } // 如果有引用为空的对象则继续循环，不将对象放入resultList if (isReferNull) { continue; // 否则将对象放入resultList } else { resultList.add(entity); } } // EnrollStudentBean enrollStudentBean = new EnrollStudentBean(); // 将数据保存到数据库 // commonBean.saveEntitys(resultList); // 如果有错误列表，则将其导出 if (errorMap.size() > 0) { errorLines = errorMap.size(); // 创建错误列表工作簿 WritableWorkbook ewwb = Workbook.createWorkbook(response .getOutputStream()); WritableSheet esheet = ewwb.createSheet("sheet1", 0); // 1.给错误列表填充表头 for (int i = 0; i < excelFieldNames.length; i++) { Label label = new Label(i, 0, excelFieldNames[i]); esheet.addCell(label); } // 2.给表头另外添加两列，分别是“错误原因”和“所在行号” esheet.addCell(new Label(excelFieldNames.length, 0, "错误原因")); esheet.addCell(new Label(excelFieldNames.length + 1, 0, "所在行号")); // 将错误数据从源Excel复制到错误列表Excel addErrorRow(sheet, esheet, errorMap); // 1.设置默认文件名为当前时间 String fileName = new SimpleDateFormat("yyyyMMddhhmmss") .format(new Date()).toString() + "ErrorData"; // 2.设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件 response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls"); // 设置单元格格式 setColumnAutoSize(esheet, 5); // 导出Excel ewwb.write(); ewwb.close(); } } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { e.printStackTrace(); throw new ExcelException("导入Excel失败"); } } return resultList; } /* * <-------------------------辅助的私有方法------------------------------------------ * -----> */ /** * @MethodName : addErrorRow * @Description : 添加一行错误列表 * @param eSheet * 错误列表 * @param errorRow * 有错误的行 * @param reason * 错误原因 * @throws WriteException * @throws Exception */ private void addErrorRow(Sheet sourceSheet, WritableSheet eSheet, LinkedHashMap<Integer, String> errorMap) throws Exception { // 复制错误的数据到错误列表 for (Entry<Integer, String> entry : errorMap.entrySet()) { int errorNo = entry.getKey(); String reason = entry.getValue(); int rows = eSheet.getRows(); for (int i = 0; i < sourceSheet.getColumns(); i++) { System.out.println("错误列表当前列号" + i); eSheet.addCell(new Label(i, rows, sourceSheet.getCell(i, errorNo).getContents())); } // 添加错误原因和所在行号 eSheet.addCell(new Label(sourceSheet.getColumns(), rows, reason)); eSheet.addCell(new Label(sourceSheet.getColumns() + 1, rows, String .valueOf(errorNo + 1))); } } /** * @MethodName : getObjByFields * @Description :根据复合业务主键查询对象 * @param clazz * ：对象对应的类 * @param equalFields * ：复合业务主键对应的map * @return 查询到的对象 */ private <T> T getObjByFields(Class<T> clazz, LinkedHashMap<Object, Object> equalFields) { List<T> list = null; if (equalFields.size() != 0) { // list=commonBean.findResultListByEqual(clazz, equalFields); } return list == null || list.size() == 0 ? null : list.get(0); } /** * @MethodName : combineFields * @Description : 组合普通和引用中英文字段Map * @param normalFieldMap * 普通字段Map * @param referFieldMap * 引用字段Map * @return 组合后的Map */ private LinkedHashMap<String, String> combineFields( LinkedHashMap<String, String> normalFieldMap, LinkedHashMap<LinkedHashMap<String, Class<?>>, LinkedHashMap<String, String>> referFieldMap) { LinkedHashMap<String, String> combineMap = new LinkedHashMap<String, String>(); // 如果存在普通字段，则添加之 if (normalFieldMap != null && normalFieldMap.size() != 0) { combineMap.putAll(normalFieldMap); } // 如果存在引用字段，则添加之 if (referFieldMap != null && referFieldMap.size() != 0) { // 组建引用中英文字段Map LinkedHashMap<String, String> simpleReferFieldMap = new LinkedHashMap<String, String>(); for (Entry<LinkedHashMap<String, Class<?>>, LinkedHashMap<String, String>> entry : referFieldMap .entrySet()) { LinkedHashMap<String, Class<?>> keyMap = entry.getKey(); LinkedHashMap<String, String> valueMap = entry.getValue(); // 获取引用中文字段名 String referField = ""; for (Entry<String, Class<?>> keyEntry : keyMap.entrySet()) { referField = keyEntry.getKey(); break; } for (Entry<String, String> valueEntry : valueMap.entrySet()) { String enField = valueEntry.getValue(); String cnField = valueEntry.getKey(); // 拼接英文引用字段 String fullEnField = referField + "." + enField; // 放入simpleReferFieldMap simpleReferFieldMap.put(cnField, fullEnField); } } // 放入combineMap combineMap.putAll(simpleReferFieldMap); } return combineMap; } /** * 导出Excel模板 考试专用（导出到浏览器，可以自定义工作表的大小）吕世华 * * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param sheetName * 工作表的名称 * @param response * 使用response可以导出到浏览器 * @throws ExcelException * 异常 */ @SuppressWarnings("unchecked") public static <T> void leadToExcelPaperTemplet( List<LinkedHashMap<String, String>> lstFieldMaps, String[] sheetNames, HttpServletResponse response) throws ExcelException { // 设置默认文件名为当前时间：年月日时分秒 String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format( new Date()).toString(); // 如果文件名没提供，则使用时间戳 if (fileName == null || fileName.trim().equals("")) { // 设置默认文件名为当前时间：年月日时分秒 fileName = new SimpleDateFormat("yyyyMMddhhmmss") .format(new Date()).toString(); } // 设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件 response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls"); // 创建工作簿并发送到浏览器 try { OutputStream out = response.getOutputStream(); int 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()); double sheetNum = sheetNames.length; int sheetNameIndex = 0; // 2.创建相应的工作表，并向其中填充数据 for (Iterator iterator = lstFieldMaps.iterator(); iterator .hasNext();) { LinkedHashMap<String, String> fieldMap = (LinkedHashMap<String, String>) iterator .next(); WritableSheet sheet = wwb.createSheet( sheetNames[sheetNameIndex], 1); sheetNameIndex++; // 定义存放英文字段名和中文字段名的数组 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); } // 设置自动列宽 setColumnAutoSize(sheet, 5); } wwb.write(); wwb.close(); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } /** * 导出Excel模板 * * @param fieldMap * 类的英文属性和Excel中的中文列名的对应关系 * @param sheetName * 工作表的名称 * @param response * 使用response可以导出到浏览器 * @throws ExcelException * 异常 */ public <T> void leadToExcel(LinkedHashMap<String, String> fieldMap, String sheetName, HttpServletResponse response) throws ExcelException { // 设置默认文件名为当前时间：年月日时分秒 String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format( new Date()).toString(); // 如果文件名没提供，则使用时间戳 if (fileName == null || fileName.trim().equals("")) { // 设置默认文件名为当前时间：年月日时分秒 fileName = new SimpleDateFormat("yyyyMMddhhmmss") .format(new Date()).toString(); } // 设置response头信息 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件 response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls"); // 创建工作簿并发送到浏览器 try { OutputStream out = response.getOutputStream(); int 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()); double sheetNum = 1; // 2.创建相应的工作表，并向其中填充数据 // 如果只有一个工作表的情况 if (1 == sheetNum) { WritableSheet sheet = wwb.createSheet(sheetName, 1); // 定义存放英文字段名和中文字段名的数组 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); } // 设置自动列宽 setColumnAutoSize(sheet, 5); } wwb.write(); wwb.close(); } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } catch (Exception e) { e.printStackTrace(); // 如果是ExcelException，则直接抛出 if (e instanceof ExcelException) { throw (ExcelException) e; // 否则将其它异常包装成ExcelException再抛出 } else { throw new ExcelException("导出Excel失败"); } } } }
