Solved

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

Posted on 2013-10-30
6
432 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
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

840 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