csharp read excel file get sheetName list

复制代码
 1         /// <summary>
 2         ///      
 3         /// 塗聚文
 4         /// 20120803
 5         /// Geovin Du
 6         ///找到EXCEL的工作表名称 要考慮打開的文件的進程問題
 7         /// </summary>
 8         /// <param name="filename"></param>
 9         /// <param name="comboBox2"></param>
10         /// <returns></returns>
11         public static System.Data.DataTable getSheetName(string filename, ComboBox comboBox2)
12         {
13             System.Data.DataTable dtSheets = new System.Data.DataTable();
14             try
15             {
16                 
17                 dtSheets.Columns.Add("id", typeof(int));
18                 dtSheets.Columns.Add("SheetName", typeof(string));            
19                 object objOpt = Missing.Value;
20                 Excel.Application excelApp = new Excel.Application();
21                 excelApp.Workbooks.Open(filename, objOpt, objOpt, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
22                 for (int i = 0; i < excelApp.Workbooks[1].Worksheets.Count; i++)
23                 {
24                     Excel.Worksheet ws = (Excel.Worksheet)excelApp.Workbooks[1].Worksheets[i + 1];
25                     string sSheetName = ws.Name;
26                     dtSheets.Rows.Add(i, ws.Name);
27                 }
28                 comboBox2.DataSource = dtSheets;
29                 comboBox2.DisplayMember = "SheetName";
30                 comboBox2.ValueMember = "id";
31                 comboBox2.AutoCompleteMode = AutoCompleteMode.Suggest;
32                 comboBox2.AutoCompleteSource = AutoCompleteSource.ListItems;
33                 KillExcelProceed();
34                 Kill(excelApp);
35             }
36             catch (IOException ex)
37             {
38                 ex.Message.ToString();
39             }
40             return dtSheets;
41         }
42         /// <summary>
43         ///      
44         /// 塗聚文 締友計算機信息技術有限公司
45         /// 20120803
46         /// Geovin Du
47         /// </summary>
48         /// <param name="filename"></param>
49         /// <param name="comboBox2"></param>
50         /// <returns></returns>
51         public static System.Data.DataTable getGeovinDuSheetName(string filename, ComboBox comboBox2)
52         {
53             System.Data.DataTable dtSheets = new System.Data.DataTable();
54             
55 
56             try
57             {
58 
59                 dtSheets.Columns.Add("id", typeof(int));
60                 dtSheets.Columns.Add("SheetName", typeof(string));
61                 string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", filename); //高版本用:Microsoft.ACE.OLEDB.12.0
62                 DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
63                 DbConnection connection = factory.CreateConnection();
64                 connection.ConnectionString = connectionString;
65                 connection.Open();
66                 DataTable tbl = connection.GetSchema("Tables");
67                 connection.Close();
68                 int i = 0;
69                 foreach (DataRow row in tbl.Rows)
70                 {
71                     string sheetName = (string)row["TABLE_NAME"];
72                     if (sheetName.EndsWith("$"))
73                     {
74                         sheetName = sheetName.Substring(0, sheetName.Length - 1);
75                     }
76                     //繁體系統需要此操作,簡體的不需要也可以
77                     sheetName = sheetName.Replace("$", "");
78                     sheetName = sheetName.Replace("'", "");
79                     dtSheets.Rows.Add(i,sheetName.Replace("$", ""));
80                     i++;
81                 }
82                 comboBox2.DataSource = dtSheets;
83                 comboBox2.DisplayMember = "SheetName";
84                 comboBox2.ValueMember = "id";
85                 comboBox2.AutoCompleteMode = AutoCompleteMode.Suggest;
86                 comboBox2.AutoCompleteSource = AutoCompleteSource.ListItems;
87 
88                 return dtSheets;
89             }
90             catch (IOException ex)
91             {
92                 ex.Message.ToString();
93                 return null;
94             }
95                 
96         }
复制代码

 

posted @   ®Geovin Du Dream Park™  阅读(711)  评论(0编辑  收藏  举报
编辑推荐:
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
阅读排行:
· 25岁的心里话
· 闲置电脑爆改个人服务器(超详细) #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· 零经验选手,Compose 一天开发一款小游戏!
· 一起来玩mcp_server_sqlite,让AI帮你做增删改查!!
< 2012年8月 >
29 30 31 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示