Click here to Skip to main content
Click here to Skip to main content

Automating MS Excel Documents

, 25 Oct 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
This article will help demonstrate how to automate and get content from an MS Excel document.

Introduction

I have been working on automating MS Office applications since some time. I have seen that it is very easy to find information on automation of MS Word but it is difficult to find information on automation of PowerPoint or Excel.

So I decided to write one and share the information with others.

Using the code

Automation of Excel is the same as for Word. Create a new project, go ahead and right click on References in Solution Explorer, and select Add Reference… When the Add Reference window comes up, select the COM tab. This will list all component names which are available on your machine. Since we are going to use MS Excel, we will scroll down until we find Microsoft Excel Object Library.

The following code will help understand the rest of the automation:

public string excelExtract(string path)
{
    object ex_missing = System.Reflection.Missing.Value;

    object ex_visible = true;
    object ex_false = false;
    object ex_true = true;

    object ex_update_links = 0;
    object ex_read_only = ex_true;
    object ex_format = 1;
    object ex_password = "Govardhan";
    object ex_write_res_password = ex_missing;
    object ex_ignore_read_only_recommend = ex_true;
    object ex_origin = ex_missing;
    object ex_delimiter = ex_missing;
    object ex_editable = ex_false;
    object ex_notify = ex_false;
    object ex_converter = 0;
    object ex_add_to_mru = ex_false;
    object ex_local = ex_false;
    object ex_corrupt_load = ex_false;

    object ex_save_changes = ex_false;
    object ex_route_workbook = ex_false;

    bool errorFlag = false;

    Excel.Application excelApp = null;
    Excel.Workbook excelWorkbook = null;
    Excel.Sheets excelSheets = null;
    Excel.Worksheet excelWorksheet = null;

    excelApp = new Excel.ApplicationClass();
    excelApp.Visible = false;

    object missing = System.Reflection.Missing.Value;

    string fileName = path;

    string heading = null;

    string content = null;

    try
    {
        excelWorkbook = excelApp.Workbooks.Open(
            fileName, ex_update_links, ex_read_only, 
            ex_format, ex_password,
            ex_write_res_password, 
            ex_ignore_read_only_recommend, ex_origin,
            ex_delimiter, ex_editable, 
            ex_notify, ex_converter, ex_add_to_mru,
            ex_local, ex_corrupt_load);

        string startUpPath = path.Substring(0, path.Length - 4); 
        excelSheets = excelWorkbook.Worksheets;
        int count = excelSheets.Count;

        if (excelSheets != null)
        {
            for (int i = 1; i <= count; i++)
            {
                string sheetName = startUpPath + "exc_text_" + i + ".txt";
                excelWorksheet = (Excel.Worksheet)excelSheets.get_Item((object)i);
                excelWorksheet.Activate();
                excelWorksheet.SaveAs(sheetName, Excel.XlFileFormat.xlTextWindows, 
                  missing, missing, missing, missing, missing, missing, missing, missing);
            }

            excelWorkbook.Close(false, missing, missing);

            for (int i = 1; i <= count; i++)
            {

                heading = "\r\n\r\n" + 
                  "                EXCEL WORKSHEET " + i + 
                  "                " + "\r\n\r\n";

                content += heading;

                string sheetName = startUpPath + "exc_text_" + i + ".txt";

                FileStream fs = new FileStream(sheetName, 
                           System.IO.FileMode.Open, System.IO.FileAccess.Read);

                StreamReader sr = new StreamReader(sheetName);

                content += sr.ReadToEnd().ToString();

                sr.Close();

                fs.Close();

                File.Delete(sheetName);

            }
        }
    }
    catch (System.Exception error)
    {
        string temp = error.Message.ToString();
        errorFlag = true;
      
        if (excelApp != null)
            excelApp.Quit();
    }
    finally
    {
        if (excelApp != null)
            excelApp.Quit();
    }
    if (!errorFlag)
    {
        return (content);
    }
    else
        return ("");
}

Points of Interest

I want to mention one thing: I have used the Office XP COM object and this can be used with MS Office 2003 and 2007.

Good luck guys...

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Govardhana Reddy
Web Developer India
India India
This is Govardhana Reddy, i am here to explore this world of INTERNET. I feel this is one way through which i can explore this world of INTERNET.
 
I want to be one among the best in this profession (a Software Developer, not a Software Engineer its a bit Controversial.) if not the "BEST"
 
My definition of a Software Engineer : "A person who knows what to cut/copy and where to paste".
 
Apart from my technical stuff I love Long Drives, Computer Gaming, Sports, Bikes and much more to say.
 
Anyways long road ahead keep me accompanied...
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 25 Oct 2007
Article Copyright 2007 by Govardhana Reddy
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid