#============== Indicators that determine whether chart type uses this or not ================
$max_col = 20;   $dataTableName = "Data Table";
$warning = ""; $axisTitleInd = "1"; $xAxisAlign = "";
	
#============== Acceptance of values from the calling program ================================
($maxColumns,$typeData,$AppName,$wbFullDirectory,$wbChartDirectory,$wbExcelName,$wbExcelData,
$dataCol,$strtRow,$labelCol,$endRow,$typeGraph,$ChartName,
$ChartTitle,$ChartSubTitle,$filter,$LegendKey,$LegendType,*returnVals,*X_Axis,*Y_Axis,*LegendVals) = @_;
    
$dataCol++;  $labelCol++;
$showName = ""; $showTitle = ""; 
if ($maxColumns < $max_col) { $max_col = $maxColumns; }
	
#============== Grab reference to Excel Application ================================
$swExcel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');

if ((Win32::OLE::LastError()) !~ /^(0)/) { 
    my $lastError = "Excel Application Failure! " . Win32::OLE::LastError();
    return $lastError; 
}
if ($! ne "" && $! != 0) { return $!; }
$swExcel->{'Visible'} = 1;                                      
#=== will set to visible if running from command line (doesn't seem 
														
#== to want to from CGI)
#============= Check to see if the data application exists =========================
if (!(-r ("$wbFullDirectory$wbExcelData"))) {  
    print "Cannot read the directory $wbFullDirectory$wbExcelData"; die; 
}
my $swDataWorkBook = $swExcel->Workbooks->Open("$wbFullDirectory$wbExcelData");

if ((Win32::OLE::LastError()) !~ /^(0)/) { 
    my $lastError = Win32::OLE::LastError();
   $swExcel->Quit();
   return $lastError; 
}

if ($! ne "" && $! != 0) { return $!; }
#============= Setting up Workbooks ==============================
  $swWorkBook = $swExcel->Workbooks->Add();
  $swDataWorkSheet = $swDataWorkBook->Worksheets(1);
  $swWorkSheet = $swWorkBook->Worksheets(1);
   #==========================================================================
#======= Copy data from tab-delimited file to Excel spreadsheet ===========
   #==========================================================================
$maxLetter = chr(64+$max_col);
	
####### JAN5 Start ##########
$swDataWorkSheet->Range("A1:".$maxLetter."$endRow");
#############################
$swDataWorkSheet->Range("A1:".$maxLetter."$endRow")->Copy;
    
$swWorkBook->Worksheets("Sheet1")->Select;
$swWorkBook->Worksheets("Sheet1")->{'Name'} = "Data Table";
$localCopy = $swWorkBook->Worksheets("$dataTableName");
$localCopy->Range("A1")->Select();
$localCopy->Paste();
	
#===================================================================================   
#============= Create a new Chart in the destination WorkBook ======================
  #===================================================================================
$swWorkSheet->Range("A1")->Select;
$swChart = $swExcel->Charts->Add;
$localChart = $swWorkBook->Charts("Chart1");
$localChart->Select;
if ($typeGraph =~ /COLUMN|^([ ]+)$/i) {
    $typeGraph = "COLUMN";
    $swChart->{ChartType} = xlColumnClustered;
    if ($LegendType eq "") { $LegendType = "PERC"; }
	$xAxisAlign = xlUpward;      
    } elsif ($typeGraph =~ /PIE/i) {
        $swChart->{ChartType} = xlPie;
        if ($LegendType eq "") { $LegendType = "LABPERC"; }
            $axisTitleInd = "0";
	} elsif ($typeGraph =~ /BAR/i) {
	    $swChart->{ChartType} = xlBarClustered;
	    if ($LegendType eq "") { $LegendType = "PERC"; }
	} elsif ($typeGraph =~ /LINE/i) {
	    $swChart->{ChartType} = xlLineMarkers;
	} elsif ($typeGraph =~ /XY/i) {
	    $swChart->{ChartType} = xlXYScatter;
	} elsif ($typeGraph =~ /AREA/i) {
	    $swChart->{ChartType} = xlAreaStacked;
	} elsif ($typeGraph =~ /DOUGHNUT/i) {
	    $swChart->{ChartType} = xlDoughnut;
	    $axisTitleInd = "0";
	} elsif ($typeGraph =~ /RADAR/i) {
	    $swChart->{ChartType} = xlRadarMarkers;
	    $axisTitleInd = "0";
	} elsif ($typeGraph =~ /CYLINDER/i) {
	    $swChart->{ChartType} = xlCylinderColClustered;
	} elsif ($typeGraph =~ /CONE/i) {
	    $swChart->{ChartType} = xlConeColClustered;
	} elsif ($typeGraph =~ /PYRAMID/i) {
	    $swChart->{ChartType} = xlPyramidColClustered;
	}
	if ($LegendType eq "") { $LegendType = "VAL"; }
   #================================================================
#=========== Set up the data selection code here  ===============
   #================================================================
$swChart->{HasTitle} = True;
$swChart->SetSourceData({Source=>$swWorkSheet->Range("A1")});
	
#======================== If another series is added:  $swChart->SeriesCollection->NewSeries;
$swChart->SeriesCollection(1)->{XValues} = "='".$dataTableName."'\!R".$strtRow."C".$labelCol.":R".$endRow."C".$labelCol;
$swChart->SeriesCollection(1)->{Values} = "='".$dataTableName."'\!R".$strtRow."C".$dataCol.":R".$endRow."C".$dataCol;
$swChart->Location({Where => xlLocationAsNewSheet, Name => $ChartName});	
   
   #========================================================== 
#============= Set up the Legend here      ================
#==========================================================if ($LegendType eq "PERC") {
    $swChart->ApplyDataLabels({Type => xlDataLabelsShowPercent, LegendKey => $LegendKey, HasLeaderLines => True});
} elsif ($LegendType eq "LABPERC") {
    $swChart->ApplyDataLabels({Type => xlDataLabelsShowValue, LegendKey => $LegendKey, HasLeaderLines => True});
} elsif ($LegendType eq "VAL") { 
    $swChart->ApplyDataLabels({Type => xlDataLabelsShowValue, LegendKey => $LegendKey, HasLeaderLines => True});
} elsif ($LegendType eq "LABEL") {
    $swChart->ApplyDataLabels({Type => xlDataLabelsShowLabel, LegendKey => $LegendKey});
}
$showLegend = (($LegendVals{"ShowLegend"} =~ /on/i) ? True : False);
#### JAN5
$swChart->{HasLegend} = $showLegend; 
$swChart->{HasDataTable} = (($LegendVals{ShowDataTable} =~ /on/i) ? True : False);
$showLegend = (($LegendVals{ShowLegendKeys} =~ /on/i) ? True : False);
#### JAN5
 
#***** Below axes should only display on non-pie related charts *******
if ($X_Axis{AxisTitle} ne "" && $axisTitleInd eq "1") { 
    $swChart->Axes(xlCategory, xlPrimary)->{HasTitle} = True;
    $swChart->ChartArea->Select;
    $swChart->Axes(xlCategory, xlPrimary)->AxisTitle->Characters->{Text} = "$X_Axis{AxisTitle}";
}
if ($Y_Axis{AxisTitle} ne "" && $axisTitleInd eq "1") { 
    $swChart->Axes(xlValue, xlPrimary)->{HasTitle} = True;
    $swChart->ChartArea->Select;
    $swChart->Axes(xlValue, xlPrimary)->AxisTitle->Characters->{Text} = "$Y_Axis{AxisTitle}";
}
if ($xAxisAlign ne "") {
    $swChart->Axes(xlCategory)->Select;
    $swChart->Axes(xlCategory)->TickLabels->{Orientation} = $xAxisAlign;      #*** $xAxisAlign should be xlDownard or xlUpward
}
$swChart->Legend->Select;
$designPos = '';
if ($LegendVals{"LegendPlacment"} =~ /Bottom/) {
    $designPos = xlBottom;
} elsif ($LegendVals{"LegendPlacment"} =~ /Corner/) {
    $designpos = xlCorner;
} elsif ($LegendVals{"LegendPlacment"} =~ /Top/) {
    $designPos = xlTop;
} elsif ($LegendVals{"LegendPlacment"} =~ /Right/) {
    $designPos = xlRight;
} elsif ($LegendVals{"LegendPlacment"} =~ /Left/) {
    $designPos = xlLeft;
} else {
    $designPos = xlRight;
}
$swChart->Legend->{Position} = $designPos;
   #========================================================
#===== Activate/Deactivate the gridlines
   #========================================================
$swChart->ChartArea->Select;
$CategoryAxis = $swChart->Axes(xlCategory);        
$ValueAxis = $swChart->Axes(xlValue);
$CategoryAxis->{HasMajorGridlines} = (($X_Axis{XMajor} =~ /ON/i) ? True : False);
$CategoryAxis->{HasMinorGridlines} = (($X_Axis{XMinor} =~ /ON/i) ? True : False);
$ValueAxis->{HasMajorGridlines} = (($Y_Axis{YMajor} =~ /ON/i) ? True : False);
$ValueAxis->{HasMinorGridlines} = (($Y_Axis{YMinor} =~ /ON/i) ? True : False);
   #===============================================================
#================== Chart title section ========================
   #===============================================================
$swChart->ChartTitle->Select;
$swChart->ChartTitle->{AutoScaleFont} = True;
$ChartFont = $swChart->ChartTitle->Font;
$ChartFont->{Size} = 14;
$ChartFont->{Name} = "Arial";
$ChartFont->{Strikethrough} = False;
$ChartFont->{Superscript} = False;
$ChartFont->{Subscript} = False;
$ChartFont->{OutlineFont} = False;
$ChartFont->{Shadow} = False;
$ChartFont->{Underline} = xlUnderlineStyleNone;
$ChartFont->{ColorIndex} = xlAutomatic;
$ChartFont->{Background} = xlAutomatic;
$ChartFont->{Bold} = True;
$swChart->ChartTitle->Select;
$swChart->ChartArea->Select;
    
#================================================================================
#=== Sets up the chart headers and the color and sizing of the sub-header
   #================================================================================
my $ChartLen = length($ChartTitle);
my $ChartSubLen = length($ChartSubTitle);
$swChart->ChartTitle->Characters->{Text} = $ChartTitle."\n".$ChartSubTitle;
$swChart->ChartTitle->Characters({Start => ($ChartLen+2), Length => $ChartSubLen})->Font->{"FontStyle"} = "Bold";
$swChart->ChartTitle->Characters({Start => ($ChartLen+2), Length => $ChartSubLen})->Font->{"Size"} = 12;
$swChart->ChartTitle->Characters({Start => ($ChartLen+2), Length => $ChartSubLen})->Font->{"ColorIndex"} = 9;
$savename = $wbFullDirectory . $wbExcelName . "_" . $typeGraph;
$chartname = $wbChartDirectory . $wbExcelName . "_" . $typeGraph;
$swChartObj = $swWorkBook->Worksheets(1);
if (-e ("$chartname\.xls")) { unlink ("$chartname\.xls"); }   #=== Couldn't find the parameter (if it exists) to force 
$swWorkBook->SaveAs({FileName => "$chartname\.xls", FileFormat => xlNormal, Password => "", WriteResPassword => "", ReadOnlyRecommended => False, CreateBackup => False});
$returnVals{"ExcelLink"} = "/Web$AppName/Charts/" . $wbExcelName . "_" . $typeGraph . "\.xls";
$swWorkBook->Close;
$swDataWorkSheet->Close;
$swExcel->Quit();

return $warning;