ssm poi导入mysql_SSM+poi导入和导出

@ResourceprivateIUserService userService;private static Log log = LogFactory.getLog(ClientController.class);/*** 上传Excel,读取Excel中内容

*@paramfile

*@paramrequest

*@paramresponse

*@return*@throwsIOException*/@RequestMapping(value= "/batchimport",method =RequestMethod.POST)public String batchimport(@RequestParam(value="filename") MultipartFile file,

HttpServletRequest request,HttpServletResponse response)throwsIOException{

log.info("ClientController ..batchimport() start");

String Msg=null;boolean b = false;//判断文件是否为空

if(file==null){

Msg="文件是为空！";

request.getSession().setAttribute("msg",Msg);return "client/client";

}//获取文件名

String name=file.getOriginalFilename();

System.out.println(name);//进一步判断文件是否为空(即判断其大小是否为0或其名称是否为null)验证文件名是否合格

long size=file.getSize();if(name==null || ("").equals(name) && size==0 && !WDWUtil.validateExcel(name)){

Msg="文件格式不正确！请使用.xls或.xlsx后缀文档。";

request.getSession().setAttribute("msg",Msg);return "client/client";

}//创建处理EXCEL

ReadExcel readExcel=newReadExcel();//解析excel，获取客户信息集合。

List dataList =readExcel.getExcelInfo(file);if(dataList != null && !dataList.toString().equals("[]") && dataList.size()>=1){

b= true;

}

userService.insertBatch(dataList);if(b){//迭代添加客户信息(注：实际上这里也可以直接将UserList集合作为参数，在Mybatis的相应映射文件中使用foreach标签进行批量添加。)

for(User User:dataList){//这里可以做添加数据库的功能

System.out.println("第一个值："+User.getUserName()+"\t第二个值："+User.getPassword()+"\t第三个值："+User.getAge());

}

Msg="批量导入EXCEL成功！";

request.getSession().setAttribute("msg",Msg);

}else{

Msg="批量导入EXCEL失败！";

request.getSession().setAttribute("msg",Msg);

}return "client/client";

}private static Logger log=LoggerFactory.getLogger(UserController.class);

@ResourceprivateIUserService userService;

@RequestMapping("/list")public @ResponseBody String list(@RequestParam(value = "userName", required = false) String userName,

@RequestParam(value="rows" , required = false)Integer rows,

@RequestParam(value="page" , required = false)Integer page ){

Integer pageno= (page - 1) *rows;

Integer pagesize=rows;

Pager pager =userService.findPager(pageno,pagesize,userName);

JsonConfig jc= newJsonConfig();

JSON json=JSONSerializer.toJSON(pager,jc);returnjson.toString();

}//导出excel

@RequestMapping("/export.controller")public void export(String ids,HttpServletResponse response) throwsIOException{//只是让浏览器知道要保存为什么文件而已，真正的文件还是在流里面的数据，你设定一个下载类型并不会去改变流里的内容。//而实际上只要你的内容正确，文件后缀名之类可以随便改，就算你指定是下载excel文件，下载时我也可以把他改成pdf等。

System.out.println(ids);

response.setContentType("application/vnd.ms-excel");//传递中文参数编码

String codedFileName = java.net.URLEncoder.encode("中文","UTF-8");

response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");

List list = new ArrayList();

String[] array= ids.split(",");

System.out.println(array);int[] id = new int[array.length];for (int i = 0; i < id.length; i++) {

User user=userService.getUserById(Integer.valueOf(array[i]));//将数据添加到list中

list.add(user);

}//定义一个工作薄

Workbook workbook = newHSSFWorkbook();//创建一个sheet页

Sheet sheet = workbook.createSheet("用户信息");//创建一行

Row row = sheet.createRow(0);//在本行赋值 以0开始

row.createCell(0).setCellValue("用户id");

row.createCell(1).setCellValue("用户名");

row.createCell(2).setCellValue("用户密码");

row.createCell(3).setCellValue("用户年龄");//定义样式

CellStyle cellStyle =workbook.createCellStyle();//格式化日期//cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));//遍历输出

for (int i = 1; i <= list.size(); i++) {

User user1= list.get(i - 1);

row=sheet.createRow(i);

row.createCell(0).setCellValue(user1.getId());

row.createCell(1).setCellValue(user1.getUserName());

row.createCell(2).setCellValue(user1.getPassword());

row.createCell(3).setCellValue(user1.getAge());

}

OutputStream fOut=response.getOutputStream();

workbook.write(fOut);

fOut.flush();

fOut.close();

}