/// <summary> /// /// 塗聚文 /// 20120803 /// Geovin Du ///找到EXCEL的工作表名称 要考慮打開的文件的進程問題 /// </summary> /// <param name="filename"></param> /// <param name="comboBox2"></param> /// <returns></returns> public static System.Data.DataTable getSheetName(string filename, ComboBox comboBox2) { System.Data.DataTable dtSheets = new System.Data.DataTable(); try { dtSheets.Columns.Add("id", typeof(int)); dtSheets.Columns.Add("SheetName", typeof(string)); object objOpt = Missing.Value; Excel.Application excelApp = new Excel.Application(); excelApp.Workbooks.Open(filename, objOpt, objOpt, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt); for (int i = 0; i < excelApp.Workbooks[1].Worksheets.Count; i++) { Excel.Worksheet ws = (Excel.Worksheet)excelApp.Workbooks[1].Worksheets[i + 1]; string sSheetName = ws.Name; dtSheets.Rows.Add(i, ws.Name); } comboBox2.DataSource = dtSheets; comboBox2.DisplayMember = "SheetName"; comboBox2.ValueMember = "id"; comboBox2.AutoCompleteMode = AutoCompleteMode.Suggest; comboBox2.AutoCompleteSource = AutoCompleteSource.ListItems; KillExcelProceed(); Kill(excelApp); } catch (IOException ex) { ex.Message.ToString(); } return dtSheets; } /// <summary> /// /// 塗聚文 締友計算機信息技術有限公司 /// 20120803 /// Geovin Du /// </summary> /// <param name="filename"></param> /// <param name="comboBox2"></param> /// <returns></returns> public static System.Data.DataTable getGeovinDuSheetName(string filename, ComboBox comboBox2) { System.Data.DataTable dtSheets = new System.Data.DataTable(); try { dtSheets.Columns.Add("id", typeof(int)); dtSheets.Columns.Add("SheetName", typeof(string)); 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 DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb"); DbConnection connection = factory.CreateConnection(); connection.ConnectionString = connectionString; connection.Open(); DataTable tbl = connection.GetSchema("Tables"); connection.Close(); int i = 0; foreach (DataRow row in tbl.Rows) { string sheetName = (string)row["TABLE_NAME"]; if (sheetName.EndsWith("$")) { sheetName = sheetName.Substring(0, sheetName.Length - 1); } //繁體系統需要此操作,簡體的不需要也可以 sheetName = sheetName.Replace("$", ""); sheetName = sheetName.Replace("'", ""); dtSheets.Rows.Add(i,sheetName.Replace("$", "")); i++; } comboBox2.DataSource = dtSheets; comboBox2.DisplayMember = "SheetName"; comboBox2.ValueMember = "id"; comboBox2.AutoCompleteMode = AutoCompleteMode.Suggest; comboBox2.AutoCompleteSource = AutoCompleteSource.ListItems; return dtSheets; } catch (IOException ex) { ex.Message.ToString(); return null; } }