Summary of Excel Table Method in ASP.NET

xiaoxiao2021-03-06  54

1, generated by DataSet

public void CreateExcel (DataSet ds, string typeid, 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 = ""; int i = 0;

// Define the table object and the row object, and initialize the value of the value DataSet DataTable DT = DS.TABLES [0]; DATAROW [] MyRow = DT.SELECT (""); // typeid == "1" Export as an Excel format file; TypeId == "2" exports to XML format file IF (typeid == "1") {// acquisition data tables, each title is divided into / t, the last column header After adding the car (i = 0; i colheaders = dt.columns [i] .caption.tostring () "colheaders = dt.columns [i] .caption.toString () " / n "; // Write the data information of the obtained data in the HTTP output stream Resp.Write (COLHEADERS)" // Directive Data Foreach (DATAROW ROW in Myrow) {// In the current row, obtain data, data Between / T Separation, return to the car / N for (i = 0; I ls_item = row [i] .tostring () "/ t"; ls_item = row [i] .tostring () "/ n"; // Current row data writes the HTTP output stream, and sets empty LS_ITEM to down data Resp.Write (ls_item); ls_item = "";}} else {if (typeID == "2") { // Directly export XML data directly from the DataSet and write resp.write in the HTTP output stream;}}}} // The data in the write buffer is resp.end ();

}

2, using Microsoft's C written ActiveX control: http://download/officexpdev/sample/download/officexpdev/sample/dsoframerctl.exe3, generated by DataGrid:

public void ToExcel (System.Web.UI.Control ctl) {HttpContext.Current.Response.AppendHeader ( "Content-Disposition", "attachment; filename = Excel.xls"); HttpContext.Current.Response.Charset = "UTF- 8 "; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; HttpContext.Current.Response.ContentType =" application / ms-excel "; // image / JPEG; text / HTML; image / GIF; vnd .ms-excel / msword ctl.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter (); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw) CTL.RenderControl (HW); httpContext.current.response.write (Tw.toString ()); httpContext.current.Response.end ();} Usage: TOEXCEL (DataGrid1);

4, this uses DataView, the code is good

Public Void OutputExcel (DataView DV, String Str) {// // Todo: Add Construction Functions Logic // // DV to output to Excel, STR is the title name gc.collect (); Application Excel; // = new application (); int rotalog = 1;

_Workbook XBK; _Worksheet XST;

Excel = new applicationclass (); xbk = excel.workbooks.add (true); xst = (_Worksheet) XBK.Activesheet;

// // get the title // foreach (Datacolumn col in dv.table.columns) {Colindex ; Excel.cells [4, ColIndex] = columnname; xst.get_range (Excel.cells [4, ColIndex], Excel. Cells [4, ColIndex]). Horizontalalignment = XLVALIGN.XLVALIGNCENTER; // Set the title format to align the center alignment}

// // Number of data in the table {RowIndex ; colIndex = 1; Foreach (Datacolumn col in dv.table.columns) {Colindex ; if (col.DataType == System.Type.gettype ("system.datetime")) {Excel.cells [RowIndex, COLINDEX] = (CONVERT.TODATETIME (Row [Col.columnName] .tostring ())). TOSTRING ("YYYY-MM-DD" ); Xst.get_Range (Excel.cells [RowIndex, Colindex], Excel.cells [RowIndex, Colindex]). HorizontalaLightMent = XLVALIGN.XLVALIGNCENTER; // Setting the date type field format is aligned} Else if (col.DataType = = System.Type.gettype ("System.String")) {Excel.cells [RowIndex, ColIndex] = "'" row [col.columnname] .tostring (); xst.get_range (Excel.cells [RowIndex, Colindex ], Excel.cells [RowIndex, Colindex]). Horizontalalignment = XLVALIGN.XLVALIGNCENTER; / / Set the field format of the character pattern to the center align} else {Excel.cels [RowIndex, ColIndex] = ROW [columnname] .tostring );}}} // // load a total of a total line // int ROWSUM = RowIndex 1; int COLSUM = 2; Excel.cells [Rowsum, 2] = "Total"; Xst.get_Range (Excel.cells [Rowsum, 2], Excel.cells [Rowsum, 2]). HorizontalaLAlignment = Xlhalign.xlhalignCenter; // // Set Color // xst.get_range (Excel.cells [ROWSUM) , COLSUM], Excel.cells [Rowsum, Colindex]). SELECT (); Xst.get_Range (Excel.cells [Rowsum, COLSUM], Excel.cells [Rowsum, Colindex]). Interior.ColorIndex = 19; // Setting For light yellow, there are 56 species // // out of the title // Excel.cells [2, 2, 2] = Str; // // x 设置 g (Excel.cells " 2, 2], Excel.cells [2, 2]). Font.Bold = true; xst.get_range (Excel.cells [2, 2], Excel.cells [2, 2]). Font.size = 22;

// // Set the report form to the most adaptive width // xst.get_range (Excel.cells [4, 2], Excel.cells [Rowsum, ColIndex]). Select (); xst.get_range (Excel.cells [4, 2], Excel.cells [Rowsum, Colindex]). Column.autofit (); // // Setting the title of the entire report to cross-ranking // xst.get_range (Excel.cells [2, 2], Excel.cells [2, Colindex]). SELECT (); Xst.get_Range (Excel.cells [2, 2], Excel.cells [2, Colindex]). Horizontalalignment = xlhalign.xlhalignCenterCrossSselection; // // Draw Border // xst. GET_RANGE (Excel.cells [4, 2], Excel.cells [Rowsum, Colindex]). Borders.LineStyle = 1; Xst.get_Range (Excel.cells [4, 2], Excel.cells [Rowsum, 2]). Borders [xlbordersindex.xledgeleft] .weight = xlborderweight.xlt = xlborderweight.xlt = xlborderweight.xlt = xlborderweight.xlt = / 设置 设置 加 x x g g 加 加 加 加 加 加 加 加 加 加...............).... Weight = XlBorderWeight.xlThick;. // set on edge thickening xSt.get_Range (excel.Cells [4, colIndex], excel.Cells [rowSum, colIndex]) Borders [XlBordersIndex.xlEdgeRight] .Weight = XlBorderWeight.xlThick; / / Set the right line plus thick xst.get_range (Excel.cells [Rowsum, 2], Excel.cells [Rowsum, ColIndex]). Borders [xlborde rsindex.xledgebottom] .weight = xlborderweight.xlt = xlborderweight.xlt = xlborderweight.xlt = xlborderweight.xlt = / 设置 下 加 加 加; 加 加;;;;;;;;;; () . " this.xlfile.Text " xls ", SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML); xBk.SaveCopyAs (Server.MapPath (". ") " // " this.xlfile .Text ". Xls");

ds = null; xBk.Close (false, null, null); excel.Quit (); System.Runtime.InteropServices.Marshal.ReleaseComObject (xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject (excel); System.Runtime . InterleaseComObject (XST); XBK = null; Excel = NULL; XST = NULL; gc.collect (); string path = server.mappath (this.xLfile.Text ". Xls"); system.io. FileInfo file = new system.io.fileinfo (path); response.clear (); response.charset = "gb2312"; response.contentencoding = system.text.Encoding.utf8; // Add header information, for file download / Save As Dialog Specifies the default file name. Response.addheader ("Content-Disposition", "attachment; filename =" server.urlencode (file.name)); // Add header information, specify the file size, so that the browser can Display download progress response.addhead ("content-length", file.length.Length.Length.Length ()); // Specify that the return is a stream that cannot be read by the client, must be downloaded Response.contentType = "Application / MS- Excel "; // sends the file stream to the client response.writefile (file.fullname); / / Stop the page execution response.end ();

转载请注明原文地址:https://www.9cbs.com/read-116674.html

New Post(0)