Automating MS Excel Documents

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