none
How access a excel cell by its name? RRS feed

  • Question

  • at the moment I access as follow:

      sheet.Cells[5, "D"] = "sfsfsf"

    However, I wanna give a name to a cell for example <g class="gr_ gr_133 gr-alert gr_gramm gr_run_anim Grammar multiReplace" data-gr-id="133" id="133">cat</g> and change its value as below (below is not working, what's the right way of doing it?)

      sheet.Cells["cat"] = "fsfsfs"
    How can I do so?

    • Changed type Andy ONeill Friday, June 10, 2016 5:45 PM it's a question
    • Moved by DotNet Wang Monday, June 13, 2016 2:42 AM excel dev related
    Friday, June 10, 2016 2:08 PM

Answers

  • Viorel's approach didn't work for me, maybe I misunderstood something.

    The link Karen provided uses the get_Range method, which works.

    For example:

            private void button1_Click(object sender, EventArgs e)
            {
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                excelApp.Workbooks.Add();
                // single worksheet
                Microsoft.Office.Interop.Excel._Worksheet workSheet = excelApp.ActiveSheet;
                excelApp.Visible = true;
    
                Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1,5];
                rng.Value = "sfsfsf";
                rng.Name = "cat";
    
                workSheet.get_Range("cat").Value = "abc";

    I have a reference to Microsoft.Office.Interop.Excel in my project.


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    Friday, June 10, 2016 6:05 PM

All replies

  • Hello,

    If you were to have a named range then this would work. Otherwise if looking for a distinct value then you can identify a range, in this case a single cell via Range object's Find method which in turn returns a range that could be null if the value was not located, if lactated then access value via the Cell.Value property. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 10, 2016 2:39 PM
  • Thanks for the reply. 

    Tools don't exist in 

    Microsoft.Office.Tools

    I tried to add the reference to "Microsoft Office 12.0 Object Libray", but it seems that doesn't exist either.

    Friday, June 10, 2016 3:36 PM
  • Please change the type of your post to "Question". It is currently "General discussion".

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 10, 2016 4:36 PM
  • Try this too:

    sheet.Range["cat"] = "fsfsfs";
    
    

    Friday, June 10, 2016 5:26 PM
  • Viorel's approach didn't work for me, maybe I misunderstood something.

    The link Karen provided uses the get_Range method, which works.

    For example:

            private void button1_Click(object sender, EventArgs e)
            {
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                excelApp.Workbooks.Add();
                // single worksheet
                Microsoft.Office.Interop.Excel._Worksheet workSheet = excelApp.ActiveSheet;
                excelApp.Visible = true;
    
                Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1,5];
                rng.Value = "sfsfsf";
                rng.Name = "cat";
    
                workSheet.get_Range("cat").Value = "abc";

    I have a reference to Microsoft.Office.Interop.Excel in my project.


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    Friday, June 10, 2016 6:05 PM
  • Please see the link below.  Also, check out all the links at the bottom of that page.

    http://csharp.net-informations.com/excel/csharp-read-excel.htm


    MY BOOK

    Saturday, June 11, 2016 2:38 PM
  • Hi,

    You can try this free library:http://freenetexcel.codeplex.com/ and  the following code to rename a cell, access the cell by its name and reset its value:

    Workbook workbook = new Workbook();
    workbook.LoadFromFile(@"E:\Sample.xlsx");
    Worksheet sheet = workbook.Worksheets[0];
    //Rename cell "A1" to "Cat"
    sheet.Names.Add("Cat", sheet[String.Format("A1")]);
    //Access cell by name
    CellRange range = sheet.Range["Cat"];
    //Reset cell value
    range.Value = "fsfsfs";
    workbook.SaveToFile("UsingCellName.xlsx", FileFormat.Version2010); System.Diagnostics.Process.Start("UsingCellName.xlsx");
    


    Kind Regards,





    Monday, June 13, 2016 2:04 AM
  • Hi Bhupinder Singh (Mech. Eng.),

    First I you need to add reference of Excel interop in your project.

    For that Right click on References in solution Explorer.

    then click on Add new Reference.

    Then find "Microsoft.Office.Interop.Excel" and checked on that.

    Then you need to import it in your code by following you can do this.

    using Excel = Microsoft.Office.Interop.Excel;

    then you can use access it in your code.

    Refer the link below to use the named range in c# code.

    Create and Apply Excel Named Range in C#

    if you think that any of the suggestion given here helped you to solve your issue then I would recommend you to mark that suggestion as an Answer.

    if you still facing a problem regarding that then please let us know so that we can try to provide you further Assistance.

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 14, 2016 1:38 AM
    Moderator
  • You can check out the following:

    https://msdn.microsoft.com/en-us/library/7zte17ya.aspx

    Also here is an alternative that you can also check out:

    ExcelFile ef = ExcelFile.Load("Sample.xlsx");
    ExcelWorksheet ws = ef.Worksheets.ActiveWorksheet;
                
    // Set NamedRange.
    ws.NamedRanges.Add("cat", ws.Cells.GetSubrange("D5"));
    
    // Get NamedRange and set its value.
    ws.NamedRanges["cat"].Range.Value = "fsfsfs";
    This code uses the following C# library for excel.
    Monday, July 11, 2016 8:22 AM