ASP.NET Excel导入到SQL Server数据库
提供把Excel里的数据导入到SQL Server 数据库，前提是Excel里的字段在Sql Server表里都有，不然会出现错误。注释很详细哦！要引用的命名空间是：
using System.Data.OleDb;
using System.Data.SqlClient;
//操作类
public class ExcelToSQL
{
//string SqlConnectionString = "Server=(local);Initial Catalog=Test;Integrated Security=True";
public SqlConnection sqlcon; //创建SQL连接
public SqlCommand sqlcom; //创建SQL命令对象
public ExcelToSQL()
{
DataOperation dataOperation = new DataOperation(); //用到平台的函数，就是初始化SqlConnection对象
DBUnit dbUnit = dataOperation.GetDbUnit();
sqlcon = (SqlConnection)dbUnit.cnt;
if (sqlcon.State.ToString() == "Open")
sqlcon.Close();
}
public int ImportSql(string excelPath, string tableName) //导入的Excel的路径，数据库里的表名
{
if (!TableExist(tableName)) //表名是否存在
return (int)ImportState.tableNameError;
DataTable dt = ExcelToDataTable(excelPath);
if (dt == null)
{
return (int)ImportState.excelFormatError;
}
ArrayList tableField = GetTableField(tableName); //表格的列名称
string columnName = "ID,"; //Excel里的列名，增加一个ID列
for (int i = 0; i < dt.Columns.Count; i++)
{
columnName += dt.Columns[i].ColumnName + ",";
string currentColumn = dt.Columns[i].ToString().ToUpper(); //当前列名
for (int j = 0; j < tableField.Count; j++)
{
if (tableField[j].ToString().ToUpper() == dt.Columns[i].ToString().ToUpper())
break; //跳出本层和上一层循环，continue是跳出本层循环，如果用continue，会继续执行j++
//Excel里的字段必须在Sql中都有
if ((tableField[j].ToString().ToUpper() != dt.Columns[i].ToString().ToUpper()) && j == tableField.Count - 1)
return (int)ImportState.fieldMatchError;
}
}
int m = columnName.LastIndexOf(',');
columnName = columnName.Remove(m); //移除最后一个逗号
sqlcom = new SqlCommand();
sqlcom.Connection = sqlcon;
sqlcon.Open();
sqlcom.CommandType = CommandType.Text;
for (int h = 0; h < dt.Rows.Count; h++)
{
string value = "'" + System.Guid.NewGuid().ToString() + "'" + ",";
for (int k = 0; k < dt.Columns.Count; k++) //根据列名得到值
{
value += "'" + dt.Rows[h][k].ToString() + "'" + ",";
}
value = value.Remove(0, 1);
int n = value.LastIndexOf(',');
value = value.Remove(n); //移除最后一个逗号
n = value.LastIndexOf("'");
value = value.Remove(n);
try
{
string sql = "insert into " + tableName + "(" + columnName + ") values('" + value + "')";
sqlcom.CommandText = sql;
string sss = sqlcom.ExecuteNonQuery().ToString();
}
catch (Exception err)
{
string erroe = err.Message;
return (int)ImportState.dataTypeError;
}
}
sqlcon.Close();
sqlcom.Dispose();
return (int)ImportState.right;
}
public DataTable ExcelToDataTable(string excelPath) //把Excel里的数据转换为DataTable，并返回DataTable
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'";
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = "SELECT * FROM [Sheet1$]";
DataTable dt;
try
{
Conn.Open();
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[Sheet1$]");
Conn.Close();
dt = ds.Tables[0];
}
catch(Exception err)
{
return null;
}
return dt;
}
public bool TableExist(string tableName) //查看数据库里是否有此表名
{
sqlcom = new SqlCommand();
sqlcom.Connection = sqlcon;
sqlcom.CommandType = CommandType.Text;
try
{
sqlcon.Open();
string sql = "select name from sysobjects where type='u'";
sqlcom.CommandText = sql;
SqlDataReader sqldr = sqlcom.ExecuteReader();
while (sqldr.Read())
{
if (sqldr.GetString(0).ToUpper() == tableName.ToUpper())
return true;
}
}
catch { return false; }
finally
{
sqlcon.Close();
}
return false;
}
public ArrayList GetTableField(string tableName) //得到数据库某一个表中的所有字段
{
ArrayList al = new ArrayList();
sqlcom = new SqlCommand();
sqlcom.Connection = sqlcon;
sqlcom.CommandType = CommandType.Text;
try
{
sqlcon.Open();
string sql = "SELECT b.name FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id WHERE (a.name = '" + tableName + "')";
sqlcom.CommandText = sql;
SqlDataReader sqldr = sqlcom.ExecuteReader();
while (sqldr.Read())
{
al.Add(sqldr.GetString(0));
}
}
finally
{
sqlcon.Close();
}
return al; //返回的是表中的字段
}
public enum ImportState
{
right = 1, //成功
tableNameError = 2,//表名不存在
fieldMatchError = 3,//excel里的字段和数据库表里的字段不匹配
dataTypeError = 4, //转换数据类型时发生错误
excelFormatError=5,//Excel格式不能读取
}
public void Alert(string str)
{
HttpContext.Current.Response.Write("<script language='javascript'>alert('" + str + "');</script>");
}
}
//页面调用
protected void btnExport_Click(object sender, EventArgs e)
{
string filepath = this.fileUpload.PostedFile.FileName;
if (filepath != "")
{
if (this.txtTableName.Text != "")
{
if (filepath.Contains("xls"))
{
int result = ets.ImportSql(filepath, this.txtTableName.Text);
if (result == (int)ExcelToSQL.ImportState.tableNameError)
ets.Alert("此表名在数据中不存在！");
else if(result==(int)ExcelToSQL.ImportState.excelFormatError)
ets.Alert("Excel格式不能正确读取！");
else if (result == (int)ExcelToSQL.ImportState.fieldMatchError)
ets.Alert("Excel里的字段和Sql Server里的字段不匹配！");
else if(result==(int)ExcelToSQL.ImportState.dataTypeError)
ets.Alert("转换数据类型时发生错误!");
else if (result == (int)ExcelToSQL.ImportState.right)
{
ets.Alert("导入成功");
}
}
else ets.Alert("上传的文件类型必须为excel文件！");
}
else ets.Alert("表名不能为空！");
}
else ets.Alert("没有选择要上传的文件！");
}
//前台代码
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="路径"></asp:Label>
<asp:FileUpload ID="fileUpload" runat="server" Width="443px" /><br />
<asp:Label ID="Label2" runat="server" Text="数据库表名称"></asp:Label>
<asp:TextBox ID="txtTableName" runat="server"></asp:TextBox><br />
<asp:Button ID="btnExport" runat="server" Text="导入到SQL" OnClick="btnExport_Click" />
</div>
</form>