Java读取Excel文件并写入数据库

思路: 
   1.加载配置文件,进行对 数据库的相关初始化及其他相关参数

   2.尝试打开指定的xls文件开始对数据库表写入

相关资源

  ,比如配置文件,创建数据库表的sql 语句及readme.txt见 本人的上传资源具体地址为:

点我下载对应的代码及创建数据库表的sql语句 和 配置文件

具体代码展示:

 1.Main <主流程控制类>

/* * Copyright 2007 - 2014 RollStone * * All Rights Reserved. */package csi;import csi.file.FileBase;import csi.file.FileDelay;import csi.file.FileFail;import csi.file.FileFirewall;import java.sql.Connection;import java.sql.SQLException;import java.text.SimpleDateFormat;import java.util.Calendar;/** * @brief * @author RollStone * @created Jul 9, 2014 5:05:19 PM */public class Main {    public static void main(String[] args) {        //确定日期,若是无参数则取值为昨天的yyyymmdd        String dateStr;        if (args.length == 1) {            dateStr = args[0];        } else {            dateStr = getYesterday();        }        Config config = new Config();        config.load();        FileBase[] fbs = new FileBase[]{new FileFirewall(), new FileFail(), new FileDelay()};        try {            Connection conn = new DBConnector().getConnection();            conn.setAutoCommit(false); //以防止分割的时候只进行了部分插入            for (FileBase base : fbs) {                base.setMsgMaxLength(config.getMsgMaxLength());                base.setExcelFilename(config.getProvinceCode(), dateStr);                String excelfilename = base.getExcelFilename();                int existRecordNums = base.checkData(conn, excelfilename);                String efWithPath = config.getExcelPath() + excelfilename;                base.writeExcelContentToTable(conn, efWithPath, existRecordNums + 1);            }                        conn.close();        } catch (SQLException e) {        }    }    public static String getYesterday() {        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");        Calendar c = Calendar.getInstance();        c.add(Calendar.DATE, -1);        return sdf.format(c.getTime());    }}

 2.Config <通过load函数加载配置文件中的除Database信息相关的其他配置参数的内容>

/* * Copyright 2000 - 2014 RollStone  * All Rights Reservered. */package csi;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.Properties;/** * @brief * * @author RollStone * @created 2014-07-24 03:34:39 */public class Config {    private static final String DEFAULT_CONFIG = "pax.properties";    private String configFile;    protected Properties properties = new Properties();    public Config(String cfg) {        configFile = cfg;        initOthers();    }    public Config() {        configFile = DEFAULT_CONFIG;        initOthers();    }    ///////////////////// Database part /////////////////////////    //This section be extends by class DBConnector    ///////////////////// Config others /////////////////////////    private String msgMaxLenth;    private String excelPath;    private String provinceCode;    private boolean isLinux;    private boolean isTest;    private void initOthers() {        msgMaxLenth = null;        excelPath = null;        provinceCode = null;        isLinux = true;        isTest = false;    }    public int getMsgMaxLength() {        return Integer.parseInt(msgMaxLenth);    }    public String getProvinceCode() {        return provinceCode;    }    public String getExcelPath() {        String sp = "/";        if (isLinux == false) {            sp = "\\";        }        return excelPath.endsWith(sp) == true ? excelPath : excelPath + sp;    }    public boolean isTest() {        return isTest;    }//////////////////////////加载函数的框架,实际工作在 doLoad////////////////////////    public final void load() {        try {            File cf = new File(configFile);            InputStream in = new FileInputStream(cf);            properties.load(in);            in.close();            doLoad();            properties.clear();            properties = null;        } catch (FileNotFoundException ex) {            ex.printStackTrace();        } catch (IOException ex) {            ex.printStackTrace();        }    }    protected void doLoad() {        msgMaxLenth = properties.getProperty("MsgMaxLength");        excelPath = properties.getProperty("ExcelPath");        provinceCode = properties.getProperty("ProvinceCode");        //setting default values        if ("".equalsIgnoreCase(msgMaxLenth)) {            msgMaxLenth = "2048";        }        if ("".equalsIgnoreCase(excelPath)) {            excelPath = ".";        }        if ("".equalsIgnoreCase(provinceCode)) {            provinceCode = "951";        }        if ("false".equalsIgnoreCase(properties.getProperty("isLinux"))) {            isLinux = false;        }        if ("true".equalsIgnoreCase(properties.getProperty("isTest"))) {            isTest = true;        }    }}

3.DBConnector <覆盖了Config的doLoad方法用于对数据库的信息初始化>

/* * Copyright 2007 - 2014 RollStone  * * All Rights Reserved. */package csi;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;/** * @brief * * @author RollStone * @created Jul 7, 2014 10:50:13 AM */public final class DBConnector extends Config {private static final String ORACLE_DB_DRIVER= "oracle.jdbc.OracleDriver";    private String dbipaddr;    private String dbname;    private String dbport;    private String dbuser;    private String dbpasswd;    private String dburl;    public DBConnector() {        dbdriver = null;        dbipaddr = null;        dbname = null;        dbport = null;        dbuser = null;        dbpasswd = null;        dburl = null;        load();    }    /**     * 读取连接到数据库的参数值     */    @Override    protected void doLoad() {        dbipaddr = properties.getProperty("ipaddr");        dbname = properties.getProperty("dbname");        dbport = properties.getProperty("port");        dbuser = properties.getProperty("dbuser");        dbpasswd = properties.getProperty("password");        //组装连接到数据库的连接url        dburl = "jdbc:oracle:thin:@" + dbipaddr + ":" + dbport + ":" + dbname;    }    public Connection getConnection() {        Connection conn = null;        try {            Class.forName(ORACLE_DB_DRIVER);            conn = DriverManager.getConnection(dburl, dbuser, dbpasswd);        } catch (SQLException e) {        } catch (ClassNotFoundException ex) {        }        return conn;    }}

4.FileBase <涉及到了Excel列数数组及与其对应的数据库表字段的数组,等围绕两者展开的各种方法>

/* * Copyright 2000 - 2014 RollStone  * All Rights Reservered. */package csi.file;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.text.SimpleDateFormat;import java.util.Date;import java.util.HashMap;import java.util.Map;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;/** * @brief * * @author RollStone * @created 2014-07-24 04:20:08 */public class FileBase {    /////////////////////////////// Excel ///////////////////////////////    protected String[] ecs = null; //excel columns    protected void setECS(String stringEC) {        ecs = stringEC.split(",");    }    /////////////////////////////// Table ///////////////////////////////    protected String[] tfs = null; //table fields    protected String keyword = null; //确定文件名的关键字    protected String tablename = null;    protected int MSG_MAX_LEN = 2048;    protected String efn = null;    private String insertsqlheader = null;    public final String getExcelFilename() {        return efn;    }    public final void setExcelFilename(String provinceCode, String ymd) {        efn = provinceCode + "_" + ymd + "_" + keyword + ".xls";    }    public final String getKeyword() {        return keyword;    }    protected final void setKeyword(String kw) {        keyword = kw;    }    protected final void setTFS(String tfline) {        tfs = tfline.split(",");    }    public void setTablename(String tablename) {        this.tablename = tablename;    }    public void setMsgMaxLength(int len) {        MSG_MAX_LEN = len;    }    public void writeExcelContentToTable(Connection conn, String fromFile, int startLine) throws SQLException {        try {            File file = new File(fromFile);            InputStream ins = new FileInputStream(file);            Sheet sheet = Workbook.getWorkbook(ins).getSheet(0);            int numOfRows = sheet.getRows();            Statement stmt = conn.createStatement();            for (int cr = startLine; cr < numOfRows; cr++) {                Cell[] ces = sheet.getRow(cr); //获取当前行                      HashMap map = new HashMap();                for (int i = 0; i < ecs.length; i++) {                    map.put(ecs[i], ces[i].getContents());                }                if (checkBlankLine(map) == true) {//若是空行则直接跳过                    continue;                }                //按照数据库表字段数进行对创建字符串数组                String[] record = new String[tfs.length];                assignContentToRecord(map, record, cr);                doUpdate(stmt, record);                conn.commit();            }            System.out.println("Success Update all data in excel " + getExcelFilename());            stmt.close();            ins.close();        } catch (IOException e) {            System.out.println("Failed to open file " + fromFile + ",skip it");        } catch (BiffException e) {        } catch (IndexOutOfBoundsException e) {        }    }    /////////////////////////////// Table 功能函数 ////////////////////////////////index为当前行在Excel表中的行号,从1开始(可以确保第0行为各个列的列名)    private void assignContentToRecord(Map mpr, String[] record, int index) {        int mapSize = mpr.size();        if (mapSize >= record.length) {            throw new RuntimeException("Impossable! check your data");        }        for (int i = 0; i < record.length; i++) {            if (i < mapSize) {                record[i] = mpr.get(ecs[i]).toString();            } else {//添加对 sortnum,filename,indate,linenum                String tf = tfs[i];                if ("sortnum".equalsIgnoreCase(tf)) {                    record[i] = "0";                } else if ("filename".equalsIgnoreCase(tf)) {                    record[i] = getExcelFilename();                } else if ("indate".equalsIgnoreCase(tf)) {                    record[i] = getCurrentTime();                } else if ("linenum".equalsIgnoreCase(tf)) {                    record[i] = "" + index;                }            }        }    }    protected void doUpdate(Statement stmt, String[] record) throws SQLException {        if (isContainMsg() != true) {//不含有Reqmsg,Respmsg,Sortnum三个字段 直接进行插入            stmt.executeUpdate(assembleSql(record));        } else { //含有Reqmsg,Respmsg,Sortnum            //找到reqmsg,respmsg,sortnum等字段的在表中的序号            int idxReq = getIndexOfField("reqmsg");            int idxResp = getIndexOfField("respmsg");            int idxSort = getIndexOfField("sortnum");            //获取Reqmsg,Respmsg的内容与长度            String msgReq = record[idxReq];            String msgResp = record[idxResp];            int lenReq = msgReq.length();            int lenResp = msgResp.length();            if (lenReq > MSG_MAX_LEN || lenResp > MSG_MAX_LEN) {//只要有一个长度超过就要进行分割                //从两个长度中选出较大的那个 按照它来进行分割段数的确定                int lenReal = lenReq - lenResp > 0 ? lenReq : lenResp;                int spn = getSpiltNum(lenReal, MSG_MAX_LEN);                for (int i = 0; i < spn; i++) {//此处进行分割插入                    record[idxReq] = getSpiltString(msgReq, i);//原先的较长字符串被替换成为短串                    record[idxResp] = getSpiltString(msgResp, i);                    record[idxSort] = "" + i;                    stmt.executeUpdate(assembleSql(record));                }            } else {//均是小于 最大限定长度 就直接插入                stmt.executeUpdate(assembleSql(record));            }        }    }    //检查 是否为空行    protected boolean checkBlankLine(Map mp) {        int emptyFieldCount = 0;        for (int i = 0; i < mp.size(); i++) {            if ("".equalsIgnoreCase(mp.get(ecs[i]).toString()) == false) {                return false;            } else {                emptyFieldCount++;            }        }        return emptyFieldCount == mp.size();    }    // 检查数据库表里面是否存在数据    public int checkData(Connection conn, String fieldValue) {        int hasRecord = -1;        String sql = "select max(linenum) from "                + tablename + " where filename='" + fieldValue + "'"+checkSortnumExist();        try {            Statement stmt = conn.createStatement();            ResultSet rs = stmt.executeQuery(sql);            if (rs.next()) {                hasRecord = rs.getInt(1);            }            rs.close();            stmt.close();        } catch (SQLException e) {        }        return hasRecord;    }    private String checkSortnumExist(){        String retStr="";        if (getIndexOfField("sortnum")!=-1) {            retStr=" and sortnum=0";        }        return retStr;    }    public boolean isContainMsg() {        return getIndexOfField("reqmsg") != -1;    }    //查看是否含有指定名称的字段在数据表里面,若是fieldName存在,返回其索引号;否则返回-1    protected int getIndexOfField(String fieldName) {        int idxReq = -1;        for (int i = 0; i < tfs.length; i++) {            if (fieldName.equalsIgnoreCase(tfs[i])) {                idxReq = i;                break;            }        }        return idxReq;    }    public String getCurrentTime() {        return getCurrentTime("yyyy-MM-dd HH:mm:ss");    }    protected String getCurrentTime(String format) {        SimpleDateFormat sdf = new SimpleDateFormat(format);        return sdf.format(new Date());    }//组装sql语句    public String assembleSql(String[] record) {        if (record.length != tfs.length) {            throw new RuntimeException("Data format error");        }        StringBuilder sb = new StringBuilder(assembleSqlHeader());        for (int i = 0; i < record.length; i++) {            //添加对date的转换处理            if (tfs[i].endsWith("date")) {                sb.append("to_date('").append(record[i]).append("','yyyy-mm-dd hh24:mi:ss')");            } else {                sb.append("'").append(record[i]).append("'");            }            if (i < tfs.length - 1) {                sb.append(",");            } else {                sb.append(")");            }        }        return sb.toString();    }    private String assembleSqlHeader() {        if (insertsqlheader == null) {            StringBuilder sb = new StringBuilder("insert into ");            sb.append(tablename).append("(");            for (int i = 0; i < tfs.length; i++) {                sb.append(tfs[i]);                if (i < tfs.length - 1) {                    sb.append(",");                } else {                    sb.append(") values(");                }            }            insertsqlheader = sb.toString();        }        return insertsqlheader;    }    // 根据起始位置与分割长度来确定分割出来的子串的内容    private String getSplitString(String value, int startPos, int interval) {        String retString = "";//返回的默认值        int slen = value.length();        if (startPos <= slen) {            if (startPos + interval <= slen) {                retString = value.substring(startPos, startPos + interval);            } else {                retString = value.substring(startPos);            }        }        return retString;    }    protected String getSpiltString(String source, int index) {        return getSplitString(source, index * MSG_MAX_LEN, MSG_MAX_LEN);    }    protected int getSpiltNum(int len, int interval) {        int retInt = len / interval;        if (len % interval != 0) {            retInt++;        }        return retInt;    }}

5.FileFirewall<FileBase的其中一个派生类,>

/* * Copyright 2000 - 2014 RollStone  * All Rights Reservered. */package csi.file;/** * @brief * * @author RollStone * @created 2014-07-24 05:17:47 */public class FileFirewall extends FileBase {    public FileFirewall() {        setECS("交易流水,手机号码,省份编码,渠道编码,接口类型,返回码,返回信息描述"                + ",防火墙开启时间,接口请求时间,请求报文,返回报文");        setKeyword("firewall");        setTablename("loc_check_file_firewall");        setTFS("Redoid,servnumber,Region,channeltype,BIPCode"                + ",Retcode,Retdesc,Firewallondate,reqdate,Reqmsg,respmsg"                + ",Sortnum,Filename,indate,Linenum");    }}