Passing Data to Excel from C# + Visual Studio

Question

Hello I am a newbie to C#. What I am trying to do is write my C# data (e.g. integers & strings) into in Excel How do I do this? thanks J

j l · Answer

This article seemed to work for me http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/ef11a193-54f3-407b-9374-9f5770fd9fd7 J

j l · Answer

let me give you a better example of all I need to do...real straight forward Send to Excel = csResult.Customers[ 0 ].AccountNumber; Send to Excel = csResult.Customers[ 0 ].CustomerID; Send to Excel = csResult.Customers[ 0 ].FullName; Send to Excel = csResult.Customers[ 0 ].IsProspect; I want to put the AccountNumberCustomerIDFullName and IsProspect into cells within Excel....what is the C# code to put these values into Excel?

Raaj Kumar · Answer

Yes it is working in Test Project. I am attaching the solution along with the TestProject.

j l · Answer

Can you get this to run inside of a Test Project? I need to use the functions inside a Test Project (your console APP does work though) But I need the Test Project view to work for me. Do you know how to do this? J

Raaj Kumar · Answer

I am using Console application. I am attaching it.

j l · Answer

What type of project are you runnig it from? Im running it from a Test Project J

Raaj Kumar · Answer

did you checked in c:\. I have checked and it is working fine.

j l · Answer

Still no file c:\sample is generated ??? using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.Office.Interop.Excel; namespace ExportToExcel { public class Employee { public int ID { get ; set ; } public string FirstName { get ; set ; } public string LastName { get ; set ; } public string Address { get ; set ; } public string Sex { get ; set ; } } class Program { static void Main( string [] args) { List Employee > employeeList = new List Employee >(); employeeList.Add( new Employee () { ID = 1  FirstName = Test1  LastName = TestLast  Address = TestAddress1  Sex = Male }); employeeList.Add( new Employee () { ID = 2  FirstName = Test2  LastName = TestLast  Address = TestAddress2  Sex = FeMale }); employeeList.Add( new Employee () { ID = 3  FirstName = Test3  LastName = TestLast  Address = TestAddress3  Sex = Male }); employeeList.Add( new Employee () { ID = 4  FirstName = Test4  LastName = TestLast  Address = TestAddress4  Sex = FeMale }); employeeList.Add( new Employee () { ID = 5  FirstName = Test5  LastName = TestLast  Address = TestAddress5  Sex = Male }); Application xlApp = new Application (); Workbook wb = xlApp.Workbooks.Add( XlSheetType .xlWorksheet); Worksheet ws = ( Worksheet )xlApp.ActiveSheet; int row= 1 column= 1 ; foreach ( Employee employee in employeeList) { ws.Cells[row column] = employee.ID; ws.Cells[row column++] = employee.FirstName; ws.Cells[row column++] = employee.LastName; ws.Cells[row column++] = employee.Address; ws.Cells[row column++] = employee.Sex; column = 1 ; row++; } // xlApp.Visible = true; // xlApp.UserControl = true; //save the workbook wb.SaveAs( @c:\sample  XlFileFormat .xlWorkbookNormal Type .Missing Type .Missing Type .Missing Type .Missing XlSaveAsAccessMode .xlExclusive Type .Missing Type .Missing Type .Missing Type .Missing Type .Missing); wb.Close( true  Type .Missing Type .Missing); //close xlApp.Quit(); //exit } } }

Raaj Kumar · Answer

Do not specify the extension like .xlsx. Just simply give the filename like c:\sample. Because the extension is already specified ( XlFileFormat .xlWorkbookNormal). And Remove these 2 line xlApp.Visible = true ; xlApp.UserControl = true ;

j l · Answer

I still do not see an Excel file C:\sample\.xlsx that is created with the data in it?? I am running your code....should it be in a Unit Test Project? using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.Office.Interop.Excel; namespace ExportToExcel { public class Employee { public int ID { get ; set ; } public string FirstName { get ; set ; } public string LastName { get ; set ; } public string Address { get ; set ; } public string Sex { get ; set ; } } class Program { static void Main( string [] args) { List Employee > employeeList = new List Employee >(); employeeList.Add( new Employee () { ID = 1  FirstName = Test1  LastName = TestLast  Address = TestAddress1  Sex = Male }); employeeList.Add( new Employee () { ID = 2  FirstName = Test2  LastName = TestLast  Address = TestAddress2  Sex = FeMale }); employeeList.Add( new Employee () { ID = 3  FirstName = Test3  LastName = TestLast  Address = TestAddress3  Sex = Male }); employeeList.Add( new Employee () { ID = 4  FirstName = Test4  LastName = TestLast  Address = TestAddress4  Sex = FeMale }); employeeList.Add( new Employee () { ID = 5  FirstName = Test5  LastName = TestLast  Address = TestAddress5  Sex = Male }); Application xlApp = new Application (); Workbook wb = xlApp.Workbooks.Add( XlSheetType .xlWorksheet); Worksheet ws = ( Worksheet )xlApp.ActiveSheet; int row= 1 column= 1 ; foreach ( Employee employee in employeeList) { ws.Cells[row column] = employee.ID; ws.Cells[row column++] = employee.FirstName; ws.Cells[row column++] = employee.LastName; ws.Cells[row column++] = employee.Address; ws.Cells[row column++] = employee.Sex; column = 1 ; row++; } xlApp.Visible = true ; xlApp.UserControl = true ; //save the workbook wb.SaveAs( @c:\sample.xlsx  XlFileFormat .xlWorkbookNormal Type .Missing Type .Missing Type .Missing Type .Missing XlSaveAsAccessMode .xlExclusive Type .Missing Type .Missing Type .Missing Type .Missing Type .Missing); wb.Close( true  Type .Missing Type .Missing); //close xlApp.Quit(); //exit } } }

Raaj Kumar · Answer

Hi Yes you need to add the reference which I had mentioned previously. Since you are using a COM object you need to add the reference. Add this code at the end. //save the workbook wb.SaveAs(@c:\sample XlFileFormat.xlWorkbookNormal Type.Missing Type.Missing Type.Missing Type.Missing XlSaveAsAccessMode.xlExclusive Type.Missing Type.Missing Type.Missing Type.Missing Type.Missing); wb.Close(true Type.Missing Type.Missing);//close xlApp.Quit();//exit Regards Raaj

j l · Answer

ok thanks! I was able to compile your code...but I had to add references to Microsoft Excel Library and Microsoft.Csharp.dll. What path/directory is the Excel spreadsheet that is generated? Also I will need to call and save my data to an existing spreadsheet in the path C:\ART\Input\WSCatalogLookupSS.xlsx How does this integrate to the code you sent? thanks J

Raaj Kumar · Answer

Hi Here i am attaching the code. Please have a look at this. Let me explain the code. 1. Create a Employee Class and add few properties to that(IDFirstName....) 2. In Main method create a list of type employees and add few employee type objects. 3. Now create an instance of Workbook and worksheet. assign these list values to the worksheet. You can also try using arraylist. Hope this will help you. Regards Raaj

j l · Answer

Im new to C#....can you be more specific How does that integrate into your 1st post? J

Raaj Kumar · Answer

Hi Just create a class like this and try public class Employee { public int ID { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Address { get; set; } public string Sex { get; set; } } Regards Raaj

j l · Answer

Thanks for the response! I get some errors though. Error #1: Employee namespace can not be found How do I correct this error? thanks J

Lalit M · Answer

http://support.microsoft.com/kb/306023

Raaj Kumar · Answer

Here is the code for Exporting data to Excel. Add a reference to Microsoft.Office.Interop.Excel dll. using Microsoft.Office.Interop.Excel; List Employee > employeeList = new List Employee >(); employeeList.Add( new Employee () { ID = 1 FirstName = Test1  LastName = TestLast  Address = TestAddress1  Sex = Male }); employeeList.Add( new Employee () { ID = 2 FirstName = Test2  LastName = TestLast  Address = TestAddress2  Sex = FeMale }); employeeList.Add( new Employee () { ID = 3 FirstName = Test3  LastName = TestLast  Address = TestAddress3  Sex = Male }); employeeList.Add( new Employee () { ID = 4 FirstName = Test4  LastName = TestLast  Address = TestAddress4  Sex = FeMale }); employeeList.Add( new Employee () { ID = 5 FirstName = Test5  LastName = TestLast  Address = TestAddress5  Sex = Male }); Application xlApp = new Application (); Workbook wb = xlApp.Workbooks.Add( XlSheetType .xlWorksheet); Worksheet ws = ( Worksheet )xlApp.ActiveSheet; int row=1column=1; foreach ( Employee employee in employeeList) { ws.Cells[row column] = employee.ID; ws.Cells[row column++] = employee.FirstName; ws.Cells[row column++] = employee.LastName; ws.Cells[row column++] = employee.Address; ws.Cells[row column++] = employee.Sex; column = 1; row++; } xlApp.Visible = true ; xlApp.UserControl = true ;