#!/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
