EXCEL2007导入SQL生成新表并插入数据

因工作需求，实现导入EXCEL2007生成新表并插入数据

packagecom.utils; 

importjava.io.File;

importjava.io.FileInputStream;

importjava.io.IOException;

importjava.io.ObjectInputStream.GetField;

importjava.sql.Connection;

importjava.sql.SQLException;

importjava.util.ArrayList;

importjava.util.Date;

importjava.util.Vector;

importorg.apache.poi.hssf.usermodel.HSSFDataFormat;

importorg.apache.poi.hssf.usermodel.HSSFDateUtil;

importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;

importorg.apache.poi.xssf.usermodel.XSSFCell;

importorg.apache.poi.xssf.usermodel.XSSFDataFormat;

importorg.apache.poi.xssf.usermodel.XSSFRow;

importorg.apache.poi.xssf.usermodel.XSSFSheet;

importorg.apache.poi.xssf.usermodel.XSSFWorkbook;

importorg.junit.Test;

importcom.dao.WageDao;

publicclass XlsUtils {

    Vector<String> titleList;

    privateWageDao wagedao = newWageDao();

    publicString importXls(String tbName, File file) throwsException  {

        XSSFWorkbook book = newXSSFWorkbook(file);// 获得文件

        XSSFSheet sheet = book.getSheetAt(0);// 获得第一个工作表

        XSSFRow title = sheet.getRow(0);// 获得标题行

        inttitles = title.getLastCellNum();// 获得字段总数

        introws = sheet.getLastRowNum();// 获得总行数

        String message = "更新成功";

        Connection conn = ConnUtils.getConnection();//获得数据库连接，开启事务控制插入出错。

        titleList = newVector<String>();//接收第一行字段名

        for(inti = 0; i < titles; i++) {

            XSSFCell cel = title.getCell(i);

            String result = getStringCellValue(cel);

            titleList.add(result);

        }

        try{

            wagedao.createTable(titleList, tbName);//将字段名交给数据库处理类生成表。

            conn.setAutoCommit(false);//开启事务

            for(inti = 1; i <= rows; i++) {// 遍历将表数据装进数组

                ArrayList<String> v = newArrayList<String>();

                XSSFRow row = sheet.getRow(i);

                intcels = row.getLastCellNum();

                for(intj = 0; j < cels; j++) {

                    String result = "";

                    XSSFCell cel = row.getCell(j);

                    result = getStringCellValue(cel);

                    v.add(result);

                }

                wagedao.insert(conn,titleList, v, tbName);// 将数级插入数据库。

            }

             

            conn.setAutoCommit(true);//关闭事务，插入的数据会回滚，但是新表会建成只是没有数据。

        }catch(Exception e) {

             

             message = e.getMessage()+"更新失败";

            e.printStackTrace();

        }finally{

            conn.close();

        }

        book.close();

        returnmessage;

    }

    publicVector<String> getTitles() {

        returntitleList;

    }

    privatestatic String getStringCellValue(XSSFCell cell) {// 将XLSX内容转为STRING，空的将默认为0

        String strCell = "";

        inttype = 0;

        try{

            switch(cell.getCellType()) {

            caseXSSFCell.CELL_TYPE_BLANK:

                strCell = "0";

                break;

            caseXSSFCell.CELL_TYPE_STRING:

                strCell = cell.getStringCellValue();

                break;

            caseXSSFCell.CELL_TYPE_NUMERIC:

                strCell = String.valueOf(cell.getNumericCellValue());

                break;

            caseXSSFCell.CELL_TYPE_BOOLEAN:

                strCell = String.valueOf(cell.getBooleanCellValue());

                break;

            default:

                strCell = "0";

                break;

            }

        }catch(Exception e) {

            if(e.getMessage() == null) {

                strCell = "0";

            }

        }

 

        if(strCell.equals("") || strCell == null) {

            return"0";

        }

        if(cell == null) {

            return"0";

        }

        returnstrCell;

    }

}

操作数据库类

packagecom.dao; 

importjava.sql.Connection;

importjava.sql.PreparedStatement;

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.sql.Statement;

importjava.util.ArrayList;

importjava.util.Arrays;

importjava.util.List;

importjava.util.UUID;

importjava.util.Vector; 

importjavax.sql.DataSource; 

importorg.apache.commons.dbutils.QueryRunner;

importorg.apache.commons.dbutils.ResultSetHandler;

importorg.apache.commons.dbutils.handlers.ArrayHandler;

importorg.apache.commons.dbutils.handlers.ArrayListHandler;

importorg.junit.Test;

importcom.utils.ConnUtils;

importcom.utils.XlsUtils;

publicclass WageDao {// 传过来的数组进行处理。

 

    publicint insert(Connection conn, Vector<String> titleList,//传过来的数进行插入处理。

            ArrayList<String> v, String tbName) throwsSQLException  {

        introw=0;

        String id = UUID.randomUUID().toString().replace("-","");//生成随机数

        String sql = "insert into " + tbName + "(id ";//拼接插入字段

        inttitles = 0;

        for(String s : titleList) {

            String t = ","+ s;

            sql += t;

            titles++;

        }

        sql += ")";

        sql = sql + " values ('" + id + "' ";//拼接插入数据

        for(inti = 0; i < titles; i++) {

            String s = ", ? ";

            sql += s;

        }

        sql += ")";

        PreparedStatement ps = conn.prepareStatement(sql);

        for(inti = 0; i < titles; i++) {

            ps.setString(i + 1, v.get(i));

        }

        ps.executeUpdate();

        ps.close();

        returnrow;

    }

 

    publicvoid createTable(Vector<String> titleList, String tbName)

            throwsException {

        QueryRunner run = newQueryRunner(ConnUtils.getDataSource(), true);//SQL数据库要加

        String dele = " drop table " + tbName;//删除同名的表，覆盖数据使用

        String sql = "create table " + tbName + "( id varchar(100)";//准备拼接使用

        String creadPk = "ALTER TABLE " + tbName + "  ADD UNIQUE (工号)";//生成工号约束，不能重复。

        for(String s : titleList) {//创建语句拼接

            String s1 = ", " + s + " varchar(50) default 0 ";

            sql += s1;

        }

        sql += ")";

        if(isExist(tbName)) {

            run.update(dele);

        }

        run.update(sql);

        run.update(creadPk);

    }

 

    // 判断表是否存在

    publicstatic boolean isExist(String tbName) throwsSQLException {

        QueryRunner run = newQueryRunner(ConnUtils.getDataSource(), true);

        String sql = "select * from dbo.sysobjects where id = object_id(N'["

                + tbName + "]')";

        Object[] b = run.query(sql, newArrayHandler());

        if(b.length > 0) {

            returntrue;

        }else{

            returnfalse;

        }

 

    }

 

}