讀取excel內容到數據庫

最近在做一個小項目遇到一個問題，對於一個科研學科，分爲一級學科和二級學科，且這些數據都是放在一個excel文件中。數據量接近900行，如果手工錄入肯定會瘋掉，說不定還會錄錯，既然咱是程序猿肯定不用自己去幹這麼傻的事。網上溜達了一下找到一個解決方案。

1、首先到網上去找一個jxl.jar的jar包，加入到項目中。(注意一點：此jar包不支持excel2007+版本)

2、將要讀取的資源放到硬盤上容易找到的地方，爲了方便我就放在D盤下了。

3、加入數據庫連接jar包，寫一個數據庫管理類

DBManager:

package com.akwolf.transform; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBManager { private String url = "jdbc:mysql://localhost:3306/mytest"; private volatile static DBManager dbManger; private Connection conn ; private DBManager(){ } public static DBManager getInstance() { if (dbManger == null) { synchronized (DBManager.class) { if (dbManger == null) { try { Class.forName("com.mysql.jdbc.Driver") ; } catch (ClassNotFoundException e) { e.printStackTrace(); } dbManger = new DBManager(); } } } return dbManger; } public Connection getConnection() { try { conn = DriverManager.getConnection(url, "zhangh", "123456"); } catch (SQLException e) { e.printStackTrace(); } return conn; } public void realse(){ if(conn!=null){ try { conn.close() ; } catch (SQLException e) { e.printStackTrace(); } } } }

4、ok，現在就來進入主要部分編寫轉換代碼

package com.akwolf.transform; import java.io.File; import java.sql.Connection; import java.sql.Statement; import jxl.Sheet; import jxl.Workbook; /** * 讀取Excel到數據庫 * * @author zhanghua * */ public class TransformExcel { public static void main(String[] args) throws Exception { File file = new File("D:" + File.separator + "subject.xls"); //取得一個可以操作的excel Workbook workbook = Workbook.getWorkbook(file); Sheet[] sheet = workbook.getSheets(); Connection conn = DBManager.getInstance().getConnection();// 取得連接 TransformExcel excel = new TransformExcel(); String sn = "", intro = "", sql = "", firstSn = ""; boolean insert = true; //是否向數據庫中插入數據，若一行數據爲空據不進行插入 for (int x = 0; x < sheet.length; x++) { for (int y = 0; y < sheet[x].getRows(); y++) { boolean first = false; for (int z = 0; z < sheet[x].getColumns(); z++) { String content = sheet[x].getCell(z, y).getContents(); // 本行爲空 if (z == 0 && content.equals("")) { insert = false; break; } // 如果是一級學科 if (content.length() == 3 && z == 0) { firstSn = content; first = true; } //設置每列的數據庫 if (z == 0) { sn = content; } else if (z == 1) { intro = content; } } //拼裝sql語句 if (first) { sql = "insert into firstsubject(first_subject_id,first_subject_name) values('" + sn + "','" + intro + "')"; first = false; } else { sql = "insert into secondsubject(second_subject_id,second_subject_name,first_subject_id) values('" + sn + "','" + intro + "','" + firstSn + "')"; } //是否可以插入到數據庫 if (insert) { excel.insertIntoDB(conn, sql); }else{ insert = true; } } } DBManager.getInstance().realse(); } public void insertIntoDB(Connection conn, String sql) throws Exception { Statement stmt = conn.createStatement(); stmt.executeUpdate(sql); stmt.close(); } }
5、貼上數據庫建表語句

set foreign_key_checks=0; create table if not exists firstsubject ( first_subject_id varchar(100) not null, -- 一級學科主鍵 first_subject_name varchar(255) null, -- 一級學科名稱 isdel bit(1) default b'0', -- defautlt 0,刪除標記位 balance int(11) null, -- 排序 primary key (first_subject_id) ); create table if not exists secondsubject ( second_subject_id varchar(100) not null, -- 二級學科主鍵 second_subject_name varchar(255) null, -- 二級學科名稱 first_subject_id varchar(100) not null, -- 關聯firstsubject.first_subject_id isdel bit(1) default b'0', -- defautlt 0,刪除標記位 balance int(11) null, -- 排序 primary key (second_subject_id), key first_second_fk (first_subject_id), constraint first_second_fk foreign key (first_subject_id) references firstsubject (first_subject_id) on delete cascade on update cascade );

看一下運行結果：

好了這個小demo完成了,希望可以幫到大家！！

所有用到的素材都打包好了，有需要的同學可以自己去下載。

http://download.csdn.net/detail/akwolf/3636901