1procedureTFrmWeekAnalysisQry.BtnExportToExcelClick(Sender: TObject);
2varwordApp,WordDoc,WrdSelection:variant;
3strAdd:string;
4i,j,iRangeEnd,iStart,iEnd : integer;
5wdPar,wdRange:OleVariant;
6oShape, oChart,myCol: OleVariant;
7SumJHTonCount, SumWCTonCount, PJTS, PJZCSJ, YSSR, DuoJing: Currency;
8SumJHCarCount, SumWCCarCount: Integer;
9SumDJ,SumFDJ: Currency;//多经 非多经汇总
10begin
11wordApp := CreateOleObject('Word.Application');
12wordApp.Visible :=true;
13wordDoc:=WordApp.Documents.Add();
14wordDoc.select;
15wrdSelection :=WordApp.selection;
16strAdd:='%s年第%d周战略装车点生产经营分析';
17strAdd:= Format(strAdd, [FormatDateTime('YYYY', Date), WeekofYear(Date)]);
18wrdSelection.ParagraphFormat.Alignment:=wdAlignParagraphCenter;
19wrdSelection.Font.bold :=true;
20wrdSelection.Font.Size := 15;
21//wrdSelection.Font.UnderLine := 1; 下划线
22wrdSelection.TypeText(strAdd);
23wordApp.selection.TypeParagraph;//换行
24wrdSelection.Font.bold :=false;
25wrdSelection.Font.Size := 10;
26wrdSelection.Font.bold :=false;
27wrdSelection.TypeText(FormatDateTime('MM月DD日', DateBegin.Date)+'-'+FormatDateTime('MM月DD日', DateEnd.Date));
28wordApp.selection.TypeParagraph;//换行
29wrdSelection.TypeText('西安铁路局货运处');
30wordApp.selection.TypeParagraph;//换行
31wrdSelection.ParagraphFormat.Alignment:=wdAlignParagraphLeft;
32wrdSelection.Font.bold :=true;
33wrdSelection.TypeText('1、战略装车点主要运营指标完成情况：');
34//从新设置字体
35wrdSelection.ParagraphFormat.Alignment:=wdAlignParagraphLeft;
36wrdSelection.Font.bold :=false;
37wrdSelection.Font.Size := 10;
38wrdSelection.Font.UnderLine := 0;
39wdPar:=WordApp.ActiveDocument.Paragraphs.Add;
40wdRange:=wdPar.Range;
41wdRange :=wordApp.ActiveDocument.Content;
42wdRange.Collapse(wdCollapseEnd);
43wordDoc.Tables.Add(wdRange,cdsMain.RecordCount+ 2,11);
44//合并单元格不成功
45{iStart:=WordDoc.Tables.Item(1).Cell(1,1).Range.Start;
46//myCol:= WordDoc.Tables.Item(1).Cell(1,2);
47//myCol:= WordDoc.Tables.Item(1).Columns.Item(2);
48//iEnd:=myCol.Cells.Item(myCol.Cells.Count).Range.End;
49//iEnd:= WordDoc.Tables.Item(1).Cell(2,1).Range.Start;
50iEnd:= iStart+ 1;
51wdRange:=WordDoc.Range;
52wdRange.Start:=iStart;
53wdRange.End :=iEnd;
54wdRange.Cells.Merge; }
55//插入数据
56wordDoc.Tables.Item(1).Cell(1,1).Range.Text:= '序号';
57wordDoc.Tables.Item(1).Cell(1,2).Range.Text:= '车站';
58wordDoc.Tables.Item(1).Cell(1,3).Range.Text:= '品类';
59wordDoc.Tables.Item(1).Cell(1,4).Range.Text:= '周计划车数';
60wordDoc.Tables.Item(1).Cell(1,5).Range.Text:= '周计划万吨';
61wordDoc.Tables.Item(1).Cell(1,6).Range.Text:= '周完成车数';
62wordDoc.Tables.Item(1).Cell(1,7).Range.Text:= '周完成万吨';
63wordDoc.Tables.Item(1).Cell(1,8).Range.Text:= '平均停时(小时)';
64wordDoc.Tables.Item(1).Cell(1,9).Range.Text:= '平均装车时间(小时)';
65wordDoc.Tables.Item(1).Cell(1,10).Range.Text:= '运输收入(万元)';
66wordDoc.Tables.Item(1).Cell(1,11).Range.Text:= '多经毛利测算(万元)';
67cdsMain.First;
68SumJHTonCount:= 0;
69SumWCTonCount:= 0;
70PJTS:= 0;
71PJZCSJ:= 0;
72YSSR:= 0;
73SumJHCarCount:= 0;
74SumWCCarCount:= 0;
75DuoJing:= 0;
76fori:= 2tocdsMain.RecordCount+ 1do
77begin
78wordDoc.Tables.Item(1).Cell(i,1).Range.Text:= cdsMain.FieldByName('ROWNUM').AsString;
79wordDoc.Tables.Item(1).Cell(i,2).Range.Text:= cdsMain.FieldByName('UPNAME').AsString;
80wordDoc.Tables.Item(1).Cell(i,3).Range.Text:= cdsMain.FieldByName('PLNAME').AsString;
81wordDoc.Tables.Item(1).Cell(i,4).Range.Text:= cdsMain.FieldByName('PLANCARCOUNT').AsString;
82wordDoc.Tables.Item(1).Cell(i,5).Range.Text:= cdsMain.FieldByName('PLANTONCOUNT').AsString;
83wordDoc.Tables.Item(1).Cell(i,6).Range.Text:= cdsMain.FieldByName('CARCOUNT').AsString;
84wordDoc.Tables.Item(1).Cell(i,7).Range.Text:= cdsMain.FieldByName('TONCOUNT').AsString;
85wordDoc.Tables.Item(1).Cell(i,8).Range.Text:= cdsMain.FieldByName('AVGSTOP').AsString;
86wordDoc.Tables.Item(1).Cell(i,9).Range.Text:= cdsMain.FieldByName('AVGLOAD').AsString;
87wordDoc.Tables.Item(1).Cell(i,10).Range.Text:= cdsMain.FieldByName('TRANSINCOME').AsString;
88wordDoc.Tables.Item(1).Cell(i,11).Range.Text:= cdsMain.FieldByName('DUOJING').AsString;
89SumJHTonCount:= SumJHTonCount+ cdsMain.FieldByName('PLANTONCOUNT').AsCurrency;
90SumWCTonCount:= SumWCTonCount+ cdsMain.FieldByName('TONCOUNT').AsCurrency;
91PJTS:= PJTS+ cdsMain.FieldByName('AVGSTOP').AsCurrency;
92PJZCSJ:= PJZCSJ+ cdsMain.FieldByName('AVGLOAD').AsCurrency;
93YSSR:= YSSR+ cdsMain.FieldByName('TRANSINCOME').AsCurrency;
94SumJHCarCount:= SumJHCarCount+ cdsMain.FieldByName('PLANCARCOUNT').AsInteger;
95SumWCCarCount:= SumWCCarCount+ cdsMain.FieldByName('CARCOUNT').AsInteger;
96DuoJing:= DuoJing+ cdsMain.FieldByName('DUOJING').AsCurrency;
97cdsMain.Next;
98end;
99Inc(i);
100wordDoc.Tables.Item(1).Cell(i,2).Range.Text:= '合计';
101wordDoc.Tables.Item(1).Cell(i,3).Range.Text:= '';
102wordDoc.Tables.Item(1).Cell(i,4).Range.Text:=IntToStr(SumJHCarCount);
103wordDoc.Tables.Item(1).Cell(i,5).Range.Text:=CurrToStr(SumJHTonCount);
104wordDoc.Tables.Item(1).Cell(i,6).Range.Text:=IntToStr(SumWCCarCount);
105wordDoc.Tables.Item(1).Cell(i,7).Range.Text:=CurrToStr(SumWCTonCount);
106ifcdsMain.RecordCount> 0then
107begin
108wordDoc.Tables.Item(1).Cell(i,8).Range.Text:= CurrToStr(PJTS/cdsMain.RecordCount);
109wordDoc.Tables.Item(1).Cell(i,9).Range.Text:= CurrToStr(PJZCSJ/cdsMain.RecordCount);
110end;
111wordDoc.Tables.Item(1).Cell(i,10).Range.Text:=CurrToStr(YSSR);
112wordDoc.Tables.Item(1).Cell(i,10).Range.Text:=CurrToStr(DuoJing);
113
114wordApp.Selection.EndKey(wdStory,EmptyParam);
115wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
116wdRange :=wordApp.activeDocument.Content;
117wdRange.Collapse(wdCollapseEnd);
118ifSumJHTonCount<=>0thenSumJHTonCount:=SumWCTonCount;
119//ifEdtQJHF.Value<=>0thenEdtQJHF.Value:=SumWCTonCount;
120//ifSumJHCarCount<=>0thenSumJHTonCount:=SumWCCarCount;
121wrdSelection.TypeText('（1）全局28个战略装车点共装车'+ IntToStr(SumWCCarCount)+'辆，完成计划的'+ GetPercent(SumWCCarCount , SumJHCarCount)+'；发送货物'
122+CurrToStr(SumWCTonCount)+'万吨，完成计划的'+GetPercent(SumWCTonCount , SumJHTonCount)+'，占全局货发总量的'
123+GetPercent(SumWCTonCount , EdtQJHF.Value)+ '。');
124wordApp.selection.TypeParagraph;//换行
125wrdSelection.ParagraphFormat.Alignment:=wdAlignParagraphCenter;
126wrdSelection.TypeText('图-6 战略装车点装车数完成情况');
127
128
129wordApp.Selection.EndKey(wdStory,EmptyParam);
130wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
131wdRange :=wordApp.activeDocument.Content;
132wdRange.Collapse(wdCollapseEnd);
133
134oShape:= wordApp.Selection.InlineShapes.AddOleObject(ClassType:='MSGraph.Chart.8',FileName:='',LinkToFile :=False,DisplayAsIcon :=False);
135oChart:=oShape.OleFormat.Object;
136oChart.Application.DataSheet.Cells.Clear;
137oChart.ChartArea.Font.Size:= 8;
138oChart.Application.Update;
139oChart.ChartType:= 51;// 1是面积图 4是线形图 5是饼图 51是柱状图
140//添加行标题
141oChart.Application.DataSheet.Cells[2,1].Value:= '计划';
142oChart.Application.DataSheet.Cells[3,1].Value:= '实际完成';
143//添加列标题
144oChart.Application.DataSheet.Cells[1,2].Value:= '上周';
145oChart.Application.DataSheet.Cells[1,3].Value:= '本周';
146
147oChart.Application.DataSheet.Cells[2, 2].Value:=LastPlanCarCount;
148oChart.Application.DataSheet.Cells[2, 3].Value:=SumJHCarCount;
149oChart.Application.DataSheet.Cells[3, 2].Value:=LastImplCarCount;
150oChart.Application.DataSheet.Cells[3, 3].Value:=SumWCCarCount;
151oChart.Application.Update;
152oChart.Application.Quit;
153
154wordApp.Selection.EndKey(wdStory,EmptyParam);
155wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
156wdRange :=wordApp.activeDocument.Content;
157wdRange.Collapse(wdCollapseEnd);
158wrdSelection.TypeText('图-7 战略装车点货物发送量完成情况');
159wordApp.Selection.EndKey(wdStory,EmptyParam);
160wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
161wdRange :=wordApp.activeDocument.Content;
162wdRange.Collapse(wdCollapseEnd);
163
164oShape:= wordApp.Selection.InlineShapes.AddOleObject(ClassType:='MSGraph.Chart.8',FileName:='',LinkToFile :=False,DisplayAsIcon :=False);
165oChart:=oShape.OleFormat.Object;
166oChart.Application.DataSheet.Cells.Clear;
167oChart.ChartArea.Font.Size:= 8;
168oChart.Application.Update;
169oChart.ChartType:= 51;// 1是面积图 4是线形图 5是饼图 51是柱状图
170//添加行标题
171oChart.Application.DataSheet.Cells[2,1].Value:= '计划';
172oChart.Application.DataSheet.Cells[3,1].Value:= '实际完成';
173//添加列标题
174oChart.Application.DataSheet.Cells[1,2].Value:= '上周';
175oChart.Application.DataSheet.Cells[1,3].Value:= '本周';
176
177oChart.Application.DataSheet.Cells[2, 2].Value:=LastPlanTonCount;
178oChart.Application.DataSheet.Cells[2, 3].Value:=SumJHTonCount;
179oChart.Application.DataSheet.Cells[3, 2].Value:=LastImplTonCount;
180oChart.Application.DataSheet.Cells[3, 3].Value:=SumWCTonCount;
181oChart.Application.Update;
182oChart.Application.Quit;
183
184wordApp.Selection.EndKey(wdStory,EmptyParam);
185wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
186wdRange :=wordApp.activeDocument.Content;
187wdRange.Collapse(wdCollapseEnd);
188wrdSelection.TypeText('图-8 战略装车量占货物发送量情况');
189wordApp.Selection.EndKey(wdStory,EmptyParam);
190wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
191wdRange :=wordApp.activeDocument.Content;
192wdRange.Collapse(wdCollapseEnd);
193
194oShape:= wordApp.Selection.InlineShapes.AddOleObject(ClassType:='MSGraph.Chart.8',FileName:='',LinkToFile :=False,DisplayAsIcon :=False);
195oChart:=oShape.OleFormat.Object;
196oChart.Application.DataSheet.Cells.Clear;
197oChart.ChartArea.Font.Size:= 8;
198oChart.Application.Update;
199oChart.ChartType:= 5;// 1是面积图 4是线形图 5是饼图 51是柱状图
200//添加行标题
201oChart.Application.DataSheet.Cells[2,1].Value:= '发送万吨';
202//添加列标题
203oChart.Application.DataSheet.Cells[1,2].Value:= '战略装车点';
204oChart.Application.DataSheet.Cells[1,3].Value:= '其他装车';
205oChart.Application.DataSheet.Cells[2, 2].Value:=SumWCTonCount;
206oChart.Application.DataSheet.Cells[2, 3].Value:=EdtQJHF.Value;
207oChart.Application.Update;
208oChart.Application.Quit;
209
210wordApp.Selection.EndKey(wdStory,EmptyParam);
211wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
212wdRange :=wordApp.activeDocument.Content;
213wdRange.Collapse(wdCollapseEnd);
214wrdSelection.TypeText('图-9 战略装车量分品类完成情况');
215wordApp.Selection.EndKey(wdStory,EmptyParam);
216wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
217wdRange :=wordApp.activeDocument.Content;
218wdRange.Collapse(wdCollapseEnd);
219
220oShape:= wordApp.Selection.InlineShapes.AddOleObject(ClassType:='MSGraph.Chart.8',FileName:='',LinkToFile :=False,DisplayAsIcon :=False);
221oChart:=oShape.OleFormat.Object;
222oChart.Application.DataSheet.Cells.Clear;
223oChart.ChartArea.Font.Size:= 8;
224oChart.Application.Update;
225oChart.ChartType:= 5;// 1是面积图 4是线形图 5是饼图 51是柱状图
226//添加行标题
227oChart.Application.DataSheet.Cells[2,1].Value:= '发送万吨';
228//添加列标题
229cdsGroupByPL.First;
230fori:= 2tocdsGroupByPL.RecordCount+ 1do
231begin
232oChart.Application.DataSheet.Cells[1,i].Value:= Trim(cdsGroupByPL.FieldByName('PLNAME').AsString);
233oChart.Application.DataSheet.Cells[2, i].Value:= cdsGroupByPL.FieldByName('TONCOUNT').AsCurrency;
234cdsGroupByPL.Next;
235end;
236oChart.Application.Update;
237oChart.Application.Quit;
238
239wordApp.Selection.EndKey(wdStory,EmptyParam);
240wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
241wdRange :=wordApp.activeDocument.Content;
242wdRange.Collapse(wdCollapseEnd);
243wrdSelection.TypeText('（2）货车停留时间平均为'+ GetDiv(PJTS,cdsMain.RecordCount)+'小时,较考核目标超'+GetOverloadTime(PJTS,cdsMain.RecordCount,5)+'小时，'
244+'较全局平均停时减少2.8小时；平均装车作业时间'+ GetDiv(PJZCSJ, cdsMain.RecordCount)+'小时，较考核目标超'+GetOverloadTime(PJZCSJ,cdsMain.RecordCount,2)+'小时。');
245wordApp.selection.TypeParagraph;//换行
246wrdSelection.TypeText('（3）路局多经参与经营的12个战略装车点，本周总计完成'+IntToStr(SumWCCarCount)
247+'车，比上周'+ IntToStr(Trunc(LastImplCarCount))+'车多装'+ IntToStr(Trunc(SumWCCarCount- LastImplCarCount))+'车，增加了'
248+GetPercent(SumJHCarCount- LastImplCarCount, LastImplCarCount)+'，日均装车'+GetDiv(SumWCTonCount, Trunc(DateEnd.Date-DateBegin.Date+ 1))
249+'车。占全局战略装车点发送总量的'+ GetPercent(SumWCCarCount, EdtQJHF.Value)+'。');
250wordApp.selection.TypeParagraph;//换行
251wrdSelection.TypeText('3、毛利分析：本周实现毛利'+ CurrToStr(DuoJing)+'万元，和上周基本持平。');
252SumDJ:= 0;
253SumFDJ:= 0;
254cdsMain.First;
255withcdsMain do
256begin
257DisableControls;
258whilenotEof do
259begin
260ifFieldByName('DJCY').AsInteger= 0then
261SumDJ:= SumDJ+ FieldByName('TONCOUNT').AsCurrency
262else
263SumFDJ:= SumFDJ+ FieldByName('TONCOUNT').AsCurrency;
264cdsMain.Next;
265end;
266EnableControls;
267end;
268wordApp.selection.TypeParagraph;//换行
269wrdSelection.ParagraphFormat.Alignment:=wdAlignParagraphCenter;
270wrdSelection.TypeText('图-10 多经参与战略装车情况');
271wordApp.Selection.EndKey(wdStory,EmptyParam);
272wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
273wdRange :=wordApp.activeDocument.Content;
274wdRange.Collapse(wdCollapseEnd);
275
276oShape:= wordApp.Selection.InlineShapes.AddOleObject(ClassType:='MSGraph.Chart.8',FileName:='',LinkToFile :=False,DisplayAsIcon :=False);
277oChart:=oShape.OleFormat.Object;
278oChart.Application.DataSheet.Cells.Clear;
279oChart.ChartArea.Font.Size:= 8;
280oChart.Application.Update;
281oChart.ChartType:= 5;// 1是面积图 4是线形图 5是饼图 51是柱状图
282//添加行标题
283oChart.Application.DataSheet.Cells[2,1].Value:= '发送吨';
284//添加列标题
285oChart.Application.DataSheet.Cells[1,2].Value:= '多经参与经营发送吨';
286oChart.Application.DataSheet.Cells[1,3].Value:= '其他战略装车点发送吨';
287oChart.Application.DataSheet.Cells[2, 2].Value:=SumDJ;
288oChart.Application.DataSheet.Cells[2, 3].Value:=SumFDJ;
289oChart.Application.Update;
290oChart.Application.Quit;
291
292wordApp.selection.TypeParagraph;//换行
293wrdSelection.ParagraphFormat.Alignment:=wdAlignParagraphCenter;
294wrdSelection.TypeText('图-11 多经参与战略装车点经营收入（万元）');
295wordApp.Selection.EndKey(wdStory,EmptyParam);
296wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
297wdRange :=wordApp.activeDocument.Content;
298wdRange.Collapse(wdCollapseEnd);
299
300oShape:= wordApp.Selection.InlineShapes.AddOleObject(ClassType:='MSGraph.Chart.8',FileName:='',LinkToFile :=False,DisplayAsIcon :=False);
301oChart:=oShape.OleFormat.Object;
302oChart.Application.DataSheet.Cells.Clear;
303oChart.ChartArea.Font.Size:= 8;
304oChart.Application.Update;
305oChart.ChartType:= 51;// 1是面积图 4是线形图 5是饼图 51是柱状图
306//添加行标题
307oChart.Application.DataSheet.Cells[2,1].Value:= '收入（万元）';
308//添加列标题
309oChart.Application.DataSheet.Cells[1,2].Value:= '本周';
310oChart.Application.DataSheet.Cells[1,3].Value:= '上周';
311oChart.Application.DataSheet.Cells[2, 2].Value:= 319.14;
312oChart.Application.DataSheet.Cells[2, 3].Value:= 314.96;
313oChart.Application.Update;
314oChart.Application.Quit;
315
316wordApp.Selection.EndKey(wdStory,EmptyParam);
317wdPar :=wordApp.ActiveDocument.Paragraphs.Add;
318wdRange :=wordApp.activeDocument.Content;
319wdRange.Collapse(wdCollapseEnd);
320wrdSelection.TypeText('（4）装车分析：与上周一样，全局仅有黄陵、安口南两个战略装车点到达日均装运一列的要求，'
321+'占全局战略装车点总数的7%。周装车少于100车的有梅家坪、白水江、燕子砭、咸阳西4个点。');
322wordApp.selection.TypeParagraph;//换行
323wrdSelection.TypeText('2.存在问题及分析：');
324wordApp.selection.TypeParagraph;//换行
325wrdSelection.TypeText('（1）全局共有14个装车点为完成周装车计划。完成比例在50%以下的有桑树坪、合阳、白水江、燕子砭4个战略装车点，占总数的14%');
326wordApp.selection.TypeParagraph;//换行
327wrdSelection.TypeText('主要原因有四个方面。一是空出来源紧张，不能满足日均装车需求。装运大宗货源煤炭、石油需要的敞车、罐车缺口较大。'
328+'如牛家梁在请求车5418车的情况下，仅承认了916车；柞水、官渡、三桥也存在此类原因。本周共产生坏车及过期车2104量，'
329+'修复使用1745车。三是停限装原因，战略装车点请求车兑现率仅为41%。四是货源依旧不足。铁矿石、焦炭市场不好影响装车。');
330wordApp.selection.TypeParagraph;//换行
331wrdSelection.TypeText('（2）一是装车速度较慢，影响装车时间，造成平均停时较大，如合阳点专用线装车为漏斗漏煤，装车速度较快，平煤采取刮板刮平后，'
332+'人工平顶，速度较慢，引起停时较大。二是坏车修复时间长，也是引起停时较大的一个重要原因。');
333wordApp.selection.TypeParagraph;//换行
334wrdSelection.TypeText('3.战略装车点下周重点工作：');
335wordApp.selection.TypeParagraph;//换行
336wrdSelection.TypeText('（1）加大货源组织工作。广泛宣传战略装车点优势，公布资源受理电话、去向、运输品类和开行时间，在货运计划、配空车'
337+'等方面予以优惠政策，重点做好新建战略装车点瑶曲、安口南的货源组织工作，开发战略装车点新货源，保证装车上量。');
338wordApp.selection.TypeParagraph;//换行
339wrdSelection.TypeText('（2）提高承认车兑现率。对组织不力造成承认车落空的进行认真分析，纳入考核并追究管理责任。调度、运输、货运部门要加强协作，'
340+'落实战略装车点计划、承认、配空、挂运四优先制度。');
341wordApp.selection.TypeParagraph;//换行
342wrdSelection.TypeText('（3）大力压缩等待时间。与专用线加强协调，增强战略装车点装车、平顶、加固车门人员力量，压缩撞车后的整理等待时间。');
343end;
