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 id="form1" runat="server"> 2 <div> 3 <asp:Label ID="Label1" runat="server" Text="路径"></asp:Label> 4 <asp:FileUpload ID="fileUpload" runat="server" Width="443px" /><br /> 5 <asp:Label ID="Label2" runat="server" Text="数据库表名称"></asp:Label> 6 <asp:TextBox ID="txtTableName" runat="server"></asp:TextBox><br /> 7 <asp:Button ID="btnExport" runat="server" Text="导入到SQL" OnClick="btnExport_Click" /> 8 </div> 9 </form>
posted on 2015-03-22 10:37 ultrastrong 阅读(652) 评论(0) 编辑 收藏 举报