This project has moved. For the latest updates, please go here.

Access data in an ExcelNamedRange

Mar 2, 2011 at 10:56 AM

hi!

I just wanted to perform a (imho) very trivial task:
I have data in an named range that I dont know the dimensions of (kinda the reason for a named range) and i just want to access this data in a table formed (i.e. rows/columns) way.
any hints?

using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(ExcelFilePath)))
{
	ExcelNamedRange er = xlPackage.Workbook.Names["Data"];
	// how to access data in a table formed way?

	// throws randomly System.AccessViolationException when debugging and there is no information when a new "row" has begun
	foreach (var x in er)
	{
		Console.WriteLine(x);
	}
}

Coordinator
Mar 3, 2011 at 3:42 PM

Hi,

Not sure why this is not working for you.

Check that your name reference an address in the workbook (names referencing external workbooks, containg formulas or a values are not supported in version 2.8.0.2).

To get the Address/row/column use the Address, Start.Row/Start.Column and End.Row/End.Column properties.

 

Mar 8, 2011 at 11:20 AM

the following code (thx to the hint) works like i want it to - even though i have to loop over the NamedRange and use the Address to get the value out of the worksheet.Cells collection

ExcelNamedRange namedRange = xlPackage.Workbook.Names["Data"];
for (rowIndex = namedRange.Start.Row + 1; rowIndex <= namedRange.End.Row; rowIndex++)
{
	for (columnIndex = namedRange.Start.Column + 1; columnIndex <= namedRange.End.Column; columnIndex++)
	{
		cellValue = worksheet.Cells[rowIndex, columnIndex].Value;
	}
}

thx!
Sep 30, 2011 at 12:09 PM

Hi Jan,

Please let me know when you get a fix for this issue, I have made a separate post on the same issue at : http://epplus.codeplex.com/discussions/274360

I am using the latest build, and the Named Range are randomly stripped off values (eg. I have 2012 Naed range, but only 1876 are populated in Names in alphabetical order).

Some of the fields that go missing are of type dropdown (using DataValidation List). and some have default value set (example Company Name field reads (Enter Company Name))

 

Please Suggest.