Solved

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

Posted on 2013-10-30
6
440 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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

628 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