[译]使用ASP.NET2.0生成Excel报表

文章来源:


http://www.beansoftware.com/asp.net-tutorials/generating-excel-reports.aspx

文章作者:Sushmita Kumari

这是一篇很老的文章了,很长时间没有翻译文章,拿来练一练. 同时把作者的VB版,改成了C#版


源文件:

下载文件 点击下载此文件



Excel几乎是所有公司都要用来分析数据的一个超级工具,然而十年后的互联网已不可日而语,几乎所有的呈现的数据都是动态生成的,也就是说通过一个数据库里的数据交互产生所要的数据结果.有时,我们可能需在一个Excel的文件中,通过使用Excel的某些特性让分析的数据更好的适应我们的需要并呈现出来.在这篇文章中,我们可以学习到如何与Excel文件中的数据进行包括读写的交互操作.

相关源文件下载:
下载文件 点击下载此文件

相关的文件:为了更好的演示本文章,我们先找到了一个一个包含学生分数数据的一个Excel文件.里面的数据都是随机的.

Excel - ASP.NET Scenarios


让我们新建一个接口从Excel文件中获取数据.如图






注意引入以下组件及命名空间:


Imports System.Data.OleDb
Imports System.Data



VB Code


Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
            Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
            Dim dbConn As New OleDbConnection(strExcelConn)
            Dim strSQL As String

            strSQL = "Select * FROM [" & cmbSheets.SelectedItem.ToString() & "$]"

            dbConn.Open()

            Dim cmd As New OleDbCommand(strSQL, dbConn)
            Dim dsExcel As New DataSet
            Dim daExcel As New OleDbDataAdapter(cmd)

            daExcel.Fill(dsExcel)

            dgResults.DataSource = dsExcel
            dgResults.DataBind()
        Catch ex As Exception
            Throw ex
        End Try
End Sub


C# Code



protected void btnSearch_Click(object sender, System.EventArgs e)
{
    try {
        string strExcelConn = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString();
        OleDbConnection dbConn = new OleDbConnection(strExcelConn);
        string strSQL;

        strSQL = "Select * FROM [" + cmbSheets.SelectedItem.ToString() + "$]";

        dbConn.Open();

        OleDbCommand cmd = new OleDbCommand(strSQL, dbConn);
        DataSet dsExcel = new DataSet();
        OleDbDataAdapter daExcel = new OleDbDataAdapter(cmd);

        daExcel.Fill(dsExcel);

        dgResults.DataSource = dsExcel;
        dgResults.DataBind();
    }
    catch (Exception ex) {
        throw ex;
    }
}






值得注意的是:在查询中的表名应该是[TableName$]格式进行命名,必须要以[]和$结尾

Oledb连接已经这个Excel文件已经转变成了一个普通的数据库. 同时,第一行的已成包含了各列的字段名.现在我们可以根据降序对学生的数学,地理及总分产生报表.



Protected Sub btnGenerateReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerateReport.Click
Try


Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String

strSQL = "Select S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total "& _
                     "FROM [Students$] S, [Mathematics$] M, [Geography$] G "& _
                     "Where(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " & _
                     "ORDER BY (M.Marks+G.Marks) DESC"
dbConn.Open()

Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)

daExcel.Fill(dsExcel)

dgReports.DataSource = dsExcel
dgReports.DataBind()

Catch ex As Exception
Throw ex
End Try
End Sub



C# Code

protected void btnGenerateReport_Click(object sender, System.EventArgs e)
{
    try {


        string strExcelConn = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString();
        OleDbConnection dbConn = new OleDbConnection(strExcelConn);
        string strSQL;

        strSQL = "Select S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total " + "FROM [Students$] S, [Mathematics$] M, [Geography$] G " + "Where(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " + "ORDER BY (M.Marks+G.Marks) DESC";
        dbConn.Open();

        OleDbCommand cmd = new OleDbCommand(strSQL, dbConn);
        DataSet dsExcel = new DataSet();
        OleDbDataAdapter daExcel = new OleDbDataAdapter(cmd);

        daExcel.Fill(dsExcel);

        dgReports.DataSource = dsExcel;
        dgReports.DataBind();
    }

    catch (Exception ex) {
        throw ex;
    }
}






Generating Excel Reports 产生Excel报表


我们可以通过两种方法产生Excel的表,一种是使用”Response类和复杂的Microsoft Excel 11.0 实现.

下面的代码就是实现代码

VB Code



Protected Sub btnToExcelByResponse_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByResponse.Click
        '   Variables declaration
        Dim dsExport As New DataSet()
        Dim tw As New System.IO.StringWriter()
        Dim hw As New System.Web.UI.HtmlTextWriter(tw)
        Dim dgGrid As New DataGrid()

        dgGrid.DataSource = getData()

        '   Report Header
        hw.WriteLine(" Student Marking Report ")

        '   Get the HTML for the control.
        dgGrid.HeaderStyle.Font.Bold = True
        dgGrid.DataBind()
        dgGrid.RenderControl(hw)

        '   Write the HTML back to the browser.
        Response.ContentType = "application/vnd.ms-excel"
        Me.EnableViewState = False
        Response.Write(tw.ToString())
        Response.End()
    End Sub
    
    


C# Code

protected void btnToExcelByResponse_Click(object sender, System.EventArgs e)
{
    //   Variables declaration
    DataSet dsExport = new DataSet();
    System.IO.StringWriter tw = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
    DataGrid dgGrid = new DataGrid();

    dgGrid.DataSource = getData();

    //   Report Header
    hw.WriteLine(" Student Marking Report ");

    //   Get the HTML for the control.
    dgGrid.HeaderStyle.Font.Bold = true;
    dgGrid.DataBind();
    dgGrid.RenderControl(hw);

    //   Write the HTML back to the browser.
    Response.ContentType = "application/vnd.ms-excel";
    this.EnableViewState = false;
    Response.Write(tw.ToString());
    Response.End();
}



首先,我们需要创建一个dataset对象来装载这个数据.之后,通过StringWrite对象把一个HtmlTextWrite 看作一个参数写进对象中. 我们也可以使用一个datagrid对象来存储数据.而代码中的getData()方法只包括了前面的返回的数据. 由hw.WriteLine(" Student Marking Report ") 我们可以看到,可通过使用HTML代码来达到所需要呈现的效果,在我们的这个例子中,字体的大小是5,下划线并加粗. dgGrid.RenderControl(hw)则render了一个datagrid所包含的HTML Object.所得到的结果则是一个格式化好了的表格. Response.ContentType = "application/vnd.ms-excel" 将Response对象输出至Excel. Response.Write(tw.ToString()) 则是将已经格式化好的对象输入至Excel.





第二种方法是通过Microsoft Excel 11.0 Objects来产生报表.这个方法主要用于需要更复杂的功能或报表中使用.

VB Code


Protected Sub btnToExcelByObjects_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByObjects.Click
        Try
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet

            xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)
            xlWorkBook.Application.Visible = True
            xlWorkSheet = xlWorkBook.ActiveSheet

            '   Gets the dataset containing the data
            Dim dsData As DataSet = getData()
            Dim i As Integer = 2

            '   Outputting the fieldnames in pink bold color
            xlWorkSheet.Cells(1, 1) = "Student ID"
            xlWorkSheet.Cells(1, 2) = "Student Name"
            xlWorkSheet.Cells(1, 3) = "Mathematics"
            xlWorkSheet.Cells(1, 4) = "Geography"
            xlWorkSheet.Cells(1, 5) = "Total"

            xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1
            xlWorkSheet.Range("$A1:$E1").Font.Bold = True

            '   Outputting the data
            For Each dr As DataRow In dsData.Tables(0).Rows
                xlWorkSheet.Cells(i, 1) = dr(0)
                xlWorkSheet.Cells(i, 2) = dr(1)
                xlWorkSheet.Cells(i, 3) = dr(2)
                xlWorkSheet.Cells(i, 4) = dr(3)

                '   Building the formula for calculating the sum
                xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString())

                '   Going to the next row
                i = i + 1
            Next

            '   Auto fit the columns
            xlWorkSheet.Columns.AutoFit()

            '   Generating the graph
            Dim chart As Excel.Chart
            chart = xlWorkBook.Charts.Add()

            With chart
                .ChartType = Excel.XlChartType.xlColumnClustered
                .SetSourceData(xlWorkSheet.Range("A1:E11"), 2)

                .HasTitle = True
                .ChartTitle.Characters.Text = "Students' marks"

                .Axes(1, Excel.XlAxisGroup.xlPrimary).HasTitle = True
                .Axes(1, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Students"
                .Axes(2, Excel.XlAxisGroup.xlPrimary).HasTitle = True
                .Axes(2, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Marks"
            End With
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
      


C# Code

protected void btnToExcelByObjects_Click(object sender, System.EventArgs e)
{
    try {
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;

        xlWorkBook = new Excel.Application().Workbooks.Add(Missing.Value);
        xlWorkBook.Application.Visible = true;
        xlWorkSheet = xlWorkBook.ActiveSheet;

        //   Gets the dataset containing the data
        DataSet dsData = getData();
        int i = 2;

        //   Outputting the fieldnames in pink bold color
        xlWorkSheet.Cells(1, 1) = "Student ID";
        xlWorkSheet.Cells(1, 2) = "Student Name";
        xlWorkSheet.Cells(1, 3) = "Mathematics";
        xlWorkSheet.Cells(1, 4) = "Geography";
        xlWorkSheet.Cells(1, 5) = "Total";

        xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1;
        xlWorkSheet.Range("$A1:$E1").Font.Bold = true;

        //   Outputting the data
        foreach (DataRow dr in dsData.Tables(0).Rows) {
            xlWorkSheet.Cells(i, 1) = dr(0);
            xlWorkSheet.Cells(i, 2) = dr(1);
            xlWorkSheet.Cells(i, 3) = dr(2);
            xlWorkSheet.Cells(i, 4) = dr(3);

            //   Building the formula for calculating the sum
            xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString());

            //   Going to the next row
            i = i + 1;
        }

        //   Auto fit the columns
        xlWorkSheet.Columns.AutoFit();

        //   Generating the graph
        Excel.Chart chart;
        chart = xlWorkBook.Charts.Add();



         // ERROR: Not supported in C#: WithStatement

    }
    catch (Exception ex) {
        throw ex;
    }
}



首先创建了个Excel的工作簿,之后将这已经激活的工作簿通过xlWorkSheet = xlWorkBook.ActiveShee 之后,我们将数据放入dataset中等待处理.标题可以通过合适的字体呈现.而Excel中的公式则被用来计算每个学生的总分: xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString()).





同样,可以通过这个对象生成图生.X坐标表球每个学生的ID和姓名,而Y坐标则是不同学生的分数.可以通过这个图表将数据分析呈现出来.



在这篇文章中,我们学习到使用Response对象或Microsoft Excel 11.0 objects provided从Excel中读取数据.文章不可能把所有的功能都一一介绍到,但很多重新的技术点都可以帮助你达到你的目的.

搜索了一下其它相关的文章,估计下面的也对大家有所帮助:

http://topic.csdn.net/u/20080114/17/efd61025-1a59-411e-8fee-763581305d5d.html
方法1:

3种方法
1:

C# code

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();



2:使用MS的组件和服务

System.Data.DataTable dt = CreateExcelTable();
            if (dt.Rows.Count <= 0)
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "");
                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();



3:Excel的XML格式

C# code

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("");
            sbRowsText.Append("            //设置超链接地址
            sbRowsText.Append("http://www.126.com/");
            sbRowsText.Append("\">View
");
            sbRowsText.Append("");
            //设置内容
            sbRowsText.Append("Content");
            sbRowsText.Append("
");
            sbRowsText.Append("
");

            //保存完整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();
        }
    }
}




评论: 0 | 引用: 0 | 查看次数: 6667
发表评论
昵 称:
密 码: 游客发言不需要密码.
邮 箱: 邮件地址支持Gravatar头像,邮箱地址不会公开.
网 址: 输入网址便于回访.
内 容:
验证码:
选 项:
虽然发表评论不用注册,但是为了保护您的发言权,建议您注册帐号.
字数限制 30 字 | UBB代码 关闭 | [img]标签 关闭