[原]用Excel做报表的试验

2009年4月26日星期日

[原]用Excel做报表的试验

 

写这篇文章之前，先提几个问题，希望高手留言指点、讨论：


1、              水晶报表(Crystal Reports)的表头能不能冻结，即：记录多的时候，记录翻页而报表的头部持久显示；如果能怎么实现。


2、              水晶报表在设计的时候表头字段是拖上去的，如果要做一个报表定制工具，这个表头能用程序控制吗？(非vs2005自带的水晶报表)


3、              OWC组建在用vs2005做Web开发的时候为什么添加不到工具栏，而vs2003可以，vs2005的Winform程序也可以。


正是前面提到的问题1和问题2无法解决，现在要把整个系统中的水晶报表换成用Excel实现，Excel实现也存在一些问题，有难度，首先是报表表头的样式的控制，当然这我们可以用宏，但宏是不安全的，所有人都知道，不用宏就只能用VBA语句了，这个对我来说是比较陌生；其次是数据的统计和图表的显示；最后就是用户根据需求自行定制报表，这个灵活性太大，整个模型中数据表就近400张，组织管理细节太多。


先完成了一个小试验，分享一下吧，试验的内容包括：


1、 用简单的VBA语句给Excel中写数据。


2、 将Excel放置在Web页面上来操作。


3、 通过不同框架传值来更新Web页面上的Excel数据内容。


4、 控制输出上下标格式的数据。


5、 更新Excel内容时强制结束前一个Excel进程（同时也会结束用户进程，是本实例的一个bug）。


步骤：


　　一、新建一个asp.net网站，添加一个纯Html页面Default.htm，在页面上定义两个框架，如下：



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head>

    <title>无标题页</title>

</head>

<frameset rows ="30,80%"> <!--border="0" framespacing="0" frameborder="1"-->

        <frame name="Input" marginwidth="0" marginheight="0" src="Input.aspx" scrolling="no"/>

        <frame name="Output" src="Output.aspx" scrolling="no" />

</frameset>

<body>

</body>

</html>







二、              在添加一个输入的aspx页面：Input.aspx。在页面上放置一个DropDownList，命名为DDL_Time,和一个Button命名为BN_Query，在cs文件中写下面代码：




using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;



using System.Text;



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

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            DDL_Time.Items.Add("20080415");

            DDL_Time.Items.Add("20080416");

            DDL_Time.SelectedIndex = 0;

        }

    }

    protected void BN_Query_Click(object sender, EventArgs e)

    {

        Location("Output.aspx?Time=" + DDL_Time.SelectedItem.ToString());

    }



    /// <summary>

    /// 页面重载

    /// </summary>

    public void Location(string sPath)

    {

        StringBuilder sb = new StringBuilder();

        sb.Append("<script language=\"javascript\"> \n");

        sb.Append("parent.frames['Output'].location.href='" + sPath + "';");

        sb.Append("</script>");

        System.Web.HttpContext.Current.Response.Write(sb.ToString());



    }

}




　　三、再做一个Excel报表的模板，命名为Nomalr.xls，建一个简单的数据库，结构如下：




 　　 四、 在添加一个输出的aspx页面：Output.aspx，在页面上放置一个label，来显示input框架传递过来的参数，在cs文件中添加代码如下：




using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;



using Excel = Microsoft.Office.Interop.Excel;

using System.Reflection;

using System.Data.OleDb;

using System.IO;

using System.Diagnostics;



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

{

    OleDbConnection Olecn;

    OleDbCommand OleCamm;

    DataTable DT = new DataTable();



    private string StrTime = "";

    protected void Page_Load(object sender, EventArgs e)

    {

        //接收Input框架传递的参数

        if (!IsPostBack)

        {

            StrTime = Request.QueryString["Time"];

            if (StrTime != null)

                Label1.Text = "时间：" + StrTime;

        }



        //在页面加载的时候将数据取出

        if (ConnectionDataBase())

        {

            OleCamm = new OleDbCommand();

            OleCamm.Connection = Olecn;

            Olecn.Open();

            OleCamm.CommandText = "select * from pl where date = " + "'" + StrTime + "'";

            OleDbDataAdapter OleDAdp = new OleDbDataAdapter(OleCamm);

            OleDAdp.Fill(DT);

            Olecn.Close();

        }

        Exceltest();

    }

    

    /// <summary>

    /// 连接数据库

    /// </summary>

    /// <returns></returns>

    private bool ConnectionDataBase()

    {

        try

        {

            string DataSource = Server.MapPath("~") + "\\App_Data\\Excel.mdb";

            Olecn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+DataSource);

            Olecn.Open();

        }

        catch (Exception e1)

        {

            Response.Write(e1.Message);

            return false;

        }

        finally

        {

            Olecn.Close();

        }

        return true;

    }



    /// <summary>

    /// 对Excel的操作

    /// </summary>

    private void Exceltest()



    {

        //在创建Excel Application前先将已经打开的Excel资源释放掉

        EndExcelProcesse();



        //指定模板文件

        FileInfo mode = new FileInfo(Server.MapPath("~") + "\\Nomarl.xls");



        //打开复制后的文件X

        object missing = Missing.Value;

        Excel.Application myExcel = new Excel.Application();



        //打开模板文件

        myExcel.Application.Workbooks.Open(mode.FullName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);



        //选中有数据的Cells

        Excel.Workbook myBook = myExcel.Workbooks[1];

        Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[1];

        Excel.Range r = mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[DT.Rows.Count + 2, DT.Columns.Count - 3]);

        r.Select();



        //不单独显示Excel，最后在IE中显示

        myExcel.Visible = false;



        //第一行为报表的标题

        myExcel.Cells[1, 1] = "用模板导出的报表";



        //逐行写入数据，数组中第一行为报表的列标题

        for (int i = 0; i < DT.Columns.Count - 3; i++)

        {

            myExcel.Cells[2, 1 + i] = DT.Columns[i].Caption; ;

        }

        

        //为报表填充数据并设置显示上下标格式

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

        {

            for (int j = 0; j < DT.Columns.Count - 4; j++)

            {

                myExcel.Cells[3 + i, 1 + j] = DT.Rows[i][j];

            }

            string a = DT.Rows[i][DT.Columns.Count-4].ToString();

            string b = DT.Rows[i][DT.Columns.Count-3].ToString();

            string c = DT.Rows[i][DT.Columns.Count -2].ToString();

            myExcel.Cells[3 + i, DT.Columns.Count - 3] = a + b + c;



            //控制输出样式为下标

            mySheet.get_Range(mySheet.Cells[i + 3, DT.Columns.Count - 3], mySheet.Cells[i + 3, DT.Columns.Count - 3]).get_Characters(a.Length + 1, b.Length).Font.Subscript = true;



            //控制输出样式为上标

            mySheet.get_Range(mySheet.Cells[i + 3, DT.Columns.Count - 3], mySheet.Cells[i + 3, DT.Columns.Count - 3]).get_Characters(a.Length + b.Length + 1, c.Length).Font.Superscript = true;

            mySheet.Columns.AutoFit();

        }



        //在当前目录下指定一个临时文件

        string FileName = Server.MapPath("~") + "\\Temp.xls";

        if (File.Exists(FileName))

        {

            File.Delete(FileName);

        }

        myExcel.Save(FileName);

        mySheet.Cells.Clear() ;

        //设置不出现保存提示框

        myBook.Saved = true;

        myExcel.Application.Workbooks.Close();



        //将Excel文件嵌入在IE里面，也可以在aspx页面用

        //<iframe id="myExcelHtml" src ="E:\\练习\\excell\\WebSite2\\Temp.xls" width="100%" height="100%" runat ="server"></iframe>

        //标签来嵌入

        Response.ClearContent();

        Response.ClearHeaders();

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

        Response.AddHeader("Content-Disposition", "inline;filename='我的文件'");

        Response.WriteFile(FileName);

        Response.Flush();

        Response.Close();

    }



    /// <summary>

    /// 当报表数据更新时,先强制结束前一个报表的数据源

    /// 这种方法会同时杀死掉用户的excel进程

    /// </summary>

    protected void EndExcelProcesse()

    {

        try

        {    

            Process[] myProcesses = Process.GetProcessesByName("excel");

            foreach (Process instance in myProcesses)

            {

                instance.Kill();   //强制终止    

            }

        }

        catch { }

        //在网上查的用下面的方法结束Excel进程表较好,但一直没有效果

        //try

        //{

        //    System.Runtime.InteropServices.Marshal.ReleaseComObject(r);

        //    System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);

        //    System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);

        //    System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);

            

        //    myBook = null;

        //    mySheet = null;

        //    myExcel = null;

        //    r = null;

        //    GC.Collect();

        //}

        //catch { }

    }



    

}







整个试验的程序结构为：






运行后的结果：










如需要源代码，请留下联系方式。



 
Tag标签: VBA,Excel嵌入网页,结束进程,Excel中上下标的显示

发帖者 文博客 时间： 04:40
标签：

0 评论:

发表评论

订阅： 博文评论 (Atom)


~ 返回首页