ASP.NET Excel操作类

首先添加Com中的Excel插件。要在服务器安装Office。本例中是Office 2003

实现例子： 

namespace Demo { public partial class WebForm1 : System.Web.UI.Page { ExcelEdit.ExcelEdit excel = new ExcelEdit.ExcelEdit(); protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { //打开Excel excel.Open(HttpContext.Current.Server.MapPath("./ExcelTest.xlt")); //获取工作表 Excel.Worksheet weet = excel.GetSheet("Sheet1"); //写入Excel excel.SetCellValue(weet, 1, 2, "1011"); //另存为Excel excel.SaveAs(HttpContext.Current.Server.MapPath("./ExcelTest11.xls")); //注销Excel进程 excel.Close(); } } protected void Button1_Click(object sender, EventArgs e) {//Excel生成Html //打开Excel excel.Open(HttpContext.Current.Server.MapPath("./ExcelTest.xlt")); //另存为Excel excel.SaveAsHtml(HttpContext.Current.Server.MapPath("./aa.html")); //注销Excel进程 excel.Close(); //转向新生成的页面 Response.Redirect("./aa.html"); } } }

 

Excel操作类：

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; namespace ExcelEdit { /// <summary > /// Excel操作类 /// </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: 在此处添加构造函数逻辑 // } /// <summary> /// 创建一个Excel对象 /// </summary> public void Create() { app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(true); } /// <summary> /// 打开一个Excel文件 /// </summary> /// <param name="FileName">Excel文件路径及名称</param> public void Open(string FileName) { app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); mFilename = FileName; } /// <summary> /// 获取一个工作表 /// </summary> /// <param name="SheetName">工作表名称</param> /// <returns>Excel工作表</returns> public Excel.Worksheet GetSheet(string SheetName) { Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[SheetName]; return s; } /// <summary> /// 添加一个工作表 /// </summary> /// <param name="SheetName">工作表名称</param> /// <returns>Excel工作表</returns> 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; } /// <summary> /// 删除一个工作表 /// </summary> /// <param name="SheetName">工作表名称</param> public void DelSheet(string SheetName) { ((Excel.Worksheet)wb.Worksheets[SheetName]).Delete(); } /// <summary> /// 重命名一个工作表 /// </summary> /// <param name="OldSheetName">要改名的工作表</param> /// <param name="NewSheetName">工作表新名称</param> /// <returns>工作表</returns> public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName) { Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[OldSheetName]; s.Name = NewSheetName; return s; } /// <summary> /// 重命名一个工作表 /// </summary> /// <param name="Sheet">Excel工作表实例</param> /// <param name="NewSheetName">新命名的工作表</param> /// <returns>Excel工作表</returns> public Excel.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName) { Sheet.Name = NewSheetName; return Sheet; } /// <summary> /// 设置工作表的值 /// </summary> /// <param name="ws">要设值的工作表</param> /// <param name="x">行</param> /// <param name="y">列</param> /// <param name="value">要设置的值</param> public void SetCellValue(Excel.Worksheet ws, int x, int y, object value) { ws.Cells[x, y] = value; } /// <summary> /// 设置工作表的值 /// </summary> /// <param name="ws">工作表的名称</param> /// <param name="x">行</param> /// <param name="y">列</param> /// <param name="value">要设置的值</param> public void SetCellValue(string ws, int x, int y, object value) { GetSheet(ws).Cells[x, y] = value; } /// <summary> /// 设置工作表的值 /// </summary> /// <param name="ws">工作表</param> /// <param name="Startx">开始的行</param> /// <param name="Starty">开始的列</param> /// <param name="Endx">结束的行</param> /// <param name="Endy">结束的列</param> /// <param name="size">大小</param> /// <param name="name">字体名称</param> /// <param name="color">颜色</param> /// <param name="HorizontalAlignment">对齐方式</param> 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; } /// <summary> /// 设置工作表的值 /// </summary> /// <param name="ws">工作表的名称</param> /// <param name="Startx">开始的行</param> /// <param name="Starty">开始的列</param> /// <param name="Endx">结束的行</param> /// <param name="Endy">结束的列</param> /// <param name="size">大小</param> /// <param name="name">字体名称</param> /// <param name="color">颜色</param> /// <param name="HorizontalAlignment">对齐方式</param> 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; } /// <summary> /// 合并单元格 /// </summary> /// <param name="ws">工作表</param> /// <param name="x1">开始的行</param> /// <param name="y1">开始的列</param> /// <param name="x2">结束的行</param> /// <param name="y2">结束的列</param> 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); } /// <summary> /// 合并单元格 /// </summary> /// <param name="ws">工作表名称</param> /// <param name="x1">开始的行</param> /// <param name="y1">开始的列</param> /// <param name="x2">结束的行</param> /// <param name="y2">结束的列</param> 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); } /// <summary> /// 将表格插入到Excel的指定工作表指定位置 /// </summary> /// <param name="dt">DataTable</param> /// <param name="ws">工作表名称</param> /// <param name="startX">开始行</param> /// <param name="startY">开始列</param> public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY) { 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(); } } } /// <summary> /// 将表格插入到Excel的指定工作表指定位置 /// </summary> /// <param name="dt">DataTable</param> /// <param name="ws">工作表</param> /// <param name="startX">开始行</param> /// <param name="startY">开始列</param> public void InsertTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY) { 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]; } } } /// <summary> /// DataTable表格添加到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) { 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]; } } } /// <summary> /// DataTable表格添加到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, Excel.Worksheet ws, int startX, int startY) { 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]; } } } /// <summary> /// 将图片插入到工作表中 /// </summary> /// <param name="Filename">图片</param> /// <param name="ws">工作表</param> public void InsertPictures(string Filename, string ws) { GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);//后面的数字表示位置 } 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); } } /// <summary> /// 保存文档 /// </summary> /// <returns>是否保存成功</returns> public bool Save() { if (mFilename == " ") { return false; } else { try { wb.Save(); return true; } catch (Exception ex) { return false; } } } /// <summary> /// 文档的另存为 /// </summary> /// <param name="FileName">另存为名称</param> /// <returns>是否保存成功</returns> 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; } } /// <summary> /// 将文档另存为Html页 /// </summary> /// <param name="HtmlName">Html页面名称</param> /// <returns>是否保存成功</returns> public bool SaveAsHtml(object HtmlName)//文档另存为 { try { wb.SaveAs(HtmlName, Excel.XlFileFormat.xlHtml, 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; } } /// <summary> /// 关闭一个Excel对象，销毁对象 /// </summary> public void Close() { wb.Close(Type.Missing, Type.Missing, Type.Missing); wbs.Close(); app.Quit(); wb = null; wbs = null; app = null; GC.Collect(); } } }