C#COM Interop Excel：如何使用Interop Excel从C#写入单元格？ [英] C# COM Interop Excel: How to write to cells from C# using Interop Excel?

问题描述

我正在编写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();
}

推荐答案

如果使用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();
        }
    }
}

这篇关于C#COM Interop Excel：如何使用Interop Excel从C#写入单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！