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 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 with the row object, and initialize its value with DataSet ??? DataTable DT = DS.TABLES [0]; ??? DataRow [] Myrow = dt.select ("); ??? // typeId == "1" exports to an Excel format file; TypeId == "2" exports to XML format file ??? if (typeid == "1") ??? {???? / / Total of the data tables, each title is divided into / t, the last column title is added back to the car ???? for (i = 0; I

????? colheaders = dt.columns [i] .caption.toString () "/ t";

???? colheaders = dt.columns [i] .caption.toString () "/ n";???

???? // written to the acquired data information to the HTTP output stream

???? Resp.write (colheaders);?

???? // progressive data??

???? foreach (DataRow Row In Myrow)

???? {

????? // In the current row, obtain data one by one, and the data is separated by / t, and the car is returned to the car.

????? 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 takes an empty LS_ITEM to down the data ????

????? resp.write (ls_item);

????? ls_item = ""

????}

???}

??? ELSE

??? {

???? f (typeid == "2")

???? {?

????? // export XML data directly from the DataSet and write to the HTTP output stream

????? resp.write (ds.getxml ());

????} ????

???}

??? // write the data in the buffer to the HTTP header file

??? 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 constructor logic here ??? // ??????????????? ??????????? // DV is the data to be output to Excel, STR is the title name ??? gc.collect (); ??? Application Excel; // = new application ();? ?? int RowIndex = 4; ??? INT colIndex = 1;

??? _ workbook XBK; ??? _ Worksheet XST;

??? EXCEL = new applibilityclass (); ????? xbk = Excel.Workbooks.add (true); ?????? xst = (_Worksheet) xbk.activesheet;

??? // ??? // get the title ??? // ??? foreach (Datacolumn COL in DV.Table.Column) ??? {???? colIndex ; ???? Excel.cells [4 , colIndex] = col.columnname; ???? xst.get_range (Excel.cells [4, Colindex], Excel.cells [4, Colindex]). HorizontalaLignment = XLVALIGN.XLVALIGNCENTER; // Set the title format to align the title format? ??}

??? // ??? // get the data in the table ??? // ??? Foreach (DATAROWVIEW ROW IN DV) ??? {???? rowindex ; ???? colINdex = 1; ???? foreach (Datacolumn COL IN DV.TABLE.COLUMNS) ???? {????? COLINDEX ; ????? IF (col.DataType == System.Type.gettype ("System. DateTime ")) ????? {????? eXcel.cells [RowIndex, ColIndex] = (CONVERT.TODATEX) (Row [columnName] .tostring ())). Tostring (" YYYY-MM- "); ?????? xst.get_range (Excel.cells [RowIndex, ColIndex], Excel.cells [RowIndex, Colindex]). Horizontalalignment = XLVALIGN.XLVALIGNCENTER; // Setting the date type field format is aligned ?????} ????? Else ?????? ife (col.DataType == system.type.gettype ("system.string") ????? {?????? Excel.cells [RowIndex, ColIndex] = "'" row [columnname] .tostring (); ?????? xst.get_range (Excel.cells [RowIndex, ColIndex], Excel.cells [RowIndex, Colindex ]). HorizontalaLAlignment = XLVALIGN.XLVALIGNCENTER; / / Set the field format of the character pattern to be aligned ?????} ????? Else ????? {?????? Excel.cells [RowIndex, COLINDEX] = row [col.columnname] .tostring (); ?????} ????} ???} ??? // ??? // load a total of a total row ??? // ?? ? int Rowsum = rowindex 1; ??? int COLSUM = 2; ??? EXCEL.CELLS [ROWSUM, 2] = "Total "??????? xst.get_range (Excel.cells [Rowsum, 2], Excel.cells [Rowsum, 2]). Horizontalalignment = Xlhalign.xlhalignCenter; ??? // ??? // Set the color of the selected section ??? // ??? 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; // Set to light yellow, total 56 species ??? // ??? // Title of the entire report ??? // ?? EXCEL.CELLS [2, 2] = Str; ??? // ??? // Set the title format of the entire report ??? // ??? Xst.get_Range (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]). Columns.autofit (); ??? // ??? // Set the title of the entire report as Cross-column in the middle ??? // ??? 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; // Set the left line plus thick ??? xst.get_range (Excel.cells [4, Colindex]). Borders [xlbordersindex.xledgeTop] .weight = XlborderWeight.xlt.xlt; // Set the upper side line plus thick ??? xst.get_range (Excel.cells [Rows [Rowsum, ColIndex]). Borders [xlbordersindex.xledgeright] .weight = xlborderweight.xlt; // Set the right line to add thick ??? xst.get_range (Excel.cells [Rowsum, 2], Excel.cells [Rowsum, Colindex]). Borders [XLBO RDERSINDEX.XLEDGEBOTTOM] .Weight = xlborderweight.xlt = xlborderweight.xlt = xlborderweight.xlt = xlborderweight.xlt = xlborderweight.xlt = xlborderweight.xlt = xlborderweight.xlt = xlborderweight.xlt = x 设置 下 加 加 ??? // ???? Excel.visible = true; ??? // xst .Export (Server.MapPath () "//" 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.InteropServices.Marshal.ReleaseComobject (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 head Information, specify the default file name for the "File Download / Save As" dialog box ??? response.addheader ("content-disposition", "attachment; filename =" server.urlencode (file.name)); ??? / / Add header information, specify the file size, so that the browser can display the download progress ??? Response.addheader ("content-length", file.length.tostring ()); ?????? // Specify return A stream that cannot be read by the client, must be downloaded ??? response.contentType = "Application / MS-Excel"; ?????? // Send the file stream to the client ??? Response.writefile File.FullName); ??? // Stop the execution of the page ????? response.end ();

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

New Post(0)