Hi
I have a feature request about filling out Excel sheets programmatically.
I have decided that Named Ranges fits the purpose. The end user has a set of keys for fields, that can be filled out in the Excel sheet. In the template Excel sheet a named range can be defined, and all cells within that range will get a value set once the sheet is populated with data.
This works fine for named ranges that are continously, either a singe cell, or a square grid.
But if I use the name manager in Excel to define three different locations that the name refers to I have had some troubles.
My approach was at first to use Workbook.Worksheets.GetNamesRanges() method. But this only returns all the ranges where the cells are in a square grid (or a single cell) - not the one with cells split out over the worksheet.
After reading a bit about, I have found out that I can use the Names collection on the Worksheet, which seems to contains all of them. On the Square ranges I can use GetRange() method to get the range, but that returns null in the split scenario. I then only have the “RefersTo” on the Name object to find the cells - which of course can be done.
Will this be the correct approach - or am I missing a smarter API call that can get me all the cells for a given Name?
I have attached a sample Excel workbook with the different types of names in it.
The named range “MultipleCellRangeTag” is the one causing all the fuzz.
I hope you understand my request, and please correct me if I have misunderstood any terms, e.g. what a Name vs Named Range is.
Best regards
/Anders
For reference, the current solution looks like the code below.
The GetCellsFromRange could be changed to an extension method for neater syntax in the CacheCells method. But it seems to work as supposed.
I end up with a list of “keys” (tags in code), and for each key I have access to all the cells that are in a range marked with that key.
private void CacheCells()
{
_fieldsCache = new Dictionary<string, List>();
foreach (var name in ExcelWorkbook.Workbook.Worksheets.Names)
{
var lowerCasedTag = UnifyTag(name.Text);
foreach (var range in name.GetRanges())
{
foreach(var cell in GetCellsFromRange(range))
{
AddToCache(lowerCasedTag, cell);
{
}
}
}
private IEnumerable GetCellsFromRange(Range range)
{
var cells = new List();
var firstColumn = range.FirstColumn;
var firstRow = range.FirstRow;
var numberOfColumns = range.ColumnCount;
var numberOfRows = range.RowCount;
for (int currentRow = firstRow; currentRow < firstRow + numberOfRows; currentRow++)
{
for (int currentColumn = firstColumn; currentColumn < firstColumn + numberOfColumns; currentColumn++)
{
var cell = range.Worksheet.Cells[currentRow, currentColumn];
cells.Add(cell);
}
}
return cells;
}
private void AddToCache(string pTag, Cell pCell)
{
if (_fieldsCache.ContainsKey(pTag))
_fieldsCache[pTag].Add(pCell);
else
_fieldsCache.Add(pTag, new List() { pCell });
}
Hi,
Thanks for the template file and details.
Well, you may try to use Name.GetRanges() method to get non contiguous (non sequenced cells) named ranges cells areas for your requirements. I have written
a sample code using your template file to access the different cells areas for the underlying range, please refer to it.
e.g
Sample code:
Workbook workbook = new Workbook(@"e:\test2\NamedRangeTagHandler.xlsx");
Name name = workbook.Worksheets.Names["MultipleCellRangeTag"];
Range[] ranges = name.GetRanges();
int frow, fcol;
int rowcount, colcount;
if (ranges != null)
{
for (int i = 0; i < ranges.Length; i++)
{
frow = ranges[i].FirstRow;
fcol = ranges[i].FirstColumn;
string f1 = CellsHelper.CellIndexToName(frow, fcol);
MessageBox.Show(ranges[i].FirstRow + ":" + ranges[i].FirstColumn);
rowcount = ranges[i].RowCount - 1 + ranges[i].FirstRow;
colcount = ranges[i].ColumnCount - 1 + ranges[i].FirstColumn;
string f2 = CellsHelper.CellIndexToName(rowcount, colcount);
MessageBox.Show(f1 + ":" + f2);
}
}
Hope, this helps a bit.
PS. And, your solution looks to me Ok.
Thank you.