using System; |
using System.Data; |
using System.Configuration; |
using Excel; |
//using Microsoft.Office.Interop; |
using Microsoft.Office.Core; |
|
|
namespace ExcelEdit |
{ |
/// <SUMMARY> |
/// ExcelEdit 的摘要说明 |
/// </SUMMARY> |
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() |
{ |
// |
// TODO: 在此处添加构造函数逻辑 |
// |
} |
public void Create() //创建一个Excel对象 |
{ |
app = new Excel.Application(); |
wbs = app.Workbooks; |
wb = wbs.Add( true ); |
} |
public void Open( string FileName) //打开一个Excel文件 |
{ |
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; |
} |
|
public void SetCellValue(Excel.Worksheet ws, int x, int y, object value) |
//ws:要设值的工作表 X行Y列 value 值 |
{ |
ws.Cells[x, y] = value; |
} |
public void SetCellValue( string ws, int x, int y, object value) |
//ws:要设值的工作表的名称 X行Y列 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); |
|
} |
|
|
// public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY) |
////将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一 |
// { |
|
// 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(); |
|
// } |
|
// } |
|
// } |
// public void InsertTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY) |
////将内存中数据表格插入到Excel指定工作表的指定位置二 |
// { |
|
// 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]; |
|
// } |
|
// } |
|
// } |
|
|
// public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY) |
////将内存中数据表格添加到Excel指定工作表的指定位置一 |
// { |
|
// 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]; |
|
// } |
|
// } |
|
// } |
// public void AddTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY) |
////将内存中数据表格添加到Excel指定工作表的指定位置二 |
// { |
|
|
// 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 ; |
|
} |
} |
public void Close() |
//关闭一个Excel对象,销毁对象 |
{ |
//wb.Save(); |
wb.Close(Type.Missing, Type.Missing, Type.Missing); |
wbs.Close(); |
app.Quit(); |
wb = null ; |
wbs = null ; |
app = null ; |
GC.Collect(); |
} |
} |
} |