Excel + Perl + Sort Columns

[mikedaruke]

Hi

I wrote a script to create an excel spreadsheet off data I have. I can't find out how to sort a column using perl. I know I can sort the info before I write it to the excel file but i need to sort it after. I am using, use Spreadsheet::WriteExcel;

Any help? Should I use something else? I found by searching how to do it with VB but it doesn't work when I do it in perl. Thanks.

 

[varakal]

I dont think there is some sort functionality with Spreadsheet::WriteExcel.

 

[dmazzini]

From the internet:

The Spreadsheet::WriteExcel module can be used to create
a cross-platform Excel binary file. Multiple worksheets can be added
to a workbook and formatting can be applied to cells. Text, numbers,
formulas, hyperlinks and images can be written to the cells.

The Excel file produced by this module is compatible with Excel 5, 95,
97, 2000 and 2002, also Gnumeric and OpenOffice.

This module cannot be used to write to an existing Excel file.........

It means that if you have already created an excel file with this module and you want to re-write (sort it) you will need to create another excel file.

cheers







dmazzini
GSM System and Telecomm Consultant

 

[ishnid]

What format is your data in before you use Spreadsheet::WriteExcel?

 

[mikedaruke]

I read in 2 text files. 1.txt - has every item in it with information that goes with it. 2.txt- that is just a list of items. I read both of them in, if the items from the list 2 are in the list 1 then I print the information to an excel spread sheet. But I want to sort some of those columns.


dmazzini - Thats fine, I can always write another excel file, if I can sort the columns some how.

I guess I could sort the arrays first, my problem is I read the file line by line, each line containing 4 fields. So I have 4 arrays. Should I just have one? To print the 6th element, I do, array1[5] array2[5] etc.
Any help?

 

[rharsh]

How about using something like a hash of arrays. I have no idea what your data really looks like, so I took a (probably poor) guess. Anyway, this might give you a bit of a direction to go in.

my %HoA;
while (<DATA>) {
    my @temp = &trim(split(';', $_));
    $HoA{$temp[0]} = [@temp];
}

my @sorted_keys = sort {&sortFields2and0} keys %HoA;
foreach (@sorted_keys) {
    # Being lazy and setting some variables for output.
    local $, = " ; ";
    local $\ = "\n";
    print @{$HoA{$_}};
}

sub sortFields2and0 {
    # Sorts by date field, then by ID field.
    $HoA{$a}[2] <=> $HoA{$b}[2] ||
    $HoA{$a}[0] <=> $HoA{$b}[0]
}

sub trim {
	my @out= @_;
	for (@out) {
		s/^\s+//;
		s/\s+$//;
	}
	return wantarray ? @out : $out[0];
}

#ID ; Name ; YYMMDD ; Description
__DATA__
987 ; Widget 1 ; 040201 ; Wow
567 ; Cog 1 ; 050101 ; Cool Beans
123 ; Widget 2 ; 040101 ; Narf
234 ; Cog 2 ; 050101 ; This one sucks

 

[mikedaruke]

Thank you. Here is what my data looks like.

ID, POS, Where
123,4,5
321,4,5
456,3,5
322,4,5
459,4,3
450,2,1

The other list is just ID

ID
321
456
450
459

So I want to take that list, check to see if those IDS are in the first list, if so print out the ID, POS, Where

Sorted by POS the Where. Ascending.

So out put should be

450,2,1
456,3,5
459,4,3
321,4,5

Thanks! Please help anyone!

 

[rharsh]

I'm assuming IDs are unique, yeah? There are some comments about what will need to be fixed in there. To shorten the code, I omitted that trim function, but you'll probably still want it.

my (%output, %HoA);

# This will need to changed to read in the data from
# the second list/file.
foreach (qw/321 456 450 459/) { $output{$_}++; }

# This will need to changed to read in the data from
# the first list/file.
while (<DATA>) {
    my @temp = &trim(split(',', $_));
    $HoA{$temp[0]} = [@temp];
}

my @sorted_keys = sort {&sortFields} keys %HoA;

foreach (@sorted_keys) {
    # Being lazy and setting some variables for output.
    # Rewrite this block with your excel-writing code.
    local $, = ",";
    local $\ = "\n";
    if ($output{$_}) {
        print @{$HoA{$_}};
    }
}

sub sortFields {
    # Sorted by POS the Where
    $HoA{$a}[1] <=> $HoA{$b}[1] ||
    $HoA{$a}[2] <=> $HoA{$b}[2]
}

# ID, POS, Where
__DATA__
450,2,1
456,3,5
459,4,3
321,4,5
999,8,7
888,7,6

 

[mikedaruke]

Code is nice! Only problem is, the sort. It sorts on the second column, but not the third. Should there be an && instead of ||?

I need it to sort by the 2 column, then the 3rd. Still keeping the second column sorted? Makes sense? So, if column 2 is the same, then if goes and sorts by column 3.

123, 3, 4
345, 3, 5


Not:

345, 3, 5
123, 3, 4

 

[mikedaruke]

I am sorry I got it to work. The way I was reading it in was messed up. Another question. Where you print the hash, how do I print the seperate elements of it. Because I need to put the values in there own columns in excel.

So I need to print the first line.

123 in excel
POS in excel
where in excel.

So I think I need to do them separetly since I need to define the colunmns and cells. Make sense? Thanks

 

[rharsh]

You might want to read up a bit on references and data structures. Take a look at perldoc perlref.

In the mean time, to print all the IDs, you could use code similar to:

foreach (@sorted_keys) {
    if ($output{$_}) {
        print ${$HoA{$_}}[0];  # print ID
        print ${$HoA{$_}}[1];  # print POS
        print ${$HoA{$_}}[2];  # print Where
    }
}