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));
                }
            }
        }
    }
}