引用
using System;
using System.Data;
using System.Xml;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
namespace TextBook.basic.Component
{
/// <summary>
/// wwExcel 的摘要说明。
/// </summary>
public class wwExcel
{
private Microsoft.Office.Interop.Excel.Application app;
private string mFilename;
private Microsoft.Office.Interop.Excel.Workbook wb;
private Microsoft.Office.Interop.Excel.Workbooks wbs;
///<summary>
///打开Excel文件对象
///</summary>
///<param name="FileName">文件名,包含磁盘路径</param>
public void Open(string FileName)
{
app = new Microsoft.Office.Interop.Excel.ApplicationClass();
wbs = this.app.Workbooks;
wb = this.wbs.Open(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
mFilename = FileName;
}
///<summary>
///关闭Excel操作对象
///</summary>
public void Close()
{
wb.Saved = true;
wb.Close(Missing.Value, Missing.Value, Missing.Value);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
///<summary>
///新建一个Excel操作对象
///</summary>
public void Creat()
{
app = new ApplicationClass();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
///<summary>
///保存Excel文件对象
///</summary>
///<returns></returns>
public bool Save()
{
bool flag1;
try
{
wb.Save();
flag1 = true;
}
catch
{
flag1 = false;
}
return flag1;
}
///<summary>
///Excel文件对象另村为
///</summary>
///<param name="FileName"></param>
///<returns></returns>
public bool SaveAs(object FileName)
{
bool flag1;
try
{
this.wb.SaveAs(FileName,Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
flag1 = true;
}
catch
{
flag1 = false;
}
return flag1;
}
///<summary>
///重命名Sheet
///</summary>
///<param name="Sheet">Worksheet对象</param>
///<param name="NewSheetName">新名字</param>
///<returns>Worksheet对象</returns>
public Worksheet ReNameSheet(Worksheet Sheet, string NewSheetName)
{
Sheet.Name = NewSheetName;
return Sheet;
}
///<summary>
///重命名Shee
///</summary>
///<param name="OldSheetName">旧名字</param>
///<param name="NewSheetName">新名字</param>
///<returns>Worksheet对象</returns>
public Worksheet ReNameSheet(string OldSheetName, string NewSheetName)
{
Worksheet worksheet2 = (Worksheet)this.wb.Worksheets[OldSheetName];
worksheet2.Name = NewSheetName;
return worksheet2;
}
///<summary>
///新增一张WorkSheet
///</summary>
///<param name="SheetName">名字</param>
///<returns>WorkSheet对象</returns>
public Worksheet AddSheet(string SheetName)
{
Worksheet worksheet2 = (Worksheet)this.wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
worksheet2.Name = SheetName;
return worksheet2;
}
///<summary>
///删除一个Worksheets
///</summary>
///<param name="SheetName">Worksheets的名字</param>
public void DelSheet(string SheetName)
{
((Worksheet)wb.Worksheets[SheetName]).Delete();
}
///<summary>
///获取一个Worksheet对象
///</summary>
///<param name="SheetName">Worksheet的名字</param>
///<returns>Worksheet对象</returns>
public Worksheet GetSheet(string SheetName)
{
return (Worksheet)this.wb.Worksheets[SheetName];
}
///<summary>
///向Excel中加入一个数据表对象
///</summary>
///<param name="dt">DataTable对象</param>
///<param name="ws">工作表对象</param>
///<param name="startX">起始的横坐标</param>
///<param name="startY">起始的纵坐标</param>
public void AddTable(System.Data.DataTable dt, Worksheet ws, int startX, int startY)
{
if (dt == null)
{
return;
}
int numRowCount = dt.Rows.Count - 1;
for (int numRow = 0; numRow <= numRowCount; numRow++)
{
int numColCount = dt.Columns.Count - 1;
for (int numCol = 0; numCol <= numColCount; numCol++)
{
ws.Cells[numRow + startX, numCol + startY] = dt.Rows[numRow][numCol].ToString();
}
}
}
///<summary>
///向Excel中加入一个数据表对象
///</summary>
///<param name="dt">DataTable对象</param>
///<param name="ws">工作表的名字</param>
///<param name="startX">起始的横坐标</param>
///<param name="startY">起始的纵坐标</param>
public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)
{
if (dt == null)
{
return;
}
int numRowCount = dt.Rows.Count - 1;
for (int numRow = 0; numRow <= numRowCount; numRow++)
{
int numColCount = dt.Columns.Count - 1;
for (int numCol = 0; numCol <= numColCount; numCol++)
{
this.GetSheet(ws).Cells[numRow + startX, numCol + startY] =dt.Rows[numRow][numCol].ToString();
}
}
}
///<summary>
///设置单元格的值
///</summary>
///<param name="ws">Worksheet对象</param>
///<param name="x">单元格横坐标</param>
///<param name="y">单元格纵坐标</param>
///<param name="value">输入的值</param>
public void SetCellValue(Worksheet ws, int x, int y, object values)
{
ws.Cells[x, y] = values.ToString();
}
///<summary>
///设置单元格的值
///</summary>
///<param name="ws">Worksheet的名字</param>
///<param name="x">单元格横坐标</param>
///<param name="y">单元格纵坐标</param>
///<param name="value">输入的值</param>
public void SetCellValue(string ws, int x, int y, object values)
{
this.GetSheet(ws).Cells[x, y] = values.ToString();
}
}
上面基本上包含了对excel的操作，下面演示一个简单的调用方法
-----------------------------------------------------
首先还是添加引用,下面是我的引用地址
using TextBook.basic.Component;
然后添加方法
private void btnOut_Click(object sender, System.EventArgs e)
{
string date = System.DateTime.Now.Date.ToShortDateString();
wwExcel exl = new wwExcel();
exl.Creat();
//得到当前日期,以当前日期做EXCEL工作簿名称
exl.AddSheet(date);
//增加EXCEL第一行列标题
for (int colcount =0 ; colcount < dgdEnterpriseInfo.Columns.Count;colcount++)
{
exl.SetCellValue(date,1,colcount +1,(dgdEnterpriseInfo.Columns[colcount].HeaderText.Trim().ToString()));
}
//增加EXCEL数据内容,从第2行开始，从t_BatchOrderCustomer表中读数据
exl.AddTable(t_BatchOrderCustomer,date,2,1);
//设置文件保存路径和文件名
string path = this.MapPath("./");
string filename = "资料导出.xls";
//检查文件是否存在，如果存在则将原有数据删除
if(System.IO.File.Exists(path + filename)==true)
{
System.IO.File.Delete(path + filename);
}
exl.SaveAs(path + filename);
//一定要Close,否则会将系统资源耗尽
exl.Close();
Response.Redirect("./"+ filename);
}
