C#操作Excel全能帮助类:
1,Excel单元格边框的线条的粗细枚举
2,Excel单元格边框枚举
3,彻底关闭Excel的资源和进程
4,打印预览Excel文件
5,设置工作簿的名称
6,设置指定单元格的内容，可以指定格式
7,设置指定范围的单元格格式
using Excel;
using System;
using System.Reflection;
namespace ExcelClass
{
public class ExcelBase
{
#region " Private Variable Definition "
private Application exlApp;
private _Workbook exlWorkBook;
private _Worksheet exlWorkSheet;
private int sheetNumber = 1;
#endregion
#region " Public Property and Constant Definition "
/// <summary>
/// Excel单元格边框的线条的粗细枚举
/// </summary>
public enum ExcelBorderWeight
{
/// <summary>
/// 极细的线条
/// </summary>
Hairline = Excel.XlBorderWeight.xlHairline,
/// <summary>
/// 中等的线条
/// </summary>
Medium = Excel.XlBorderWeight.xlMedium,
/// <summary>
/// 粗线条
/// </summary>
Thick = Excel.XlBorderWeight.xlThick,
/// <summary>
/// 细线条
/// </summary>
Thin = Excel.XlBorderWeight.xlThin
}
/// <summary>
/// Excel单元格边框枚举
/// </summary>
public enum ExcelBordersIndex
{
/// <summary>
/// 主对角线从
/// </summary>
DiagonalDown = Excel.XlBordersIndex.xlDiagonalDown,
/// <summary>
/// 辅对角线
/// </summary>
DiagonUp = Excel.XlBordersIndex.xlDiagonalUp,
/// <summary>
///底边框
/// </summary>
EdgeBottom = Excel.XlBordersIndex.xlEdgeBottom,
/// <summary>
/// 左边框
/// </summary>
EdgeLeft = Excel.XlBordersIndex.xlEdgeLeft,
/// <summary>
/// 右边框
/// </summary>
EdgeRight = Excel.XlBordersIndex.xlEdgeRight,
/// <summary>
/// 顶边框
/// </summary>
EdgeTop = Excel.XlBordersIndex.xlEdgeTop,
/// <summary>
/// 边框内水平横线
/// </summary>
InsideHorizontal = Excel.XlBordersIndex.xlInsideHorizontal,
/// <summary>
/// 边框内垂直竖线
/// </summary>
InsideVertical = Excel.XlBordersIndex.xlInsideVertical
}
/// <summary>
/// Excel单元格的竖直方法对齐枚举
/// </summary>
public enum ExcelVerticalAlignment
{
/// <summary>
/// 居中
/// </summary>
Center = Excel.Constants.xlCenter,
/// <summary>
/// 靠上
/// </summary>
Top = Excel.Constants.xlTop,
/// <summary>
/// 靠下
/// </summary>
Bottom = Excel.Constants.xlBottom,
/// <summary>
/// 两端对齐
/// </summary>
Justify = Excel.Constants.xlJustify,
/// <summary>
/// 分散对齐
/// </summary>
Distributed = Excel.Constants.xlDistributed
};
/// <summary>
/// Excel 水平方向对齐枚举
/// </summary>
public enum ExcelHorizontalAlignment
{
/// <summary>
///常规
/// </summary>
General = Excel.Constants.xlGeneral,
/// <summary>
/// 靠左
/// </summary>
Left = Excel.Constants.xlLeft,
/// <summary>
/// 居中
/// </summary>
Center = Excel.Constants.xlCenter,
/// <summary>
/// 靠右
/// </summary>
Right = Excel.Constants.xlRight,
/// <summary>
/// 填充
/// </summary>
Fill = Excel.Constants.xlFill,
/// <summary>
/// 两端对齐
/// </summary>
Justify = Excel.Constants.xlJustify,
/// <summary>
/// 跨列居中
/// </summary>
CenterAcrossSelection = Excel.Constants.xlCenterAcrossSelection,
/// <summary>
/// 分散对齐
/// </summary>
Distributed = Excel.Constants.xlDistributed
}
/// <summary>
/// Excel边框线条的枚举
/// </summary>
public enum ExcelStyleLine
{
/// <summary>
/// 没有线条
/// </summary>
StyleNone = Excel.XlLineStyle.xlLineStyleNone,
/// <summary>
/// 连续的细线
/// </summary>
Continious = Excel.XlLineStyle.xlContinuous,
/// <summary>
/// 点状线
/// </summary>
Dot = Excel.XlLineStyle.xlDot,
/// <summary>
/// 双条线
/// </summary>
Double = Excel.XlLineStyle.xlDouble,
}
/// <summary>
/// 排序的玫举
/// </summary>
public enum ExcelSortOrder
{
/// <summary>
/// 升序
/// </summary>
Ascending = Excel.XlSortOrder.xlAscending,
/// <summary>
/// 降序
/// </summary>
Descending = Excel.XlSortOrder.xlDescending,
}
#endregion
#region " Construction Method "
/// <summary>
/// 构造函数
/// </summary>
public ExcelBase()
{
//实例化Excel对象。
exlApp = new Excel.Application();
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="ExcelVisible">Excel是否可见</param>
public ExcelBase(bool ExcelVisible)
{
exlApp = new Excel.Application();
exlApp.Visible = ExcelVisible;
}
#endregion
#region " Open and dispose method definition "
/// <summary>
/// 打开一个Excel文件
/// </summary>
public void Open()
{
//Get a new WorkSheet
exlWorkBook = (Workbook)exlApp.Workbooks.Add(Missing.Value);
exlWorkSheet = (Worksheet)exlWorkBook.ActiveSheet;
}
/// <summary>
/// 打开已经存在的Excel文件模版
/// </summary>
/// <param name="XLTPath">已经存在的文件模版的完整路径</param>
public void Open(string XLTPath)
{
if (System.IO.File.Exists(XLTPath))
{
exlWorkBook = (Workbook)exlApp.Workbooks.Add(XLTPath);
exlWorkSheet = (Worksheet)exlWorkBook.ActiveSheet;
}
else
{
throw new System.IO.FileNotFoundException(string.Format("{0}不存在，请重新确定文件名", XLTPath));
}
}
/// <summary>
/// 保存Excel文件
/// </summary>
/// <param name="fileName">保存的文件名</param>
public void SaveAs(string fileName)
{
exlWorkSheet.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value);
}
/// <summary>
/// 彻底关闭Excel的资源和进程
/// </summary>
public void Dispose()
{
if (exlApp != null)
{
exlApp.Quit();
}
if (exlWorkBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlWorkBook);
exlWorkBook = null;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlWorkSheet);
exlWorkSheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlApp);
exlApp = null;
GC.Collect();
}
#endregion
#region " Print and PrintPreview method definition "
/// <summary>
/// 打印Excel文件,可以设置是否是打印前预览打印的Excel文件
/// </summary>
/// <param name="IsPrintPreview">打印前是否预览 , true:打印前预览false:直接打印,不预览 </param>
public void Print(bool IsPrintPreview)
{
bool flag = exlApp.Visible;
if (exlApp.Visible)
{
exlApp.Visible = true;
}
exlWorkSheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, IsPrintPreview, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
exlApp.Visible = flag;
}
/// <summary>
/// 打印Excel文件,可以设置是否打印预览，以及打印的份数
/// </summary>
/// <param name="IsPrintPreview">打印前是否预览 , true:打印前预览false:直接打印,不预览</param>
/// <param name="iCopy">打印的份数</param>
public void Print(bool IsPrintPreview, int iCopy)
{
if (iCopy < 1)
{
iCopy = 1;
}
exlWorkSheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, IsPrintPreview, iCopy, Missing.Value, Missing.Value, Missing.Value);
}
/// <summary>
/// 打印预览Excel文件
/// </summary>
public void PrintPreview()
{
exlWorkSheet.PrintPreview(Missing.Value);
}
#endregion
#region " Detail control excel method "
/// <summary>
/// 将Excel隐藏
/// </summary>
public void Hide()
{
exlApp.Visible = false;
}
/// <summary>
/// 将Excel显示
/// </summary>
public void Show()
{
exlApp.Visible = true;
}
/// <summary>
/// 设置工作簿的名称
/// </summary>
/// <param name="WorkSheet"></param>
public void SetWorkSheetName(string WorkSheet)
{
exlWorkSheet.Name = WorkSheet;
}
/// <summary>
///返回指定单元格的内容
/// </summary>
/// <param name="iRow">定位的行</param>
/// <param name="iCol">定位的列</param>
/// <returns>返回指定单元格的内容</returns>
public string GetCellText(int iRow, int iCol)
{
Range sRange = GetRange(iRow, iCol, iRow, iCol);
string returnText = (string)sRange.Text;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
return returnText;
}
/// <summary>
///返回指定单元格的内容
/// </summary>
/// <param name="iRow">定位的行</param>
/// <param name="iCol">定位的列</param>
/// <returns>返回指定单元格的内容</returns>
public string GetCellText(int startRow, int startCol, int startRow2, int startCol2)
{
Range sRange = GetRange(startRow, startCol, startRow2, startCol2);
string returnText = (string)sRange.Text;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
return returnText;
}
/// <summary>
/// 设置指定范围单元格的内容,通过单元格，比如从"A1" 到 "B3"
/// </summary>
/// <param name="startCell">开始的单元格,比如"A1"</param>
/// <param name="endCell">结束的单元格,比如"B2"</param>机动车统计表.xlt
/// <param name="text">要设置的内容，可以使用Excel的公式</param>
public void SetCellText(string startCell, string endCell, string text)
{
Range sRange = exlWorkSheet.get_Range(startCell, endCell);
//这里没有用value属性，而用Formula属性，因为考虑到可以扩展，可以利用公式
sRange.Cells.Formula = text;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定范围单元格的内容,通过单元格，比如从"A1" 到 "B3"
/// </summary>
/// <param name="startCell">开始的单元格,比如"A1"</param>
/// <param name="endCell">结束的单元格,比如"B2"</param>机动车统计表.xlt
/// <param name="text">要设置的内容，可以使用Excel的公式</param>
public void SetCellText(string startCell, string endCell, int text)
{
Range sRange = exlWorkSheet.get_Range(startCell, endCell);
//这里没有用value属性，而用Formula属性，因为考虑到可以扩展，可以利用公式
sRange.Cells.Formula = text.ToString();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定范围的单元格的内容,通过行列来定位。如第1行第2列内容
/// </summary>
/// <param name="iRow">开始的行</param>
/// <param name="iCol">开始的列</param>
///<param name="text">要设置的文本，可以使用Excel的公式</param>
public void SetCellText(int iRow, int iCol, string text)
{
Range sRange = this.GetRange(iRow, iCol, iRow, iCol);
sRange.Cells.Formula = text;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定范围的单元格的内容,通过行列来定位。如第1行第2列内容
/// </summary>
/// <param name="iRow">开始的行</param>
/// <param name="iCol">开始的列</param>
///<param name="text">要设置的文本，可以使用Excel的公式</param>
public void SetCellTextNOZero(int iRow, int iCol, string text)
{
string txt = "";
try
{
if (System.Convert.ToInt32(text) == 0)
{
txt = "";
}
else
{
txt = text;
}
}
catch
{
txt = text;
}
Range sRange = this.GetRange(iRow, iCol, iRow, iCol);
sRange.Cells.Formula = txt;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定范围的单元格的内容,通过行列来定位。如第1行第2列内容
/// </summary>
/// <param name="iRow">开始的行</param>
/// <param name="iCol">开始的列</param>
///<param name="text">要设置的文本，可以使用Excel的公式</param>
public void SetCellText(int iRow, int iCol, int text)
{
Range sRange = this.GetRange(iRow, iCol, iRow, iCol);
sRange.Cells.Formula = text;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定单元格的内容,比如设置"A1"单元格的内容
/// </summary>
/// <param name="cell">指定的单元格</param>
/// <param name="text">要设置的内容，可以使用Excel的公式，如sum(A1:A7)--合计A1到A7数值</param>
public void SetCellText(string cell, string text)
{
Range sRange = GetRange(cell);
sRange.Cells.Formula = text;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定单元格的内容
/// </summary>
/// <param name="cell">指定的单元格</param>
/// <param name="num">要设置的内容</param>
public void SetCellText(string cell, Int32 num)
{
Range sRange = GetRange(cell);
sRange.Cells.Formula = num.ToString();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定单元格的内容，可以指定格式
/// </summary>
/// <param name="cell">要指定的单元格</param>
/// <param name="textValue">要填写的内容</param>
/// <param name="StringFormat">要显示的格式</param>
///<param name="FontName">设置单元格的字体</param>
/// <param name="FontSize">设置单元格的字体大小</param>
public void setCellTextByFormat(string cell, string textValue, string StringFormat, string FontName, string FontSize)
{
Range sRange = GetRange(cell);
sRange.Select();
if (StringFormat != "")
{
sRange.NumberFormatLocal = StringFormat;
}
if (FontName != "")
{
sRange.Font.Name = FontName;
}
if (FontSize != "")
{
sRange.Font.Size = FontSize;
}
sRange.Cells.Formula = textValue;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定单元格的内容，可以指定格式
/// </summary>
/// <param name="cell">要指定的单元格</param>
/// <param name="textValue">要填写的内容</param>
/// <param name="StringFormat">要显示的格式</param>
///<param name="FontName">设置单元格的字体</param>
/// <param name="FontSize">设置单元格的字体大小</param>
/// <param name="colorIndex">设置单元格的颜色，我查了MSDN但是没有颜色代码的说明,Excel中一共有56种颜色的代码，常用的几个是
/// 1-黑色 2-白色 3-红色 4-草绿色 5-蓝色 6-黄色 7-紫色 ，如果想看仔细的颜色，就依次从 1 循环到 56 把颜色打印出来看看</param>
public void setCellTextByFormat(string cell, string textValue, string StringFormat, string FontName, string FontSize, int colorIndex)
{
Range sRange = GetRange(cell);
sRange.Select();
if (StringFormat != "")
{
sRange.Cells.NumberFormatLocal = StringFormat;
}
if (FontName != "")
{
sRange.Font.Name = FontName;
}
if (FontSize != "")
{
sRange.Font.Size = FontSize;
}
if (colorIndex != 0)
{
sRange.Font.ColorIndex = colorIndex;
}
sRange.Cells.Formula = textValue;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定单元格的内容，可以指定格式
/// </summary>
/// <param name="cell">要指定的单元格</param>
/// <param name="textValue">要填写的内容</param>
/// <param name="StringFormat">要显示的格式</param>
/// <param name="FontName">设置单元格的字体</param>
/// <param name="FontSize">设置单元格的字体大小</param>
/// <param name="colorIndex">设置单元格的颜色，我查了MSDN但是没有颜色代码的说明,Excel中一共有56种颜色的代码，常用的几个是
/// 1-黑色 2-白色 3-红色 4-草绿色 5-蓝色 6-黄色 7-紫色 ，如果想看仔细的颜色，就依次从 1 循环到 56 把颜色打印出来看看</param>
/// <param name="Bold">设置单元格的字体是否粗体</param>
public void setCellTextByFormat(string cell, string textValue, string StringFormat, string FontName, string FontSize, int colorIndex, bool Bold)
{
Range sRange = GetRange(cell);
sRange.Select();
if (StringFormat != "")
{
sRange.Cells.NumberFormatLocal = StringFormat;
}
if (FontName != "")
{
sRange.Font.Name = FontName;
}
if (FontSize != "")
{
sRange.Font.Size = FontSize;
}
if (colorIndex != 0)
{
sRange.Font.ColorIndex = colorIndex;
}
sRange.Font.Bold = Bold;
sRange.Cells.Formula = textValue;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定单元格的内容，可以指定格式
/// </summary>
/// <param name="cell">要指定的单元格</param>
/// <param name="textValue">要填写的内容</param>
/// <param name="StringFormat">要显示的格式</param>
/// <param name="FontName">设置单元格的字体</param>
/// <param name="FontSize">设置单元格的字体大小</param>
/// <param name="colorIndex">设置单元格的颜色，我查了MSDN但是没有颜色代码的说明,Excel中一共有56种颜色的代码，常用的几个是
/// 1-黑色 2-白色 3-红色 4-草绿色 5-蓝色 6-黄色 7-紫色 ，如果想看仔细的颜色，就依次从 1 循环到 56 把颜色打印出来看看</param>
/// <param name="Bold">设置单元格的字体是否粗体</param>
/// <param name="BcolorIndex">设置单元格背景颜色</param>
public void setCellTextByFormat(string cell, string textValue, string StringFormat, string FontName, string FontSize, int colorIndex, bool Bold, int BcolorIndex)
{
Range sRange = GetRange(cell);
sRange.Select();
if (StringFormat != "")
{
sRange.Cells.NumberFormatLocal = StringFormat;
}
if (FontName != "")
{
sRange.Font.Name = FontName;
}
if (FontSize != "")
{
sRange.Font.Size = FontSize;
}
if (colorIndex != 0)
{
sRange.Font.ColorIndex = colorIndex;
}
sRange.Font.Bold = Bold;
sRange.Cells.Formula = textValue;
sRange.Interior.ColorIndex = BcolorIndex;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置单元格的内容（指定单元格的格式化字符串）
/// </summary>
/// <param name="cell">指定的单元格</param>
/// <param name="textValue">内容</param>
/// <param name="stringFormat">格式化字符串</param>
public void setCellText(string cell, string textValue, string stringFormat)
{
Range sRange = GetRange(cell);
sRange.Select();
if (stringFormat != "")
{
sRange.Cells.NumberFormatLocal = stringFormat;
}
sRange.Cells.Formula = textValue;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 得到指定单元格的内容
/// </summary>
/// <param name="cell">指定的单元格比如 A1,A2</param>
/// <returns>返回指定的内容</returns>
public object GetCellText(string cell)
{
object returnValue;
Range sRange = GetRange(cell);
returnValue = sRange.Cells.Text;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
return returnValue;
}
/// <summary>
/// 设置指定单元格的内容，比如设置"A1"单元格的内容
/// </summary>
/// <param name="cell">指定的单元格</param>
/// <param name="text">要设置的内容，使用Excel里面的R1C1这样的格式(不知道是不是画蛇添足，因为Excel里的Macro中是这样使用的)</param>
public void SetCellTextR1C1(string cell, string text)
{
Range sRange = GetRange(cell);
sRange.Cells.FormulaR1C1 = text;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
///设置单元格的单元格格式
/// </summary>
/// <param name="cell">要设定的单元格的坐标</param>
/// <param name="formatString">单元格的格式化字符
/// 常规："G/通用格式"
/// 数值："[红色]-0.00"(表示是2位小数，如果是负数的话则用红色表示)
/// 货币："￥#,##0.000;[红色]￥-#,##0.000"(￥是货币符号，可以用$,也可以不填写，0.000代表三位小数位；[红色]表示如果是负数的话，用红色表示)
/// 日期：@"yyyy"年"m"月"d"日";@" (表示用年月日了表示) @"[DBNum1][$-804]yyyy"年"m"月"d"日";@"(表示用汉字表示年月日)
/// 百分比："0.000%;[红色]-0.000%"(表示小数位为3位,红色表示如果是负数的话则用红色表示)
/// 文本："@"(表示是文本格式)
/// 特殊："[DBNum1][$-804]G/通用格式"(能将数字转换成中文小写，如1234转换成一千二百三十四) "[DBNum2][$-804]G/通用格式"(能将数字转换成中文大写，如1234转换成 壹仟贰佰叁拾肆)
/// 自定义：输入自定义的格式化字符串
/// </param>
public void SetCellFormat(string cell, string formatString)
{
Range sRange = GetRange(cell);
sRange.Select();
sRange.NumberFormatLocal = formatString;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定范围的单元格格式
/// </summary>
/// <param name="startCell">开始的单元格</param>
/// <param name="endCell">结束的单元格</param>
/// <param name="formatString">单元格的格式化字符
/// 常规："G/通用格式"
/// 数值："[红色]-0.00"(表示是2位小数，如果是负数的话则用红色表示)
/// 货币："￥#,##0.000;[红色]￥-#,##0.000"(￥是货币符号，可以用$,也可以不填写，0.000代表三位小数位；[红色]表示如果是负数的话，用红色表示)
/// 日期：@"yyyy"年"m"月"d"日";@" (表示用年月日了表示) @"[DBNum1][$-804]yyyy"年"m"月"d"日";@"(表示用汉字表示年月日)
/// 百分比："0.000%;[红色]-0.000%"(表示小数位为3位,红色表示如果是负数的话则用红色表示)
/// 文本："@"(表示是文本格式)
/// 特殊："[DBNum1][$-804]G/通用格式"(能将数字转换成中文小写，如1234转换成一千二百三十四) "[DBNum2][$-804]G/通用格式"(能将数字转换成中文大写，如1234转换成 壹仟贰佰叁拾肆)
/// 自定义：输入自定义的格式化字符串</param>
public void SetAreaCellFormat(string startCell, string endCell, string formatString)
{
Range sRange = GetRange(startCell, endCell);
sRange.Select();
sRange.NumberFormatLocal = formatString;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置某一列，某几列的列宽为自动适应大小，比如要设置第1列为自动适应大小SetColumnAutoFit("A","A")
/// </summary>
/// <param name="startColumn">开始的列</param>
/// <param name="endColumn">结束的列</param>
///
//TODO:stapangpang 要增加自动适应列大小的方法
public void SetColumnAutoFit(string startColumn, string endColumn)
{
Range sRange = (Range)exlWorkSheet.Columns[String.Format("{0}:{1}", startColumn, endColumn), Missing.Value];
sRange.Select();
sRange.EntireColumn.AutoFit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
#endregion
#region " Get excel range method definition "
/// <summary>
/// 根据行列的定位，返回选定的单元格。因为Range 是通过Cell来定位的，而Cell需要2个参数定位，所以需要四个参数。
/// </summary>
/// <param name="iStartRow">定位开始Range的Cell的行</param>
/// <param name="iStartCol">定位开始Range的Cell的列</param>
/// <param name="iEndRow">定位结束Range的Cell的行</param>
/// <param name="iEndCol">定位结束Range的Cell的列</param>
/// <returns>返回指定范围的Range</returns>
public Range GetRange(int iStartRow, int iStartCol, int iEndRow, int iEndCol)
{
return exlWorkSheet.get_Range(exlApp.Cells[iStartRow, iStartCol], exlApp.Cells[iEndRow, iEndCol]);
}
/// <summary>
/// 返回指定的单元格
/// </summary>
/// <param name="cell">指定的单元格</param>
/// <returns>返回指定的单元格</returns>
public Range GetRange(string cell)
{
return exlWorkSheet.get_Range(cell, Missing.Value);
}
/// <summary>
/// 返回一个单元格的范围
/// </summary>
/// <param name="startCell">开始的单元格坐标</param>
/// <param name="endCell">结束的单元格坐标</param>
/// <returns>返回指定的单元格范围</returns>
public Range GetRange(string startCell, string endCell)
{
return exlWorkSheet.get_Range(startCell, endCell);
}
/// <summary>
/// 增加一个工作簿
/// </summary>
public void AddWorkSheet()
{
if (this.sheetNumber <= 3)
{
exlApp.ActiveWorkbook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
exlWorkSheet = (Worksheet)exlWorkBook.ActiveSheet;
exlWorkSheet.Select(Missing.Value);
}
else
{
sheetNumber ;
exlApp.ActiveWorkbook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
exlWorkSheet = (Worksheet)exlWorkBook.ActiveSheet;
exlWorkSheet.Select(Missing.Value);
}
//exlWorkBook.ActiveSheet;
}
#endregion
#region " Excel range style method definition "
/// <summary>
/// 设置单元格的垂直方向对齐方式
/// </summary>
/// <param name="cell">指定的单元格</param>
/// <param name="cellAlignment">垂直方向的对齐方式</param>
public void SetCellVerticalAlignment(string cell, ExcelVerticalAlignment cellAlignment)
{
Range sRange = GetRange(cell);
sRange.Select();
sRange.VerticalAlignment = cellAlignment;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设定指定范围的单元格的垂直对齐方式
/// </summary>
/// <param name="startCell">开始的单元格的坐标</param>
/// <param name="endCell">结束单元格的坐标</param>
/// <param name="cellAlignment">对齐方式</param>
public void SetCellAreaVerticalAlignment(string startCell, string endCell, ExcelVerticalAlignment cellAlignment)
{
Range sRange = GetRange(startCell, endCell);
sRange.Select();
sRange.VerticalAlignment = cellAlignment;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定范围的单元格的水平方向的对齐方式
/// </summary>
/// <param name="cell">指定的单元格</param>
/// <param name="cellAlignment">水平方向的对齐方式</param>
public void SetCellHorizontalAlignment(string cell, ExcelHorizontalAlignment cellAlignment)
{
Range sRange = GetRange(cell);
sRange.Select();
sRange.HorizontalAlignment = cellAlignment;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设定指定范围的单元格的水平对齐方式
/// </summary>
/// <param name="startCell">开始的单元格的坐标</param>
/// <param name="endCell">结束单元格的坐标</param>
/// <param name="cellAlignment">对齐方式</param>
public void SetCellAreaHorizontalAlignment(string startCell, string endCell, ExcelHorizontalAlignment cellAlignment)
{
Range sRange = GetRange(startCell, endCell);
sRange.Select();
sRange.HorizontalAlignment = cellAlignment;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定单元格的边框，这里只能设置单个单元格的边框
/// </summary>
/// <param name="cell">要设定的单元格</param>
public void SetCellBorder(string cell)
{
Range sRange = GetRange(cell);
//上边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeTop].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeTop].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeTop].ColorIndex = Excel.Constants.xlAutomatic;
//底边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeBottom].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeBottom].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeBottom].ColorIndex = Excel.Constants.xlAutomatic;
//右边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeRight].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeRight].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeRight].ColorIndex = Excel.Constants.xlAutomatic;
//左边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeLeft].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeLeft].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeLeft].ColorIndex = Excel.Constants.xlAutomatic;
//释放资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定范围的Excel单元格的边框，包括外边框，内边框
/// </summary>
/// <param name="startCell">开始的单元格坐标</param>
/// <param name="endCell">结束的单元格坐标</param>
public void SetAreaBorder1(string startCell, String endCell)
{
Range sRange = GetRange(startCell, endCell);
//上边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeTop].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeTop].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeTop].ColorIndex = Excel.Constants.xlAutomatic;
//底边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeBottom].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeBottom].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeBottom].ColorIndex = Excel.Constants.xlAutomatic;
//右边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeRight].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeRight].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeRight].ColorIndex = Excel.Constants.xlAutomatic;
//左边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeLeft].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeLeft].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeLeft].ColorIndex = Excel.Constants.xlAutomatic;
//范围内竖直竖线
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.InsideVertical].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.InsideVertical].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.InsideVertical].ColorIndex = Excel.Constants.xlAutomatic;
//释放资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定范围的Excel单元格的边框，包括外边框，内边框
/// </summary>
/// <param name="startCell">开始的单元格坐标</param>
/// <param name="endCell">结束的单元格坐标</param>
public void SetAreaBorder(string startCell, String endCell)
{
Range sRange = GetRange(startCell, endCell);
//上边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeTop].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeTop].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeTop].ColorIndex = Excel.Constants.xlAutomatic;
//底边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeBottom].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeBottom].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeBottom].ColorIndex = Excel.Constants.xlAutomatic;
//右边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeRight].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeRight].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeRight].ColorIndex = Excel.Constants.xlAutomatic;
//左边框
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeLeft].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeLeft].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.EdgeLeft].ColorIndex = Excel.Constants.xlAutomatic;
//范围内水平横线
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.InsideHorizontal].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.InsideHorizontal].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.InsideHorizontal].ColorIndex = Excel.Constants.xlAutomatic;
//范围内竖直竖线
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.InsideVertical].LineStyle = ExcelStyleLine.Continious;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.InsideVertical].Weight = ExcelBorderWeight.Thin;
sRange.Borders[(Excel.XlBordersIndex)ExcelBordersIndex.InsideVertical].ColorIndex = Excel.Constants.xlAutomatic;
//释放资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置单元格的颜色
/// </summary>
/// <param name="cell">定位改单元格</param>
/// <param name="colorIndex">颜色的代码，我查了MSDN但是没有颜色代码的说明,Excel中一共有56种颜色的代码，常用的几个是
/// 1-黑色 2-白色 3-红色 4-草绿色 5-蓝色 6-黄色 7-紫色 ，如果想看仔细的颜色，就依次从 1 循环到 56 把颜色打印出来看看</param>
public void SetCellBackGroundColor(string cell, int colorIndex)
{
Range sRange = GetRange(cell);
sRange.Select();
sRange.Font.ColorIndex = colorIndex;
//释放资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 设置指定单元格范围的颜色
/// </summary>
/// <param name="startCell">开始的单元格</param>
/// <param name="endCell">结束的单元格</param>
/// <param name="colorIndex">颜色的代码，我查了MSDN但是没有颜色代码的说明,Excel中一共有56种颜色的代码，常用的几个是
/// 1-黑色 2-白色 3-红色 4-草绿色 5-蓝色 6-黄色 7-紫色 ，如果想看仔细的颜色，就依次从 1 循环到 56 把颜色打印出来看看</param>
public void SetAreaCellBackGroundColor(string startCell, string endCell, int colorIndex)
{
Range sRange = GetRange(startCell, endCell);
sRange.Select();
sRange.Font.ColorIndex = colorIndex;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 合并单元格
/// <param name="startCell">开始的单元格</param>
/// <param name="endCell">结束的单元格</param>
/// </summary>
public void SetMergeCells(string startCell, string endCell)
{
Range sRange = GetRange(startCell, endCell);
sRange.MergeCells = true;
}
/// <summary>
/// 合并单元格
/// <param name="iStartRow">定位开始Range的Cell的行(A1=1,1)</param>
/// <param name="iStartCol">定位开始Range的Cell的列(A1=1,1)</param>
/// <param name="iEndRow">定位结束Range的Cell的行(A1=1,1)</param>
/// <param name="iEndCol">定位结束Range的Cell的列(A1=1,1)</param>
/// </summary>
public void SetMergeCells(int iStartRow, int iStartCol, int iEndRow, int iEndCol)
{
Range sRange = GetRange(iStartRow, iStartCol, iEndRow, iEndCol);
sRange.MergeCells = true;
}
/// <summary>
/// 设置单元格背景颜色
/// <param name="startCell">开始的单元格</param>
/// <param name="endCell">结束的单元格</param>
/// <param name="ColorIndex">颜色的代码，我查了MSDN但是没有颜色代码的说明,Excel中一共有56种颜色的代码，常用的几个是
/// 1-黑色 2-白色 3-红色 4-草绿色 5-蓝色 6-黄色 7-紫色 ，如果想看仔细的颜色，就依次从 1 循环到 56 把颜色打印出来看看</param>
/// </summary>
public void SetInteriorColor(string startCell, string endCell, int ColorIndex)
{
Range sRange = GetRange(startCell, endCell);
sRange.Interior.ColorIndex = ColorIndex;
}
/// <summary>
/// 设置单元格背景颜色
/// <param name="iStartRow">定位开始Range的Cell的行(A1=1,1)</param>
/// <param name="iStartCol">定位开始Range的Cell的列(A1=1,1)</param>
/// <param name="iEndRow">定位结束Range的Cell的行(A1=1,1)</param>
/// <param name="iEndCol">定位结束Range的Cell的列(A1=1,1)</param>
/// <param name="ColorIndex">颜色的代码，我查了MSDN但是没有颜色代码的说明,Excel中一共有56种颜色的代码，常用的几个是
/// 1-黑色 2-白色 3-红色 4-草绿色 5-蓝色 6-黄色 7-紫色 ，如果想看仔细的颜色，就依次从 1 循环到 56 把颜色打印出来看看</param>
/// </summary>
public void SetInteriorColor(int iStartRow, int iStartCol, int iEndRow, int iEndCol, int ColorIndex)
{
Range sRange = GetRange(iStartRow, iStartCol, iEndRow, iEndCol);
sRange.Interior.ColorIndex = ColorIndex;
}
#endregion
}
}
原文链接：C#操作Excel帮助类