java的poi技术上传Excel读取Excel中内容 -

aoyouzi

浏览: 1973218 次
性别:
来自: 北京

最近访客更多访客>>

dy.f

zhaoshijie

lbq136957978

juzhibest

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

java的poi技术上传Excel读取Excel中内容

博客分类：

技术总结
踩坑长经验

java poi Excel

使用到的jar包【注】POI 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;
    }
}

效果页面：

===============

可能的报错：

Expected MultipartHttpServletRequest: is a MultipartResolver configured?

java.lang.IllegalArgumentException: Expected MultipartHttpServletRequest: is a MultipartResolver configured?
at org.springframework.util.Assert.notNull(Assert.java:112)
at org.springframework.web.method.annotation.RequestParamMethodArgumentResolver.resolveName(RequestParamMethodArgumentResolver.java:151)
at org.springframework.web.method.annotation.AbstractNamedValueMethodArgumentResolver.resolveArgument(AbstractNamedValueMethodArgumentResolver.java:86)
at org.springframework.web.method.support.HandlerMethodArgumentResolverComposite.resolveArgument(HandlerMethodArgumentResolverComposite.java:77)
at org.springframework.web.method.support.InvocableHandlerMethod.getMethodArgumentValues(InvocableHandlerMethod.java:157)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:124)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:855)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:502)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1124)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:101)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1115)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1115)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:361)
at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:766)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:417)
at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:230)
at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:324)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:534)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:879)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:741)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:213)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:403)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:522)

解决：

spring mvc中上传附件，需要做两步操作

引入包commons-fileupload-*.jar，版本号可以根据项目情况调整；

在spring mvc配置文件中增加配置，文件大小限制可根据项目情况调整：

<!-- 上传文件拦截，设置最大上传文件大小   10M=10*1024*1024(B)=10485760 bytes -->  
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">  
    <property name="maxUploadSize" value="10485760" />  
</bean>

这样就可以实现upload上传了。

分享到：

对研发团队里技术分享的一些思考 | 使用 Cobertura 和反射机制提高 Java 单元 ...

2016-11-24 13:57
浏览 10340
评论(0)
分类:行业应用
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

java的poi技术上传Excel读取Excel中内容

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

java的poi技术上传Excel读取Excel中内容

评论

发表评论

相关推荐

万字总结Java 9~15新特性

架构制图：工具与方法论

性能优化

【冬察冬见】FFmpeg系列学习笔记

有关创新的一些思考

浅谈面试官的培养

冬察冬见·全视角再议晋升

冬察冬见·晋升-晋升的那些事儿1

线上故障快速排查技巧

物联网MQTT实战

大小公司都适用的架构选型工具箱（涵盖上百个组件）

MySQL EXPLAIN 完全解读

MySQL优化实战

救火必备！问题排查与系统优化手册

直播低延时踩坑

elasticsearch使用踩坑

【冬察冬见】读书日话高效读书

【冬察冬见·荐书】4·23世界读书日 80本书单推荐承包你一年的书单

Mysql高性能优化规范建议

快速上手 AB Test

最近访客更多访客>>