Excel操作类C#版 - 许明吉博客 - 博客园

来源： Excel操作类C#版 – 许明吉博客 – 博客园

//引入Excel的COM组件
using System;
using System.Data;
using System.Configuration;
using System.Web;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
namespace ExcelEdit
{
///

public class ExcelEdit
{
public string mFilename;
public Excel.Application app;
public Excel.Workbooks wbs;
public Excel.Workbook wb;
public Excel.Worksheets wss;
public Excel.Worksheet ws;
public ExcelEdit()
{
}
//创建一个Excel对象
public void Create()
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
//打开一个Excel文件
public void Open(string FileName)
{
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;
}
//获取一个工作表
public Excel.Worksheet GetSheet(string SheetName)
{
Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[SheetName];
return s;
}
//添加一个工作表
public Excel.Worksheet AddSheet(string SheetName)
{
Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
s.Name = SheetName;
return s;
}
//删除一个工作表
public void DelSheet(string SheetName)
{
((Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
}
//重命名一个工作表一
public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)
{
Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[OldSheetName];
s.Name = NewSheetName;
return s;
}
//重命名一个工作表二
public Excel.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName)
{
Sheet.Name = NewSheetName;
return Sheet;
}
//ws：要设值的工作表 X行Y列 value 值
public void SetCellValue(Excel.Worksheet ws, int x, int y, object value)
{
ws.Cells[x, y] = value;
}
//ws：要设值的工作表的名称 X行Y列 value 值
public void SetCellValue(string ws, int x, int y, object value)
{
GetSheet(ws).Cells[x, y] = value;
}
//设置一个单元格的属性 字体， 大小，颜色 ，对齐方式
public void SetCellProperty(Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)
{
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;
}
public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)
{
//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;
}
//合并单元格
public void UniteCells(Excel.Worksheet ws, int x1, int y1, int x2, int y2)
{
ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
}
//合并单元格
public void UniteCells(string ws, int x1, int y1, int x2, int y2)
{
GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).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 - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString(); } } } //将内存中数据表格插入到Excel指定工作表的指定位置二 public void InsertTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY) { 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]; } } } //将内存中数据表格添加到Excel指定工作表的指定位置一 public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY) { 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]; } } } //将内存中数据表格添加到Excel指定工作表的指定位置二 public void AddTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY) { 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]; } } } //插入图片操作一 public void InsertPictures(string Filename, string ws) { GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150); //后面的数字表示位置 } //插入图片操作二 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; } //插入图表操作 public void InsertActiveChart(Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Excel.XlRowCol ChartDataType) { 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); } } //保存文档 public bool Save() { if (mFilename == "") { return false; } else { try { wb.Save(); return true; } catch (Exception ex) { return false; } } } //文档另存为 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 (Exception ex) { return false; } } //关闭一个Excel对象，销毁对象 public void Close() { //wb.Save(); wb.Close(Type.Missing, Type.Missing, Type.Missing); wbs.Close(); app.Quit(); wb = null; wbs = null; app = null; GC.Collect(); } } } [/code]