In the morning, a function was added to the order management part of the system to export the queried order information to Excel for the administrator to analyze. The previously written code is as follows:
Response.Clear();
Response.BufferOutput = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename = FileName.xls");
Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
System.Globalization.CultureInfo myinfo = new System.Globalization.CultureInfo("ZH-CN", true);
StringWriter osw = new StringWriter(myinfo);
HtmlTextWriter ohtw = new HtmlTextWriter(osw);
dgQueryResult.RenderControl(ohtw);
Response.Write(osw);
Response.End();
My query used paging, and the customer required that all the queried data be exported to Excel. Therefore, this function could not be achieved by exporting the content of the control to Excel as before, so I changed the code to this:
Response.Clear();
Response.BufferOutput = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename = order.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel";
string strExcelHeader = string.Empty;
string strExcelItems;
if(ViewState["SQL"] != null) // Get the SQL statement used in the previous query
{
// Get the titles of each column, separate each title with t, and add a carriage return character after the last column title
strExcelHeader = "Order numbertDealertRegiontSmall categorytCommoditytAmount paidtOrder timetValid statustProcessing statustn";
//Write the obtained data information to the HTTP output stream
Response.Write(strExcelHeader);
// Process query result data row by row
ITDBHandle itDbHandle = new ITDBHandle();
itDbHandle.QueryString = ViewState["SQL"].ToString();
SqlDataReader reader = itDbHandle.ExecuteDataReader();
while(reader.Read())
{
strExcelItems = string.Empty;
strExcelItems += reader["OrderID"].ToString() + "t";
strExcelItems += reader["DealerName"].ToString() + "t";
strExcelItems += reader["City"].ToString() + "t";
strExcelItems += reader["SmallClassName"].ToString() + "t";
strExcelItems += reader["BrandName"].ToString() + reader["Model"].ToString() + "t";
strExcelItems += reader["TotalPrice"].ToString() + "t";
strExcelItems += reader["OrderDate"].ToString() + "t";
strExcelItems += reader["IsValid"].ToString() + "t";
strExcelItems += reader["DealState"].ToString() +"n";
Response.Write(strExcelItems);
}
reader.Close();
Response.End();
}
Here I simply write the queried data in the form of Excel (of course it can also be in other formats, such as XML), which is enough for simple needs. Of course, it is also very simple to use .NET to directly operate Excel files. , there are many such examples for reference.