c-操作excel总结.doc

C#操作Excel总结

Excel 基本操作
查看Excel中宏的方法如下：
ALT+F11
0. 导入命名空间： 
view source

print?
1
using Microsoft.Office.Core;
2
using Microsoft.Office.Interop.Excel;

3
using System.IO;
4
using System.Reflection;

1. 如何打开已有excel文档，或者创建一个新的excel文档 

view source

print?
1
Application app = new Application();
2
Workbooks wbks = app.Workbooks;

3
_Workbook _wbk = wbks.Add(xxx);

若打开已有excel，把“xxx”替换成该excel的文件路径；
注：若新建一个excel文档，“xxx”替换成true即可；不过这里新建的excel文档默认只有一个sheet。

2. 取得、删除和添加sheet

view source

print?
1
Sheets shs = _wbk.Sheets;

2.1取得：

view source

print?
1
//i是要取得的sheet的index
2
_Worksheet _wsh = (_Worksheet)shs.get_Item(i)

2.2 删除：
view source

print?
1
//删除sheet必须的设置
2
app.DisplayAlerts = false;

3
_wsh.Delete();
2.3 添加：
view source

print?
1
//a(before)，b(after)：确定添加位置；c：数目；d：类型
2
app.Worksheets.Add(a，b，c，d);
2.4 sheet的重命名
view source

print?
1
_wsh.Name = "xxx";

3. 删除行和列 
3.1 删除行：
view source

print?
1
((Range)_wsh.Rows[3, Missing.Value]).Delete(XlDeleteShiftDirection.xlShiftUp);
3.2 删除列：
view source

print?
1
_wsh.get_Range(
2
_wsh.Cells[1, 2], 

3
_wsh.Cells[_wsh.Rows.Count, 2]).Delete(XlDeleteShiftDirection.xlShiftToLeft
4
);

4. 添加行和列 
4.1 添加行：
view source

print?
1
((Range)_wsh.Rows[11, Missing.Value])
2
.Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
4.2 添加列：
view source

print?
1
_wsh.get_Range(
2
_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1])

3
.Insert(Missing.Value, XlInsertShiftDirection.xlShiftToRight);

5. 单元格操作 
5.1 单元格的取得
view source

print?
1
//获得单元格对象
2
_wsh.Cells[row, cell]
5.2 设置公式
view source

print?
1
//在对应的单元格输入公式即可
2
_wsh.Cells[row, cell] = "=Sum(A1/B1)";
5.3 合并单元格
view source

print?
1
((Range)_wsh.Rows[1, Missing.Value]).Merge(Missing.Value);
5.4 设置行高和列宽
view source

print?
1
((Range)_wsh.Rows[3, Missing.Value]).RowHeight = 5;
2
((Range)_wsh.Rows[3, Missing.Value]).ColumnWidth = 5;
5.5 设置单元格颜色 颜色共有56中，详情请参照附录的[颜色对照表]
view source

print?
1
((Range)_wsh.Rows[1, Missing.Value]).Interior.ColorIndex = 3;
5.6 设置字号
view source

print?
1
((Range)_wsh.Cells[1, "B"]).Font.Size = 8;
5.7 是否设置粗体
view source

print?
1
((Range)_wsh.Rows[1, Missing.Value]).Font.Bold = false;
5.8 单元格/区域、水平垂直居中
view source

print?
1
((Range)_wsh.Cells[2, 1]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
5.9 设置区域边框
view source

print?
1
((Range)_wsh.Cells[3, 3]).Borders.LineStyle = 3;
5.10 设置边框的上、下、左、右线条
view source

print?
01
//左
02
_wsh.get_Range(

03
_wsh.Cells[2, 1], _wsh.Cells[2, 2])
04
.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//

05

06
//右

07
_wsh.get_Range(
08
_wsh.Cells[2, 1], _wsh.Cells[2, 2])

09
.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//
10

11
//上
12
_wsh.get_Range(

13
_wsh.Cells[2, 1], _wsh.Cells[2, 2])
14
.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//下

15

16
//下

17
_wsh.get_Range(
18
_wsh.Cells[2, 1], _wsh.Cells[2, 2])

19
.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;

自动换行

range.WrapText

6. 指定区域的复制 
view source

print?
01
_Worksheet _wsh = (_Worksheet)shs.get_Item(1);//复制选中区域的内容
02

03
Range range = _wsh.get_Range(_wsh.Cells[7, 1], _wsh.Cells[10, _wsh.Columns.Count]);
04

05
range.Select();
06
range.Copy(Type.Missing);

07

08
//选中粘贴的起始位置

09
Range test = ((Range)_wsh.Cells[11, 1]);
10
test.Select();

11

12
//屏蔽掉Alert，默认确定粘贴

13
app.DisplayAlerts = false;
14
test.Parse(Missing.Value, Missing.Value);
注：Type.Missing和Missing.Value，在excel的操作中被视为某些参数的默认值，他们起到的作用很多时候是形式补足参数

7. excel文件的保存，及后续处理 
7.1 文件保存
view source

print?
1
//屏蔽掉系统跳出的Alert
2
app.AlertBeforeOverwriting = false;

3

4
//保存到指定目录

5
SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
注：这个地方只能采用该方法保存，不然在指定路径下保存文件外，在我的文档中也会生成一个对应的副本
7.2 后续处理：退出和释放
view source

print?
1
//_wbk.Close(null, null, null);
2
//wbks.Close();

3
app.Quit();
4

5
//释放掉多余的excel进程
6
System.Runtime.InteropServices.Marshal.ReleaseComObject(app); 

7
app = null;
说明：在application关闭的过程中，通常我们有两种方案：
#直接退出app
#先关闭workbook，然后关闭workbooks，最后在退出app
鉴于这两种方式，或许本质上是一样的(这点需要证明)，但是依据我们软件开发的原则：哪里需要哪里声明，哪里结束哪里释放回收。
既然在直接退出app的时候，我们不清楚workbook和workbooks具体在什么时间关闭，不如在结束的时候直接手动关闭，这样做可以做到资源的快速直接回收；
所以，建议采用先关闭workbook，然后关闭workbooks，最后在退出app。

8. 关于单元格设置域和取得域里需要的数据 
8.1 若单元格已经设置为下拉框
view source

print?
1
//这里的“1，2，3”设置的就是下拉框的值
2
((Range)_wsh.Cells[2, 1])

3
.Validation.Modify(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, Type.Missing, "1,2,3", Type.Missing);
8.2 若单元格还没有设置为下拉框的形式
view source

print?
1
((Range)_wsh.Cells[2, 1])
2
.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, Type.Missing,"1,2,3", Type.Missing);
8.3 取得下拉框域的值
view source

print?
1
string strValue = ((Range)_wsh.Cells[2, 1]).Validation.Formula1;
注：若在excel模板中通过有效性设定了下拉框的值，strValue得到的将会是excel里的公式，需将其转换, 取得strValue后，可以根据其索引得到你需要的数值；

9. 隐藏行和隐藏列 
9.1 隐藏行
view source

print?
1
_wsh.get_Range(_wsh.Cells[19, 1], _wsh.Cells[22, 1]).EntireRow.Hidden = true;
9.2 隐藏列
view source

print?
1
_wsh.get_Range(_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1])
2
.EntireColumn.Hidden = true;
10．锁定指定区域
Range.Locked

11.Excel中插入图片

1.using System;
2.using System.Collections.Generic;
3.using System.Text;
4.//using Excel = Microsoft.Office.Interop.Excel;
5.using Excel;
6.using System.Windows.Forms;
7.
8.
9. namespace ExcelReport
10. {
11. class InsertPicToExcel
12. {
13. /**//// <summary>
14. /// 打开没有模板的操作。
15. /// </summary>
16. public void Open()
17. {
18. this.Open(String.Empty);
19. }
20.
21. /**//// <summary>
22. /// 功能：实现Excel应用程序的打开
23. /// </summary>
24. /// <param name="TemplateFilePath">模板文件物理路径</param>
25. public void Open(string TemplateFilePath)
26. {
27. //打开对象
28. m_objExcel = new Excel.Application();
29. m_objExcel.Visible = false;
30. m_objExcel.DisplayAlerts = false;
31.
32. if (m_objExcel.Version != "11.0")
33. {
34. MessageBox.Show("您的 Excel 版本不是 11.0 （Office 2003），操作可能会出现问题。");
35. m_objExcel.Quit();
36. return;
37. }
38.
39. m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
40. if (TemplateFilePath.Equals(String.Empty))
41. {
42. m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
43. }
44. else
45. {
46. m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
47. m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
48. }
49. m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
50. m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
51. m_objExcel.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose);
52. }
53.
54. private void m_objExcel_WorkbookBeforeClose(Excel.Workbook m_objBooks, ref bool _Cancel)
55. {
56. MessageBox.Show("保存完毕！");
57. }
58.
59. /**//// <summary>
60. /// 将图片插入到指定的单元格位置。
61. /// 注意：图片必须是绝对物理路径
62. /// </summary>
63. /// <param name="RangeName">单元格名称，例如：B4</param>
64. /// <param name="PicturePath">要插入图片的绝对路径。</param>
65. public void InsertPicture(string RangeName, string PicturePath)
66. {
67. m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
68. m_objRange.Select();
69. Excel.Pictures pics = (Excel.Pictures)m_objSheet.Pictures(m_objOpt);
70. pics.Insert(PicturePath, m_objOpt);
71. }
72.
73. /**//// <summary>
74. /// 将图片插入到指定的单元格位置，并设置图片的宽度和高度。
75. /// 注意：图片必须是绝对物理路径
76. /// </summary>
77. /// <param name="RangeName">单元格名称，例如：B4</param>
78. /// <param name="PicturePath">要插入图片的绝对路径。</param>
79. /// <param name="PictuteWidth">插入后，图片在Excel中显示的宽度。</param>
80. /// <param name="PictureHeight">插入后，图片在Excel中显示的高度。</param>
81. public void InsertPicture(string RangeName, string PicturePath, float PictuteWidth, float PictureHeight)
82. {
83. m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
84. m_objRange.Select();
85. float PicLeft, PicTop;
86. PicLeft = Convert.ToSingle(m_objRange.Left);
87. PicTop = Convert.ToSingle(m_objRange.Top);
88. //参数含义：
89. //图片路径
90. //是否链接到文件
91. //图片插入时是否随文档一起保存
92. //图片在文档中的坐标位置（单位：points）
93. //图片显示的宽度和高度（单位：points）
94. //参数详细信息参见：
95. m_objSheet.Shapes.AddPicture(PicturePath,Microsoft.Office.Core.MsoTriState.msoFalse,
96. Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight);
97. }
98.
99. /**//// <summary>
100. /// 将Excel文件保存到指定的目录，目录必须事先存在，文件名称不一定要存在。
101. /// </summary>
102. /// <param name="OutputFilePath">要保存成的文件的全路径。</param>
103. public void SaveFile(string OutputFilePath)
104. {
105. m_objBook.SaveAs(OutputFilePath, m_objOpt, m_objOpt,
106. m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
107. m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
108.
109. this.Close();
110. }
111. /**//// <summary>
112. /// 关闭应用程序
113. /// </summary>
114. private void Close()
115. {
116. m_objBook.Close(false, m_objOpt, m_objOpt);
117. m_objExcel.Quit();
118. }
119.
120. /**//// <summary>
121. /// 释放所引用的COM对象。注意：这个过程一定要执行。
122. /// </summary>
123. public void Dispose()
124. {
125. ReleaseObj(m_objSheets);
126. ReleaseObj(m_objBook);
127. ReleaseObj(m_objBooks);
128. ReleaseObj(m_objExcel);
129. System.GC.Collect();
130. System.GC.WaitForPendingFinalizers();
131. }
132. /**//// <summary>
133. /// 释放对象，内部调用
134. /// </summary>
135. /// <param name="o"></param>
136. private void ReleaseObj(object o)
137. {
138. try
139. {
140. System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
141. }
142. catch { }
143. finally { o = null; }
144. }
145.
146. private Excel.Application m_objExcel = null;
147. private Excel.Workbooks m_objBooks = null;
148. private Excel._Workbook m_objBook = null;
149. private Excel.Sheets m_objSheets = null;
150. private Excel._Worksheet m_objSheet = null;
151. private Excel.Range m_objRange = null;
152. private object m_objOpt = System.Reflection.Missing.Value;
153. }
154.
155.}
156.

调用:

1.InsertPicToExcel ipt = new InsertPicToExcel();
2.ipt.Open();
3.ipt.InsertPicture("B2", @"C:\Excellogo.gif");
4.ipt.InsertPicture("B18", @"C:\Excellogo.gif", 120, 80);
5.ipt.SaveFile(@"C:\ExcelTest.xls");
6.ipt.Dispose();
7.

【Excel】行高和列宽单位的换算

默认情况下，Excel的行高为14.25（19像素），列宽为8.38（72像素），单位无法直接用毫米（mm）或厘米（cm）表示，需要换算，换算表如下。

======================================
像素 磅 英寸 毫米 厘米 十二点活字
96 72 1 25.4 2.54 6
======================================

行高是以磅为单位
1个单位=1磅=4/3像素=0.3528mm（25.4/72）
1mm=2.835个单位（72/25.4）

列宽与EXCEL的标准字体有关
“帮助”中说——出现在“标准列宽”框中的数字为适合于单元格的标准字体的数字 0-9 的平均值。
【注解】
标准字体——是指“EXCEL”---“工具”---“选项”---“常规”中的标准字体。当然与字的大小也有关，因为字体中,如0与1，其宽度有可能不一样，EXCEL是以“标准字体”的0123456789这10个字符的宽度平均值为计量单位。
1 个单位列宽为多少磅可用VBA得到。
=Range("A1").Width/Columns(1).ColumnWidth
(当然A1不能是合并了的单元格）
如：简体OFFICE，默认标准字体（宋体12号）情况下
Range("A1").Width/Columns(1).ColumnWidth=6.444
这样行高为56.7,列宽为8.799时,为2*2CM

1个单位列宽=2.2733mm
1mm=0.4399个单位列宽

在调整单元格行高和列宽时，如果觉得换算有些麻烦，不过有个简单的方法：
先插入一矩形或文本框，将其宽度设为10mm，移动使其左边界与表格线重合，然后调整单元格列宽使网络线与矩形的右边界重合。也只能是近似的。

使用毫米或厘米表示Excel的行高和列宽单位
1.Excel的行高和列宽单位无法用mm或cm表示。需要计算。在1024*768下，1CM约为38像素。以此为基准，可计算并控制报表的行列位置。
行高是以磅为单位
1mm=2.835磅
2.Excel行高所使用单位为磅（1cm=28.6磅），列宽使用单位为1/10英寸（既1个单位为2.54mm)
excel里的单位和cm(厘米)可以这样转换：
行高: 1毫米＝2.7682个单位 1厘米＝27.682个单位 1个单位＝0.3612毫米
列宽： 1毫米＝0.4374个单位 1厘米＝4.374 个单位 1个单位＝2.2862毫米

3.列宽与EXCEL的标准字体有关
=====================
"帮助"中说-------
出现在“标准列宽”框中的数字为适合于单元格的标准字体的数字 0-9 的平均值。
=====================
注解：
标准字体------是指EXCEL---工具---选项---常规中的标准字体(当然与字大小也有关)
因为字体中,如,0与1其宽度有可能不一样,EXCEL是以"标准字体"的0123456789这10个字符的平均值为计量单位.
1 个单位列宽为多少磅可用VBA得到。
=Range("A1").Width/Columns(1).ColumnWidth
(当然A1不能是合并了的单元格）
如：
简体OFFICE，默认标准字体（宋体12号）情况下
Range("A1").Width/Columns(1).ColumnWidth=6.444
这样行高为56.7,列宽为8.799时,为2*2CM

（此文档部分内容来源于网络，如有侵权请告知删除，文档可自行编辑修改内容，供参考，感谢您的配合和支持）

编辑版word