ASP.NET 导入导出原始规格的 Excel 文件
2011-01-06 10:35 音乐让我说 阅读(1143) 评论(5) 编辑 收藏 举报代码胜过一切!
导出到 Excel 文件:
using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.Configuration; namespace CSASPNETExcelImportExport { public partial class ExcelExport : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected DataTable RetrieveData() { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ToString())) { SqlDataAdapter da = new SqlDataAdapter("select Id,typeName,typeDesc from HRVacationType", conn); da.Fill(dt); } return dt; } protected void ExportToExcel(string strConn, DataTable dtSQL) { using (OleDbConnection conn = new OleDbConnection(strConn)) { OleDbCommand cmd = new OleDbCommand("create table HRVacationType(Id int, typeName varchar(50),typeDesc varchar(50))", conn); conn.Open(); cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO HRVacationType (Id,typeName, typeDesc) values (?,?,?)"; cmd.Parameters.Add("Id", OleDbType.Integer, 4, "Id"); cmd.Parameters.Add("typeName", OleDbType.VarChar, 50, "typeName"); cmd.Parameters.Add("typeDesc", OleDbType.VarChar, 50, "typeDesc"); OleDbDataAdapter da = new OleDbDataAdapter("select * from HRVacationType", conn); da.InsertCommand = cmd; foreach (DataRow dr in dtSQL.Rows) { dr.SetAdded(); } da.Update(dtSQL); } } protected void btnExport_Click(object sender, EventArgs e) { string strDownloadFileName = ""; string strExcelConn = ""; if (rblExtension.SelectedValue == "2003") { // Excel 97-2003 strDownloadFileName = "~/DownloadFiles/" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strDownloadFileName) + ";Extended Properties='Excel 8.0;HDR=Yes'"; } else { // Excel 2007 strDownloadFileName = "~/DownloadFiles/" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(strDownloadFileName) + ";Extended Properties='Excel 12.0 Xml;HDR=Yes'"; } DataTable dtSQL = RetrieveData(); ExportToExcel(strExcelConn, dtSQL); if (rblDownload.SelectedValue == "Yes") { hlDownload.Visible = true; hlDownload.Text = "Click here to download file."; hlDownload.NavigateUrl = strDownloadFileName; } else { hlDownload.Visible = false; } } } }
导入 Excel 数据到 SQL Server :
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["SQLServer2005DBConnectionString"].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["SQLServer2005DBConnectionString"].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)); } } } } }
成功导出到Excel 的截图:
插入到SQL Server成功前的截图:
插入到SQL Server成功后的截图:
谢谢浏览!
下载 Demo!
https://files.cnblogs.com/Music/CSASPNETExcelImportExport.rar
作者:音乐让我说(音乐让我说 - 博客园)
出处:http://music.cnblogs.com/
文章版权归本人所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。