Error programming to export a Visual C#.Net Windows Forms Application dynamic dataset to MS Excel using C#

I have a VS C#.Net Windows Forms application in which I am trying to export a dynamic dataset to Excel.  I have the reference Microsoft.Office.Interop.Excel added and have "using Microsoft.Office.Interop.Excel;".  I found this code on the internet and am getting an error:  The type or namespace name 'ExcelFile' could not be found (are you missing a using directive or an assembly reference?).

I am using MS Office 2013.

Would someone please assist me with getting past this error?  Right now there is only 1 table in my dataset.

public void ExportRptToExcel(DataSet dsTngData)
 {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();                  
            var efRpt = new ExcelFile();    // Create new ExcelFile.              

            // Imports all the tables from DataSet to new file.

            foreach (System.Data.DataTable dt in dsTngData.Tables)
            {                
                var wsRpt = efRpt.Worksheets.Add(dt.TableName); 
                wsRpt.InsertDataTable(dt, "A1", true);
            }
            efRpt.SaveXls("dsTngData.xls");
  }

Open in new window

ptslvAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
ensure you have the right version of the interop referenced in your project.  

I believe you should be using version 15.  If you don't have them available, do a repair on your office and add the .net programmability support.

http://olappivottableextend.codeplex.com/wikipage?title=Office%202013%20Primary%20Interop%20Assemblies

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ptslvAuthor Commented:
Kyle,

Thanks for the quick response.  That is the reference I had added.  I found it under the COM references.  Is there another reference that I need?

ptslv
Kyle AbrahamsSenior .Net DeveloperCommented:
Remove that one, there should be one under .Net

References
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

ptslvAuthor Commented:
Kyle,

I am using Visual Studio 2012, Framework 4.5.  I do not have a tab for .NET when adding references.

ptslv
Kyle AbrahamsSenior .Net DeveloperCommented:
Are you right clicking on the solution or the project?  

Also from these steps it says you should be using the 4.0 framework . . . not sure if the 4.5 is compatible?

https://msdn.microsoft.com/en-us/library/vstudio/Dd264733(v=vs.110).aspx
ptslvAuthor Commented:
I changed the Framework from 4.5 to 4.0, saved and closed my project, re-opened my project.  I deleted the reference that I had and re-added it and the  Microsoft Excel 15.0 Object Library, and performed a Build.  I am still getting the same error message.

I have my solution open and am right-clicking on the References tab in the Solution Explorer.  I selected "Add Reference".  I still do not see a tab for .NET.
Kyle AbrahamsSenior .Net DeveloperCommented:
Do you have a 32 bit or 64 bit version of office?  Also what about your target cpu?  Not sure why you're not seeing the .Net references.  

If you right click on the project itself, and say add reference, do you get a different list?
ptslvAuthor Commented:
I am using 64 Bit.  

I do not have the option to Add References when I left-click or right-click on Project.  

I noticed that you are using Framework 4.0 Client Profile.  What is the difference between Framework 4.0 and Framework 4.0 Client Profile?
Kyle AbrahamsSenior .Net DeveloperCommented:
The client profile is a little lighter . . . but it shouldn't make a difference regarding that.  Was VS 2012 installed after word?  If so try doing a repair on the office installation.  The fact that you don't have a .net option at all is a little more disconcerting.

If you start with a blank windows forms solution, do you get that option?
ptslvAuthor Commented:
When I start a new Windows Forms project, the Add Reference option shows up in the Project tab.  But I still do not get the .NET tab under Add References.

The version VS that I have was installed after Office 2013 was installed.  I will try a repair on the VS and see what happens.  I also now have a NuGet warning since I rolled back to Framework 4.0 that I am trying to work through.
Kyle AbrahamsSenior .Net DeveloperCommented:
Also do a repair on the office side of things . . . the install may detect the new VS instance and put the references in there for you.  

Also check for any updates (service packs?) for visual studio 2012.
ptslvAuthor Commented:
Kyle,

I did the repair on the Visual Studio as you suggested.  It did fix some problems that were going on with my log files; however, I still do not have the .NET tab.  I checked with others that I work with, and no one has the view of tabs as you show in one of your comments above, so I am going with what I have.

I did manage to fix my reference problem.  I changed the Using statement to:  using Excel = Microsoft.Office.Interop.Excel;

That removed the reference error I was getting.  I also removed the line of code that was causing the error and re-wrote my function with the help of some code I found on the internet.  

 public void ExportRptToExcel()
        {
            int colIndex = 1,   int rowIndex = 1;
          
             Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheetRpt;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            Excel.Range ExelRange;

            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheetRpt = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            System.Data.DataTable dt = new DataTable();
            dt = dsTngData.Tables[0];
 
            // Imports all the tables from DataSet to new file.

            foreach (System.Data.DataRow dr in dt.Rows)
            {
                rowIndex = rowIndex + 1;
                colIndex = 0;
                foreach (DataColumn dc in dt.Columns)
                {
                    colIndex = colIndex + 1;
                    xlWorkSheetRpt.Cells[rowIndex + 1, colIndex] = dr[dc.ColumnName];
                    xlWorkSheetRpt.Rows.AutoFit();
                    xlWorkSheetRpt.Columns.AutoFit();
                }
            }

            xlWorkSheetRpt.get_Range("b2", "e2").Merge(false);

            ExelRange = xlWorkSheetRpt.get_Range("b2", "e2");
            ExelRange.FormulaR1C1 = "Report Header here ";        
            ExelRange.HorizontalAlignment = 3;
            ExelRange.VerticalAlignment = 3;

            xlApp.Visible = true;
            ObjectRelease(xlWorkSheetRpt);
            ObjectRelease(xlWorkBook);
            ObjectRelease(xlApp);
        }

Open in new window


I appreciate your time and assistance.  Thank you very much.
Kyle AbrahamsSenior .Net DeveloperCommented:
Glad you got it resolved.  Feel free to accept your own comment as the solution and mine as an assisted solution . . . applying points to the degree you feel I helped with the overall outcome.
ptslvAuthor Commented:
I gave the solution a B because, although performing a repair showed that I was having problems with my log files, it did not impact the library version that I had.  Somewhere there is a disconnect with the version of Visual Studio 2012 that we are both using.  Part of the solution is in my final comment, but I gave all the points to Kyle for sticking with me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.