C#对Excel报表进行操作（读写和基本操作）

 //1.添加引用-〉com-〉microsoft excel 11.0
n'O },cJ ]w0//2.若出现错误:命名空间“Microsoft.Office”中不存在类型或命名空间名称“Interop”(是缺少程序集引用吗?)51Testing软件测试网(\p#bw6?H+|2A
//解决方法：先删除引用中的Excel,然后找到文件Microsoft.Office.Interop.Excel.dll，手动添加该文件的引用51Testing软件测试网R2]7sg ^"O-n

using System;
Ce1HlIlOJ0using System.Data;
e&A Wz0JI1pm0using System.Reflection;
7iUpF+K}a/x0using System.IO;
SD#p4B-@hL0using Microsoft.Office.Core;
%F~ ~qk&n,W0using System.Windows.Forms;
Pd:F*E@H&R6b6tPV(\;s0using Excel = Microsoft.Office.Interop.Excel;51Testing软件测试网4L8worT!C*G

namespace Wage.Common51Testing软件测试网n1x`S4qA3U
{51Testing软件测试网I:Wa:b(L+oW
    /// <summary>51Testing软件测试网9b*dV^g,hC
    ///作者Li Aimin (原创)
(`Ge_oa;G0    ///功能描述：C#对Excel报表进行操作
Eg;l\
MMf0    ///创建时间：2006－01－17,修改时间：２００７－１－１4
{j ~I9R*H0J0    ///说明：在工程中需要添加Excel11.0对象库的引用（Office 2000为Excel9.0，Office XP为Excel10.0）；
Z5vv+_'H"v"}VNF0    ///   需要在Dcom中配置Excel应用程序的权限；
e]S ff+M(g5[x_p0    ///   服务器需要安装Office2003
%rax,l
V0    /// </summary>
H(?D%`M|9ZTVIO(O?H0    public class ExcelLib51Testing软件测试网_"kI3@u0@B D(? Q
    {51Testing软件测试网.zNRt9O/c.Oj!N
        //http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfexcelapplicationobject.asp
&M"Er3o S0        #region Variables51Testing软件测试网-A R}&u#V;sU
        private Excel.Application excelApplication = null;
y1g.X:F g"fE0        private Excel.Workbooks excelWorkBooks = null;51Testing软件测试网0YFaz4h;MB g
        private Excel.Workbook excelWorkBook = null;
dqr$O9l0C9Hf0        private Excel.Worksheet excelWorkSheet = null;
BrJ ^{Wko\9j0        private Excel.Range excelRange = null;//Excel Range Object,多种用途
ZG!C/Fw.x`'q j-EF0        private Excel.Range excelCopySourceRange = null;//Excel Range Object

b)K3j C9m.h2l#uI;C0        private int excelActiveWorkSheetIndex;   //活动工作表索引51Testing软件测试网#oya7H"K$U+},gC^
        private string excelOpenFileName = "";  //操作Excel的路径51Testing软件测试网/A*e0S o#[
        private string excelSaveFileName = "";  //保存Excel的路径51Testing软件测试网"_k/I7ZE4I"~
        #endregion51Testing软件测试网N.Z!W3tP,j

        #region Properties51Testing软件测试网4?
w2d'Rm{oJ
        public int ActiveSheetIndex51Testing软件测试网7`2K1]Q"?lA3P0ro-a
        {
e.]8yK,A5y$r0            get51Testing软件测试网_(yq#JJPWh
            {
6ym qu;i7G dc)D#m0                return excelActiveWorkSheetIndex;
|)ZA7}:`A4r6D'e0            }51Testing软件测试网lq_N~
            set51Testing软件测试网s#Y#XJ Rx7m-FI
            {
+Mq&}K*{-d0                excelActiveWorkSheetIndex = value;51Testing软件测试网!JR3ueY.B+y U*blO
            }

e&Irf K7MPj!] x1Y$m0        }51Testing软件测试网0mT](i GBw
        public string OpenFileName
Ti ZCy7RDbG0        {
g0s+MUk`(k0            get51Testing软件测试网mtK6f&p
E$fb`3p U
            {51Testing软件测试网[+@{5aQ7zE%B
                return excelOpenFileName;
Ppa T5{z2Ik#b0            }51Testing软件测试网?SC%h(~2B!i
            set
8ek&pX5c._)B1V4ao0            {
Xki,r5k0                excelOpenFileName = value;
(IbId4\(S@K7l0            }
#De,iK'B0        }51Testing软件测试网I:A/s rM7{;Zi!w1?
        public string SaveFileName51Testing软件测试网A4S4QYm#i
        {51Testing软件测试网/qID'Wr
            get
7Rrm&Ssu:L0            {
`w7S4^}7V,Zl0cb0                return excelSaveFileName;
pJ-\r(fZq9Q6z(j0            }
-uKn+Dcm0            set
+[C$m{V0            {
`.r}U)t&Cx0                excelSaveFileName = value;51Testing软件测试网4N t(A4{i-id0yh {
            }51Testing软件测试网&@mk9mW,F
        }
5of:rO
I,[F0        #endregion

        //
Gk3G`:]]&S
Ly0        //--------------------------------------------------------------------------------------------------------
LV i3W1K R A,z](XKaW0        /// <summary>51Testing软件测试网
S@
c"h&Z*_7|o3f7GFO
        ///构造函数；51Testing软件测试网Q$eh!?ot
        /// </summary>
,LE7r h)I,j#T0        public ExcelLib()51Testing软件测试网A%azXR
        {
+J!IZ;O!E&x RJ0            excelApplication = null;//Excel Application Object
5?&A`-f2XB @0            excelWorkBooks = null;//Workbooks51Testing软件测试网])y1p~%s]
            excelWorkBook = null;//Excel Workbook Object
g@ \ K$ln F0            excelWorkSheet = null;//Excel Worksheet Object
L_}S&X!~jr0            ActiveSheetIndex = 1;   //默认值活动工作簿为第一个；设置活动工作簿请参阅SetActiveWorkSheet() 51Testing软件测试网Ib8Utie?
        }
D1x-{.W)l(G1B+J0        /// <summary>51Testing软件测试网'T"~5k3\4e.o,a
        ///以excelOpenFileName为模板新建Excel文件
!tDG#t'SI0_[0        /// </summary>
a9VG"N.q@4h0        public bool OpenExcelFile()
og"B6~JdC0ki0        {
%`5vo'i+a4V0            if (excelApplication != null) CloseExcelApplication();51Testing软件测试网*r+EpN!z%|\!S"\

            //检查文件是否存在
$V3nk)D(a'zv9T0E8qo r0            if (excelOpenFileName == "")51Testing软件测试网q5QD,Lx d
            {51Testing软件测试网rbq&s"wd6g4P
                throw new Exception("请选择文件！");51Testing软件测试网CQ?%y(EiVP
            }
;h-N `6p/E0            if (!File.Exists(excelOpenFileName))51Testing软件测试网#e*R)_:@?4]X.K
            {

                throw new Exception(excelOpenFileName + "该文件不存在！");//该异常如何处理，由什么处理？？？？
;y&L@j1h#Av0            }51Testing软件测试网9E},gL Uv
            try
3M8fA}
lI
K j0            {51Testing软件测试网 q2IF$l%UHdeh{
                excelApplication = new Excel.ApplicationClass();
;Z^7w{9j2]2w:~0                excelWorkBooks = excelApplication.Workbooks;51Testing软件测试网7r.nt#y:mm
                excelWorkBook = ((Excel.Workbook)excelWorkBooks.Open(excelOpenFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
?H6uPnc0                excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets[excelActiveWorkSheetIndex];51Testing软件测试网1JBe l*TL)p&j
                excelApplication.Visible = false;

                return true;
Pbj3w V0y.~N7I5\0            }
(|J1OvG0            catch (Exception e)
ym&YK B&p0            {51Testing软件测试网&T'a;WUe`c'}I?H
                CloseExcelApplication();

n9muP5_ u3Tq0                MessageBox.Show("(1)没有安装Excel 2003；(2)或没有安装Excel 2003 .NET可编程性支持；\n详细信息："51Testing软件测试网"F rE"u J#[[1Sf Ug
                    +e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
3Q0Yw {%U_0                //throw new Exception(e.Message);51Testing软件测试网[j[*e4Y
                return false;
:vUr7nZ$?_a6]m0            }51Testing软件测试网+kZ
Vu-B0`id
        }51Testing软件测试网rVBD ZE)udSN

        /// <summary>51Testing软件测试网U+{A,ol)cfc
j
        ///读取一个Cell的值51Testing软件测试网-JN1z,^%H
g+G
        /// </summary>
1VzeQ Sy|0        /// <param name="CellRowID">要读取的Cell的行索引</param>
8Eso#g@3U#p4]0        /// <param name="CellColumnID">要读取的Cell的列索引</param>
g Xeyt!qGeK0        /// <returns>Cell的值</returns>51Testing软件测试网:ViS*s8u`Wf;o
        public string getOneCellValue(int CellRowID, int CellColumnID)
9N#{ ra [i{0        {51Testing软件测试网:Ni6{:~9{O8n
            if (CellRowID <= 0)
9W3fI,B\cI0            {
@E'C?8d B*y0                throw new Exception("行索引超出范围！");
;Y7TT5D5Og0            }51Testing软件测试网4Z W(V4r:X$|p
            string sValue = "";
U Kkm_%uM6{&_0            try
+~'?CQ]Km.ZT'u0            {
hf
H3I dh:e x[2?0                sValue = ((Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]).Text.ToString();51Testing软件测试网7d
^ ?^.i?6Mzi`+B
            }51Testing软件测试网Djy5TQd
            catch (Exception e)51Testing软件测试网#N*CJ3X7|
            {
UUfH,Vf-?ke0                CloseExcelApplication();
#d"~]/A
r`n0                throw new Exception(e.Message);
erM-\O0            }
,HL$Zx0E l-Z0            return (sValue);51Testing软件测试网K-g'_)vt
        }51Testing软件测试网2w_} N(MIfw
        /// <summary>
#{*\c.eJ0| hfm0        ///读取一个连续区域的Cell的值(矩形区域，包含一行或一列,或多行，多列)，返回一个一维字符串数组。51Testing软件测试网"M0K4KhTszT y
        /// </summary>51Testing软件测试网M7Iv4o2V}!}
        /// <param name="StartCell">StartCell是要写入区域的左上角单元格</param>
A&VjS-uQP\2T0        /// <param name="EndCell">EndCell是要写入区域的右下角单元格</param>51Testing软件测试网0M}f$}2y-{1}.F/Hl
        /// <returns>值的集合</returns>
{Wu#_/Ut)t#f6I0        public string[] getCellsValue(string StartCell, string EndCell)
D4b[Ph9q7_H!M7N0        {
,n(qVO]wi0            string[] sValue = null;
0I,Rl-M
yC"P4w0            //try
8qinE;J?0            //{51Testing软件测试网#s\5C)LZ6b$_
            excelRange = (Excel.Range)excelWorkSheet.get_Range(StartCell, EndCell);51Testing软件测试网N] @6ZQ a
            sValue = new string[excelRange.Count];
o(R!\AP0            int rowStartIndex = ((Excel.Range)excelWorkSheet.get_Range(StartCell, StartCell)).Row;  //起始行号51Testing软件测试网&L3d]yk ]\]S a
            int columnStartIndex = ((Excel.Range)excelWorkSheet.get_Range(StartCell, StartCell)).Column; //起始列号
gPtrqo1A0ZL%@0            int rowNum = excelRange.Rows.Count;     //行数目51Testing软件测试网:p yhEmG{9k}U
            int columnNum = excelRange.Columns.Count;    //列数目
;o?8y$\.`0            int index = 0;51Testing软件测试网}zC_*G0Uu!j
            for (int i = rowStartIndex; i < rowStartIndex + rowNum; i++)51Testing软件测试网F#W!m#j+LrQ
            {51Testing软件测试网&?4Z7z%PWk ik!{&t t!H
                for (int j = columnStartIndex; j < columnNum + columnStartIndex; j++)
[1[$C-X9AWU0                {
F[wb3h/c4yp0                    //读到空值null和读到空串""分别处理
*vl3`%am&\|V[0                    sValue[index] = ((Excel.Range)excelWorkSheet.Cells[i, j]).Text.ToString();
8rk+W7h_0                    index++;51Testing软件测试网1UU1wZ9W_I:\
                }51Testing软件测试网#Wi lcH @YD,^me
            }
"C0~!z%d!f6Q/Y?0            //}
kI SN,y0            //catch (Exception e)51Testing软件测试网]6v,E,?h%f
            //{
l;xe$OI5JO0            //    CloseExcelApplication();
6a0q3O_^0            //    throw new Exception(e.Message);
g2CG4b0X$|0            //}

            return (sValue);
'zR\V*}H0        }51Testing软件测试网m3q#h,^y-ZB-F OI

        /// <summary>
]5grJ6b+B0        ///读取所有单元格的数据(矩形区域)，返回一个datatable.假设所有单元格靠工作表左上区域。
g:qD A5G(Y7{7{0        /// </summary>51Testing软件测试网#? f:AT/n*dHvR
        public DataTable getAllCellsValue()
s.RXJ1@-EI0        {
a?)d1F6W0vW8c0            int columnCount = getTotalColumnCount();
L*D+z(bu7z0            int rowCount = getTotalRowCount();
6\?4Cs4JCT0            DataTable dt = new DataTable();51Testing软件测试网e-c M/{9mo3i
            //设置datatable列的名称
Es4F(M3Z,qp0            for (int columnID = 1; columnID <= columnCount; columnID++)
U:]/G mhzV(M0            {
`sA!TP0pD0                dt.Columns.Add(((Excel.Range)excelWorkSheet.Cells[1, columnID]).Text.ToString());51Testing软件测试网 MI-B'gN%O
            }51Testing软件测试网/oNX z`E;Yj

            for (int rowID = 2; rowID <= rowCount; rowID++)
8J)x/~%gk't VM4y0            {51Testing软件测试网u*b;B%{*xQc\/Gd6Wp
                DataRow dr = dt.NewRow();
n$fe#b-?7r*m9D0                for (int columnID = 1; columnID <= columnCount; columnID++)
0a!sb7qT
tY0                {51Testing软件测试网[&xtv3|p5V
                    dr[columnID - 1] = ((Excel.Range)excelWorkSheet.Cells[rowID, columnID]).Text.ToString();51Testing软件测试网E\#L*ri1B|
                    //读到空值null和读到空串""分别处理51Testing软件测试网V7@nlD(]cI|
                }
lA\8at0                dt.Rows.Add(dr);
9A-G
b D)N|`y*o0            }
3?:D#yF$vJg0            return (dt);
L:p%I'L\Y5i0        }51Testing软件测试网'jwTI9c
        public int getTotalRowCount()
]3rR&FK+F*~0        {//当前活动工作表中有效行数(总行数)
)m%N.Vc
S8c7h`#Z`4E0            int rowsNumber = 0;51Testing软件测试网 YLcRN0a\4g"yO
            try
H j(a#^cL:tsr7RN0            {51Testing软件测试网A;I~&?u&N/jK!b
                while (true)

y2o'ds4uKE0                {
J{Na+f,tz0                    if (((Excel.Range)excelWorkSheet.Cells[rowsNumber + 1, 1]).Text.ToString().Trim() == "" &&
H1Yd:SFS-M0                           ((Excel.Range)excelWorkSheet.Cells[rowsNumber + 2, 1]).Text.ToString().Trim() == "" &&51Testing软件测试网(V#X9Y)V1@0\
                           ((Excel.Range)excelWorkSheet.Cells[rowsNumber + 3, 1]).Text.ToString().Trim() == "")51Testing软件测试网Q?9E7C{_})B:[#hq;R
                        break;
iVM"Q9ik cK3k0                    rowsNumber++;
_#@7w6yU|,P0                }
:QDj(A2]5P,l(`0            }51Testing软件测试网4G2`!JZ/UfafG
            catch

y4j*H3I+e?:l,Y2r6Z({0            {51Testing软件测试网4UC5do1Z
                return -1;
``yZ!~U\2J0            }51Testing软件测试网2Dx-MSm m
|
            return rowsNumber;51Testing软件测试网2G/Mal0o6Eo@f
WD
        }51Testing软件测试网
tK/N"YJ/H!K*j&Y
G
        /// <summary>

ppq1C6ePAj9Y+W0        ///当前活动工作表中有效列数(总列数)
9[n*Wh2|1}4[0        /// </summary>51Testing软件测试网yTvzL1?fh-ID
        /// <param></param>51Testing软件测试网#a1P)g heY3|)d'?
        public int getTotalColumnCount()51Testing软件测试网WEO0v&Juj$d9r"O
        {
9jSnM1nW)n!J p0            int columnNumber = 0;51Testing软件测试网2Y_u"hU/qqOQ\|6\
            try51Testing软件测试网R0zq(i+~v
            {
pTz$p"}-F.M0                while (true)
rA,F fBB$v*k0                {51Testing软件测试网0b9uN7^6C [dU
                    if (((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 1]).Text.ToString().Trim() == "" &&

iLN
b;y0                           ((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 2]).Text.ToString().Trim() == "" &&51Testing软件测试网+so&N}7Z
                           ((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 3]).Text.ToString().Trim() == "")

k+N}#nb0                        break;
8I&pNJ3x\1x p)_0                    columnNumber++;51Testing软件测试网`.~)| T@,jK N
                }
lb Q-\1HK0            }
ysE}.Iu-`0            catch51Testing软件测试网&J'\JYVS;ES"n
            {
T D%`'C%x@WN8z&]0                return -1;51Testing软件测试网n#e*nd9w!j r'F'u.V!k3C
            }51Testing软件测试网#Q} ?5b#g
            return columnNumber;51Testing软件测试网5| J/^!^O*D0u9p
        }

        /// <summary>
d`I7YbT0        ///向一个Cell写入数据51Testing软件测试网MX+s Ho(E
        /// </summary>
c4zf1L
M.F.k3e5m}|0        /// <param name="CellRowID">CellRowID是cell的行索引</param>51Testing软件测试网!Gz3}Tz}b)i
        /// <param name="CellColumnID">CellColumnID是cell的列索引</param>51Testing软件测试网Qz7l5K+K&a;r(T4t!Dm
        ///<param name="Value">要写入该单元格的数据值</param>51Testing软件测试网7`8H5j/j5po&b;?
        public void setOneCellValue(int CellRowID, int CellColumnID, string Value)51Testing软件测试网VZE5?8rS
        {51Testing软件测试网,N?#oAC[[!C
            try
9? D'g\5^0            {51Testing软件测试网-k!Q~7wH8k*[ d'eg
                excelRange = (Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID];51Testing软件测试网o#{+F fe\,Vt
                excelRange.Value2 = Value;//Value2?51Testing软件测试网%p]/E8b$U.U/@
                //Gets or sets the value of the NamedRange control.
$sb MPN J6o/mow`0                //The only difference between this property and the Value property is that Value2 is not a parameterized property.51Testing软件测试网Y~ q
bTN5u:m
                excelRange = null;51Testing软件测试网7T+K:v&l#P:jE
            }
+II v? _v*i|u6}0            catch (Exception e)51Testing软件测试网?,Tz3t4o+ji%W
            {51Testing软件测试网(V0H/J#R(Z:Wnb0o
                CloseExcelApplication();51Testing软件测试网I}AZ a:E;A+C
                throw new Exception(e.Message);
,oR^Cn#t6} f3ok(V0            }
Kwxsp0        }
l7W9@9SQZ0        /// <summary>51Testing软件测试网*|)A\|v O+u
        ///设置活动工作表
*SN%IsYE d0        /// </summary>51Testing软件测试网m3Jq}1kg!R
        /// <param name="SheetIndex">要设置为活动工作表的索引值</param>
Dw!hF5^F0        public void SetActiveWorkSheet(int SheetIndex)
B5c a [,A0[Y)H8b0        {51Testing软件测试网A p Oz$}f+d
            if (SheetIndex <= 0)51Testing软件测试网E4U0h{y"c#xw n
            {
*Fh9^?c0                throw new Exception("索引超出范围！");
9?QF#p%]G/uwD.| ~0            }51Testing软件测试网8b$dW"{H\\
            try
b&|]CXy0            {
)Hi-F5[,aO0                ActiveSheetIndex = SheetIndex;
zF7ch3v x7A0                excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets[ActiveSheetIndex];51Testing软件测试网t-?@+zdA4V
            }
j]B4n/?8y&s*}l s0            catch (Exception e)51Testing软件测试网r+Vv,_le)xG#R
            {
Kgj`oZ
ukh0                CloseExcelApplication();51Testing软件测试网$X}Bo V8K-D1t~
                throw new Exception(e.Message);51Testing软件测试网 B?6YU3Jy\ Y
            }51Testing软件测试网$^(^J.@D6m&t
        }51Testing软件测试网J\*iUr \b
        /// <summary>
@9R(pccm*V,{,R"U0        ///向连续区域一次性写入数据；只有在区域连续和写入的值相同的情况下可以使用方法51Testing软件测试网X8GOj ].Q
        /// </summary>

cf,J7@_0        /// <param name="StartCell">StartCell是要写入区域的左上角单元格</param>
F.]c|Zx(C0        /// <param name="EndCell">EndCell是要写入区域的右下角单元格</param>51Testing软件测试网q-l$Ir
i5pW
        /// <param name="Value">要写入指定区域所有单元格的数据值</param>51Testing软件测试网:By)Ev#uR@\
        public void setCellsValue(string StartCell, string EndCell, string Value)
Y~O?[w0        {51Testing软件测试网?$I;U+MfM2h8IB
            try
%m-Tz V2O Q[0    &