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();