Java里的下载Excel模板上传Excel文件

       由于项目的需要做过VB版的上传excel，vb.net，以及c#的上传excel，现在做Java项目也同样遇到了，为了解决异构系统或者整理原始数据时无法避免的会用到这个东东，所以这样的功能算是非常常见的。这篇博客将更深入的分享如何下载excel模板以及上传excel文件。

      Controller------导出excel模板

public void leadToExcel(HttpServletRequest request,HttpServletResponse response) {excelUtil = new ExcelUtil();try {// excel表格的表头，mapLinkedHashMap<String, String> fieldMap = new LinkedHashMap<String, String>();fieldMap.put("studentCode", "学号");fieldMap.put("studentName", "学生姓名");fieldMap.put("teachClass", "班级名称");fieldMap.put("dailyResult", "平时成绩");String sheetName = "平时成绩";// 导出模板excelUtil.leadToExcel(fieldMap, sheetName, response);} catch (Exception e) {e.printStackTrace();}}

工具类excelUtil.leadToExcel

/** * 导出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失败");}}}

Controller导入excel文件

/** * 导入 *  * @author 刘新阳 * @version 1.0.0 , 2016年3月30日15:51:51 * @return */@RequestMapping(value = "importDailyResult", method = RequestMethod.POST)public void importDailyResult(HttpServletResponse response,HttpServletRequest request) throws Exception {// 创建一个通用的多部分解析器CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());InputStream inExcelFile = null;String dataBaseName = "itoo_basic";// 判断 request 是否有文件上传,即多部分请求if (multipartResolver.isMultipart(request)) {// 转换成多部分requestMultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;// 取得request中的所有文件名Iterator<String> iter = multiRequest.getFileNames();while (iter.hasNext()) {// 记录上传过程起始时的时间，用来计算上传时间int pre = (int) System.currentTimeMillis();// 取得上传文件MultipartFile file = multiRequest.getFile(iter.next());try {inExcelFile = file.getInputStream();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}// 创建一个list 用来存储读取的内容List list = new ArrayList();Workbook rwb = null;Cell cell = null;String result = "error";// 获取Excel文件对象try {rwb = Workbook.getWorkbook(inExcelFile);} catch (BiffException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}// 获取文件的指定工作表 默认的第一个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();}// 把刚获取的列存入listlist.add(str);}List studentList = new ArrayList();List teachClassList = new ArrayList();List studentResult = new ArrayList();String teachCode= (String) request.getSession().getAttribute("name");//"1240364";String teacherId =questionBean.QueryTeachid(dataBaseName, teachCode) ;List<StudentResult>studentResultlist=new ArrayList<StudentResult>();String termId=request.getParameter("termId");if (list.size() > 0) {for (int i = 1; i < list.size(); i++) {StudentResult studentResultEntity= new StudentResult();  Object[] o = (Object[]) list.get(i);String studentCode = o[0].toString();String teachClassName = o[2].toString();String dailyResult = o[3].toString();// 1.判断学生表中是否存在该学号。根据学生学号查询学生idstudentList = studentResultBean.querystudentId(dataBaseName,studentCode);if (studentList.size() > 0) {String StudentId = studentList.get(0).toString();// 2.判断上课班中是否存在该上课班名称，并根据上课班名称查询上课班idteachClassList = studentResultBean.queryteachClassId(dataBaseName, teachClassName);// 3.根据学生id和上课班id判断学生成绩表中是否已经存在该学生在该上课班的成绩，如果存在删提示，如果不存在则插入if (teachClassList.size() > 0) {String teachClassId = teachClassList.get(0).toString();//4.根据学年id，上课班id，教师id查询成绩规则idString resultRuleId=studentResultBean.queryResultId(dataBaseName, termId, teachClassId, teacherId);studentResult = studentResultBean.queryStudentByStudentIdandTeachClassId(dataBaseName, teachClassId, StudentId);if (studentResult.size()== 0) {// 5.将学生和上课班以及平时成绩加入到学生成绩实体。studentResultEntity.setStudentId(StudentId);studentResultEntity.setTeachClassId(teachClassId);studentResultEntity.setDaliyResult(dailyResult);studentResultEntity.setRemark("未判分");studentResultEntity.setOperator("lxy");studentResultEntity.setDataBaseName(dataBaseName);studentResultEntity.setResultRuleId(resultRuleId);studentResultlist.add(studentResultEntity);}}}}    boolean flag= studentResultBean.saveStudenResult(studentResultlist);             if (flag) {  result = "success"; } }JacksonJsonUntil jacksonJsonUntil = new JacksonJsonUntil();jacksonJsonUntil.beanToJson(response, result);}