如何使用ASP.NET从SQL Server Table中导出Excel数据?

问题描述：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="samp.aspx.cs" Inherits="samp._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td><span style="color:#FF0000">*</span>Attach Excel File</td>
                <td><asp:FileUpload ID="fileuploadExcel" runat="server" /></td>
            </tr>
            <tr>
                <td></td>
                <td><asp:Button ID="btnSend" runat="server" Text="Export" OnClick="btnSend_click"></asp:Button></td>
            </tr>
        </table>
      <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </div>
    </form>
</body>
</html>

背后的代码是

Code Behind is

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace samp
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btnSend_click(object sender, EventArgs e)
        {
            String strConnection = "Data Source=MANIADSBHI-PC;Initial Catalog=master;Integrated Security=True";
            //file upload path
            string path = fileuploadExcel.PostedFile.FileName;
            //Create connection string to Excel work book
            string excelConnectionString = @"Provider=SQLOLEDB.1;Data Source=" + path + ";Extended Properties=Excel 7.0;Persist Security Info=True";
            //Create Connection to Excel work book
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
            //Create OleDbCommand to fetch data from Excel
            OleDbCommand cmd = new OleDbCommand("Select [sno],[fname],[lname],[mobnum],[city],[zip] from emp [Sheet1$]", excelConnection);
            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();
            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            //Give your Destination table name
            sqlBulk.DestinationTableName = "emp";
            sqlBulk.WriteToServer(dReader);
            excelConnection.Close();
        }
    }
}

现在我在第30行后面的代码上得到了错误...即:-excelCOnnenction.open();
请任何人解决该错误..

Now i got the error on code behind line no:30... ie:- excelCOnnenction.open();
Pls anyone solve the error..

答

"，excelConnection)； excelConnection.Open(); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = 新 SqlBulkCopy(strConnection); // 提供目标表名称 sqlBulk.DestinationTableName = " ; sqlBulk.WriteToServer(dReader); excelConnection.Close(); } } }

", excelConnection); excelConnection.Open(); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); //Give your Destination table name sqlBulk.DestinationTableName = "emp"; sqlBulk.WriteToServer(dReader); excelConnection.Close(); } } }

现在我在第30行后面的代码上得到了错误...即:-excelCOnnenction.open();
请任何人解决该错误..

Now i got the error on code behind line no:30... ie:- excelCOnnenction.open();
Pls anyone solve the error..

我认为您为excel连接字符串指定了错误的提供程序.

Excel连接通常使用ACE或JET提供程序进行.

尝试将您的连接字符串更改为以下内容:

I think you have specified an incorrect Provider for your excel connectionstring.

Excel connections are usually made use an ACE or JET provider.

Try changing your connectionstring to something like:

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0";

或

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";

希望对您有所帮助.

Hope it helps.

String strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Server.MapPath("your .xlsx file path;") + ";"
+ "Extended Properties=''Excel 12.0;HDR=Yes''";

祝你好运

Good Luck

如何使用ASP.NET从SQL Server Table中导出Excel数据?

相关推荐