Excel操作类
[c#]代碼庫(kù)
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單元格邊框的線條的粗細(xì)枚舉
/// </summary>
public enum ExcelBorderWeight
{
/// <summary>
/// 極細(xì)的線條
/// </summary>
Hairline = Excel.XlBorderWeight.xlHairline,
/// <summary>
/// 中等的線條
/// </summary>
Medium = Excel.XlBorderWeight.xlMedium,
/// <summary>
/// 粗線條
/// </summary>
Thick = Excel.XlBorderWeight.xlThick,
/// <summary>
/// 細(xì)線條
/// </summary>
Thin = Excel.XlBorderWeight.xlThin
}
/// <summary>
/// Excel單元格邊框枚舉
/// </summary>
public enum ExcelBordersIndex
{
/// <summary>
/// 主對(duì)角線從
/// </summary>
DiagonalDown = Excel.XlBordersIndex.xlDiagonalDown,
/// <summary>
/// 輔對(duì)角線
/// </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>
/// 邊框內(nèi)水平橫線
/// </summary>
InsideHorizontal = Excel.XlBordersIndex.xlInsideHorizontal,
/// <summary>
/// 邊框內(nèi)垂直豎線
/// </summary>
InsideVertical = Excel.XlBordersIndex.xlInsideVertical
}
/// <summary>
/// Excel單元格的豎直方法對(duì)齊枚舉
/// </summary>
public enum ExcelVerticalAlignment
{
/// <summary>
/// 居中
/// </summary>
Center = Excel.Constants.xlCenter,
/// <summary>
/// 靠上
/// </summary>
Top = Excel.Constants.xlTop,
/// <summary>
/// 靠下
/// </summary>
Bottom = Excel.Constants.xlBottom,
/// <summary>
/// 兩端對(duì)齊
/// </summary>
Justify = Excel.Constants.xlJustify,
/// <summary>
/// 分散對(duì)齊
/// </summary>
Distributed = Excel.Constants.xlDistributed
};
/// <summary>
/// Excel 水平方向?qū)R枚舉
/// </summary>
public enum ExcelHorizontalAlignment
{
/// <summary>
///常規(guī)
/// </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>
/// 兩端對(duì)齊
/// </summary>
Justify = Excel.Constants.xlJustify,
/// <summary>
/// 跨列居中
/// </summary>
CenterAcrossSelection = Excel.Constants.xlCenterAcrossSelection,
/// <summary>
/// 分散對(duì)齊
/// </summary>
Distributed = Excel.Constants.xlDistributed
}
/// <summary>
/// Excel邊框線條的枚舉
/// </summary>
public enum ExcelStyleLine
{
/// <summary>
/// 沒有線條
/// </summary>
StyleNone = Excel.XlLineStyle.xlLineStyleNone,
/// <summary>
/// 連續(xù)的細(xì)線
/// </summary>
Continious = Excel.XlLineStyle.xlContinuous,
/// <summary>
/// 點(diǎn)狀線
/// </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>
/// 構(gòu)造函數(shù)
/// </summary>
public ExcelBase()
{
//實(shí)例化Excel對(duì)象。
exlApp = new Excel.Application();
}
/// <summary>
/// 構(gòu)造函數(shù)
/// </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>
/// 打開一個(gè)Excel文件
/// </summary>
public void Open()
{
//Get a new WorkSheet
exlWorkBook = (Workbook)exlApp.Workbooks.Add(Missing.Value);
exlWorkSheet = (Worksheet)exlWorkBook.ActiveSheet;
}
/// <summary>
/// 打開已經(jīng)存在的Excel文件模版
/// </summary>
/// <param name="XLTPath">已經(jīng)存在的文件模版的完整路徑</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}不存在，請(qǐng)重新確定文件名", 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>
/// 徹底關(guān)閉Excel的資源和進(jìn)程
/// </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文件,可以設(shè)置是否是打印前預(yù)覽打印的Excel文件
/// </summary>
/// <param name="IsPrintPreview">打印前是否預(yù)覽 , true:打印前預(yù)覽false:直接打印,不預(yù)覽 </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文件,可以設(shè)置是否打印預(yù)覽，以及打印的份數(shù)
/// </summary>
/// <param name="IsPrintPreview">打印前是否預(yù)覽 , true:打印前預(yù)覽false:直接打印,不預(yù)覽</param>
/// <param name="iCopy">打印的份數(shù)</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>
/// 打印預(yù)覽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>
/// 設(shè)置工作簿的名稱
/// </summary>
/// <param name="WorkSheet"></param>
public void SetWorkSheetName(string WorkSheet)
{
exlWorkSheet.Name = WorkSheet;
}
/// <summary>
///返回指定單元格的內(nèi)容
/// </summary>
/// <param name="iRow">定位的行</param>
/// <param name="iCol">定位的列</param>
/// <returns>返回指定單元格的內(nèi)容</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>
///返回指定單元格的內(nèi)容
/// </summary>
/// <param name="iRow">定位的行</param>
/// <param name="iCol">定位的列</param>
/// <returns>返回指定單元格的內(nèi)容</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>
/// 設(shè)置指定范圍單元格的內(nèi)容,通過單元格，比如從"A1" 到 "B3"
/// </summary>
/// <param name="startCell">開始的單元格,比如"A1"</param>
/// <param name="endCell">結(jié)束的單元格,比如"B2"</param>機(jī)動(dòng)車統(tǒng)計(jì)表.xlt
/// <param name="text">要設(shè)置的內(nèi)容，可以使用Excel的公式</param>
public void SetCellText(string startCell, string endCell, string text)
{
Range sRange = exlWorkSheet.get_Range(startCell, endCell);
//這里沒有用value屬性，而用Formula屬性，因?yàn)榭紤]到可以擴(kuò)展，可以利用公式
sRange.Cells.Formula = text;
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 設(shè)置指定范圍單元格的內(nèi)容,通過單元格，比如從"A1" 到 "B3"
/// </summary>
/// <param name="startCell">開始的單元格,比如"A1"</param>
/// <param name="endCell">結(jié)束的單元格,比如"B2"</param>機(jī)動(dòng)車統(tǒng)計(jì)表.xlt
/// <param name="text">要設(shè)置的內(nèi)容，可以使用Excel的公式</param>
public void SetCellText(string startCell, string endCell, int text)
{
Range sRange = exlWorkSheet.get_Range(startCell, endCell);
//這里沒有用value屬性，而用Formula屬性，因?yàn)榭紤]到可以擴(kuò)展，可以利用公式
sRange.Cells.Formula = text.ToString();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sRange);
sRange = null;
}
/// <summary>
/// 設(shè)置指定范圍的單元格的內(nèi)容,通過行列來定位。如第1行第2列內(nèi)容
/// </summary>
/// <param name="iRow">開始的行</param>
/// <param name="iCol">開始的列</param>
///<param name="text">要設(shè)置的文本，可以使用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>
/// 設(shè)置指定范圍的單元格的內(nèi)容,通過行列來定位。如第1行第2列內(nèi)容
/// </summary>
/// <param name="iRow">開始的行</param>
/// <param name="iCol">開始的列</param>
///<param name="text">要設(shè)置的文本，可以使用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>
/// 設(shè)置指定范圍的單元格的內(nèi)容,通過行列來定位。如第1行第2列內(nèi)容
/// </summary>
/// <param name="iRow">開始的行</param>
/// <param name="iCol">開始的列</param>
///<param name="text">要設(shè)置的文本，可以使用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>
/// 設(shè)置指定單元格的內(nèi)容,比如設(shè)置"A1"單元格的內(nèi)容
/// </summary>
/// <param name="cell">指定的單元格</param>
/// <param name="text">要設(shè)置的內(nèi)容，可以使用Excel的公式，如sum(A1:A7)--合計(jì)A1到A7數(shù)值</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>
/// 設(shè)置指定單元格的內(nèi)容
/// </summary>
/// <param name="cell">指定的單元格</param>
/// <param name="num">要設(shè)置的內(nèi)容</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>
/// 設(shè)置指定單元格的內(nèi)容，可以指定格式
/// </summary>
/// <param name="cell">要指定的單元格</param>
/// <param name="textValue">要填寫的內(nèi)容</param>
/// <param name="StringFormat">要顯示的格式</param>
///<param name="FontName">設(shè)置單元格的字體</param>
/// <param name="FontSize">設(shè)置單元格的字體大小</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>
/// 設(shè)置指定單元格的內(nèi)容，可以指定格式
/// </summary>
/// <param name="cell">要指定的單元格</param>
/// <param name="textValue">要填寫的內(nèi)容</param>
/// <param name="StringFormat">要顯示的格式</param>
///<param name="FontName">設(shè)置單元格的字體</param>
/// <param name="FontSize">設(shè)置單元格的字體大小</param>
/// <param name="colorIndex">設(shè)置單元格的顏色，我查了MSDN但是沒有顏色代碼的說明,Excel中一共有56種顏色的代碼，常用的幾個(gè)是
/// 1-黑色 2-白色 3-紅色 4-草綠色 5-藍(lán)色 6-黃色 7-紫色 ，如果想看仔細(xì)的顏色，就依次從 1 循環(huán)到 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>
/// 設(shè)置指定單元格的內(nèi)容，可以指定格式
/// </summary>
/// <param name="cell">要指定的單元格</param>
/// <param name="textValue">要填寫的內(nèi)容</param>
/// <param name="StringFormat">要顯示的格式</param>
/// <param name="FontName">設(shè)置單元格的字體</param>
/// <param name="FontSize">設(shè)置單元格的字體大小</param>
/// <param name="colorIndex">設(shè)置單元格的顏色，我查了MSDN但是沒有顏色代碼的說明,Excel中一共有56種顏色的代碼，常用的幾個(gè)是
/// 1-黑色 2-白色 3-紅色 4-草綠色 5-藍(lán)色 6-黃色 7-紫色 ，如果想看仔細(xì)的顏色，就依次從 1 循環(huán)到 56 把顏色打印出來看看</param>
/// <param name="Bold">設(shè)置單元格的字體是否粗體</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>
/// 設(shè)置指定單元格的內(nèi)容，可以指定格式
/// </summary>
/// <param name="cell">要指定的單元格</param>
/// <param name="textValue">要填寫的內(nèi)容</param>
/// <param name="StringFormat">要顯示的格式</param>
/// <param name="FontName">設(shè)置單元格的字體</param>
/// <param name="FontSize">設(shè)置單元格的字體大小</param>
/// <param name="colorIndex">設(shè)置單元格的顏色，我查了MSDN但是沒有顏色代碼的說明,Excel中一共有56種顏色的代碼，常用的幾個(gè)是
/// 1-黑色 2-白色 3-紅色 4-草綠色 5-藍(lán)色 6-黃色 7-紫色 ，如果想看仔細(xì)的顏色，就依次從 1 循環(huán)到 56 把顏色打印出來看看</param>
/// <param name="Bold">設(shè)置單元格的字體是否粗體</param>
/// <param name="BcolorIndex">設(shè)置單元格背景顏色</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>
/// 設(shè)置單元格的內(nèi)容（指定單元格的格式化字符串）
/// </summary>
/// <param name="cell">指定的單元格</param>
/// <param name="textValue">內(nèi)容</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>
/// 得到指定單元格的內(nèi)容
/// </summary>
/// <param name="cell">指定的單元格比如 A1,A2</param>
/// <returns>返回指定的內(nèi)容</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>
/// 設(shè)置指定單元格的內(nèi)容，比如設(shè)置"A1"單元格的內(nèi)容
/// </summary>
/// <param name="cell">指定的單元格</param>
/// <param name="text">要設(shè)置的內(nèi)容，使用Excel里面的R1C1這樣的格式(不知道是不是畫蛇添足，因?yàn)镋xcel里的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>
///設(shè)置單元格的單元格格式
/// </summary>
/// <param name="cell">要設(shè)定的單元格的坐標(biāo)</param>
/// <param name="formatString">單元格的格式化字符
/// 常規(guī)："G/通用格式"
/// 數(shù)值："[紅色]-0.00"(表示是2位小數(shù)，如果是負(fù)數(shù)的話則用紅色表示)
/// 貨幣："￥#,##0.000;[紅色]￥-#,##0.000"(￥是貨幣符號(hào)，可以用$,也可以不填寫，0.000代表三位小數(shù)位；[紅色]表示如果是負(fù)數(shù)的話，用紅色表示)
/// 日期：@"yyyy"年"m"月"d"日";@" (表示用年月日了表示) @"[DBNum1][$-804]yyyy"年"m"月"d"日";@"(表示用漢字表示年月日)
/// 百分比："0.000%;[紅色]-0.000%"(表示小數(shù)位為3位,紅色表示如果是負(fù)數(shù)的話則用紅色表示)
/// 文本："@"(表示是文本格式)
/// 特殊："[DBNum1][$-804]G/通用格式"(能將數(shù)字轉(zhuǎn)換成中文小寫，如1234轉(zhuǎn)換成一千二百三十四) "[DBNum2][$-804]G/通用格式"(能將數(shù)字轉(zhuǎn)換成中文大寫，如1234轉(zhuǎn)換成 壹仟貳佰叁拾肆)
/// 自定義：輸入自定義的格式化字符串
/// </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>
/// 設(shè)置指定范圍的單元格格式
/// </summary>
/// <param name="startCell">開始的單元格</param>
/// <param name="endCell">結(jié)束的單元格</param>
/// <param name="formatString">單元格的格式化字符
/// 常規(guī)："G/通用格式"
/// 數(shù)值："[紅色]-0.00"(表示是2位小數(shù)，如果是負(fù)數(shù)的話則用紅色表示)
/// 貨幣："￥#,##0.000;[紅色]￥-#,##0.000"(￥是貨幣符號(hào)，可以用$,也可以不填寫，0.000代表三位小數(shù)位；[紅色]表示如果是負(fù)數(shù)的話，用紅色表示)
/// 日期：@"yyyy"年"m"月"d"日";@" (表示用年月日了表示) @"[DBNum1][$-804]yyyy"年"m"月"d"日";@"(表示用漢字表示年月日)
/// 百分比："0.000%;[紅色]-0.000%"(表示小數(shù)位為3位,紅色表示如果是負(fù)數(shù)的話則用紅色表示)
/// 文本："@"(表示是文本格式)
/// 特殊："[DBNum1][$-804]G/通用格式"(能將數(shù)字轉(zhuǎn)換成中文小寫，如1234轉(zhuǎn)換成一千二百三十四) "[DBNum2][$-804]G/通用格式"(能將數(shù)字轉(zhuǎn)換成中文大寫，如1234轉(zhuǎn)換成 壹仟貳佰叁拾肆)
/// 自定義：輸入自定義的格式化字符串</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>
/// 設(shè)置某一列，某幾列的列寬為自動(dòng)適應(yīng)大小，比如要設(shè)置第1列為自動(dòng)適應(yīng)大小SetColumnAutoFit("A","A")
/// </summary>
/// <param name="startColumn">開始的列</param>
/// <param name="endColumn">結(jié)束的列</param>
///
//TODO:stapangpang 要增加自動(dòng)適應(yīng)列大小的方法
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>
/// 根據(jù)行列的定位，返回選定的單元格。因?yàn)镽ange 是通過Cell來定位的，而Cell需要2個(gè)參數(shù)定位，所以需要四個(gè)參數(shù)。
/// </summary>
/// <param name="iStartRow">定位開始Range的Cell的行</param>
/// <param name="iStartCol">定位開始Range的Cell的列</param>
/// <param name="iEndRow">定位結(jié)束Range的Cell的行</param>
/// <param name="iEndCol">定位結(jié)束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>
/// 返回一個(gè)單元格的范圍
/// </summary>
/// <param name="startCell">開始的單元格坐標(biāo)</param>
/// <param name="endCell">結(jié)束的單元格坐標(biāo)</param>
/// <returns>返回指定的單元格范圍</returns>
public Range GetRange(string startCell, string endCell)
{
return exlWorkSheet.get_Range(startCell, endCell);
}
/// <summary>
/// 增加一個(gè)工作簿
/// </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>
/// 設(shè)置單元格的垂直方向?qū)R方式
/// </summary>
/// <param name="cell">指定的單元格</param>
/// <param name="cellAlignment">垂直方向的對(duì)齊方式</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>
/// 設(shè)定指定范圍的單元格的垂直對(duì)齊方式
/// </summary>
/// <param name="startCell">開始的單元格的坐標(biāo)</param>
/// <param name="endCell">結(jié)束單元格的坐標(biāo)</param>
/// <param name="cellAlignment">對(duì)齊方式</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>
/// 設(shè)置指定范圍的單元格的水平方向的對(duì)齊方式
/// </summary>
/// <param name="cell">指定的單元格</param>
/// <param name="cellAlignment">水平方向的對(duì)齊方式</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>
/// 設(shè)定指定范圍的單元格的水平對(duì)齊方式
/// </summary>
/// <param name="startCell">開始的單元格的坐標(biāo)</param>
/// <param name="endCell">結(jié)束單元格的坐標(biāo)</param>
/// <param name="cellAlignment">對(duì)齊方式</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>
/// 設(shè)置指定單元格的邊框，這里只能設(shè)置單個(gè)單元格的邊框
/// </summary>
/// <param name="cell">要設(shè)定的單元格</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>
/// 設(shè)置指定范圍的Excel單元格的邊框，包括外邊框，內(nèi)邊框
/// </summary>
/// <param name="startCell">開始的單元格坐標(biāo)</param>
/// <param name="endCell">結(jié)束的單元格坐標(biāo)</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;
//范圍內(nèi)豎直豎線
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>
/// 設(shè)置指定范圍的Excel單元格的邊框，包括外邊框，內(nèi)邊框
/// </summary>
/// <param name="startCell">開始的單元格坐標(biāo)</param>
/// <param name="endCell">結(jié)束的單元格坐標(biāo)</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;
//范圍內(nèi)水平橫線
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;
//范圍內(nèi)豎直豎線
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>
/// 設(shè)置單元格的顏色
/// </summary>
/// <param name="cell">定位改單元格</param>
/// <param name="colorIndex">顏色的代碼，我查了MSDN但是沒有顏色代碼的說明,Excel中一共有56種顏色的代碼，常用的幾個(gè)是
/// 1-黑色 2-白色 3-紅色 4-草綠色 5-藍(lán)色 6-黃色 7-紫色 ，如果想看仔細(xì)的顏色，就依次從 1 循環(huán)到 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>
/// 設(shè)置指定單元格范圍的顏色
/// </summary>
/// <param name="startCell">開始的單元格</param>
/// <param name="endCell">結(jié)束的單元格</param>
/// <param name="colorIndex">顏色的代碼，我查了MSDN但是沒有顏色代碼的說明,Excel中一共有56種顏色的代碼，常用的幾個(gè)是
/// 1-黑色 2-白色 3-紅色 4-草綠色 5-藍(lán)色 6-黃色 7-紫色 ，如果想看仔細(xì)的顏色，就依次從 1 循環(huán)到 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">結(jié)束的單元格</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">定位結(jié)束Range的Cell的行(A1=1,1)</param>
/// <param name="iEndCol">定位結(jié)束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>
/// 設(shè)置單元格背景顏色
/// <param name="startCell">開始的單元格</param>
/// <param name="endCell">結(jié)束的單元格</param>
/// <param name="ColorIndex">顏色的代碼，我查了MSDN但是沒有顏色代碼的說明,Excel中一共有56種顏色的代碼，常用的幾個(gè)是
/// 1-黑色 2-白色 3-紅色 4-草綠色 5-藍(lán)色 6-黃色 7-紫色 ，如果想看仔細(xì)的顏色，就依次從 1 循環(huán)到 56 把顏色打印出來看看</param>
/// </summary>
public void SetInteriorColor(string startCell, string endCell, int ColorIndex)
{
Range sRange = GetRange(startCell, endCell);
sRange.Interior.ColorIndex = ColorIndex;
}
/// <summary>
/// 設(shè)置單元格背景顏色
/// <param name="iStartRow">定位開始Range的Cell的行(A1=1,1)</param>
/// <param name="iStartCol">定位開始Range的Cell的列(A1=1,1)</param>
/// <param name="iEndRow">定位結(jié)束Range的Cell的行(A1=1,1)</param>
/// <param name="iEndCol">定位結(jié)束Range的Cell的列(A1=1,1)</param>
/// <param name="ColorIndex">顏色的代碼，我查了MSDN但是沒有顏色代碼的說明,Excel中一共有56種顏色的代碼，常用的幾個(gè)是
/// 1-黑色 2-白色 3-紅色 4-草綠色 5-藍(lán)色 6-黃色 7-紫色 ，如果想看仔細(xì)的顏色，就依次從 1 循環(huán)到 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
}
}
轉(zhuǎn)載于:https://www.cnblogs.com/chuangjie1988/p/6821914.html
總結(jié)
- 上一篇： Java序列化小结
- 下一篇： Redis开发运维实践问题处理只内存检查