Import the Access database directly to an Excel file in ASP. NET 2.0

In actual development applications, Database Import and Export are common problems. In particular, the import and export between databases and Excel files still have inconsistent data types. For example, when the number of a database is too long, it will be formatted in Excel as a scientific notation, or the recorded content is a mixture of numbers and characters, and the content will be lost. You can avoid these problems by importing the Access database content directly to Excel.

The following example shows how to implement this function. In this example, the database in ASP. NET 2.0 application development technology is used as an example.

In addition, you must note that there are many restrictions on Excel files. If the database records a lot of content, you need to calculate the number of exported data and the number of Sheet records each time. In addition, monitoring of the same Sheet name is also omitted, if you need it, add it as needed. It is better to combine the paging function of the stored procedure.

C #:

&lt;% @ Page Language = "C #" %&gt; &lt;! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.0 Transitional // EN "" http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd "&gt; <ptml xmlns =" http://www.w3.org/1999/xhtml "&gt; <pead runat =" server "&gt; &lt;title&gt; import the Access database directly to an Excel file &lt;/title&gt; </pead&gt; &lt;body&gt; </ptml&gt;

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 Sub