后台Controller中的方法:
package com.lee.springmvc.controller;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import com.lee.springmvc.entity.Student;
import com.lee.springmvc.service.StudentService;
import com.lee.springmvc.util.ExportUtil;
@Controller
public class StudentController {
@Autowired
private StudentService studentService;
@RequestMapping(value="pages/studentInfo")
public ModelAndView studentInfo(){
//学生信息 按钮对应的是 @RequestMapping("pages/studentInfo")的值
//此方法:跳转到studentList.jsp页面 (easyui框架,先跳转,然后在取值。是异步请求)
ModelAndView mav = new ModelAndView();
// mav.setViewName("aa");
mav.setViewName("studentList");
mav.addObject("type", 0);//刚进来的时候默认是 0
return mav;
}
@RequestMapping(method=RequestMethod.POST, value = "pages/getStudentList", consumes = "application/json;charset=UTF-8")
@ResponseBody
public String getStudentList(int page, int rows){
System.out.println("page = "+page+", rows = "+rows);
//page rows 这两个参数从前台传过来
//前台 easyui执行分页时向后台传去两个参数,一个就是当前页数另一个就是每页显示行数(封装好了的)
List<Student> slist = studentService.list(page, rows);
int total = studentService.getTotal();
Map<String,Object> map = new HashMap<String, Object>();
map.put("total", total);
map.put("rows", slist);
map.put("type", 0);//刚进来的时候默认是 0 ;
return JSONObject.fromObject(map).toString();//前台要的是json对象格式的数据
}
/***
* 添加操作
* @param stu
* @return
* @throws Exception
*/
@RequestMapping(value="pages/saveStudent")
@ResponseBody
public String saveStudent(Student stu) throws Exception{
System.out.println("保存的数据是:" + stu);
String msg = "操作失败!";
boolean flag = false;
try {
flag = studentService.addStudent(stu);
msg = "操作成功!";
} catch (Exception e) {
e.printStackTrace();
msg = "操作失败";
throw e;
}
Map<String, Object> modelMap = new HashMap<String, Object>();
modelMap.put("flag", flag);
modelMap.put("msg", msg);
modelMap.put("type", 1);// 1 代表 显示添加事件对应的按钮(添加)
return JSONObject.fromObject(modelMap).toString();
}
/***
* 修改 -- 根据sid查询单条数据
* @param sid
* @return
*/
@RequestMapping(value = "pages/editData", method = RequestMethod.POST)
@ResponseBody
public String editStudentInfo(String sid) {
Student result = studentService.getSingle(sid);
System.out.println("修改的数据是:"+result.toString());
Map<String, Object> modelMap = new HashMap<String, Object>();
modelMap.put("result", result);
modelMap.put("type", 2);// 2 代表 显示修改事件对应的按钮(修改)
return JSONObject.fromObject(modelMap).toString();
}
/***
* 修改 -- 根据sid 修改 单条数据
* @param sid
* @return
*/
@RequestMapping(value = "pages/updateStu", method = RequestMethod.POST)
@ResponseBody
public String updateStu(Student stu) {
System.out.println("要修改的数据是:" + stu);
String msg = "操作失败!";
boolean flag = false;
try {
flag = studentService.updateStudent(stu);
msg = "操作成功!";
} catch (Exception e) {
e.printStackTrace();
msg = "操作失败";
throw e;
}
Map<String, Object> modelMap = new HashMap<String, Object>();
modelMap.put("flag", flag);
modelMap.put("msg", msg);
modelMap.put("type", 2);// 2 代表 显示修改事件对应的按钮(修改)
// ModelAndView mav = new ModelAndView();
// mav.addObject("type", 2);//刚进来的时候默认是 0
return JSONObject.fromObject(modelMap).toString();
}
/***
* 读取数据库,放到list中 并导出Excel
* @param page 当前页数
* @param rows 每页的记录数(行数)
* @return
* @throws Exception
*/
@RequestMapping(value = "<strong><span style="color:#FF0000;">pages/getStudentExport</span></strong>", method = RequestMethod.POST)
@ResponseBody
public String <span style="color:#FF0000;"><strong>getStudentExport</strong></span>(int page, int rows) throws Exception{
System.out.println("---------page = "+page+", rows = "+rows);
// String[] strs = {"学生Id","学生编号","学生姓名","性 别","年龄","出生日期","爱好"};
// HSSFWorkbook workbook = ExportUtil.getHSSFWorkbook(strs, stuName, values);
// return workbook;
//第一步创建一个webbook ,对应一个Excel文件
HSSFWorkbook webBook = new HSSFWorkbook();
//第二步,在webbook中添 添加一个sheet 对应的Excel 文件中的sheet
HSSFSheet sheet = webBook.createSheet("学生信息表");
//第三步,在sheet中添加表头 第 0 行(从 0 开始的),老版本的poi 对Excel的行数列数有限制 short
HSSFRow row = sheet.createRow((int)0);
//第四步,创建单元格,并设置表头居中
HSSFCellStyle style = webBook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//创建一个居中格式
HSSFCell cell = null;
String[] strs = {"学生Id","学生编号","学生姓名","性 别","年龄","出生日期","爱好"};
for (int i = 0; i < strs.length; i++) {
// row.createCell((short)1).setCellValue("工单数");
cell = row.createCell(i);
cell.setCellValue(strs[i]);
cell.setCellStyle(style);
}
//第五步 写入实体数据 实际应用中这些数据从数据库得到
List<Student> list = <strong><span style="color:#FF0000;">studentService.list(page, rows);</span></strong>
// List<Object> values = new ArrayList<Object>();
String stuName = "学生信息";
// Iterator it = list.iterator();
// while(it.hasNext()){
// Student stu = (Student) it.next();
// /*int sid = stu.getSid();
// String stunum = stu.getStunum();
// String name = stu.getStuname();
// String stuage = stu.getStuage();
// String stusex = stu.getStusex();
// String stubirthday = stu.getStubirthday();
// String stuhobby = stu.getStuhobby();*/
// values.add(stu);
// }
for (int i = 0; i < list.size(); i++) {
// String name = list.get(i).getStuname();
// values.add(list.get(i));
Student stu = new Student();
stu = list.get(i);
row = sheet.createRow((int) i + 1 );
//第四步, 创建单元格,并设置值
row.createCell(0).setCellValue((int)stu.getSid());
row.createCell(1).setCellValue((String)stu.getStunum());
row.createCell(2).setCellValue((String)stu.getStuname());
row.createCell(3).setCellValue((String)stu.getStusex());
row.createCell(4).setCellValue((String)stu.getStuage());
row.createCell(5).setCellValue((String)stu.getStubirthday());
// row.createCell(6).setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu.getStuhobby()));
row.createCell(6).setCellValue(stu.getStuhobby());
}
String msg = "操作失败!";
boolean flag = false;
//第六步 将文件存放到指定位置
try{
FileOutputStream fout = new FileOutputStream("E:/Students.xls");
webBook.write(fout);
fout.close();
flag = true;
msg = "操作成功!";
}catch(Exception e){
e.printStackTrace();
msg = "操作失败";
throw e;
}
Map<String, Object> modelMap = new HashMap<String, Object>();
modelMap.put("flag", flag);
modelMap.put("msg", msg);
return JSONObject.fromObject(modelMap).toString();
}
}
对应的DAO实现方法:
<span style="font-size:18px;">package com.lee.springmvc.dao.impl;
import java.sql.SQLException;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.lee.springmvc.dao.StudentDAO;
import com.lee.springmvc.entity.Student;
import java.sql.ResultSet;
import java.sql.SQLException;
@Repository
public class StudentDAOImpl implements StudentDAO{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public List<Student> listStu(int currentPage, int pageSize) {
// List<Student> slist = new ArrayList<Student>();
String sql = "select * from t_student where 1=1" ;
//判断某字符串是否 不为空 且 长度不为0 且 不由空白符(whitespace) 构成,等于 !isBlank(String str)
if(currentPage>0 && pageSize >0){
sql += " limit " + (currentPage-1)*pageSize +" , " + pageSize ;
}
// List<Student> slist = jdbcTemplate.query(sql, new BeanPropertyRowMapper(Student.class));
// return slist;
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Student>(Student.class));
}
}</span>
ajax的定义形式:
<span style="font-size:18px;">//导出Excel
function exportMd(){
var grid = $('#t_student');
var options = grid.datagrid('getPager').data("pagination").options;
var page = options.pageNumber;
//var total = options.total;
//var max = Math.ceil(total/options.pageSize);
var rows = options.pageSize;
$.ajax( {
type : "POST", //定义方法类型(get,post)
url : "action.jsp", //对应后台的访问地址:URL
data : {
page:page,
rows:rows
}, //往后台传送的参数
dataType : "json",//传参数的类型
success : function(data) {
if(data){
if(data.flag){
$.messager.alert('提示', data.msg, 'info', function(){
$('#t_student').datagrid('reload');
});
}else{
$.messager.alert('提示',data.msg, 'error');
}
}
},
error : function(data) {
$.messager.alert('警告',"导入Excel数据失败,请检查网络!", 'error');
}
});
}
对应的jsp页面代码
<div id="lay" class="easyui-layout" style="width: 100%; height: 100%">
<div region="center">
<div id="toolbar"></div>
<table id="t_student"></table>
</div>
</div>
</span>