How do I write to an .xls file format in C#?

Question

Currently, I have some code that is able to create the file, create new worksheets, change the active worksheet, but it won't write to the cells. Below is my code:

var excelApp = new Excel.Application
{
    Visible = false,
    ScreenUpdating = false
};

object misValue = System.Reflection.Missing.Value;

var workbook = excelApp.Workbooks.Add(misValue);
var worksheets = workbook.Worksheets;
var worksheet = (Excel.Worksheet)workbook.ActiveSheet;

worksheet.Cells[3, 3].Value2 = "123"; //This is the part where I'm struggling

workbook.SaveAs(completePathToFile, Excel.XlFileFormat.xlWorkbookNormal);
workbook.Close();

How it currently is, it throws:

Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'Cannot perform runtime binding on a null reference'

Which makes little sense as to why it would throw that when I'm trying to set its value to not null. I have also tried removing the Value2 and just setting the cell directly. The error goes away, but it still doesn't write to the .xls file.

Have a look at this: https://stackoverflow.com/questions/23041021/how-to-write-some-data-to-excel-file-xlsx — Mikaal Anwar, May 23 '18 at 15:23
Your code actually works on my machine (assuming a nuget of `Install-Package Microsoft.Office.Interop.Excel` and `using Excel = Microsoft.Office.Interop.Excel;`). You might be falling foul of the security restrictions around automating the Office Suite - you will need to [disable security settings like this](https://support.office.com/en-us/article/enable-or-disable-macros-in-office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6) to enable external automation — StuartLC, May 23 '18 at 15:25
@StuartLC That seems like the most likely problem. I tried enabling macros, but that didn't fix anything. — Alex, May 23 '18 at 15:54
Strongly suggest that you make [Excel visible](https://stackoverflow.com/questions/17629805/how-to-make-excel-sheet-visible-programmatically-in-c) while you dev and debug - with some luck might be a popup or other indication of why it isn't playing nicely with your code. — StuartLC, May 23 '18 at 19:09
@StuartLC Justr tried that, didn't make a difference. I think my Visual Studios and Excel just don't work together for some reason. — Alex, May 23 '18 at 21:23

score 0 · Answer 1 · answered May 23 '18 at 15:23

Try

     m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));

        (Excel.Workbooks)m_objExcel.Workbooks;
        //Start a new workbook in Excel.
        m_objExcel = new Excel.Application();
        //Add data to cells in the first worksheet in the new workbook.
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
             m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
             m_objRange = m_objSheet.Range["A1", m_objOpt);
             m_objRange.Value = "Last Name";
             m_objRange = m_objSheet.Range["B1", m_objOpt);
             m_objRange.Value = "First Name";
             m_objRange = m_objSheet.Range["A2", m_objOpt);
             m_objRange.Value = "Doe";
             m_objRange = m_objSheet.Range["B2", m_objOpt);
             m_objRange.Value = "John";

              //Apply bold to cells A1:B1.
             m_objRange = m_objSheet.Range["A1", "B1");
             m_objFont = m_objRange.Font;
             m_objFont.Bold = true;

              //Save the Workbook and quit Excel.
             m_objBook.SaveAs(m_strSampleFolder + "Book1.xls", m_objOpt, m_objOpt,
                 m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                 m_objOpt, m_objOpt, m_objOpt, m_objOpt);
             m_objBook.Close(false, m_objOpt, m_objOpt);
             m_objExcel.Quit();

How do I write to an .xls file format in C#?

1 Answers1