利用jxl导入Excel,导出Excel

导入maven依赖

<dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.12</version>
    </dependency>  

导出

前端

	$("#exportExcelBtn").click(function() {
				//带数据到你的controller
	window.location.href = "/student/msg/stuMsg_export"
})

后端

设置表名

WritableWorkbook workbook;
String fileName="学生信息表";
fileName = new String(fileName.getBytes(),"iso-8859-1");
			response.setCharacterEncoding("gb2312");response.reset();
			response.setContentType("application/OCTET-STREAM;charset=gb2312");
			response.setHeader("pragma", "no-cache");
			response.addHeader("Content-Disposition", "attachment;filename=\""+ fileName + ".xls\"");// 点击导出excle按钮时候页面显示的默认名称
			workbook = Workbook.createWorkbook(response.getOutputStream());

设置sheet

WritableSheet sheet = workbook.createSheet("学生信息表", 0);

设置字体

// 设置字体的attribute
			WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD);
			WritableCellFormat format = new WritableCellFormat(font);

设置第一行的列名

// 3.设置column名
sheet.addCell(new Label(0, 0, "学号", format));
sheet.addCell(new Label(1, 0, "姓名", format));
sheet.addCell(new Label(2, 0, "性别", format));
sheet.addCell(new Label(3, 0, "民族", format));
sheet.addCell(new Label(4, 0, "手机号", format));
sheet.addCell(new Label(5, 0, "班级", format));
sheet.addCell(new Label(6, 0, "专业", format));
sheet.addCell(new Label(7, 0, "院系", format));
sheet.addCell(new Label(8, 0, "年级", format));

循环导入数据

  
	List<Student>=mapper.getAllStudentMsg();
		// 导入行
		for (int i = 0, j = 1; i < stuList.size(); i++, j++) {
		Student stuMsg = stuList.get(i);

				// 设置列宽
				sheet.setColumnView(i, 16);
				// 重新设置部分列宽
				sheet.setColumnView(3, 14);
				sheet.setColumnView(6, 10);
				sheet.setColumnView(7, 10);
				// 设置行高
				sheet.setRowView(i, 350);
				// 设置字体的attribute
				WritableFont font1 = new WritableFont(WritableFont.createFont("楷体 _GB2312"), 12, WritableFont.NO_BOLD);
WritableCellFormat format1 = new WritableCellFormat(font1);

sheet.addCell(new Label(0, j, stuMsg.getStuId(), format1));
sheet.addCell(new Label(1, j, stuMsg.getStuName(), format1));
sheet.addCell(new Label(2, j, stuMsg.getSex(), format1));
sheet.addCell(new Label(3, j, stuMsg.getNation(), format1));

sheet.addCell(new Label(4, j, stuMsg.getPhone(), format1));
sheet.addCell(new Label(5, j, stuMsg.getClassName(), format1));
sheet.addCell(new Label(6, j, stuMsg.getMajorName(), format1));

sheet.addCell(new Label(7, j, stuMsg.getDepName(), format1));
sheet.addCell(new Label(8, j, stuMsg.getGradeClassName(), format1));

			}

			// 5.写入数据
			workbook.write();
			// 6.关闭资源
			workbook.close();

导入

前端

文件上传的表单

					<form id="QueryForm" action="${ctx}/student/msg/stuMsg_To_Lead"
						method="post" enctype="multipart/form-data">
						<div class="row">
							<div class="col-sm-3" style="width: 78%;">
								<div class="box box-primary">
									<div class="box-header with-border">

										<div class="box-body">
		                               <input id="excel_file" class="form-control" type="file"
												name="filename" accept="xlsx" size="80" />
										</div>
									</div>

								</div>
							</div>

						</div>
					</form>

随便定义一个要提交的btn

<button class="btn btn-info"  id="Excel_To_Lead" >导入</button>

jQuery提交表单

//文件上传			
					$("#Excel_To_Lead").click(function (){

						$("#QueryForm").submit();
							
					})			
			

后端

@Controller

  /**接收上传的文件
	 * @throws IOException 
	 * @throws BiffException */
    @RequestMapping("/stuMsg_To_Lead")
    public String upLoad(@RequestParam(value="filename")MultipartFile file) {
    	
    	List<Student> list=new ArrayList();
    	
    	// 1.获取用户上传的文件
        Workbook workbook;
		try {
			workbook = Workbook.getWorkbook(file.getInputStream());
			
		      // 2.获取工作簿sheet
	        Sheet sheet = workbook.getSheet(0);
	        // 3.获取总行数
	        int rows = sheet.getRows();
	       
	        for (int i = 1; i < rows; i++) {
	         
	        	Student s=new Student();
	        	
	    
	            s.setStuId(sheet.getCell(0, i).getContents());
	            s.setStuName(sheet.getCell(1, i).getContents());
	            s.setSex(sheet.getCell(2, i).getContents());
	            s.setNation(sheet.getCell(3, i).getContents());
	            s.setPhone(sheet.getCell(4, i).getContents());
	            s.setClassName(sheet.getCell(5, i).getContents()); 
	     
	            // 4.添加到数据库中
	           list.add(s);
	        }
	        
	        //调用服务层
	        service.buildInsertStudentMsgList(list);
	     // 5.关闭资源
	        workbook.close();
			
		} catch (Exception e) {
			throw new ErrorReturnPageException("重复导入数据");
		} 
    //重定向跳到主页
     return "redirect:/student/msg/list";
    }

“provider动态sql”

	/**
	 * Excel导入数据 插入多条数据
	 * @param studentList
	 * @return
	 */
	 
	public static String buildInsertStudentMsgList(Map map) {
		List<Student> urlBlack = (List<Student>) map.get("list");
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO student ");
        sb.append("(stu_id, stu_name, class_id, sex,nation,phone) ");
        sb.append("VALUES ");
        MessageFormat mf = new MessageFormat("(#'{'list[{0}].stuId},#'{'list[{0}].stuName},#'{'list[{0}].classId},#'{'list[{0}].sex},#'{'list[{0}].nation},#'{'list[{0}].phone})");
        for (int i = 0; i < urlBlack.size(); i++) {
            sb.append(mf.format(new Object[]{i}));
            if (i < urlBlack.size() - 1) {
                sb.append(",");
            }
        }
        return sb.toString();


	
	}

mapper

	//导入数据

	   @InsertProvider(type = StudentProvider.class, method = "buildInsertStudentMsgList")
	    int buildInsertStudentMsgList(List<Student> list);

感谢

参考的大神地址