How to allow excel data modification in manual as well as programmatically?

Question

I am using C# windows application for Excel data add/update. I had added Microsoft.Office.Interop.Excel reference(Reference -> Right Click -> Add Reference -> COM -> Type Libraries -> Microsoft Excel 1X.0 Object Libraries). On my form, I have one panel control panel1, one List-box lstSamples and two button btnAddSample, btnFormatWorksheet.

My sample code is as below:

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Windows.Forms;


public partial class Form1 : Form
{
    Microsoft.Office.Interop.Excel.Application excelApp;
    Workbook excelWorkBook;
    Worksheet excelWorkSheet;

    public Form1()
    {
        InitializeComponent();
        LoadExcelFile();
    }

    [DllImport("user32.dll")]
    static extern IntPtr SetParent(IntPtr hWndChild, IntPtr hWndNewParent);

    private void LoadExcelFile()
    {
        excelApp = new Microsoft.Office.Interop.Excel.Application();

        excelApp.Visible = true;
        excelApp.ScreenUpdating = true;
        excelApp.EnableAutoComplete = false;
        excelWorkBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        IntPtr excelHwnd = new IntPtr(excelApp.Application.Hwnd);
        SetParent(excelHwnd, panel1.Handle);
    }

    private void btnAddSample_Click(object sender, EventArgs e)
    {
        excelWorkSheet = (Worksheet)excelWorkBook.Worksheets.get_Item(1);
        int lastUsedRow = excelWorkSheet.UsedRange.Rows.Count;
        excelWorkSheet.Cells[lastUsedRow + 1, 1] = lstSamples.SelectedItem.ToString();
        lstSamples.Items.Remove(lstSamples.SelectedItem);
    }

   private void btnFormatWorksheet_Click(object sender, EventArgs e)
   {
        Range chartRange;
        excelWorkSheet = (Worksheet)excelWorkBook.Worksheets.get_Item(1);
        chartRange = excelWorkSheet.get_Range("b2", "e9");
        chartRange.BorderAround(XlLineStyle.xlContinuous, 
        XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, 
        XlColorIndex.xlColorIndexAutomatic);

    }
}

Please follow the steps as I mentioned 1. Run the application and add data in "A1" cell (Which is of string type) 2. Again add some data in "A2" cell and Press enter 3. Select one item from lstSamples listbox and click on btnAddSample (Result is like selected item will get added into "A3" cell 4. Try to modify "A1" or "A2" cell data. (Here lstSample is having items of string type like Test1, Test2, Test3,....). If you are able to edit cells then click on btnFormatWorksheet then try to edit any cell.

I cannot reproduce your issue. How exactly does the file get opened as read-only? You're not even saving the file in your code. Do you save it manually and then click the btnAddSample button? — 41686d6564
1) How is it read-only if the file is not saved (or do you save it before clicking the button?) 2) Please be specific about how you determine it's actually open in read-only mode. For instance, does it say "[read-only]" in the title? Are you not able to modify a cell? Are you not able to save? Etc. — 41686d6564
Actually that file is not saved any where, even if I don't know what was file mode "[read-only]" or "[read-write]", but I'm not able to do the modification manually (not able to modify cell)once that code block is executed. — Kiran Desai
Well, I cannot reproduce that? What value is selected in lstSamples when you click the button? Does it get into the sheet successfully? Also, can you please elaborate on the "I'm not able to do the modification manually" part? Can you actually type in a cell or not? If not, what happens when you activate Excel and double click an empty cell? — 41686d6564
Repeating yourself in comments doesn't help anyone. It would be much more helpful if you would edit your question and include the answers to the questions you've been asked in the comments to provide the additional information. — Ken White

Jeremy Thompson Jeremy Thompson · Accepted Answer · 2019-04-10T23:05:28

Why are you referencing the COM DLL? You should be referencing the .Net PIA's - the Primary Interop Assemblies in this location, via the .Net tab in the References window and browse to:

C:\Program Files (x86)\Microsoft Visual Studio [version]\Visual Studio Tools for Office\PIA\Office[version]\Microsoft.Office.Interop.Excel.dll

Only for unit testing do you reference the COM one. See my answer here and how I originally worked it out. It's easy to get confused because in Solution Explorer they are both called the same thing!

If that doesn't work, I originally put this as an answer to save other peoples time being wasted.

Both Ahmed and I cannot reproduce the problem you described with the code you have provided.

See I typed in cells A1 and A2, then I selected an item in the list and clicked the button. Then I select the cell A2 and type Editable.

ps If you can provide the steps to reproduce I'll be happy to take another look at it.

UPDATE:

Your revised steps to reproduce the problem are incorrect, it works for me:

UPDATE 2:

Check if the message pump filters are causing the focus to go to another cell/control:

Excel CustomTaskPane with WebBrowser control - keyboard/focus issues

How to allow excel data modification in manual as well as programmatically?

1 Answers

UPDATE:

UPDATE 2: