Grayarea has asked for the wisdom of the Perl Monks concerning the following question:

Hi all,

I have a Perl app that generates a spreadsheet in Excell using OLE.

I have done quite a lot of interesting things including adding charts etc, but have become stuck tring to sort data on a worksheet.

Here is what I have been playing with;

    my $start = $offset[$k] + $vert_offset;
    my $end = $offset[$k + 1] + ($vert_offset - 2);

    # Sort data ready for charting

    $sheet4->Rows("$start:$end")->Sort("F5");

[download]

This is supposed to sort all of the rows selected using column F5 as the sort column.

It appears to do nothing!

I have looked at the object browser and used the VBA macro which generated this;

# sort rows
Rows("5:58").Select
Selection.Sort Key1:=Range("F5"), Order1:=2, Header:=2,
    OrderCustom:=1, MatchCase:=False, Orientation:=1,
    DataOption1:=0

[download]

I cannot work out how to implement this!

Any clues would be great :)

Grayarea

20040928 Janitored by Corion: Added code tags

Replies are listed 'Best First'.
Re: Sorting spread sheets in Perl via OLE Excel
by Grygonos (Chaplain) on Sep 28, 2004 at 17:20 UTC

    Ok, I think I see it, if you look at the example vba code in the help file for sort. they give a reference to the sort cell, not just the cell location i.e. 

    $sheet->Rows("5:35")->Sort($sheet->Range("F5"));

    [download]
    I haven't tested this .. but this is my best guess.

    Grygonos
[reply]
[d/l]
      You were spot on, that worked as expected
      Thanks,
      Grayarea.
[reply]
      hi, I need to sort my sheet by first column then by 2nd column then by 3rd column. Do you know how I can do this? I know the topic is 13 years old but I really need this :p Thanks by advance :)
[reply]

        Can you sort the data with Perl before writing to the spreadsheet?


        The way forward always starts with a minimal test.
[reply]

Back to Seekers of Perl Wisdom