Export dataset data to Excel and dataset data export

Export dataset data to Excel and dataset data export

1. Write the data to the HTTP output stream/the exported data is in one row.

Public void CreateExcel (DataSet ds, string FileName) {HttpResponse resp; resp = Page. response; resp. contentEncoding = System. text. encoding. getEncoding ("GB2312"); resp. appendHeader ("Content-Disposition", "attachment; filename =" + FileName); string colHeaders = "", ls_item = ""; // Define table objects and row objects, at the same time, use DataSet to initialize its value DataTable dt = ds. tables [0]; DataRow [] myRow = dt. select (); // It can be similar to dt. int I = 0; int cl = dt. columns. count; // obtain the titles of each column in the data table, separated by/t. The carriage return (I = 0; I <cl; I ++) is added after the title of the last column) {if (I = (cl-1) // Add/n {colHeaders + = dt to the last column. columns [I]. caption. toString () + "/n";} else {colHeaders + = dt. columns [I]. caption. toString () + "/t" ;}} resp. write (colHeaders); // the data information written to the HTTP output stream // the data processed row by row (DataRow row in myRow) {// write the data in the current row to the HTTP output stream, and leave ls_item empty for downstream data (I = 0; I <cl; I ++) {if (I = (cl-1) // Add/n {ls_item + = row [I] to the last column. toString () + "/n";} else {ls_item + = row [I]. toString () + "/t" ;}} resp. write (ls_item); ls_item = "";} resp. end ();}

2. Open an excel file to write data row by row

You need to add two com references.

Microsoft Office 15.0 Object Library

Microsoft Excel 15.0 Object Library

 

Public bool DataSetToExcel (DataSet dataSet, bool isShowExcle) {DataTable dataTable = dataSet. tables [0]; int rowNumber = dataTable. rows. count; int columnNumber = dataTable. columns. count; if (rowNumber = 0) {// MessageBox. show ("no data can be imported to an Excel file! "); Return false;} else {Microsoft. office. interop. excel. application excel = new Microsoft. office. interop. excel. application (); excel. application. workbooks. add (true); excel. visible = isShowExcle; // whether to open the Excel file for (int c = 0; c <rowNumber; c ++) {for (int j = 0; j <columnNumber; j ++) {excel. cells [c + 1, j + 1] = dataTable. rows [c]. itemArray [j] ;}} return true ;}}