.NET操作Excel筆記

2019-11-14 16:45:58

字體：大中小

來源：轉載

供稿：網友

如果你新建一個項目的話，首先要添加Microsoft.Office.Core 與Microsoft.Office.Interop.Exce這兩個應用，然后就能很方便的操作了，示例代碼（只實現了簡單的讀寫）：

1PRivateExcel._application excelApp; 
2privateWorkbook wbclass; 
3   
4excelApp = newExcel.Application(); 
5   
6objectobjOpt = System.Reflection.Missing.Value; 
7               
8wbclass = (Workbook)excelApp.Workbooks.Open("E:/Book6.xlsx", objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);

上面聲明，引用，并把要操作的 excel 的路徑傳給他

得到所有的表名：

1List<string> list = new List<string>();
2           Excel.Sheets sheets = wbclass.Worksheets;
3           string sheetNams = string.Empty;
4           foreach (Excel.Worksheet sheet in sheets)
5           {
6               list.Add(sheet.Name);
7           }

獲取某個表中的數據，這里獲取的是sheet 表中的：

01publicExcel.Worksheet GetWorksheetByName(string name) 
02        { 
03            Excel.Worksheet sheet = null; 
04            Excel.Sheets sheets = wbclass.Worksheets; 
05            foreach (Excel.Worksheet s in sheets) 
06            { 
07                if(s.Name == name) 
08                { 
09                    sheet = s; 
10                    break; 
11                } 
12            } 
13            returnsheet; 
14        } 
15   
16        publicSystem.Data.DataTable GetDateTable(string name) 
17        { 
18            System.Data.DataTable dt = newSystem.Data.DataTable(); 
19   
20            var worksheet = GetWorksheetByName(name);      //調用上面的方法，利用表名得到這張表 
21   
22            string cellContent; 
23   
24            intiRowCount = worksheet.UsedRange.Rows.Count; 
25            intiColCount = worksheet.UsedRange.Columns.Count; 
26            Excel.Range range; 
27            for (intiRow = 1; iRow <= iRowCount; iRow++) 
28            { 
29                DataRow dr = dt.NewRow(); 
30   
31                for (intiCol = 1; iCol <= iColCount; iCol++) 
32                { 
33                    range = (Excel.Range)worksheet.Cells[iRow, iCol]; 
34   
35                    cellContent = (range.Value2 == null) ? "": range.Text.ToString(); 
36   
37                    if(iRow == 1) 
38                    { 
39                        dt.Columns.Add(cellContent); 
40                    } 
41                    else 
42                    { 
43                        dr[iCol - 1] = cellContent; 
44                    } 
45                } 
46   
47                if(iRow != 1) 
48                    dt.Rows.Add(dr); 
49            } 
50   
51            returndt; 
52   
53        }

上面得到的只是

1System.Data.DataTable

如何把數據取出來，請看下面：

1var dataTable = GetDateTable("Sheet1");   //調用上面的方法 
2           
3           foreach (DataRow row indataTable.Rows) 
4           {      
5               stringa = (string)row[2]; 
6               stringb = (string)row[4]; 
7 
8}