EXCEL表里的数据导入SQL中

代码是从EXCEL2007导入到数据库的，如果想从2003中导入，则将Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'　改为Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'View Code using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;using System.Data.OleDb;using System.Collections;using System.IO;using Excel = Microsoft.Office.Interop.Excel;using Microsoft.Office.Interop.Excel;namespace SqlExcel{ public partial class Form1 : Form { public Form1() { InitializeComponent(); } SqlHelper sqlhelper = new SqlHelper(); private void Form1_Load(object sender, EventArgs e) { } #region Excel导入SQL数据库 /// /// 选择要向SQL数据库中导入数据的Excel文件 /// private void btnChoose_Click(object sender, EventArgs e) { using (OpenFileDialog dialog = new OpenFileDialog()) { dialog.Multiselect = true; if (dialog.ShowDialog() == DialogResult.OK) { try { txtPath.Text = dialog.FileName; } catch { } } } } /// /// 获取Excel数据表列表 /// /// public static ArrayList GetExcelTables(string FilePath) { //将Excel架构存入数据里 System.Data.DataTable dt = new System.Data.DataTable(); ArrayList TablesList = new ArrayList(); if (File.Exists(FilePath)) { using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1\";Data Source=" + FilePath)) { try { conn.Open(); dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); } catch (Exception exp) { MessageBox.Show(exp.Message); } //获取数据表个数 int tablecount = dt.Rows.Count; for (int i = 0; i < tablecount; i = i + 2) { string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$'); if (TablesList.IndexOf(tablename) < 0) { TablesList.Add(tablename); } } } } return TablesList; } /// /// 导入Excel数据表至DataTable（第一行作为表头） /// /// public static System.Data.DataSet FillDataSet(string FilePath) { if (!File.Exists(FilePath)) { throw new Exception("Excel文件不存在！"); } ArrayList TableList = new ArrayList(); TableList = GetExcelTables(FilePath); if (TableList.Count <= 0) { return null; } System.Data.DataTable table; System.Data.DataSet ds = new DataSet(); OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'"); try { if (dbcon.State == ConnectionState.Closed) { dbcon.Open(); } for (int i = 0; i < TableList.Count; i++) { string dtname = TableList[i].ToString(); try { OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", dbcon); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); table = new System.Data.DataTable(); adapter.Fill(table); ds.Tables.Add(table); } catch (Exception exp) { MessageBox.Show(exp.Message); } } } finally { if (dbcon.State == ConnectionState.Open) { dbcon.Close(); } } return ds; } /// /// Excel导入数据库 /// /// public static DataSet ImportFromExcel(string FilePath) { return FillDataSet(FilePath); } /// /// 将Excel中的数据导入到SQL数据库中 /// private void btnExcelToSql_Click(object sender, EventArgs e) { int ii = 0; DataSet ds = ImportFromExcel(txtPath.Text); System.Data.DataTable dt = ds.Tables[0]; try { string strInsertComm; for (int i = 0; i < dt.Rows.Count; i++) { strInsertComm = ""; strInsertComm = "Insert INTO T_Student(F_Num,F_Name,F_Sex,F_Dept)"; strInsertComm += " values("; for (int j = 0; j < dt.Columns.Count; j++) { if (j > 0) { strInsertComm += ",'" + dt.Rows[i][j].ToString().Trim() + "'"; } else { strInsertComm += "'" + dt.Rows[i][j].ToString().Trim() + "'"; } } strInsertComm += ")"; ii = sqlhelper.getcomnum(strInsertComm); } if (ii > 0) { MessageBox.Show("导入成功！"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } #endregion