学步园

using Microsoft.Office.Interop.Excel;
针对office 2003需添加引用Microsoft   Excel   11.0   Object   Library

如添加之后还不能用再using microsoft.office.core和excel

引用的dll为

Interop.Microsoft.Office.Core.dll

Interop.Excel.dll

Excel导出类:

using System;
using System.IO;
using System.Data;
using Microsoft.Office.Interop.Excel;

    ///   <summary>  
    ///   导出Excal文件 
    ///   </summary>  
public class ImportExcel
{
    private string title;
    private string rpt_name;
    private string outFilePath;
    private System.Data.DataTable dt;
    private System.Data.DataTable outTable;

    public ImportExcel()
    {

    }

    ///   <summary>  
    ///   设置Excel标题  
    ///   </summary>  
    public string rptName
    {
        get
        {
            return title;
        }
        set
        {
            title = value;
        }
    }

    ///   <summary>  
    ///   需要导入EXCEL的表  
    ///   </summary>  
    public System.Data.DataTable RptData
    {
        get
        {
            return dt;
        }
        set
        {
            dt = value;
        }
    }

    ///   <summary>  
    ///   设置输出文件的存放位置  
    ///   </summary>  
    public string OutFilePath
    {
        get
        {
            return outFilePath;
        }
        set
        {
            outFilePath = value;
        }
    }

    ///   <summary>  
    ///   构造函数,使用该类需传入Excel标题,DataTable,文件的存放路径  
    ///   </summary>  
    ///   <param   name="rptName">要设置的Excel标题</param>  
    ///   <param   name="dtable">要填充Excel的表</param>  
    ///   <param   name="FilePath">要存放的路径</param>  
    public ImportExcel(string rptName, System.Data.DataTable dtable, string file_path)
    {
        rpt_name = rptName;
        outTable = dtable;
        outFilePath = file_path;

        try
        {
            this.InsertExcel();
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
    }

    ///   <summary>  
    ///   该函数向Excel表中插入记录,要插入的数据为一个内存表.其中内存表中的列名为表中的列名  
    ///   该函数所使用的动态连接库Excel.dll,Office.dll,VBIDE.dll.  
    ///   </summary>  
    public void InsertExcel()
    {
        int col_count = 0;
        int row_count = 0;
        int k = 0;
        int l = 0;

        //检查文件是否存在  
        if (File.Exists(outFilePath))
        {
            throw new Exception("文件已存在，创建失败！");
        }

        Microsoft.Office.Interop.Excel.ApplicationClass rptApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

        rptApp.Application.Workbooks.Add(true);

        rptApp.Visible = false;

        object missing = System.Reflection.Missing.Value;

        Microsoft.Office.Interop.Excel.Workbook rptBook = rptApp.Workbooks[1];
        Microsoft.Office.Interop.Excel.Worksheet rptSheet = (Microsoft.Office.Interop.Excel.Worksheet)rptBook.ActiveSheet;


        //设置报表的标题  
        rptSheet.Cells[1, 1] = rpt_name;

        //插入列标题  
        foreach (DataColumn col in outTable.Columns)
        {
            col_count++;
            rptSheet.Cells[2, col_count] = col.ColumnName.ToString();
        }
        //设置列标题格式
        rptSheet.get_Range(rptSheet.Cells[2, 1],rptSheet.Cells[2, col_count]).Font.Bold = true; // 是否加粗：是

        //设置单元格的格式  
        foreach (DataRow dr in outTable.Rows)
        {
            row_count++;
            col_count = 0;
            foreach (DataColumn col in outTable.Columns)
            {
                col_count++;
                if (col.DataType == System.Type.GetType("System.String"))
                {
                    rptSheet.get_Range(rptSheet.Cells[1, 1], rptSheet.Cells[row_count + 2, col_count]).NumberFormatLocal = "@";
                }
                else if (col.DataType == System.Type.GetType("System.DateTime"))
                {
                    rptSheet.get_Range(rptSheet.Cells[1, 1], rptSheet.Cells[row_count + 2, col_count]).NumberFormatLocal = "yyyy-m-d";
                }
            }
        }

        //向报表插入记录  
        for (int i = 0; i < outTable.Rows.Count; i++)
        {
            for (int j = 0; j < outTable.Columns.Count; j++)
            {
                k = i + 3;
                l = j + 1;
                rptSheet.Cells[k, l] = outTable.Rows[i][j].ToString();
                rptSheet.get_Range(rptSheet.Cells[1, l], rptSheet.Cells[1, l]).Borders.LineStyle = 7;
                rptSheet.get_Range(rptSheet.Cells[2, l], rptSheet.Cells[2, l]).Borders.LineStyle = 1;
                rptSheet.get_Range(rptSheet.Cells[k, l], rptSheet.Cells[k, l]).Borders.LineStyle = 1;
            }
        }

        //设置标题的格式  
        rptSheet.get_Range(rptSheet.Cells[1, 1], rptSheet.Cells[1, 1]).Font.Bold = true;
        rptSheet.get_Range(rptSheet.Cells[1, 1], rptSheet.Cells[1, 1]).Font.Size = 22;

        //设置报表的标题为跨列居中合并单元格  
        rptSheet.get_Range(rptSheet.Cells[1, 1], rptSheet.Cells[1, col_count]).Select();
        rptSheet.get_Range(rptSheet.Cells[1, 1], rptSheet.Cells[1, col_count]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenterAcrossSelection;

        //设置报表表格为最适应宽度  
        rptSheet.get_Range(rptSheet.Cells[1, 1], rptSheet.Cells[row_count, col_count]).Select();
        rptSheet.get_Range(rptSheet.Cells[1, 1], rptSheet.Cells[row_count, col_count]).Columns.AutoFit();

        //另存文件到指定路径下  
        rptBook.SaveAs(outFilePath, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
        //关闭文件  
        rptBook.Close(false, outFilePath, true);
        rptApp.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(rptSheet);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(rptBook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(rptApp);
        //强制释放无用资源  
        GC.Collect();
    }
}

 

错误:检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046} 的组件时失败，原因是出现以下错误: 80070005。

解决方法一:
控制面板－》管理工具－》组件服务－》计算机－》我的电脑－》DCom配置－》找到Microsoft Excel 应用程序
之后
单击属性打开此应用程序的属性对话框。  
2. 单击标识选项卡，然后选择交互式用户。
3.单击"安全"选项卡,分别在"启动和激活权限"和"访问权限"组中选中"自定义",然后
自定义->编辑->添加ASP.NET账户和IUSER_计算机名: 需要本地激活,和本地访问两个权限

4."标识"选项卡 选择交互式用户