microsoft.office.interop导出到excel [英] microsoft.office.interop export to excel

问题描述

我正在尝试使用Microsoft.Office.Interop.Excel从sql server数据库导出到excel;



但我收到错误microsoft。 office.interop.excel.application不包含'workbooks'的定义，也没有扩展方法'workbooks'



我也添加了所有必需的引用。请让我知道如何解决这个问题。



PS-也为SaveAs带来同样的错误。

Hi, I am trying to export to excel from sql server database using Microsoft.Office.Interop.Excel;

But I am getting an error "microsoft.office.interop.excel.application does not contain a definition for 'workbooks' and no extension method 'workbooks' "

I have added all the required references too. Please let me know how can I solve the issue.

P.S- also getting same error for SaveAs.

void ExportToExcel(string sqlquery, string filename)
        {
            Cursor.Current = Cursors.WaitCursor;
            SqlConnection cnn;
            string connectionString = null;
            string sql = null;
            string data = null;
            int i = 0;
            int j = 0;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            connectionString = "Data Source=HULMOSSPROD;Initial Catalog=" + cbx_Settings.Text + ";User Id=mosssa;Password=Unilever123;";
            cnn = new SqlConnection(connectionString);
            cnn.Open();
            sql = sqlquery;
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds);


            //==============================================================================================================
            string strLine;

            SqlCommand cmd = new SqlCommand(sqlquery, cnn);
            SqlDataReader dr;
            dr = cmd.ExecuteReader();

            //Initialize the string that is used to build the file.
            strLine = "";

            //Enumerate the field names and the records that are used to build 
            //the file.
            for (int m = 0; m < 1; m++)
            {
                for (int k = 0; k <= dr.FieldCount - 1; k++)
                {

                    strLine = dr.GetName(k).ToString();
                    //xlWorkSheet.Cells[m + 1, k + 1] = strLine;

                }
            }
            //==============================================================================================================


            for (i = 1; i <= ds.Tables[0].Rows.Count; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    data = ds.Tables[0].Rows[i - 1].ItemArray[j].ToString();
                    //xlWorkSheet.Cells[i + 1, j + 1] = data;
                }
            }



            xlWorkBook.SaveAs("C:\\80IB_Reports\\" + filename, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            cnn.Close();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            Cursor.Current = Cursors.Default;

            MessageBox.Show("Export Successful. You can find the file at C:\\80IB_Reports");

        }

推荐答案

为何互操作？这可能不是最好的方法。如何使用Microsoft Open XML SDK呢？请参阅此CodeProject文章：使用Open XML创建基本Excel工作簿 [ ^ ]。



另请参阅：

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2 [ ^ ]，

http://support.microsoft.com/kb/257757/en-us [ ^ ]，

Microsot office Interop [ ^ ]。

-SA

Why interop? This might not be the best way. How about using Microsoft Open XML SDK instead? Please see this CodeProject article: Creating basic Excel workbook with Open XML[^].

See also:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2[^],
http://support.microsoft.com/kb/257757/en-us[^],
Microsot office Interop[^].

—SA

这条线很奇怪

It is very strange as this line

xlApp = new Excel.ApplicationClass();

应该为您提供'没有为Microsoft.Office.Interop.Excel.ApplicationClass类型定义的构造函数。 '错误...

您将xlApp定义为Excel.Application但尝试将其创建为ApplicationClass ...将行更改为

should give you a 'There is no constructor defined for Microsoft.Office.Interop.Excel.ApplicationClass type.' error...
You are defined xlApp as Excel.Application but try to create it as ApplicationClass...change the line to

xlApp = new Excel.Application();

这篇关于microsoft.office.interop导出到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！