Use ADO.NET to convert data to Excel format and provide download (transfer)

Objective To make a customer for some reason, we will ask us to export system information as Excel or Access this kind of file format they are familiar. Since the print control of IE is more difficult, we can also consider downloading the Excel file format when we make printing, allowing customers to adjust the format in Excel. For these reasons, we may need to provide data downloads in the Excel file format in the program.

There are many ways to export to Excel files, such as: 1. Use Excel Automation Server to generate. 2. Generate a delimiter file and then use Excel to open, saved as an XLS file. 3. Using the XML file as an intermediate process file, then open using Excel's OpenXML method (requiring Excel2002 or later). 4. Using ADO.NET. The method I have to use here is the fourth, using ADO.NET to convert.

Basic thinking I implemented my plan in such a step: 1. Read the information in SQL Server to DataSet. 2. Create a table with OLEDB (a Workbooks in the Excel file). 3. Insert the DataSet's content into the table just established by loop. 4. Provide the download of the file that has just been generated. 5. Delete the provisional EXCEL file. Here is a problem, which is the naming conflict of the Excel file, I use GUID to generate a unique name.

Sample code is ready to work, I am going to put TEMP in the virtual directory as a 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;

Establish a table structure

Objcmd.commandtext = @ "CREATE TABLE Customer Information (Customer Name VARCHAR, Registration Time Varchar);

Objcmd.executenonquery ();

Insert new data

// Establish a commandobjcmd.commandtext = "INSERT INTO Customer Data (Customer Name, Birthday) VALUES (@customernameter (" @ customernameter) "; objcmd.parameters.add (" @ Customernameter)) Objcmd.Parameters.Add (New OledbParameter ("@registerTime", OLEDBTYPE.VARCHAR);

// Traversing DataSet Insert Data into the newly built Excel file, Customerinfo reads the data read from the database (DataRow Row In CustomerInfo.Tables [0] .ROWS) {for (int i = 0; i

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 the temporary file response.end ();