Hello,

By putting some snippets together I am able to create a pivottable using win32::ole. In the pivottable I need to sort the data columns in Descending order (it's days) Could someone help me out?

the .csv used:

Month,Day of Month,Privileges,Count
5,20,user1 : su > root,2
5,20,user2 : su > root,1
5,21,user3 : su > boats,1
5,22,user3 : su > cars,5
5,22,user1 : su > bikes,31
5,23,user4 : su > root,1
[download]

Code tied together:

use Win32::OLE;

*error = *Win32::OLE::LastError;

# some variables
my $excelfile = 'test.csv';

# Start Excel and make it visible
$Excel = Win32::OLE->new('Excel.Application');
$Excel->{Visible} = 1;

# Turn off alerts
#$Excel->{DisplayAlerts}=0;

# Create a new workbook
# $xlBook = $xlApp->Workbooks->Add;
# Save workbook
# $xlBook->SaveAs($excelfile); Good habit when working with OLE, save.


# Or open an existing file
#my $workbook = $Excel->Workbooks->Open($excelfile);
my $workbook = $Excel->Workbooks->Open('D:\tools\cygwin\home\me\test.c
+sv');

# create a new worksheet
my $worksheet = $workbook->WorkSheets(1);
$worksheet->{Name} = "Sheet1";

$workbook->PivotCaches->Add( {
        SourceType => 1,
        SourceData => 'D:\tools\cygwin\home\me\test.csv!R1C1:R101C4'
} )->CreatePivotTable( {
        TableDestination => 'Sheet1!R1C6',
        TableName => 'PivotTable1',
        DefaultVersion => 1 } );

$workbook->ActiveSheet->PivotTables('PivotTable1')->PivotFields('Privi
+lege')->{Orientation} = 1; # xlRowField
$workbook->ActiveSheet->PivotTables('PivotTable1')->PivotFields('Privi
+lege')->{Position}    = 1;

$workbook->ActiveSheet->PivotTables('PivotTable1')->AddDataField($work
+book->ActiveSheet->PivotTables('PivotTable1')->PivotFields('Count'));
die error() if error();

#$workbook->ActiveSheet->Range("G2")->Select;
$workbook->ActiveSheet->PivotTables('PivotTable1')->PivotFields('Day o
+f Month')->{AutoSort} = xlDescending, 'Day of Month' ;
die error() if error();

$workbook->ActiveSheet->PivotTables('PivotTable1')->PivotFields('Day o
+f Month')->{Orientation} = 2; # xlColumnField
die error() if error();
$workbook->ActiveSheet->PivotTables('PivotTable1')->PivotFields('Day o
+f Month')->{Position}    = 1;
die error() if error();
$workbook->ActiveSheet->Columns("g:r")->{ColumnWidth}    = 3; # 3
die error() if error();

#$workbook->ActiveSheet->PivotTables('PivotTable1')->PivotFields('Day 
+of Month'), AutoSort, xlDescending, 'Day of Month';
#die error() if error();

# clean up
$workbook->SaveAs( $excelfile );

die error() if error();

$workbook->Close();

die error() if error();

exit 0;


print "All done.";
[download]

And it should look like below in the end:

Sum of Aantal    Column Labels                                        
+        
Row Labels    31    30    29    28    27    26    25    24    23    22
+    21    20    Grand Total
user1 : su > root                    2                            2   
+ 4
user2 : su > root        2                                        1   
+ 3
user3 : su > root                        3        1                   
+ 4
user4 : su > root    5    1        1            4    8    2           
+     21
[download]

Thanks a lot! bjhs

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.