重新排列具有完整过滤器的Excel表列 [英] Reordering Excel Table Columns with Filters Intact

问题描述

我通过VSTO 3.0遇到了Excel 2007的问题.在我的项目中，我偶尔需要对列进行打乱.通常，这可以正常工作，但我最近发现，当数据被过滤并且拟合的数据不连续时，会出现问题.

I am running into an issue with Excel 2007 via VSTO 3.0. In my project, I need to shuffle columns around occasionally. Normally, this works fine but I have recently found that an issue arises when the data is filtered and the fitlered data is non-contiguous.

例如，使用以下数据集:

For example, using this data set:

Ohio        Eastern
Kentucky    Eastern
Illinois    Central
California  Pacific
Florida     Eastern

如果第二列上的过滤器不包括Pacific或Central，则以下代码将产生错误.

If the filter on column two excludes either Pacific or Central, the following code produces an error.

ListObject table = FindReportTable();
// Get data to shuffle around.
ListColumn tempColumn = table.ListColumns.Add(missing);
Range range1 = table.ListColumns[column1].Range;
Range range2 = table.ListColumns[column2].Range;
Range tempRange = tempColumn.Range;
// Swap the rows.
range1.Copy(tempRange); // This is where an error is thrown.
range2.Copy(range1);
tempRange.Copy(range2);
tempColumn.Delete();

抛出的错误是System.Runtime.InteropServices.COMException，错误代码为-2146827284，并显示消息该命令不能用于多个选择".

The error thrown is a System.Runtime.InteropServices.COMException with an error code of -2146827284 and message "That command cannot be used on multiple selections".

我尝试了很多事情，但似乎都无法正常工作.如果使用table.AutoFilter.ShowAllData()，则可以继续进行，但没有任何问题，但是我清除了用户的过滤器，并无法像我想象的那样使用table.AutoFilter.ApplyFilter()将其取回.

I have tried many things and none seem to work completely. If I use table.AutoFilter.ShowAllData(), I can continue with no problems, but I have then cleared the user's filters and cannot get them back with table.AutoFilter.ApplyFilter() as I assumed.

由于两个原因，我不能可靠地存储和重新创建过滤器:一个原因是，如果过滤器中的数据集太大，则遍历table.AutoFilter.Filters并存储过滤器的数据可能会导致问题.参考我的示例数据，如果过滤器要从第2列中排除中央"，则尝试检索过滤器的信息会导致互操作错误.其次，要重新创建过滤器，我需要知道过滤器最初要传递到table.Range.AutoFilter(object, object, XlFilterOperator, object, object)方法的哪一行.

I cannot reliably store and recreate the filters for two reasons: one, iterating through table.AutoFilter.Filters and storing the filter's data can cause problems if the dataset in the filter is too large. Referring to my example data, if the filter was to exclude Central from column 2, attempting to retrieve the filter's information causes an interop error. Second, to recreate the filter, I need to know what row the filter was originally on to pass into the table.Range.AutoFilter(object, object, XlFilterOperator, object, object) method.

我已经为此花费了很长时间，但似乎无法在自己的脑海或广阔的互联网上找到解决方案.任何帮助将不胜感激.

I have been banging my head against this for too long now, and can't seem to find a solution in my own head or on the vast internets. Any help would be much appreciated.

推荐答案

我将研究删除并重新创建过滤器.如果遇到问题，则应该解决这些问题.

I would look into removing and recreating the filters. If you're seeing problems, then you should fix those problems.

查看 ListObject.AutoFilter.Filters 集合.

在此处了解更多信息.

这篇关于重新排列具有完整过滤器的Excel表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！