|
/// <summary>
/// Excel幫助類
/// Add by alswl 20101130
/// </summary>
public class ExcelHelper : IDisposable
{
private Excel._Application excelApplication = null;
public Excel._Workbook workbook = null;
public Excel._Worksheet worksheet = null;
private object missing = System.Reflection.Missing.Value;
public ExcelHelper()
{
if (excelApplication == null)
{
excelApplication = new Excel.ApplicationClass();
}
}
/// <summary>
/// ~
/// </summary>
~ExcelHelper()
{
if (excelApplication != null)
excelApplication.Quit();
}
/// <summary>
/// 釋放非托管資源
/// </summary>
public void Dispose()
{
try
{
if (excelApplication != null)
{
if (!workbook.Saved)
this.Close(false);
excelApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication);
GC.Collect(System.GC.GetGeneration(worksheet));
GC.Collect(System.GC.GetGeneration(workbook));
GC.Collect(System.GC.GetGeneration(excelApplication));
if (excelApplication != null)
{
excelApplication = null;
}
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
catch
{
}
finally
{
GC.Collect();
}
}
/// <summary>
/// 設(shè)置當(dāng)前工作表
/// </summary>
public int CurrentWorksheetIndex
{
set
{
if (value <= 0 || value > workbook.Worksheets.Count)
throw new ArgumentException('索引超出范圍');
else
{
object index = value;
worksheet = workbook.Worksheets[index] as Excel._Worksheet;
}
}
}
/// <summary>
/// 打開一個Excel工作薄
/// </summary>
/// <param name='fileName'></param>
public void OpenWorkbook(string fileName)
{
workbook = excelApplication.Workbooks.Open(fileName, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
if (workbook.Worksheets.Count > 0)
{
int index = 1;
worksheet = workbook.Worksheets[index] as Excel._Worksheet;
}
}
/// <summary>
/// 添加一個工作表
/// </summary>
/// <param name='SheetName'></param>
/// <returns></returns>
public Excel.Worksheet AddSheet(string sheetName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet.Name = sheetName;
return worksheet;
}
/// <summary>
/// 刪除一個Sheet
/// </summary>
/// <param name='sheetName'></param>
public void DeleteSheet(string sheetName)
{
Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet;
excelApplication.DisplayAlerts = false;
sheet.Delete();
excelApplication.DisplayAlerts = true;
}
/// <summary>
/// 保存數(shù)據(jù)
/// </summary>
public void Save()
{
if (workbook != null)
{
workbook.Save();
}
}
/// <summary>
/// 關(guān)閉文檔
/// </summary>
/// <param name='isSave'></param>
public void Close(bool isSave)
{
object obj_Save = isSave;
workbook.Close(obj_Save, missing, missing);
}
/// <summary>
/// 設(shè)置當(dāng)前工作表中某單元格的值
/// </summary>
/// <param name='range'></param>
/// <param name='value'></param>
public void SetRangeValue(string range, object value)
{
SetRangeValue(worksheet.Name, range, value);
}
/// <summary>
/// 設(shè)置工作表中某單元格的值
/// </summary>
/// <param name='sheetName'></param>
/// <param name='range'></param>
/// <param name='value'></param>
public void SetRangeValue(string sheetName, string range, object value)
{
Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet;
sheet.get_Range(range, missing).Value2 = value;
}
/// <summary>
/// 刪除某個Range,，右側(cè)左移
/// </summary>
/// <param name='sheetName'></param>
/// <param name='rangeStr'></param>
public void DeleteRange(string sheetName, string rangeStr)
{
Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet;
sheet.get_Range(rangeStr, missing).Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
}
/// <summary>
/// 刪除某個Range，右側(cè)左移
/// </summary>
/// <param name='sheetName'></param>
/// <param name='rangeStr'></param>
public void DeleteRange(string rangeStr)
{
DeleteRange(worksheet.Name, rangeStr);
}
/// <summary>
/// 合并單元格
/// </summary>
/// <param name='sheetName'></param>
/// <param name='range1'></param>
/// <param name='range2'></param>
public void Merge(string sheetName, string range1, string range2)
{
Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet;
Excel.Range range = worksheet.get_Range(range1, range2);
range.Merge(true);
}
/// <summary>
/// 合并單元格
/// </summary>
/// <param name='range1'></param>
/// <param name='range2'></param>
public void Merge(string range1, string range2)
{
Merge(worksheet.Name, range1, range2);
}
/// <summary>
/// 設(shè)置一個單元格的屬性
/// </summary>
/// <param name='sheetName'></param>
/// <param name='range'></param>
/// <param name='size'>大小</param>
/// <param name='name'>字體</param>
/// <param name='color'>顏色</param>
/// <param name='HorizontalAlignment'>對齊方式</param>
public void SetCellProperty(string sheetName, string rangeStr, double fontSize, string fontName,
double height, Excel.Constants horizontalStyle)
{
Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet;
Excel.Range range = sheet.get_Range(rangeStr, missing);
range.Font.Size = fontSize;
range.Font.Name = fontName;
//range.Font.Color = fontColor;
range.RowHeight = height;
range.HorizontalAlignment = horizontalStyle;
}
/// <summary>
/// 設(shè)置一個單元格的屬性
/// </summary>
/// <param name='range'></param>
/// <param name='fontSize'></param>
/// <param name='fontName'></param>
/// <param name='fontColor'></param>
/// <param name='horizontalStyle'></param>
public void SetCellProperty(string range, double fontSize, string fontName,
double height, Excel.Constants horizontalStyle)
{
SetCellProperty(worksheet.Name, range, fontSize, fontName,
height, horizontalStyle);
}
/// <summary>
/// 設(shè)定Range的邊框格式
/// </summary>
/// <param name='rangeStart'></param>
/// <param name='rangeEnd'></param>
/// <param name='topStyle'></param>
/// <param name='rightStyle'></param>
/// <param name='bottomStyle'></param>
/// <param name='leftStyle'></param>
public void SetRangeBorderStyle(string sheetName, string rangeStr, Excel.XlLineStyle topStyle,
Excel.XlLineStyle rightStyle, Excel.XlLineStyle bottomStyle, Excel.XlLineStyle leftStyle)
{
Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet;
Excel.Range range = sheet.get_Range(rangeStr, missing);
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = topStyle;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = rightStyle;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = bottomStyle;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = leftStyle;
}
/// <summary>
/// 設(shè)定Range的邊框格式
/// </summary>
/// <param name='rangeStr'></param>
/// <param name='topStyle'></param>
/// <param name='rightStyle'></param>
/// <param name='bottomStyle'></param>
/// <param name='leftStyle'></param>
public void SetRangeBorderStyle(string rangeStr, Excel.XlLineStyle topStyle,
Excel.XlLineStyle rightStyle, Excel.XlLineStyle bottomStyle, Excel.XlLineStyle leftStyle)
{
SetRangeBorderStyle(worksheet.Name, rangeStr, topStyle,
rightStyle, bottomStyle, leftStyle);
}
/// <summary>
/// 設(shè)定Range數(shù)字格式
/// </summary>
/// <param name='sheetName'></param>
/// <param name='rangeStr'></param>
/// <param name='format'></param>
public void SetRangeNumberFormat(string sheetName, string rangeStr, string format)
{
Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet;
Excel.Range range = sheet.get_Range(rangeStr, missing);
range.NumberFormat = format;//0%
}
/// <summary>
/// 設(shè)定Range數(shù)字格式
/// </summary>
/// <param name='rangeStr'></param>
/// <param name='format'></param>
public void SetRangeNumberFormat(string rangeStr, string format)
{
SetRangeNumberFormat(worksheet.Name, rangeStr, format);
}
/// <summary>
/// 將數(shù)據(jù)表格添加到Excel指定工作表的指定位置
/// </summary>
/// <param name='dt'></param>
/// <param name='ws'></param>
/// <param name='startX'></param>
/// <param name='startY'></param>
public void AddTable(System.Data.DataTable dt, int startX, int startY)
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
worksheet.Cells[i + startX, j + startY] = dt.Rows[i][j];
}
}
}
/// <summary>
/// 加入分頁符
/// </summary>
/// <param name='rangeStr'></param>
/// <param name='lineNumber'></param>
public void AddPageBreak(string rangeStr, int lineNumber)
{
Excel.Range range = worksheet.get_Range('A' + lineNumber.ToString(), missing);
worksheet.HPageBreaks.Add(range);
}
/// <summary>
/// 加入分頁符
/// </summary>
/// <param name='lineNumber'></param>
public void AddPageBreak(int lineNumber)
{
AddPageBreak(worksheet.Name, lineNumber);
}
/// <summary>
/// 從當(dāng)前工作表數(shù)據(jù)區(qū)域復(fù)制數(shù)據(jù)到另一個區(qū)域
/// </summary>
/// <param name='sheetFromRange'></param>
/// <param name='sheetToRange'></param>
public void CopyRange2Range(string sheetFromRange, string sheetToRange)
{
CopyRange2Range(worksheet.Name, worksheet.Name, sheetFromRange, sheetToRange);
}
/// <summary>
/// 從一個表的某個數(shù)據(jù)區(qū)域復(fù)制數(shù)據(jù)到另一個表的某個區(qū)域
/// </summary>
/// <param name='sheetFromName'></param>
/// <param name='sheetToName'></param>
/// <param name='sheetFromRange'></param>
/// <param name='sheetToRange'></param>
public void CopyRange2Range(string sheetFromName, string sheetToName, string sheetFromRange, string sheetToRange)
{
Excel.Worksheet sheetFrom = workbook.Worksheets[sheetFromName] as Excel.Worksheet;
Excel.Worksheet sheetTo = workbook.Worksheets[sheetToName] as Excel.Worksheet;
sheetFrom.get_Range(sheetFromRange, missing).Copy(sheetTo.get_Range(sheetToRange, missing));
}
/// <summary>
/// 移動Range到另一個Range
/// </summary>
/// <param name='FromRange'></param>
/// <param name='ToRange'></param>
public void MoveRange2Range(string FromRange, string ToRange)
{
MoveRange2Range(worksheet.Name, FromRange, ToRange);
}
/// <summary>
/// 移動Range到另一個Range
/// </summary>
/// <param name='sheetName'></param>
/// <param name='FromRange'></param>
/// <param name='ToRange'></param>
public void MoveRange2Range(string sheetName, string FromRange, string ToRange)
{
Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet;
sheet.get_Range(FromRange, missing).Cut(sheet.get_Range(ToRange, missing));
}
/// <summary>
/// 重設(shè)當(dāng)前工作目錄的打印區(qū)域
/// </summary>
public void ResetPrintArea()
{
SetPrintArea(worksheet.Name, '');
}
/// <summary>
/// 重設(shè)工作目錄的打印區(qū)域
/// </summary>
public void ResetPrintArea(string sheetName)
{
SetPrintArea(sheetName, '');
}
/// <summary>
/// 設(shè)定工作目錄的打印區(qū)域
/// </summary>
/// <param name='area'></param>
public void SetPrintArea(string area)
{
SetPrintArea(worksheet.Name, area);
}
/// <summary>
/// 設(shè)定工作目錄的打印區(qū)域
/// </summary>
/// <param name='sheetName'></param>
/// <param name='area'></param>
public void SetPrintArea(string sheetName, string area)
{
Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet;
sheet.PageSetup.PrintArea = area;
}
/// <summary>
/// 將當(dāng)前工作表中的表格數(shù)據(jù)復(fù)制到剪切板
/// </summary>
public void Copy()
{
if (worksheet != null)
{
try
{
worksheet.UsedRange.Select();
}
catch { }
worksheet.UsedRange.Copy(missing);
}
}
}
|
