Copy DataGridView to excel error: Namespace Office does not exist

VAer-4038 766 Reputation points
2021-01-03T23:28:30.647+00:00

I copied the code from one visual project to another project, and it shows a lot of errors in new project. The code works fine in original project, both projects run in same machine/same visual studio. I mean right now old project can compile in the same machine, but old project was built in a different machine last year, but that should have nothing to do with it. While there are a lot of errors for new project.

I tried to follow "potential fix" to fix some errors (I actually don't quite understand), there are still some errors left (without suggesting potential fix), now I cannot compile.

Here is the code, and I got the code from last year's post: exporting-datagridview-table-to-excel-file

        private void pbxExcelExport_Click(object sender, EventArgs e)  
        {  
            copyAlltoClipboard();  
            Microsoft.Office.Interop.Excel.Application xlexcel;  
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;  
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;  
            object misValue = System.Reflection.Missing.Value;  
            xlexcel = new Microsoft.Office.Interop.Excel.Application();  
            xlexcel.Visible = true;  
            xlWorkBook = xlexcel.Workbooks.Add(misValue);  
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);  
            Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];  
            CR.Select();  
            xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);  
  
            //Delete first blank column (row header column from DataGridView  
            ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Range["A1"]).EntireColumn.Delete(null);  
  
            dgvUsers.ClearSelection();  
  
  
  
        }  
  
  
  
        private void copyAlltoClipboard()  
        {  
            //Copy title row (Field name)  
            dgvUsers.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;  
            dgvUsers.SelectAll();  
            DataObject dataObj = dgvUsers.GetClipboardContent();  
            if (dataObj != null)  
                Clipboard.SetDataObject(dataObj);  
  
        }  
  

53003-dgv1.jpg

53061-dgv2.jpg

Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,898 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Ken Tucker 5,851 Reputation points
    2021-01-04T00:31:58.24+00:00

    Using Office Automation is not a good idea there is too much that can break when it is deployed. I would use the Microsoft Open XML sdk or NPOI to create the excel spreadsheet which allow you create office documents without office.

    Here is an example for using npoi

    Here is an example of for using OpenXMLsdk


  2. Daniel Zhang-MSFT 9,626 Reputation points
    2021-01-04T09:08:14.633+00:00

    Hi VAer-4038,
    Base on your description, you didn't add the DocumentFormat.OpenXml reference and WindowsBase.dll Nuget Package to your project.
    You can follow the steps to solve it:
    Right-click your project name->Add->Reference->search for "DocumentFormat.OpenXml" and choose it, then click OK.
    53275-144.png

    Next, right-click your project name->Manage Nuget Packages...->search for"WindowsBase" and then install it.
    53276-141.png
    Add the following code to your project:

    using DocumentFormat.OpenXml;  
    using DocumentFormat.OpenXml.Packaging;  
    using DocumentFormat.OpenXml.Spreadsheet;  
    using Workbook = DocumentFormat.OpenXml.Spreadsheet.Workbook;  
    using Worksheet = DocumentFormat.OpenXml.Spreadsheet.Worksheet;  
    using Sheets = DocumentFormat.OpenXml.Spreadsheet.Sheets;  
    

    Best Regards,
    Daniel Zhang


    If the response is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.