[原創(chuàng)]C# 操作Excel的類
最近領(lǐng)導(dǎo)要求把一些數(shù)導(dǎo)入Excel生成報(bào)表, 工作本來(lái)是要手動(dòng)完成的,但咱是做程序的, 應(yīng)該想一切辦法讓程序來(lái)代替我們完成任務(wù), 于是寫(xiě)了一個(gè)操作Excel的程序, 現(xiàn)將代碼公布, 希望對(duì)讀本文的朋友有所幫助
http://xingfustar.cnblogs.com
代碼寫(xiě)的不好,請(qǐng)多原諒, 目的與大家多交流學(xué)習(xí),有什么問(wèn)題,歡迎指出.
http://xingfustar.cnblogs.com
使用該代碼要先引用Excel
/*----------------------------------------------------------------
// 版權(quán)：http://XingFuStar.cnblogs.com
//
// 文件名： OpeareExcel
// 文件功能描述： 用C#來(lái)操作Excel,
//
// 作者：XingFuStar
// 日期：2007年8月10日
//
// 當(dāng)前版本：V1.0.2
//
// 修改日期：2007年8月13日
// 修改內(nèi)容：增加打開(kāi)保存等功能
// 修改日期：2007年9月12日
// 修改內(nèi)容：修改關(guān)閉Excel時(shí)提供保存選擇
//----------------------------------------------------------------*/
using System;
using Microsoft.Office.Core;
using Excel;
namespace XingFuStudio.ExcelEdit
{
class ExcelEdit
{
string myFileName;
Excel.Application myExcel;
Excel.Workbook myWorkBook;
/// <summary>
/// 構(gòu)造函數(shù)，不創(chuàng)建Excel工作薄
/// </summary>
public ExcelEdit()
{
//請(qǐng)不要?jiǎng)h除以下信息
//版權(quán)：http://XingFuStar.cnblogs.com
}
/// <summary>
/// 創(chuàng)建Excel工作薄
/// </summary>
public void CreateExcel()
{
myExcel = new Excel.Application();
myWorkBook = myExcel.Application.Workbooks.Add(true);
}
/// <summary>
/// 顯示Excel
/// </summary>
public void ShowExcel()
{
myExcel.Visible = true;
}
/// <summary>
/// 將數(shù)據(jù)寫(xiě)入Excel
/// </summary>
/// <param name="data">要寫(xiě)入的二維數(shù)組數(shù)據(jù)</param>
/// <param name="startRow">Excel中的起始行</param>
/// <param name="startColumn">Excel中的起始列</param>
public void WriteData(string[,] data, int startRow, int startColumn)
{
int rowNumber = data.GetLength(0);
int columnNumber = data.GetLength(1);
for (int i = 0; i < rowNumber; i++)
{
for (int j = 0; j < columnNumber; j++)
{
//在Excel中，如果某單元格以單引號(hào)“'”開(kāi)頭，表示該單元格為純文本，因此，我們?cè)诿總€(gè)單元格前面加單引號(hào)。
myExcel.Cells[startRow + i, startColumn + j] = "'" + data[i, j];
}
}
}
/// <summary>
/// 將數(shù)據(jù)寫(xiě)入Excel
/// </summary>
/// <param name="data">要寫(xiě)入的字符串</param>
/// <param name="starRow">寫(xiě)入的行</param>
/// <param name="startColumn">寫(xiě)入的列</param>
public void WriteData(string data, int row, int column)
{
myExcel.Cells[row, column] = data;
}
/// <summary>
/// 將數(shù)據(jù)寫(xiě)入Excel
/// </summary>
/// <param name="data">要寫(xiě)入的數(shù)據(jù)表</param>
/// <param name="startRow">Excel中的起始行</param>
/// <param name="startColumn">Excel中的起始列</param>
public void WriteData(System.Data.DataTable data, int startRow, int startColumn)
{
for (int i = 0; i <= data.Rows.Count - 1; i++)
{
for (int j = 0; j <= data.Columns.Count - 1; j++)
{
//在Excel中，如果某單元格以單引號(hào)“'”開(kāi)頭，表示該單元格為純文本，因此，我們?cè)诿總€(gè)單元格前面加單引號(hào)。
myExcel.Cells[startRow + i, startColumn + j] = "'" + data.Rows[i][j].ToString();
}
}
}
/// <summary>
/// 讀取指定單元格數(shù)據(jù)
/// </summary>
/// <param name="row">行序號(hào)</param>
/// <param name="column">列序號(hào)</param>
/// <returns>該格的數(shù)據(jù)</returns>
public string ReadData(int row, int column)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]);
return range.Text.ToString();
}
/// <summary>
/// 向Excel中插入圖片
/// </summary>
/// <param name="pictureName">圖片的絕對(duì)路徑加文件名</param>
public void InsertPictures(string pictureName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
//后面的數(shù)字表示位置，位置默認(rèn)
worksheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
}
/// <summary>
/// 向Excel中插入圖片
/// </summary>
/// <param name="pictureName">圖片的絕對(duì)路徑加文件名</param>
/// <param name="left">左邊距</param>
/// <param name="top">右邊距</param>
/// <param name="width">寬</param>
/// <param name="heigth">高</param>
public void InsertPictures(string pictureName, int left, int top, int width, int heigth)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
worksheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, top, left, heigth, width);
}
/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="sheetNum">工作表序號(hào)，從左到右，從1開(kāi)始</param>
/// <param name="newSheetName">新的工作表名</param>
public void ReNameSheet(int sheetNum, string newSheetName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Name = newSheetName;
}
/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="oldSheetName">原有工作表名</param>
/// <param name="newSheetName">新的工作表名</param>
public void ReNameSheet(string oldSheetName, string newSheetName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[oldSheetName];
worksheet.Name = newSheetName;
}
/// <summary>
/// 新建工作表
/// </summary>
/// <param name="sheetName">工作表名</param>
public void CreateWorkSheet(string sheetName)
{
Excel.Worksheet newWorksheet = (Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
newWorksheet.Name = sheetName;
}
/// <summary>
/// 激活工作表
/// </summary>
/// <param name="sheetName">工作表名</param>
public void ActivateSheet(string sheetName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetName];
worksheet.Activate();
}
/// <summary>
/// 激活工作表
/// </summary>
/// <param name="sheetNum">工作表序號(hào)</param>
public void ActivateSheet(int sheetNum)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Activate();
}
/// <summary>
/// 刪除一個(gè)工作表
/// </summary>
/// <param name="SheetName">刪除的工作表名</param>
public void DeleteSheet(int sheetNum)
{
((Excel.Worksheet)myWorkBook.Worksheets[sheetNum]).Delete();
}
/// <summary>
/// 刪除一個(gè)工作表
/// </summary>
/// <param name="SheetName">刪除的工作表序號(hào)</param>
public void DeleteSheet(string sheetName)
{
((Excel.Worksheet)myWorkBook.Worksheets[sheetName]).Delete();
}
/// <summary>
/// 合并單元格
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
public void CellsUnite(int startRow, int startColumn, int endRow, int endColumn)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.MergeCells = true;
}
/// <summary>
/// 單元格文字對(duì)齊方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="hAlign">水平對(duì)齊</param>
/// <param name="vAlign">垂直對(duì)齊</param>
public void CellsAlignment(int startRow, int startColumn, int endRow, int endColumn, ExcelHAlign hAlign, ExcelVAlign vAlign)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.HorizontalAlignment = hAlign;
range.VerticalAlignment = vAlign;
}
/// <summary>
/// 繪制指定單元格的邊框
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
public void CellsDrawFrame(int startRow, int startColumn, int endRow, int endColumn)
{
CellsDrawFrame(startRow, startColumn, endRow, endColumn,
true, true, true, true, true, true, false, false,
LineStyle.連續(xù)直線, BorderWeight.細(xì), ColorIndex.自動(dòng));
}
/// <summary>
/// 繪制指定單元格的邊框
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="isDrawTop">是否畫(huà)上外框</param>
/// <param name="isDrawBottom">是否畫(huà)下外框</param>
/// <param name="isDrawLeft">是否畫(huà)左外框</param>
/// <param name="isDrawRight">是否畫(huà)右外框</param>
/// <param name="isDrawHInside">是否畫(huà)水平內(nèi)框</param>
/// <param name="isDrawVInside">是否畫(huà)垂直內(nèi)框</param>
/// <param name="isDrawDown">是否畫(huà)斜向下線</param>
/// <param name="isDrawUp">是否畫(huà)斜向上線</param>
/// <param name="lineStyle">線類型</param>
/// <param name="borderWeight">線粗細(xì)</param>
/// <param name="color">線顏色</param>
public void CellsDrawFrame(int startRow, int startColumn, int endRow, int endColumn,
bool isDrawTop, bool isDrawBottom, bool isDrawLeft, bool isDrawRight,
bool isDrawHInside, bool isDrawVInside, bool isDrawDiagonalDown, bool isDrawDiagonalUp,
LineStyle lineStyle, BorderWeight borderWeight, ColorIndex color)
{
//獲取畫(huà)邊框的單元格
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
//清除所有邊框
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = LineStyle.無(wú);
//以下是按參數(shù)畫(huà)邊框
if (isDrawTop)
{
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeTop].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeTop].ColorIndex = color;
}
if (isDrawBottom)
{
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeBottom].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeBottom].ColorIndex = color;
}
if (isDrawLeft)
{
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeLeft].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeLeft].ColorIndex = color;
}
if (isDrawRight)
{
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeRight].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeRight].ColorIndex = color;
}
if (isDrawVInside)
{
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlInsideVertical].Weight = borderWeight;
range.Borders[XlBordersIndex.xlInsideVertical].ColorIndex = color;
}
if (isDrawHInside)
{
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = borderWeight;
range.Borders[XlBordersIndex.xlInsideHorizontal].ColorIndex = color;
}
if (isDrawDiagonalDown)
{
range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlDiagonalDown].Weight = borderWeight;
range.Borders[XlBordersIndex.xlDiagonalDown].ColorIndex = color;
}
if (isDrawDiagonalUp)
{
range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlDiagonalUp].Weight = borderWeight;
range.Borders[XlBordersIndex.xlDiagonalUp].ColorIndex = color;
}
}
/// <summary>
/// 單元格背景色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="color">顏色索引</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = Pattern.Solid;
}
/// <summary>
/// 單元格背景色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="color">顏色索引</param>
/// <param name="pattern">填充方式</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color, Pattern pattern)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = pattern;
}
/// <summary>
/// 設(shè)置行高
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="height">行高</param>
public void SetRowHeight(int startRow, int endRow, int height)
{
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];
range.RowHeight = height;
}
/// <summary>
/// 自動(dòng)調(diào)整行高
/// </summary>
/// <param name="columnNum">列號(hào)</param>
public void RowAutoFit(int rowNum)
{
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 設(shè)置列寬
/// </summary>
/// <param name="startColumn">起始列(列對(duì)應(yīng)的字母)</param>
/// <param name="endColumn">結(jié)束列(列對(duì)應(yīng)的字母)</param>
/// <param name="width"></param>
public void SetColumnWidth(string startColumn, string endColumn, int width)
{
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
/// 設(shè)置列寬
/// </summary>
/// <param name="startColumn">起始列</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="width"></param>
public void SetColumnWidth(int startColumn, int endColumn, int width)
{
string strStartColumn = GetColumnName(startColumn);
string strEndColumn = GetColumnName(endColumn);
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
/// 自動(dòng)調(diào)整列寬
/// </summary>
/// <param name="columnNum">列號(hào)</param>
public void ColumnAutoFit(string column)
{
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 自動(dòng)調(diào)整列寬
/// </summary>
/// <param name="columnNum">列號(hào)</param>
public void ColumnAutoFit(int columnNum)
{
string strcolumnNum = GetColumnName(columnNum);
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 字體顏色
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="color">顏色索引</param>
public void FontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.ColorIndex = color;
}
/// <summary>
/// 字體樣式(加粗,斜體,下劃線)
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="isBold">是否加粗</param>
/// <param name="isItalic">是否斜體</param>
/// <param name="underline">下劃線類型</param>
public void FontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, UnderlineStyle underline)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.Bold = isBold;
range.Font.Underline = underline;
range.Font.Italic = isItalic;
}
/// <summary>
/// 單元格字體及大小
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="fontName">字體名稱</param>
/// <param name="fontSize">字體大小</param>
public void FontNameSize(int startRow, int startColumn, int endRow, int endColumn,string fontName, int fontSize)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.Name = fontName;
range.Font.Size = fontSize;
}
/// <summary>
/// 打開(kāi)一個(gè)存在的Excel文件
/// </summary>
/// <param name="fileName">Excel完整路徑加文件名</param>
public void Open(string fileName)
{
myExcel = new Excel.Application();
myWorkBook = myExcel.Workbooks.Add(fileName);
myFileName = fileName;
}
/// <summary>
/// 保存Excel
/// </summary>
/// <returns>保存成功返回True</returns>
public bool Save()
{
if (myFileName == "")
{
return false;
}
else
{
try
{
myWorkBook.Save();
return true;
}
catch (Exception ex)
{
return false;
}
}
}
/// <summary>
/// Excel文檔另存為
/// </summary>
/// <param name="fileName">保存完整路徑加文件名</param>
/// <returns>保存成功返回True</returns>
public bool SaveAs(string fileName)
{
try
{
myWorkBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
return true;
}
catch (Exception ex)
{
return false;
}
}
/// <summary>
/// 關(guān)閉Excel
/// </summary>
public void Close()
{
myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
/// <summary>
/// 關(guān)閉Excel
/// </summary>
/// <param name="isSave">是否保存</param>
public void Close(bool isSave)
{
myWorkBook.Close(isSave, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
/// <summary>
/// 關(guān)閉Excel
/// </summary>
/// <param name="isSave">是否保存</param>
/// <param name="fileName">存儲(chǔ)文件名</param>
public void Close(bool isSave,string fileName)
{
myWorkBook.Close(isSave, fileName, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
#region 私有成員
private string GetColumnName(int number)
{
int h, l;
h = number / 26;
l = number % 26;
if (l == 0)
{
h -= 1;
l = 26;
}
string s = GetLetter(h) + GetLetter(l);
return s;
}
private string GetLetter(int number)
{
switch (number)
{
case 1:
return "A";
case 2:
return "B";
case 3:
return "C";
case 4:
return "D";
case 5:
return "E";
case 6:
return "F";
case 7:
return "G";
case 8:
return "H";
case 9:
return "I";
case 10:
return "J";
case 11:
return "K";
case 12:
return "L";
case 13:
return "M";
case 14:
return "N";
case 15:
return "O";
case 16:
return "P";
case 17:
return "Q";
case 18:
return "R";
case 19:
return "S";
case 20:
return "T";
case 21:
return "U";
case 22:
return "V";
case 23:
return "W";
case 24:
return "X";
case 25:
return "Y";
case 26:
return "Z";
default:
return "";
}
}
#endregion
}
/// <summary>
/// 水平對(duì)齊方式
/// </summary>
public enum ExcelHAlign
{
常規(guī) = 1,
靠左,
居中,
靠右,
填充,
兩端對(duì)齊,
跨列居中,
分散對(duì)齊
}
/// <summary>
/// 垂直對(duì)齊方式
/// </summary>
public enum ExcelVAlign
{
靠上 = 1,
居中,
靠下,
兩端對(duì)齊,
分散對(duì)齊
}
/// <summary>
/// 線粗
/// </summary>
public enum BorderWeight
{
極細(xì) = 1,
細(xì) = 2,
粗 = -4138,
極粗 = 4
}
/// <summary>
/// 線樣式
/// </summary>
public enum LineStyle
{
連續(xù)直線 = 1,
短線 = -4115,
線點(diǎn)相間 = 4,
短線間兩點(diǎn) = 5,
點(diǎn) = -4118,
雙線 = -4119,
無(wú) = -4142,
少量?jī)A斜點(diǎn) = 13
}
/// <summary>
/// 下劃線方式
/// </summary>
public enum UnderlineStyle
{
無(wú)下劃線 = -4142,
雙線 = - 4119,
雙線充滿全格 = 5,
單線 = 2,
單線充滿全格 = 4
}
/// <summary>
/// 單元格填充方式
/// </summary>
public enum Pattern
{
Automatic = -4105,
Checker = 9,
CrissCross = 16,
Down = -4121,
Gray16 = 17,
Gray25 = -4124,
Gray50 = -4125,
Gray75 = -4126,
Gray8 = 18,
Grid = 15,
Horizontal = -4128,
LightDown = 13,
LightHorizontal = 11,
LightUp = 14,
LightVertical = 12,
None = -4142,
SemiGray75 = 10,
Solid = 1,
Up = -4162,
Vertical = -4166
}
/// <summary>
/// 常用顏色定義,對(duì)就Excel中顏色名
/// </summary>
public enum ColorIndex
{
無(wú)色 = -4142,
自動(dòng) = -4105,
黑色 = 1,
褐色 = 53,
橄欖 = 52,
深綠 = 51,
深青 = 49,
深藍(lán) = 11,
靛藍(lán) = 55,
灰色80 = 56,
深紅 = 9,
橙色 = 46,
深黃 = 12,
綠色 = 10,
青色 = 14,
藍(lán)色 = 5,
藍(lán)灰 = 47,
灰色50 = 16,
紅色 = 3,
淺橙色 = 45,
酸橙色 = 43,
海綠 = 50,
水綠色 = 42,
淺藍(lán) = 41,
紫羅蘭 = 13,
灰色40 = 48,
粉紅 = 7,
金色 = 44,
黃色 = 6,
鮮綠 = 4,
青綠 = 8,
天藍(lán) = 33,
梅紅 = 54,
灰色25 = 15,
玫瑰紅 = 38,
茶色 = 40,
淺黃 = 36,
淺綠 = 35,
淺青綠 = 34,
淡藍(lán) = 37,
淡紫 = 39,
白色 = 2
}
}
// 版權(quán)：http://XingFuStar.cnblogs.com
//
// 文件名： OpeareExcel
// 文件功能描述： 用C#來(lái)操作Excel,
//
// 作者：XingFuStar
// 日期：2007年8月10日
//
// 當(dāng)前版本：V1.0.2
//
// 修改日期：2007年8月13日
// 修改內(nèi)容：增加打開(kāi)保存等功能
// 修改日期：2007年9月12日
// 修改內(nèi)容：修改關(guān)閉Excel時(shí)提供保存選擇
//----------------------------------------------------------------*/
using System;
using Microsoft.Office.Core;
using Excel;
namespace XingFuStudio.ExcelEdit
{
class ExcelEdit
{
string myFileName;
Excel.Application myExcel;
Excel.Workbook myWorkBook;
/// <summary>
/// 構(gòu)造函數(shù)，不創(chuàng)建Excel工作薄
/// </summary>
public ExcelEdit()
{
//請(qǐng)不要?jiǎng)h除以下信息
//版權(quán)：http://XingFuStar.cnblogs.com
}
/// <summary>
/// 創(chuàng)建Excel工作薄
/// </summary>
public void CreateExcel()
{
myExcel = new Excel.Application();
myWorkBook = myExcel.Application.Workbooks.Add(true);
}
/// <summary>
/// 顯示Excel
/// </summary>
public void ShowExcel()
{
myExcel.Visible = true;
}
/// <summary>
/// 將數(shù)據(jù)寫(xiě)入Excel
/// </summary>
/// <param name="data">要寫(xiě)入的二維數(shù)組數(shù)據(jù)</param>
/// <param name="startRow">Excel中的起始行</param>
/// <param name="startColumn">Excel中的起始列</param>
public void WriteData(string[,] data, int startRow, int startColumn)
{
int rowNumber = data.GetLength(0);
int columnNumber = data.GetLength(1);
for (int i = 0; i < rowNumber; i++)
{
for (int j = 0; j < columnNumber; j++)
{
//在Excel中，如果某單元格以單引號(hào)“'”開(kāi)頭，表示該單元格為純文本，因此，我們?cè)诿總€(gè)單元格前面加單引號(hào)。
myExcel.Cells[startRow + i, startColumn + j] = "'" + data[i, j];
}
}
}
/// <summary>
/// 將數(shù)據(jù)寫(xiě)入Excel
/// </summary>
/// <param name="data">要寫(xiě)入的字符串</param>
/// <param name="starRow">寫(xiě)入的行</param>
/// <param name="startColumn">寫(xiě)入的列</param>
public void WriteData(string data, int row, int column)
{
myExcel.Cells[row, column] = data;
}
/// <summary>
/// 將數(shù)據(jù)寫(xiě)入Excel
/// </summary>
/// <param name="data">要寫(xiě)入的數(shù)據(jù)表</param>
/// <param name="startRow">Excel中的起始行</param>
/// <param name="startColumn">Excel中的起始列</param>
public void WriteData(System.Data.DataTable data, int startRow, int startColumn)
{
for (int i = 0; i <= data.Rows.Count - 1; i++)
{
for (int j = 0; j <= data.Columns.Count - 1; j++)
{
//在Excel中，如果某單元格以單引號(hào)“'”開(kāi)頭，表示該單元格為純文本，因此，我們?cè)诿總€(gè)單元格前面加單引號(hào)。
myExcel.Cells[startRow + i, startColumn + j] = "'" + data.Rows[i][j].ToString();
}
}
}
/// <summary>
/// 讀取指定單元格數(shù)據(jù)
/// </summary>
/// <param name="row">行序號(hào)</param>
/// <param name="column">列序號(hào)</param>
/// <returns>該格的數(shù)據(jù)</returns>
public string ReadData(int row, int column)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]);
return range.Text.ToString();
}
/// <summary>
/// 向Excel中插入圖片
/// </summary>
/// <param name="pictureName">圖片的絕對(duì)路徑加文件名</param>
public void InsertPictures(string pictureName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
//后面的數(shù)字表示位置，位置默認(rèn)
worksheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
}
/// <summary>
/// 向Excel中插入圖片
/// </summary>
/// <param name="pictureName">圖片的絕對(duì)路徑加文件名</param>
/// <param name="left">左邊距</param>
/// <param name="top">右邊距</param>
/// <param name="width">寬</param>
/// <param name="heigth">高</param>
public void InsertPictures(string pictureName, int left, int top, int width, int heigth)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
worksheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, top, left, heigth, width);
}
/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="sheetNum">工作表序號(hào)，從左到右，從1開(kāi)始</param>
/// <param name="newSheetName">新的工作表名</param>
public void ReNameSheet(int sheetNum, string newSheetName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Name = newSheetName;
}
/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="oldSheetName">原有工作表名</param>
/// <param name="newSheetName">新的工作表名</param>
public void ReNameSheet(string oldSheetName, string newSheetName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[oldSheetName];
worksheet.Name = newSheetName;
}
/// <summary>
/// 新建工作表
/// </summary>
/// <param name="sheetName">工作表名</param>
public void CreateWorkSheet(string sheetName)
{
Excel.Worksheet newWorksheet = (Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
newWorksheet.Name = sheetName;
}
/// <summary>
/// 激活工作表
/// </summary>
/// <param name="sheetName">工作表名</param>
public void ActivateSheet(string sheetName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetName];
worksheet.Activate();
}
/// <summary>
/// 激活工作表
/// </summary>
/// <param name="sheetNum">工作表序號(hào)</param>
public void ActivateSheet(int sheetNum)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Activate();
}
/// <summary>
/// 刪除一個(gè)工作表
/// </summary>
/// <param name="SheetName">刪除的工作表名</param>
public void DeleteSheet(int sheetNum)
{
((Excel.Worksheet)myWorkBook.Worksheets[sheetNum]).Delete();
}
/// <summary>
/// 刪除一個(gè)工作表
/// </summary>
/// <param name="SheetName">刪除的工作表序號(hào)</param>
public void DeleteSheet(string sheetName)
{
((Excel.Worksheet)myWorkBook.Worksheets[sheetName]).Delete();
}
/// <summary>
/// 合并單元格
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
public void CellsUnite(int startRow, int startColumn, int endRow, int endColumn)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.MergeCells = true;
}
/// <summary>
/// 單元格文字對(duì)齊方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="hAlign">水平對(duì)齊</param>
/// <param name="vAlign">垂直對(duì)齊</param>
public void CellsAlignment(int startRow, int startColumn, int endRow, int endColumn, ExcelHAlign hAlign, ExcelVAlign vAlign)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.HorizontalAlignment = hAlign;
range.VerticalAlignment = vAlign;
}
/// <summary>
/// 繪制指定單元格的邊框
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
public void CellsDrawFrame(int startRow, int startColumn, int endRow, int endColumn)
{
CellsDrawFrame(startRow, startColumn, endRow, endColumn,
true, true, true, true, true, true, false, false,
LineStyle.連續(xù)直線, BorderWeight.細(xì), ColorIndex.自動(dòng));
}
/// <summary>
/// 繪制指定單元格的邊框
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="isDrawTop">是否畫(huà)上外框</param>
/// <param name="isDrawBottom">是否畫(huà)下外框</param>
/// <param name="isDrawLeft">是否畫(huà)左外框</param>
/// <param name="isDrawRight">是否畫(huà)右外框</param>
/// <param name="isDrawHInside">是否畫(huà)水平內(nèi)框</param>
/// <param name="isDrawVInside">是否畫(huà)垂直內(nèi)框</param>
/// <param name="isDrawDown">是否畫(huà)斜向下線</param>
/// <param name="isDrawUp">是否畫(huà)斜向上線</param>
/// <param name="lineStyle">線類型</param>
/// <param name="borderWeight">線粗細(xì)</param>
/// <param name="color">線顏色</param>
public void CellsDrawFrame(int startRow, int startColumn, int endRow, int endColumn,
bool isDrawTop, bool isDrawBottom, bool isDrawLeft, bool isDrawRight,
bool isDrawHInside, bool isDrawVInside, bool isDrawDiagonalDown, bool isDrawDiagonalUp,
LineStyle lineStyle, BorderWeight borderWeight, ColorIndex color)
{
//獲取畫(huà)邊框的單元格
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
//清除所有邊框
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = LineStyle.無(wú);
range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = LineStyle.無(wú);
//以下是按參數(shù)畫(huà)邊框
if (isDrawTop)
{
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeTop].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeTop].ColorIndex = color;
}
if (isDrawBottom)
{
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeBottom].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeBottom].ColorIndex = color;
}
if (isDrawLeft)
{
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeLeft].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeLeft].ColorIndex = color;
}
if (isDrawRight)
{
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeRight].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeRight].ColorIndex = color;
}
if (isDrawVInside)
{
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlInsideVertical].Weight = borderWeight;
range.Borders[XlBordersIndex.xlInsideVertical].ColorIndex = color;
}
if (isDrawHInside)
{
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = borderWeight;
range.Borders[XlBordersIndex.xlInsideHorizontal].ColorIndex = color;
}
if (isDrawDiagonalDown)
{
range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlDiagonalDown].Weight = borderWeight;
range.Borders[XlBordersIndex.xlDiagonalDown].ColorIndex = color;
}
if (isDrawDiagonalUp)
{
range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlDiagonalUp].Weight = borderWeight;
range.Borders[XlBordersIndex.xlDiagonalUp].ColorIndex = color;
}
}
/// <summary>
/// 單元格背景色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="color">顏色索引</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = Pattern.Solid;
}
/// <summary>
/// 單元格背景色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="color">顏色索引</param>
/// <param name="pattern">填充方式</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color, Pattern pattern)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = pattern;
}
/// <summary>
/// 設(shè)置行高
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="height">行高</param>
public void SetRowHeight(int startRow, int endRow, int height)
{
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];
range.RowHeight = height;
}
/// <summary>
/// 自動(dòng)調(diào)整行高
/// </summary>
/// <param name="columnNum">列號(hào)</param>
public void RowAutoFit(int rowNum)
{
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 設(shè)置列寬
/// </summary>
/// <param name="startColumn">起始列(列對(duì)應(yīng)的字母)</param>
/// <param name="endColumn">結(jié)束列(列對(duì)應(yīng)的字母)</param>
/// <param name="width"></param>
public void SetColumnWidth(string startColumn, string endColumn, int width)
{
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
/// 設(shè)置列寬
/// </summary>
/// <param name="startColumn">起始列</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="width"></param>
public void SetColumnWidth(int startColumn, int endColumn, int width)
{
string strStartColumn = GetColumnName(startColumn);
string strEndColumn = GetColumnName(endColumn);
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
/// 自動(dòng)調(diào)整列寬
/// </summary>
/// <param name="columnNum">列號(hào)</param>
public void ColumnAutoFit(string column)
{
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 自動(dòng)調(diào)整列寬
/// </summary>
/// <param name="columnNum">列號(hào)</param>
public void ColumnAutoFit(int columnNum)
{
string strcolumnNum = GetColumnName(columnNum);
//獲取當(dāng)前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 字體顏色
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="color">顏色索引</param>
public void FontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.ColorIndex = color;
}
/// <summary>
/// 字體樣式(加粗,斜體,下劃線)
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="isBold">是否加粗</param>
/// <param name="isItalic">是否斜體</param>
/// <param name="underline">下劃線類型</param>
public void FontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, UnderlineStyle underline)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.Bold = isBold;
range.Font.Underline = underline;
range.Font.Italic = isItalic;
}
/// <summary>
/// 單元格字體及大小
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">結(jié)束行</param>
/// <param name="endColumn">結(jié)束列</param>
/// <param name="fontName">字體名稱</param>
/// <param name="fontSize">字體大小</param>
public void FontNameSize(int startRow, int startColumn, int endRow, int endColumn,string fontName, int fontSize)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.Name = fontName;
range.Font.Size = fontSize;
}
/// <summary>
/// 打開(kāi)一個(gè)存在的Excel文件
/// </summary>
/// <param name="fileName">Excel完整路徑加文件名</param>
public void Open(string fileName)
{
myExcel = new Excel.Application();
myWorkBook = myExcel.Workbooks.Add(fileName);
myFileName = fileName;
}
/// <summary>
/// 保存Excel
/// </summary>
/// <returns>保存成功返回True</returns>
public bool Save()
{
if (myFileName == "")
{
return false;
}
else
{
try
{
myWorkBook.Save();
return true;
}
catch (Exception ex)
{
return false;
}
}
}
/// <summary>
/// Excel文檔另存為
/// </summary>
/// <param name="fileName">保存完整路徑加文件名</param>
/// <returns>保存成功返回True</returns>
public bool SaveAs(string fileName)
{
try
{
myWorkBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
return true;
}
catch (Exception ex)
{
return false;
}
}
/// <summary>
/// 關(guān)閉Excel
/// </summary>
public void Close()
{
myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
/// <summary>
/// 關(guān)閉Excel
/// </summary>
/// <param name="isSave">是否保存</param>
public void Close(bool isSave)
{
myWorkBook.Close(isSave, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
/// <summary>
/// 關(guān)閉Excel
/// </summary>
/// <param name="isSave">是否保存</param>
/// <param name="fileName">存儲(chǔ)文件名</param>
public void Close(bool isSave,string fileName)
{
myWorkBook.Close(isSave, fileName, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
#region 私有成員
private string GetColumnName(int number)
{
int h, l;
h = number / 26;
l = number % 26;
if (l == 0)
{
h -= 1;
l = 26;
}
string s = GetLetter(h) + GetLetter(l);
return s;
}
private string GetLetter(int number)
{
switch (number)
{
case 1:
return "A";
case 2:
return "B";
case 3:
return "C";
case 4:
return "D";
case 5:
return "E";
case 6:
return "F";
case 7:
return "G";
case 8:
return "H";
case 9:
return "I";
case 10:
return "J";
case 11:
return "K";
case 12:
return "L";
case 13:
return "M";
case 14:
return "N";
case 15:
return "O";
case 16:
return "P";
case 17:
return "Q";
case 18:
return "R";
case 19:
return "S";
case 20:
return "T";
case 21:
return "U";
case 22:
return "V";
case 23:
return "W";
case 24:
return "X";
case 25:
return "Y";
case 26:
return "Z";
default:
return "";
}
}
#endregion
}
/// <summary>
/// 水平對(duì)齊方式
/// </summary>
public enum ExcelHAlign
{
常規(guī) = 1,
靠左,
居中,
靠右,
填充,
兩端對(duì)齊,
跨列居中,
分散對(duì)齊
}
/// <summary>
/// 垂直對(duì)齊方式
/// </summary>
public enum ExcelVAlign
{
靠上 = 1,
居中,
靠下,
兩端對(duì)齊,
分散對(duì)齊
}
/// <summary>
/// 線粗
/// </summary>
public enum BorderWeight
{
極細(xì) = 1,
細(xì) = 2,
粗 = -4138,
極粗 = 4
}
/// <summary>
/// 線樣式
/// </summary>
public enum LineStyle
{
連續(xù)直線 = 1,
短線 = -4115,
線點(diǎn)相間 = 4,
短線間兩點(diǎn) = 5,
點(diǎn) = -4118,
雙線 = -4119,
無(wú) = -4142,
少量?jī)A斜點(diǎn) = 13
}
/// <summary>
/// 下劃線方式
/// </summary>
public enum UnderlineStyle
{
無(wú)下劃線 = -4142,
雙線 = - 4119,
雙線充滿全格 = 5,
單線 = 2,
單線充滿全格 = 4
}
/// <summary>
/// 單元格填充方式
/// </summary>
public enum Pattern
{
Automatic = -4105,
Checker = 9,
CrissCross = 16,
Down = -4121,
Gray16 = 17,
Gray25 = -4124,
Gray50 = -4125,
Gray75 = -4126,
Gray8 = 18,
Grid = 15,
Horizontal = -4128,
LightDown = 13,
LightHorizontal = 11,
LightUp = 14,
LightVertical = 12,
None = -4142,
SemiGray75 = 10,
Solid = 1,
Up = -4162,
Vertical = -4166
}
/// <summary>
/// 常用顏色定義,對(duì)就Excel中顏色名
/// </summary>
public enum ColorIndex
{
無(wú)色 = -4142,
自動(dòng) = -4105,
黑色 = 1,
褐色 = 53,
橄欖 = 52,
深綠 = 51,
深青 = 49,
深藍(lán) = 11,
靛藍(lán) = 55,
灰色80 = 56,
深紅 = 9,
橙色 = 46,
深黃 = 12,
綠色 = 10,
青色 = 14,
藍(lán)色 = 5,
藍(lán)灰 = 47,
灰色50 = 16,
紅色 = 3,
淺橙色 = 45,
酸橙色 = 43,
海綠 = 50,
水綠色 = 42,
淺藍(lán) = 41,
紫羅蘭 = 13,
灰色40 = 48,
粉紅 = 7,
金色 = 44,
黃色 = 6,
鮮綠 = 4,
青綠 = 8,
天藍(lán) = 33,
梅紅 = 54,
灰色25 = 15,
玫瑰紅 = 38,
茶色 = 40,
淺黃 = 36,
淺綠 = 35,
淺青綠 = 34,
淡藍(lán) = 37,
淡紫 = 39,
白色 = 2
}
}
http://xingfustar.cnblogs.com
