C#导出excel表格

[a15101097329]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;


namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
// TODO: 这行代码将数据加载到表“personnel_managementDataSet.Jobs”中。您可以根据需要移动或移除它。
this.jobsTableAdapter.Fill(this.personnel_managementDataSet.Jobs);

}

private void button1_Click(object sender, EventArgs e)
{
DataGridviewShowToExcel(dataGridView1,true);
}
public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
{
if (dgv.Rows.Count == 0)
return false;
//建立Excel对象
Excel.Application excel = new Excel.Application();
//excel.Application.Workbooks.Add(true);
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcle;
//生成字段名称
for (int i = 0; i < dgv.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dgv.RowCount - 1; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
}
}
}
return true;
}

}
}
运行之后出现错误：
无法将类型为“Microsoft.Office.Interop.Excel.ApplicationClass”的 COM 对象强制转换为接口类型“Microsoft.Office.Interop.Excel._Application”。此操作失败的原因是对 IID 为“{000208D5-0000-0000-C000-000000000046}”的接口的 COM 组件调用 QueryInterface 因以下错误而失败: 加载类型库/DLL 时出错。 (异常来自 HRESULT:0x80029C4A (TYPE_E_CANTLOADLIBRARY))。

我就是把原来用的office重新装了一遍，其他的没动，这是怎么回事……代码没错啊

[Just4life]

重新卸载之后,再装一遍吧

[a15101097329]

大神帮忙啊

[a15101097329]

就没人会吗……

[liufeihardwork]

  /// <summary>
    /// 创建Excel 并插入数据
    /// </summary>
    public bool CreateExcelFile(string sheetname, string filepath)
    {
        string sqlstr = "Select Name From SysColumns Where id=Object_Id('" + sheetname + "') ";
        DataTable dt = QueryDS(sqlstr, ConnectionString).Tables[0];
        int rowscount = dt.Rows.Count;
        string OLEDBConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";";
        OLEDBConnStr += " Extended Properties=Excel 8.0;";

        if (!File.Exists(filepath))
        {
            string strCreateTableSQL = @" CREATE TABLE " + sheetname + "(";
            for (int k = 0; k < rowscount; k++)
            {
                if (k == rowscount - 1)
                {
                    strCreateTableSQL += @"" + dt.Rows[k][0].ToString() + " NText";
                }
                else
                {
                    strCreateTableSQL += @"" + dt.Rows[k][0].ToString() + " NText,";
                }

            }

            strCreateTableSQL += @" ) ";
            OleDbConnection oConn = new OleDbConnection();
            oConn.ConnectionString = OLEDBConnStr;
            oConn.Open();
            OleDbCommand oCreateComm = new OleDbCommand();
            OleDbTransaction str = oConn.BeginTransaction();
            oCreateComm.Connection = oConn;
            oCreateComm.Transaction = str;
            try
            {
                oCreateComm.CommandText = strCreateTableSQL;

                oCreateComm.ExecuteNonQuery();
                str.Commit();
                oConn.Close();
                DataTableToExcel(filepath, sheetname);
                return true;
            }
            catch (Exception)
            {
                str.Rollback();
                return false;
            }
        }
        else
        {
            Response.Write("文件已经存在！");
            return false;
        }
    }
 /// <summary>
    /// 两个DataTable的数据对拷
    /// </summary>
    /// <param name="srcTable">要导出的数据集合</param>
    /// <param name="destTable">新的数据集</param>
    private void CopyDataTable(DataTable srcTable, DataTable destTable)
    {
        foreach (DataRow row in srcTable.Rows)
        {
            destTable.Rows.Add(row.ItemArray);
        }
    }
    /// <summary>
    /// DataTable 导出到Excel 
    /// </summary>
    /// <param name="Path">Excel 路径</param>
    /// <param name="sheetName">Excel表名</param>
    private void DataTableToExcel(string Path, string sheetName)
    {
        DataTable dtNew = new DataTable();
        dtNew = ddst.Tables[0].Clone();
        CopyDataTable(ddst.Tables[0], dtNew);
        UpdateTable(sheetName, dtNew, Path);
    }

  /// <summary>
    /// 执行修改表数据
    /// </summary>
    /// <param name="SheetName"></param>
    /// <param name="ds">导出的数据</param>
    /// <param name="path">Excel 路径</param>
    /// <returns></returns>
    public static int UpdateTable(string SheetName, DataTable dt, string path)
    {
        int num = 0;
        string SQLString = "select * from [" + SheetName + "$]";
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties=Excel 8.0;";
        using (OleDbConnection connection = new OleDbConnection(strCon))
        {
            using (OleDbCommand cmd = new OleDbCommand())
            {
                connection.Open();
                using (OleDbTransaction str = connection.BeginTransaction())
                {
                    try
                    {
                        cmd.Connection = connection;
                        cmd.CommandText = SQLString;
                        cmd.Transaction = str;
                        OleDbDataAdapter command = new OleDbDataAdapter(cmd);
                        OleDbCommandBuilder scb = new OleDbCommandBuilder(command);
                        scb.QuotePrefix = "[";
                        scb.QuoteSuffix = "]";
                        num = command.Update(dt);
                        str.Commit();
                    }
                    catch (OleDbException)
                    {
                        str.Rollback();
                    }
                    return num;
                }
            }
        }
    }

[心灵彩虹]

应该是office 的dll文件造成的。

[心灵彩虹]

以前也遇到这个，把设计到excel的dll重新引用一下试试呢？

[AK47]

学习一下。

[pwxcomer]

其实这个主要就是服务器上没有安装office,或者安装了但是没有相关权限.

具体解决方法如下:
1:在服务器上安装office的Excel软件.
2:在"开始"->"运行"中输入dcomcnfg.exe启动"组件服务"
3:依次双击"组件服务"->"计算机"->"我的电脑"->"DCOM配置"
4:在"DCOM配置"中找到"Microsoft Excel 应用程序",在它上面点击右键,然后点击"属性",弹出"Microsoft Excel 应
用程序属性"对话框
5:点击"标识"标签,选择"交互式用户"
6:点击"安全"标签,在"启动和激活权限"上点击"自定义",然后点击对应的"编辑"按钮,在弹出的"安全性"对话框中填加
一个"NETWORK SERVICE"用户(注意要选择本计算机名),并给它赋予"本地启动"和"本地激活"权限.
7:依然是"安全"标签,在"访问权限"上点击"自定义",然后点击"编辑",在弹出的"安全性"对话框中也填加一个"NETWORK
SERVICE"用户,然后赋予"本地访问"权限.
这样,我们便配置好了相应的Excel的DCOM权限.
注意:这是在WIN2003上配置的,在2000,xp上,NETWORK SERVICE用户改为ASP.net用户