Copy DataGridView to excel error: Namespace Office does not exist

Question

question

VAer-4038 asked Jan 3, '21 DanielZhang-MSFT edited Jan 4, '21

Copy DataGridView to excel error: Namespace Office does not exist

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);
    
         }

windows-forms

dgv1.jpg (137.3 KiB)

dgv2.jpg (111.9 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnnaXiu-MSFT · Jan 04, 2021 at 03:34 AM

Hi VAer-4038. as issues with tag “vs-general” focus on general questions of Visual Studio, your issue is more related to code development. So I changed the “vs-general” tag to “windows-forms” tag. Thanks for your understanding.

0 ·

Answer 1 · 2021-01-04T00:31:58.24Z

vb2ae answered Jan 4, '21 VAer-4038 commented Jan 4, '21

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VAer-4038 · Jan 04, 2021 at 02:33 AM

I have tried your code (both links), there are just a lot of errors. I am not an IT guy, and don't understand the error, so have no ideas how to fix.

I have tried some other codes posted online, it does not work too.

For my original code, I don't know why some code works in old project but not in new project.

Take xlexcel.Workbooks.Add for example, it shows the error "object does not contain a definition for Add ....... are you missing a using directive or assembly reference

I checked both using directive and reference, there is no difference between two forms.

I am so tired about this issue, have spent multiple hours on it so far.

Thanks anyway.

0 ·

datagrid.jpg (167.1 KiB)

VAer-4038 · Jan 04, 2021 at 02:43 AM

For somewhat, I just made original code work, not idea how/why.

Thanks.

0 ·

Answer 2 · 2021-01-04T09:08:14.633Z

DanielZhang-MSFT answered Jan 4, '21 DanielZhang-MSFT edited Jan 4, '21

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.

Next, right-click your project name->Manage Nuget Packages...->search for"WindowsBase" and then install it.

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.

144.png (12.2 KiB)

141.png (16.9 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question