C# COM互操作Excel：如何使用Interop Excel从C#写入单元格？

Question

C# COM互操作Excel：如何使用Interop Excel从C#写入单元格？

3

我在编写C#代码，最终想要将生成的数组导出到Excel文件中。为此，我查找了示例代码以先运行模拟结果，然后再用于我的代码。我尝试使用Interop Excel实现代码，虽然代码能够运行，可以打开/创建工作簿、打开/创建工作表、重命名工作表、将结果保存下来，但是我无法更改单元格。无论值更改还是格式更改都不起作用。它会保存带有更改的空Excel文件。

请参见下面我尝试运行的示例代码：我正在使用Rider，但在Visual Studio中也失败了。还尝试从多台计算机上进行操作，但也不起作用。.NET框架是4.0.3，安装的Interop包是最新的15.0.4795（同时Microsoft Office Core也安装了最新的15.0.0版本）。CSV写入确实有效（请参见第一个代码片段中的注释部分）。

我不知道还能尝试什么，如果需要进一步的上下文信息，请告诉我。谢谢您的帮助！

using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;

public void ExcelExport()
{
    var fileLoc = "...\\test.xlsx";

    // CSV writer
    // using (TextWriter sw = new StreamWriter(fileLoc))
    // {
    //     string strData = "Zaara";
    //     float floatData = 324.563F;//Note it's a float not string
    //     sw.WriteLine("{0},{1}", strData, floatData.ToString("F2"));
    // }
    
    var excelApp = new Excel.Application();
    excelApp.Visible = true;
    excelApp.DisplayAlerts = false;
    var workBook = (Excel.Workbook) excelApp.Workbooks.Add();
    var reportSheet = (Excel.Worksheet) workBook.Worksheets.Add();
    reportSheet.Name = "Report";
    reportSheet.Cells[3, 4] = "Contract Name";
    reportSheet.Range["A2, A2"].Value2 = 10;
    workBook.SaveAs(fileLoc);
    workBook.Close();
    excelApp.DisplayAlerts = true;
    excelApp.Quit();

}


public void ExcelExport2()
{
    var fileLoc = "...\\test2.xlsx";
    
    Excel.Application oXL;
    Excel._Workbook oWB;
    Excel._Worksheet oSheet;
    Excel.Range oRng;

    //Start Excel and get Application object.
    oXL = new Excel.Application();
    oXL.Visible = true;

    //Get a new workbook.
    oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
    oSheet = (Excel._Worksheet)oWB.ActiveSheet;

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

    // 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).
    oSheet.get_Range("A2", "B6").Value2 = saNames;

    //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
    oRng = oSheet.get_Range("D2", "D6");
    oRng.Formula = "=RAND()*100000";
    oRng.NumberFormat = "$0.00";
    
    oWB.SaveAs(fileLoc, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
        false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    oWB.Close();
    oXL.Quit();
}

- Elruna

使用Interop Excel会很慢，尝试使用OleDb来完成此操作。 - arne

你的代码在哪里和何时运行？ - Eugene Astafiev

你把结果Excel工作簿保存在哪里？ - Eugene Astafiev

目前的问题是我甚至无法将单个值放入Excel文件中，因此何时和何地似乎不太相关。我使用[Test] [Explicit]将它们放入测试文件中以单独调试/运行它们。我将结果保存到fileLoc中，该变量设置为我的桌面。 - Elruna

你尝试过调试代码吗？代码中是否有任何异常？ - Eugene Astafiev

4个回答

0

Interop Excel非常慢。我使用了datagradview，有超过50k行和10列的数据。我需要添加一个进度条，因为用户会认为程序崩溃了。

如果你想更快地运行任务，你需要一些其他的第三方库。我尝试了EPPlus。它非常快。但是缺点是它占用内存较多：你可以在这里找到代码：

https://dev59.com/i7_5oIgBc1ULPQZFDhrK#72763371

- robni

0

我建议将Application.Calculation属性设置为xlCalculationManual值，然后在完成后再设置回xlCalculationAutomatic。

您还可以考虑将Application.ScreenUpdating设置为false，然后再设置回true。

作为可能的解决方法，您可以考虑使用Open XML SDK。

- Eugene Astafiev

谢谢Eugene！你能在创建/打开工作簿之前设置这些属性，然后在关闭/保存之前将其设置回来吗？ - Elruna

我添加了这两个设置，但结果没有改变：遗憾的是单元格没有被改变。 - Elruna

0

这很奇怪。出于好奇，我尝试运行用户9938提供的代码，但我得到与Elruna相同的结果。它可以运行，Excel确实被正确打开、保存和关闭，但它没有在文件中写入任何内容。如果代码已经经过测试，那么可能是Excel中特定的配置问题吗？

- Greenlambo

请跟随并尝试在评论中的讨论，看看您的问题是否仍然存在。 - HardcoreGamer

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user09938 · Accepted Answer

如果使用Excel互操作，请尝试以下操作：

using Excel = Microsoft.Office.Interop.Excel;

WriteToExcel:

public static void WriteToExcel(string filename, string[,] data)
{
    //Write cell value using row number and column number

    //*Note: Excel cells, can also be referenced by name, such as "E2" by using "Range"
    //
    //       All indices in Excel (rowNumber, columnNumber, etc...) start with 1 
    //       The format is: <rowNumber>, <columnNumber>
    //       The top left-most column, is: 1,1


    object oMissing = System.Reflection.Missing.Value;

    Excel.Application excelApp = null;
    Excel.Range range = null;
    Excel.Workbook workbook = null;
    Excel.Worksheet worksheet = null;

    int worksheetCount = 0;

    try
    {
        //create new instance
        excelApp = new Excel.Application();

        //suppress displaying alerts (such as prompting to overwrite existing file)
        excelApp.DisplayAlerts = false;

        //set Excel visability
        excelApp.Visible = true;

        //disable user control while modifying the Excel Workbook
        //to prevent user interference
        //only necessary if Excel application Visibility property = true
        //excelApp.UserControl = false;

        //disable
        //excelApp.Calculation = Excel.XlCalculation.xlCalculationManual;

        //if writing/updating a large amount of data
        //disable screen updating by setting value to false
        //for better performance.
        //re-enable when done writing/updating data, if desired
        //excelApp.ScreenUpdating = false;

        //create new workbook
        workbook = excelApp.Workbooks.Add();

        //get number of existing worksheets
        worksheetCount = workbook.Sheets.Count;

        //add a worksheet and set the value to the new worksheet
        worksheet = workbook.Sheets.Add();

        if (data != null)
        {
            for (int i = 0; i < data.GetLength(0); i++)
            {
                int rowNum = i + 1;

                for (int j = 0; j < data.GetLength(1); j++)
                {
                    int colNum = j + 1;

                    //set cell location that data needs to be written to
                    //range = worksheet.Cells[rowNum, colNum];

                    //set value of cell
                    //range.Value = data[i,j];

                    //set value of cell
                    worksheet.Cells[rowNum, colNum] = data[i,j];
                }
            }
        }

        //enable
        //excelApp.Calculation = Excel.XlCalculation.xlCalculationManual;
        //excelApp.ScreenUpdating = true;

        //save Workbook - if file exists, overwrite it
        workbook.SaveAs(filename, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

        System.Diagnostics.Debug.WriteLine("Status: Complete. " + DateTime.Now.ToString("HH:mm:ss"));
    }
    catch (Exception ex)
    {
        string errMsg = "Error (WriteToExcel) - " + ex.Message;
        System.Diagnostics.Debug.WriteLine(errMsg);

        if (ex.Message.StartsWith("Cannot access read-only document"))
        {
            System.Windows.Forms.MessageBox.Show(ex.Message + "Please close the workbook, before trying again.", "Error - Unable To Write To Workbook", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
        }
    }
    finally
    {
        if (workbook != null)
        {
            //close workbook
            workbook.Close();

            //release all resources
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
        }

        if (excelApp != null)
        {
            //close Excel
            excelApp.Quit();

            //release all resources
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
        }
    }
}

创建一些测试数据：

private string[,] CreateTestData()
{
    string[,] data = new string[6, 4];

    data[0, 0] = "First Name";
    data[0, 1] = "Last Name";
    data[0, 2] = "Full Name";
    data[0, 3] = "Salary";

    data[1, 0] = "John";
    data[1, 1] = "Smith";

    data[2, 0] = "Tom";
    data[2, 1] = "Brown";

    data[3, 0] = "Sue";
    data[3, 1] = "Thomas";

    data[4, 0] = "Jane";
    data[4, 1] = "Jones";

    data[5, 0] = "Adam";
    data[5, 1] = "Johnson";

    return data;
}

更新：

以下是完整代码：

创建一个类（名称：HelperExcelInterop.cs）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelInteropTest
{
    public class HelperExcelInterop
    {
        public static void WriteToExcel(string filename, string[,] data)
        {
            //Write cell value using row number and column number

            //*Note: Excel cells, can also be referenced by name, such as "E2" by using "Range"
            //
            //       All indices in Excel (rowNumber, columnNumber, etc...) start with 1 
            //       The format is: <rowNumber>, <columnNumber>
            //       The top left-most column, is: 1,1


            object oMissing = System.Reflection.Missing.Value;

            Excel.Application excelApp = null;
            Excel.Range range = null;
            Excel.Workbook workbook = null;
            Excel.Worksheet worksheet = null;

            int worksheetCount = 0;

            try
            {
                //create new instance
                excelApp = new Excel.Application();

                //suppress displaying alerts (such as prompting to overwrite existing file)
                excelApp.DisplayAlerts = false;

                //set Excel visability
                excelApp.Visible = true;

                //disable user control while modifying the Excel Workbook
                //to prevent user interference
                //only necessary if Excel application Visibility property = true
                //excelApp.UserControl = false;

                //disable
                //excelApp.Calculation = Excel.XlCalculation.xlCalculationManual;

                //if writing/updating a large amount of data
                //disable screen updating by setting value to false
                //for better performance.
                //re-enable when done writing/updating data, if desired
                //excelApp.ScreenUpdating = false;

                //create new workbook
                workbook = excelApp.Workbooks.Add();

                //get number of existing worksheets
                worksheetCount = workbook.Sheets.Count;

                //add a worksheet and set the value to the new worksheet
                worksheet = workbook.Sheets.Add();

                if (data != null)
                {
                    for (int i = 0; i < data.GetLength(0); i++)
                    {
                        int rowNum = i + 1;

                        for (int j = 0; j < data.GetLength(1); j++)
                        {
                            int colNum = j + 1;

                            //set cell location that data needs to be written to
                            //range = worksheet.Cells[rowNum, colNum];

                            //set value of cell
                            //range.Value = data[i,j];

                            //set value of cell
                            worksheet.Cells[rowNum, colNum] = data[i,j];
                        }
                    }
                }

                //enable
                //excelApp.Calculation = Excel.XlCalculation.xlCalculationManual;
                //excelApp.ScreenUpdating = true;

                //save Workbook - if file exists, overwrite it
                workbook.SaveAs(filename, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

                System.Diagnostics.Debug.WriteLine("Status: Complete. " + DateTime.Now.ToString("HH:mm:ss"));
            }
            catch (Exception ex)
            {
                string errMsg = "Error (WriteToExcel) - " + ex.Message;
                System.Diagnostics.Debug.WriteLine(errMsg);

                if (ex.Message.StartsWith("Cannot access read-only document"))
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message + "Please close the workbook, before trying again.", "Error - Unable To Write To Workbook", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                }
            }
            finally
            {
                if (workbook != null)
                {
                    //close workbook
                    workbook.Close();

                    //release all resources
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
                }

                if (excelApp != null)
                {
                    //close Excel
                    excelApp.Quit();

                    //release all resources
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
                }
            }
        }
    }
}

在 Form1 上添加一个按钮（名称：btnRun）。

双击该按钮以添加 Click 事件处理程序。

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExcelInteropTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private string[,] CreateTestData()
        {
            string[,] data = new string[6, 4];

            data[0, 0] = "First Name";
            data[0, 1] = "Last Name";
            data[0, 2] = "Full Name";
            data[0, 3] = "Salary";

            data[1, 0] = "John";
            data[1, 1] = "Smith";

            data[2, 0] = "Tom";
            data[2, 1] = "Brown";

            data[3, 0] = "Sue";
            data[3, 1] = "Thomas";

            data[4, 0] = "Jane";
            data[4, 1] = "Jones";

            data[5, 0] = "Adam";
            data[5, 1] = "Johnson";

            return data;
        }

        private void WriteData()
        {
            string[,] data = CreateTestData();

            string filename = System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Test123.xlsx");
            System.Diagnostics.Debug.WriteLine("filename: " + filename);
            HelperExcelInterop.WriteToExcel(filename, data);
        }

        private void btnRun_Click(object sender, EventArgs e)
        {
            WriteData();
        }
    }
}