excel 一些常用操作方法(转)
来源:互联网 发布:域名注册 知乎 编辑:程序博客网 时间:2024/05/18 00:47
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
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;
}
{
((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;
}
{
}
{
ws.Cells[x, y] = value;
}
public void SetCellValue(string ws, int x, int y, object value)//ws:要设值的工作表的名称 X行Y列 value 值
{
}
{
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;
}
{
//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;
}
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);
}
{
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 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 j = 0; j <= dt.Columns.Count - 1; j++)
{
public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置一
{
{
for (int j = 0; j <= dt.Columns.Count - 1; 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++)
{
}
{
GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse,MsoTriState.msoTrue, 10, 10, 150, 150);//后面的数字表示位置
}
//{
// 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.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;
//}
{
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;
}
{
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;
}
{
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();
}
}
}
转自:http://www.360doc.com/content/07/0730/12/12027_642751.shtml
- excel 一些常用操作方法(转)
- C#的一些常用文件操作方法[极好]
- C#的一些常用文件操作方法[极好]
- C#常用的一些简单的操作方法
- linux对于服务器的一些常用操作方法
- 一些常用Java操作方法封装:Java Utils
- Excel(1):常用的一些函数
- excel一些常用函数
- excel 一些常用设置
- Objective-C中关于NSDictionary的一些常用的操作方法
- 常用的文件操作方法(3)
- Array数组常用操作方法(二)
- Excel基本操作方法
- CString一些简单操作方法
- C#一些字符串操作方法
- word一些操作方法
- CString一些简单操作方法
- 图片的一些操作方法
- 自问自答 1 [技术类] [.net]窗体的size最小值是123*34吗?
- 开始学习C#
- 初学编程该怎么学?——对初学者程序设计语言学习的思考1
- 初学编程该怎么学?——对初学者程序设计语言学习的思考(2)
- 初学编程该怎么学?——对初学者程序设计语言学习的思考(2)
- excel 一些常用操作方法(转)
- gao
- 7段数码管显示器公用驱动程序
- Dataset用法实践之一概述
- 2天玩转单反相机引领快乐光影新生活
- Oracle select --分析函数over()
- 清除SQL被注入恶意病毒代码的语句
- 抽象工厂
- php strtotime函数的格式详细说明