Excel操作-优快云博客

/**

* Excel控制类

* 作者：王文斌

* 版本：bate2 2008-6-6

* 备注：部分方法未经测试，使用时请注意。新增复制模板方法。支持26列以上表格。

* 使用方法：确保已安装office03，并安装Excel的.net开发支持

* 引用com口dll

* */

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;

//}

}

转载一个EXCEL操作类