csharp read excel file get sheetName list

简介: /// <summary> /// /// 塗聚文 /// 20120803 /// Geovin Du ///找到EXCEL的工作表名称 要考慮打開的文件的進程問題 /// </summary> /// <p
/// <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;
             }
                 
         }

目录
相关文章
|
6月前
|
存储 Java
惊呆了!这些Java List竟然藏着这么多秘密!你get到了吗?
【6月更文挑战第17天】Java中的ArrayList在添加元素时自动扩容,容量翻倍以适应增长;LinkedList则利用双向链表结构提供高效头尾操作。迭代List时,并发修改会导致`ConcurrentModificationException`,需用Iterator或并发集合如CopyOnWriteArrayList。了解这些秘密能优化性能并避免异常。
23 0
Excel读取并数据List/Map-POI
Excel读取并数据List/Map-POI
|
7月前
|
Python
Python小姿势 - 5 tips to get the most out of list comprehensions in Python
Python小姿势 - 5 tips to get the most out of list comprehensions in Python
|
Python
Python无法打开.xlsx文件:xlrd.biffh.XLRDError: Excel xlsx file; not supported
了解Python无法打开.xlsx文件:xlrd.biffh.XLRDError: Excel xlsx file; not supported。
264 0
Python无法打开.xlsx文件:xlrd.biffh.XLRDError: Excel xlsx file; not supported
pandas读excel类型文件报错: xlrd.biffh.XLRDError: Excel xlsx file; not supported
pandas读excel类型文件报错: xlrd.biffh.XLRDError: Excel xlsx file; not supported
Python+Excel:xlrd.biffh.XLRDError: Excel xlsx file; not supported,两种解决方案
Python+Excel:xlrd.biffh.XLRDError: Excel xlsx file; not supported,两种解决方案
348 0
Python+Excel:xlrd.biffh.XLRDError: Excel xlsx file; not supported,两种解决方案
成功解决TypeError: read_excel() got an unexpected keyword argument ‘parse_cols or ‘sheetname‘
成功解决TypeError: read_excel() got an unexpected keyword argument ‘parse_cols or ‘sheetname‘
|
Python
Python xlrd将同一个excel表的工作簿sheet拆分成多个以sheetname命名的xlsx表
Python xlrd将同一个excel表的工作簿sheet拆分成多个以sheetname命名的xlsx表
307 0