Function to write cells in excel

Question

I get a bit of code from here: http://forum.codecall.net/topic/71788-reading-excel-files-in-c/ which works fine and is easy to understand for me. It can only read excel cells tho, and I want to write some too. So I want to do this in my main: excel_setValue("C10", "520");

This is what I tried as function:

    private static Microsoft.Office.Interop.Excel.ApplicationClass appExcel;
    private static Workbook newWorkbook = null;
    private static _Worksheet objsheet = null;


static string excel_setValue(string cellname, string value)  
        {
            if (objsheet.get_Range(cellname).get_Value().ToString() == string.Empty) // Here is error
            {
                Console.WriteLine("watch out u trying to overwrite files");
            }
            else
            {
                objsheet.get_Range(cellname).set_Value(value);
            }         
        }

It tells me: NullReferenceExpection was unhandled - Object reference not set to an instance of an object.

Another error I got when I only put this:

static void excel_setValue(string cellname, string value)
        {
            //if (objsheet.get_Range(cellname).get_Value().ToString() == string.Empty)
            //{
            //    Console.WriteLine("kijk uit je probeerd cellen te overschrijven");
            //}
            //else
            //{
                objsheet.get_Range(cellname).set_Value(value);
            //}         
        }

Error: COMException was unhandled: Exception from HRESULT: 0x800A03EC

How I call my excel_setValue:

excel_init("C:\\");
excel_setValue("B10","520");

excel_init:

static void excel_init(String path)
        {
            appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                Console.WriteLine("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;
            }

        }

If get_Range returns null, you will get that error for get_Value(). Please show how you're calling the excel_setValue method. — JWiley, Jun 09 '15 at 14:11
Within this method (this scope), `objsheet` does not exist. Or is it defined within the encapsulating class? — Melvin, Jun 09 '15 at 14:12
ok, looks like you're not setting objsheet as Melvin pointed out. `private static _Worksheet objsheet = null;` It's null here, and never set before calling `get_Range` — JWiley, Jun 09 '15 at 14:15

score 1 · Accepted Answer · edited May 23 '17 at 10:26

1

As shown in the code you linked in the method excel_init, you need to initialize objsheet. It is done on this line in the link:

objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;

For this edit in your question:

How I call my excel_setValue:

excel_setValue("B10","520");
excel_init("C:\\");

Is that the order of execution you're using? You have to call excel_init first and initialize objsheet before using it in excel_setValue.

You may be using get_Range incorrectly, see this question for further examples.

You are also using Range.set_Value incorrectly, try using objsheet.get_Range(cellname).set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault,value);

edited May 23 '17 at 10:26

Community

1
1

answered Jun 09 '15 at 14:17

JWiley

3,129
8
41
66

I added the init method in my code to the questoin, it is in this part of the code – wouter Jun 09 '15 at 14:18
No this is not the order I execute, it was a speed error. – wouter Jun 09 '15 at 14:23
I'm seeing [get_Range](https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.get_range.aspx) as taking two parameters... – JWiley Jun 09 '15 at 14:32
objsheet.get_Range(cellname).set_Value(Type.Missing, value); this fixed the error, but it doenst print the data in the excel table ??? sorry im new in this – wouter Jun 09 '15 at 14:34
Great! Please mark the answer as accepted when you can. Also as a side note, interop is notorious for memory leaks if not used correctly, not to mention it requires Excel to be installed. I'd check out [closed xml](https://closedxml.codeplex.com/) if you continue doing excel creation/manipulation. – JWiley Jun 09 '15 at 14:41

Function to write cells in excel

1 Answers1