C#Excel Interop排序范围，因此空白单元格排在最后，带有数据的单元格在选定范围内在上升 [英] C# Excel Interop sort range so blank cells go last and cells with data go up in selected range

问题描述

我有一个使用C#-4.0和Excel.Interop从.dbf转换的Excel 97-2003 .xls电子表格.数据根据日期根据列D进行排序.

http://www.tiikoni.com/tis/view/?id=af4cf69 [ ^ ]

现在，我需要按G列对所选范围(如图中所示)进行排序，以使空白单元格位于所选范围的底部.

该图像正确显示了该图像，但这仅仅是因为从输入源检索到的数据是以正确的顺序输入的.如果无法按正确的顺序输入数据，则空白单元格可能不会从一开始就位于G列的底部.

这就是我要对每个D日期范围(一天)进行排序的原因.

I have an Excel 97-2003 .xls spreadsheet converted from .dbf using C#-4.0 and Excel.Interop. The data is sorted by date according to column D.

http://www.tiikoni.com/tis/view/?id=af4cf69[^]

Now I would need to sort the selected range (shown in the image), by column G so that blank cells get to be on the bottom of the selected range.

The image shows it correctly, but just because the data retrieved from the input source was entered in the proper order. If data wouldn''t have been entered in the right order, then blank cells might not be at the bottom in column G from the start.

This is what I have, to do that sorting for each D date range (a day).

Range incasariSortRange;
Range sRange;
int startDateRowIndex = 6; // index of row where a D date starts
int endDateRowIndex = 6; // index of row where the same D date ends

public void selectGroupRange()
{
    for (int r = startDateRowIndex; r < rowIndex; r++)
    {
        if (worksheet.Cells[endDateRowIndex, 4].Value == worksheet.Cells[r, 4].Value)
        {
            endDateRowIndex = r;
        }
        else
        {
            incasariSortRange = worksheet.get_Range("B" + startDateRowIndex, "H" + endDateRowIndex);
            sRange = incasariSortRange.get_Range("G" + startDateRowIndex, "G" + endDateRowIndex);

            // Sort the first 'D' date range's row by wether the cells in column 'G' 
            //of that range have any values (to be the first ones) or not (to be the last ones).
            incasariSortRange.Sort(sRange, XlSortOrder.xlAscending,
                Type.Missing, Type.Missing, XlSortOrder.xlAscending,
                Type.Missing, XlSortOrder.xlAscending, XlYesNoGuess.xlNo, Type.Missing,
                Type.Missing, XlSortOrientation.xlSortColumns, XlSortMethod.xlPinYin, XlSortDataOption.xlSortNormal,
                XlSortDataOption.xlSortNormal, XlSortDataOption.xlSortNormal);

            // Set the start and end (date) row indexes to the same so the incasariSortRange will be one row only.
            startDateRowIndex = r; // set the start to a new date
            endDateRowIndex = r; // set the end to the same new date
        }
    }
}

"rowIndex"是电子表格中包含数据的最后一行之后的行的索引号.

但是，如此处所示，它以另一种方式对行进行排序，以使G列中的空白单元格到达所选范围的顶部.

http://www.tiikoni.com/tis/view/?id=ea48320 [ ^ ]

我的第二个问题是，在进行了这种排序之后，如何才能从选定的范围中仅选择G列中的单元格不为空白的行? -以便我可以再次对它们进行排序.

谢谢.

''rowIndex'' is the index number of the row after the last row with data in the spreadsheet.

But as shown here, it sorts the rows the other way, so that blank cells in column G get to the top of the selected range.

http://www.tiikoni.com/tis/view/?id=ea48320[^]

My second question would be, after doing this sorting, how can I select from the selected range only the rows where the cells in column G are not blank? -so that I can sort those again.

Thank you.

推荐答案

以下是基于一个列对工作表进行排序的方法:
http://msdn.microsoft.com/en-us/library/wxks80sb.aspx [ ^ ]

以下是过滤集合的方法:
http://msdn. microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.autofilter%28v=vs.80%29.aspx [ http://social.msdn.microsoft.com /Forums/zh-CN/vsto/thread/45d43ed9-cbfd-4843-a8d6-2918705ec992/ [

Here is how you would sort a sheet based on one column:
http://msdn.microsoft.com/en-us/library/wxks80sb.aspx[^]

And here is how to filter the collection:
http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.autofilter%28v=vs.80%29.aspx[^]

Filter with more than two conditions:
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/45d43ed9-cbfd-4843-a8d6-2918705ec992/[^]

最后，我设法做到了像这样.

数据组"是一组行，这些行在D(D)列中具有相同的值，并按天分组. 类型"并不是真正相关的，只是因为在我的DataSet.Table [0]中有一个列，其中有两个可能的值，并根据行中的值将行写入工作簿中的第一个或第二个工作表.

"Incasari"是我用来排序的列.这样，符号，数字，小写字母，大写字母，然后出现空格或空字符串；并且该列仅包含符号，数字，小写字母和空格/空字符串.

In the end I managed to do it like this.

A "datagroup" is a group of rows which have the same values in column(D) DATE, grouped by days. "type" is not really relevant, it is just because in my DataSet.Table[0]''s I have a column in which there are two possible values, and according to the value in the row, the row will be written in either the first or the second worksheet in the workbook.

The "Incasari" is the column which I use to sort by. Like this, symbols, numbers, lower case letters, uppercase letters, then blank spaces or empty strings come; and that column has only symbols, numbers, lower case letters and blank spaces/ empty strings.

var sortedDataGroup = datagroup.OrderBy(row =>
{
    var wrapper = new DataRowWrapper(row, type);

    if (wrapper.Incasari != null)
        return wrapper.ContCor.ToLower();
    else
        return "A"; // Capital letters are after lower case letters
});

这可能不是解决此排序问题的最佳方法，但我找不到更好的方法.

It might not be the best way to solve this sorting problem, but I could not find better methods.

这篇关于C#Excel Interop排序范围，因此空白单元格排在最后，带有数据的单元格在选定范围内在上升的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！