C # Excel sheets everywhere

In normal programming, we often encounter the need to excel tables everywhere. Below is a summary of the small series, hoping to be useful to everyone.

Scoresmr score = new scoresmr (); // create a scoresmr 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 to be saved as string newfilename = datetime. now. tostring ("yyyymmddhhmmss") + ". xls "; // convert to the physical path newfilename = server. mappath ("Temp/" + newfilename); // generate the Excel file according to the template. copy (server. mappath (".. /modulemr.xls "), newfilename, true); // create a database connection 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, prepare conn to operate the file. 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 is complete. Close the connection to Conn. close (); // open the file to be downloaded and store the file in filestream system. io. filestream reader = system. io. file. openread (newfilename); // number of bytes remaining 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]; // The buffer that stores the data to be sent. Int bytetoread; // The actual number of bytes read each time while (length> 0) {// the remaining number of bytes is not zero, and if (response. isclientconnected) {// The client browser is still open. Continue to transfer 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 the length to the client immediately-= bytetoread; // reduce the number of remaining bytes} else {// The client browser has been disconnected to prevent the loop from continuing length =-1 ;}} // close the file reader. close (); // Delete the Excel file. delete (newfilename );

C # Excel sheets everywhere