Solved

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

Posted on 2013-10-30
6
420 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
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Ess Kay
Comment Utility
you havent posted code where the problem arrises.

Hint: Its where you call outlook
0
 

Author Comment

by:spazjr01
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:spazjr01
Comment Utility
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
Comment Utility
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
Comment Utility
No one else provided a solution.   I discovered the solution on my own.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

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…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now