C# Excel Interop sort range so blank cells go last and cells with data go up in selected range

Question

0.00/5 (No votes)

See more:

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).

C#

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' 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.

Posted 16-Aug-12 7:21am

Zsombi55

Updated 20-Aug-12 7:02am

v3

Add a Solution

Comments

Kenneth Haugland 16-Aug-12 15:21pm

I dont understand were you are doing this? Are we talking about VB.NET or a VBA inside Excel. It looks like a pure VB.NET program, and if it is i think its simple task to do.. :)

Zsombi55 16-Aug-12 18:58pm

I am talking about C#.NET, while using the Excel.Interop and InteropServices.

2 solutions

Solution 1

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/[^]

Posted 16-Aug-12 10:35am

Kenneth Haugland

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Zsombi55 · Accepted Answer · 2012-09-07T01:02:00

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.

C#

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.