大全一:
//引入Excel的COM组件
using System;
using Excel = Microsoft.Office.Interop.Excel;//.NET里面选
using Office = Microsoft.Office.Core;//COM 里面选Microft Office 12.0 object Library
namespace ExcelTest
{
/// <summary>
/// EXCEL文件操作类,先创建或者打开再进行操作,注意创建和打开最后保存时的区别
/// </summary>
public class ExcelHelper
{
/// <summary>
/// 文件名称
/// </summary>
private string mFilename;
/// <summary>
/// excel 对象
/// </summary>
private Excel.Application app;
/// <summary>
/// 工作表组
/// </summary>
private Excel.Workbooks wbs;
/// <summary>
/// 工作表
/// </summary>
private Excel.Workbook wb;
/// <summary>
/// 创建一个Excel对象
/// </summary>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool Create()
{
try
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 打开一个Excel文件
/// </summary>
/// <param name="FileName">文件名(路径)</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool Open(string FileName)
{
try
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
//wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
wb = wbs.Open(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlPlatform.xlWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
mFilename = FileName;
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 获取一个工作表
/// </summary>
/// <param name="SheetName">工作表名称</param>
/// <returns>获取的工作表</returns>
public Excel.Worksheet GetSheet(string SheetName)
{
try
{
Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[SheetName];
return s;
}
catch
{
return null;
}
}
/// <summary>
/// 添加一个工作表
/// </summary>
/// <param name="SheetName">工作表名称</param>
/// <returns>新生成的工作表</returns>
public Excel.Worksheet AddSheet(string SheetName)
{
try
{
Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
s.Name = SheetName;
return s;
}
catch
{
return null;
}
}
/// <summary>
/// 删除一个工作表
/// </summary>
/// <param name="SheetName">工作表名称</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool DelSheet(string SheetName)
{
try
{
((Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 重命名一个工作表
/// </summary>
/// <param name="OldSheetName">原始工作表名称</param>
/// <param name="NewSheetName">新工作表名称</param>
/// <returns>重命名后的工作表对象</returns>
public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)
{
try
{
Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[OldSheetName];
s.Name = NewSheetName;
return s;
}
catch
{
return null;
}
}
/// <summary>
/// 重命名一个工作表
/// </summary>
/// <param name="Sheet">原始工作表对象</param>
/// <param name="NewSheetName">新工作表名称</param>
/// <returns>重命名后的工作表对象</returns>
public Excel.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName)
{
try
{
Sheet.Name = NewSheetName;
return Sheet;
}
catch
{
return null;
}
}
/// <summary>
/// 设定工作表的值
/// </summary>
/// <param name="ws">工作表对象</param>
/// <param name="x">X 列值</param>
/// <param name="y">Y 行值</param>
/// <param name="value">值</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool SetCellValue(Excel.Worksheet ws, int x, int y, object value)
{
try
{
ws.Cells[x, y] = value;
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 设定工作表的值
/// </summary>
/// <param name="ws">工作表名称</param>
/// <param name="x">X 列值</param>
/// <param name="y">Y 行值</param>
/// <param name="value">值</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool SetCellValue(string ws, int x, int y, object value)
{
try
{
GetSheet(ws).Cells[x, y] = value;
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 设定单元格属性
/// </summary>
/// <param name="ws">工作表对象</param>
/// <param name="Startx">开始X 列值</param>
/// <param name="Starty">开始Y 行值</param>
/// <param name="Endx">结束X 列值</param>
/// <param name="Endy">结束Y 行值</param>
/// <param name="size">字体大小</param>
/// <param name="name">字体名称</param>
/// <param name="color">字体颜色</param>
/// <param name="HorizontalAlignment">对齐方式</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool SetCellProperty(Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)
{
try
{
name = "宋体";
size = 12;
color = Excel.Constants.xlAutomatic;
HorizontalAlignment = Excel.Constants.xlRight;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 设定单元格属性
/// </summary>
/// <param name="ws">工作表名称</param>
/// <param name="Startx">开始X 列值</param>
/// <param name="Starty">开始Y 行值</param>
/// <param name="Endx">结束X 列值</param>
/// <param name="Endy">结束Y 行值</param>
/// <param name="size">字体大小</param>
/// <param name="name">字体名称</param>
/// <param name="color">字体颜色</param>
/// <param name="HorizontalAlignment">对齐方式</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)
{
try
{
//name = "宋体";
//size = 12;
//color = Excel.Constants.xlAutomatic;
//HorizontalAlignment = Excel.Constants.xlRight;
Excel.Worksheet ws = GetSheet(wsn);
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="ws">工作表对象</param>
/// <param name="x1">起始单元格X 列值</param>
/// <param name="y1">起始单元格Y 行值</param>
/// <param name="x2">结束单元格X 列值</param>
/// <param name="y2">结束单元格Y 行值</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool UniteCells(Excel.Worksheet ws, int x1, int y1, int x2, int y2)
{
try
{
ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="ws">工作表名称</param>
/// <param name="x1">起始单元格X 列值</param>
/// <param name="y1">起始单元格Y 行值</param>
/// <param name="x2">结束单元格X 列值</param>
/// <param name="y2">结束单元格Y 行值</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool UniteCells(string ws, int x1, int y1, int x2, int y2)
{
try
{
GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 将内存中数据表格插入到Excel指定工作表的指定位置
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="ws">工作表名称</param>
/// <param name="startX">起始的X 列值</param>
/// <param name="startY">起始的Y 行值</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)
{
try
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
}
}
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 将内存中数据表格插入到Excel指定工作表的指定位置
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="ws">工作表对象</param>
/// <param name="startX">起始的X 列值</param>
/// <param name="startY">起始的Y 行值</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool InsertTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)
{
try
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
ws.Cells[startX + i, j + startY] = dt.Rows[i][j];
}
}
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 将内存中数据表格添加到Excel指定工作表的指定位置
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="ws">工作表名称</param>
/// <param name="startX">起始的X 列值</param>
/// <param name="startY">起始的Y 行值</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool AddTable(System.Data.DataTable dt, string ws, int startX, int startY)
{
try
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j];
}
}
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 将内存中数据表格添加到Excel指定工作表的指定位置
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="ws">工作表对象</param>
/// <param name="startX">起始的X 列值</param>
/// <param name="startY">起始的Y 行值</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool AddTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)
{
try
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
ws.Cells[i + startX, j + startY] = dt.Rows[i][j];
}
}
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 插入图表操作
/// </summary>
/// <param name="ChartType">图表类型 Excel.XlChartType</param>
/// <param name="ws">工作表名称</param>
/// <param name="DataSourcesX1">起始的X 列值</param>
/// <param name="DataSourcesY1">起始的Y 行值</param>
/// <param name="DataSourcesX2">结束的X 列值</param>
/// <param name="DataSourcesY2">结束的Y 行值</param>
/// <param name="ChartDataType">数据类型 Excel.XlRowCol</param>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool InsertActiveChart(Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Excel.XlRowCol ChartDataType)
{
try
{
ChartDataType = Excel.XlRowCol.xlColumns;
wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
{
wb.ActiveChart.ChartType = ChartType;
wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType);
wb.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws);
}
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 保存文档
/// </summary>
/// <returns><c>true</c>操作成功<c>false</c>操作失败</returns>
public bool Save()
{
if (mFilename == "")
{
return false;
}
else
{
try
{
wb.Save();
return true;
}
catch
{
return false;
}
}
}
/// <summary>
/// 文档另存为
/// </summary>
/// <param name="FileName">文件路径名</param>
/// <returns><c>true</c>保存成功<c>false</c>保存失败</returns>
public bool SaveAs(object FileName)
{
try
{
wb.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
{
return false;
}
}
/// <summary>
/// 关闭一个Excel对象,销毁对象
/// </summary>
public void Close()
{
try
{
//wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
catch
{
;
}
}
}
}