Passing Data to Excel from C# + Visual Studio

j l

16y7.6k

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

18 Replies

Know the answer? Post it — somebody with the same question will find it here.

j l16y ago
This article seemed to work for me
http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/ef11a193-54f3-407b-9374-9f5770fd9fd7
J
j l16y ago
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 AccountNumber,CustomerID,FullName and IsProspect into cells within Excel....what is the C# code to put these values into Excel?
Raaj Kumar16y ago
Yes it is working in Test Project. I am attaching the solution along with the TestProject.
j l16y ago
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 Kumar16y ago
I am using Console application. I am attaching it.
j l16y ago
What type of project are you runnig it from? Im running it from a Test Project
J
Raaj Kumar16y ago
did you checked in c:\. I have checked and it is working fine.
j l16y ago
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 Kumar16y ago
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 l16y ago
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 Kumar16y ago
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 l16y ago
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 Kumar16y ago
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(ID,FirstName....)
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 l16y ago
Im new to C#....can you be more specific
How does that integrate into your 1st post?
J
Raaj Kumar16y ago
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 l16y ago
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 M16y ago
http://support.microsoft.com/kb/306023
Raaj Kumar16y ago
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=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;