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”。请确定该对象存在,并正确拼写其名称和路径名。哪里错了
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”。请确定该对象存在,并正确拼写其名称和路径名。哪里错了
解决方案 »
- 学习mvc环境求助
- 把excel倒进SQL里
- Unexpected end of file while parsing name has occurred 是什么错误?怎样解决!
- .NET怎么实现注册用户的时候发送邮件要求确认?
- 控制asp。net中控件textbox的位置的做法是什么?
- |M| 一个Object对像 他可能是Button 也可能是 ImageButton 也可能是 LinkButton 我要怎么知道他是哪一种呢
- 有一个问题困扰着我,一直没有得到答案?希望使用过的人能指点迷津!
- 高手请进!jsp与asp.net共享cookie的问题!
- 打开方案为何改不了里面的程序.
- 请问怎么做象MS的Spaces和Hotmail里的自定义弹出对话框的样式???
- 怎么用js判断DataList里面的checkbox是否选中?
- 怎么实现定时修改数据库?
<connectionStrings>
<add name="YYConnectionString" connectionString="Data Source=.;Initial Catalog=YY;User ID=sa;Password=1"
providerName="System.Data.SqlClient" />
</connectionStrings>
{
DataTable dtExcel = new DataTable();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
OleDbDataAdapter da = new OleDbDataAdapter("select * from [HRVacationType$]", conn);
da.Fill(dtExcel);
}
return dtExcel;
}