Asp.net操作Excel

using System;
using System.IO;
using System.Web;
using System.Text;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Runtime.InteropServices;
using System.Data;

/// <summary>
///ExportToExcel 的摘要说明
/// </summary>
public class ExportToExcel
{
    #region Interop
    [DllImport("User32.dll", CharSet = CharSet.Auto)]
    public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

    /// <summary>
    /// execl 导出
    /// </summary>
    /// <remarks>
    /// author:zhujt
    /// create date:2014-7-24 18:49:54
    /// </remarks>
    /// <param name="source">数据源</param>
    /// <param name="fileName">文件名称</param>
    /// <param name="sheetName">工作表名称</param>
    /// <param name="path">路径</param> 
    public static void ExportExcel(System.Data.DataTable source, string title, string fileName, string sheetName)
    { 
        // 创建Excel对象
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        if (excel != null)
        {
            // 获取缺少的object类型值
            object missing = System.Reflection.Missing.Value;
            // 不显示提示对话框
            excel.Application.DisplayAlerts = false;
            // 创建工作薄对象
            Microsoft.Office.Interop.Excel._Workbook wb = excel.Application.Workbooks.Add(1);
            // 删除多余工作表
            if (wb.Sheets.Count > 0) wb.Sheets.Delete();
            // 获取工作表
            Microsoft.Office.Interop.Excel._Worksheet sheet = (Microsoft.Office.Interop.Excel._Worksheet)excel.Worksheets.get_Item(1);
            // 工作表名称
            sheet.Name = sheetName;
            if (sheet != null)
            { 
                //sheet.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; 
                // 添加表头
                string[] titles = title.Split('#');
                // 列数
                int cols = titles.Length; 
                for (int i = 0; i < cols; i++)
                {
                    //设置标题格式 
                    //sheet.get_Range(sheet.Cells[1, i + 1], sheet.Cells[1, i + 1]).Font.Bold = true;
                    //sheet.get_Range(sheet.Cells[5 + i, 1], sheet.Cells[5 + i, 19]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); 
                    sheet.Cells[1, i + 1] = titles[i];
                    // 加粗
                    sheet.Cells[1, i + 1].Font.Bold = true;
                    // 字体大小
                    sheet.Cells[1, i + 1].Font.Size = 10;
                    // 列宽
                    sheet.Cells[1, i + 1].ColumnWidth = titles[i].Length * 2.5;
                    // 行高
                    sheet.Cells[1, i + 1].RowHeight = 30;
                    // 边框颜色
                    sheet.Cells[1, i + 1].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                    // 自动换行
                    sheet.Cells[1, i + 1].WrapText = true;
                    // 水平居右
                    sheet.Cells[1, i + 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                    // 垂直居中
                    sheet.Cells[1, i + 1].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                    // NET 4.0 
                    Microsoft.Office.Interop.Excel.Range range = sheet.Range[sheet.Cells[1, i + 1], sheet.Cells[2, i + 1]];
                    range.MergeCells = true;
                }

                int rows = source.Rows.Count;

                Microsoft.Office.Interop.Excel.Range sheetRange = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rows, cols + 1]];

                sheetRange.Borders.LineStyle = 1;
                sheetRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());

                for (int i = 2; i < rows + 2; i++)
                {
                    for (int j = 2; j < cols + 2; j++)
                    {
                        sheet.Cells[i, j - 1] = source.Rows[i - 2][j - 2].ToString();
                        try
                        {
                            string a = source.Rows[i - 2][j - 2].ToString();
                            string b = source.Rows[i - 1][j - 2].ToString();

                            if (a == b)
                            {
                                sheet.Range[sheet.Cells[i, j - 1], sheet.Cells[i - 1, j - 1]].MergeCells = true;
                                sheet.Range[sheet.Cells[i, j - 1], sheet.Cells[i - 1, j - 1]].Value = sheet.Cells[i, j - 1];
                            }
                        }
                        catch { }
                    }
                }
            }


            // 获取桌面路径
            string dir = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            string path = dir + "\\" + fileName + ".xls";
            if (File.Exists(path))
                File.Delete(path);

            wb.SaveAs(path, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
            // 清除Excel进程
            KillExcel(excel);
        }
        else
        {

        }
    }

    /// <summary>
    /// 清除Excel进程
    /// </summary>
    /// <remarks>
    /// author:zhujt
    /// create date:2014-7-24 19:30:57
    /// </remarks>
    /// <param name="excel">清除Excel进程</param>
    private static void KillExcel(Microsoft.Office.Interop.Excel.Application excel)
    {
        // 获取Excel窗口句柄 
        IntPtr hwnd = new IntPtr(excel.Hwnd);
        if (hwnd != IntPtr.Zero)
        {
            // Excel线程ID
            int threadID = 0;
            GetWindowThreadProcessId(hwnd, out threadID);
            // 获取Excel进程
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(threadID);
            // 清除
            p.Kill();
        }
    }
    #endregion
}

/// <summary>
/// 导出辅助类
/// </summary>
/// <remarks>
/// author:zhujt
/// create date:2014-7-24 19:37:32
/// </remarks>
public class UserExport
{
    private System.Data.DataTable _source;
    /// <summary>
    /// 数据源
    /// </summary>
    public System.Data.DataTable source
    {
        get { return _source; }
        set { _source = value; }
    }

    private string _title;
    /// <summary>
    /// 表头名称 以#分割
    /// </summary>
    public string title
    {
        get { return _title; }
        set { _title = value; }
    }

    private string _fileName;
    /// <summary>
    /// 文件名称
    /// </summary>
    public string fileName
    {
        get { return _fileName; }
        set { _fileName = value; }
    }

    private string _sheetName;
    /// <summary>
    /// 工作薄名称
    /// </summary>
    public string sheetName
    {
        get { return _sheetName; }
        set { _sheetName = value; }
    }

    /// <summary>
    /// 数据数据导出
    /// </summary>
    public void ExportExcel()
    {
        ExportToExcel.ExportExcel(this._source, this._title, this._fileName, this._sheetName);
    }
}

下载地址ExportExcel.rar

Asp.net操作Excel,古老的榕树,5-wow.com