This project has moved and is read-only. For the latest updates, please go here.

Creating a range with absolute address

Jul 5, 2011 at 12:56 AM

I need to create a range with absolute address, i.e. $A$1:$A3 instead of A1:A3. I was able to get this done as follows:

    sheet.Range(sheet.Cell(a, b).Address.ToStringFixed(), sheet.Cell(c, d).Address.ToStringFixed());

But that seems more like a hack than an elegant solution. Perhaps support for absolute addresses should be improved in ClosedXML?

Just as one suggestion, how about being able to do something like this:

    sheet.Range(a, b, c, d, true);

Where fifth argument indicates that absolute (as opposed to relative) addressing is required.

 

Now, let me tell you how and why I ran into this. And perhaps I am doing it wrong.. I am creating list-validation of a range as follows:

    var listRange = sheet.Range(...);

    var dataRange = sheet.Range(...);

    valueRange.DataValidation.List(listRange);

This code has the following problem. Let's say, my listRange is A1:A3 and my dataRange is B1:B3. Everything works great for cell B1. It is validated by the A1:A3 range. And I would expect every single cell in B1:B3 range to be validated against the same A1:A3 range. However, supposedly because of the relative address of listRange, B2 is validated against A2:A4, B3 is validated against A3:A5, and so on.

So, perhaps I am not doing my list validation properly?

Jul 5, 2011 at 1:23 AM

I don't know, the following works just fine...

            var wb = new XLWorkbook();

            var ws = wb.Worksheets.Add("Sheet1");

            ws.Cell("A1").Value = "Item1";
            ws.Cell("A2").Value = "Item2";
            ws.Cell("A3").Value = "Item3";
            
            var listRange = ws.Range("A1:A3");
            var dataRange = ws.Range("B1:B3");

            dataRange.DataValidation.List(listRange);

Jul 5, 2011 at 1:58 PM
Edited Jul 6, 2011 at 12:36 PM

Do you ever sleep? :D

 

And no, this doesn’t work for me. Attached is the resulting spreadsheet. And here are validation lists for B1, B2, and B3:

 

 

 

Notice that the validation list is growing shorter as I move down from B1 to B3. And validation formulas from B1 to B3 change as follows: =A1:A3, =A2:A4. =A3:A5.

Jul 5, 2011 at 4:05 PM

Sleep is overrated...

What's your locale?

Jul 6, 2011 at 7:32 AM
Edited Jul 6, 2011 at 12:37 PM

In Japan now on a business trip. But I live in Europe now.

Jul 6, 2011 at 9:23 AM

Try:

....
var
listRange = ws.Range("A$1:A$3"); var dataRange = ws.Range("B1:B3");
dataRange.DataValidation.List(listRange);
....
Jul 6, 2011 at 12:35 PM
Edited Jul 6, 2011 at 12:36 PM

I am working with numeric ranges (integers for row and column numbers), so A1 style is not very suitable. But still, I believe I tried something like that and it didn’t work either.

Jul 6, 2011 at 2:49 PM
Edited Jul 6, 2011 at 2:49 PM

To make it clear if you do:

var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Sheet1");
ws.Cell("A1").Value = "Item1";
ws.Cell("A2").Value = "Item2";
ws.Cell("A3").Value = "Item3";
var listRange = ws.Range("A$1:A$3");
var dataRange = ws.Range("B1:B3");
dataRange.DataValidation.List(listRange);
wb.SaveAs("test.xlsx");

What exactly wrong?B1, B2 and B3 have all 3 Items in list.

Jul 6, 2011 at 4:25 PM

It's a bug. For some reason it doesn't work when you specify the range with numbers (it does work with a string).

I'll create an issue for it.

Jul 8, 2011 at 2:37 AM

I have no idea what's going on but I can't reproduce it now (I swear I got the anomaly yesterday). Do you have a piece of code that reproduces it?

Jul 8, 2011 at 5:59 AM

Got it! It isn't how you define your range. It's Excel 2010! On Excel 2007 it works as expected but on 2010 it gives the error. I'll work on it.

Jul 8, 2011 at 11:31 PM

Fixed.