qq_37525300
qq_37525300
采纳率0%
2017-05-18 03:16 阅读 1.6k

一个工具类,就是使用java读取excel写入mysql数据库

1

public static boolean importExcel(File file,HashMap colMapping,String tableName) {
return true;

}
HashMap colMapping 这里面一个是数据库的字段,一个是excel对应的字段

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

7条回答 默认 最新

  • caozhy 从今以后生命中的每一秒都属于我爱的人 2017-05-18 03:21

    java读取excel可以用poi这个类,但是excel表千差万别,所以要结合你的 excel表格式来看

    点赞 评论 复制链接分享
  • qq_37525300 qq_37525300 2017-05-18 03:28

    colMapping里面有两个String 一个事数据库字段,一个事excel字段 有对应的 现在就是写一个工具类,求具体实现代码

    点赞 评论 复制链接分享
  • qq_37525300 qq_37525300 2017-05-18 03:29

    图片说明

    大概就是这样的表,colMapping对应编号,性别,姓名的,然后hashmap里面可以动态选择

    点赞 评论 复制链接分享
  • qq_37525300 qq_37525300 2017-05-18 03:31

    图片说明
    这个是数据库,colMapping对应bianhao,name,sex

    点赞 评论 复制链接分享
  • qq_37525300 qq_37525300 2017-05-18 03:58

    package util;

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.HashMap;

    import jxl.Cell;
    import jxl.Sheet;
    import jxl.Workbook;

    public class PayslipExcelImport {

    static String createTableSql = "user";// 创建数据库的sql
    static String colType = "TEXT";// 字段类型
    static String key = "id";// 主键
    static String charSet = "utf8";// 表格字符类型
    static String ENGINE = "InnoDB";// 表格类型
    static String tableName = "tempExcelToMysql";// 表名称
    static String colName = "";// 默认字段名
    static File file;
    static HashMap<String, String> cloMapping;
    static Connection conn = null;
    
    public static void main(String args[]) {
        try {
            // 构建Workbook对象, 只读Workbook对象
            // 直接从本地文件创建Workbook
            // 从输入流创建Workbook
            System.out.println("start load file-------------------------");
            InputStream is = new FileInputStream("C:/Users/chen/Desktop/object/xxx.xls");// 创建输入
            jxl.Workbook rwb = Workbook.getWorkbook(is);
            Sheet rs = rwb.getSheet(0); // 读取第一个sheet
            int colNum = rs.getColumns();// 列数
            int rowNum = rs.getRows();// 行数
            System.out.println("colNum rowNum------------------" + rowNum + "," + colNum);
            System.out.println("start create base-------------------------");
            getConntion();
            String tableSql = getCreateTableSql(rowNum, colNum);
            Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            st.execute(tableSql);
            st.close();
            System.out.println("create base end -------------------------");
            String sql = getColName(rowNum, colNum);
            PreparedStatement ps = null;
            String strValue = "1";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i < rowNum; i++) {
                strValue = "1";
                for (int j = 0; j < colNum; j++) {
                    Cell c = rs.getCell(j, i);
                    strValue = c.getContents();
                    ps.setString(j + 1, strValue);
                }
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();
            if (ps != null) {
                ps.close();
            }
            System.out.println(" insert end-------------------------");
            close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    static String getCreateTableSql(int rowNum, int colNum) {
        // 可以做成可配置文件
        createTableSql = "create table " + tableName + "( `" + key + "` bigint(12) NOT NULL auto_increment, ";
        String temp = "bianhao";
        for (int j = 0; j < colNum; j++) {
            temp = temp + "`" + colName + j + "` " + colType + " DEFAULT NULL,";
        }
        createTableSql = createTableSql + " " + temp + " PRIMARY KEY (`" + key + "`)" + ") ENGINE=" + ENGINE
                + " DEFAULT CHARSET=" + charSet + ";";
        return createTableSql;
    }
    
    static String getColName(int rowNum, int colNum) {
        // 可以做成可配置文件
        String colSql = "1";
        String colValue = "1";
        for (int j = 0; j < colNum; j++) {
            colSql = colSql + "`" + colName + j + "`,";
            colValue = colValue + "" + "?,";
        }
        return "insert into " + tableName + " (" + colSql.substring(0, colSql.lastIndexOf(",")) + ")values("
                + colValue.substring(0, colValue.lastIndexOf(",")) + ")";
    }
    
    static void getConntion() {
        try {
            String driver_class = "com.mysql.jdbc.Driver";
            String connection_url = "jdbc:mysql://localhost:3306/map?useUnicode=true&characterEncoding=utf-8";
            String user_name = "root";
            String db_password = "695545459";
            Class.forName(driver_class);
            conn = DriverManager.getConnection(connection_url, user_name, db_password);
            conn.setAutoCommit(false);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    static void close() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    

    }

    代码总感觉差点什么 ,求大神指导

    点赞 评论 复制链接分享
  • xuanzhangran 转角人生 2017-05-18 05:35

    前几天刚做的导入excel:你可以参考一下:http://blog.csdn.net/xuanzhangran/article/details/71603478

    点赞 评论 复制链接分享
  • xuanzhangran 转角人生 2017-05-18 05:44
    点赞 评论 复制链接分享

相关推荐