C#.net对EXCEL的操作
来源:互联网 发布:linux多线程应用 编辑:程序博客网 时间:2024/06/05 19:27
/// <SUMMARY>
/// ExcelEdit 的摘要说明
/// </SUMMARY>
public class ExcelEdit
{
public string mFilename;
public Microsoft.Office.Interop.Excel.Application app;
public Microsoft.Office.Interop.Excel.Workbooks wbs;
public Microsoft.Office.Interop.Excel.Workbook wb;
public Microsoft.Office.Interop.Excel.Worksheets wss;
public Microsoft.Office.Interop.Excel.Worksheet ws;
public ExcelEdit()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public void Create()//创建一个Excel对象
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
public void Open(string FileName)//打开一个Excel文件
{
app = new Microsoft.Office.Interop.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 Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)
//获取一个工作表
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName];
return s;
}
public Microsoft.Office.Interop.Excel.Worksheet AddSheet(string SheetName)
//添加一个工作表
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
s.Name = SheetName;
return s;
}
public void DelSheet(string SheetName)//删除一个工作表
{
((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
}
public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.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;
}
public void SetCellValue(Microsoft.Office.Interop.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(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[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, 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[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(Microsoft.Office.Interop.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, Microsoft.Office.Interop.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, Microsoft.Office.Interop.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);
//后面的数字表示位置
}
}
/// ExcelEdit 的摘要说明
/// </SUMMARY>
public class ExcelEdit
{
public string mFilename;
public Microsoft.Office.Interop.Excel.Application app;
public Microsoft.Office.Interop.Excel.Workbooks wbs;
public Microsoft.Office.Interop.Excel.Workbook wb;
public Microsoft.Office.Interop.Excel.Worksheets wss;
public Microsoft.Office.Interop.Excel.Worksheet ws;
public ExcelEdit()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public void Create()//创建一个Excel对象
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
public void Open(string FileName)//打开一个Excel文件
{
app = new Microsoft.Office.Interop.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 Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)
//获取一个工作表
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName];
return s;
}
public Microsoft.Office.Interop.Excel.Worksheet AddSheet(string SheetName)
//添加一个工作表
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
s.Name = SheetName;
return s;
}
public void DelSheet(string SheetName)//删除一个工作表
{
((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
}
public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.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;
}
public void SetCellValue(Microsoft.Office.Interop.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(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[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, 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[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(Microsoft.Office.Interop.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, Microsoft.Office.Interop.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, Microsoft.Office.Interop.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);
//后面的数字表示位置
}
}
- C#.net对EXCEL的操作
- .net对excel的操作
- [C#]对Excel的操作
- C#.Net操作Excel
- C#.net实现windows窗体程序对Excel文件的读写操作
- C语言对Excel操作
- vb.net对excel的操作------感谢:就爱小毛虫
- C#.net 数据操作--excel 操作类
- vb.net 对excel表操作
- .net对Excel表数据读写操作
- Asp.net(C#)操作Excel的问题
- c#.net 下的excel操作(一)
- c#.net 下的excel操作(二)
- [NET][C#]操作Excel,套用模板并对数据进行分页
- .NET对Excel的读写
- .NET对Excel的读写
- pb 对excel的操作
- C#对Excel的操作
- 再谈U-Boot的设备管理框架
- 输入与输出 File
- C语言常用转义字符表
- .NET基础
- 筛素数<一>
- C#.net对EXCEL的操作
- Jsp页面中文参数传递get和post方法分析
- c/c++高质量编程(林锐)读书笔记二
- flex中AdvancedDataGrid之间的拖拽
- 使用SplitContainer来实现隐藏窗口的部分内容
- 第12章 事件 (四)
- 解决win7部署的网站其他用户不能访问
- HTML5-localstorage的介紹與JSON.parse、JSON.stringify方法的使用
- 照片墙一多实例演示