论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2012-12-11 09:55 |只看该作者 |倒序浏览

本帖最后由 yinpenghero 于 2012-12-11 10:55 编辑

====以下是正文，转载请证明出处＝＝＝＝
＝＝＝附上源excel文件，供测试用=====

################################################
# @author: Peng YIN
# @email: yinpenghero@163.com
# @filename: CreateChart.pl
# @date: 2012-12-11
# @declaration:
################################################
#!/usr/bin/perl -w
use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

my $file = 'E:\test.xls';
my $worksheet = 'test';
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new( 'Excel.Application', 'Quit' );
$Excel->{DisplayAlerts} = 'False';
my $src_book = $Excel->Workbooks->Open($file);
my $sheet= $src_book->Worksheets($worksheet);

#=================================================
# Create new Chart
#=================================================
my $chart_range = $sheet->Range( "C9:G12" );       # 数据源范围
my $charts = $Excel->Charts->Add();    #　新建图表

$charts->{'ChartType'} = xlLineMarkers;          #　图表样式(可从VBA代码中查看具体图表类型)
$charts->SetSourceData(
{
Source =>$chart_range, #　数据源RANG
PlotBy =>xlRows,                   # 以源数据库横向还是竖向为标准生成图表
}
);

$charts->{'HasTitle'} = 1;
$charts->ChartTitle->{'Text'} = "Test Metrics"; #图表标题

#$charts->{'HasAxis(xlCategory, xlPrimary)'} = 1; # 数据源ＸＹ坐标轴的值
#$charts->{'HasAxis(xlValue, xlPrimary)'} = 1;

#======VBA Codes =====
# ActiveChart.SeriesCollection(1).XValues = "=test!R8C3:R8C7"
# ActiveChart.SeriesCollection(1).Values = "=test!R9C3:R9C7"
# ActiveChart.SeriesCollection(1).Name = "=test!R9C2"
# ActiveChart.SeriesCollection(2).XValues = "=test!R8C3:R8C7"
# ActiveChart.SeriesCollection(1).Values = "=test!R10C3:R10C7"
# ActiveChart.SeriesCollection(2).Name = "=test!R10C2"
# ActiveChart.SeriesCollection(3).XValues = "=test!R8C3:R8C7"
# ActiveChart.SeriesCollection(1).Values = "=test!R11C3:R11C7"
# ActiveChart.SeriesCollection(3).Name = "=test!R11C2"
# ActiveChart.SeriesCollection(4).XValues = "=test!R8C3:R8C7"
# ActiveChart.SeriesCollection(1).Values = "=test!R12C3:R12C7"
# ActiveChart.SeriesCollection(4).Name = "=test!R12C2"

my $xlCategory = '='.$worksheet.'!R8C3:R8C7';
my $xlValues = '='.$worksheet.'!R9C3:R9C7';
my $xlName = '='.$worksheet.'!R9C2';

$charts->SeriesCollection(1)->{'XValues'} = $xlCategory;
$charts->SeriesCollection(1)->{'Values'} = $xlValues;
$charts->SeriesCollection(1)->{'Name'} = $xlName;

$xlValues = '='.$worksheet.'!R10C3:R10C7';
$xlName = '='.$worksheet.'!R10C2';
$charts->SeriesCollection(2)->{'XValues'} = $xlCategory;
$charts->SeriesCollection(2)->{'Values'} = $xlValues;
$charts->SeriesCollection(2)->{'Name'} = $xlName;

$xlValues = '='.$worksheet.'!R11C3:R11C7';
$xlName = '='.$worksheet.'!R11C2';
$charts->SeriesCollection(3)->{'XValues'} = $xlCategory;
$charts->SeriesCollection(3)->{'Values'} = $xlValues;
$charts->SeriesCollection(3)->{'Name'} = $xlName;

$xlValues = '='.$worksheet.'!R12C3:R12C7';
$xlName = '='.$worksheet.'!R12C2';
$charts->SeriesCollection(4)->{'XValues'} = $xlCategory;
$charts->SeriesCollection(4)->{'Values'} = $xlValues;
$charts->SeriesCollection(4)->{'Name'} = $xlName;

#　y轴参数的定义
#=====VBA Codes=========
# With ActiveChart.Axes(xlValue)
# .MinimumScaleIsAuto = True
# .MaximumScale = 1
# .MinorUnit = 0.04
# .MajorUnit = 0.1
# .Crosses = xlAutomatic
# .ReversePlotOrder = False
# .ScaleType = xlLinear
# .DisplayUnit = xlNone
# End With
$charts->Axes(xlCategory, xlPrimary)->{'CategoryType'} = xlCategoryScale;
$charts->Axes(xlValue)->{'MinimumScaleIsAuto'} = 1;
$charts->Axes(xlValue)->{'MaximumScale'} = 1;
$charts->Axes(xlValue)->{'MinorUnit'} = 0.04;
$charts->Axes(xlValue)->{'MajorUnit'} = 0.1;
$charts->Axes(xlValue)->{'Crosses'} = xlCustom;
$charts->Axes(xlValue)->{'CrossesAt'} = 0;
$charts->Axes(xlValue)->{'ReversePlotOrder'} = 0;
$charts->Axes(xlValue)->{'ScaleType'} = xlLinear;
$charts->Axes(xlValue)->{'DisplayUnit'} = xlNone;

#图表位置（不定义则单独生成一新的EXCEL表格）
$charts->Location(
{
Where => xlLocationAsObject,
Name => $worksheet,          # sheet name
}
);
#保存
$src_book->Save;
$Excel->{DisplayAlerts} = 'True';
undef $src_book;
undef $Excel;

#　更改图表的大小及位置，必须先保存，然后重新打开再更改
#========================================================================
# Move Chart
#========================================================================
#ActiveSheet.Shapes("Chart 1").ScaleWidth 1.46, msoFalse, _
# msoScaleFromBottomRight
#ActiveSheet.Shapes("Chart 1").ScaleWidth 1.13, msoFalse, msoScaleFromTopLeft
#ActiveSheet.Shapes("Chart 1").ScaleHeight 0.94, msoFalse, _
# msoScaleFromBottomRight
#ActiveSheet.Shapes("Chart 1").ScaleHeight 0.79, msoFalse, msoScaleFromTopLeft

$Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new( 'Excel.Application', 'Quit' );
$Excel->{DisplayAlerts} = 'False';
$src_book = $Excel->Workbooks->Open($file);
$sheet= $src_book->Worksheets($worksheet);

$sheet->Shapes("Chart 1")->ScaleWidth(1.46,0,2);  # 宽度： 1.46 是原宽大小的倍数; 0 是msoFalse,不知啥意思； 2 is 宽的右侧不动，向左侧变化1.76倍
$sheet->Shapes("Chart 1")->ScaleWidth(1.13,0,0);  # （如果0是左侧不动，向变化1.46倍;　如果是1,则是左右同时变化1.46倍）;
$sheet->Shapes("Chart 1")->ScaleHeight(0.94,0,2); #　高度： 2 是上面不动，下面变化0.94倍, 1 是上下同时变化0.94倍，０是下面不动，上面变化0.94倍
$sheet->Shapes("Chart 1")->ScaleHeight(0.79,0,0); #

$src_book->Save;
$Excel->{DisplayAlerts} = 'True';
undef $src_book;
undef $Excel;

# 结束EXCEL的进程
my $KillExcelProcess = `taskkill  /F /IM EXCEL.EXE 2>>nul`;
chomp $KillExcelProcess;
print "Kill Excel Process：$KillExcelProcess \n" if ($KillExcelProcess);