Manipulating Excel files in C#


Hello ,

Here is the summary of what I need to do

I will need to create a program in C# ( using  Visual Studio 2010 ) which will basically take and an Excel file as an input ( attached ).
Create a new work sheet ( Actual results as in the attached file ) compare every row in the Actual Results Worksheet to the Expected Results work sheet for each of those cells having different data I will need to make them in a different color.

Breaking this down.

The input will be an Excel (.xlsx ) file which will have two worksheets

1) Query

2) Expected Results

Now the program will need to do the following

1) Create a new WorkSheet and name it Actual_Results_Current_Date , Current Date will be todays Date

2) Copy the first column TC  from the Expected Results Worksheet and paste it into the  Actual_Results_Current_Date work sheet

3) Create a new column called Validation in the Actual_Results_Current_Date

4) Next run the SQL statement in the Query tab and populate the results in the Actual_Results_Current_Date

  * The query will have the same number of columns as in the Expected results worksheet except the first column TC.

5) Now we will need to compare each and every cell in the Actual_Results_Current_Date work sheet with the every cell in  the Expected Results work sheet.


  * B2 ( Expected Results )  --->  C2 in Actual_Results_Current_Date
  * C2  ( Expected Results ) --->  D2  in Actual_Results_Current_Date
 .... etc

6) For the ones which dont match , I will need to highlight that particular in Actual_Results_Current_Date work sheet in  Red and Mark that row as Fail. For the rows that pass I will mark them as Pass and highlight the cell in Validation Row in Green as seen in the    attached file.

Now I dont expect anyone to send me the whole program to do this .. but I would like to know the following

i) How to copy one column from a worksheet into another ( Step 2 )

ii) How to Create a new Worksheet

ii) And How I can compare two cells in two differnt work sheets and highlight them if they are not equal..

I can spilt this question into two if you wnat me to and allot 500 points each as I know its quite complicated.

Hoping for reply. I really hope some one can help me out as this is pretty urgent and I am not very familar with Excel programming.


Who is Participating?
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.

your a little out of range for just 500 points
I would suggest Contacting this out if you are not a C# programmer
you don't really need too much C# you can use VBA....


For I.

For II.

Ashwin_shastryAuthor Commented:
Thanks for your reply plusone3055,  I understand that this question is out of range for 500 points and I can break it down into multiple questions for you.

I will do this as soon as I get a hang of the means to get the results I am trying to achieve.

Thanks for the links , I looked at the links , now the problem is this is going to be a part of a larger solution.

And the whole solution is in C#, how can I in use VBA in C# ?
absolutely its all part of the Microsoft family :)

a good solution for htis would be to attach an excel sheet to your application and put the VBA code in that excell sheet for all the comparisons :)


Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

and you dont need to break it into different questions
dont broadcast something like that either.. its a nono in EE Rules

Ashwin_shastryAuthor Commented:
Sorry about that... I will make sure it does not happen in future.

"a good solution for htis would be to attach an excel sheet to your application and put the VBA code in that excell sheet for all the comparisons :) "

Now with regards to what you said above... I have to tell you that I have never used VBA with C# in my life ... I know how to read an excel file in C# but after I read it in C# how do I put the VBA code in there to
create a new column , do comparisons and also would you know how I can add data from data set returned from SQL into an excel...

Sorry I am asking too many questions ... but I have hit a dead end with my search online and I need to complete this project by the end of tomorrow :((
not to worry thats what EE is for....

why does this have to be in C # ????

this is sometihng that can be done with VBScript and excel Sheets

but i digresss...

heres a quick tutorial on using VBA

now what you want to do here is insert the VBA into an excel sheet... Then attach it to your C# Applcaition
(like you would with an Image in a folder)

and then to call the code
heres a tutorial for that

side note: why such a deadline ?
Ashwin_shastryAuthor Commented:
Well people are quite crazy here :( hence this deadline ...

Here is the thing , since this is part of a larger solution which is in C#, I will be provided just the path to excel , so my program in c# should be able to get the file, do the necessary manipulations and return a bool to the program ( in C#)  calling the function i am supposed to write ( which takes just the path to the excel file ) .

True would indicate that the comparison is fine and that the results match , false indicates that the results which is got from the DB ( by running the SQL query in the Query tab ) does not match completely.

any help would be greatly greatly appreciated !!!!!!!!!!!!!!
okay so im understanding 100%

your getting a path to an excel File

your putting that path into your C# Program

make a VBA macro in an excell sheet to make comparisons

Step2 Attach that Excell sheet inside your C# Application

Take the data from the excel sheet (from the path you are provided)  and copy(all the data) it into the excel sheet attached inside your C# program

Step4  Now you have the data inside your Excel worksheet inside the C# Program
(this excel sheet has the VBA code in it)

Step 5
You call the VBA code (tuttorial Above) in your applicaiton to compare the data as needed and return the Bool


@plusone3055 - great tips.  Can you assist the OP with the VBA?  I can collaborate if needed.

heres a grat one for comparing two sheets inthe same book
Ashwin_shastryAuthor Commented:
@plusone3055 thank you... I am very sorry about my ignorance but I will need quite a lot of help to get this done :(

If possible , I will need you to hold my hand and walk me through this sir....

I looked up a link which allows me to add a new worksheet to an existing excel file. And I created a console app to do that .. I have added the code...

Now when I run this ... I am able to add a new work sheet , but when I try to open the Excel file it says its in read only mode ... even if I close the console app and try and open it ...

Now we can consider step 2 as done if we can resolve this error message whcih says its in read only ...

with regards to Step 1 How can add a VBA problematically from C# into excel ?

Thanks again for taking time to reply to my dumb question .. :(
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace ExcelManipulate
    class Program
        static void Main(string[] args)
            AddWorksheetToExcelWorkbook(@"C:\TWNM\Code\Release\R11\Excel File Manipulation\ExcelManipulate\ExcelManipulate\Test.xlsx", "Actual Results");

        private static void AddWorksheetToExcelWorkbook(string fullFilename, string worksheetName)
            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Workbook xlWorkbook = null;
            Sheets xlSheets = null;
            Worksheet xlNewSheet = null;

                xlApp = new Microsoft.Office.Interop.Excel.Application();

                if (xlApp == null)

                // Uncomment the line below if you want to see what's happening in Excel
                // xlApp.Visible = true;

                xlWorkbook = xlApp.Workbooks.Open(fullFilename, 0, false, 5, "", "",
                        false, XlPlatform.xlWindows, "",
                        true, false, 0, true, false, false);

                xlSheets = xlWorkbook.Sheets as Sheets;

                // The first argument below inserts the new worksheet as the first one
                xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
                xlNewSheet.Name = worksheetName;

                xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
                xlApp = null;

Open in new window

Ashwin_shastryAuthor Commented:
"with regards to Step 1 How can add a VBA problematically from C# into excel ?"

I meant * programmatically  ... I have enough problems here :(
Ashwin_shastryAuthor Commented:
@plusone3055: Yes that's the link I used ... for some reason its not releasing the excel even if I close my console app.
Ashwin_shastryAuthor Commented:
Okayy I got it working ! ....  

I changed the order in which it was releasing the objects from


right :)
I'm going ot have to get going for the day its 2am over here now and I have 1.5 hour train to ride home. Please continue to follow the steps and tutorials  Ive have provided to finish your assignment.. Don't get too stressed you have all the tools in front of you.. just let it flow and you;ll have the end result..

Also in all fairness im sure others will assist  this evening but please awards points fairly at the end I am the original worker and have kept consistent for 2 + hours :)

If you are in the US it would be 3:40PM EST
I will be able to look at things more in depth around 10am EST the next day
Ashwin_shastryAuthor Commented:

Sure buddy !!! Thanks a ton for all your time ! Greatly appreciated !  Thanks for the encouraging words..

 I will definitely allot points accordingly no question about that ... and Yes i am in the east coast and I am trying get as much done as possible and i am sure i will need your help further...

Have a great night !!
Ashwin_shastryAuthor Commented:
Hi plusone3055,

I was able to accomplish what I  wanted to achieve.  I have written a console app to do whatever i wanted to do in C#. I am not sure if its the best way to do this ..

I wanted to know if I can attach the project here for you to just review it and see if there is a better way to do it ... do you think it would be possible for you to do a quick review. ?

sure you can attach
Ashwin_shastryAuthor Commented:
Hi plusone3055,

I have attached two files

Progrma.cs and Results1.xlsx

Program.cs has all the code which manipulates the Results1.xlsx.

If you can let me know any improvements I can do that would be great.

Thanks a lot for your time again

I think you did a great job with that.. I'm actaully going to save it in my toolbox it may come in handy someday


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
Ashwin_shastryAuthor Commented:
Thanks for your time and help !
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

From novice to tech pro — start learning today.