using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
public partial class 导入数据_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
/// <summary>
/// 导入按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
Label2.Text = DateTime.Now.ToString();
//测试,将excel中的sheet1导入到sqlserver中
string connString = "server=localhost;uid=sa;pwd=123;database=master";
string path = Server.MapPath("abc.xls");
TransferData(path, "Sheet1", connString);
Label3.Text = DateTime.Now.ToString();
TimeSpan sp = DateTime.Now.Subtract(Convert.ToDateTime(Label2.Text));
Label4.Text = sp.Seconds.ToString();
}
/// <summary>
/// 导入数据
/// </summary>
public void TransferData(string excelFile, string sheetName, string connectionString)
{
DataSet ds = new DataSet();
try
{
//获取全部数据(将EXCEL表中数据填充到DataSet)
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";// 连接EXCEL文件
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
//如果目标表不存在则创建(自动创建表)
string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
{
// 根据excel文件得到列名
strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
}
strSql = strSql.Trim(',') + ")";// 去掉最后的","
// 起到自动释放的作用
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
command.ExecuteNonQuery();
sqlconn.Close();
}
//用bcp导入数据
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.BatchSize = 100;//每次传输的行数
bcp.NotifyAfter = 100;//进度提示的行数
bcp.DestinationTableName = sheetName;//目标表
bcp.WriteToServer(ds.Tables[0]);
}
}
catch (Exception ex)
{
//错误提示消息。
}
}
/// <summary>
/// 进度显示
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
{
Label1.Text = e.RowsCopied.ToString();
}
}