using System;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Drawing;
namespace WWBClassLib.Excel
{
/// <summary>
/// ExcelClass 的摘要说明。
/// </summary>
public class ExcelCtrl
{
/// <summary>
/// 构建ExcelClass类
/// </summary>
public ExcelCtrl()
{
this.m_objExcel = new Microsoft.Office.Interop.Excel.Application();
}
/// <summary>
/// 构建ExcelClass类
/// </summary>
/// <param name="objExcel">Excel.Application</param>
public ExcelCtrl(Microsoft.Office.Interop.Excel.Application objExcel)
{
this.m_objExcel = objExcel;
}
/// <summary>
/// 列标号
/// </summary>
private string AList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
/// <summary>
/// 获取描述区域的字符
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <returns></returns>
public string GetAix(int x, int y)
{
char[] AChars = AList.ToCharArray();
int i = x % 26;
int j = x / 26;
//if (x >= 26) { return ""; }
string s = "";
if (j > 0)
{
s = s + AChars[j - 1].ToString() + AChars[i].ToString();
}
else
{
s = s + AChars[x - 1].ToString();
}
//s = s + AChars[x - 1].ToString();
s = s + y.ToString();
return s;
}
/// <summary>
/// 获取当前活动Sheet的Range的值
/// </summary>
/// <param name="y"></param>
/// <param name="x"></param>
/// <returns></returns>
public string GetRangeText(int y, int x)
{
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
return range.Text.ToString();
}
/// <summary>
/// 给单元格赋值1
/// </summary>
/// <param name="x">行号</param>
/// <param name="y">列号</param>
/// <param name="align">对齐（CENTER、LEFT、RIGHT）</param>
/// <param name="text">值</param>
public void setValue(int y, int x, string align, string text)
{
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
range.set_Value(miss, text);
if (align.ToUpper() == "CENTER")
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
if (align.ToUpper() == "LEFT")
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
if (align.ToUpper() == "RIGHT")
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
}
}
/// <summary>
/// 给单元格赋值2
/// </summary>
/// <param name="x">行号</param>
/// <param name="y">列号</param>
/// <param name="text">值</param>
public void setValue(int y, int x, string text)
{
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
range.set_Value(miss, text);
}
/// <summary>
/// 给单元格赋值3
/// </summary>
/// <param name="x">行号</param>
/// <param name="y">列号</param>
/// <param name="text">值</param>
/// <param name="font">字符格式</param>
/// <param name="color">颜色</param>
public void setValue(int y, int x, string text, System.Drawing.Font font, System.Drawing.Color color)
{
this.setValue(x, y, text);
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
range.Font.Size = font.Size;
range.Font.Bold = font.Bold;
range.Font.Color = color;
range.Font.Name = font.Name;
range.Font.Italic = font.Italic;
range.Font.Underline = font.Underline;
}
/// <summary>
/// 给单元格赋值4
/// </summary>
/// <param name="x">行号</param>
/// <param name="y">列号</param>
/// <param name="text">值</param>
/// <param name="font">字符格式</param>
public void setValue(int y, int x, string text, float size)
{
this.setValue(x, y, text);
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
range.Font.Size = size;
}
//public void CopeTo(string fromSheetName,string startCell,string endCell,string ToSheetName)
//{
// Microsoft.Office.Interop.Excel.Worksheet fromsheet = GetSheet(fromSheetName);
// Microsoft.Office.Interop.Excel.Worksheet tosheet = GetSheet(ToSheetName);
// Microsoft.Office.Interop.Excel.Range rangefrom = fromsheet.get_Range(startCell, endCell);
// Microsoft.Office.Interop.Excel.Range rangeto = tosheet.get_Range(startCell, endCell);
// //rangeto.Copy(rangefrom);
// tosheet.Paste(rangefrom, rangeto);
//}
/// <summary>
/// 复制sheet
/// 修正完成，重命名即可
///
/// </summary>
/// <param name="fromSheetName"></param>
/// <param name="ToSheetName"></param>
/// <returns></returns>
public void CopySheet(string fromSheetName, string ToSheetName)
{
Microsoft.Office.Interop.Excel.Worksheet fromsheet = GetSheet(fromSheetName);
fromsheet.Copy(fromsheet, miss);
ReNameSheet(fromSheetName + " (2)", ToSheetName);
}
/// <summary>
/// 插入新行 将设定行拷贝，然后插入空行，再将设定行粘贴到新行上
/// </summary>
/// <param name="y">模板行号</param>
public void insertRow(int y)
{
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y));
range.Copy(miss);
range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, miss);
range.get_Range(GetAix(1, y), GetAix(25, y));
range.Select();
sheet.Paste(miss, miss);
}
//public void insertCopyRow(int x1,int y1,int x2,int y2)
//{
// Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y));
// range.Copy(miss);
// range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, miss);
// range.get_Range(GetAix(1, y), GetAix(25, y));
// range.Select();
// sheet.Paste(miss, miss);
//}
///// <summary>
///// 把剪切内容粘贴到指定区域
///// </summary>
public void past(int x1, int y1, int x2, int y2, int x3, int y3, int x4, int y4)
{
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(GetAix(x1, y1), GetAix(x2, y2));
range.Copy(miss);
sheet.Paste(sheet.get_Range(this.GetAix(x3, y3), this.GetAix(x4, y4)), range);
}
/// <summary>
/// 设置边框
/// </summary>
/// <param name="x1"></param>
/// <param name="y1"></param>
/// <param name="x2"></param>
/// <param name="y2"></param>
/// <param name="Width"></param>
public void setBorder(int x1, int y1, int x2, int y2, int Width)
{
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
range.Borders.Weight = Width;
}
/// <summary>
/// 合并单元格，并设置居中方式 测试有问题 修正
/// </summary>
/// <param name="x1"></param>
/// <param name="y1"></param>
/// <param name="x2"></param>
/// <param name="y2"></param>
/// <param name="align">居中方式：center,left,right</param>
public void mergeCell(int x1, int y1, int x2, int y2, string align)
{
UniteCells(sheet, x1, y1, x2, y2);
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
//range.Merge(true);
if (align.ToUpper() == "CENTER")
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
if (align.ToUpper() == "LEFT")
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
if (align.ToUpper() == "RIGHT")
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
}
}
/// <summary>
/// 获取Range区域
/// </summary>
/// <param name="x1"></param>
/// <param name="y1"></param>
/// <param name="x2"></param>
/// <param name="y2"></param>
/// <returns></returns>
public Microsoft.Office.Interop.Excel.Range getRange(int x1, int y1, int x2, int y2)
{
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
return range;
}
private object miss = Missing.Value; //忽略的参数OLENULL
private Microsoft.Office.Interop.Excel.Application m_objExcel;//Excel应用程序实例
private Microsoft.Office.Interop.Excel.Workbooks m_objBooks;//工作表集合
private Microsoft.Office.Interop.Excel.Workbook m_objBook;//当前操作的工作表
private Microsoft.Office.Interop.Excel.Worksheet sheet;//当前操作的表格
/// <summary>
/// 活动工作Sheet
/// </summary>
public Microsoft.Office.Interop.Excel.Worksheet CurrentSheet
{
get
{
return sheet;
}
set
{
this.sheet = value;
}
}
/// <summary>
/// 获取活动WorkBooks
/// </summary>
public Microsoft.Office.Interop.Excel.Workbooks CurrentWorkBooks
{
get
{
return this.m_objBooks;
}
set
{
this.m_objBooks = value;
}
}
/// <summary>
/// 获取活动WorkBook
/// </summary>
public Microsoft.Office.Interop.Excel.Workbook CurrentWorkBook
{
get
{
return this.m_objBook;
}
set
{
this.m_objBook = value;
}
}
/// <summary>
/// 打开Excel文件
/// </summary>
/// <param name="filename">路径</param>
public void OpenExcelFile(string filename)
{
UserControl(false);
m_objExcel.Workbooks.Open(filename, miss, miss, miss, miss, miss, miss, miss,
miss, miss, miss, miss, miss, miss, miss);
m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = m_objExcel.ActiveWorkbook;
sheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet;
}
public void UserControl(bool usercontrol)
{
if (m_objExcel == null) { return; }
m_objExcel.UserControl = usercontrol;
m_objExcel.DisplayAlerts = usercontrol;
m_objExcel.Visible = usercontrol;
}
/// <summary>
/// 创建Excel文件
/// </summary>
public void CreateExceFile()
{
UserControl(false);
m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Microsoft.Office.Interop.Excel.Workbook)(m_objBooks.Add(miss));
sheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet;
}
/// <summary>
/// Excel文件另存为
/// </summary>
/// <param name="FileName"></param>
public void SaveAs(string FileName)
{
m_objBook.SaveAs(FileName, miss, miss, miss, miss,
miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges,
miss, miss, miss, miss);
//m_objBook.Close(false, miss, miss);
}
/// <summary>
/// 释放Excel文件
/// </summary>
public void ReleaseExcel()
{
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet);
m_objExcel = null;
m_objBooks = null;
m_objBook = null;
sheet = null;
GC.Collect();
}
/////////////////////////////////
public bool KillAllExcelApp()
{
try
{
if (m_objExcel != null) // isRunning是判断xlApp是怎么启动的flag.
{
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
//释放COM组件，其实就是将其引用计数减1
//System.Diagnostics.Process theProc;
foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
//先关闭图形窗口。如果关闭失败...有的时候在状态里看不到图形窗口的excel了，
//但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要杀掉它:p
if (theProc.CloseMainWindow() == false)
{
theProc.Kill();
}
}
m_objExcel = null;
return true;
}
}
catch
{
return false;
}
return true;
}
/////////////////////////////////////////////
/// <summary>
/// 获取一个工作表
/// </summary>
/// <param name="SheetName">Sheet名</param>
/// <returns>Sheet对象</returns>
public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets[SheetName];
return s;
}
/// <summary>
/// 添加一个工作表
/// </summary>
/// <param name="SheetName"></param>
/// <returns></returns>
public Microsoft.Office.Interop.Excel.Worksheet AddSheetWithReturn(string SheetName)
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
s.Name = SheetName;
return s;
}
/// <summary>
/// 添加一个工作表
/// </summary>
public void AddSheet(string SheetName)
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
s.Name = SheetName;
}
/// <summary>
/// 删除一个工作表
/// </summary>
/// <param name="SheetName"></param>
public void DelSheet(string SheetName)
{
((Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets[SheetName]).Delete();
}
/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="OldSheetName">原名</param>
/// <param name="NewSheetName">新名</param>
/// <returns></returns>
public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets[OldSheetName];
s.Name = NewSheetName;
return s;
}
public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二
{
Sheet.Name = NewSheetName;
return Sheet;
}
/// <summary>
/// 设置单元格值
/// </summary>
/// <param name="ws"></param>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value)
{
ws.Cells[y, x] = value;
}
/// <summary>
/// 设置单元格值
/// </summary>
/// <param name="ws">sheet名</param>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void SetCellValue(string ws, int x, int y, object value)
{
GetSheet(ws).Cells[y, x] = value;
}
public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
//设置一个单元格的属性 字体， 大小，颜色 ，对齐方式
{
//name = "宋体";
//size = 12;
color = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;
HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Name = name;
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Size = size;
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Color = color;
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = HorizontalAlignment;
}
/// <summary>
/// 设置单元格属性
/// </summary>
/// <param name="wsn">Sheet页名称</param>
/// <param name="Startx">x1</param>
/// <param name="Starty">y1</param>
/// <param name="Endx">x2</param>
/// <param name="Endy">y2</param>
/// <param name="size">字体大小</param>
/// <param name="name">字体名称</param>
/// <param name="color">字体颜色</param>
/// <param name="HorizontalAlignment">对齐方式</param>
public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
{
//name = "宋体";
//size = 12;
//color = Excel.Constants.xlAutomatic;
//HorizontalAlignment = Excel.Constants.xlRight;
Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Name = name;
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Size = size;
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Color = color;
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = HorizontalAlignment;
}
/// <summary>
/// 设置单元格属性
/// </summary>
/// <param name="wsn">Sheet页名称</param>
/// <param name="Startx">x1</param>
/// <param name="Starty">y1</param>
/// <param name="Endx">x2</param>
/// <param name="Endy">y2</param>
/// <param name="size">字体大小</param>
/// <param name="name">字体名称</param>
/// <param name="color">字体颜色名称（系统预定义颜色）</param>
/// <param name="HorizontalAlignment">对齐方式</param>
public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, string colorName, string HorizontalAlignment)
{
//name = "宋体";
//size = 12;
//color = Excel.Constants.xlAutomatic;
//HorizontalAlignment = Excel.Constants.xlRight;
Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Name = name;
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Size = size;
Color color = Color.FromName(colorName);
color = Color.FromArgb(color.B, color.G, color.R);
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Color = color.ToArgb();
if (HorizontalAlignment.ToUpper() == "CENTER")
{
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ;
}
if (HorizontalAlignment.ToUpper() == "LEFT")
{
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
if (HorizontalAlignment.ToUpper() == "RIGHT")
{
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
}
}
/// <summary>
/// 设置单元格底色
/// </summary>
/// <param name="wsn">sheet页名称</param>
/// <param name="Startx">x1</param>
/// <param name="Starty">y1</param>
/// <param name="Endx">x2</param>
/// <param name="Endy">y2</param>
/// <param name="colorName">系统颜色名称</param>
public void SetBorderColor(string wsn, int Startx, int Starty, int Endx, int Endy, string colorName)
{
Color color = Color.FromName(colorName);
color = Color.FromArgb(color.B, color.G, color.R);
Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Cells.Interior.Color = color.ToArgb();
ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Cells.Interior.Pattern = Microsoft.Office.Interop.Excel.XlBackground.xlBackgroundAutomatic;
}
//合并单元格 测试ok
public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)
{
ws.get_Range(ws.Cells[y1, x1], ws.Cells[y2, x2]).Merge(Type.Missing);
}
//合并单元格 测试ok
public void UniteCells(string ws, int x1, int y1, int x2, int y2)
{
GetSheet(ws).get_Range(GetSheet(ws).Cells[y1, x1], GetSheet(ws).Cells[y2, x2]).Merge(Type.Missing);
}
//将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
GetSheet(ws).Cells[startY + i, j + startX] = dt.Rows[i][j].ToString();
}
}
}
//将内存中数据表格插入到Excel指定工作表的指定位置二
public void InsertTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
ws.Cells[startY + i, j + startX] = dt.Rows[i][j];
}
}
}
//插入图片操作一
public void InsertPictures(string Filename, string ws, int x, int y, int wight, int lenth)
{
//后面的数字表示位置
GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, x, y, wight, lenth);
}
//public void InsertPictures(string Filename, string ws, int Height, int Width)
//插入图片操作二
//{
// GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
// GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;
// GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;
//}
//public void InsertPictures(string Filename, string ws, int left, int top, int Height, int Width)
//插入图片操作三
//{
// GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
// GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left);
// GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top);
// GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;
// GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;
//}
}
}
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/hfzsjz/archive/2008/12/16/3521706.aspx