【使用java ssm Excel解析创建数据库批量导入数据库】

最近属实忙的很，也很久没有写blog，既然有点时间就整理一下吧。

额。。。。没时间直接贴代码吧！

使用java ssm Excel解析 创建数据库批量导入

实体类

根据自己的实体字段编写；
后期做改进，自动解析excel第二行自动映射为数据库的字段

service层
ExcelBaseDataService

  public List initExceSubject(File file, String rootPath) throws IOException, SQLException, ClassNotFoundException {
        boolean notNull = false;
        String fileName = String.valueOf(file);
        List<PeijianExcelBean> userList = new ArrayList<>();
        List<FixFreeBean> userList2 = new ArrayList<>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new MyException("上传文件格式不正确");
        }

        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        FileInputStream is = new FileInputStream(file);

        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if (sheet != null) {
            notNull = true;
        }
        SqliteHelper sqliteHelper = new SqliteHelper(rootPath + GlobalExam.EXCEL_DB_PATH + "/" + excelDataBaseName);



        for (int k= 0; k < 1; k++) {
            Row roww = sheet.getRow(k);
            String rowTable=roww.getCell(0).getStringCellValue();
            //rowTableName=rowTable;
            if (rowTable.equals("配件价格表")) {

                for (int r = 2; r <= sheet.getLastRowNum(); r++) {
                    Row row = sheet.getRow(r);
                    if (row == null) {
                        continue;
                    }
                    PeijianExcelBean PeijianExcelBean = new PeijianExcelBean();
                    String categoryMax = row.getCell(0).getStringCellValue();
                    String categoryMin = row.getCell(1).getStringCellValue();
                    CellType priceCell= row.getCell(2).getCellTypeEnum();
                    String priceStr = "";

                    if(priceCell==CellType.STRING){
                        priceStr=row.getCell(2).getStringCellValue();

                        PeijianExcelBean.setPrice(priceStr);
                    } else if (priceCell==CellType.NUMERIC){
                        String  price2 = String.valueOf(row.getCell(2).getNumericCellValue());
                        PeijianExcelBean.setPrice(price2);
                    }


                    if (categoryMax.equals("")&&categoryMin.equals("")) {
                        continue;

                    }
                    PeijianExcelBean.setCategoryMax(categoryMax);
                    PeijianExcelBean.setCategoryMin(categoryMin);

                    userList.add(PeijianExcelBean);

                    sqliteHelper.initExcel(userList);
                }
        }else if (rowTable.equals("维修费用表")){

                for (int j = 2; j <= sheet.getLastRowNum(); j++) {
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        continue;
                    }
                    FixFreeBean fixFreeBean=new FixFreeBean();
                    String categoryMax = row.getCell(0).getStringCellValue();
                    String categoryMin = row.getCell(1).getStringCellValue();
                    CellType priceCell= row.getCell(2).getCellTypeEnum();
                    String priceStr = "";

                    if(priceCell==CellType.STRING){
                        priceStr=row.getCell(2).getStringCellValue();

                        fixFreeBean.setPrice(priceStr);
                    } else if (priceCell==CellType.NUMERIC){
                        String  price2 = String.valueOf(row.getCell(2).getNumericCellValue());
                        fixFreeBean.setPrice(price2);
                    }


                    if (categoryMax.equals("")&&categoryMin.equals("")) {
                        continue;

                    }
                    fixFreeBean.setCategoryMax(categoryMax);
                    fixFreeBean.setCategoryMin(categoryMin);

                    userList2.add(fixFreeBean);
                    //logger.info("加载到list等待插入数据库");
                    sqliteHelper.initFixExcel(userList2);
                }

            }else {
                logger.info("请规范excel格式");
            }

            logger.info("解析完成");
        }


        return userList;
    }

controller 调用一下就好了

until 数据库工具实现创建插入数据库

 /*配件*/
    @Transactional
    public void initExcel(List<PeijianExcelBean> subjectList) throws SQLException, ClassNotFoundException {
        try {
            beginTransaction();
            //创建数据库表
            LOGGER.info("开始创建表");
            String createTableSql = "DROP TABLE IF EXISTS \"表\";\n" +
                    "CREATE TABLE \"表\" (\n" +
                    "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
                    "  \"category_max\" varchar(20),\n" +
                    "  \"category_min\" varchar(20),\n" +
                    "  \"price\" varchar(10))";

            getStatement().executeUpdate(createTableSql);
            LOGGER.info("成功创建表");
            LOGGER.info("执行导入数据");
            String sql = "INSERT INTO 表(category_max, category_min, price) " +
                    "VALUES ( ?, ?, ?);";
            PreparedStatement preparedStatement = getConnection().prepareStatement(sql);
            for (PeijianExcelBean subject : subjectList) {

                preparedStatement.setString(1, subject.getCategoryMax());
                preparedStatement.setString(2, subject.getCategoryMin());
                preparedStatement.setString(3, subject.getPrice());

                preparedStatement.addBatch();
            }
            int[] updateCounts = preparedStatement.executeBatch();
            LOGGER.info("结束执行导excel数据");
            commitTransaction();
        } finally {
            destroyed();
        }
    }

这个代码是自己记录回忆使用，板书格式很随意。