• excel导入数据到mysql


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

    先看下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());
        }

     先看看测试表数据

    启动项目后的index.jsp页面

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

    添加数据进去上传文件

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

  • 相关阅读:
    「题解」洛谷 P1169 [ZJOI2007]棋盘制作
    「题解」洛谷 P4147 玉蟾宫
    dsu on tree 学习笔记
    [USACO08FEB]Hotel G「线段树」
    城市环路「树形DP」
    Siano「线段树」
    Emiya 家今天的饭「容斥+DP」
    Matlab调用其他文件夹下的函数
    基于小波金字塔的简单图像融合算法matlab实现
    知网引文网络使用方法
  • 原文地址:https://www.cnblogs.com/magepi/p/10509099.html
Copyright © 2020-2023  润新知