Exporting your data to excel in a much much easier way

When you're gonna interact between your data within the database and MS Excel, you noticed that it's kind of frustrating to do so. Because exporting the data to Excel is much difficult if you are fond of doing projects that involves MS Excel. Well, I'm glad to announce you there's a simple way to do so.

I used to have that kind of frustration until I've found a way to export your data to MS Excel in a much easier, programmer-friendly way. I researched and used some of the solutions I've come across the net just only to export my simple data in Excel in just a few cells but none of it seemed to work on me since it's just way too ridiculous and waaaaaaaay too longer codes. I mean, they worked but the coding seemed inefficient for me I can hardly follow in their algorithm of just interacting and writing in excel.

But first, let me list the requirements before doing this exercise:

• Visual Studio IDE(just Express would do)
• Common knowledge in C#.net
• Familiarization with COM objects(not required but might help you understand some concepts in communicating with Excel)

So some of you already know that in order to accomplish the task, we're gonna need to communicate with MS Excel through the use of COM. Don't worry for those who are not used in working with the mentioned technology since .net framework will do the dirty work for you by referencing Microsoft.Office.Interop.Excel. So you can pick this activity quite easier since I've made it easier for you to understand.

Now, let's get started.

Step 1: Create a console application. Name it testExcel

As usual we're gonna make use of console application for simulating the exporting of data to excel to focus more on coding.

**note: make sure you are using .net framework 4.0 for this activity or this will not work.

Step 2: On your Reference pane in Solution Explorer, add the reference Microsoft.Office.Interop.Excel.

Step 3: Make use of Application, Workbook, and Worksheet class

var app = new Application { Visible = true };
Workbook workbook = app.Workbooks.Add();
Worksheet worksheet = app.ActiveSheet;

As you have noticed, we utilized 3 classes. Whoa that's pretty lot of codes to use now isn't it? Unfortunately we're gonna have to use these mentioned classes if we wanted to export our data through Excel application.

• Application class represents an entire Excel application. For every instance of it comes with a new Excel application.
• Workbook class represents a workbook within Excel application.
• Lastly, Sheet class represents a sheet within Excel application. The ones you can see below? Like, sheet1, sheet2, sheet3 and so on when creating an Excel file. Also, every sheet serves as your workplace when working on an Excel file. You write something, compute something, so on and so forth.

Step 4: Create a class. Name it person

class person
    {
        public string fname { get; set; }
        public string lname{ get; set; }
    }

Your code should look like this. Although this isn't really necessary but since we're simulating the exporting of data to excel file, we should just use in-memory data to see the output.

Step 5: Create a list.

List<person> persons = new List<person>
            {
                new person() {fname="Shu",lname="Ouma"},
                new person() {fname="Kirigaya",lname="Kazuto"},
                new person() {fname="Chiaki",lname="Shinichi"},
            };

Your code should look like this. Since we've just created the class person, we're gonna populate that class by the use of List<> to make sure it can hold more than one value. This is one of the main advantages of Generics.

Step 6: Export the data to excel

int row = 1; //use row variable to control each row and loop it through
            worksheet.Name = "List of persons"; //sets the name of worksheet
            worksheet.Cells[row, 1].Value = "List of Employees within XYZ company";
            row += 1;
            foreach (var person in persons)
            {
                worksheet.Cells[row, 1].Value = person.fname;
                worksheet.Cells[row,1].Interior.Color = Color.Red; //apply color in excel
                worksheet.Cells[row, 2].Value = person.lname;
                row++;
            }
            workbook.SaveAs(Filename: @"d:\demo.xls",FileFormat: XlFileFormat.xlWorkbookNormal);

Your code should look like this. First off, I used a row variable to identify each row within the cell since the first index within worksheet.Cells[] tells what rows to access and the second index in its corresponding column.

Visual representation: workbook.Cells[row,column]

The code will loop through the first row down until all the collections of data were done looping by foreach. I used the first column to display the first name and second column for last name respectively. You can also apply colors to each cells by adding worksheet.Cells[row,1].Interior.Color. Make sure you have System.Drawing referenced within the application.

Although, these would not work if you won't set your application to .net framework 4.0. Thanks to that, we've been able to export our set of data to Excel file with just a few lines of code. I've seen few tutorials before within the web and I've used none of them since to me, they're just gonna complicate my code so I've come up with this simpler approach of the code.

Lastly, you can save the application by using workbook.SaveAs(). Alternatively, you may not use this since we've set the visibility of Excel File to true. Having this set to true, the Excel Application will immediately open with all the data exported and you can just immediately go to excel application to set its save location.

And eureka! You've successfully exported your application data to excel. I hope this will help some developers who've currently experiencing frustrations in working with Excel files just like I used to.

P.S. for those who are delving deeper in interacting with MS excel, unfortunately I only tackled a basic concept and presented a simple algorithm in interacting with Cells, worksheets and workbook within Excel. If you wanted a more technical-specific-formatted approach, look elsewhere.