Solved

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

Posted on 2013-10-30
6
433 Views
Last Modified: 2013-11-19
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
Comment
Question by:spazjr01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39611812
you havent posted code where the problem arrises.

Hint: Its where you call outlook
0
 

Author Comment

by:spazjr01
ID: 39611908
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
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39612129
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:spazjr01
ID: 39612572
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
 

Accepted Solution

by:
spazjr01 earned 0 total points
ID: 39648985
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
 

Author Closing Comment

by:spazjr01
ID: 39658851
No one else provided a solution.   I discovered the solution on my own.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question