C#读取Excel（通过OleDb）

通过OleDb连接，读取Excel。

Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'

特别注意：

　　Extended Properties='Excel 8.0;HDR=yes;IMEX=1'

　　A：HDR ( HeaDer Row ) 默认值YES。

　　HDR=Yes表示第一行是标题，不做为数据使用；当HDR=NO，则表示第一行不是标题，做为数据来使用。

　　B：IMEX ( IMport EXport mode )

　　IMEX 有三种模式，各自引起的读写行为也不同，容後再述：

　　0 is Export mode

　　1 is Import mode

　　2 is Linked mode (full update capabilities)

　　当IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。

　　当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。

　 IMEX=1 解决数字与字符混合时,识别不正常的情况。

　　当 IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。

　　C：Excel 8.0

　　对于Excel 97以上版本都用Excel 8.0

// 导入收款单数据
         protected void Button2_Click(object sender, EventArgs e)
         {
             try{
                 string path = "D:\\Work\\核销记录20121116\\";
                 string[] names = new string[]{
                     //"天津 期初数据格式（核销记录）.xlsx"
                     "浙江大区债权数据（核销记录）.xlsx"
                 };
                 for (int i = 0; i < names.Length;i++ )
                 {
                     readExcel(path, names[i]);
                 }
                 // 操作数据库
                 insertDB();
             }
             catch (Exception ex)
             {
                 string Script = CommonBLL.getRedirectErrorPageScript("ZJ0000", Server.UrlEncode(Request.RawUrl.ToString()), ex);
                 Response.Write(Script);
                 Response.End();
             }
         }
 
        private void readExcel(string path, string name)
         {
             object missing = Missing.Value;
             Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
             excelApp.Workbooks.Open(path + name, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
 
            for (int index = 0; index < excelApp.Workbooks[1].Worksheets.Count; index++)
             {
                 //只导第一个Sheet
                 if(index > 0){
                     break;
                 }
                 //取得每个Sheet名
                 Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Workbooks[1].Worksheets[index + 1];
                 string sSheetName = ws.Name;
 
                DataSet ds = ExecleDs(path + name, sSheetName);
                 DataTable dt = ds.Tables["Execl"];
 
                for (int i = 0; i < dt.Rows.Count; i++)
                 {
                     if (String.IsNullOrEmpty(dt.Rows[i]["整机机号"].ToString()))
                     {
                         continue;
                     }
                     //收款单号
                     iDocumentNo++;
                     string receiptDocumentNo = "SK" + iDocumentNo;
                     aList.Add(getInsertReceiptDocument(dt.Rows[i], receiptDocumentNo, name));
                 }
             }
         }
 
        private DataSet ExecleDs(string filenameurl,string sheet)
         {
             OleDbConnection OleCon = new OleDbConnection();
             OleDbCommand OleCmd = new OleDbCommand();
             OleDbDataAdapter OleDa = new OleDbDataAdapter();
             DataSet Myds = new DataSet();
             OleCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filenameurl + ";Extended Properties=Excel 12.0;";
             OleCon.Open();
             OleCmd.CommandText = "select * from ["+sheet+"$]";
             OleCmd.Connection = OleCon;
             OleDa.SelectCommand = OleCmd;
             OleDa.Fill(Myds, "Execl");
             OleCon.Close();
             OleCmd.Dispose();
             OleDa.Dispose();
             OleCon.Dispose();
             return Myds;
         }
 
        private ReceiptDocumentModel getInsertReceiptDocument(DataRow dr, string receiptDocumentNo, string fileName)
         {
             ReceiptDocumentModel receiptDocumentModel = new ReceiptDocumentModel();
 
            receiptDocumentModel.Receipt_document_no = receiptDocumentNo;
 
            switch (fileName.Substring(0, 2))
             {
                 case "天津":
                     receiptDocumentModel.Business_area = "B0002";
                     break;
                 case "浙江":
                     receiptDocumentModel.Business_area = "C0003";
                     break;
             }
             receiptDocumentModel.Machine_no = dr["整机机号"].ToString();
             receiptDocumentModel.Receipt_status = "DL119001";
             receiptDocumentModel.Receipt_amount = CommonBLL.converTextToAmount(dr["核销金额"].ToString());
             receiptDocumentModel.Receipt_verificated_amount = 0;
             receiptDocumentModel.Receipt_refund_amount = 0;
             receiptDocumentModel.Receipt_balance = CommonBLL.converTextToAmount(dr["核销金额"].ToString());
             receiptDocumentModel.Receipted_date = DateTime.Parse(dr["核销日期"].ToString()).ToString("yyyy-MM-dd");
             receiptDocumentModel.Creater = "admin";
             receiptDocumentModel.Last_modify_date = DateTime.Today.ToString("yyyy-MM-dd");
             receiptDocumentModel.Remark = dr["销售合同号"].ToString();
 
            return receiptDocumentModel;
         }
 

        // 操作数据库
         private void insertDB(){
             object[] insertObject = new object[aList.Count];
             for (int i = 0; i < aList.Count; i++)
             {
                 insertObject[i] = aList[i];
             }
             // 操作数据库
             int result = moneyBLL.updateAccount(insertObject, null);
             if (result > 0)
             {
                 ClientScript.RegisterStartupScript(this.GetType(), "myscript", "<script type='text/javascript'>alert('导入成功。');</script> ");
             }
             else
             {
                 ClientScript.RegisterStartupScript(this.GetType(), "myscript", "<script type='text/javascript'>alert('导入失败。');</script> ");
             }
         }