asked on

Manipulating Excel files in C#

Results.xlsx

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.

Example:

* 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.

Thanks,
Ashwin

plusone3055

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.
http://www.excelforum.com/excel-programming/707434-how-to-copy-one-column-into-another-sheet.html

For II.
http://stackoverflow.com/questions/1221696/access-vba-how-to-add-new-sheets-to-excel

For III
http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

Ashwin_shastry

ASKER

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# ?

plusone3055

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 :)

plusone3055

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

Ashwin_shastry

ASKER

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 :((

plusone3055

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
http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html

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
http://msdn.microsoft.com/en-us/library/bb608613.aspx

side note: why such a deadline ?

Ashwin_shastry

ASKER

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 !!!!!!!!!!!!!!

plusone3055

okay so im understanding 100%

your getting a path to an excel File

your putting that path into your C# Program

Step1
make a VBA macro in an excell sheet to make comparisons

Step2 Attach that Excell sheet inside your C# Application

Step3
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

:)

dlmille

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

Dave

plusone3055

heres a grat one for comparing two sheets inthe same book

http://www.ozgrid.com/forum/showthread.php?t=111676&page=1

Ashwin_shastry

ASKER

@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;

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

                if (xlApp == null)
                    return;

                // 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.Save();
                xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
                xlApp.Quit();
            }
            finally
            {
                Marshal.ReleaseComObject(xlNewSheet);
                Marshal.ReleaseComObject(xlSheets);
                Marshal.ReleaseComObject(xlWorkbook);
                Marshal.ReleaseComObject(xlApp);
                xlApp = null;
            }
        }
    }
}

Open in new window

Ashwin_shastry

ASKER

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

I meant * programmatically ... I have enough problems here :(

plusone3055

http://stackoverflow.com/questions/193092/c-how-to-add-excel-worksheet-programatically-office-xp-2003

Ashwin_shastry

ASKER

@plusone3055: Yes that's the link I used ... for some reason its not releasing the excel even if I close my console app.

Ashwin_shastry

ASKER

Okayy I got it working ! ....

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

Marshal.ReleaseComObject(xlNewSheet);
Marshal.ReleaseComObject(xlSheets);

To
Marshal.ReleaseComObject(xlSheets);
Marshal.ReleaseComObject(xlNewSheet);

plusone3055

right :)

plusone3055

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 :)

plusone3055

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_shastry

ASKER

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_shastry

ASKER

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. ?

Thanks,
Ashwin.

plusone3055

sure you can attach

Ashwin_shastry

ASKER

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

Ashwin
Program.cs
Results1.xlsx

ASKER CERTIFIED SOLUTION

plusone3055

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ashwin_shastry

ASKER

Thanks for your time and help !