.NET 匯入Excel

1. 使用OleDbDataAdapter + GridView

string OleConStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", Server.MapPath(xlsFullName));

OleDbConnection Conn = new OleDbConnection(OleConStr);

Conn.Open();
DataSet Ds = new DataSet();
OleDbDataAdapter Da = new OleDbDataAdapter("Select * from [Sheet1$]", Conn);
Da.Fill(Ds, "excel_data");
Conn.Close();

gv_test.DataSource = Ds.Tables["excel_data"];
gv_test.DataBind();

OleCn.Close();

2. 使用OleDbDataReader (read row by row)

string OleConStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", Server.MapPath(xlsFullName));

OleDbConnection OleCn = new OleDbConnection(OleConStr);
OleDbCommand OleCmd = new OleDbCommand();
OleCmd.Connection = OleCn;
string strSQL = @"SELECT * FROM [Sheet1$]";
OleCmd.CommandText = strSQL;

try
{
OleCn.Open();
OleDbDataReader OleDr = OleCmd.ExecuteReader();
while (OleDr.Read())
{

~~~ CUSTOM CODE ~~~

{
catch (Exception ex)
{
String scriptString = string.Format(@"alert('Excel格式錯誤! FilePath:{0} ; Advance MSG:{1}');", xlsFullName, ex.Message);
ClientScript.RegisterStartupScript(this.GetType(), "s", scriptString, true);
return;
}

OleCn.Close();


註:read Excel檔案時要注意是否有權限,包括檔案的權限,資料夾的權限,以及web.config檔裡面的屬性設定成 false

<identity impersonate="false"/>