自动化 MS Excel 文档

引言

我一直在从事 MS Office 应用程序的自动化工作。 我已经看到，很容易找到有关 MS Word 自动化的信息，但很难找到有关 PowerPoint 或 Excel 自动化的信息。

所以我决定写一篇文章并与大家分享这些信息。

使用代码

Excel 的自动化与 Word 相同。 创建一个新项目，然后在解决方案资源管理器中右键单击“引用”，然后选择“添加引用”。 当“添加引用”窗口出现时，选择“COM”选项卡。 这将列出机器上所有可用的组件名称。 由于我们将使用 MS Excel，我们将向下滚动，直到找到 Microsoft Excel 对象库。

以下代码将帮助理解其余的自动化过程

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 ("");
}

关注点

我想提一件事：我使用了 Office XP COM 对象，它可以与 MS Office 2003 和 2007 一起使用。

祝你好运！