Generated: Tue Feb 2 17:54:22 2010 from autoexcel06.pl 2006/06/19 7.9 KB.
#!/Perl # # Can I find what AddIns are loaded in Excel? # Some information # from : http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel._application.aspx # use Win32::OLE qw(in valof with); use Win32::OLE::Const 'Microsoft Excel'; use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG); my $lgid = MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT); # Save workbook to file - ensure path exists my $FileBase = 'autoexcel06'; my $OutPath = 'c:\tmp'; my $OutBase = "$OutPath\\$FileBase"; my $Filename = $OutBase.'.xls'; my @Bars2 = (); # just for LOG FILE ouput ... my $out_lines = 0; # for DEBUG only ;=)) my ($LOG); my $write_log = 0; my $verb = 1; # increase to 9 to see more output my $outfile = "temp.$0.txt"; # note name of perl file used as base if ( open( $LOG, ">$outfile" ) ) { $write_log = 1; # we have a LOG file } else { $write_log = 0; prt( "WARNING: Unable to open $outfile LOG ...\n" ); } fill_bars2(); # just a set of arbitrary data $Win32::OLE::LCID = MAKELCID($lgid); $Win32::OLE::Warn = 3; prt("Loading Excel Application ...\n"); my $Excel = Win32::OLE->new('Excel.Application', 'Quit') or mydie( "Error: Failed to load excel ...\n" ); out_version(); ####################################################### ### Add data, and chart data ####################################################### $Excel->{SheetsInNewWorkbook} = 1; prt( "Adding a workbook ...\n" ); my $Book = $Excel->Workbooks->Add; prt( "Getting and naming the sheet ...\n" ); my $Sheet = $Book->Worksheets(1); $Sheet->{Name} = 'Test'; # Insert column titles prt( "Insert column titles ... bold font ...\n" ); my $Range = $Sheet->Range("A1:E1"); $Range->{Value} = [qw(Time Open High Low Close)]; $Range->Font->{Bold} = 1; prt( "Formatting ranges ...\n" ); $Sheet->Columns("A:A")->{NumberFormat} = "h:mm"; # Open/High/Low/Close to be displayed in 32nds $Sheet->Columns("B:E")->{NumberFormat} = "# ?/32"; # Add 15 minute data to spreadsheet prt( "Adding ".scalar @Bars2." columns of data ...\n"); $Range = $Sheet->Range(sprintf "A2:E%d", 2+$#Bars2); $Range->{Value} = \@Bars2; # Create candle stick chart as new object on worksheet prt( "Selecting range for chart creation ...\n" ); $Sheet->Range("A:E")->Select; add_chart(); if ( -f $Filename) { prt( "Removing existing file $Filename ...\n" ); unlink $Filename; } prt( "Save the sheet as $Filename ...\n" ); $Book->SaveAs($Filename); prt( "Close the work book ..\n" ); $Book->Close; prt( "All done ...\n" ); log_close(); exit(0); sub out_version { prt( "Excel Version: [" . $Excel->Version() . "]\n" ); prt( "Excel Build: [" . $Excel->Build() . "]\n" ); prt( "Excel Caption: [" . $Excel->Caption() . "]\n" ); my $Collection = $Excel->AddIns(); foreach my $addin (in $Collection) { ##prt( "Got add-in $addin ... " ); prt( "Title[" . $addin->Title() . "] " ); prt( "Name[" . $addin->Name() . "] " ); #if (defined $addin->CLSID) { # prt( "CLSID[" . $addin->CLSID() . "] " ); #} if ($addin->Installed()) { prt( "INSTALLED" ); } else { prt( "NOT INSTALLED" ); } prt("\n"); } } sub add_chart { prt( "Add the chart ... as xlStockOHLC chart ...\n" ); my $Chart = $Book->Charts->Add; $Chart->{ChartType} = xlStockOHLC; $Chart->Location(xlLocationAsObject, $Sheet->{Name}); # Excel bug: the old $Chart is now invalid! $Chart = $Excel->ActiveChart; # Add title, remove legend prt( "Remove legend, and add title ...\n" ); with($Chart, HasLegend => 0, HasTitle => 1); $Chart->ChartTitle->Characters->{Text} = "US Corn"; # Set up daily statistics prt( "Set up the statistics ..." ); $Open = $Bars2[0][1]; # get first Open $High = $Sheet->Evaluate("MAX(C:C)"); $Low = $Sheet->Evaluate("MIN(D:D)"); $Close = $Bars2[$#Bars2][4]; # get last Close prt( "Open[$Open] High[$High] Low[$Low] Close[$Close] ...\n" ); prt( "Change tickmark spacing from decimal to fractional ...\n" ); with($Chart->Axes(xlValue), HasMajorGridlines => 1, HasMinorGridlines => 1, MajorUnit => 1/8, MinorUnit => 1/16, MinimumScale => int($Low*16)/16, MaximumScale => int($High*16+1)/16 ); prt( "Set fat candles with only 5% gaps ...\n" ); $Chart->ChartGroups(1)->{GapWidth} = 5; sub RGB { $_[0] | ($_[1] << 8) | ($_[2] << 16) } prt( "Set Yellow background, with a solid border ...\n"); $Chart->PlotArea->Border->{LineStyle} = xlContinuous; $Chart->PlotArea->Border->{Color} = RGB(0,0,0); $Chart->PlotArea->Interior->{Color} = RGB(246,246,104); # NOTE: This requires an Add-In to be installed in Excel ... prt( "Add 1 hour, blue, moving average of the Close series ...\n" ); my $MovAvg = $Chart->SeriesCollection(4)->Trendlines->Add({Type => xlMovingAvg, Period => 4}); $MovAvg->Border->{Color} = RGB(0,0,255); } sub fill_bars2 { # Time Open High Low Close push(@Bars2, [ "09:15", "24.5625", "24.875", "24.875", "24.875" ] ); push(@Bars2, [ "09:30", "24.5625", "25", "25", "24.9375" ] ); push(@Bars2, [ "09:45", "24.875", "24.9375", "24.9375", "24.375" ] ); push(@Bars2, [ "10:00", "24.4375", "24.625", "24.625", "24.3125" ] ); push(@Bars2, [ "10:15", "24.375", "24.6875", "24.6875", "24.6875" ] ); push(@Bars2, [ "10:30", "24.75", "24.75", "24.75", "24.5625" ] ); push(@Bars2, [ "10:45", "24.625", "24.625", "24.625", "24.5625" ] ); push(@Bars2, [ "11:00", "24.4375", "24.5625", "24.5625", "24.4375" ] ); push(@Bars2, [ "11:15", "24.375", "24.4375", "24.4375", "24.375" ] ); push(@Bars2, [ "11:30", "24.3125", "24.625", "24.625", "24.625" ] ); push(@Bars2, [ "11:45", "24.5625", "24.5625", "24.5625", "24.375" ] ); push(@Bars2, [ "12:00", "24.4375", "24.4375", "24.4375", "24.125" ] ); push(@Bars2, [ "12:15", "24.25", "24.375", "24.375", "24.25" ] ); push(@Bars2, [ "12:30", "24.3125", "24.5625", "24.5625", "24.4375" ] ); push(@Bars2, [ "12:45", "24.5625", "24.75", "24.75", "24.75" ] ); push(@Bars2, [ "13:00", "24.875", "25", "25", "24.6875" ] ); push(@Bars2, [ "13:15", "24.5625", "24.875", "24.875", "24.5625" ] ); } sub fill_bars2_org { # Time Open High Low Close push(@Bars2, [ "09:15", "24.5625", "24.875", "24.875", "24.875"] ); push(@Bars2, [ "09:30", "24.5625", "25", "25", "24.9375"] ); push(@Bars2, [ "09:45", "24.875", "24.9375", "24.9375", "24.375"] ); push(@Bars2, [ "10:00", "24.4375", "24.625", "24.625", "24.3125"] ); push(@Bars2, [ "10:15", "24.375", "24.6875", "24.6875", "24.6875"] ); push(@Bars2, [ "10:30", "24.75", "24.75", "24.75", "24.5625"] ); push(@Bars2, [ "10:45", "24.625", "24.625", "24.625", "24.5625"] ); push(@Bars2, [ "11:00", "24.4375", "24.5625", "24.5625", "24.4375"] ); push(@Bars2, [ "11:15", "24.375", "24.4375", "24.4375", "24.375"] ); push(@Bars2, [ "11:30", "24.3125", "24.625", "24.625", "24.625"] ); push(@Bars2, [ "11:45", "24.5625", "24.5625", "24.5625", "24.375"] ); push(@Bars2, [ "12:00", "24.4375", "24.4375", "24.4375", "24.125"] ); push(@Bars2, [ "12:15", "24.25", "24.375", "24.375", "24.25"] ); push(@Bars2, [ "12:30", "24.3125", "24.5625", "24.5625", "24.4375"] ); push(@Bars2, [ "12:45", "24.5625", "24.75", "24.75", "24.75"] ); push(@Bars2, [ "13:00", "24.875", "25", "25", "24.6875"] ); push(@Bars2, [ "13:15", "24.5625", "24.875", "24.875", "24.5625"] ); } ################################ ### output and log file sub wlog { my $ml = shift; print $LOG $ml; } sub prt { my $m = shift; if ($write_log) { wlog($m); } print STDOUT $m; } sub mydie { my $msg = shift; if ($write_log) { wlog($msg); } die $msg; } sub log_close { if ($write_log) { close( $LOG ); } } sub close_log { if ($write_log) { prt( "Closing LOG file, and passing to 'system($outfile)'\nMay need to CLOSE notepad to continue ...\n" ); log_close(); system( $outfile ); } } # eof - autoexcel06.pl