1, data fill
First establish the connection object conn, and read the connection configuration information from the global configuration file web.config file via system.appsetting.configuration, and then use the SqlDataAdapter object to search the result and fill the result and populate the DataSet object instance DS. In, then pass the DS to the custom process ExportData process, complete the data export work. code show as below:
SqlConnection conn = new sqlconnection (system.configuration.configurations ["exportData"]);
SqlDataAdapter Da = New SqlDataAdapter (ls_sqlcmd, sqlconnection);
DataSet DS = New DataSet ();
DA.FILL (DS, "ExportDataTable");
ExportData (DS, DDL_DCLX.SelectedValue, "Exportfile." (DDL_DCLX.SelectedValue == "1" "XLS": "XML"));
2, implementation of the exportData process
This process has three parameters: the first is DataSet, passed the query data to be processed; the second is String, identify the file format we exported, the third is also string, specify the name of the export file. The code is as follows:
/ * Define the HTTPRESPONSE object, and initialize it with page.response, specify the output stream HTTP character set to Chinese Simplified, the HTTP header is added to the output stream, specify the result of the results at the end of the data processing * /
HTTPRESPONSE RESP;
Resp = page.response;
Resp.contentencoding = system.text.Encoding.Getencoding ("GB2312");
Resp.Appendheader ("Content-Disposition", "Attachment; FileName =" FileName);
String colheaders = "" ", ls_item =" ";
INT I, J;
/ * Define the table object and the row object, and initialize its value with DataSet * /
DataTable DT = DS.TABLES [0];
DATAROW [] Myrow = DT.SELECT ("");
/ * typeID == "1" exports to an Excel format file; TypeID == "2" exports to XML format file * /
IF (TypeID == "1")
{
/ * Net the title of the data table, and the items are divided between / t, the last column header is returned to the car * /
For (i = 0; i Colheaders = dt.columns [i] .caption.toString () "/ t"; Colheaders = dt.columns [i] .caption.toString () "/ n"; / * Data information written to the HTTP output stream * / Resp.write (colheaders); / * Processing data * / Foreach (DataRow Row In Myrow) { / * In the current line, obtain data one by one, data between / t, end up, and return to the car / N For (i = 0; i LS_Item = row [i] .tostring () "/ t"; LS_Item = row [i] .tostring () "/ n"; / * The current row data is written to the HTTP output stream, and the LS_ITEM is set to down data * / Resp.Write (Ls_Item); ls_item = ""; } } Else { IF (TypeId == "2") { / * Directly export XML data directly from the DataSet and write in the HTTP output stream * / Resp.write (DS.GETXML ()); } } / * The data in the write buffer is in the HTTP header file * / Resp.end (); In the above code, mainly involving three aspects: (1) Set the output stream HTTP file header through the Page.Response object to ensure that the data is displayed in a suitable coded set, and the data output in the data output stream is specified. (2) If you want to export data in DataTable, you must read the data one by one, plus the associated split identifier, write the output stream, and then buffer when executing Page.Response.end () The output stream data is written in the specified file in our request. (3) In Microsoft's .NET strategy, ADO.NET and XML highly integrated, the most important controls for disconnect data processing in ADO.NET are DataSet, which provides seamless support for XML. Database processing and XML integration are the primary targets that DataSet needs to be implemented, so writing data streams in DataSet is a very simple thing, get data in XML format, and then by Page.Response .Write () can write directly to the output stream.
