Automating MS Excel Documents

I had been working on automating MS Office application since some time. I have seen that its very easy to find information on automation of MS Word but its difficult to find the information on automation of others like Power Point, Excel.

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

Using the code

Automation of the Excel is same as Word. Create a new project, go ahead and right click on References in the 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 u 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