ASP.NET中三種操作Excel的方法

方法一 採用OleDB操作Excel檔案(自己也寫過支援全系列Excel，比較下)：
①讀取Excel,把Excel檔案當作資料來源，執行個體如下：

　　   public DataSet ExcelToDS(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
            return ds;
        }

對於Excel中的表即sheet([sheet1$])如果不是固定的可以使用下面的方法得到：

　　　string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
        string tableName = schemaTable.Rows[0][2].ToString().Trim();

②寫入Excel,執行個體如下：

　　　public void DSToExcel(string Path, DataSet oldds)
        {
            //先得到匯總Excel的DataSet 主要目的是獲得Excel在DataSet中的結構
            string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + path1 + ";Extended Properties=Excel 8.0";
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = "select * from [Sheet1$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ystem.Data.OleDb.OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
            //QuotePrefix和QuoteSuffix主要是對builder產生InsertComment命令時使用。
            builder.QuotePrefix = "[";     //擷取insert語句中保留字元（起始位置）
            builder.QuoteSuffix = "]"; //擷取insert語句中保留字元（結束位置）
            DataSet newds = new DataSet();
            myCommand.Fill(newds, "Table1");
            for (int i = 0; i < oldds.Tables[0].Rows.Count; i++)
            {
                //在這裡不能使用ImportRow方法將一行匯入到news中，
                //因為ImportRow將保留原來DataRow的所有設定(DataRowState狀態不變)。
                //在使用ImportRow後newds內有值，但不能更新到Excel中因為所有匯入行的DataRowState!=Added
                DataRow nrow = aDataSet.Tables["Table1"].NewRow();
                for (int j = 0; j < newds.Tables[0].Columns.Count; j++)
                {
                    nrow[j] = oldds.Tables[0].Rows[i][j];
                }
                newds.Tables["Table1"].Rows.Add(nrow);
            }
            myCommand.Update(newds, "Table1");
            myConn.Close();
        }

方法二 使用Microsoft.Office.Interop.Excel.dll讀取Excel檔案：

　　  //讀取EXCEL的方法    (用範圍地區讀取資料)
        private void OpenExcel(string strFileName)
        {
            object missing = System.Reflection.Missing.Value;
            Application excel = new Application();//lauch excel application
            if (excel == null)
            {
                Response.Write("<script>alert('Can't access excel')</script>");
            }
            else
            {
                excel.Visible = false; excel.UserControl = true;
                // 以唯讀形式開啟EXCEL檔案
                Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
                 missing, missing, missing, true, missing, missing, missing, missing, missing);
                //取得第一個工作薄
                Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);
                //取得總記錄行數    (包括標題列)
                int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行數
                //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列數
                //取得資料範圍地區   (不包括標題列) 
                Range rng1 = ws.Cells.get_Range("B2", "B" + rowsint);   //item
                Range rng2 = ws.Cells.get_Range("K2", "K" + rowsint);  //Customer
                object[,] arryItem = (object[,])rng1.Value2;   //get range's value
                object[,] arryCus = (object[,])rng2.Value2;
                //將新值賦給一個數組
                string[,] arry = new string[rowsint - 1, 2];
                for (int i = 1; i <= rowsint - 1; i++)
                {
                    //Item_Code列
                    arry[i - 1, 0] = arryItem[i, 1].ToString();
                    //Customer_Name列
                    arry[i - 1, 1] = arryCus[i, 1].ToString();
                }
                Response.Write(arry[0, 0] + "   /   " + arry[0, 1] + "#" + arry[rowsint - 2, 0] + "   /   " + arry[rowsint - 2, 1]);
            }
            excel.Quit(); excel = null;
            Process[] procs = Process.GetProcessesByName("excel");
            foreach (Process pro in procs)
            {
                pro.Kill();//沒有更好的方法,只有殺掉進程
            }
            GC.Collect();
        }

方法三 將Excel檔案轉化成CSV（逗號分隔）的檔案，用檔案流讀取(等價就是讀取一個txt文字檔)：

FileStream fs = new FileStream("d:\\Customer.csv", FileMode.Open, FileAccess.Read, FileShare.None);
               StreamReader sr = new StreamReader(fs, System.Text.Encoding.GetEncoding(936));
               string str = "";
               string s = Console.ReadLine();
               while (str != null)
               {     str = sr.ReadLine();
                   string[] xu = new String[2];
                    xu = str.Split(',');
                   string ser = xu[0];
                   string dse = xu[1];                if (ser == s)
                    {   Console.WriteLine(dse);break;
                    }
               }
               sr.Close();