C# .NET - Excel ListObject 在数据绑定上自动调整大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
.NET - Excel ListObject autosizing on databind
提问by goric
I'm developing an Excel 2007 add-in using Visual Studio Tools for Office (2008). I have one sheet with several ListObjects on it, which are being bound to datatables on startup. When they are bound, they autosize correctly.
我正在使用 Visual Studio Tools for Office (2008) 开发 Excel 2007 加载项。我有一张纸，上面有几个 ListObjects，它们在启动时绑定到数据表。当它们被绑定时，它们会正确地自动调整大小。
The problem comes when they are re-bound. I have a custom button on the ribbon bar which goes back out to the database and retrieves different information based on some criteria that the user inputs. This new data comes back and is re-bound to the ListObjects - however, this time they are not resized and I get an exception:
当它们被重新绑定时，问题就来了。我在功能区栏上有一个自定义按钮，它返回到数据库并根据用户输入的某些条件检索不同的信息。这个新数据回来并重新绑定到 ListObjects - 但是，这次它们没有调整大小，我得到一个例外：
ListObject cannot be bound because it cannot be resized to fit the data. The ListObject failed to add new rows. This can be caused because of inability to move objects below of the list object.
Inner exception: "Insert method of Range class failed"
Reason: Microsoft.Office.Tools.Excel.FailureReason.CouldNotResizeListObject
无法绑定 ListObject，因为无法调整其大小以适合数据。ListObject 添加新行失败。这可能是由于无法将对象移动到列表对象下方造成的。
内部异常：“Range 类的插入方法失败”
原因：Microsoft.Office.Tools.Excel.FailureReason.CouldNotResizeListObject
I was not able to find anything very meaningful on this error on Google or MSDN. I have been trying to figure this out for a while, but to no avail.
我无法在 Google 或 MSDN 上找到有关此错误的任何非常有意义的信息。我一直试图弄清楚这一点，但无济于事。
Basic code structure:
基本代码结构：
//at startup
DataTable tbl = //get from database
listObj1.SetDataBinding(tbl);
DataTable tbl2 = //get from database
listObj2.SetDataBinding(tbl2);
//in buttonClick event handler
DataTable tbl = //get different info from database
//have tried with and without unbinding old source
listObj1.SetDataBinding(tbl); <-- exception here
DataTable tbl2 = //get different info from database
listObj2.SetDataBinding(tbl2);
Note that this exception occurs even when the ListObject is shrinking, and not only when it grows.
请注意，即使在 ListObject 缩小时也会发生此异常，而不仅仅是在它增长时。
采纳答案by goric
If anyone else is having this problem, I have found the cause of this exception. ListObjects will automatically re-size on binding, as long as they do not affect any other objects on the sheet. Keep in mind that ListObjects can only affect the Ranges which they wrap around.
如果其他人遇到这个问题，我已经找到了这个异常的原因。ListObjects 将在绑定时自动重新调整大小，只要它们不影响工作表上的任何其他对象。请记住，ListObjects 只能影响它们环绕的范围。
In my case, the list object which was above the other one had fewer columns than the one below it. Let's say the top ListObject had 2 columns, and the bottom ListObject had 3 columns. When the top ListObject changed its number of rows, it had no ability to make any changes to the third column since it wasn't in it's underlying Range. This means that it couldn't shift any cells in the third column, and so the second ListObject couldn't be properly moved, resulting in my exception above.
就我而言，位于另一个上方的列表对象的列数少于其下方的列。假设顶部 ListObject 有 2 列，底部 ListObject 有 3 列。当顶部 ListObject 更改其行数时，它无法对第三列进行任何更改，因为它不在其底层 Range 中。这意味着它无法移动第三列中的任何单元格，因此无法正确移动第二个 ListObject，从而导致上述异常。
Changing the positions of the ListObjects to place the wider one above the smaller one works fine. Following the logic above, this now means that the wider ListObject can shift all of the columns of the second ListObject, and since there is nothing below the smaller one it can also shift any cells necessary. The reason I wasn't having any trouble on the initial binding is that both ListObjects were a single cell.
更改 ListObjects 的位置以将较宽的放置在较小的上方，效果很好。按照上面的逻辑，这现在意味着更宽的 ListObject 可以移动第二个 ListObject 的所有列，并且由于在较小的列下方没有任何内容，因此它也可以移动任何必要的单元格。我在初始绑定上没有任何问题的原因是两个 ListObjects 都是一个单元格。
Since this is not optimal in my case, I will probably use empty columns or try to play around with invisible columns if that's possible, but at least the cause is now clear.
由于这在我的情况下不是最佳的，如果可能的话，我可能会使用空列或尝试使用不可见的列，但至少原因现在已经清楚了。
回答by Guy
Just an idea of something to try to see if it gives you more info: Try resizes the list object before the exception line and see if that also throws an exception. If not, try and resize the range object to the new size of the DataTable.
只是想看看它是否为您提供更多信息的想法：尝试在异常行之前调整列表对象的大小，看看是否也会引发异常。如果没有，请尝试将范围对象的大小调整为 DataTable 的新大小。
You say that this happens when the ListObject shrinks and grows. Does it also happen if the ListObject remains the same size?
你说当 ListObject 缩小和增长时会发生这种情况。如果 ListObject 保持相同的大小，也会发生这种情况吗？
回答by goric
I've got a similar issue with refreshign multiple listobjects. We are setting each listObject.DataSource = null, then rebinding starting at the bottom listobject and working our way up instead of the top down.
我在刷新多个列表对象时遇到了类似的问题。我们正在设置每个 listObject.DataSource = null，然后从底部列表对象开始重新绑定，并按照我们的方式向上而不是自上而下。
