Use ado.net to convert data to Excel format and provide download

Purpose
Sometimes, for some reason, the customer may ask us to export the system data to excel or access the familiar file format. Due to the difficulty in printing IE, we can also consider downloading the Excel file format when making and printing, so that the customer can adjust the format and print it in Excel. For these reasons, we may needProgramTo download data in the Excel file format.

Channels
There are many ways to export an Excel file, such:
1. Generate it cyclically using Excel Automation server.
2. Generate a delimiter file and open it in Excel. Save it as an xls file.
3. Use the XML file as the intermediate process file, and then open it using the openxml method of Excel (excel2002 or above is required ).
4. Use ado.net.
The fourth method I want to use here is to use ado.net for conversion.

Basic Ideas
Follow these steps to implement my plan:
1. read data from SQL server into dataset.
2. Use oledb to create a table (workbooks in an Excel file ).
3. cyclically Insert the content of dataset into the table just created.
4. Download the generated file.
5. Delete the temporary Excel file.
There is a problem here, that is, the temporary generated Excel File Name Conflict problem. I use guid to generate a unique name.

ExampleCode
Preparations: I want to use temp in the virtual directory as the temporary file directory.

String urlpath = httpcontext. Current. Request. applicationpath + "/temp /";

String physicpath = httpcontext. Current. server. mappath (urlpath );

String filename = guid. newguid () + ". xls ";

String connstring = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + physicpath + filename + "; extended properties = Excel 8.0 ;";

Oledbconnection objconn = new oledbconnection (connstring );
Oledbcommand objcmd = new oledbcommand ();
Objcmd. Connection = objconn;

Create Table Structure

Objcmd. commandtext = @ "create table customer information
(
Customer name varchar,
Registration Time varchar
)
";

Objcmd. executenonquery ();

Insert new data

// Create the INSERT command
Objcmd. commandtext = "insert into customer information (customer name, birthday) values (@ customername, @ registertime )";
Objcmd. Parameters. Add (New oledbparameter ("@ mermername", oledbtype. varchar ));
Objcmd. Parameters. Add (New oledbparameter ("@ registertime", oledbtype. varchar ));

// Traverse dataset and insert the data into the new Excel file. customerinfo is the data we read from the database.
Foreach (datarow row in customerinfo. Tables [0]. Rows)
{
For (INT I = 0; I <parm. length; I ++)
{
Parm [I]. value = row [I];
}
Objcmd. executenonquery ();
}

Download

Httpresponse response = httpcontext. Current. response;
Response. Clear ();
Response. writefile (path + filename );
String httpheader = "attachment; filename = backup. xls ";
Response. appendheader ("content-disposition", httpheader );
Response. Flush ();

System. Io. file. Delete (path + filename); // delete a temporary file
Response. End ();

original address: http://www.cnblogs.com/Meyer/archive/2004/04/21/6977.aspx