• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

How do I properly display datagridview contents in Excel using C#?

I have the below code and it works (sort of).  While Excel opens just fine and displays the contents of the datagridview object, there are immediately problems with other apps, such as Word or Outlook.  If I try to open one of these other apps after clicking the button to execute the below code (which displays the datagridview object in Excel), I get an error and this error is posted to the eventlog:  

Faulting application name: OUTLOOK.EXE, version: 14.0.7105.5000, time stamp: 0x51e84e55
Faulting module name: ole32.dll, version: 6.1.7601.17514, time stamp: 0x4ce7b96f
Exception code: 0xc0000005
Fault offset: 0x000ae0b4
Faulting process id: 0x1b4c
Faulting application start time: 0x01ced579c682afd8
Faulting application path: C:\Program Files (x86)\Microsoft Office\Office14\OUTLOOK.EXE
Faulting module path: C:\Windows\syswow64\ole32.dll
Report Id: f72edffb-416e-11e3-a394-a0b3cc248271

Here is my code.  Can someone tell me what I am doing wrong?

private void exportToExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            myDataGridView.SelectAll();
            DataObject dataObj = myDataGridView.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);
            Excel.Application objApp;
            Excel.Workbook objBook;
            Excel.Worksheet objSheet;
            Excel.Range objRange;            object misValue = System.Reflection.Missing.Value;
           
            objApp = new Excel.Application();
            objApp.Visible = true;
            objApp.WindowState = Excel.XlWindowState.xlMaximized;
            objBook = objApp.Workbooks.Add(misValue);
            objSheet = (Excel.Worksheet)objBook.Worksheets.get_Item(1);
           
            //Create title and column headers
            objSheet.Cells[1, 5] = "Title";
            objSheet.Cells[2, 2] = "1st Column";
            objSheet.Cells[2, 3] = "2nd Column";
           
            // paste datagridview
            objRange = (Excel.Range)objSheet.Cells[3, 1];
            objRange.Select();
            objSheet.PasteSpecial(objRange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
            releaseObject(objRange);
            releaseObject(dataObj);

            //adjust worksheet
            objSheet.Columns.AutoFit();
            objRange = objSheet.get_Range("A:A");
            objRange.Delete();
            releaseObject(objRange);

            objRange = objSheet.get_Range("A:M");
            objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            releaseObject(objRange);

            objRange = objSheet.get_Range("1:2");
            objRange.Font.Bold = true;
            releaseObject(objRange);
           
            Clipboard.Clear();
        }

 private void releaseObject(object obj)
        {
            try
            {
                Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;

            }
            finally
            {
                GC.Collect();
            }
        }
0
spazjr01
Asked:
spazjr01
  • 4
  • 2
1 Solution
 
Ess KayEntrapenuerCommented:
you havent posted code where the problem arrises.

Hint: Its where you call outlook
0
 
spazjr01Author Commented:
I don't have code for Outlook or Word or any of the other apps that have a problem.  This is what I'm saying:  After the above code is executed....that is, after the objects are created and the Excel app opens,...other apps (Outlook, Word, etc.), which normally work with no problems, start having errors like the one listed above.  So, this code (above) is causing other apps to have errors.  So, I'm guessing that I'm not managing these objects correctly.  But, I don't know what I'm doing wrong.
0
 
Ess KayEntrapenuerCommented:
when you open the object, it creates a process of office with application excel.
I'm assuming that you need to unload the process before opening the other applications.


try this:



after clipboard.clear();
add
objApp = nothing();


and make a call to      releaseObject(objApp);

I think though, the better way to do this is to re-write your method altogether, create a book and save it, then open the saved book, instead of linking it though the program.

<link removed - GaryC123>
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
spazjr01Author Commented:
I tried your suggestions.  None of them solved the problem.  It doesn't matter whether I create the file, save it, reopen it or not.  Adding the your cleanup suggestions didn't solve the problem either.  So, I started thinking that these things are not the cause.  So, I started taking things out to see what breaks this.  Here is what I found.  When I remove the actions which copy and past the datagridview to the sheet, the problem goes away.  So, something is wrong with my implementation of copying and pasting the datagridview into the excel spreadsheet.  While it works, it causes this problem.  Is there anything wrong with these statements?  Should I do something else to clear up the objects in the below code.

myDataGridView.SelectAll();
            DataObject dataObj = myDataGridView.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);
.
.
.

 // paste datagridview
            objRange = (Excel.Range)objSheet.Cells[3, 1];
            objRange.Select();
            objSheet.PasteSpecial(objRange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
            releaseObject(objRange);
            releaseObject(dataObj);
.
.
Clipboard.Clear();
0
 
spazjr01Author Commented:
After some research, I discovered that the problem was being caused by the Clipboard.Clear() command.  After I removed this command, all of my problems disappeared.  Since I didn't care of the clipboard was cleared or not, this solved my problem.
0
 
spazjr01Author Commented:
No one else provided a solution.   I discovered the solution on my own.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now