ASP.NET Excel导入到SQL Server数据库
本文转自：http://www.cnblogs.com/lhking/archive/2009/06/08/1499002.html
提供把Excel里的数据导入到SQL Server 数据库，前提是Excel里的字段在Sql Server表里都有，不然会出现错误。注释很详细哦！要引用的命名空间是：
using System.Data.OleDb;
using System.Data.SqlClient;
1 public class ExcelToSQL
2 {
3 //string SqlConnectionString = "Server=(local);Initial Catalog=Test;Integrated Security=True";
4 public SqlConnection sqlcon; //创建SQL连接
5 public SqlCommand sqlcom; //创建SQL命令对象
6
7
8 public ExcelToSQL()
9 {
10 DataOperation dataOperation = new DataOperation(); //用到平台的函数，就是初始化SqlConnection对象
11 DBUnit dbUnit = dataOperation.GetDbUnit();
12 sqlcon = (SqlConnection)dbUnit.cnt;
13 if (sqlcon.State.ToString() == "Open")
14 sqlcon.Close();
15 }
16 public int ImportSql(string excelPath, string tableName) //导入的Excel的路径，数据库里的表名
17 {
18 if (!TableExist(tableName)) //表名是否存在
19 return (int)ImportState.tableNameError;
20
21 DataTable dt = ExcelToDataTable(excelPath);
22 if (dt == null)
23 {
24 return (int)ImportState.excelFormatError;
25 }
26 ArrayList tableField = GetTableField(tableName); //表格的列名称
27
28 string columnName = "ID,"; //Excel里的列名，增加一个ID列
29 for (int i = 0; i < dt.Columns.Count; i++)
30 {
31 columnName += dt.Columns[i].ColumnName + ",";
32 string currentColumn = dt.Columns[i].ToString().ToUpper(); //当前列名
33 for (int j = 0; j < tableField.Count; j++)
34 {
35 if (tableField[j].ToString().ToUpper() == dt.Columns[i].ToString().ToUpper())
36 break; //跳出本层和上一层循环，continue是跳出本层循环，如果用continue，会继续执行j++
37 //Excel里的字段必须在Sql中都有
38 if ((tableField[j].ToString().ToUpper() != dt.Columns[i].ToString().ToUpper()) && j == tableField.Count - 1)
39 return (int)ImportState.fieldMatchError;
40 }
41 }
42 int m = columnName.LastIndexOf(',');
43 columnName = columnName.Remove(m); //移除最后一个逗号
44
45 sqlcom = new SqlCommand();
46 sqlcom.Connection = sqlcon;
47 sqlcon.Open();
48 sqlcom.CommandType = CommandType.Text;
49
50 for (int h = 0; h < dt.Rows.Count; h++)
51 {
52 string value = "'" + System.Guid.NewGuid().ToString() + "'" + ",";
53 for (int k = 0; k < dt.Columns.Count; k++) //根据列名得到值
54 {
55 value += "'" + dt.Rows[h][k].ToString() + "'" + ",";
56 }
57 value = value.Remove(0, 1);
58 int n = value.LastIndexOf(',');
59 value = value.Remove(n); //移除最后一个逗号
60 n = value.LastIndexOf("'");
61 value = value.Remove(n);
62
63 try
64 {
65 string sql = "insert into " + tableName + "(" + columnName + ") values('" + value + "')";
66 sqlcom.CommandText = sql;
67 string sss = sqlcom.ExecuteNonQuery().ToString();
68 }
69 catch (Exception err)
70 {
71 string erroe = err.Message;
72 return (int)ImportState.dataTypeError;
73 }
74 }
75 sqlcon.Close();
76 sqlcom.Dispose();
77
78 return (int)ImportState.right;
79 }
80 public DataTable ExcelToDataTable(string excelPath) //把Excel里的数据转换为DataTable，并返回DataTable
81 {
82 string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'";
83 System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
84 string strCom = "SELECT * FROM [Sheet1$]";
85 DataTable dt;
86 try
87 {
88 Conn.Open();
89 System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
90 DataSet ds = new DataSet();
91 myCommand.Fill(ds, "[Sheet1$]");
92 Conn.Close();
93 dt = ds.Tables[0];
94 }
95 catch(Exception err)
96 {
97 return null;
98 }
99 return dt;
100 }
101 public bool TableExist(string tableName) //查看数据库里是否有此表名
102 {
103 sqlcom = new SqlCommand();
104 sqlcom.Connection = sqlcon;
105 sqlcom.CommandType = CommandType.Text;
106 try
107 {
108 sqlcon.Open();
109 string sql = "select name from sysobjects where type='u'";
110 sqlcom.CommandText = sql;
111 SqlDataReader sqldr = sqlcom.ExecuteReader();
112 while (sqldr.Read())
113 {
114 if (sqldr.GetString(0).ToUpper() == tableName.ToUpper())
115 return true;
116 }
117 }
118 catch { return false; }
119 finally
120 {
121 sqlcon.Close();
122 }
123 return false;
124 }
125 public ArrayList GetTableField(string tableName) //得到数据库某一个表中的所有字段
126 {
127 ArrayList al = new ArrayList();
128 sqlcom = new SqlCommand();
129 sqlcom.Connection = sqlcon;
130 sqlcom.CommandType = CommandType.Text;
131 try
132 {
133 sqlcon.Open();
134 string sql = "SELECT b.name FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id WHERE (a.name = '" + tableName + "')";
135 sqlcom.CommandText = sql;
136 SqlDataReader sqldr = sqlcom.ExecuteReader();
137 while (sqldr.Read())
138 {
139 al.Add(sqldr.GetString(0));
140 }
141 }
142 finally
143 {
144 sqlcon.Close();
145 }
146 return al; //返回的是表中的字段
147 }
148 public enum ImportState
149 {
150 right = 1, //成功
151 tableNameError = 2,//表名不存在
152 fieldMatchError = 3,//excel里的字段和数据库表里的字段不匹配
153 dataTypeError = 4, //转换数据类型时发生错误
154 excelFormatError=5,//Excel格式不能读取
155 }
156 public void Alert(string str)
157 {
158 HttpContext.Current.Response.Write("<script language='javascript'>alert('" + str + "');</script>");
159 }
160 }页面调用：
1 protected void btnExport_Click(object sender, EventArgs e)
2 {
3 string filepath = this.fileUpload.PostedFile.FileName;
4 if (filepath != "")
5 {
6 if (this.txtTableName.Text != "")
7 {
8 if (filepath.Contains("xls"))
9 {
10 int result = ets.ImportSql(filepath, this.txtTableName.Text);
11
12 if (result == (int)ExcelToSQL.ImportState.tableNameError)
13 ets.Alert("此表名在数据中不存在！");
14 else if(result==(int)ExcelToSQL.ImportState.excelFormatError)
15 ets.Alert("Excel格式不能正确读取！");
16 else if (result == (int)ExcelToSQL.ImportState.fieldMatchError)
17 ets.Alert("Excel里的字段和Sql Server里的字段不匹配！");
18 else if(result==(int)ExcelToSQL.ImportState.dataTypeError)
19 ets.Alert("转换数据类型时发生错误!");
20 else if (result == (int)ExcelToSQL.ImportState.right)
21 {
22 ets.Alert("导入成功");
23 }
24 }
25 else ets.Alert("上传的文件类型必须为excel文件！");
26 }
27 else ets.Alert("表名不能为空！");
28 }
29 else ets.Alert("没有选择要上传的文件！");
30 }前台代码：
1 <form runat="server"> 2 <div> 3 <asp:Label runat="server" Text="路径"></asp:Label> 4 <asp:FileUpload runat="server" Width="443px" /><br /> 5 <asp:Label runat="server" Text="数据库表名称"></asp:Label> 6 <asp:TextBox runat="server"></asp:TextBox><br /> 7 <asp:Button runat="server" Text="导入到SQL" OnClick="btnExport_Click" /> 8 </div> 9 </form>
