excel导入数据到mysql

时间：2022-11-07 20:23:02

因为业务需要用到导入数据的功能,所以自己也研究了下,记录下来

先看下maven包

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

    <!--上传下载的包-->
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.3.1</version>
    </dependency>
    <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>2.4</version>
    </dependency>

前端的index.jsp 先下载excel模板再填写内容后导入mysql(大致是这个流程)

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta http-equiv="pragma" content="no-cache" />
    <base target="_self">
    <title>文件上传</title>
</head>
<body>
<a href="test/download.do?fileName=test.xls">下载Exel模板</a>
<form method="post" action="test/import.do" enctype="multipart/form-data">
    <input type="file" name="files" value="file">
    <input type="submit" value="确定">
</form>
</body>
</html>

mapper的sql语句(我用的测试表里面只有这三个字段)

<!--Excel批量导入-->
    <insert id="insertInfoBatch" parameterType="java.util.List">
        insert into my_test(id,name,endTime)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id},#{item.name},#{item.endTime})
        </foreach>
    </insert>

这是service层方法(注:这里的PageData等同于map)

 //excel批量导入数据
    public void importExcelInfo(InputStream in, MultipartFile file) throws Exception {
        List<List<Object>> listob = ExcelUtil.getBankListByExcel(in,file.getOriginalFilename());
        List<PageData> salaryList = new ArrayList<>();
        //遍历listob数据，把数据放到List中
        for (int i = 0; i < listob.size(); i++) {
            List<Object> ob = listob.get(i);
            PageData pd=new PageData();
            //设置编号

            pd.put("id",String.valueOf(ob.get(1)));
            //通过遍历实现把每一列封装成一个model中，再把所有的model用List集合装载
            pd.put("name",String.valueOf(ob.get(2)));
            pd.put("endTime",String.valueOf(ob.get(3)));
            //object类型转Double类型
            salaryList.add(pd);
        }
        //批量插入
        dao.batchSave("TestMapper.insertInfoBatch",salaryList);
    }

因为我用的是SqlSessionTemplate进行增删查改,没有用实体类的方式(框架为ssm)

dao

package com.pskj.JXGSLZ.dao;

import java.util.List;

public interface Dao {

    /**
     * 保存对象
     *
     * @param str
     * @param obj
     * @return
     * @throws Exception
     */
    Object save(String str, Object obj) throws Exception;

    /**
     * 批量保存
     * @param str
     * @param objs
     * @return
     * @throws Exception
     */
    Object batchSave(String str, List objs)throws Exception;

    /**
     * 修改对象
     *
     * @param str
     * @param obj
     * @return
     * @throws Exception
     */
    Object update(String str, Object obj) throws Exception;

    /**
     * 删除对象
     *
     * @param str
     * @param obj
     * @return
     * @throws Exception
     */
    Object delete(String str, Object obj) throws Exception;

    /**
     * 查找对象
     *
     * @param str
     * @param obj
     * @return
     * @throws Exception
     */
    Object findForObject(String str, Object obj) throws Exception;

    /**
     * 查找对象
     *
     * @param str
     * @param obj
     * @return
     * @throws Exception
     */
    Object findForList(String str, Object obj) throws Exception;



}

DaoImpl(dao的实现类)

package com.pskj.JXGSLZ.dao.daoImpl;


import com.pskj.JXGSLZ.dao.Dao;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

@Repository("daoImpl")
public class DaoImpl implements Dao {

    @Resource(name="sqlSessionTemplate")
    private SqlSessionTemplate sqlSessionTemplate;

    /**
     * 保存对象
     *
     * @param str
     * @param obj
     * @return
     * @throws Exception
     */
    public Object save(String str, Object obj){
        return sqlSessionTemplate.insert(str, obj);

    }

    /**
     * 批量保存
     * @param str
     * @param objs
     * @return
     * @throws Exception
     */
    public Object batchSave(String str, List objs ){
        return sqlSessionTemplate.insert(str, objs);
    }

    /**
     * 批量保存
     * @param str
     * @param objs
     * @return
     * @throws Exception
     */
//    public void batchSave(String str, List objs ){
//        SqlSessionFactory sqlSessionFactory=sqlSessionTemplate.getSqlSessionFactory();
//        //批量执行器
//        SqlSession sqlSession=sqlSessionFactory.openSession(ExecutorType.BATCH,false);
//        try{
//            if(objs!=null){
//                for(int i=0,len=objs.size();i<len;i++){
//                    sqlSession.insert(str,objs.get(i));
//                }
//                sqlSession.flushStatements();
//                sqlSession.commit();
//                sqlSession.clearCache();
//
//            }
//        }catch (Exception e){
//            e.printStackTrace();
//        }finally {
//            sqlSession.close();
//        }
//    }


    /**
     * 修改对象
     *
     * @param str
     * @param obj
     * @return
     * @throws Exception
     */
    @Override
    public Object update(String str, Object obj){
        return sqlSessionTemplate.update(str,obj);
    }

    /**
     * 删除对象
     *
     * @param str
     * @param obj
     * @return
     * @throws Exception
     */
    public Object delete(String str, Object obj){
        return sqlSessionTemplate.delete(str, obj);
    }


    /**
     * 查找对象
     *
     * @param str
     * @param obj
     * @return
     * @throws Exception
     */
    @Override
    public Object findForObject(String str, Object obj){
        return sqlSessionTemplate.selectOne(str, obj);
    }

    /**
     * 查找对象
     *
     * @param str
     * @param obj
     * @return
     * @throws Exception
     */
    @Override
    public Object findForList(String str, Object obj){
        return sqlSessionTemplate.selectList(str, obj);
    }



}

操作excel需要的操作类

ExcelUtil

package com.pskj.JXGSLZ.utils.Excel;


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 java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @date 2018/10/11
 * 导出excel
 */
public class ExcelUtil {
    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel
    /**
     * Excel导入
     */
    public static List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception {
        List<List<Object>> list = null;
        //创建Excel工作薄
        Workbook work = 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 y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));
                }
                list.add(li);
            }
        }
        return list;
    }
    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     */
    public static  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;
    }
    /**
     * 描述：对表格中数值进行格式化
     */
    public static Object getCellValue(Cell cell){
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化字符类型的数字
        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;
    }


}

再就是controller层(注testService是我自己写的service)

//Excel导入数据
    @RequestMapping("import")
    public void impotr(HttpServletRequest request)  {
        Map map=new HashMap();
        //获取上传的文件
        MultipartHttpServletRequest multipart = (MultipartHttpServletRequest) request;
        MultipartFile file = multipart.getFile("files");
        try{
            InputStream in = file.getInputStream();
            //数据导入
            testService.importExcelInfo(in,file);
            in.close();//关闭流
            map.put("success",true);
        }catch(Exception e){
            e.printStackTrace();
            map.put("success",false);
        }
        JsonUtils.writeJson(map, this.getRequest(), this.getResponse());
    }

 先看看测试表数据

excel导入数据到mysql

启动项目后的index.jsp页面

excel导入数据到mysql

点击下载Excel模板得到如下(这是事先制作好的模板)

excel导入数据到mysql

添加数据进去上传文件

excel导入数据到mysql

再刷新测试表就可以看到数据了

excel导入数据到mysql

标签：

相关文章