Excel导入数据库 数据库是SQL

Question

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;namespace CSASPNETExcelImportExport
{
    public partial class ExcelImport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {        }        protected int GetRowCounts()
        {
            int iRowCount = 0;            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YYConnectionString"].ToString()))
            {
                SqlCommand cmd = new SqlCommand("select count(*) from HRVacationType", conn);
                conn.Open();
                iRowCount = (int)cmd.ExecuteScalar();
            }            return iRowCount;
        }        protected DataTable RetrieveData(string strConn)
        {
            DataTable dtExcel = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                OleDbDataAdapter da = new OleDbDataAdapter("select * from HRVacationType", conn);
                da.Fill(dtExcel);
            }
            return dtExcel;
        }        protected void SqlBulkCopyImport(DataTable dtExcel)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YYConnectionString"].ToString()))
            {
                conn.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.DestinationTableName = "dbo.HRVacationType";
                    foreach (DataColumn dc in dtExcel.Columns)
                    {
                        // 由于 Excel 的列不一定完全等于SQL Server 中表的列，所以我们需要映射列名相同的列
                        bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                    }
                    bulkCopy.WriteToServer(dtExcel);
                }
            }
        }        protected void btnImport_Click(object sender, EventArgs e)
        {
            if (fupExcel.HasFile)
            {
                string strFileName = Server.HtmlEncode(fupExcel.FileName);                string strExtension = Path.GetExtension(strFileName);                if (strExtension != ".xls" && strExtension != ".xlsx")
                {
                    Response.Write("<script>alert('请选择正确的Excel文件!');</script>");
                    return;
                }                string strUploadFileName = "~/UploadFiles/" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;
                fupExcel.SaveAs(Server.MapPath(strUploadFileName));
                string strExcelConn = "";
                if (strExtension == ".xls")
                {
                    // Excel 97-2003
                    strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties='Excel 8.0;HDR=YES;'";
                }
                else
                {
                    // Excel 2007
                    strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                }
                DataTable dtExcel = RetrieveData(strExcelConn);
                int iStartCount = GetRowCounts();
                SqlBulkCopyImport(dtExcel);
                int iEndCount = GetRowCounts();
                lblMessages.Text = Convert.ToString(iEndCount - iStartCount) + " 行数据成功被导入到数据库！";
                if (rblArchive.SelectedValue == "No")
                {
                    File.Delete(Server.MapPath(strUploadFileName));
                }
            }
        }
    }
}Microsoft Office Access 数据库引擎找不到对象“HRVacationType”。请确定该对象存在，并正确拼写其名称和路径名。哪里错了

Answer 1

WEB.config  这么写的
 <connectionStrings>
    <add name="YYConnectionString" connectionString="Data Source=.;Initial Catalog=YY;User ID=sa;Password=1"
  providerName="System.Data.SqlClient" />
</connectionStrings>

Answer 2

protected DataTable RetrieveData(string strConn)
  {
  DataTable dtExcel = new DataTable();
  using (OleDbConnection conn = new OleDbConnection(strConn))
  {
  OleDbDataAdapter da = new OleDbDataAdapter("select * from [HRVacationType$]", conn);
  da.Fill(dtExcel);
  }
  return dtExcel;
  }

Answer 3

红色部分名称要跟Excel文件工作表名称一样