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

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

package com.utils; 

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.ObjectInputStream.GetField;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Date;

import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFDataFormat;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.junit.Test;

import com.dao.WageDao;

public class XlsUtils {

    Vector<String> titleList;

    private WageDao wagedao = new WageDao();

    public String importXls(String tbName, File file) throws Exception  {

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

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

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

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

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

        String message = "更新成功";

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

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

        for (int i = 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 (int i = 1; i <= rows; i++) {// 遍历将表数据装进数组

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

                XSSFRow row = sheet.getRow(i);

                int cels = row.getLastCellNum();

                for (int j = 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();

        return message;

    }

    public Vector<String> getTitles() {

        return titleList;

    }

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

        String strCell = "";

        int type = 0;

        try {

            switch (cell.getCellType()) {

            case XSSFCell.CELL_TYPE_BLANK:

                strCell = "0";

                break;

            case XSSFCell.CELL_TYPE_STRING:

                strCell = cell.getStringCellValue();

                break;

            case XSSFCell.CELL_TYPE_NUMERIC:

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

                break;

            case XSSFCell.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";

        }

        return strCell;

    }

}

操作数据库类

package com.dao; 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.List;

import java.util.UUID;

import java.util.Vector; 

import javax.sql.DataSource; 

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.ResultSetHandler;

import org.apache.commons.dbutils.handlers.ArrayHandler;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

import org.junit.Test;

import com.utils.ConnUtils;

import com.utils.XlsUtils;

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

 

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

            ArrayList<String> v, String tbName) throws SQLException  {

        int row=0;

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

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

        int titles = 0;

        for (String s : titleList) {

            String t = "," + s;

            sql += t;

            titles++;

        }

        sql += ")";

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

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

            String s = ", ? ";

            sql += s;

        }

        sql += ")";

        PreparedStatement ps = conn.prepareStatement(sql);

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

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

        }

        ps.executeUpdate();

        ps.close();

        return row;

    }

 

    public void createTable(Vector<String> titleList, String tbName)

            throws Exception {

        QueryRunner run = new QueryRunner(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);

    }

 

    // 判断表是否存在

    public static boolean isExist(String tbName) throws SQLException {

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

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

                + tbName + "]')";

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

        if (b.length > 0) {

            return true;

        } else {

            return false;

        }

 

    }

 

}