关闭

读取excel文件

标签: excelstringexceptionnullpropertiesdatabase
364人阅读 评论(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;
            }
        }

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:38500次
    • 积分:590
    • 等级:
    • 排名:千里之外
    • 原创:19篇
    • 转载:10篇
    • 译文:0篇
    • 评论:6条
    最新评论