• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

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




0
Ashwin_shastry
Asked:
Ashwin_shastry
  • 12
  • 11
1 Solution
 
plusone3055Commented:
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
0
 
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# ?
0
 
plusone3055Commented:
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 :)

 

 
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

0
 
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 :((
0
 
plusone3055Commented:
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 ?
0
 
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 !!!!!!!!!!!!!!
0
 
plusone3055Commented:
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

:)

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

Dave
0
 
plusone3055Commented:
heres a grat one for comparing two sheets inthe same book

http://www.ozgrid.com/forum/showthread.php?t=111676&page=1
0
 
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;

            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

0
 
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 :(
0
 
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.
0
 
Ashwin_shastryAuthor Commented:
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);    
0
 
plusone3055Commented:
right :)
0
 
plusone3055Commented:
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 :)

0
 
plusone3055Commented:
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
0
 
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 !!
0
 
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. ?

Thanks,
Ashwin.
0
 
plusone3055Commented:
sure you can attach
0
 
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

Ashwin
Program.cs
Results1.xlsx
0
 
plusone3055Commented:
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

0
 
Ashwin_shastryAuthor Commented:
Thanks for your time and help !
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now