Create a named range

Oct 1, 2010 at 5:46 PM

Hi,

I am trying to create a named range in a spreadsheet I am creating. However there does not seem to be a way to add the named range - although Workbook.Names.Add is public, it requires an ExcelRangeBase object, which does not seem to have a public constructor, so I am unable to create it.

Am I doing this right, or is there another way?  Apologies if I just haven't looked in the right place,

Thanks,

Miles

Coordinator
Oct 1, 2010 at 5:54 PM

Use the Cells property like...

Workbook.Names.Add("Name",WorkSheet.Cells["A1:A2"]);

Jan

Oct 1, 2010 at 6:07 PM

Brilliant - works great.

Thanks for your speedy response, now I can knock this off before the weekend!

Oct 4, 2010 at 2:58 PM

Hi,

Is there a change between 2.7.0.1 and trunk that would cause this to only work on the trunk?  I can't see anything in the changelog about it, but my defined name is missing from a test sheet if I compile using the 2.7.0.1 binaries, but not if I compile using source from the trunk.

If so, would it be possible to release an updated version?

Here is the test code that I use to exercise the problem:

using System;
using System.IO;
using OfficeOpenXml;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main()
        {
            var newFile = new FileInfo(System.IO.Path.GetTempFileName());
            int nameCount;

            using (ExcelPackage pck = new ExcelPackage())
            {
                var ws = pck.Workbook.Worksheets.Add("Content 1, scggdfg");
                ws.Cells["B1"].Value = "Name";
                ws.Cells["C1"].Value = "Size";
                ws.Cells["D1"].Value = "Created";
                ws.Cells["E1"].Value = "Last modified";
                ws.Cells["B1:E1"].Style.Font.Bold = true;

                var range = ExcelRange.GetAddress(1, 1, 1, 3, false);
                pck.Workbook.Names.Add("booger", ws.Cells[range]);
                nameCount = pck.Workbook.Names.Count;

                // create a temp file
                pck.SaveAs(newFile);
            }

            using (var pck2 = new ExcelPackage(newFile))
            {
                if (pck2.Workbook.Names.Count != nameCount)
                {
                    throw new Exception("NAMES LOST AFTER SAVING");
                }
            }


        }
    }
}

 

 

Coordinator
Oct 5, 2010 at 6:53 AM

Yes, the bug has only been fixed in the trunk. But its one line of code. I can check it in to the 2.7 tag next time i update the code.

You can change it yourself in the tag if you want to...

Change the > to == in ExistsNames in ExcelWorkbook...

        private bool ExistsNames()
        {
            if (_names.Count == 0)

 I guess i should do all bug fixes in the tag as well but its a lot of extra work and I really dont have all that much time for this project. Anyway, I hope i can release a new version in a month or two.
/Jan
Oct 5, 2010 at 2:33 PM

Thanks,

Sorry for the hassle, because of the policies here and that other production assemblies here use epplus, I can't get my work through QA unless the change is an "official" update.

So I am happy to make the change in the tag, but I don't have access at the moment, are you able to add me?

EPPlus is great BTW, thanks for all your help

Miles

Coordinator
Oct 6, 2010 at 5:45 AM

If you want to use the 2.7 version a workaround is to add your name as a worksheet-local name (use the Worksheet.Names collection instead) or if you need a workbook-global add the workbook name and then add add a dummy worksheet-local name in any worksheet.

I have already made the change in the code but I have some unfinished work to complete before I can check it in.

Jan

Aug 30, 2013 at 12:02 PM
Edited Aug 30, 2013 at 12:32 PM
Hi Jan,
I'm using 3.1 and still appear to be having issues with Names collections saving. I've tried adding the named range at the workbook and worksheet level, and still seem to wind up with a worksheet that is missing the range once I re-open the workbook. Is there a specific way this needs to be done?
ExcelRange rng= sht1.Cells["F1:F100"];
for (int iwks = 1; iwks <= book.Worksheets.Count; iwks++)
{
    ExcelWorksheet sht = book.Worksheets[iwks];                                
    sht.Names.Add("NAME", rng);                            
}
rng.Dispose();
Thanks Jan for a great project!

Dean