Java使用poi导入Excel文件

前面说到下载模板后，对数据需要导入。

工具类

ImportExcelUtils :excel模板工具类
FileUtils：文件上传工具类(这个工具类作用比较大，很多地方用的着，包括头像上传）
ResponseData ：对需要返回信息的一个封装工具类

导入需要maven引入的jar包(这里用的是poi)

pom.xml

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10.1</version>
        </dependency>

        <dependency>
           <groupId>org.apache.poi</groupId>
           <artifactId>poi</artifactId>
           <version>3.6</version>
       </dependency>

Excel导入的一个模板工具类

导入包
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
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.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ImportExcelUtils {
    private final static String excel2003L =".xls";    //2003- 版本的excel  
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel  

    /** 
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象 
     * @param in,fileName 
     * @return 
     * @throws IOException  
     */  
    public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{  
        List<List<Object>> list = null;  

        //创建Excel工作薄  
        Workbook work = this.getWorkbook(in,fileName);  
        if(null == work){  
            throw new Exception("创建Excel工作薄为空！");  
        }  
        Sheet sheet = null;  
        Row row = null;  
        Cell cell = null;  

        list = new ArrayList<List<Object>>();  
        //遍历Excel中所有的sheet  
        for (int i = 0; i < work.getNumberOfSheets(); i++) {  
            sheet = work.getSheetAt(i);  
            if(sheet==null){continue;}  

            //遍历当前sheet中的所有行  
            for (int j = sheet.getFirstRowNum(); j <=sheet.getLastRowNum(); j++) {  
                row = sheet.getRow(j); 
                if(row==null||row.getFirstCellNum()==j){continue;}  

                //遍历所有的列  
                List<Object> li = new ArrayList<Object>();  
                for (int k = row.getFirstCellNum(); k <row.getLastCellNum(); k++) {  
                    cell = row.getCell(k);
                    if(cell==null){continue;}  

                    li.add(this.getCellValue(cell));  
                }  
                list.add(li);  
            }  
        }  
        System.out.println(list);
        return list;  

        }

    /** 
     * 描述：根据文件后缀，自适应上传文件的版本  
     * @param inStr,fileName 
     * @return 
     * @throws Exception 
     */  
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
        Workbook wb = null;  
        String fileType = fileName.substring(fileName.lastIndexOf("."));  
        if(excel2003L.equals(fileType)){  
            wb = new HSSFWorkbook(inStr);  //2003-  
        }else if(excel2007U.equals(fileType)){  
            wb = new XSSFWorkbook(inStr);  //2007+  
        }else{  
            throw new Exception("解析的文件格式有误！");  
        }  
        return wb;  
    }  


    /** 
     * 描述：对表格中数值进行格式化 
     * @param cell 
     * @return 
     */  
    public  Object getCellValue(Cell cell){  
        Object value = null;  
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符  
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化  
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字  

        switch (cell.getCellType()) {  
        case Cell.CELL_TYPE_STRING:  
            value = cell.getRichStringCellValue().getString();  
            break;  
        case Cell.CELL_TYPE_NUMERIC:  
            if("General".equals(cell.getCellStyle().getDataFormatString())){  
                value = df.format(cell.getNumericCellValue());  
            }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){  
                value = sdf.format(cell.getDateCellValue());  
            }else{  
                value = df2.format(cell.getNumericCellValue());  
            }  
            break;  
        case Cell.CELL_TYPE_BOOLEAN:  
            value = cell.getBooleanCellValue();  
            break;  
        case Cell.CELL_TYPE_BLANK:  
            value = "";  
            break;  
        default:  
            break;  
        }  
        return value;  
    }      



}

这是Excel解析工具类,参考地址：https://blog.csdn.net/weixin_35751376/article/details/60139028
大家也可以借鉴这位博主的这篇，地址：https://blog.csdn.net/xuanzhangran/article/details/70158995

前端js

一般上传，必须走form表单，需要对文件解析成流的方式进行上传。

upload.jsp

 <form  id="importFrom" action="${request.getContextPath()}/example/upload.do" method="post" enctype="multipart/form-data" >      
    <button type="button"  onclick="uploadFile()">导入</button>

    <input type='file' id='fileUp' name='fileData' style="display: none" onchange="fu()" />
</form>             

隐藏一个file,通过点击按钮来触发上传

以ajax方式对文件进行上传

//excel导入
    function uploadFile(){       
        $("#fileUp").trigger("click");
    }

    function fu() {
         var files = document.getElementById('fileUp').files;  
         var fileSize = 0;
            if(files.length!=0){
                fileSize = files[0].size;
            }
         $.ajaxFileUpload({
            url : '${request.getContextPath()}/example/upload.do',
            secureuri : false, //是否启用安全提交 默认为false
            fileElementId : 'fileUp', //file标签的id  
            dataType : 'jsonp',//返回数据的类型  
            success : function(data) {                
                if(data=='null'){
                    alert("导入信息成功");
                    window.location.reload();
                }else{
                    alert("导入信息以下部分异常，请重新导入失败部分："+data);
                    window.location.reload();
                }
            },
            error : function(data, status, e) {
                alert(e);
            }
        });
    }

上面回调的data是后台中对数据导入保存前验证不合格数据的返回。和一般我们新增数据做信息验证本质一样。
$.ajaxFileUpload这样需要引入一个ajaxfileupload.js.以下是我的一个地址

下载地址：https://download.csdn.net/download/tt336798/10632016

后台代码Controller

导入方法(springmvc上传的方式)

往下看借鉴后面的一个controller

  //@RequestParam("fileData") 对应页面中的name
  @RequestMapping(value="upload",method={RequestMethod.GET,RequestMethod.POST})
  public List  uploadExcel(@RequestParam("fileData") MultipartFile file,HttpServletRequest request,HttpServletResponse response) throws Exception {

            InputStream in =null;
            List<List<Object>> listob = null;
            if(file.isEmpty()){
                throw new Exception("文件不存在！");
            }
            in = file.getInputStream();
            listob = new ImportExcelUtils().getBankListByExcel(in,file.getOriginalFilename());
            in.close();

            //一个个的取值放入到对应的属性setXX()中
           //声明变量，接受数据
            String empName=null;          
            List<String> errorList = new ArrayList<>();
            for (int i = 0; i < listob.size(); i++) {
                List<Object> lo = listob.get(i);

                 StaffEntity sta=new StaffEntity();   //创建对象         
                //姓名
                empName=String.valueOf(lo.get(0)).trim();
                if(empName==null){
                    String msg = "第" + (i + 2) + "行的姓名不能为空";
                    errorList.add(msg);
                }
              sta.setEmpName(empName);
              //........
             //后面调用保存的方法，将对象进行数据保存(前台我写的是json数据，后面记得要转json)
             //.........
         }       

        return errorList;
}           

以上这样，需要在springMVC配置中做配置信息

<!-- 文件上传解析器 -->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    <property name="defaultEncoding" value="utf-8"></property>
    <property name="maxUploadSize" value="10485760000"></property><!-- 最大上传文件大小 -->
    <property name="maxInMemorySize" value="10960"></property>
</bean>

---

后台另外一种，需要借助一个工具类(最后有两张图片详解)

FileUtils工具类

导入包

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.util.Date;
//
import javax.servlet.http.HttpServletRequest;
//
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;
//
import sun.misc.BASE64Decoder;

public class FileUtils {

    public static String fileUpload(HttpServletRequest request,String fileRoot, String rootPath, String extArr) throws IOException {

        MultipartFile fileData = null;
        CommonsMultipartResolver commonsMultipartResolver = new CommonsMultipartResolver();
        if (commonsMultipartResolver.isMultipart(request)) {
            MultipartHttpServletRequest multipartFile = commonsMultipartResolver
                    .resolveMultipart(request);

            fileData = multipartFile.getFile("fileData"); //页面中name
        }
        if (null == fileData || fileData.getSize() == 0) {
            System.out.println("fileData="+fileData);
            return null;
        }
        String fileName = fileData.getOriginalFilename();  //获得文件名字
        String ext = fileName.substring(fileName.indexOf("."));  //获取.xls
        File filePath = new File(rootPath + fileRoot);

        //文件不存在的时候创建新文件夹
        if (!filePath.exists()) {
            filePath.mkdirs();
        }
        if (ext != null && ext.length() > 1&& extArr.indexOf(ext.toLowerCase().substring(1)) > -1) {
            InputStream is = null;
            String tempName = new Date().getTime() + ext;  //产生新的文件名
            String url = rootPath + fileRoot + tempName;
            File file = new File(url);
            if (!file.exists()) {
                file.createNewFile();
            }
            FileOutputStream out = new FileOutputStream(file);
            try {
                is = fileData.getInputStream();
                IOUtils.copy(is, out);

            } catch (IOException e) {
                e.printStackTrace();
                return null;
            } finally {
                if (out != null) {
                    out.close();
                }
                if (is != null) {
                    is.close();
                }
            }
            return fileRoot + tempName;
        } else {
            throw new IOException("文件类型不匹配");
        }

    }

    public static String createFile(String rootPath, String childPath,
            String content, String fileName) throws IOException {
        try {
            File dir = new File(rootPath + childPath);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            File file = new File(rootPath + childPath + fileName);
            if (!file.exists()) {
                file.createNewFile();
            }
            OutputStreamWriter oStreamWriter = new OutputStreamWriter(
                    new FileOutputStream(file), "utf-8");
            oStreamWriter.write(content);
            oStreamWriter.flush();
            oStreamWriter.close();

            // HtmlImageGenerator hig=new HtmlImageGenerator();

            // hig.loadHtml(content);
            // hig.saveAsImage(rootPath + childPath + imgName);

            return childPath + fileName;
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return null;
    }

    /**
     * base64生成文件
     * 
     * @param rootPath
     * @param childPath
     * @param content
     * @param fileName
     * @return
     * @throws IOException
     */
    public static String createBase64File(String rootPath, String childPath,
            String content, String fileName) throws IOException {
        if (StringUtils.isBlank(content)) {
            return null;
        }
        try {
            File dir = new File(rootPath + childPath);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            File file = new File(rootPath + childPath + fileName);
            if (!file.exists()) {
                file.createNewFile();
            }

            BASE64Decoder decoder = new BASE64Decoder();

            // Base64解码
            byte[] b = decoder.decodeBuffer(content);
            for (int i = 0; i < b.length; ++i) {
                if (b[i] < 0) {// 调整异常数据
                    b[i] += 256;
                }
            }
            // 生成jpeg图片
            OutputStream out = new FileOutputStream(file);
            out.write(b);
            out.flush();
            out.close();
            return childPath + fileName;
        } catch (Exception e) {
            e.printStackTrace();

        }

        return null;
    }

    public static String readFile(String rootPath, String childPath,
            String fileName) throws IOException {
        try {

            File file = new File(rootPath + childPath + fileName);
            FileInputStream in = new FileInputStream(file);

            int size = in.available();

            byte[] buffer = new byte[size];

            in.read(buffer);

            in.close();

            String str = new String(buffer, "utf-8");
            return str;
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return null;
    }

    public static String copyFile(String rootPath, String childPath,
            String srcFileName, String descFileName) throws IOException {

        FileInputStream in = new FileInputStream(rootPath + childPath + srcFileName);
        FileOutputStream out = new FileOutputStream(rootPath + childPath + descFileName);
        try {

            IOUtils.copy(in, out);

        } catch (IOException e) {
            return null;
        } finally {
            if (out != null) {
                out.close();
            }
            if (in != null) {
                in.close();
            }
        }
        return childPath + descFileName;

    }

}

maven需要导入（包本类需要的）

pom.xml

后面自己写了个对错误信息封装的工具类

返回值工具类

public class ResponseData {
    public static final String ERRORS_KEY = "errors";

    private String message;
    private int code = 0;
    private Object data;

    public ResponseData() {
        this.code = 0;
    }

    public String getMessage() {
        return message;
    }

    public int getCode() {
        return code;
    }

    public Object getData() {
        return data;
    }

    public ResponseData putDataValue(Object value) {
        this.data = value;
        return this;
    }
    public ResponseData fail(String message) {
        return this.setResponseData(10003, message, null);
    }
}

好了，万事具备，这里开始展示后台信息controller

后台Controller

@Autowired
private StaffServiceImpl staffServiceImpl;

//@Value("${file.root.path}")
private String rootPath="/D:";

//@Value("${file.csv.path}")
private String tempFile = "/MyWorkDoc";



/**
     * 导入Excel
     * 
     * @param file
     * @param request
     * @param response
     * @param msgList 
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "upload")
    @ResponseBody
public ResponseData uploadExcel(HttpServletRequest request,
            HttpServletResponse response) throws Exception {

        System.out.println("开始导入.....");
        ResponseData responseData = new ResponseData();
        try {
            String url = FileUtils.fileUpload(request, tempFile, rootPath,".xls");
            if (StringUtils.isBlank(url)) {
                responseData.fail("导入信息异常");
            }
            File file = new File(rootPath + url);
            if (!file.exists()) {
                responseData.fail("导入信息异常");
            }

            List<List<Object>> listob = null;

            InputStream in = new FileInputStream(file);
            listob = new ImportExcelUtils().getBankListByExcel(in, url); //解析Excel
            in.close();

            // 声明变量，接受数据
            String empName = null;
            String idCard = null;
            String phoneCode = null;
            List<String> errorList = new ArrayList<>();
            for (int i = 0; i < listob.size(); i++) {
                List<Object> lo = listob.get(i);
                StaffEntity sta = new StaffEntity();

                // 姓名
                empName = String.valueOf(lo.get(0)).trim();
                if (StringUtils.isBlank(empName)) {
                    String msg = "第" + (i + 2) + "行的姓名不能为空";
                    errorList.add(msg);
                    continue;
                }

                // 手机号
                phoneCode = String.valueOf(lo.get(1));
                if (StringUtils.isBlank(phoneCode)) {
                    errorList.add("第" + (i + 2) + "行: 手机号为空");
                    continue;
                }
                StaffEntity PhoneCordEntity = staffServiceImpl.findByStaffTel(phoneCode); //验证手机号码唯一
                if (PhoneCordEntity != null) {
                    errorList.add("第" + (i + 2) + "行: 手机号已经存在");
                    continue;
                }

                // 身份证号
                idCard = String.valueOf(lo.get(2));
                if (StringUtils.isBlank(idCard)) {
                    errorList.add("第" + (i + 2) + "行: 身份证号为空");
                    continue;
                }
                StaffEntity idcardEntity = this.staffServiceImpl.findByStaffIdCard(idCard);//验证身份证号码唯一
                if (idcardEntity != null) {
                    errorList.add("第" + (i + 2) + "行: 员工身份证号码已经存在");
                    continue;
                }


                sta.setStaffName(empName);          
                sta.setStaffTel(phoneCode); // 手机号
                sta.setStaffIdCard(idCard); // 身份证号
                sta.setStaffBankCard(String.valueOf(lo.get(3))); // 银行卡

                sta.setUpdateTime(new Date());
                sta = this.staffServiceImpl.save(sta);

            }
            if (errorList != null && errorList.size() > 0) {
                responseData.putDataValue(JSON.toJSONString(errorList));
            }
        } catch (Exception e) {
            e.printStackTrace();
            responseData.serverInternalError();
        }
        return responseData;
    }   

回到前台JS页面(作稍微的更改)

success : function(data) {  
            if(data.data=='null'){
                alert("导入员工信息成功");
                window.location.reload();
            }else{
                alert("导入员工信息以下部分异常，请重新导入失败部分："+data.data);
                window.location.reload();
            }
},

最后这里上两张FileUtils的详解图，可以参考它的属性值

图中fileName是我要上传的文件名

使用了shiro框架，request会自动转换为ShiroHttpServletRequest,这是我们通常遇到的问题，在这个类中很好的避免了。
会遇到的错误是：
java.lang.ClassCastException: org.apache.shiro.web.servlet.ShiroHttpServletRequest cannot be cast to org.springframework.web.multipart.MultipartHttpServletRequest