利用POI导入Excel数据到数据库，基于Spring+SpringMVC+Mybatis

翻阅了网上一些博文，整理了一下。

实现功能: 使用Excel表向数据库批量导入数据。（如有错误还望指正）

 

1.Maven依赖

             <!-- poi -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.9</version>
            </dependency>

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

            <dependency>
                <groupId>xmlbeans</groupId>
                <artifactId>xmlbeans</artifactId>
                <version>2.3.0</version>
            </dependency>

            <dependency>
                <groupId>stax</groupId>
                <artifactId>stax-api</artifactId>
                <version>1.0.1</version>
            </dependency>

 

2.Controller

   
/**
 * 客服中心的Controller
 */
@Controller
public class CustomerServiceCenterController {


    private CustomerServiceCenterService customerServiceCenterService;

    @Autowired
    public CustomerServiceCenterController(CustomerServiceCenterService customerServiceCenterService){
        this.customerServiceCenterService = customerServiceCenterService;
    }


    /**
     * 导入投诉记录
     * @param file
     * @return
     */
    @RequestMapping(value = "/importComplaints", method = RequestMethod.POST ,produces = "application/json;charset=utf-8")
    @ResponseBody
    public String importComplaints(@RequestParam("fileName") MultipartFile file){

        //判断文件名是否为空
        if(file==null) return null;

        //获取文件名
        String name=file.getOriginalFilename();

        //判断文件大小、即名称
        long size=file.getSize();
        if(name==null || ("").equals(name) && size==0) return null;

       
        try {
            //把文件转换成字节流形式
            InputStream in = file.getInputStream();
            customerServiceCenterService.importComplaints(name,file);

        } catch (Exception e) {
            e.printStackTrace();
        }

        
        return null; //可根据自己需求返回成功记录，成功数等
    }


}

 

3.service实现

/**
     * 导入投诉记录
     * @param name
     * @param file
     * @return
     */
    @Override
    public void importComplaints(String name, MultipartFile file) {

        //处理EXCEL
        ReadExcel readExcel=new ReadExcel();
        //解析Excel，获得Excel里的数据集
        List<UComplaints> uComplaintsList=readExcel.getExcelInfo(name,file);

     
        //操作数据库，将uComplaintsList插入到数据库中即可


        return null;
    }

 

 

4. ReadExcel类

import com.platform.pojo.UComplaints;
import org.apache.commons.fileupload.disk.DiskFileItem;
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;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

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 filePath
     * @return
     */
    public boolean validateExcel(String filePath){
        if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))){
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }


    /**
     * 读Excel文件
     * @return
     */
    public List<UComplaints> getExcelInfo(String fileName, MultipartFile Mfile){

        //把spring文件上传的MultipartFile转换成File
        CommonsMultipartFile cf= (CommonsMultipartFile)Mfile;
        DiskFileItem fi = (DiskFileItem)cf.getFileItem();
        File file = fi.getStoreLocation();

        List<UComplaints> uComplaintsList =new ArrayList<UComplaints>();
        InputStream is = null;
        try{
            //验证文件名是否合格
            if(!validateExcel(fileName)){
                return null;
            }
            //判断文件时2003版本还是2007版本
            boolean isExcel2003 = true;
            if(WDWUtil.isExcel2007(fileName)){
                isExcel2003 = false;
            }
            is = new FileInputStream(file);
            uComplaintsList =getExcelInfo(is, isExcel2003);
            is.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        finally{
            if(is !=null)
            {
                try{
                    is.close();
                }catch(IOException e){
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        return uComplaintsList;
    }
    /**
     * 此方法两个参数InputStream是字节流。isExcel2003是excel是2003还是2007版本
     * @param is
     * @param isExcel2003
     * @return
     * @throws IOException
     */
    public  List<UComplaints> getExcelInfo(InputStream is,boolean isExcel2003){

        List<UComplaints> uComplaintsList=null;
        try{
            /** 根据版本选择创建Workbook的方式 */
            Workbook wb = null;
            //当excel是2003时
            if(isExcel2003){
                wb = new HSSFWorkbook(is);
            }
            else{
                wb = new XSSFWorkbook(is);
            }
            uComplaintsList = readExcelValue(wb);
        }
        catch (IOException e)  {
            e.printStackTrace();
        }
        return uComplaintsList;
    }
    /**
     * 读取Excel里面的信息
     * @param wb
     * @return
     */
    private List<UComplaints> readExcelValue(Workbook wb){
        //得到第一个shell
        Sheet sheet=wb.getSheetAt(0);

        //得到Excel的行数
        this.totalRows=sheet.getPhysicalNumberOfRows();

        //得到Excel的列数(前提是有行数)
        if(totalRows>=1 && sheet.getRow(0) != null){
            this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
        }

        List<UComplaints> uComplaintsList =new ArrayList<UComplaints>();
        UComplaints uComplaints;           //投诉记录的bean


        //循环Excel行数,从第二行开始。标题不入库
        for(int r=1; r<totalRows; r++)
        {
            Row row = sheet.getRow(r);
            if (row == null) continue;

            uComplaints=new UComplaints();


            //循环Excel的列
            for(int c = 0; c <this.totalCells; c++)
            {
                Cell cell = row.getCell(c);
                if (null != cell)
                {
                    //第一列
                    if(c==0){
                        //获得第一列<手机号>
                        /**
                         * 处理：使用POI读excel文件，当遇到特殊格式的字串，比如“13612345678”，等等，
                         * 这样的本来是一个字符串，但是POI在读的时候总是以数值型识别，由此，这样的电话号码读出来后总是1.3XXX+E4
                         */
                        DecimalFormat df = new DecimalFormat("#");
                        String cellValue=df.format(cell.getNumericCellValue());
                        uComplaints.setPhonenum(cellValue);

                    }
                    //获得第二列<产品id>，放到到用户登录bean中。作为登录账号及密码
                    else if(c==1){
                       
                        DecimalFormat df = new DecimalFormat("#");
                        String cellValue=df.format(cell.getNumericCellValue());
                        Integer appId = null;
                        try {
                            appId = Integer.parseInt(cellValue);
                        } catch (NumberFormatException e) {
                            e.printStackTrace();
                        }
                        uComplaints.setAppId(appId);


                    }
                    //第三列  投诉类型
                    else if(c==2) {
                        DecimalFormat df = new DecimalFormat("#");
                        String cellValue=df.format(cell.getNumericCellValue());
                        Integer ctype = null;
                        try {
                            ctype = Integer.parseInt(cellValue);
                        } catch (NumberFormatException e) {
                            e.printStackTrace();
                        }
                        uComplaints.setCtype(ctype);
                  }

                    //第四列描述
                    else if(c==3){
                        uComplaints.setMark(cell.getStringCellValue());
                    }


                    //第五列状态
                    else if (c==4){
                        DecimalFormat df = new DecimalFormat("#");
                        String cellValue=df.format(cell.getNumericCellValue());
                        Integer istatus = null;
                        try {
                            istatus = Integer.parseInt(cellValue);
                        } catch (NumberFormatException e) {
                            e.printStackTrace();
                        }
                        uComplaints.setIstatus(istatus);
                    }

                    //第六列结果
                    else if (c==5){
                        uComplaints.setResultdesc(cell.getStringCellValue());
                    }

                    //第七列投诉事件
                    else if (c==6){
                        uComplaints.setAddtime(cell.getDateCellValue());
                    }

                    //第八列处理时间
                    else if (c==7){
                        uComplaints.setResulttime(cell.getDateCellValue());
                    }


                    //第九列  合作伙伴id
                    else if (c==8){
                        DecimalFormat df = new DecimalFormat("#");
                        String cellValue=df.format(cell.getNumericCellValue());
                        Integer coId = null;
                        try {
                            coId = Integer.parseInt(cellValue);
                        } catch (NumberFormatException e) {
                            e.printStackTrace();
                        }
                        uComplaints.setCoId(coId);
                    }

                    //第十列  通道id
                    else if (c==9){
                        DecimalFormat df = new DecimalFormat("#");
                        String cellValue=df.format(cell.getNumericCellValue());
                        Integer bchannelid = null;
                        try {
                            bchannelid = Integer.parseInt(cellValue);
                        } catch (NumberFormatException e) {
                            e.printStackTrace();
                        }
                        uComplaints.setBchannelid(bchannelid);
                    }

                    //第十一列订单事物号
                    else if (c==10){
                        DecimalFormat df = new DecimalFormat("#");
                        String cellValue=df.format(cell.getNumericCellValue());
                        Integer pid = null;
                        try {
                            pid = Integer.parseInt(cellValue);
                        } catch (NumberFormatException e) {
                            e.printStackTrace();
                        }
                        uComplaints.setPid(pid);
                    }

                    //第十二列区域名
                    else if (c==11){
                        uComplaints.setRegionCode(cell.getStringCellValue());
                    }
                   

                    //按照自己数据库字段类型和Excels数据类型来读取
                    

                }
            }
            


            uComplaintsList.add(uComplaints);
        }
        return uComplaintsList;
    }
}
/**
 * @描述：工具类
 * 检验是否是EXCEL文件
 */
class WDWUtil
{
    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
}

 

5.测试

<html>
<body>
<form name="uploadForm" method="POST"
      enctype="multipart/form-data"
      action="/importComplaints">
    Upload File1:<input type="file" name="filename" size="30"/>
    <input type="submit" name="submit" value="提交">
    <input type="reset" name="reset" value="重置">
</form>

</body>
</html>

 

转载于:https://my.oschina.net/u/3588051/blog/1526243