Export Database Data to excel

// Go to excel events
Protected void imagebutton2_click (Object sender, imageclickeventargs e) powered by 25175.net
{
Scores score = new scores (); // create a scores object
Dataset DS = score. queryscore (); // call the queryscore method to query the score and place the query result in the dataset.
Datatable dt = Ds. Tables [0];
// Generate the name of the Excel file for storing the result
String newfilename = datetime. Now. tostring ("yyyymmddhhmmss") + ". xls ";
// Convert to a physical path
Newfilename = server. mappath ("Temp/" + newfilename );
// Generate the Excel file based on the template
File. Copy (server. mappath ("../module01.xls"), newfilename, true );
// Create a database connection pointing to the Excel file
String strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + newfilename + "; extended properties = 'excel 8.0 ;'";
Oledbconnection conn = new oledbconnection (strconn );
// Open the connection and prepare for operations on the file
Conn. open ();
Oledbcommand cmd = new oledbcommand ("", Conn );

Foreach (datarow DR in DT. Rows)
{
String xsqlstring = "insert into [sheet1 $]";
Xsqlstring + = "([user name], [exam], [score], [exam time]) values (";
Xsqlstring + = "'" + Dr ["username"] + "',";
Xsqlstring + = "'" + Dr ["papername"] + "',";
Xsqlstring + = "'" + Dr ["score"] + "',";
Xsqlstring + = "'" + Dr ["examtime"] + "')";
Cmd. commandtext = xsqlstring;
Cmd. executenonquery ();
}

// The operation ends and the connection is closed.
Conn. Close ();
// Open the file to be downloaded and store it in filestream
System. Io. filestream reader = system. Io. file. openread (newfilename );
// Number of remaining bytes for file transfer: the initial value is the total size of the file.
Long length = reader. length;

Response. Buffer = false;
Response. addheader ("connection", "keep-alive ");
Response. contenttype = "application/octet-stream ";
Response. addheader ("content-disposition", "attachment; filename =" + server. urlencode ("student ID .xls "));
Response. addheader ("Content-Length", length. tostring ());

Byte [] buffer = new byte [10000]; // buffer for storing data to be sent
Int bytetoread; // the actual number of bytes read each time

While (length> 0)
{
// If the remaining bytes are not zero, transfer continues.
If (response. isclientconnected)
{
// The client browser is still open and the transfer continues
Bytetoread = reader. Read (buffer, 0, 10000); // read data into the buffer
Response. outputstream. Write (buffer, 0, bytetoread); // write the data in the buffer to the client browser.
Response. Flush (); // write to the client immediately
Length-= bytetoread; // The number of remaining bytes is reduced.
}
Else
{
// The client browser is disconnected to prevent the loop from continuing.
Length =-1;
}
}

// Close the file
Reader. Close ();
// Delete the Excel file
File. Delete (newfilename );
}