C# COM Interop Excel: How to write to cells from C# using Interop Excel?

Question

I am writing C# code in which at the end I would want to export the resulting array to Excel. For this I looked up example codes to run first mock results to then use for my code. I tried implementing the codes using Interop Excel and while the code does run, can open/create a workbook, open/create a worksheet, rename worksheets, save the results down I cannot alter the cells. Neither value changes nor formatting changes work for me. It saves down a blank Excel file with the changes to the worksheets lets say.

Please see below the example codes I tried to run: I am using Rider but tried the results and failed in Visual Studio as well. Also tried from multiple computers and didn't work. .NET framework is 4.0.3 and the Interop package installed is the most recent 15.0.4795 (while also Microsoft Office Core is installed with the most recent 15.0.0 version). CSV writing did work (see the commented part in the first snippet).

I do not know what else to try, if I can give further context I am very happy to. Thank you for your help!

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

With interop excel this is quite slow, try if you can do this with OleDb as well — arne
– arne, Commented Aug 5, 2021 at 12:52
For now, the problem is I cannot even put a single value in an Excel file so the when and where seems less relevant. I put them in a test file using the [Test] [Explicit] starts to debug/run them separately. I save the results to fileLoc which is set to my Desktop. — Elruna
– Elruna, Commented Aug 5, 2021 at 13:42
Did you try to debug the code? Do you get any exceptions in the code? — Eugene Astafiev
– Eugene Astafiev, Commented Aug 5, 2021 at 15:26

It all makes cents · Accepted Answer · 2021-08-05 14:51:14Z

If using Excel interop, try the following:

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

Create some test data:

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;
}

Update:

Here's the complete code:

Create a class (name: 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);
                }
            }
        }
    }
}

On Form1, add a button (name: btnRun)

Double-click the button to add the Click event handler.

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

Thanks a lot for your help! I ran the code you provided but the same issue prevailed: It created an Excel workbook onto the desktop (added that as the fileLoc) with two empty sheets, Sheet2 and Sheet1 in this order. Not sure whether it is some settings level issue but it seems that I cannot write to Excel cells directly. Any idea where I could look for it/read more? Thanks
In theory the original code snippets were working parts as well but for some reason I cannot access the cells to change values/formatting etc...The code I used to call your methods was ``` public void ExcelWriting() { var fileLoc = "...\\test.xlsx"; var data = CreateTestData(); WriteToExcel(fileLoc, data); } ```
What do you mean by "cannot access the cells to change values/formatting"? If the code I posted isn't working for you, try rebooting your computer.
Thank you for the update, currently including that and trying the run with that version. What I meant by that is even with the original code, the one by you, using a different computer or environment and even after rebooting what I experience is that a) it did create/open the workbook b) it did create/open the worksheet in the workbook c) could rename the worksheet d) left all the sheets blank, no cells altered e) did save the workbook to the location intended to. Hope that clears it up!
I ran the code with the updated version and when I created a solution file from scratch, added in the button, the codes etc... it worked. Is the button necessary for the file export or could something as simple as ``` public void ExcelWriting() { var fileLoc = "...\\test.xlsx"; var data = CreateTestData(); WriteToExcel(fileLoc, data); } ``` , calliing the two methods created by you, should also generate the export? The latter I had trouble with the former I could replicate!

Eugene Astafiev · Accepted Answer · 2021-08-05 13:05:58Z

0

I'd suggest setting the Application.Calculation property to the xlCalculationManual value, then back to xlCalculationAutomatic when you are done.

You can also consider setting Application.ScreenUpdating to false and then back to true.

As a possible workaround you may consider using the Open XML SDK.

answered Aug 5, 2021 at 13:05

Eugene Astafiev

49.8k3 gold badges28 silver badges50 bronze badges

2 Comments

Elruna Over a year ago

Thanks Eugene! Would you set these properties in the very beginning, just before creating/opening a workbook and then just before closing/saving it set it back?

Elruna Over a year ago

I added these two settings but did not change the outcome: the cells were unaltered sadly.

robni · Accepted Answer · 2022-06-26 16:53:55Z

0

Interop Excel is extremly slow. I had done this with datagradview with more than 50k rows and 10 columns. I needed to add a progressbar, because the user would think the program crashed.

If you want to run the job faster, you need some other 3rd party libary. I had give EPPlus a chance. It's fast like hell. On the con-side, it is memory hungry: You find the code here:

https://stackoverflow.com/a/72763371/17817329

answered Jun 26, 2022 at 16:53

robni

1,0542 gold badges10 silver badges23 bronze badges

Collectives™ on Stack Overflow

C# COM Interop Excel: How to write to cells from C# using Interop Excel?

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related