精华内容
下载资源
问答
  • javaexcel中读入数据库存入sqlserver2005数据库
  • java读取excel存入数据库

    热门讨论 2010-12-16 15:54:40
    javaexcel读取数据并保存到sqlserver数据库中。 其中所需包需要自己下,一个是微软的sqlserver连接驱动包。还有一个是连接excel相关的包,网上都能找到。
  • 即上传文件并读取里面内容存入数据库层。 /** * 上传excel文件处理 * @param file * @return * @throws Exception */ @RequestMapping("/uploadExcel") @ResponseBody public R upload(@RequestParam("file") ...

    一、添加包依赖。

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

    二、编写controller层。即上传文件并读取里面内容存入数据库层。

    /**
         * 上传excel文件处理
         * @param file
         * @return
         * @throws Exception
         */
    @RequestMapping("/uploadExcel")
    @ResponseBody
    public R upload(@RequestParam("file") MultipartFile file) {
        List<CheckpointEntity> errorList = new ArrayList<CheckpointEntity>();
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(".xls")) {
             File dest = new File(excelFilePath + fileName);
             HSSFWorkbook hssfWorkbook = null;
             try {
                    file.transferTo(dest); //存入本地服务器
                    //excel模板路径
                    InputStream in = new FileInputStream(dest); // 读取文件流
                    //读取excel模板
                    hssfWorkbook = new HSSFWorkbook(in);
    
                } catch (IOException e) {
                    e.printStackTrace();
                }
                HSSFSheet hssfSheet = null; // 读取的一张表
                try{
                    SimpleDateFormat format=new SimpleDateFormat("yyyy-MM"); //存日期转换
                    int numberOfSheets = hssfWorkbook.getNumberOfSheets(); // 因为要读取每一
                    //张表 所以获取表数量
                    for (int k=0;k<numberOfSheets;k++) {
                        hssfSheet = hssfWorkbook.getSheetAt(k);
                        CheckpointEntity checkpointEntity;
                        for (Row row:hssfSheet) {
                            if (row.getRowNum() > 2) { // 从第三行开始读取
                                checkpointEntity = new CheckpointEntity();
                                String date = String.valueOf(row.getCell(8).getNumericCellValue()); //下面是我自己的业务逻辑 不用去看
                                // System.out.println(date);
                                String newDate = date.replace(".","-");
                                Date date1 = new Date(format.parse(newDate).getTime());
                                // System.out.println(row.getCell(2).getStringCellValue());
                                checkpointEntity.setAddress("福建省福州市福清市" + row.getCell(1).getStringCellValue() + row.getCell(4).getStringCellValue());
                                checkpointEntity.setTbbm(row.getCell(2).getStringCellValue());
                                checkpointEntity.setWfzt(row.getCell(3).getStringCellValue());
                                checkpointEntity.setZdmj(row.getCell(5).getNumericCellValue());
                                checkpointEntity.setJzmj(row.getCell(6).getNumericCellValue());
                                checkpointEntity.setWjxz(row.getCell(7).getStringCellValue());
                                checkpointEntity.setTime(date1);
                                checkpointEntity.setZrr(row.getCell(9).getStringCellValue());
                                checkpointEntity.setCcqkjsm(row.getCell(10).getStringCellValue());
                                checkpointEntity.setClassify(row.getCell(11).getStringCellValue());
                                canUploadOrNot canUpload = houseService.canUpload(checkpointEntity.getTbbm(), date);
    
                                if (canUpload != null) {
                                    Integer integer=canUpload.getObjectid();
                                    Integer state = canUpload.getState();
                                    checkpointEntity.setObjectid(integer);
                                    if (state == 0) {
                                        // System.out.println("保存");
                                        checkpointService.save(checkpointEntity);
                                        Integer integer1 = houseService.updateState(integer);
                                    }
                                    else {
                                        // System.out.println("更新");
                                        checkpointService.updateByObjectId(checkpointEntity);
                                    }
                                    // list.clear();
                                }
                                else {
                                    // System.out.println(checkpointEntity);
                                    errorList.add(checkpointEntity);
                                    // list.clear();
                                }
                            }
                        }
                    }
                    return R.ok().put("errorList",errorList);
                }
                catch (Exception e){
                    e.printStackTrace();
                }
            }
            return R.error("上传格式错误");
        }

     

    展开全文
  • 公司业务,从mysql数据库导入导出excel,但是不能excel的列可能会改变,所以实体类是创建不了。 所以写了 一套excel的方法,但是,,,暂时还不能处理time类的数据,并且只能生成单表 先看数据库,除了时间,其他的...

    使用easyexcel  mybatis  mysql

    公司业务,从mysql数据库导入导出excel,但是不能excel的列可能会改变,所以实体类是创建不了。

    所以写了 一套excel的方法,但是,,,暂时还不能处理time类的数据,并且只能生成单表

    先看数据库,除了时间,其他的类型应该都可以处理,起码String和Integer是可以的,不会图片导入导出

    sql的创建

    /*
     Navicat Premium Data Transfer
    
     Source Server         : localhost
     Source Server Type    : MySQL
     Source Server Version : 50721
     Source Host           : localhost:3306
     Source Schema         : gx
    
     Target Server Type    : MySQL
     Target Server Version : 50721
     File Encoding         : 65001
    
     Date: 13/07/2021 15:46:15
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for user_role
    -- ----------------------------
    DROP TABLE IF EXISTS `user_role`;
    CREATE TABLE `user_role`  (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `u_id` int(11) NULL DEFAULT NULL COMMENT '用户ID',
      `r_id` int(11) NULL DEFAULT NULL COMMENT '角色ID',
      `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态 0 删除 1、使用',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
    
    -- ----------------------------
    -- Records of user_role
    -- ----------------------------
    INSERT INTO `user_role` VALUES (19, 3, 4, '1');
    INSERT INTO `user_role` VALUES (20, 3, 1, '1');
    
    SET FOREIGN_KEY_CHECKS = 1;
    

    先来依赖

    
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.2.1.RELEASE</version>
            <relativePath/>
        </parent>
    
        <dependencies>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
            </dependency>
    
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.1.4</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.7</version>
            </dependency>
    
        </dependencies>

    然后是大体的结构

    GxUtils类里面是三个方法,会处理读取的数据

     /**
         * 生成excel的方法,
         * @param list 数据库查询出来的数据集
         * @param head  excel 的名字
         * @param path  生成文件的路径包含文件名,,如果前后端不分离可以直接给  例子: "F:\\新建文件夹\\练1636.xlsx"
         * @return
         */
        public static String export( List<Map> list,String head,String path){
            try {
                //list.get(0).keySet() 代表整个数据集的key,会变成excel的表头
                easyUtil(list.get(0).keySet(),head,list,path);
            }catch (Exception e){
                return e.toString();
            }
    
            return "成功导出";
        }
    
        /**
         *  处理数据集的方法,和最后生成的方法
         * @param heads  excel的表头
         * @param head  表的名称
         * @param list  数据库查询出来的数据集
         * @param path  生成文件的路径包含文件名
         */
        public static void easyUtil(Set<String> heads, String head, List<Map> list,String path){
            List<List<String>> hs = new ArrayList<>();
            for (String s : heads) {
                hs.add(Arrays.asList(head,s));
            }
            List list2 = new ArrayList<>();
            for (int i = 0; i < list.size(); i++) {
                ArrayList arrayList = new ArrayList<>(list.get(i).values());
                list2.add(arrayList);
            }
            EasyExcel.write(path).head(hs).sheet().doWrite(list2);
        }

    GxController生成excel,算了,service  和 impl, 一起写了,

    //controller
    
    @Autowired
        GxService gxService;
    
    @GetMapping(value = "/export")
        public String export(){
            return gxService.export();
        }
    
    //service
     String export();
    
    //impl
     @Autowired
        GxDao gxDao;
    
        @Override
        public String export() {
            List<Map> list = gxDao.selectAll();
            System.out.println(list);
            //excel的大表头吧,应该,一会看结果 图就懂了
            String head = "练习表";
            //文件路径的文件夹必须存在,不能缺少,可以用file新建
            String path= "F:\\系统激活\\新建文件夹\\新联系.xlsx";
            String export = GxUtils.export(list, head, path);
            return export;
        }
    //GxDao
    List<Map> selectAll();

    然后

    联系表这三个字出来了

    Mapper

    <select id="selectAll" resultType="java.util.Map">
            select * from user_role
        </select>

    然后是把excel导入mysql,注意时间暂时没办法 处理,无论导入导出都不行,因为没有实体类,不能处理Timestamp类型

    GxUtils,我的逻辑是从数据库查出来的是List<Map> 格式的,excel读出来是这样[{0=u_id, 1=id, 2=r_id, 3=status}, {0=3, 1=19, 2=4, 3=1}, {0=3, 1=20, 2=1, 3=1}],截取第一个map,取values值,把他后面的所有的map中的key替换成第一个的map的values

    注意点:Map 只能用ConcurrentHashMap,不然会报错,转换的方法是遍历Map,顺便写入ConcurrentHashMap,而且只能用iterator迭代器遍历,用两个for也会报错,

    public static Map readExcel(Map mapOne,Map map)  {
            Map map1 = new ConcurrentHashMap();
            Map map2 = new ConcurrentHashMap();//创建ConcurrentHashMap
            for (Object w : mapOne.keySet()){//把Map 转换ConcurrentHashMap
                map1.put(w,mapOne.get(w));
            }
    
            for (Object w : map.keySet()){ //把Map 转换ConcurrentHashMap
                map2.put(w,map.get(w));
            }
            //遍历表头信息  例子: 原始数据:[{0=u_id, 1=id, 2=r_id, 3=status}, {0=3, 1=19, 2=4, 3=1}, {0=3, 1=20, 2=1, 3=1}]
            for (Object w : map1.keySet()){
                //把遍历的表头值的key转成String类型,,,,本身是Integer的,
                String f = w.toString();
                //使用iterator迭代器遍历map2,获得键值对   例子: {0=3, 1=19, 2=4, 3=1}
                Iterator iterator12 = map2.keySet().iterator();
                while (iterator12.hasNext()){
                    //把键值对的key转换成String类型
                    String next = iterator12.next().toString();
                    //比较表头的key和键值对的Key,excel都出来的数据是[{0=u_id, 1=id, 2=r_id, 3=status}, {0=3, 1=19, 2=4, 3=1}, {0=3, 1=20, 2=1, 3=1}]
                    // key都是Integer的类型,我没有用==比较,而是把他们转换成String用equals比较的
                    if (f.equals(next)){
                        //相同,把键值对的key,修改成表头的value
                        // Map  map = new HashMap();
                        //  map.put("新的key",map.remove(原来的key));
                        //f和next是两个map的key,查出f对应的value,替换next
                        map2.put(map1.get(Integer.valueOf(f)),map2.remove(Integer.valueOf(next)));
                    }
                }
            }
            return map2;
        }
    

    然后是controller 等

    //controller
     @PostMapping(value = "/readExcel")
        public String readExcel(@RequestBody String path) throws FileNotFoundException {
            System.out.println(path+"*************");
            return gxService.readExcel(path);
        }
    
    //service
    String readExcel(String path) throws FileNotFoundException;
    //impl
      @Override
    //  @Transactional(timeout = 60,rollbackFor = Exception.class)//开启数据库事务 如果使用,把启动类上的@EnableTransactionManagement注解打开
        public String readExcel(String path) throws FileNotFoundException {
            //下方的new FileInputStream("F:\\系统激活\\新建文件夹\\新联系.xlsx")
            // 可以把路径换成前端传回来的path但是报错java.io.FileNotFoundException: "F:\系统激活\新建文件夹\新联系. (文件名、目录名或卷标语法不正确。)
            //等我研究好了再改正,现在先写死
            List<Map> list = EasyExcel.read(new FileInputStream("F:\\系统激活\\新建文件夹\\新联系.xlsx")).doReadAllSync();
            System.out.println("原始数据:"+list);
           List<Map> newList = new ArrayList<>();
            for ( Map map1 : list.subList(1, list.size())){
                Map map2 = GxUtils.readExcel(list.get(0), map1);
                newList.add(map2);
            }
            System.out.println("处理数据"+newList);
            //写入数据库
            gxDao.insertMap(newList);
    //      excel数据量大的话可以先分割
    //        int batchCount = 10000;  //将list切割,每次写入10000条数据,我只有两条数据
    //        int size = newList.size();
    //        int index = size / batchCount;
    //        // 余量
    //        int mod = size % batchCount;
    //        try {
    //            for (int i = 0; i < index; i++) {
    //                gxDao.insertMap(newList.subList(i * batchCount, i * batchCount + batchCount));
    //            }
    //            if (mod > 0) {
    //                gxDao.insertMap(newList.subList(index * batchCount, size));
    //            }
    //        } catch (Exception e) {
             //开启数据库回滚
    //            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
    //            return "更新数据异常,停止更新,请重试";
    //        }
    
            return "成功";
        }
    
    //dao
    
    void insertMap(List<Map> list);

    mapper.xml

    <!-- 使用foreach写入数据库   -->
        <insert id="insertMap" parameterType="List">
            insert into user_role_copy1(id,u_id,r_id,status )
             values
            <foreach collection="list" item="item"  separator=",">
                (#{item.id},#{item.u_id},#{item.r_id},#{item.status})
            </foreach>
        </insert>

    中间的处理

    大致上就这些,可能写的不是很详细,我把 源码放在码云上https://gitee.com/guoxu999/test_excel_shiwu

    展开全文
  • springBoot读取excel存入数据库

    千次阅读 2020-05-04 16:54:38
    springBoot读取excel存入数据库 代码块如下(适用与xls2003及xlsx2007版本): 吾日三省吾身,高否,富否,帅否,否,学习去 maven依赖 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <...

    springBoot读取excel并存入数据库

    代码块如下(适用与xls2003及xlsx2007版本):
    吾日三省吾身,高否,富否,帅否,否,学习去

    maven依赖

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

    Mysql创建表语句

    /*
     Navicat Premium Data Transfer
    
     Source Server         : 127.0.0.1
     Source Server Type    : MySQL
     Source Server Version : 50709
     Source Host           : localhost:3306
     Source Schema         : data
    
     Target Server Type    : MySQL
     Target Server Version : 50709
     File Encoding         : 65001
    
     Date: 04/05/2020 16:29:25
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for workers_data_df
    -- ----------------------------
    DROP TABLE IF EXISTS `workers_data_df`;
    CREATE TABLE `workers_data_df`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `card_num` int(11) NOT NULL,
      `worker_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
      `age` int(30) NULL DEFAULT NULL,
      `sex` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
      `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
      `position` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
      `work_date` date NULL DEFAULT NULL,
      `add_data_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`, `card_num`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 91 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
    
    SET FOREIGN_KEY_CHECKS = 1;
    
    

    excel示例样板
    在这里插入图片描述

    读取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 org.springframework.web.multipart.MultipartFile;
    
    import com.project.main.excel.upload.entity.WorkersPO;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.text.DecimalFormat;
    import java.util.*;
    import java.util.logging.Logger;
    
    
    /**
     * 读取Excel内容
     * @author Administrator
     *
     */
    
    public class ExcelReader {
    
        private static Logger logger = Logger.getLogger(ExcelReader.class.getName()); // 日志打印类
        //Map<String,Object> map = new HashMap<String, Object>();
    
        private static final String XLS = "xls";
        private static final String XLSX = "xlsx";
    
        /**
                   * 根据文件后缀名类型获取对应的工作簿对象
         * @param inputStream 读取文件的输入流
         * @param fileType 文件后缀名类型(xls或xlsx)
         * @return 包含文件数据的工作簿对象
         * @throws IOException
         */
        public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
            Workbook workbook = null;
            if (fileType.equalsIgnoreCase(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileType.equalsIgnoreCase(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            }
            return workbook;
        }
    
        /**
                   * 读取Excel文件内容
         * @param fileName 要读取的Excel文件所在路径
         * @return 读取结果列表,读取失败时返回null
         */
        public static List<WorkersPO> readExcel(String fileName) {
    
            Workbook workbook = null;
            FileInputStream inputStream = null;
    
            try {
                // 获取Excel后缀名
                String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
                // 获取Excel文件
                File excelFile = new File(fileName);
                if (!excelFile.exists()) {
                    logger.warning("指定的Excel文件不存在!");
                }
    
                // 获取Excel工作簿
                inputStream = new FileInputStream(excelFile);
                workbook = getWorkbook(inputStream, fileType);
    
                // 读取excel中的数据
                List<WorkersPO> resultDataList = parseExcel(workbook);
    
                return resultDataList;
            } catch (Exception e) {
                logger.warning("解析Excel失败,文件名:" + fileName + " 错误信息:" + e.getMessage());
                return null;
            } finally {
                try {
                    if (null != workbook) {
                        workbook.close();
                    }
                    if (null != inputStream) {
                        inputStream.close();
                    }
                } catch (Exception e) {
                    logger.warning("关闭数据流出错!错误信息:" + e.getMessage());
                    return null;
                }
            }
        }
    
        /**
                   * 读取Excel文件内容
         * @param file 上传的Excel文件
         * @return 读取结果列表,读取失败时返回null
         */
        public static List<WorkersPO> readExcel(MultipartFile file) {
    
            Workbook workbook = null;
    
            try {
                // 获取Excel后缀名
                String fileName = file.getOriginalFilename();
                if (fileName == null || fileName.isEmpty() || fileName.lastIndexOf(".") < 0) {
                    logger.warning("解析Excel失败,因为获取到的Excel文件名非法!");
                    return null;
                }
                String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
    
                // 获取Excel工作簿
                workbook = getWorkbook(file.getInputStream(), fileType);
    
                // 读取excel中的数据
                List<WorkersPO> resultDataList = parseExcel(workbook);
    
                return resultDataList;
            } catch (Exception e) {
                logger.warning("解析Excel失败,文件名:" + file.getOriginalFilename() + " 错误信息:" + e.getMessage());
                return null;
            } finally {
                try {
                    if (null != workbook) {
                        workbook.close();
                    }
                } catch (Exception e) {
                    logger.warning("关闭数据流出错!错误信息:" + e.getMessage());
                    return null;
                }
            }
        }
    
    
        /**
                   * 解析Excel数据
         * @param workbook Excel工作簿对象
         * @return 解析结果
         */
        private static List<WorkersPO> parseExcel(Workbook workbook) {
           List<WorkersPO> resultDataList = new ArrayList<>();
            // 解析sheet
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                Sheet sheet = workbook.getSheetAt(sheetNum);
    
                // 校验sheet是否合法
                if (sheet == null) {
                    continue;
                }
    
                // 获取第一行数据
                int firstRowNum = sheet.getFirstRowNum();
                Row firstRow = sheet.getRow(firstRowNum);
                //if (null == firstRow) {
                    //logger.warning("解析Excel失败,在第一行没有读取到任何数据!");
                //}
    
                // 解析每一行的数据,构造数据对象
                int rowStart = firstRowNum + 2; //获取第几行
                int rowEnd = sheet.getPhysicalNumberOfRows();
                for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                    Row row = sheet.getRow(rowNum);
    
                    if (null == row) {
                        continue;
                    }
    
                    WorkersPO resultData = convertRowToData(row);
                    if (null == resultData) {
                        logger.warning("第 " + row.getRowNum() + "行数据不合法,已忽略!");
                        continue;
                    }
                    resultDataList.add(resultData);
                }
            }
    
            return resultDataList;
        }
    
        /**
         * 将单元格内容转换为字符串
         * @param cell
         * @return
         */
        private static String convertCellValueToString(Cell cell) {
            if(cell==null){
                return null;
            }
            String returnValue = null;
            switch (cell.getCellType()) {
                case NUMERIC:   //数字
                    Double doubleValue = cell.getNumericCellValue();
    
                    // 格式化科学计数法,取一位整数
                    DecimalFormat df = new DecimalFormat("0");
                    returnValue = df.format(doubleValue);
                    break;
                case STRING:    //字符串
                    returnValue = cell.getStringCellValue();
                    break;
                case BOOLEAN:   //布尔
                    Boolean booleanValue = cell.getBooleanCellValue();
                    returnValue = booleanValue.toString();
                    break;
                case BLANK:     // 空值
                    break;
                case FORMULA:   // 公式
                    returnValue = cell.getCellFormula();
                    break;
                case ERROR:     // 故障
                    break;
                default:
                    break;
            }
            return returnValue;
        }
    
        /**
         * 提取每一行中需要的数据,构造成为一个结果数据对象
         *
         * 当该行中有单元格的数据为空或不合法时,忽略该行的数据
         *
         * @param row 行数据
         * @return 解析后的行数据对象,行数据错误时返回null
         */
        private static WorkersPO convertRowToData(Row row) {
        	WorkersPO resultData = new WorkersPO();
    
            Cell cell;
            int cellNum = 0;
            
            //获取工号
            cell = row.getCell(cellNum++);
            String cardNum = convertCellValueToString(cell);
            if (null == cardNum || "".equals(cardNum)) {
                // 年龄为空
                resultData.setCardNum(cardNum);
            } else {
                resultData.setCardNum(cardNum);
            }
            
            // 获取姓名
            cell = row.getCell(cellNum++);
            String workerName = convertCellValueToString(cell);
            if (null == workerName || "".equals(workerName)) {
                // 年龄为空
                resultData.setWorkerName(workerName);
            } else {
                resultData.setWorkerName(workerName);
            }
            
            // 获取年龄
            cell = row.getCell(cellNum++);
            String age = convertCellValueToString(cell);
            if (null == age || "".equals(age)) {
                // 年龄为空
                resultData.setAge(null);
            } else {
                resultData.setAge(age);
            }
            
            // 获取性别
            cell = row.getCell(cellNum++);
            String sex = convertCellValueToString(cell);
            resultData.setSex(sex);
            
            // 获取居住地
            cell = row.getCell(cellNum++);
            String address = convertCellValueToString(cell);
            resultData.setAddress(address);
            
            // 获取部门
            cell = row.getCell(cellNum++);
            String position = convertCellValueToString(cell);
            resultData.setPosition(position);
            
            // 获取入职时间
            cell = row.getCell(cellNum++);
            String workDate = convertCellValueToString(cell);
            resultData.setWorkDate(workDate);
    
            return resultData;
        }
    
    }
    

    实体类(推荐试用lombok插件,本文没用)

    import java.sql.Date;
    
    /**
     * 职工信息表实体
     * @author Administrator
     *
     */
    public class WorkersPO {
    	private String cardNum;
    	private String workerName;
    	private String age;
    	private String sex;
    	private String address;
    	private String position;
    	private String workDate;
    	public String getCardNum() {
    		return cardNum;
    	}
    	public void setCardNum(String cardNum) {
    		this.cardNum = cardNum;
    	}
    	public String getWorkerName() {
    		return workerName;
    	}
    	public void setWorkerName(String workerName) {
    		this.workerName = workerName;
    	}
    	public String getAge() {
    		return age;
    	}
    	public void setAge(String age) {
    		this.age = age;
    	}
    	public String getSex() {
    		return sex;
    	}
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    	public String getAddress() {
    		return address;
    	}
    	public void setAddress(String address) {
    		this.address = address;
    	}
    	public String getPosition() {
    		return position;
    	}
    	public void setPosition(String position) {
    		this.position = position;
    	}
    	public String getWorkDate() {
    		return workDate;
    	}
    	public void setWorkDate(String workDate) {
    		this.workDate = workDate;
    	}
    	public WorkersPO(String cardNum, String workerName, String age, String sex, String address, String position,
    			String workDate) {
    		super();
    		this.cardNum = cardNum;
    		this.workerName = workerName;
    		this.age = age;
    		this.sex = sex;
    		this.address = address;
    		this.position = position;
    		this.workDate = workDate;
    	}
    	public WorkersPO() {
    		super();
    	}
    	@Override
    	public String toString() {
    		return "WorkersPO [cardNum=" + cardNum + ", workerName=" + workerName + ", age=" + age + ", sex=" + sex
    				+ ", address=" + address + ", position=" + position + ", workDate=" + workDate + "]";
    	}
    }
    

    Mapper类

    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Select;
    
    @Mapper
    public interface WorkersMapper {
    	/**
    	 * 插入数据(读取excel入库)
    	 * @param workersPO
    	 * @return
    	 */
    	int insertWorkers(Map<String,Object> map);
    	
    	@Select("SELECT card_num FROM data.workers_data_df")
    	List<String> getCardNum();
    }
    

    MyBatis

    	<insert id="insertWorkers">
            INSERT INTO data.workers_data_df
            <trim prefix="(" suffix=")" suffixOverrides="," >
                <if test='cardNum != null and cardNum !=""'>
                    card_num,
                </if>
                <if test='workerName != null and workerName !=""'>
                    worker_name,
                </if>
                <if test='age != null and age !=""'>
                    age,
                </if>
                <if test='sex != null and sex !=""'>
                    sex,
                </if>
                <if test='address != null and address !=""'>
                    address,
                </if>
                <if test='position != null and position !=""'>
                    position,
                </if>
                <if test='workDate != null'>
                    work_date
                </if>
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                 <if test='cardNum != null and cardNum !=""'>
                 	#{cardNum,jdbcType=INTEGER},
                </if>
                <if test='workerName != null and workerName !=""'>
                	#{workerName,jdbcType=VARCHAR},
                </if>
                <if test='age != null and age !=""'>
                	#{age,jdbcType=INTEGER},
                </if>
                <if test='sex != null and sex !=""'>
                	#{sex,jdbcType=VARCHAR},
                </if>
                <if test='address != null and address !=""'>
                	#{address,jdbcType=VARCHAR},
                </if>
                <if test='position != null and position !=""'>
                	#{position,jdbcType=VARCHAR},
                </if>	
                <if test='workDate != null'>
                	#{workDate,jdbcType=DATE}
                </if>
            </trim>
        </insert>
    

    Service类

    import java.util.List;
    import java.util.Map;
    
    
    public interface WorkersService {
    	/**
    	 * 插入数据(读取excel入库)
    	 * @param workersPO
    	 * @return
    	 */
    	int insertWorkers(Map<String,Object> map);
    	
    	List<String> getCardNum();
    }
    

    Service实现类

    import java.util.List;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import com.project.main.excel.upload.mapper.WorkersMapper;
    import com.project.main.excel.upload.service.WorkersService;
    
    @Service
    public class WorkersServiceImpl implements WorkersService{
    	
    	@Autowired
    	private WorkersMapper workersMapper;
    
    	@Override
    	public int insertWorkers(Map<String,Object> map) {
    		return workersMapper.insertWorkers(map);
    	}
    
    	@Override
    	public List<String> getCardNum() {
    		return workersMapper.getCardNum();
    	}
    }
    

    Controller类

    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.CrossOrigin;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.project.main.excel.upload.entity.WorkersPO;
    import com.project.main.excel.upload.service.WorkersService;
    import com.project.main.excel.upload.utils.ExcelReader;
    import com.project.main.utils.R;
    
    @RestController
    @CrossOrigin
    public class WorkersController {
    	
    	@Autowired
    	private WorkersService workersService;
    	
    	@GetMapping("/workersExcelUpload")
    	public R workersExcelUpload(@RequestParam String excelFileName) {
    		try {
    			Map<String,Object> map = new HashMap<String, Object>();
    			List<String> cardNumList = workersService.getCardNum(); //查询库里已有卡号
    	        List<WorkersPO> readResult = ExcelReader.readExcel(excelFileName); //excel读取到的数据
    	        System.out.println(readResult);
    	        for (int i=0;i<readResult.size();i++){
    	        	String cardNum = readResult.get(i).getCardNum();
    	        	if(cardNumList.contains(cardNum)) { //只添加库中卡号没有数据进行判定
    	        		continue;
    	        	}else {
    	        		map.put("cardNum", cardNum);
    	        		map.put("workerName", readResult.get(i).getWorkerName());
    	        		map.put("age", readResult.get(i).getAge());
    	        		map.put("sex", readResult.get(i).getSex());
    	        		map.put("address", readResult.get(i).getAddress());
    	        		map.put("position", readResult.get(i).getPosition());
    	        		map.put("workDate", readResult.get(i).getWorkDate());
    	        		workersService.insertWorkers(map); //添加入库
    	        	}
    	        }
    			return R.ok();
    		} catch (Exception e) {
    			e.printStackTrace();
    			return R.error("数据导入失败,请规范导入模板");
    		}
    	}
    }
    

    返回示例工具类R.java见本博主其他文章有
    到这里流程已经整体走完
    更多好玩的代码及工具,可微信公众号搜索关注“小逸分享君”领取哦

    展开全文
  • poi 解 析在java中,完全读取excel文件数据,保存到数据库中用poi 专门操作excel文件的如何将Excel表格中的数据导入到sql数据库里已经存在的表里,注意是已经建好的?insert into tablenameSELECT *FROM ...

    求一个java程序,excel表格读取表格内容存入到数据库,再从数据库读取数据存放到excel文件中。

    poi 解 析

    在java中,完全读取excel文件数据,保存到数据库中

    用poi 专门操作excel文件的

    如何将Excel表格中的数据导入到sql数据库里已经存在的表里,注意是已经建好的?

    insert into tablename

    SELECT *

    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

    'Data Source="c:\temp1.xls";User ID=;Password=;Extended properties=Excel 5.0')...[Sheet1$]

    如果两边类型不一致需要cast或convert转换数型

    C#读取EXCEL中的信息,并保存到数据库

    其实就跟C#连接SQL读取表中的数据是一样的,只是在这数据库为Excel而已过连接字符串:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=文件路径;Extended Properties=Excel 8.0

    连接Excel即可,然后通过SQL语句:(类似)select * from [Sheet1$] 把数据取出来即可,然后按照以前自己连接SQL数据库的方式把相应的字段存储起来即可。

    需要留意的时03和07的连接字符串不一样,以上是03版的Excel连接方式

    Excel 2007:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=文件路径;Extended Properties=Excel 12.0

    如有什么不明白的 可以留言 ^_^

    如何用java导入Excel数据到数据库?

    个例子:(jxl)

    基本思路样的

    1,先把Excel数据读取出来

    InputStream is = new FileInputStream(path);//path是文件路径

    Workbook wb = Workbook.getWorkbook(is);// 获得工作

    Sheet st = wb.getSheet(0);

    //取值的时候getCell(4, 1)前面一个参数是列号,后一个是行号

    String stuId = st.getCell(4, 1).getContents().trim();

    String stuName = st.getCell(5, 1).getContents().trim();

    String stuAge = st.getCell(6, 1).getContents().trim();

    2,写入数据到数据库

    StringBuffer sb = new StringBuffer();

    sb.append(" insert into student (id,name,age) VALUES (");

    sb.append(stuId "," stuName " ," stuAge );

    JdbcTemplate jdbcTemplate = (JdbcTemplate) omponentFactory.getBean ("jdbcTemplate");

    jdbcTemplate.execute(sb.toString()); //执行sql

    展开全文
  • java 读取excel存入数据库

    千次阅读 2018-03-20 11:34:11
    import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import com.jfinal.plugin.activerecord.Db; import org.apache...
  • java读取excle的数据存入数据库 1.附上前端代码jsp页面 $(function() { $('#statisticsElecAddForm1').form({ url : getContextPath() + "/actysity/ReadExce.do", onSubmit : function() { ...
  • java导入Excel表格数据 本文可参考:https://www.hutool.cn/docs/#/poi/Excel读取-ExcelReader 一,引入依赖 <!-- 导入Excel,版本要兼容,不然报错的 --> <dependency> <groupId>org.apache....
  • package ...import java.io.File; import jxl.*; import jxl.write.*; import jxl.write.biff.RowsExceededException; import java.sql.*; import java.util.*; public class DBtoExc...
  • 本篇文章主要介绍了Java解析Excel文件并把数据存入数据库 ,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
  • 用的语言是servlet+jsp+mysql和后面两个包commons-fileupload-1.3.1.jar jxl.jar 在线等,有没有会这个代码的大神
  • javaexcel数据读取存入mysql数据库
  • java导入excel数据内容存入数据库

    千次阅读 2017-08-29 10:26:35
    示例:导入案件,excel controller方法: /** * * @param file * @param batch * @param request * @param model * @param attr * @param response * @return * @throws InterruptedException ...
  • 数据库中抽取出来的字段,写入到excel文件里:java使用poi把从数据库中取出的数据写入excel最近实现了一个相反的功能,前台传一个excel文件,在后台解析该excel文件的数据,转换为javaBean后存入数据库中,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,760
精华内容 704
关键字:

java读取excel的数据存入数据库

java 订阅