?? ?連續這么多天持續寫SQL，晚上找資料研究一下Microsoft Office Web Components（續上一篇Excel的文章）調節一下自己，和上一篇文章的主題一樣，這篇主要研究OWC做報表的方法。先看一下示例的效果：

?

?? 一、主要內容：

1、?OWC組件在web頁面展示的幾種方法。

2、?OWC數據的填充。

3、?OWC顯示格式的設置。

4、?在OWC中的電子表格類中填充數據的方法。

5、?OWC中顯示數據格式的控制。

二、要點總結：

1、?OWC組件在web頁面展示的方法。

A、將數據導出成Excel臨時文件，再將臨時文件呈現到Web頁面，代碼如下：

?Sheet.Export(FileName,?OWC11.SheetExportActionEnum.ssExportActionNone,?OWC11.SheetExportFormat.ssExportHTML);//Sheet為OWC11中SpreadsheetClass類的對象
????????Response.ClearContent();
????????Response.ClearHeaders();
????????Response.ContentType?=?"application/vnd.ms-excel";
????????Response.AddHeader("Content-Disposition",?"inline;filename='我的文件'");
????????Response.WriteFile(FileName);
????????Response.Flush();
????????Response.Close();

B、?不導出到本地臨時文件，直接從內存加載到Web頁面，代碼如下：

Response.Clear();
????????Response.Buffer?=?true;
????????Response.ContentEncoding?=?System.Text.Encoding.Default;
????????//Response.Charset?=?"utf-8";
????????Response.ContentType?=?"application/vnd.ms-excel";
????????Response.Write(Sheet.HTMLData);
????????this.EnableViewState?=?false;
?Response.End();

?????? A、B效果如圖：

?

C、標簽：

<object?id="Spreadsheet1"?classid="clsid:0002E559-0000-0000-C000-000000000046"?name="Spreadsheet1">?????? 效果如圖：

????? 2、在OWC中的電子表格類中填充數據的方法：
OWC11.SpreadsheetClass?Sheet?=?new?OWC11.SpreadsheetClass();
//第一行為報表的標題
????????Sheet.ActiveCell[1,?1]?=?"報表的標題";

????????//逐行寫入數據，數組中第一行為報表的列標題
????????for?(int?i?=?0;?i?<?DT.Columns.Count?-?3;?i++)
????????{
????????????Sheet.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++)
????????????{
????????????????Sheet.ActiveCell[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();
????????????Sheet.ActiveCell[3?+?i,DT.Columns.Count?-?3]?=?a?+?b?+?c;
????????????Sheet.Columns.AutoFit();
????}
3、????OWC中數據顯示格式的控制(沒找到控制上下標格式數據的方法)：
以下是對輸出Excel的格式控制

???? 運行效果如下：

???3、圖表ChartSpace類的例子，網上比較多，直接看綜合示例，就不另作說明了。

三、綜合示例：

? 注釋很全，就不加說明了。

???????? 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?System.Reflection;
using?System.Data.OleDb;
using?System.IO;
using?System.Diagnostics;
//using?OWC11?=?Microsoft.Office.Interop.Owc11;

public?partial?class?Output?:?System.Web.UI.Page
{
????//取數據的全局變量
????OleDbConnection?Olecn;
????OleDbCommand?OleCamm;
????DataTable?DT?=?new?DataTable();

????//實例化OWC11
????OWC11.SpreadsheetClass?Sheet?=?new?OWC11.SpreadsheetClass();

????//跨框架傳參用
????private?String?StrTime?=?"";

????//導出電子表格的全路徑
????public?String?FileName;

????//導出圖片的全路徑
????public?String?PicPath;

????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();
????????ChartsTest();
????}
????
????///?<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>
????///?OWC中SpreadsheetClass類的操作?
????///?</summary>
????private?void?Exceltest()

????{
????????//將已經打開的Excel資源釋放掉
????????EndExcelProcesse();

????????//第一行為報表的標題
????????Sheet.ActiveCell[1,?1]?=?"報表的標題欄";

????????//逐行寫入數據，報表的列標題
????????for?(int?i?=?0;?i?<?DT.Columns.Count?-?3;?i++)
????????{
????????????Sheet.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++)
????????????{
????????????????Sheet.ActiveCell[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();
????????????Sheet.ActiveCell[3?+?i,DT.Columns.Count?-?3]?=?a?+?b?+?c;
????????????Sheet.Columns.AutoFit();
????????}

以下是對輸出Excel的格式控制

????????FileName?=?Server.MapPath("~")?+?"\\Temp.html";
????????if?(File.Exists(FileName))
????????{
????????????File.Delete(FileName);
????????}
????????//將Sheet導出成html格式的數據，在前臺頁面顯示用
????????Sheet.Export(FileName,?OWC11.SheetExportActionEnum.ssExportActionNone,?OWC11.SheetExportFormat.ssExportHTML);
????????/*
?????????*?可以直接用下面兩種方法在頁面上顯示，用下面的方式顯示時，導出的文件格式為：.xls
????????Response.ClearContent();
????????Response.ClearHeaders();
????????Response.ContentType?=?"application/vnd.ms-excel";
????????Response.AddHeader("Content-Disposition",?"inline;filename='我的文件'");
????????Response.WriteFile(FileName);
????????Response.Flush();
????????Response.Close();

????????Response.Clear();
????????Response.Buffer?=?true;
????????Response.ContentEncoding?=?System.Text.Encoding.Default;
????????//Response.Charset?=?"utf-8";
????????Response.ContentType?=?"application/vnd.ms-excel";
????????Response.Write(Sheet.HTMLData);
????????this.EnableViewState?=?false;?
????????Response.End();
????????*/

????}

????///?<summary>
????///?繪制圖表
????///?</summary>
????private?void?ChartsTest()
????{
????????//創建ChartSpace對象來放置圖表
????????OWC11.ChartSpace?objCSpace?=?new?OWC11.ChartSpaceClass();

????????//在ChartSpace對象中添加圖表，Add方法返回chart對象
????????OWC11.ChChart?objChart?=?objCSpace.Charts.Add(0);

????????//指定圖表的類型。類型由OWC11.ChartChartTypeEnum枚舉值得到
????????objChart.Type?=?OWC11.ChartChartTypeEnum.chChartTypeColumnClustered;

????????//指定圖表是否需要圖例
????????objChart.HasLegend?=?true;

????????//給定標題
????????objChart.HasTitle?=?true;
????????objChart.Title.Caption?=?"柱狀圖";

????????//給定x,y軸的圖示說明
????????objChart.Axes[0].HasTitle?=?true;
????????objChart.Axes[0].Title.Caption?=?"X：?數量";
????????objChart.Axes[1].HasTitle?=?true;
????????objChart.Axes[1].Title.Caption?=?"Y：?月份";

????????//計算數據
????????/*categories?和?values?可以用tab分割的字符串來表示*/
????????string?strSeriesName?=?"圖例?1";
????????string?strCategory?=?"1"?+?'\t'?+?"2"?+?'\t'?+?"3"?+?'\t'?+?"4"?+?'\t'?+?"5"?+?'\t'?+?"6"?+?'\t';
????????string?strValue?=?"9"?+?'\t'?+?"8"?+?'\t'?+?"4"?+?'\t'?+?"10"?+?'\t'?+?"12"?+?'\t'?+?"6"?+?'\t';

????????//添加一個series
????????objChart.SeriesCollection.Add(0);

????????//圖例
????????objChart.SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimSeriesNames,?(int)OWC11.ChartSpecialDataSourcesEnum.chDataLiteral,?strSeriesName);

????????//X軸
????????objChart.SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimCategories,?(int)OWC11.ChartSpecialDataSourcesEnum.chDataLiteral,?strCategory);

????????//柱面
????????objChart.SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimValues,?(int)OWC11.ChartSpecialDataSourcesEnum.chDataLiteral,?strValue);
????????//輸出成圖片文件.
????????PicPath?=?Server.MapPath("~")?+?"\\test.htm";
????????objCSpace.ExportPicture(PicPath,?"jpg",?600,?350);

????????//FileStream?fs?=?new?FileStream(PicPath,?FileMode.Open,?FileAccess.Read);
????????//byte[]?bytes?=?new?byte[fs.Length];
????????//fs.Read(bytes,?0,?bytes.Length);
????????//fs.Close();
????????//MemoryStream?ms?=?new?MemoryStream(bytes);

????????//System.Drawing.Image?img?=?System.Drawing.Image.FromStream(ms);

????????//Response.ContentType?=?"image/jpg";
????????//Response.Write(img);
????????//Response.Write("<img?src="+PicPath?+"/>");
????}


????///?<summary>
????///?當報表數據更新時,先強制結束前一個報表的數據源
????///?這種方法會同時殺死掉用戶的excel進程
????///?</summary>
????protected?void?EndExcelProcesse()
????{
????????try
????????{
????????????Process[]?myProcesses?=?Process.GetProcessesByName("excel");
????????????foreach?(Process?instance?in?myProcesses)
????????????{
????????????????instance.Kill();?//強制終止????
????????????}
????????}
????????catch?{?}
????}
????/*
?????*?用Response.Write在頁面顯示時，頁面卸載時刪除.xls文件
????protected?void?Page_UnLoad(object?sender,?EventArgs?e)
????{
????????EndExcelProcesse();
????????string?FileName?=?Server.MapPath("~")?+?"\\Temp.xls";
????????if?(File.Exists(FileName))
????????{
????????????File.Delete(FileName);
????????}
????}
????*/
}

??? ASPX文件：
<%@?Page?Language="C#"?AutoEventWireup="true"?CodeFile="Output.aspx.cs"?Inherits="Output"?%>

<!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?runat="server">
????<title>無標題頁</title>
</head>
<body>
????<form?id="form1"?runat="server">
????????<div>
????????
????????</div>
????????<asp:Label?ID="Label1"?runat="server"?Text=""></asp:Label><p/>
?????????<object?classid="clsid:0002E559-0000-0000-C000-000000000046"?width="600"?id="Spreadsheet1"?height="200"?align?="middle">
????????????<param?name="DataType"?value="HTMLURL"/>
????????????<param?name="HTMLURL"?value="<%=FileName%>"/>
????????????<table?width="100%"?cellpadding="0"?cellspacing="0"?border="0">
????????????????<tr>
????????????????????<td?bgcolor="#336699"?height="25"?width="10%">&nbsp;</td>
????????????????????<td?bgcolor="#666666"?width="85%">
????????????????????????<font?face="宋體"?color="white"?size="4">
????????????????????????????<b>&nbsp;?缺少?Microsoft?Office?Web?Components</b>
????????????????????????</font>
????????????????????</td>
????????????????</tr>
????????????????<tr>
????????????????????<td?bgcolor="#cccccc"?width="15">&nbsp;</td><td?bgcolor="#cccccc"?width="500px"><br>
?????????????????????<font?face="宋體"?size="2">此網頁要求?Microsoft?Office?Web?Components。
?????????????????????????<p?align="center">
?????????????????????????????<a?href="C:/IUware?Online/Microsoft?Office?Professional?Enterprise?Edition?2003/files/owc11/setup.exe">?單擊此處安裝?Microsoft?Office?Web?Components。
??????????????????????????????</a>.
??????????????????????????</p>
??????????????????????</font>
??????????????????????<p>
??????????????????????<font?face="宋體"?size="2">?此網頁同時要求?Microsoft?Internet?Explorer?5.01?或更高版本。</p>
????????????????????????<p?align="center"/><a?href="http://www.microsoft.com/windows/ie/default.htm">?單擊此處安裝最新的?Internet?Explorer</a>.
??????????????????????</font><br>&nbsp;
????????????????????</td>
??????????????????</tr>
??????????????</table>
??????</object>
??????<p?/>
??????<img?src="test.jpg""?alt="OWC圖表例子!"??/>
????</form>
</body>

</html>

??? 四、缺點：

??????客戶端必須安裝?Microsoft Office Web Components，它可隨?Office 2003?一起安裝，如果用戶的公司具有?Office 2003?站點許可證，則可以通過公司的?Intranet?? 下載?Office Web?組件，用戶還必須使用?Microsoft Internet Explorer 5.01 (Service Pack 2)?或更高版本。

總結

以上是生活随笔為你收集整理的OWC做电子表格和图表的试验的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。