procedure Create_Chart(AFileName :string);
var
TmpFileName :string;
xlapp: variant;
xlssheet1, xlssheet2, xlssheet3,
xlssheet4, xlssheet5, xlssheet6,
xlssheet7, xlssheet8, xlssheet9: variant;
l_left, l_top, l_Width, l_Height :Integer;
chart1:variant;
series,se:variant;
I, ExcelCount :Integer;
AX,BX,CX,DX :string;
d_MinimumScale,d_MaximumScale :Double;
Range1,Range2,Range3 :variant;
begin
if not VarIsEmpty(xlapp) then
begin
xlapp.DisplayAlerts := False;
xlapp.Quit;
VarClear(xlapp);
end;
try
xlapp := CreateOleObject('Excel.Application');
except
hsMsgBox('操作Excel失败', '导出报表', MB_ICONWARNING+MB_OK);
Exit;
end;
xlapp.WorkBooks.Open(AFileName);
xlssheet1 := xlapp.Workbooks[1].WorkSheets[1];
xlssheet2 := xlapp.Workbooks[1].WorkSheets[2];
xlssheet3 := xlapp.Workbooks[1].WorkSheets[3];
xlssheet4 := xlapp.Workbooks[1].WorkSheets[4];
xlssheet5 := xlapp.Workbooks[1].WorkSheets[5];
xlssheet6 := xlapp.Workbooks[1].WorkSheets[6];
xlssheet1.Columns.EntireColumn.AutoFit;
xlssheet2.Columns.EntireColumn.AutoFit;
xlssheet3.Columns.EntireColumn.AutoFit;
xlssheet4.Columns.EntireColumn.AutoFit;
xlssheet5.Columns.EntireColumn.AutoFit;
xlssheet6.Columns.EntireColumn.AutoFit;
xlapp.Workbooks[1].WorkSheets[1].Columns[1].ColumnWidth := 30;
//图1
ExcelCount := xlssheet1.Usedrange.Rows.Count;
l_left:= 1;
for I := 1 to ExcelCount do
begin
if Pos('折线图',xlssheet1.Range['A' + IntToStr(I)].Value) > 0 then
begin
Break;
end;
end;
l_top := xlssheet1.Range['A' + IntToStr(I+1) ,'A' + IntToStr(I+1)].top + 10;
l_Width := 700;
l_Height:= xlssheet1.Range['A' + IntToStr(I+1) ,'A' + IntToStr(I+1)].RowHeight*15;
//1.增加一个二维图chartobjects.add(Left, Top, 宽，高)
chart1 := xlssheet1.chartObjects.Add(l_left,l_top,l_Width,l_Height);
//2.选择二维图的形态
chart1.Activate;
chart1.chart.charttype := xlXYScatterLines;
//线条说明位置
chart1.chart.Legend.Position := xlLegendPositionTop;
//3.给二维图赋值
series := chart1.chart.SeriesCollection;
//4.给二维图加上标题
chart1.chart.HasTitle := true;
chart1.chart.ChartTitle.Characters.Text := '';
chart1.chart.ChartTitle.Font.size := 12;
//(xlValue, xlPrimary) 左标
chart1.Chart.Axes(xlValue, xlPrimary).HasTitle := True;
chart1.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text := '说明';
//( xlCategory , xlPrimary )下标
chart1.Chart.Axes(xlCategory, xlPrimary).HasTitle := True;
chart1.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text := '说明';
chart1.Chart.Axes(xlCategory, xlPrimary).HasMinorGridlines := True;
chart1.Chart.Axes(xlCategory, xlPrimary).HasMajorGridlines := True;
chart1.Chart.Axes(xlCategory, xlPrimary).MinorUnitIsAuto := True;
chart1.chart.Axes(xlCategory, xlPrimary).ScaleType := xlScaleLinear;
chart1.chart.Axes(xlCategory, xlPrimary).ticklabels.Orientation := xlTickLabelOrientationUpward;
//第一条线
ExcelCount := xlssheet2.Usedrange.Rows.Count;
series.NewSeries;
se := chart1.chart.seriescollection(1);
se.Name :='收益率';
AX := 'A' +InttoStr(ExcelCount);
se.XValues := '=sheet2!A1:'+ AX;
BX := 'B' +InttoStr(ExcelCount);
se.Values := '=sheet2!B1:'+ BX;
series.NewSeries;
se := chart1.chart.seriescollection(2);
se.Name :='基准收益率';
AX := 'A' +InttoStr(ExcelCount);
se.XValues := '=sheet2!A1:'+ AX;
CX := 'C' +InttoStr(ExcelCount);
se.Values := '=sheet2!C1:'+CX;
//自定义 水平坐标（可以删除这一段，excel自动会 水平坐标）
d_MinimumScale := 0;
d_MaximumScale := 0;
if ExcelCount > 0 then
begin
dateseparator := '-'; // 日期分隔符
shortdateformat := 'yy-mm-dd'; // 短日期格式
longdateformat := 'yyyy-mm-dd'; // 长日期格式
application.UpdateFormatSettings := false;
d_MinimumScale := Trunc(StrToDate(StringReplace(xlssheet2.Range['A1'].Value, '/', '-', [rfReplaceAll])));
d_MaximumScale := Trunc(StrToDate(StringReplace(xlssheet2.Range['A' + IntToStr(ExcelCount)].Value, '/', '-', [rfReplaceAll])));
chart1.chart.Axes(xlCategory).MinimumScale := d_MinimumScale;
chart1.chart.Axes(xlCategory).MaximumScale := d_MaximumScale;
end;
//图2
ExcelCount := xlssheet1.Usedrange.Rows.Count;
l_left:= 1;
for I := 1 to ExcelCount do
begin
if Pos('资产',xlssheet1.Range['A' + IntToStr(I)].Value) > 0 then
begin
Break;
end;
end;
l_top := xlssheet1.Range['A' + IntToStr(I+7) ,'A' + IntToStr(I+7)].top + 10;
l_Width := 700;
l_Height:= xlssheet1.Range['A' + IntToStr(I+7) ,'A' + IntToStr(I+7)].RowHeight*14;
//1.增加一个二维图chartobjects.add(Left, Top, 宽，高)
chart1 := xlssheet1.chartObjects.Add(l_left,l_top,l_Width,l_Height);
//2.选择二维图的形态
chart1.Activate;
chart1.chart.charttype := xlXYScatterLines;
//线条说明位置
chart1.chart.Legend.Position := xlLegendPositionTop;
//3.给二维图赋值
series := chart1.chart.SeriesCollection;
//4.给二维图加上标题
chart1.chart.HasTitle := true;
chart1.chart.ChartTitle.Characters.Text := '';
chart1.chart.ChartTitle.Font.size := 12;
//(xlValue, xlPrimary) 左标
chart1.Chart.Axes(xlValue, xlPrimary).HasTitle := True;
chart1.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text := '说明';
//( xlCategory , xlPrimary )下标
chart1.Chart.Axes(xlCategory, xlPrimary).HasTitle := True;
chart1.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text := '说明';
chart1.Chart.Axes(xlCategory, xlPrimary).HasMinorGridlines := True;
chart1.Chart.Axes(xlCategory, xlPrimary).HasMajorGridlines := True;
chart1.Chart.Axes(xlCategory, xlPrimary).MaximumScaleIsAuto := True;
chart1.Chart.Axes(xlCategory, xlPrimary).MinimumScaleIsAuto := True;
chart1.Chart.Axes(xlCategory, xlPrimary).MinorUnitIsAuto := True;
chart1.chart.Axes(xlCategory, xlPrimary).ScaleType := xlScaleLinear;
chart1.chart.Axes(xlCategory, xlPrimary).ticklabels.Orientation := xlTickLabelOrientationUpward;
//第一条线
ExcelCount := xlssheet3.Usedrange.Rows.Count;
series.NewSeries;
se := chart1.chart.seriescollection(1);
se.Name :='大';
AX := 'A' +InttoStr(ExcelCount);
se.XValues := '=sheet3!A1:'+ AX;
BX := 'B' +InttoStr(ExcelCount);
se.Values := '=sheet3!B1:'+ BX;
series.NewSeries;
se := chart1.chart.seriescollection(2);
se.Name :='中';
AX := 'A' +InttoStr(ExcelCount);
se.XValues := '=sheet3!A1:'+ AX;
CX := 'C' +InttoStr(ExcelCount);
se.Values := '=sheet3!C1:'+CX;
series.NewSeries;
se := chart1.chart.seriescollection(3);
se.Name :='小';
AX := 'A' +InttoStr(ExcelCount);
se.XValues := '=sheet3!A1:'+ AX;
DX := 'D' +InttoStr(ExcelCount);
se.Values := '=sheet3!D1:'+DX;
d_MinimumScale := 0;
d_MaximumScale := 0;
if ExcelCount > 0 then
begin
dateseparator := '-'; // 日期分隔符
shortdateformat := 'yy-mm-dd'; // 短日期格式
longdateformat := 'yyyy-mm-dd'; // 长日期格式
application.UpdateFormatSettings := false;
d_MinimumScale := Trunc(StrToDate(StringReplace(xlssheet3.Range['A1'].Value, '/', '-', [rfReplaceAll])));
d_MaximumScale := Trunc(StrToDate(StringReplace(xlssheet3.Range['A' + IntToStr(ExcelCount)].Value, '/', '-', [rfReplaceAll])));
chart1.chart.Axes(xlCategory).MinimumScale := d_MinimumScale;
chart1.chart.Axes(xlCategory).MaximumScale := d_MaximumScale;
end;
//图3
ExcelCount := xlssheet1.Usedrange.Rows.Count;
l_left:= 1;
for I := 1 to ExcelCount do
begin
if Pos('折线图',xlssheet1.Range['A' + IntToStr(I)].Value) > 0 then
begin
Break;
end;
end;
l_top := xlssheet1.Range['A' + IntToStr(I+1) ,'A' + IntToStr(I+1)].top + 10;
l_Width := 700;
l_Height:= xlssheet1.Range['A' + IntToStr(I+1) ,'A' + IntToStr(I+1)].RowHeight*15;
chart1 := xlssheet1.chartObjects.Add(l_left,l_top,l_Width,l_Height);
//2.选择二维图的形态
chart1.Activate;
chart1.chart.charttype := xlXYScatterLines;
chart1.chart.Legend
