asp.net build Excel and export download Five implementation methods

asp.net build Excel and export download Five implementation methods _ practical Tips

Last Update:2017-01-19 Source: Internet

Author: User

Tags flush rowcount

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

method One passes the GridView (brief comment: The method is simpler, but only suitable for generating simple Excel, and cannot keep VBA code), the page is not refreshed

Aspx.cs part

Copy Code code as follows:

Using System;

Using System.Collections;

Using System.Configuration;

Using System.Data;

Using System.Web;

Using System.Web.Security;

Using System.Web.UI;

Using System.Web.UI.HtmlControls;

Using System.Web.UI.WebControls;

Using System.Web.UI.WebControls.WebParts;

Using System.Text;

public partial class DataPage_NationDataShow:System.Web.UI.Page

{

Private Data_link Link = new Data_link ();

private String sql;

protected void Page_Load (object sender, EventArgs e)

{

Ajax.Utility.RegisterTypeForAjax (typeof (Datapage_nationdatashow));

}

protected void Btnexcel_click (object sender, EventArgs e)

{

String strexcelname = "Myexcel";

Strexcelname = Strexcelname.replace (@ "/", "");

Data_link Link = new Data_link ();

string strSQL = This.hidParam.Value;

DataSet ds = new DataSet ();

ds = link. D_dataset_return (strSQL);//Get the data you want to put in Excel

Gvexcel.visible = true;

Gvexcel.datasource = null;

Gvexcel.datamember = ds. Tables[0]. TableName;

Gvexcel.datasource = ds. Tables[0];

Gvexcel.databind ();

exportToExcel (this. Page, Gvexcel, strexcelname);

}

protected void Gvexcel_rowdatabound (object sender, GridViewRowEventArgs e)

{ }

public override void Verifyrenderinginserverform (Control control)

{ }

<summary>

Tool method, Excel output (solve garbled problem)

</summary>

<param name= "page" > call page </param>

<param name= "Excel" >excel data </param>

<param name= "filename" > FileName </param>

public void exportToExcel (System.Web.UI.Page Page, GridView Excel, String fileName)

{

Try

{

foreach (GridViewRow row in Excel. Rows)

{

for (int i = 0; i < row. Cells.count; i++)

{

Excel. Headerrow.cells[i]. BackColor = System.Drawing.Color.Yellow;

}

}

Excel. Font.Size = 10;

Excel. Alternatingrowstyle.backcolor = System.Drawing.Color.LightCyan;

Excel. Rowstyle.height = 25;

Page. Response.appendheader ("Content-disposition", "attachment;filename=" + filename);

Page. Response.Charset = "Utf-8";

Page. Response.ContentType = "application/vnd.ms-excel";

Page. Response.Write ("<meta http-equiv=content-type content=text/html;charset=utf-8>");

Excel. Page.enableviewstate = false;

Excel. Visible = true;

Excel. Headerstyle.reset ();

Excel. Alternatingrowstyle.reset ();

System.IO.StringWriter ostringwriter = new System.IO.StringWriter ();

System.Web.UI.HtmlTextWriter ohtmltextwriter = new System.Web.UI.HtmlTextWriter (ostringwriter);

Excel. RenderControl (Ohtmltextwriter);

Page. Response.Write (Ostringwriter.tostring ());

Page. Response.End ();

Excel. DataSource = null;

Excel. Visible = false;

}

catch (Exception e)

{

}

}

}

ASPX section

Copy Code code as follows:

In the Aspx.cs code just now,

Copy Code code as follows:

foreach (GridViewRow row in Excel. Rows)
{
for (int i = 0; i < row. Cells.count; i++)
{
Excel. Headerrow.cells[i]. BackColor = System.Drawing.Color.Yellow;
}
}

This section adds a style to the table header.

Sometimes in order to facilitate browsing, you need to add a style to the cross row, the simple point can be as follows:

Copy Code code as follows:

Excel. Alternatingrowstyle.backcolor = System.Drawing.Color.LightCyan;

But a closer look will show that it has changed the entire line of styles, including those that are not used in the following columns.

The solution is yes, but more cumbersome, is to modify the style of each cell.

Copy Code code as follows:

int rowcount = Excel. Rows.Count;
int colcount = Excel. HeaderRow.Cells.Count;

for (int i = 0; i < rowcount; i++)
{
for (int j=0;j<colcount; j + +)
{
Excel. Rows[i]. CELLS[J]. BackColor = System.Drawing.Color.LightCyan;
}
}

Method TwoThe page is not refreshed through the DataGrid (basically the same as method)

Aspx.cs part

Copy Code code as follows:

public override void Verifyrenderinginserverform (Control control)

{}

<summary>

エクセル Output イベント

</summary>

<param name= "Sender" ></param>

<param name= "E" ></param>

private void Excelbut_click (object sender, System.EventArgs e)

{

DataGrid dgexcel = new DataGrid ();

Try

{

DataSet ds = Getexceldata (this. Pageparams.value);/output データを obtain する

if (ds. Tables[0]. ROWS.COUNT&GT;0)

{

エクセルへデータを input する

String Execlname= "Myexcel";

Encoding Encodingtype=system.text.encoding.utf8;

Dgexcel.datamember=ds. Tables[0]. TableName;

Dgexcel.datasource=ds. Tables[0];

Response.Buffer = true;

Response.Charset = "Utf-8";

Response.appendheader ("Content-disposition", "attachment;filename=" + execlname+ ". xls");

response.contentencoding = Encodingtype;

Response.ContentType = "Application/ms-excel";

StringWriter ostringwriter = new StringWriter ();

HtmlTextWriter ohtmltextwriter = new HtmlTextWriter (Ostringwriter);

Dgexcel.databind ();

Dgexcel.visible = true;

Dgexcel.rendercontrol (Ohtmltextwriter);

Response.Write (Ostringwriter.tostring ());

Response.Flush ();

Response.close ();

Dgexcel.datasource = null;

Dgexcel.visible = false;

}

Else

{

Response.Write ("<script>alert (' xxxxxx ') </script>");

}

}

catch (Exception ex)

{

Response.Write ("<script>alert (' oooooo ') </script>");

}

}

ASPX section

Copy Code code as follows:

<head runat= "Server" >

<script type= "Text/javascript" >

Excel DownLoad

function Excelexport () {

var hidtext = document.getElementById ("Hidparam");

Hidtext.value = "some params";

document.getElementById ("Exceloutput"). Click ();

}

</script>

</head>

<body onload= "Pageinit ()" >

<form id= "Form1" runat= "Server" >

<input type= "button" value= "Excel Downloads" style= "width:100px" onclick= "Excelexport ()" id= "Excelbut" name= "excelbut"/ >

<input id= "Hidparam" type= "text" runat= "server" style= "Display:none;" Name= "Hidparam"/>

<asp:button runat= "Server" id= "Exceloutput" style= "Display:none" text= "Excel Output" width= "0px" onclick= "Btnexcel_" Click "Usesubmitbehavior= false"/>

<asp:datagrid id= "Gvexcel" visible= "False" runat= "Server" style= "z-index:107"; Position:absolute; top:72px; left:520px "

Width= "80px" height= "40px" ></asp:datagrid>

</form>

</body>

Method ThreeExcel in XML format (you can set rich styles and can have multiple sheet, but you need a template.) Fast, but the generated file is large and the VBA code cannot be preserved.

Concrete method: The template is saved as A.xml in XML document form. You can then refer to the contents of the A.xml

Copy Code code as follows:

private void Excelbut_click (object sender, System.EventArgs e)

{

DataSet ds = new DataSet ();

String excelfilename = "";

DataRow dr=[------Column name information-------];

Try

{

Excelfilename = Path.Combine (Request.physicalapplicationpath + "/excelfile", "Myexcel.xls");

Get the data that Excel needs

DS = [------obtain data-------];

if (ds. Tables.count = 0)

{

Response.Write ("<script type= ' text/javascript ' >alert (' no data ');</script>");

Return

}

int sheetnum = ds. TABLES.COUNT/2;

StreamWriter writer = new StreamWriter (Excelfilename, false);

The information in front of the styles label is equivalent to ' header information ' and does not need to be changed

Writer. WriteLine ("<?xml version=\" 1.0\ "?>");

Writer. WriteLine ("<?mso-application progid=\" excel.sheet\ "?>");

Writer. WriteLine ("<workbook xmlns=\" urn:schemas-microsoft-com:office:spreadsheet\ ")";

Writer. WriteLine ("xmlns:o=\" urn:schemas-microsoft-com:office:office\ "");

Writer. WriteLine ("xmlns:x=\" urn:schemas-microsoft-com:office:excel\ "");

Writer. WriteLine ("xmlns:ss=\" urn:schemas-microsoft-com:office:spreadsheet\ "");

Writer. WriteLine ("xmlns:html=\" http://www.w3.org/tr/rec-html40\ ">");

Writer. WriteLine ("<documentproperties xmlns=\" urn:schemas-microsoft-com:office:office\ ">");

Writer. WriteLine ("<lastauthor>automated generator example</lastauthor>");

Writer. WriteLine (String. Format ("<Created>{0}T{1}Z</Created>", DateTime.Now.ToString ("Yyyy-mm-dd"), DateTime.Now.ToString ("HH : Mm:ss "));

Writer. WriteLine ("<Company>51aspx.com</Company>");

Writer. WriteLine ("<Version>11.6408</Version>");

Writer. WriteLine ("</DocumentProperties>");

Writer. WriteLine ("<excelworkbook xmlns=\" urn:schemas-microsoft-com:office:excel\ ">");

Writer. WriteLine ("<WindowHeight>6195</WindowHeight>");

Writer. WriteLine ("<WindowWidth>18495</WindowWidth>");

Writer. WriteLine ("<WindowTopX>525</WindowTopX>");

Writer. WriteLine ("<WindowTopY>4260</WindowTopY>");

Writer. WriteLine ("<AcceptLabelsInFormulas/>");

Writer. WriteLine ("<ProtectStructure>True</ProtectStructure>");

Writer. WriteLine ("<ProtectWindows>False</ProtectWindows>");

Writer. WriteLine ("</ExcelWorkbook>");

Classes that correspond to style in HTML through Ss:id and Ss:name

Writer. WriteLine ("<Styles>");

Writer. WriteLine ("<style ss:id= ' Default ' ss:name= ' Normal ' >");

Writer. WriteLine ("<alignment ss:vertical= ' Bottom '/>");

Writer. WriteLine ("<Borders/>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<Interior/>");

Writer. WriteLine ("<NumberFormat/>");

Writer. WriteLine ("<Protection/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S23 ' >");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ' ' 11 '");

Writer. WriteLine ("ss:color= ' #FF0000 '/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S24 ' >");

Writer. WriteLine ("<Interior/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S25 ' >");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<Interior/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S26 ' >");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ' ' 11 '");

Writer. WriteLine ("ss:color= ' #FF0000 '/>");

Writer. WriteLine ("<Interior/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S27 ' >");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' left ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' right ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<interior ss:color= ' #CCFFFF ' ss:pattern= ' Solid '/>");

Writer. WriteLine ("<Protection/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S28 ' >");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' left ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' right ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<interior ss:color= ' #CCFFFF ' ss:pattern= ' Solid '/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' s29 ' >");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' left ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<interior ss:color= ' #CCFFFF ' ss:pattern= ' Solid '/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S30 ' >");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<interior ss:color= ' #CCFFFF ' ss:pattern= ' Solid '/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S31 ' >");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' right ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<interior ss:color= ' #CCFFFF ' ss:pattern= ' Solid '/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S32 ' >");

Writer. WriteLine ("<alignment ss:vertical= ' Bottom ' ss:wraptext= ' 1 '/>");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' left ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' right ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<interior ss:color= ' #CCFFFF ' ss:pattern= ' Solid '/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S33 ' >");

Writer. WriteLine ("<alignment ss:vertical= ' Bottom ' ss:wraptext= ' 1 '/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' s34 ' >");

Writer. WriteLine ("<alignment ss:vertical= ' Bottom ' ss:wraptext= ' 1 '/>");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' left ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' right ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<Interior/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S35 ' >");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' left ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' right ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<Interior/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S36 ' >");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' left ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' right ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<interior ss:color= ' #FFFF99 ' ss:pattern= ' Solid '/>");

Writer. WriteLine ("<protection ss:protected= ' 0 '/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' s37 ' >");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' left ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' right ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<Interior/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' s38 ' >");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' left ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' right ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<Interior/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("<style ss:id= ' S39 ' >");

Writer. WriteLine ("<Borders>");

Writer. WriteLine ("<border ss:position= ' Bottom ' ss:linestyle= ' continuous '" ss:weight= ' 1 '/> ");

Writer. WriteLine ("<border ss:position= ' left ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' right ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("<border ss:position= ' top ' ss:linestyle= ' continuous ' ss:weight= ' 1 '/>");

Writer. WriteLine ("</Borders>");

Writer. WriteLine ("<font ss:fontname= ' Ms pゴシック ' x:charset= ' 128 ' x:family= ' modern ' ss:size= ')"/> ");

Writer. WriteLine ("<Interior/>");

Writer. WriteLine ("</Style>");

Writer. WriteLine ("</Styles>");

Sheet operation

Writer. WriteLine ("<worksheet ss:name= '" + dr["Msg030"] + "' >");

Writer. WriteLine ("<table ss:expandedcolumncount= ' 9 ' ss:expandedrowcount= ' 3000 ' x:fullcolumns= ' 1 ' x:fullrows= ' 1 ' ss: Styleid= ' S33 ' ss:defaultcolumnwidth= ' ss:defaultrowheight= ' 13.5 ' > ');

Writer. WriteLine ("<column ss:styleid= ' s34 ' ss:autofitwidth= ' 0 ' ss:width= ' 110.25 '/> ')";

Writer. WriteLine ("<column ss:styleid= ' s34 ' ss:autofitwidth= ' 0 ' ss:width= ' 33.75 '/> ')";

Writer. WriteLine ("<column ss:styleid= ' s34 ' ss:autofitwidth= ' 0 ' ss:width= ' 118.5 '/> ')";

Writer. WriteLine ("<column ss:styleid= ' s34 ' ss:autofitwidth= ' 0 ' ss:width= ' 159.75 '/> ')";

Writer. WriteLine ("<column ss:styleid= ' s34 ' ss:autofitwidth= ' 0 ' ss:width= ' 147.75 ' ss:span= ' 4 '/> ')";

for (int num = sheetNum-1; num >= 0; num = num-1)

{

There are 3 lines of blank lines in the exam.

if (num!= sheetNum-1)

{

Writer. WriteLine ("<Row>");

Writer. WriteLine ("</Row>");

Writer. WriteLine ("<Row>");

Writer. WriteLine ("</Row>");

Writer. WriteLine ("<Row>");

Writer. WriteLine ("</Row>");

}

Column Name

Writer. WriteLine ("<row ss:height= ' 40.5 ' >");

Writer. WriteLine ("<cell ss:styleid= ' s32 ' ><data ss:type= ' String ' >" + dr["Msg002"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' s32 ' ><data ss:type= ' String ' >" + dr["Msg003"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' s32 ' ><data ss:type= ' String ' >" + dr["Msg004"] + "</Data></Cell>" );

Writer. WriteLine ("</Row>");

Detailed data

for (int i = 0; I < ds. tables[2 * num]. Rows.Count; i++)

{

Writer. WriteLine ("<row ss:height= ' >");

Writer. WriteLine ("<cell><data ss:type= ' String ' >" + ds.) tables[2 * num]. rows[i]["Evallevel1name"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell><data ss:type= ' number ' >" + ds.) tables[2 * num]. rows[i]["Evallevel2code"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell><data ss:type= ' String ' >" + ds.) tables[2 * num]. rows[i]["Evallevel2name"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("</Row>");

}

}

Writer. WriteLine ("</Table>");

Writer. WriteLine ("</Worksheet>");

Other sheet operations

for (int num = sheetNum-1; num >= 0; num--)

{

Writer. WriteLine ("<worksheet ss:name=") + ds. tables[2 * num]. ROWS[0][1]. ToString () + "' ss:protected= ' 1 ' >");

Writer. WriteLine ("<table ss:expandedcolumncount= ' ss:expandedrowcount= ' 30000 ' x:fullcolumns= ' 1 ' x:fullrows= ' 1 ' ss: Styleid= ' S25 ' ss:defaultcolumnwidth= ' ss:defaultrowheight= ' 13.5 ' > ');

Writer. WriteLine ("<Row>");

Writer. WriteLine ("<cell ss:styleid= ' S26 ' ><phonetictext xmlns= ' Urn:schemas-microsoft-com:office:excel '" > カキキイロテンスウツ</phonetictext><data ss:type= ' String ' > ' + dr[' Msg031 ' + ' </Data></Cell> ');

Writer. WriteLine ("<cell ss:index= ' ss:styleid= ' Default '/>");

Writer. WriteLine ("<cell ss:styleid= ' Default '/>");

Writer. WriteLine ("<cell ss:styleid= ' Default '/>");

Writer. WriteLine ("<cell ss:styleid= ' Default '/>");

Writer. WriteLine ("<cell ss:styleid= ' Default '/>");

Writer. WriteLine ("<cell ss:styleid= ' Default '/>");

Writer. WriteLine ("<cell ss:styleid= ' Default '/>");

Writer. WriteLine ("<cell ss:styleid= ' Default '/>");

Writer. WriteLine ("<cell ss:styleid= ' Default '/>");

Writer. WriteLine ("</Row>");

Writer. WriteLine ("<Row>");

Writer. WriteLine ("<cell ss:styleid= ' S26 ' ><phonetictext xmlns= ' Urn:schemas-microsoft-com:office:excel '" > カキキイロテンスウツ</phonetictext><data ss:type= ' String ' > ' + dr[' Msg032 ' + ' </Data></Cell> ');

Writer. WriteLine ("<cell ss:index= ' ss:styleid= ' Default '/>");

Writer. WriteLine ("<cell ss:styleid= ' Default '/>");

Writer. WriteLine ("<cell ss:styleid= ' Default '/>");

Writer. WriteLine ("</Row>");

Column Name

Writer. WriteLine ("<Row>");

Writer. WriteLine ("<cell ss:styleid= ' s27 ' ><data ss:type= ' String ' >" + dr["Msg001"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' s28 ' ><data ss:type= ' String ' >" + dr["Msg002"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' s28 ' ><data ss:type= ' String ' >" + dr["Msg003"] + "</Data></Cell>" );

Writer. WriteLine ("</Row>");

int sheetn = ds. tables[2 * num]. Rows.Count;

Detailed data

for (int i = 0; i < SHEETN; i++)

{

Writer. WriteLine ("<Row>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' number ' >" + ds.) tables[2 * num]. rows[i]["Evallevel1code"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' >" + ds.) tables[2 * num]. rows[i]["Evallevel1name"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' number ' >" + ds.) tables[2 * num]. rows[i]["Evallevel2code"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell><data ss:type= ' String ' > </Data></Cell>");

Writer. WriteLine ("</Row>");

}

Writer. WriteLine ("<Row>");

Writer. WriteLine ("</Row>");

Writer. WriteLine ("<Row>");

Writer. WriteLine ("</Row>");

Column Name

Writer. WriteLine ("<Row>");

Writer. WriteLine ("<cell ss:styleid= ' s29 ' ><data ss:type= ' String ' >" + dr["Msg011"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' S30 '/>");

Writer. WriteLine ("<cell ss:styleid= ' S31 '/>");

Writer. WriteLine ("<cell ss:styleid= ' s29 ' ><data ss:type= ' String ' >" + dr["Msg012"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' S31 '/>");

Writer. WriteLine ("<cell ss:styleid= ' s29 ' ><data ss:type= ' String ' >" + dr["Msg013"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' S30 '/>");

Writer. WriteLine ("<cell ss:styleid= ' S30 '/>");

Writer. WriteLine ("<cell ss:styleid= ' S30 '/>");

Writer. WriteLine ("<cell ss:styleid= ' S31 '/>");

Writer. WriteLine ("<cell ss:styleid= ' s29 ' ><data ss:type= ' String ' >" + dr["Msg014"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' S31 '/>");

Writer. WriteLine ("<cell ss:styleid= ' s29 ' ><data ss:type= ' String ' >" + dr["Msg015"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' S31 '/>");

Writer. WriteLine ("</Row>");

Writer. WriteLine ("<Row>");

Writer. WriteLine ("<cell ss:styleid= ' s28 ' ><data ss:type= ' String ' >" + dr["Msg016"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' s28 ' ><data ss:type= ' String ' >" + dr["Msg017"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' s28 ' ><data ss:type= ' String ' >" + dr["Msg018"] + "</Data></Cell>" );

Writer. WriteLine ("<cell ss:styleid= ' s28 ' ><data ss:type= ' String ' >" + dr["Msg019"] + "</Data></Cell>" );

Writer. WriteLine ("</Row>");

int Datelen = ds. tables[2 * num + 1]. Rows.Count;

String employeemanagement = "";

for (int i = 0; i < Datelen; i++)

{

Writer. WriteLine ("<Row>");

Writer. WriteLine ("<cell ss:styleid= ' s37 ' ><data ss:type= ' number ' >" + ds.) tables[2 * num + 1]. rows[i]["Employeemanagementid"]. ToString () + "</Data></Cell>");

if (! Employeemanagement.equals (ds. tables[2 * num + 1]. rows[i]["Employeemanagementid"]. ToString ()))

{

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' number ' >" + ds.) tables[2 * num + 1]. rows[i]["Employeecode"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' >" + ds.) tables[2 * num + 1]. rows[i]["EmployeeName"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' >" + ds.) tables[2 * num + 1]. rows[i]["BefEmployeeName2"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' >" + ds.) tables[2 * num + 1]. rows[i]["EmployeeName2"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s38 ' ><data ss:type= ' number ' >" + ds.) tables[2 * num + 1]. rows[i]["Evallevel1code"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' >" + ds.) tables[2 * num + 1]. rows[i]["Evallevel1name"]. ToString () + "</Data></Cell>");

Employeemanagement = ds. tables[2 * num + 1]. rows[i]["Employeemanagementid"]. ToString ();

}

Else

{

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' ></Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' ></Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' ></Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' ></Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s38 ' ><data ss:type= ' String ' ></Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' ></Data></Cell>");

}

Writer. WriteLine ("<cell ss:styleid= ' s39 ' ><data ss:type= ' number ' >" + ds.) tables[2 * num + 1]. rows[i]["Evallevel2code"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("<cell ss:styleid= ' s35 ' ><data ss:type= ' String ' >" + ds.) tables[2 * num + 1]. rows[i]["Evallevel2name"]. ToString () + "</Data></Cell>");

Writer. WriteLine ("</Row>");

}

Writer. WriteLine ("</Table>");

Writer. WriteLine ("</Worksheet>");

}

Writer. WriteLine ("</Workbook>");

Writer. Close ();

FileDownload (Excelfilename);

}

catch (System.Exception ex)

{

}

Finally

{

Response.End ();

}

}

public void FileDownload (Fullfilename)

{

FileInfo DownloadFile = new FileInfo (fullfilename);

Response.Clear ();

Response.ClearHeaders ();

Response.Buffer = true;

Response.appendheader ("Content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode ( System.Text.Encoding.UTF8.GetBytes (MyFile ". xls"));

Response.ContentType = "Application/ms-excel;charset=utf-8";//downloadfile.fullnameoctet-stream

response.contentencoding = System.Text.Encoding.GetEncoding ("Shift-jis");

Response.appendheader ("Content-length", DownloadFile.Length.ToString ());

Response.WriteFile (Fullfilename);

Response.Flush ();

if (file.exists (fullfilename))

{

File.delete (Fullfilename);

}

}

method FourUse Microsoft's COM component to manipulate Excel. Although it is convenient to manipulate cells and keep the VBA code, It is equivalent to opening an Excel process directly.

This COM component was prepared for WinForm, but there was a lot of support on the web side, such as the inability to close an Excel object with the methods in the component.

You can only close the Excel process by forcing it (there are a number of problems, such as closing the currently open process for this Excel, or closing the Excel process for other users). It's not recommended here.

method Five in the way of streaming, the content in table format to excel in the data benefit is to generate rich and complex format Excel, page no refresh

ASPX section

Copy Code code as follows:

<asp:button id= "Hidexport" onclick= "Hidexport_click ()" runat= "Server" ></asp:Button>

Aspx.cs part

Copy Code code as follows:

The content is very understanding, just as a table to spell the string can be

private String Getexcelcontent ()

{

StringBuilder sb = new StringBuilder ();

Sb. Append ("<table bordercolor= ' black ' border= ' 1 ' >");

Sb. Append ("<thead><tr><th colspan= ' 2 ' bgcolor= ' #ccfefe ' > Title </th></tr>");

Sb. Append ("<tr><th bgcolor= ' #ccfefe ' > Number </th><th bgcolor= ' #ccfefe ' > name </th></tr> </thead> ");

Sb. Append ("<tbody>");

Sb. Append ("<tr class= ' firsttr ' ><td bgcolor= ' #FF99CC ' ></td><td></td></tr>");

Sb. Append ("<tr class= ' secondtr ' ><td></td><td bgcolor= ' lightskyblue ' ></td></tr>") );

Sb. Append ("</tbody></table>");

Return SB. ToString ();

}

private void Hidexport_click (object sender, System.EventArgs e)

{

String content = Getexcelcontent ();

String css = ". Firsttr td{color:blue;width:100px;}. SECONDTR td{color:blue;width:100px;} ";

string filename = "Test.xls";

Commontool.exporttoexcel (filename, content, CSS);

}

Tool Class Commontool

Copy Code code as follows:

public class Commontool
{
<summary>
In the form of a stream, you can set a rich and complex style
</summary>
<param name= "Content" >excel contents (table format) </param>
<param name= "filename" > FileName </param>
<param name= "Csstext" > Style content </param>
public static void exportToExcel (string filename, string content,string csstext)
{
var res = HttpContext.Current.Response;
Content = String.Format ("<style type= ' text/css ' >{0}</style>{1}", csstext,content);

Res. Clear ();
Res. Buffer = true;
Res. Charset = "UTF-8";
Res. AddHeader ("Content-disposition", "attachment; Filename= "+ filename);
Res. ContentEncoding = System.Text.Encoding.GetEncoding ("UTF-8");
Res. ContentType = "Application/ms-excel;charset=utf-8";
Res. Write (content);
Res. Flush ();
Res. End ();
}
}

This method is more flexible and can be added through selectors, which is pretty good. The disadvantage is that you need to convert the data into strings.

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More