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 = http://www.mamicode.com/sheet.Cells[i, j - 1];>
下载地址ExportExcel.rar