C#操作Excel

Excel操作类使用方法

        protected void Button1_Click(object sender, EventArgs e)
        {
            OperateExcel excel = new OperateExcel();
            //打开Excel
            excel.Open("d:\abc.xlsx");
            //获取工作表
            var  weet = excel.GetSheet("Sheet2");
            //写入Excel
            excel.SetCellValue(weet, 1, 2, "1011");
            ////另存为Excel
            //excel.SaveAs("d:\bcd.xlsx");
            ////注销Excel进程
            //excel.Close();
            excel.SaveAsHtml(HttpContext.Current.Server.MapPath("aa.html"));

            //杀掉Excel进程
            excel.KillSpecialExcel();
        }

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.Core;
using System.Runtime.InteropServices;
using System.IO;

///   <summary >       
///   Excel操作类       
///   </summary >       
public class OperateExcel
{
    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 OperateExcel()
    {
        //       
        //   TODO:   在此处添加构造函数逻辑       
        //       
    }

    /// <summary>
    /// 创建一个Excel对象
    /// </summary>
    public void Create()
    {
        app = new Microsoft.Office.Interop.Excel.Application();
        wbs = app.Workbooks;
        wb = wbs.Add(true);
    }

    /// <summary>
    /// 打开一个Excel文件
    /// </summary>
    /// <param name="FileName">Excel文件路径及名称</param>
    public void Open(string FileName)
    {
        object missing = System.Reflection.Missing.Value;
        app = new Microsoft.Office.Interop.Excel.Application();
        app.Visible = true;
        wbs = app.Workbooks;
        wb = wbs.Open(FileName, missing, false, missing, missing, missing,missing, missing, missing, true, missing, missing, missing, missing, missing);
        //wb = wbs.Add(FileName);
        mFilename = FileName;
    }

    /// <summary>
    /// 复制并打开模板文件
    /// </summary>
    /// <param name="Path">模板文件位置及文件名</param>
    /// <param name="sufix">复制后文件所加后缀</param>
    /// <param name="IsVisible">是否可见</param>
    /// <returns>复制后文件位置</returns>
    public string CopyAndOpenTemplate(string excelName)
    {
        try
        {
            string templetFilePath = HttpContext.Current.Server.MapPath("~\XlsTemplate\SampleZhongshuExcelTemplate.xlsx");
            string currentFolder = HttpContext.Current.Server.MapPath("~\UploadFile\")+DateTime.Now.ToString("yyyy-MM-dd");
            string toPath = currentFolder + "\" + excelName;
            //string tempFolderName = DateTime.Now.ToString("yyyy-MM-dd").Replace("-", "").Replace("/", "").Replace("\", "");
            //string targetFolder = HttpContext.Current.Server.MapPath("~\UploadFile\" + tempFolderName);
            //如果不存在则创建
            if (!Directory.Exists(currentFolder))
            {
                Directory.CreateDirectory(currentFolder);
            }

            File.Copy(templetFilePath, toPath, true);

            //File.SetAttributes(toPath, FileAttributes.Normal);
            Open(toPath);
            return toPath;
        }
        catch (Exception e)
        {
            //
            KillSpecialExcel();
            throw e;

        }
    }

    /// <summary>
    /// 获取一个工作表
    /// </summary>
    /// <param name="SheetName">工作表名称</param>
    /// <returns>Excel工作表</returns>
    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;
    }

    /// <summary>
    /// 添加一个工作表
    /// </summary>
    /// <param name="SheetName">工作表名称</param>
    /// <returns>Excel工作表</returns>
    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;
    }

    /// <summary>
    /// 删除一个工作表
    /// </summary>
    /// <param name="SheetName">工作表名称</param>
    public void DelSheet(string SheetName)
    {
        ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
    }

    /// <summary>
    /// 重命名一个工作表
    /// </summary>
    /// <param name="OldSheetName">要改名的工作表</param>
    /// <param name="NewSheetName">工作表新名称</param>
    /// <returns>工作表</returns>
    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;
    }

    /// <summary>
    /// 重命名一个工作表
    /// </summary>
    /// <param name="Sheet">Excel工作表实例</param>
    /// <param name="NewSheetName">新命名的工作表</param>
    /// <returns>Excel工作表</returns>
    public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName)
    {
        Sheet.Name = NewSheetName;
        return Sheet;
    }

    /// <summary>
    /// 设置工作表的值1
    /// </summary>
    /// <param name="ws">要设值的工作表</param>
    /// <param name="x">行</param>
    /// <param name="y">列</param>
    /// <param name="value">要设置的值</param>
    public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value)
    {
        ws.Cells[x, y] = value;
    }

    /// <summary>
    /// 设置工作表的值2
    /// </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(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;
    }

    /// <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, 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;       
        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;
    }




    /// <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(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);
    }

    /// <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>
    /// 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, Microsoft.Office.Interop.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(Microsoft.Office.Interop.Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Microsoft.Office.Interop.Excel.XlRowCol ChartDataType)//插入图表操作       
    {
        ChartDataType = Microsoft.Office.Interop.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(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAsObject, ws);
        }
    }

    /// <summary>
    /// 保存文档 
    /// </summary>
    /// <returns>是否保存成功</returns>
    public bool Save()
    {
        if (string.IsNullOrEmpty(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, Microsoft.Office.Interop.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, Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.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();
    }


    #region Kill Special Excel Process
    [DllImport("user32.dll", SetLastError = true)]
    static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);

    //推荐这个方法，找了很久，不容易啊 
    public void KillSpecialExcel()
    {
        try
        {
            if (app != null)
            {
                int lpdwProcessId;
                GetWindowThreadProcessId(new IntPtr(app.Hwnd), out lpdwProcessId);

                System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Delete Excel Process Error:" + ex.Message);
        }
    }
    #endregion

}

参考：http://blog.sina.com.cn/s/blog_4adf31ea0100ifi3.html

　　　http://blog.csdn.net/zhanglei5415/article/details/5396311

　　　http://www.cnblogs.com/wang_yb/articles/1750419.html

　　　http://www.open-open.com/code/view/1420029490093