asp⽣成Excel并导出下载五种实现⽅法
通过GridView(简评:⽅法⽐较简单,但是只适合⽣成格式简单的Excel,且⽆法保留VBA代码),页⾯⽆刷新aspx.cs部分复制代码代码如下: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 nder, EventArgs e) { Ajax.Utility.RegisterTypeForAjax(typeof(DataPage_NationDataShow)); } protected void btnExcel_Click(object nder, 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);//获得想要放⼊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 nder, GridViewRowEventArgs e) { } public override void VerifyRenderingInServerForm(Control control) { } /// <summary> /// ⼯具⽅法,Excel出⼒(解决乱码问题) /// </summary> ///
<param name="page">调⽤页⾯</param> /// <param name="excel">Excel数据</param> /// <param name="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.Respon.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); page.Respon.Chart = "utf-8"; page.Respon.ContentType = "application/vnd.ms-excel"; page.Respon.Write("<meta http-equiv=Content-Type content=text/html;chart=utf-8>");
excel.Page.EnableViewState = fal; excel.Visible = true; excel.HeaderStyle.Ret(); excel.AlternatingRowStyle.Ret(); System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); excel.RenderControl(oHtmlTextWriter);
page.Respon.Write(oStringWriter.ToString()); page.Respon.End(); excel.DataSource = null; excel.Visible = fal; } catch (Exception e) { } } }aspx部分复制代码代码如下:<head runat="rver"> <
script type="text/javascript"> //Excel DownLoad function excelExport(){ var hidText = ElementById("hidParam"); hidText.value = "some params"; ElementById("ExcelOutput").click(); } </script> </head> <body onload="pageInit()"> <form id="form1"
runat="rver"> <input type="button" value="EXCEL下载" onclick="excelExport()" id="excelBut" />
<input id="hidParam" type="text" runat="rver" /> <asp:Button runat="rver" ID="ExcelOutput"
Text= "EXCEL出⼒" Width="0px" onclick="btnExcel_Click" USubmitBehavior="fal"/>
<asp:GridView ID="gvExcel" runat="rver" Height="95px" OnRowDataBound="gvExcel_RowDataBound" Visible="Fal"> </asp:GridView> </form> </body>在刚才的aspx.cs代码中复制代码代码如下: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.AlternatingRowStyle.BackColor = System.Drawing.Color.LightCyan;
但是细看⼀下会发现它把⼀整⾏的样式都改变了,包括后⾯那些没有⽤到的列。
解决办法是有,不过⽐较繁琐,就是修改每个单元格的样式。
复制代码代码如下:
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;
}
}
通过DataGrid(与⽅法⼀基本相同),页⾯⽆刷新
aspx.cs部分
复制代码代码如下:
public override void VerifyRenderingInServerForm(Control control)
{}
/// <summary>
/// エクセル出⼒イベント
/// </summary>
/// <param name="nder"></param>
/// <param name="e"></param>
private void ExcelBut_Click(object nder, System.EventArgs e)
{
DataGrid dgExcel = new DataGrid();
try
{
DataSet ds = getExcelData(this.PageParams.Value);//出⼒データを取得する
if(ds.Tables[0].Rows.Count>0)
{
//エクセルへデータを投⼊する
string execlName= "MyExcel";
Encoding encodingType=System.Text.Encoding.UTF8;
dgExcel.DataMember=ds.Tables[0].TableName;
dgExcel.DataSource=ds.Tables[0];
Respon.Buffer = true;
Respon.Chart = "utf-8";
Respon.AppendHeader("Content-Disposition", "attachment;filename=" + execlName+ ".xls"); Respon.ContentEncoding = encodingType;
Respon.ContentType = "application/ms-excel";
StringWriter oStringWriter = new StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
dgExcel.DataBind();
dgExcel.Visible = true;
dgExcel.RenderControl(oHtmlTextWriter);
Respon.Write(oStringWriter.ToString());
Respon.Flush();
Respon.Clo();
dgExcel.DataSource = null;
dgExcel.Visible = fal;
}
el
{
Respon.Write("<script>alert('xxxxxx')</script>");
}
}
catch(Exception ex)
{
Respon.Write("<script>alert('oooooo')</script>");
}
aspx部分
复制代码代码如下:
<head runat="rver">
<script type="text/javascript">
//Excel DownLoad
function excelExport(){
var hidText = ElementById("hidParam");
hidText.value = "some params";
}
</script>
</head>
<body onload="pageInit()">
<form id="form1" runat="rver">
<input type="button" value="EXCEL下载" onclick="excelExport()" id="excelBut" NAME="excelBut"/> <input id="hidParam" type="text" runat="rver" NAME="hidParam"/>
<asp:Button runat="rver" ID="ExcelOutput" Text= "EXCEL出⼒" Width="0px"
onclick="btnExcel_Click" USubmitBehavior="fal"/>
<asp:datagrid id="gvExcel" Visible="Fal" Runat="rver"
Width="80px" Height="40px"></asp:datagrid>
</form>
</body>
以XML形式的Excel⽅式(可以设置丰富的样式,并可以有多个sheet,但需要模版。速度很快,但⽣成的⽂件较⼤,且⽆法保留VBA代码)
具体⽅法:把模版以 XML Document形式另存为A.xml。然后参照A.xml中的内容即可
复制代码代码如下:
private void ExcelBut_Click(object nder, System.EventArgs e)
{
DataSet ds = new DataSet();
string ExcelFileName = "";
DataRow dr=[------列名信息-------];
try
{
ExcelFileName = Path.Combine(Request.PhysicalApplicationPath + "/ExcelFile", "MyExcel.xls");
//获取Excel需要的数据
ds = [------获得数据-------];
if (ds.Tables.Count == 0)
{
Respon.Write("<script type='text/javascript'>alert('⽆数据');</script>");
return;
}
int sheetNum = ds.Tables.Count / 2;
StreamWriter writer = new StreamWriter(ExcelFileName, fal);
//Styles标签前⾯的信息相当于'头信息',不需要改变
车间管理人员工资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=\"www.w3/TR/REC-html40\">");
writer.WriteLine("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
writer.WriteLine("<LastAuthor>Automated Report 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&</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>Fal</ProtectWindows>");
writer.WriteLine("</ExcelWorkbook>");
//通过 ss:ID 和 ss:Name相当于html中style的类鲤鱼之梦
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='MSPゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior/> ");
writer.WriteLine(" <NumberFormat/> ");
writer.WriteLine(" <Protection/> ");
writer.WriteLine(" </Style> ");
心率偏低writer.WriteLine(" <Style ss:ID='s23'> ");
writer.WriteLine(" <Font ss:FontName='MSPゴシック' 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='MSPゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior/> ");
writer.WriteLine(" </Style> ");
writer.WriteLine(" <Style ss:ID='s26'> ");
writer.WriteLine(" <Font ss:FontName='MSPゴシック' 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='MSPゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 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='MSPゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 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='MSPゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 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='MSPゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 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='MSPゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 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='MSPゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 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='MSPゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 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='MSPゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior/> ");
writer.WriteLine(" </Style> ");