asp.net中生成excel报表

action201 2008-01-14 05:53:54

asp.net中生成Excel报表的方法?

谢谢!!

...全文

2856 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

zlblog 2011-08-12

打赏
举报

试试这个。

asp.net生成Excel文件

liuscmail 2010-07-07

打赏
举报

mark

zhangxuyu1118 2008-02-25

打赏
举报

mark

c11_11_11 2008-01-24

打赏
举报

揭贴

c11_11_11 2008-01-17

打赏
举报

搞完给分

c11_11_11 2008-01-17

打赏
举报



//加上这个

 public override void VerifyRenderingInServerForm(Control control)

    {



    }

khijfv2008 2008-01-17

打赏
举报

高速导出

 Response.ContentType = "application/octet-stream";

            Response.ContentEncoding = System.Text.UTF8Encoding.Default;

            Response.AddHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls");

            //字段名绑定(header)

            ArrayList aList = getDes();

            for (int i = 0; i < aList.Count; ++i)

            {

                Response.Write(aList[i].ToString());

                Response.Write('\t');

            }

            Response.Write("\r\n");



            //取要绑定的字段

            string[] colList = GetCol().Split(',');

            for (int i = 0; i < table.Rows.Count; i++)

            {

                for (int j = 0; j < colList.Length; ++j)

                {

                    Response.Write(table.Rows[i][colList[j].ToString()].ToString());

                    Response.Write('\t');

                }

                Response.Write("\r\n");

            }

            Response.End();

挺拔的劲松 2008-01-17

打赏
举报

/// <summary>
/// 功能：导出到excel
/// </summary>
/// <param name="oDS"></param>
void ExportExcel(DataSet oDS)
{
Response.Clear();
Response.Charset = "gb2312;";
Response.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter oSW = new System.IO.StringWriter();
HtmlTextWriter oHW = new HtmlTextWriter(oSW);
DataGrid oDG = new DataGrid();
oDG.DataSource = oDS.Tables[0];
oDG.DataBind();
oDG.RenderControl(oHW);
Response.Write(oSW.ToString());
Response.Flush();
Response.Close();
}

shoushii 2008-01-15

打赏
举报

Javascript+ActiveXObject

shoushii 2008-01-15

打赏
举报

3:Excel的XML格式

using System;

using System.Text;

using System.IO;



namespace Test

{

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

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            //存放模板文件内容

            string fileContent = string.Empty;

            //模板文件位置

            string modleFileName = Server.MapPath(".") + "\\ExcelModleFile.xls";

            //生成文件位置

            string renderFileName = Server.MapPath(".") + "\\ExcelFile.xls";



            //读出并保存模板文件内容

            StreamReader sr = new StreamReader(modleFileName, System.Text.Encoding.GetEncoding("gb2312"));

            fileContent = sr.ReadToEnd();

            sr.Close();



            //循环生成数据行

            StringBuilder sbRowsText = new StringBuilder(1024);

            sbRowsText.Append("<Row ss:AutoFitHeight=\"0\">");

            sbRowsText.Append("<Cell ss:StyleID=\"s24\" ss:HRef=\"");

            //设置超链接地址

            sbRowsText.Append("http://www.126.com/");

            sbRowsText.Append("\"><Data ss:Type=\"String\">View</Data></Cell>");

            sbRowsText.Append("<Cell ss:StyleID=\"s22\"><Data ss:Type=\"String\">");

            //设置内容

            sbRowsText.Append("Content");

            sbRowsText.Append("</Data></Cell>");

            sbRowsText.Append("</Row>");



            //保存完整Excel内容的字符串

            StringBuilder sbRender = new StringBuilder();

            //获得模板内容

            sbRender.Append(fileContent);

            //设置Excel数据行

            sbRender.Replace(@"[RowCount]", "3");

            //设置Excel标题

            sbRender.Replace(@"[Header]", "Title");

            //添加数据行

            sbRender.Replace(@"[DataRows]", sbRowsText.ToString());



            lblXml.Text = sbRender.ToString();



            //将内容写入文件

            StreamWriter sw = new StreamWriter(renderFileName);

            sw.Write(sbRender.ToString());

            sw.Close();



            //将文件输出到客户端

            Response.Charset = "GB2312";

            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(renderFileName));

            // 指定返回的是一个不能被客户端读取的流，必须被下载

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

            // 把文件流发送到客户端 

            Response.WriteFile(renderFileName);

            Response.End();

        }

    }

}

shoushii 2008-01-15

打赏
举报

2:使用MS的组件和服务

System.Data.DataTable dt = CreateExcelTable();

            if (dt.Rows.Count <= 0)

            {

                Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script language=javascript>alert('没有任何可导出的内容')</script>");

                return;

            }



            Application Excel;

            int rowIndex = 2;

            int colIndex = 0;



            _Workbook xBk;

            _Worksheet xSt;



            Excel = new ApplicationClass();



            xBk = Excel.Workbooks.Add(true);



            xSt = (_Worksheet)xBk.ActiveSheet;



            //打印横向

            xSt.PageSetup.Orientation = XlPageOrientation.xlLandscape;

            //第一行特殊样式

            xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).MergeCells = true;//合并单元格

            xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[3, 26]).Borders.LineStyle = 1;//加边框

            xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置居中对齐

            xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).Font.Size = 18;//设置字体



            Excel.Cells[1, 1] = "人才夹：" + ddlHeroFolder.SelectedItem.Text;



            //取得标题 

            foreach (DataColumn col in dt.Columns)

            {

                colIndex++;

                Excel.Cells[2, colIndex] = col.ColumnName;

                xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignLeft;//设置标题格式为居中对齐

                xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Bold = true;

                //xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Italic = Convert.ToBoolean(this.chkIta.Checked);

                xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Size = 12;

                //xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Name = this.ddlFont.SelectedValue;

                //xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Width = 150;

            }

            //取得表格中的数据 

            // 

            foreach (DataRow row in dt.Rows)

            {

                rowIndex++;

                colIndex = 0;

                foreach (DataColumn col in dt.Columns)

                {

                    colIndex++;



                    Excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();

                    xSt.get_Range(Excel.Cells[rowIndex, colIndex], Excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐 



                    Range tempRange = xSt.get_Range(xSt.Cells[rowIndex, 26], xSt.Cells[rowIndex, 27]);

                    string strHyperlinks = row["查看简历"].ToString();

                    xSt.Hyperlinks.Add(tempRange, strHyperlinks, "", "", "查看简历");





                }

            }

            xSt.Columns.AutoFit();

            Excel.Visible = true;



            //IO

            xBk.SaveCopyAs(Server.MapPath(".") + "\\" + "FileName" + ".xls");



            /////回收

            //ds = null;

            xBk.Close(false, null, null);



            Excel.Quit();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);

            System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);

            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel);



            xBk = null;

            xSt = null;

            Excel = null;

            GC.Collect();



            //Dispose();



            //////



            string path = Server.MapPath("FileName.xls");



            System.IO.FileInfo file = new System.IO.FileInfo(path);

            Response.Clear();

            Response.Charset = "GB2312";

            Response.ContentEncoding = System.Text.Encoding.UTF8;

            // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 

            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));

            // 添加头信息，指定文件大小，让浏览器能够显示下载进度 

            Response.AddHeader("Content-Length", file.Length.ToString());



            // 指定返回的是一个不能被客户端读取的流，必须被下载 

            Response.ContentType = "application/ms-Excel";



            // 把文件流发送到客户端 

            Response.WriteFile(file.FullName);

            // 停止页面的执行 



            Response.End();

shoushii 2008-01-15

打赏
举报

3种方法
1:

Feli.Data.Stock.CheckStockSubDAO   objCheckStockSubDAO   =   new   Feli.Data.Stock.CheckStockSubDAO(); 

Feli.Data.Stock.CheckStockSubCollection   objCheckStockSubCol   =   objCheckStockSubDAO.GetList(); 



string   strPath   =   Server.MapPath("..\\Excel\\CheckStock"); 

string   strName   =   strPath   +   "\\"   +   "CheckStock"   +   "_"   +   strChkNo   +   ".xls"; 



System.IO.FileStream       fs   =   new   System.IO.FileStream(   strName,   System.IO.FileMode.Create,System.IO.FileAccess.Write   );           

System.IO.StreamWriter   sw   =   new   System.IO.StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312")); 



sw.WriteLine(@"商品编号"   +   "\t"   +   "商品名称"   +   "\t"   +   "可出库数"   +   "\t"   +   "单价");           



foreach(Feli.Data.Stock.CheckStockSub   objCheckStockSub   in   objCheckStockSubCol) 

{ 

          sw.WriteLine(   objCheckStockSub.GdCd   +   "\t"   +   objCheckStockSub.GdName   +   "\t"   + 

                                      objCheckStockSub.StkQty.ToString()   +   objCheckStockSub.SalePrc.ToString());           

} 



sw.Close();           

Response.AddHeader("Content-Disposition",   "attachment;   filename="   +   Server.UrlEncode(strName));               

Response.ContentType   =   "application/msexcel";//指定返回的是一个不能被客户端读取的流，必须被下载           

Response.WriteFile(strName);       //把文件流发送到客户端           

Response.End();

maoning 2008-01-15

打赏
举报

protected void saveExcel(object sender, EventArgs e)
{

string fileName=ASPnetMenu2.SelectedItem.Label.ToString().Trim()+"排名";
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312 ";
Response.ContentType = "application/ms-excel ";//vnd.xls";
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlPathEncode(fileName+".xls"));
// If you want the option to open the Excel file without saving than comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache);
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
// turn off paging
//bindGridView();
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.Flush();
Response.End();
}

goodsun2 2008-01-15

打赏
举报

2楼的方法在gv.RenderControl(hw);处提示有错误啊？？

winner2050 2008-01-14

打赏
举报

如果不需要“导出Excel"当作真正的Excel 来用的随便用html就可以了。

否则可以使用sql命令来生成，跟sql生成sqlserver 数据库一样的。

wangdetian168 2008-01-14

打赏
举报

mark

paulyjin 2008-01-14

打赏
举报

http://dotnet.aspx.cc/ShowDetail.aspx?id=EC5E84EC-68F9-4CD7-9E11-6F5C92027F0B

c11_11_11 2008-01-14

打赏
举报



 public void Export(GridView gv)

    {

        string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

        string filePath = Server.MapPath("..") + fileName;

        System.Web.HttpResponse httpResponse = Page.Response;

        httpResponse.AppendHeader("Content-Disposition", "attachment;filename="

                               + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));

        httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");



        httpResponse.ContentType = "application/excel";



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

        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

        gv.RenderControl(hw);

        

        System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);

        sw.Write(tw.ToString());

        sw.Close();



        DownFile(httpResponse, fileName, filePath);

        httpResponse.End();

    }



    private bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath)

    {

        try

        {

            Response.ContentType = "application/octet-stream";



            Response.AppendHeader("Content-Disposition", "attachment;filename=" +

            HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=GB2312");

            System.IO.FileStream fs = System.IO.File.OpenRead(fullPath);

            long fLen = fs.Length;

            int size = 102400;//每100K同时下载数据 

            byte[] readData = new byte[size];//指定缓冲区的大小 

            if (size > fLen) size = Convert.ToInt32(fLen);

            long fPos = 0;

            bool isEnd = false;

            while (!isEnd)

            {

                if ((fPos + size) > fLen)

                {

                    size = Convert.ToInt32(fLen - fPos);

                    readData = new byte[size];

                    isEnd = true;

                }

                fs.Read(readData, 0, size);//读入一个压缩块 

                Response.BinaryWrite(readData);

                fPos += size;

            }

            fs.Close();

            System.IO.File.Delete(fullPath);

            return true;

        }

        catch

        {

            return false;

        }

    }