C#操作EXCEL

最新推荐文章于 2025-08-15 12:37:27 发布

某些人

最新推荐文章于 2025-08-15 12:37:27 发布

阅读量595

点赞数 1

分类专栏： C#

C# 专栏收录该内容

39 篇文章

订阅专栏

#region 找到Excel的所有Sheetname
        /// <summary>
        /// 找到Excel的所有Sheetnames
        /// </summary>
        /// <returns>返回Sheetnames</returns>
        private List<string> ExcelSheetName()
        {
            string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txt_Filepath.Text + ";Extended Properties=Excel 8.0;";
            List<string> sheetNames = new List<string>();
            using (OleDbConnection con = new OleDbConnection(conString))
            {
                con.Open();
                System.Data.DataTable sheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });
                con.Close();
                foreach (DataRow var in sheetName.Rows)
                {
                    sheetNames.Add(var[2].ToString());
                }
            }
            return sheetNames;
        }

#endregion

#region 连接Excel 读取Excel数据并返回DataSet数据集合
/// <summary>
/// 连接Excel 读取Excel数据并返回DataSet数据集合
/// </summary>
/// <param name="filepath">Excel服务器路径</param>
/// <param name="tableName">Excel表名称</param>
/// <returns></returns>
public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection ExcelConn = new OleDbConnection(strCon);
try
{
string strCom = string.Format("SELECT * FROM [Sheet1$]");
ExcelConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[" + tableName + "$]");
ExcelConn.Close();
return ds;
}
catch
{
ExcelConn.Close();
return null;
}
}
#endregion