Excel的互操作 - 如何更改命名范围 [英] Excel Interop - How to change named range
问题描述
我有一个模板的Excel文件，我数据填充到从SQL Server OLEDB接口。该文件包含引用数据集的一些数据透视表由数据库被填充。
I have a template excel file that I fill data into from SQL Server OLEDB connector. The file contains some pivot tables that refer to the dataset being filled in by the database.
目前我要做的就是，我用数据选择表中的所有行！ $ A：$ K的范围。这带来了在数据透视表正在显示空值的问题。
Currently what I do is that I select all rows in sheet by using "Data!$A:$K" range. This brings a problem with blank values being shown in the pivot table.
我想要做的是在数据集中创建一个名为表，并参照数据透视表到（再加上我获得名称表带来一些其他的好处）。
What I wanted to do is to create a named table over the dataset and refer the pivot tables to that (plus I gain some other advantages that names tables bring).
自然是没有设置的行数，所以我想找到一种方法来命名的区域范围设定为只有实际值。
The number of rows is naturally not set so I wanted to find a way to set the named range scope to the actual values only.
我在使用Excel互操作和C＃为，我无法找到一个方法来改变范围。我只得到尽可能：
I'm using Excel Interop and C# for that and I can't find a way to change the range. I only got as far as:
oRng = oSheet.get_Range("Transactions");
它选择的命名范围。但我怎么改变哪些细胞属于它？
Which selects the named range. But how do I change which cells belong to it?
还是有可能是一个更好的解决办法，我应该追求什么？
Or is there perhaps a better solution I should pursue?
修改
动态范围是答案！
我解决了这个感谢到@TimWilliams回复：
I solved this thanks to @TimWilliams reply:
在模板中使用动态范围的 http://ozgrid.com/Excel/DynamicRanges.htm
"Use a dynamic range in your template: http://ozgrid.com/Excel/DynamicRanges.htm"
我觉得动态范围更好的描述如下：的 http://www.contextures.com/xlpivot01.html
I feel the dynamic ranges are better described here: http://www.contextures.com/xlpivot01.html
我遇到的，因为它要求至少还需要2行来操作我不能使用的范围内透视表一个轻微的问题 - 模板文件只有列标题。我添加了一个随机字符串，第二排和数据透视表的第一个单元格接受了。
I encountered a slight issue that I couldn't use the range in pivot table because it demanded it needs at least 2 rows to operate - the template file only has column headings. I added a random string to 1st cell of 2nd row and pivot table accepted that.
后来我不得不使用C＃代码以删除该行。
Afterwards I had to remove that row using c# code.
感谢你们的帮助。
推荐答案
通过执行以下操作，您将创建一个名为范围在oSheet（交易）在单元格A1盯着，并在单元格C3 finising
By doing the following, you will create a named range (Transactions) on the oSheet staring at cell A1 and finising at cell C3
Range namedRange= oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[3, 3]];
namedRange.Name = "Transactions";
如果该名工作簿中已经存在它会被取代，如果没有它将会被创建。
If the name already exist in the workbook it will be replace, if not it will be created.
这篇关于Excel的互操作 - 如何更改命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
