Running scripts in C#

I have a VBA macro that I would like to be able to run in C#.  I would like to have it execute and run on an Excel file.  I was just wondering how I could accomplish this.
zintechAsked:
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.

santhimurthydCommented:
0
AshokCommented:
using Excel = Microsoft.Office.Interop.Excel;  //(Add Reference - COM Tab - Microsoft Excel 12.0 Object Library)
using System.Reflection;  // Excel
using System.Data.OleDb;  // Reading Excel
.....
Excel.Application excelApp;
Excel._Workbook excelWB;
Excel._Worksheet excelSheet;
Excel.Range oRng;

        private void button1_Click(object sender, EventArgs e)
        {

            try
            {
                //Start Excel and get Application object.
                excelApp = new Excel.Application();
                excelApp.Visible = false;

                //Get a new workbook.
                excelWB = (Excel._Workbook)(excelApp.Workbooks.Add(Missing.Value));
                excelSheet = (Excel._Worksheet)excelWB.ActiveSheet;

                //Add table headers going cell by cell.
                excelSheet.Cells[1, 1] = "First Name";
                excelSheet.Cells[1, 2] = "Last Name";
                excelSheet.Cells[1, 3] = "Full Name";
                excelSheet.Cells[1, 4] = "Salary";

                //Format A1:D1 as bold, vertical alignment = center.
                excelSheet.get_Range("A1", "D1").Font.Bold = true;
                excelSheet.get_Range("A1", "D1").VerticalAlignment =
                    Excel.XlVAlign.xlVAlignCenter;

                // Create an array to multiple values at once.
                string[,] saNames = new string[5, 2];

                saNames[0, 0] = "John";
                saNames[0, 1] = "Smith";
                saNames[1, 0] = "Tom";
                saNames[1, 1] = "Brown";
                saNames[2, 0] = "Sue";
                saNames[2, 1] = "Thomas";
                saNames[3, 0] = "Jane";
                saNames[3, 1] = "Jones";
                saNames[4, 0] = "Adam";
                saNames[4, 1] = "Johnson";

                //Fill A2:B6 with an array of values (First and Last Names).
                excelSheet.get_Range("A2", "B6").Value2 = saNames;

                //Fill C2:C6 with a relative formula (=A2 & " " & B2).
                oRng = excelSheet.get_Range("C2", "C6");
                oRng.Formula = "=A2 & \" \" & B2";

                //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
                oRng = excelSheet.get_Range("D2", "D6");
                oRng.Formula = "=RAND()*100000";
                oRng.NumberFormat = "$0.00";

                //AutoFit columns A:D.
                oRng = excelSheet.get_Range("A1", "D1");
                oRng.EntireColumn.AutoFit();

                //Make sure Excel is visible and give the user control
                //of Microsoft Excel's lifetime.
                excelApp.Visible = false;
                excelApp.UserControl = false;
                excelWB.Close(true, "C:\\CSharp\\Windows\\CalcGain\\Test1.xlsx", false);
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                MessageBox.Show(errorMessage, "Error");
            }
        }

HTH
Ashok
0
AshokCommented:
Here's a "free" to use Export To Excel control  http://www.codeplex.com/ExportToExcel.

This control, allows you to "Export" the contents of your "GridView" control to Excel {no coding required}.

Usage

Simply drag and drop the ExportToExcel button on your page and enjoy "Zero code" export feature.

 Features:

1) Styling : If you used "HeaderStyle", "RowStyle", "AlternateRowStyle" in your GridView, you can simply enable styling by setting the "ApplyStyleInExcel' property to "True". Note: CSS styling is not supported at this point {future release}

2) TimeStamp: If you set the "IncludeTimeStamp" to true, the filename is appended with a "time Stamp". These are very useful for time sensitive reports.

3) PageSize: If you are using Pagination in your GridView, you can choose to export the "Current" page, the "Entire Grid" all pages or set a specific number of records to export. Use "All" for all pages, "Current" for the current page and 20 for first 20 records.

4) Sorting: The Export supports sorting. In otherwords, if you sort your gridview and then export, the excel spreadsheet will also be sorted.

5) GridViewID: If you have more than one gridview in a page, you can specify which gridview you'd like to export by setting this property. You do not have to set any value if you just have one grid view.

6) File Name: You can set the filename that you want to export.

HTH
Ashok
0

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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.