Excel与OWC做报表总结

最新推荐文章于 2021-06-16 01:25:23 发布

zjl5211314

最新推荐文章于 2021-06-16 01:25:23 发布

阅读量712

点赞数

分类专栏： .NET 技术文章标签： excel 报表 microsoft spreadsheet office vba

本文链接：https://blog.csdn.net/zjl5211314/article/details/6161707

版权

.NET 技术专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1.1. Excel试验：

A. Excel嵌入网页的方法。

1. 在后台用流的方式返回给前台页面展现；

2. 在后台讲Excel格式数据定好保存到本地，在前台页面用html标签加载展示；

B. Excel显示格式的控制。

1. Excel单元格格式设置，默认为常规类型；

2. 上下标格式的控制；

3. 单元格水平垂直对齐方式；

4. 单元格边框设置；

5. 单元格合并；

6. 字体设置，包括字体的颜色、大小、粗体、斜体、字体、下划线等；

7. 冻结行列；

8. 公式栏的显示与隐藏；

9. 行标题和列表题的显示与隐藏；

10. 网格的显示与隐藏；

11. 水平滚动条和垂直滚动条的显示与隐藏；

12. Sheet页的显示与隐藏；

C. 向Excel中填充数据，并导出为临时文件。

1.2. OWC试验：

A. OWC组件在web页面展示的方法。

1. 将OWC中Sheet导出为本地Excel文件，再将导出的文件呈现到Web页面；

2. 后台把配置好的Sheet直接以流文件方式返回到Web页面展现；

3. 用html标签将OWC展现在Web页面，加载后台程序导出到本地的文件（xml、html、csv格式）来显示数据；

B. 在OWC中的电子表格类中填充数据的方法。

C. OWC中显示数据格式的控制。

1. 电子表格外观控制：工具栏的显示、Office图标的显示、列标题的显示、行标题的显示、水平滚动条的显示、垂直滚动条的显示、网格的显示、网格颜色设置；

2. 单元格水平对齐方式；

3. 单元格垂直对齐方式；

4. 单元格合并；

5. 单元格字体设置：字体、字形、字号、字体颜色、字体加粗、下划线（多种样式）；

6. 单元格边框设置；

2. 主要模块

2.1. Excel

1、Excel嵌入网页的方法：

A、将后台定制好的Excel文件在后台用流的方式返回给前台页面展现，代码如下：

Response.ClearContent();

Response.ClearHeaders();

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

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

Response.WriteFile(FileName);//FileName为Excel文件所在地址

Response.Flush();

Response.Close();

运行效果：整个页面都充满了Excel。

B、在前台页面框架中直接加在，代码：

运行效果：可随意控制Excel在页面的显示位置。

2、 Excel显示格式控制。

A、Excel单元格格式设置，默认为常规类型；

代码：

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

//设置单元格格式为文本类型，文本类型可设置上下标

r.NumberFormat = "@";

//设置单元格格式为数值类型，小数点后2位

r.NumberForma = "0.00_ "

//设置单元格格式为货币类型，小数点后2位

r.NumberForma = "￥#,##0.00;￥-#,##0.00"

//设置单元格格式为会计专用类型，小数点后2位

r.NumberForma = _"_ ￥* #,##0.00_ ;_ ￥* -#,##0.00_ ;_ ￥* ""-""??_ ;_ @_ "

//设置单元格格式为日期类型

r.NumberForma = "yyyy-m-d"

//设置单元格格式为时间类型

r.NumberForma = "[$-F400]h:mm:ss AM/PM"

//设置单元格格式为百分比类型，小数点后2位

r.NumberForma = "0.00%"

//设置单元格格式为分数类型，分母为一位数

r.NumberForma = "# ?/?"

//设置单元格格式为科学技术类型，小数位数为2

r.NumberForma = "0.00E+00"

//设置单元格格式为特殊类型

r.NumberForma = "000000"

B、上下标格式的控制；

代码：

//控制输出样式为下标

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;

C、 单元格水平垂直对齐方式；

代码：

//单元格水平，垂直居中

r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

r.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

上面代码中，枚举XLHAlign的值还有：

// 右对齐

xlHAlignRight,

// 左对齐.

xlHAlignLeft,

// 两端对齐.

xlHAlignJustify,

// 分散对齐(缩进)

xlHAlignDistributed,

// 居中对齐

xlHAlignCenter,

// 依照数据类型对齐,常规

xlHAlignGeneral,

// 填充

xlHAlignFill,

// 跨列对齐.

xlHAlignCenterAcrossSelection = 7,

枚举XLVAlign的值还有：

// 靠上对齐

xlVAlignTop,

//两端对齐.

xlVAlignJustify = -4130,

//分散对齐.

xlVAlignDistributed,

//居中对齐.

xlVAlignCenter,

//靠下对齐.

xlVAlignBottom = -4107,

D、 单元格边框设置；

代码：

//设置边框

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

r.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

枚举XlLineStyle中还有下面线形：

//没边框线

xlLineStyleNone,

//双线.

xlDouble,

//点状线.

xlDot,

//虚线.

xlDash,

//连续线.

xlContinuous,

//点线交互型

xlDashDot,

//两点一线型

xlDashDotDot,

//斜线.

xlSlantDashDot,

E、单元格合并

用get_Range方法获取要合并的单元格，再设置MergeCells属性的值进行合并。

代码：

//合并单元格

myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1,4]).MergeCells = true;

F、字体设置

先用get_Range方法选中要设置字体的某个单元格或者或者直接用get_Characters方法直接选中要设置的字符进行设置；

代码：

//加粗字体

myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, 1]).Font.Bold = true;

//设置字体大小

myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Size = 16;

//设置字体的颜色

myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, 1]).Font.ColorIndex = 3;

//设置字体

myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, 1]).Font.Name = "隶书";

//设置成斜体

myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, 1]).Font.Italic = true;

//设置下划线

myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, 1]).Font.Underline = true;

G、冻结行列

用get_Range方法获取单元格，再设置其Freezepanes属性为true，就把该单元格右上方的行和列都冻结了，取消冻结将其值设为false即可。

//冻结行列

myExcel.get_Range(myExcel.Cells[3,1],myExcel.Cells[3,1]).Activate();

myExcel.ActiveWindow.FreezePanes = true;

H、 公式输入栏的隐藏

//设置是否显示Excel公式输入栏，默认为true

myExcel.DisplayFormulaBar = false;

I、 列标题与行标题的隐藏

//设置是否显示行和列的标题，默认为true

myExcel.ActiveWindow.DisplayHeadings = false;

J、网格的隐藏

//设置是否显示网格，默认为true

myExcel.ActiveWindow.DisplayGridlines = false;

K、 水平、垂直滚动条的隐藏

//设置是否显示水平滚动条

myExcel.ActiveWindow.DisplayHorizontalScrollBar = false;

//设置是否显示垂直滚动条

myExcel.ActiveWindow.DisplayVerticalScrollBar = false;

L、Sheet页的隐藏

//设置是否显示Sheet页

myExcel.ActiveWindow.DisplayWorkbookTabs = false;

经过格式设置以后，展示在页面上的效果如下图：

3、向Excel中填充数据，并保存为临时文件

代码：

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

}

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

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

2.2. OWC组件

1、 OWC组建在Web页面的展现方法；

A、将OWC中Sheet导出为本地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、后台把配置好的Sheet直接以文件方法返回到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();

C、用html标签将OWC展现在Web页面，加载后台程序导出到本地的文件（xml、html、csv格式）来显示数据；

代码如下：

</object>

2、为OWC中的SpreadSheet类中填充数据的方法：

获取Sheet的Cell或者ActiveCell，直接给里面填充数据，代码如下：

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中数据显示格式的控制：

A、电子表格外观控制：

是否显示工具栏默认值为 true

Sheet.DisplayToolbar = false;

取消显示Office图标

Sheet.DisplayOfficeLogo = false;

是否显示列标题，默认是true

Sheet.DisplayColumnHeadings = false;

是否显示行标题，默认是true

Sheet.DisplayRowHeadings = false;

是否显示水平滚动条，默认为true

Sheet.DisplayHorizontalScrollBar = false;

是否显示垂直滚动条，默认为true

Sheet.DisplayVerticalScrollBar = false;

电子表格是否显示网格，默认为true

Sheet.DisplayGridlines = false;

设置行的颜色

for (int i = 1; i < DT.Columns.Count - 2; i += 2)

{

Sheet.get_Range(Sheet.Rows.Cells[i, 1], Sheet.Rows.Cells[i, DT.Columns.Count - 3]).Interior.set_ColorIndex(2);

}

做了上面的设置，效果如下图：

B、单元格水平对齐

用Get_Range方法获取要设置的单元格，用set_HorizontalAlignment方法设置对齐方式；

代码如下：

//单元格水平居中

Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[DT.Rows.Count + 2, DT.Columns.Count - 3]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);

枚举XLHAlign的值分别是：

//右对齐：

xlHAlignRight,

//左对齐：

xlHAlignLeft,

//居中对齐：

xlHAlignCenter,

//常规

xlHAlignGeneral1,

//填充对齐：

xlHAlignFill

C、单元格垂直对齐

用Get_Range方法获取要设置的单元格，用set_VerticalAlignment方法设置对齐方式；

代码如下：

//单元格垂直居中

Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[DT.Rows.Count + 2, DT.Columns.Count - 3]).set_VerticalAlignment(OWC11.XlVAlign.xlVAlignCenter);

枚举XLHAlign的值分别是：

// 靠上对齐

xlVAlignTop,

// 居中对齐

xlVAlignCenter,

// 靠下对齐

xlVAlignBottom

D、单元格合并；

用get_Range方法先获取要合并的单元格，再用set_MergeCells方法进行合并。

代码如下：

//标题行合并单元格

Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[1, DT.Columns.Count - 3]).set_MergeCells(true);

E、字体设置；

用get_Range方法先获取要合并的单元格，设置该单元格字体：字体、字形、字号、字体颜色、字体加粗、带下划线（下划线有多种样式）等。

代码如下：

//标题行加粗字体

Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[1, 1]).Font.set_Bold(true);

//设置字体大小

Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[1, 1]).Font.set_Size(16);

//设置字体颜色

Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[1, 1]).Font.set_ColorIndex(3);

//设置字体

Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[1, 1]).Font.set_Name("隶书");

//设置字体为斜体

Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[1, 1]).Font.set_Italic(true);

//设置字体下划线

Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[1, 1]).Font.set_Underline(OWC11.XlUnderlineStyle.xlUnderlineStyleDouble);

枚举XLUnderlineStyle的值分别为：

xlUnderlineStyleNone,

xlUnderlineStyleDouble,

xlUnderlineStyleSingle,

xlUnderlineStyleSingleAccounting,

xlUnderlineStyleDoubleAccounting,

F、添加边框

用get_Range方法先获取要添加边框的单元格，用Borders.set_LineStyle方法设置边框的类型即可。

代码如下：

//画边线

Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[DT.Rows.Count + 2, DT.Columns.Count - 3]).Borders.set_LineStyle(OWC11.XlLineStyle.xlContinuous);

边框类型有一下几种：

xlLineStyleNone,

xlDot,

xlDash,

xlContinuous,

xlDashDot,

xlDashDotDot,

运行效果如下：

3. 限制条件说明

3.1. Excel

服务端需要安装Microsoft Office Excel 2003，Microsoft Office Excel 2003 SP1和Visual Studio 2005 Tools for Office Runtime（vstor.exe），客户端需要安装Microsoft Office Excel 2003，Microsoft Internet Explorer 5.01 (Service Pack 2) 或更高版本。

3.2. OWC

服务端和客户端都必须安装 Microsoft Office Web Components，它可随 Office 2003 一起安装，或者，如果用户的公司具有 Office 2003 站点许可证，则可以通过公司的 Intranet 下载 Office Web 组件，用户还必须使用 Microsoft Internet Explorer 5.01 (Service Pack 2) 或更高版本。

4. 结果分析

4.1. Excel

Excel是一款功能强大、操作方便的电子表格处理软件，使用Excel做报表可以满足我们报表的所有需求，包括：表格的样式、尺寸、颜色的设置；字体样式、颜色、大小、对齐方式、上下标的设置；表头设置、表头冻结、单元格合并；数据统计；打印等。另外在程序实现上还有一下优点：

1、使用VBA简化程序设计过程。VBA应用程序是“寄生于”Excel应用程序上的，因此，Excel的一些基本功能和函数等都可以在VBA中直接使用。

2、可以充分利用Excel中的宏。Excel中的宏录制器允许记录一系列的操作，并且将这些操作转换为VBA代码，因此，一方面可以利用宏录制器来建立应用程序的基础，另一方面，在不能确定如何编写一系列的步骤时，可以进行录制，再查看代码。

3、与数据库交互。Excel能够使用多种类型的数据库，能够对数据库中的数据进行访问，同样也能够将Excel分析的结构导入到数据库中。

但同时，用Excel做报表也有一些不足，由于IE安全策略，当网站尝试下载除图片、音乐、纯文本文件以外其他格式文件时，IE将弹出一条“文件下载”的安全警告，因为要下载的文件上可能包含计算机代码(可能是程序或计算机病毒)，这样我们在IE上展现Excel报表时，会出现如下图提示，美中不足。

解决方案：

1、将Excel封装在ActiveX里面，这样可以避免每次打开系统甚至每次刷新页面的时候弹出“文件下载”的对话框，但是在第一次打开的时候需要下载安装ActiveX。

2、在客户端机器上进行设置：打开“我的电脑”à“工具”（菜单）à“文件类型”（属性页），在“已注册的文件类型”中选择“扩展名”为“XLS”，“文件类型”为“Microsoft Excel 工作表”，点击“高级”按钮后，如下图：

在“编辑文件类型”对话框中，“下载后确认打开”复选框不被选中表示直接打开，被选中表示弹出对话框，去掉勾点击确定。这样设置会影响客户端机器电子邮件.XLS格式附件的下载。

4.2. OWC

OWC是Office Web Compents的缩写，即Microsoft的Office Web组件，包含SpreadSheet组件、Chart组件、PioTable组件和Data Source组件。我们报表使用的是SpreadSheet组件，使用它可以友好、方便的将电子表格展现在web页面，它同样可以满足我们一下需求：表格的样式、尺寸、背景颜色的设置；字体样式、颜色、大小、对齐方式的设置；表头设置、表头冻结、单元格合并；数据的统计；打印等。

在程序实现上OWC和Excel是相通的，程序设计过程也简单，可以直接调用Excel的方法，在不确定代码如何实现时也可以录制宏，查看代码。

缺点是没法实现上下标数据的显示，既是将带有上下标格式数据的Excel导入OWC的电子表格，上下标格式也失效，此问题暂没找到解决方法。