asp.net中使用OLEDB操作Excel

標籤：blog   http   os   io   使用   ar   for   檔案   資料   

最近項目中需要匯出Excel資料表單，試了好幾種方法，都感覺不怎麼順手，然後老大喊我去看看OLEDB，我接著花世間去學習了一下，感覺還挺不錯的。

開始在網上找了一些代碼，

然後需要配置連接字串

我電腦上安裝的office 2007：
所以連接字串為："Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended properties=\"Excel 12.0; HDR=Yes;\"";

HDR=Yes 表示第一行包含列名,在計算行數時就不包含第一行

IMEX   0:匯入模式,1:匯出模式:2混合模式

在VS上一跑，發現會報錯

網上去找了一下原因，發現很多人都遇到過這種問題，所以答案很快就找到了，原來是沒有安裝“AccessDatabaseEngine.exe”這個外掛程式。

於是去網上下載安裝之後，先前的代碼就能夠跑了。

下面是一些簡單的動作陳述式：

1.從Excel裡讀取資料

 string str1="select * from [Sheet1$]";

2.更新Excel裡的資料

string str2="update [Sheet1$] set FieldName1=‘aaa‘ where FiledName2=‘30‘";

3.向Excel裡寫入資料

string str3="insert into [Sheet1$](FieldName1,FieldName2,...) values(‘a‘,‘b‘,...)";

對於非標準結構的Excel表格，可以採用以下方法：

4.讀取資料

string str4="select * from [Sheet1$A3:F20]";

5.更新資料

string str5="update [Sheet1$A9:F15] set FieldName=‘bbb‘ where AnotherFieldName=‘b3‘";

6.插入資料

string str6="insert into [Sheet1$A9:F15](FildName1,FieldName2,..) values(‘a‘,‘b‘,...)";

下面是我的一些測試代碼：

向Excel寫入資料

string strFilePath = "E:\\excel1.xls";        string str1 = "insert into [Sheet1$](商戶ID,商家名稱)values('DJ001','點擊科技')";//excel1.xls必須已經存在，而且已有列名        DoOleSql(str1,strFilePath);

建立Excel表並寫入資料

string filepaths = "E:\\excel2.xls";        string stra= "CREATE TABLE CustomerInfo ([CustomerID] VarChar,[Customer] VarChar)";        string strb = "insert into CustomerInfo(CustomerID,Customer)values('DJ001','點擊科技')";        DoOleSql(stra,filepaths);        DoOleSql(strb,filepaths);

以上兩個代碼中用到的DoOleSql函數如下：

 protected void DoOleSql(string sql, string filepath)    {        OleDbConnection conn = new OleDbConnection();        conn.ConnectionString = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended properties=\"Excel 12.0; HDR=Yes;\"";        try        {//開啟串連            conn.Open();        }        catch (Exception e)        {            //Response.Write(e.ToString());        }        OleDbCommand olecommand = new OleDbCommand(sql, conn);        try        {//執行語句            olecommand.ExecuteNonQuery();        }        catch (Exception eee)        {            //Response.Write(eee.ToString());            conn.Close();        }        finally        {            conn.Close();//關閉資料庫        }        conn.Close();    }

將DataTable裡的資料匯出到Excel裡

protected void Button3_Click(object sender, EventArgs e)    {        SqlHelp sqla = new SqlHelp();        string strfaca = "select * from tOAPower order by LoginID asc";        DataTable dta = sqla.GetDataTable(strfaca);        sqla.SqlClose();        //string modelpath = "E:\\staff.xlsx";        string realpath = "E:\\staff.xls";        DataTable2Excel(dta,realpath,500);    }       /// <summary>    /// 根據DataTable產生Excel    /// </summary>    /// <param name="dataTable">資料來源</param>    /// <param name="fileName">要儲存的路徑</param>    /// <param name=" rowsCount ">當一個工作表最多的行數rowsCount，當超過時，則建立工作表。</param>    /// <returns>產生成功則返回True，否則返回False</returns>    public static bool DataTable2Excel(DataTable dataTable, string fileName, int rowsCount)    {        bool rt = false;//用於傳回值        if (dataTable == null && rowsCount < 1)        {            return false;        }        int rowNum = dataTable.Rows.Count;//擷取行數        int colNum = dataTable.Columns.Count;//擷取列數        int SheetNum = (rowNum - 1) / rowsCount + 1; //擷取工作表數        string sqlText = "";//帶類型的列名        string sqlValues = "";//值        string colCaption = "";//列名        for (int i = 0; i < colNum; i++)        {            if (i != 0)            {                sqlText += " , ";                colCaption += " , ";            }            sqlText += "[" + dataTable.Columns[i].Caption.ToString() + "] VarChar";//產生帶VarChar列的標題            colCaption += "[" + dataTable.Columns[i].Caption.ToString() + "]";//產生列的標題        }        String sConnectionString = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + fileName + ";Extended properties=\"Excel 12.0; HDR=Yes;\"";        OleDbConnection cn = new OleDbConnection(sConnectionString);        try        {            //判斷檔案是否存在,存在則先刪除            if (File.Exists(fileName))            {                File.Delete(fileName);            }            int sheet = 1;//表數            int dbRow = 0;//資料的行數            //開啟串連            cn.Open();            while (sheet <= SheetNum)            {                string sqlCreate = "CREATE TABLE [Sheet" + sheet.ToString() + "] (" + sqlText + ")";                OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);                //建立Excel檔案                cmd.ExecuteNonQuery();                for (int srow = 0; srow < rowsCount; srow++)                {                    sqlValues = "";                    for (int col = 0; col < colNum; col++)                    {                        if (col != 0)                        {                            sqlValues += " , ";                        }                        sqlValues += "'" + dataTable.Rows[dbRow][col].ToString() + "'";//拼接Value語句                    }                    String queryString = "INSERT INTO [Sheet" + sheet.ToString() + "] (" + colCaption + ") VALUES (" + sqlValues + ")";                    cmd.CommandText = queryString;                    cmd.ExecuteNonQuery();//插入資料                    dbRow++;//目前資料的行數自增                    if (dbRow >= rowNum)                    {                        //目前資料的行數等於rowNum時退出迴圈                        break;                    }                }                sheet++;            }            rt = true;        }        catch        {        }        finally        {            cn.Close();        }        return rt;    }  

大體情況就是這個樣子，我現在還有一些小問題，比如匯出的Excel文檔，不是嚴格意義上的Excel文檔，開啟的時候會出現這種情況

如果各位有解決方案的，請指教哦。

asp.net中使用OLEDB操作Excel