读取Excel数据

 1/**//// <summary>
 2        /// 将指定Excel文件中的数据转换成DataTable对象，供应用程序进一步处理
 3        /// </summary>
 4        /// <param name="filePath"></param>
 5        /// <returns></returns>
 6        public static System.Data.DataTable Import(string filePath)
 7        {
 8            System.Data.DataTable rs = new System.Data.DataTable();
 9            bool canOpen=false;
10            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+"Data Source=" + 
11
12filePath + ";" + "Extended Properties=\"Excel 8.0;\"");
13            try//尝试数据连接是否可用
14            {
15                conn.Open();
16                conn.Close();
17                canOpen=true;
18            }
19            catch{}
20            //文件可以打开
21            if(canOpen)
22            {
23                try//如果数据连接可以打开则尝试读入数据
24                {
25                    OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]",conn);
26                    OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
27                    myData.Fill(rs);
28                    conn.Close();
29                }
30                catch//如果数据连接可以打开但是读入数据失败，则从文件中提取出工作表的名称，再读入数据
31                {
32                    string sheetName=GetSheetName(filePath);
33                    if(sheetName.Length>0)
34                    {
35                        OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM 
36
37["+sheetName+"$]",conn);
38                        OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
39                        myData.Fill(rs);
40                        conn.Close();
41                    }
42                }
43            }
44            else
45            {
46                System.IO.StreamReader tmpStream=File.OpenText(filePath);
47                string tmpStr=tmpStream.ReadToEnd();
48                tmpStream.Close();
49                rs=GetDataTableFromString(tmpStr);
50                tmpStr="";
51            }
52            return rs;
53        } 

 1private void OnLoad(object sender, System.EventArgs e)
 2        {
 3            // 在此处放置用户代码以初始化页面
 4            Excel.Application xApp=new Excel.ApplicationClass(); 
 5
 6            xApp.Visible=true; 
 7            //得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件 
 8            Excel.Workbook xBook=xApp.Workbooks._Open(@"D:\Sample.xls", 
 9                Missing.Value,Missing.Value,Missing.Value,Missing.Value 
10                ,Missing.Value,Missing.Value,Missing.Value,Missing.Value 
11                ,Missing.Value,Missing.Value,Missing.Value,Missing.Value); 
12            //xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码 
13            //指定要操作的Sheet，两种方式： 
14
15            Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1]; 
16            //Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet; 
17
18            //读取数据，通过Range对象 
19            Excel.Range rng1=xSheet.get_Range("A1",Type.Missing); 
20            Console.WriteLine(rng1.Value2); 
21
22            //读取，通过Range对象，但使用不同的接口得到Range 
23            Excel.Range rng2=(Excel.Range)xSheet.Cells[3,1]; 
24            Console.WriteLine(rng2.Value2); 
25
26            //写入数据 
27            Excel.Range rng3=xSheet.get_Range("C6",Missing.Value); 
28            rng3.Value2="Hello"; 
29            rng3.Interior.ColorIndex=6; //设置Range的背景色 
30
31            //保存方式一：保存WorkBook 
32            xBook.SaveAs(@"D:\CData.xls", 
33                Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value, 
34                Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value, 
35                Missing.Value,Missing.Value); 
36
37            //保存方式二：保存WorkSheet 
38            xSheet.SaveAs(@"D:\CData2.xls", 
39                Missing.Value,Missing.Value,Missing.Value,Missing.Value, 
40                Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value); 
41
42
43            //保存方式三 
44            xBook.Save(); 
45
46            xSheet=null; 
47            xBook=null; 
48            xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出 
49            xApp=null; 
50        }