In the actual development application, the database import export is a common problem, especially the import and export between database and Excel file, and the problem of inconsistent data type. For example: the number of databases in the long time will be formatted in Excel in the scientific notation format, or the record content is a mixture of numbers and characters will be lost content and so on. You can avoid these problems by importing the contents of an Access database directly into Excel.
The following example is the implementation of this function, the database used in the example of the ASP.net 2.0 application development technology in the book with the database as an example.
In addition, you need to note that Excel files have many limitations, if the database records a lot of content, but also calculate the number of each export and the number of sheet, in addition, the same sheet name of the monitoring also omitted, the need for the reader to add up according to the circumstances. It is better to combine the paging function of stored procedure.
C#:
<%@ Page language= "C #"%>
<! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<script runat= "Server" >
protected void Button1_Click (object sender, EventArgs e)
{
String sql;
string connstr = @ "Provider=Microsoft.Jet.OLEDB.4.0; Data source=| datadirectory| Aspnet20book.mdb; Persist security Info=true ";
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection (CONNSTR);
System.Data.OleDb.OleDbCommand cmd;
cn. Open ();
First get the number of records:
sql = "Select Count (*) from paging";
cmd = new System.Data.OleDb.OleDbCommand (SQL, CN);
int RecordCount = (int) cmd. ExecuteScalar ();
TODO: Calculates the number of sheet, makes a record segment, and imports different data segments into different sheet (sheet number is not known to be limited: ())
TODO: filename, sheet name detected slightly
You can save up to 65,536 records per sheet.
sql = @ "SELECT top 65535 * to [Excel 8.0;database=" + Server.MapPath (".") + @ "Aspnet20book.xls]. [Sheet1] from paging ";
cmd = new System.Data.OleDb.OleDbCommand (SQL, CN);
Cmd. ExecuteNonQuery ();
cn. Close ();
cn. Dispose ();
cn = NULL;
}
</script>
<title> Import an Access database directly into an Excel file </title>
<body>
<form id= "Form1" runat= "Server" >
<asp:button id= "Button1" runat= "Server" onclick= "Button1_Click" text= "Everywhere Data" >
</form>
</body>
Vb. NET:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim sql As String
Dim connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|ASPNET20Book.mdb;Persist Security Info=True"
Dim cn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(connstr)
Dim cmd As System.Data.OleDb.OleDbCommand
cn.Open
sql = "select Count(*) From Paging"
cmd = New System.Data.OleDb.OleDbCommand(sql, cn)
Dim RecordCount As Integer = CType(cmd.ExecuteScalar, Integer)
sql = "select top 65535 * into [Excel 8.0;database=" + Server.MapPath(".") + "ASPNET20Book.xls].[Sheet1] from Paging"
cmd = New System.Data.OleDb.OleDbCommand(sql, cn)
cmd.ExecuteNonQuery
cn.Close
cn.Dispose
cn = Nothing
End SubStart building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
