C#中的excel操作类（集合了几个别人的类，另外自己编写了本人工作中常用到的功能函数）
www.diybl.com 时间 ： 2010-06-21 作者：佚名 编辑：壹枝雪糕 点击： [ 评论 ]
/*
public string mFilename;
public Excel.Application app;
public Excel.Workbooks wbs;
public Excel.Workbook wb;
public Excel.Worksheets wss;
public Excel.Worksheet ws;
/// 构造函数，不创建Excel工作薄
public ExcelEdit()
/// 创建Excel工作薄
public void Create()//创建一个Excel对象
/// 显示Excel
public void ShowExcel()
/// 打开一个存在的Excel文件
public void Open(string FileName)//打开一个Excel文件
public Excel.Worksheet GetSheet(string SheetName)
//获取一个工作表
public Excel.Worksheet AddSheet(string SheetName)
//添加一个工作表
public void DelSheet(string SheetName)//删除一个工作表
public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
public Excel.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二
//把EXCEl中的某工作表显示到datagridview中
public void Excel2DBView(string tablename, DataGridView dataGridView1)
//把EXCEl中的某工作表中字段值为FieldNameStr的行显示到datagridview中
public void Excel2DBView_SelectFieldValue(string tablename, string FieldName, string FieldNameStr, DataGridView dataGridView1)
//返回excel中不把第一行当做标题看待的数据集
public DataSet GetNewDataSet(string tablename)
//根据字段名，删除EXCEl中的某工作表中的记录(SQL语句未成功执行)
public void DeleteExcelFieldValue(string tablename, string FieldName, string FieldNameStr)
/// 在工作表中插入行，并调整其他行以留出空间
public void InsertRows(Excel.Worksheet sheet, int rowIndex)
/// 在工作表中删除行
public void DeleteRows(Excel.Worksheet sheet, int rowIndex)
/// 在工作表中删除所有行名为rowText的行
public void DeleteRows(string sheetName, string FieldName, string rowText)
/// 根据字段名， /// 根据字段名，追加记录
public void AddRecords(string sheetName, string FieldName, string recordText)
/// DataGridView导出到Excel
//获取字段中的字段名称集
public StringCollection GetFieldValues(string sheetName,string FieldName)
public void DGView2Excel(DataGridView dgv, string xlsFileName, string sheetName)
/// DataGridView追加到Excel指定表格中
public void DGViewAdd2Excel(DataGridView dgv, string xlsFileName, string sheetName)
public StringCollection countexcel(string _filename) //返回工作表名
public DataSet proces(string _filename) //用datset返回整个excel
public int rowcount(string _filename, string sheetname) //行数
public int rowcount(string sheetname) //行数
public int colcount(string _filename, string sheetname) //列数
public int colcount(string sheetname) //列数
public string GetCellStr(string sheetName, int row, int col) //返回指定单元格的文本
public void SetCellStr(string sheetName, int row, int col, string writeStr) //将字符串写入指定单元格
public void WriteData(string[,] data, string fileName, string sheetName, int startRow, int startColumn)
/// 将数据写入Excel
public void WriteData(string data, string fileName, string sheetName, int row, int column)
/// 读取指定单元格数据
public string ReadData(string fileName, string sheetName, int row, int column)
public void UniteCells(Excel.Worksheet ws, int x1, int y1, int x2, int y2)
//合并单元格
public void UniteCells(string ws, int x1, int y1, int x2, int y2)
//合并单元格
public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)
//将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
public void InsertTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)
//将内存中数据表格插入到Excel指定工作表的指定位置二
public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)
//将内存中数据表格添加到Excel指定工作表的指定位置一
public void AddTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)
//将内存中数据表格添加到Excel指定工作表的指定位置二
/// 保存Excel
public bool Save()
/// Excel文档另存为
public bool SaveAs(object FileName)
//去掉文件后缀
public void RemoveExefilter(string FileName)
public void Close()
//关闭一个Excel对象，销毁对象
public void release_xlsObj()
//////如何杀死word,excel等进程,下面的方法可以直接调用
public void KillProcess(string processName)
*/
//引入Excel的COM组件
using System;
//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;
//using Excel = Microsoft.Office.Interop.Excel;
//using Excel = Microsoft.Office.Interop.Excel;
using Excel;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Reflection; // 引用这个才能使用Missing字段
using System.Collections.Specialized;
using System.Runtime.InteropServices;
namespace ExcelEditClass
{
/// <SUMMARY>
/// ExcelEdit 的摘要说明
/// </SUMMARY>
public class ExcelEdit
//public class ExcelEdit:Excel.ApplicationClass //liuxfu
{ //to kill excel
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public string mFilename;
public Excel.Application app;
public Excel.Workbooks wbs;
public Excel.Workbook wb;
public Excel.Worksheets wss;
public Excel.Worksheet ws;
/// <summary>
/// 构造函数，不创建Excel工作薄
/// </summary>
public ExcelEdit()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 创建Excel工作薄
/// </summary>
public void Create()//创建一个Excel对象
{
try
{
app = new Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
}
/// <summary>
/// 显示Excel
/// </summary>
public void ShowExcel()
{
app.Visible = true;
}
/// <summary>
/// 打开一个存在的Excel文件
/// </summary>
/// <param name="FileName">Excel完整路径加文件名</param>
public void Open(string FileName)//打开一个Excel文件
{
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;
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
app.UserControl = true;//如果只想用程序控制该excel而不想让用户操作时候，可以设置为false
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
}
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);
try
{ s.Name = SheetName;
return s;
}
catch {
return null;
}
}
public void DelSheet(string SheetName)//删除一个工作表
{
try
{
((Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
}
public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
{
Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[OldSheetName];
try { s.Name = NewSheetName;
return s;
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString());
return null;
}
}
public Excel.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二
{
try { Sheet.Name = NewSheetName;
return Sheet;
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString());
return null;
}
}
//把EXCEl中的某工作表显示到datagridview中
/// <summary>
/// ExcelEdit myExcel = new ExcelEdit();
/// myExcel.Open("d:\\数据库表格20071217.xls");
/// myExcel.Excel2DBView("908", this.dataGridView1);
/// myExcel.Close();
/// </summary>
/// <param name="tablename"></param>
/// <param name="dataGridView1"></param>
public void Excel2DBView(string xlsFilaName,string tablename, DataGridView dataGridView1)
{
try
{
string sExcelFile = xlsFilaName;
//string strExcelFileName = @""+ myPath +"";
//string strString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1 '";
//string sConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + sExcelFile + ";Extended Properties=Excel 8.0;";
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
OleDbConnection connection = new OleDbConnection(sConnectionString);
string sql_select_commands = "Select * from [" + tablename + "$]";
OleDbDataAdapter adp = new OleDbDataAdapter(sql_select_commands, connection);
DataSet ds = new DataSet();
adp.Fill(ds, tablename);
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
//写入dataGridView控件标题
for (int j = 0; j < ds.Tables[tablename].Columns.Count; j++)
{
dataGridView1.Columns.Add(ds.Tables[tablename].Rows[0][j].ToString(), ds.Tables[tablename].Rows[0][j].ToString());
}
for (int i = 1; i < ds.Tables[tablename].Rows.Count; i++)
{
dataGridView1.Rows.Add();
}
//写入dataGridView控件行数据
for (int i = 1; i < ds.Tables[tablename].Rows.Count; i++)
for (int j = 0; j < ds.Tables[tablename].Columns.Count; j++)
{
dataGridView1.Rows[i - 1].Cells[j].Value = Convert.ToString(ds.Tables[tablename].Rows[i][j]);
}
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
/*
for (int i = 0; i < ds.Tables["Book1"].Rows.Count; i++)
{
sum1 += Convert.ToInt32(ds.Tables["Book1"].Rows[i]["字段A"]);
}
