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 }
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)成功.---Geovin Du(涂聚文）