Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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' 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 8:21am
Edited 20-Aug-12 8:02am
v3
Comments
Kenneth Haugland at 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 at 16-Aug-12 18:58pm
   
I am talking about C#.NET, while using the Excel.Interop and InteropServices.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  
v2

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

  Print Answers RSS
0 OriginalGriff 195
1 ProgramFOX 130
2 Maciej Los 105
3 Sergey Alexandrovich Kryukov 105
4 Afzaal Ahmad Zeeshan 82
0 OriginalGriff 6,564
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 7 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100