读取excel文件

最新推荐文章于 2024-07-20 21:05:32 发布

benliliu

最新推荐文章于 2024-07-20 21:05:32 发布

阅读量1.3k

点赞数

分类专栏：我的学习笔记:C# 文章标签： excel string exception null properties database

本文链接：https://blog.csdn.net/benliliu/article/details/1788323

版权

我的学习笔记:C# 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

1、读取.xls中所有sheet的name

public static string[] GetSheetNames(string filePath)
        {
            List<string> sheetNames=new List<string>();
            OleDbConnection objConn = null;
            DataTable dt = null;
            try
            {
                String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                    "Data Source='" + filePath + "';Extended Properties=Excel 8.0;";
                objConn = new OleDbConnection(connString);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                foreach (DataRow dr in dt.Rows)
                {
                    sheetNames.Add(dr["TABLE_NAME"].ToString());
                }
                return sheetNames.ToArray();
            }
            catch (Exception ex)
            {
                throw;
            }
        }

2、将单个sheet插入数据库对应表中

public static void InsertXLSToDB(SqlConnection sqlConn,string filePath, string sheetName,string tableName)
        {
            string strSql = "INSERT [" + tableName + "] SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + filePath + ",[" + sheetName + "];";
            try
            {
                SqlCommand sqlComm = new SqlCommand(strSql, sqlConn);
                sqlComm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw;
            }
        }