如何使用ASP.NET从SQL Server Table中导出Excel数据? [英] How to export excel data from SQL server Table using ASP.NET....?
本文介绍了如何使用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";
或
or
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数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
