java的poi技术下载Excel模板上传Excel读取Excel中内容（SSM框架）
使用到的jar包
JSP: client.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <% String importMsg = ""; if (request.getSession().getAttribute("msg") != null) { importMsg = request.getSession().getAttribute("msg").toString(); } request.getSession().setAttribute("msg", ""); %> <head> <title>批量导入客户</title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <script src="${pageContext.request.contextPath}/js/jquery-1.11.0.min.js"></script> <script type="text/javascript"> function check() { var excel_file = $("#excel_file").val(); if (excel_file == "" || excel_file.length == 0) { alert("请选择文件路径！"); return false; } else { return true; } } $(document).ready(function() { var msg = ""; if ($("#importMsg").text() != null) { msg = $("#importMsg").text(); } if (msg != "") { alert(msg); } }); </script> <body> <a href="download.htm?fileName=muban.xls">下载Exel模板</a> <div> <font color="bule">批量导入客户</font> </div> <form action="batchimport.htm" method="post" enctype="multipart/form-data" onsubmit="return check();"> <div style="margin: 30px;"> <input id="excel_file" type="file" name="filename" accept="xlsx" size="80" /> <input id="excel_button" type="submit" value="导入Excel" /> </div> <font id="importMsg" color="red"><%=importMsg%></font><input type="hidden" /> </form> </body> </html>
controller: ClientController.java
package com.shiliu.game.controller; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; 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.RequestParam; import org.springframework.web.multipart.MultipartFile; import com.shiliu.game.domain.bean.Customer; import com.shiliu.game.utils.ReadExcel; import com.shiliu.game.utils.WDWUtil; /** * @author wkr * @Date 2016-11-18 */ @Controller @RequestMapping("/client") public class ClientController { private static Log log = LogFactory.getLog(ClientController.class); /** * 访问controller进入操作页面 * @return */ @RequestMapping(value="/init") public String init(){ System.out.println("控制台输出：初始化页面信息"); return "client/client"; } /** * 上传Excel,读取Excel中内容 * @param file * @param request * @param response * @return * @throws IOException */ @RequestMapping(value = "/batchimport",method = RequestMethod.POST) public String batchimport(@RequestParam(value="filename") MultipartFile file, HttpServletRequest request,HttpServletResponse response) throws IOException{ 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(); //进一步判断文件是否为空（即判断其大小是否为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=new ReadExcel(); //解析excel，获取客户信息集合。 List<Customer> customerList = readExcel.getExcelInfo(file); if(customerList != null && !customerList.toString().equals("[]") && customerList.size()>=1){ b = true; } if(b){ //迭代添加客户信息（注：实际上这里也可以直接将customerList集合作为参数，在Mybatis的相应映射文件中使用foreach标签进行批量添加。） for(Customer customer:customerList){ //这里可以做添加数据库的功能 System.out.println("第一个值："+customer.getCustomer1()+"\t第二个值："+customer.getCustomer2()+"\t第三个值："+customer.getCustomer3()); } Msg ="批量导入EXCEL成功！"; request.getSession().setAttribute("msg",Msg); }else{ Msg ="批量导入EXCEL失败！"; request.getSession().setAttribute("msg",Msg); } return "client/client"; } /** * 下载Excel模板 * @param fileName * @param request * @param response * @return */ @RequestMapping("/download") public String download(String fileName, HttpServletRequest request, HttpServletResponse response) { System.out.println("控制台输出：走入下载"); response.setCharacterEncoding("utf-8"); response.setContentType("multipart/form-data"); response.setHeader("Content-Disposition", "attachment;fileName="+ fileName); try { /*String path = Thread.currentThread().getContextClassLoader() .getResource("").getPath() + "download";//这个download目录为啥建立在classes下的 */ String path="D:\\upload"; InputStream inputStream = new FileInputStream(new File(path+ File.separator + fileName)); OutputStream os = response.getOutputStream(); byte[] b = new byte[2048]; int length; while ((length = inputStream.read(b)) > 0) { os.write(b, 0, length); } // 这里主要关闭。 os.close(); inputStream.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } // 返回值要注意，要不然就出现下面这句错误！ //java+getOutputStream() has already been called for this response return null; } }
utils: WDWUtil.java
package com.shiliu.game.utils; /** * @author wkr * @Date 2016-11-18 * 工具类验证Excel文档 */ public class WDWUtil { /** * @描述：是否是2003的excel，返回true是2003 * @param filePath * @return */ public static boolean isExcel2003(String filePath) { return filePath.matches("^.+\\.(?i)(xls)$"); } /** * @描述：是否是2007的excel，返回true是2007 * @param filePath * @return */ public static boolean isExcel2007(String filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } /** * 验证是否是EXCEL文件 * @param filePath * @return */ public static boolean validateExcel(String filePath){ if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){ return false; } return true; } }
utils: ReadExcel.java
package com.shiliu.game.utils; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.multipart.commons.CommonsMultipartFile; import com.shiliu.game.domain.bean.Customer; /** * @author wkr * @Date 2016-11-18 * 工具类读取Excel类中内容 */ public class ReadExcel { //总行数 private int totalRows = 0; //总条数 private int totalCells = 0; //错误信息接收器 private String errorMsg; //构造方法 public ReadExcel(){} //获取总行数 public int getTotalRows() { return totalRows;} //获取总列数 public int getTotalCells() { return totalCells;} //获取错误信息-暂时未用到暂时留着 public String getErrorInfo() { return errorMsg; } /** * 读EXCEL文件，获取客户信息集合 * @param fielName * @return */ public List<Customer> getExcelInfo(MultipartFile Mfile){ //把spring文件上传的MultipartFile转换成CommonsMultipartFile类型 CommonsMultipartFile cf= (CommonsMultipartFile)Mfile; //获取本地存储路径 File file = new File("D:\\fileupload"); //创建一个目录 （它的路径名由当前 File 对象指定，包括任一必须的父路径。） if (!file.exists()) file.mkdirs(); //新建一个文件 File file1 = new File("D:\\fileupload\\" + new Date().getTime() + ".xls"); //将上传的文件写入新建的文件中 try { cf.getFileItem().write(file1); } catch (Exception e) { e.printStackTrace(); } //初始化客户信息的集合 List<Customer> customerList=new ArrayList<Customer>(); //初始化输入流 FileInputStream is = null; Workbook wb = null; try{ //根据新建的文件实例化输入流 is = new FileInputStream(file1); //根据excel里面的内容读取客户信息 //当excel是2003时 wb = new HSSFWorkbook(is); //当excel是2007时 //wb = new XSSFWorkbook(is); //读取Excel里面客户的信息 customerList=readExcelValue(wb); is.close(); }catch(Exception e){ e.printStackTrace(); } finally{ if(is !=null) { try{ is.close(); }catch(IOException e){ is = null; e.printStackTrace(); } } } return customerList; } /** * 读取Excel里面客户的信息 * @param wb * @return */ private List<Customer> readExcelValue(Workbook wb){ //得到第一个shell Sheet sheet=wb.getSheetAt(0); //得到Excel的行数 this.totalRows=sheet.getPhysicalNumberOfRows(); //得到Excel的列数(前提是有行数) if(totalRows>=1 && sheet.getRow(0) != null){//判断行数大于一，并且第一行必须有标题（这里有bug若文件第一行没值就完了） this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells(); }else{ return null; } List<Customer> customerList=new ArrayList<Customer>();//声明一个对象集合 Customer customer;//声明一个对象 //循环Excel行数,从第二行开始。标题不入库 for(int r=1;r<totalRows;r++){ Row row = sheet.getRow(r); if (row == null) continue; customer = new Customer(); //循环Excel的列 for(int c = 0; c <this.totalCells; c++){ Cell cell = row.getCell(c); if (null != cell){ if(c==0){ customer.setCustomer1(getValue(cell));//得到行中第一个值 }else if(c==1){ customer.setCustomer2(getValue(cell));//得到行中第二个值 }else if(c==2){ customer.setCustomer3(getValue(cell));//得到行中第三个值 } } } //添加对象到集合中 customerList.add(customer); } return customerList; } /** * 得到Excel表中的值 * * @param cell * Excel中的每一个格子 * @return Excel中每一个格子中的值 */ @SuppressWarnings({ "static-access", "unused" }) private String getValue(Cell cell) { if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) { // 返回布尔类型的值 return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) { // 返回数值类型的值 return String.valueOf(cell.getNumericCellValue()); } else { // 返回字符串类型的值 return String.valueOf(cell.getStringCellValue()); } } }
entity: Customer.java
package com.shiliu.game.domain.bean; /** * @author wkr * @Date 2016-11-18 * 实体类 */ public class Customer { private Integer id; private String Customer1; private String Customer2; private String Customer3; public Customer() { super(); } public Customer(Integer id, String customer1, String customer2, String customer3) { super(); this.id = id; Customer1 = customer1; Customer2 = customer2; Customer3 = customer3; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getCustomer1() { return Customer1; } public void setCustomer1(String customer1) { Customer1 = customer1; } public String getCustomer2() { return Customer2; } public void setCustomer2(String customer2) { Customer2 = customer2; } public String getCustomer3() { return Customer3; } public void setCustomer3(String customer3) { Customer3 = customer3; } }
效果页面：
