Home >Backend Development >C#.Net Tutorial >C# Get the collection of Sheet page (worksheet) names in the Excel workbook

C# Get the collection of Sheet page (worksheet) names in the Excel workbook

黄舟
黄舟Original
2017-02-18 10:48:444050browse

C# Get the collection of Sheet page (worksheet) names in the Excel workbook

  #region 获取Excel工作薄中Sheet页(工作表)名集合
        /// <summary> 
        /// 获取Excel工作薄中Sheet页(工作表)名集合
        /// </summary> 
        /// <param name="excelFile">Excel文件名及路径,EG:C:\Users\JK\Desktop\导入测试.xls</param> 
        /// <returns>Sheet页名称集合</returns> 
        private String[] GetExcelSheetNames(string fileName)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;
            try
            {
                string connString=string.Empty;
                string FileType =fileName.Substring(fileName.LastIndexOf("."));
                if (FileType == ".xls")  
                 connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
                else//.xlsx
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";  
                // 创建连接对象 
                objConn = new OleDbConnection(connString);
                // 打开数据库连接 
                objConn.Open();
                // 得到包含数据架构的数据表 
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;
                // 添加工作表名称到字符串数组 
                foreach (DataRow row in dt.Rows)
                {
                    string strSheetTableName = row["TABLE_NAME"].ToString();
                    //过滤无效SheetName
                    if (strSheetTableName.Contains("$")&&strSheetTableName.Replace("&#39;", "").EndsWith("$"))
                    {
                        excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
                    }                   
                    i++;
                }
                return excelSheets;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return null;
            }
            finally
            {
                // 清理 
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
        #endregion

The above is C# Get the contents of the collection of Sheet page (worksheet) names in the Excel workbook, more For more related content, please pay attention to the PHP Chinese website (www.php.cn)!


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article:C# Read data in ExcelNext article:C# Read data in Excel