с помощью MAV и EasyExcel ...
Создаем в программе процедуру типа SQLProcess(MAVT).
Ту да же добавляем EasyExcelLocal(EasyExcelABC_COM).
Заходим в Extension этой процедуры.
Вставем в списке на шаблон SQLProcess и жмем Insert.
Добавляем нижеописанный шаблон и настраиваем.
Компиляем и все.
Код: Выделить всё
#EXTENSION (xEasyExcelReportForMAVProcess, 'Отчет в Excel под MAV Process'),REQ(SQLProcess(MAVT))
#SHEET
#TAB('Setup')
#PROMPT('Report title:',EXPR),%ReportTitle,REQ
#BUTTON ('Fields'), MULTI(%Fields, %GetFieldLabel()),INLINE
#PROMPT('Field mode:', DROP('Normal|Computed')),%FieldMode,DEFAULT('Normal')
#ENABLE(%FieldMode='Normal')
#PROMPT('Field:', FIELD),%FieldLabel,REQ
#ENDENABLE
#ENABLE(%FieldMode='Computed')
#PROMPT('Field label:', @S100),%FieldLabelC,REQ
#PROMPT('Field type:', @S100),%FieldLabelCType,REQ,DEFAULT('STRING(100)')
#PROMPT('Field result:',EXPR),%FieldLabelCResult,REQ
#ENDENABLE
#PROMPT('Column description:', @S100),%FieldDesc,REQ
#BOXED('Column format')
#PROMPT('Format:', DROP('General|Numeric|Money|Text|Date|Time')),%FieldFormat,DEFAULT('Text')
#PROMPT('Align:', DROP('none|Left|Right|Center')),%FieldAlign,DEFAULT('none')
#ENDBOXED
#PROMPT('Compute total',CHECK),%ComputeTotal
#ENDBUTTON
#ENDTAB
#TAB('Page')
#BOXED('Write from:')
#PROMPT('Row:', @N9),%SR,REQ,DEFAULT(1)
#PROMPT('Column:', @N9),%SC,REQ,DEFAULT(1)
#ENDBOXED
#ENDTAB
#TAB('Font')
#BOXED('Report title:')
#PROMPT('Font:', @S100),%ReportFont,REQ,DEFAULT('Arial')
#PROMPT('Size:', @N9),%ReportFontSize,REQ,DEFAULT(10)
#ENDBOXED
#BOXED('Table title:')
#PROMPT('Font:', @S100),%TitleFont,REQ,DEFAULT('Arial')
#PROMPT('Size:', @N9),%TitleFontSize,REQ,DEFAULT(8)
#PROMPT('BG Color:', COLOR),%TitleBGColor,REQ,DEFAULT(0C0C0C0H)
#ENDBOXED
#BOXED('Table body:')
#PROMPT('Font:', @S100),%BodyFont,REQ,DEFAULT('Arial')
#PROMPT('Size:', @N9),%BodyFontSize,REQ,DEFAULT(8)
#ENDBOXED
#ENDTAB
#ENDSHEET
#!======================================================================================================================
#ATSTART
#DECLARE(%COUNTER)
#DECLARE(%EE)
#DECLARE(%FORMAT)
#FOR(%ActiveTemplate),WHERE(%ActiveTemplate='EasyExcelLocal(EasyExcelABC_COM)')
#FOR(%ActiveTemplateInstance)
#CONTEXT(%Procedure,%ActiveTemplateInstance)
#SET(%EE, %EXLClassName)
#ENDCONTEXT
#ENDFOR
#ENDFOR
#ENDAT
#!======================================================================================================================
#AT(%DataSectionBeforeWindow)
ReportQueue QUEUE,PRE(RQ)
#FOR(%Fields)
#CASE(%FieldMode)
#OF('Normal')
#CASE(%FieldFormat)
#OF('Date')
%[30]FieldLabel LONG ! %FieldDesc
#OF('Time')
%[30]FieldLabel REAL ! %FieldDesc
#ELSE
%[30]FieldLabel LIKE(%FieldLabel) ! %FieldDesc
#ENDCASE
#OF('Computed')
%[29]FieldLabelC %FieldLabelCType ! %FieldDesc
#ENDCASE
#ENDFOR
#EMBED(%AfterQueueDeclaration,'xNK'),'EasyExcel report','1.After Queue declaration'
END
#FOR(%Fields)
#IF(%ComputeTotal)
#CASE(%FieldMode)
#OF('Normal')
%FieldLabel:Total LIKE(%FieldLabel) ! Total for %FieldDesc
#OF('Computed')
%FieldLabelC:Total %FieldLabelCType ! Total for %FieldDesc
#ENDCASE
#ENDIF
#ENDFOR
#ENDAT
#!======================================================================================================================
#AT(%MAVProcessProcedures,%ActiveTemplateParentInstance,'Init','CODE'),LAST
#ENDAT
#!======================================================================================================================
#AT(%MAVProcessProcedures,%ActiveTemplateParentInstance,'Fetch','CODE'),LAST
#FOR(%Fields)
#CASE(%FieldMode)
#OF('Normal')
#CASE(%FieldFormat)
#OF('Date')
ReportQueue.%FieldLabel = %EE.Date2Excel(%FieldLabel) ! %FieldDesc
#OF('Time')
ReportQueue.%FieldLabel = %EE.Time2Excel(%FieldLabel) ! %FieldDesc
#ELSE
ReportQueue.%FieldLabel = %FieldLabel ! %FieldDesc
#ENDCASE
#IF(%ComputeTotal)
%FieldLabel:Total = %FieldLabel:Total + ReportQueue.%FieldLabel
#ENDIF
#OF('Computed')
ReportQueue.%FieldLabelC = %FieldLabelCResult ! %FieldDesc
#IF(%ComputeTotal)
%FieldLabelC:Total = %FieldLabelC:Total + ReportQueue.%FieldLabelC
#ENDIF
#ENDCASE
#ENDFOR
#EMBED(%BeforeAddToQueue,'xNK'),'EasyExcel report','2.Before add to Queue'
ADD(ReportQueue)
#ENDAT
#!======================================================================================================================
#AT(%MAVProcessProcedures,%ActiveTemplateParentInstance,'Kill','DATA'),LAST
SR LONG(%SR)
SC LONG(%SC)
Rows LONG
Cols LONG
#ENDAT
#!======================================================================================================================
#AT(%MAVProcessProcedures,%ActiveTemplateParentInstance,'Kill','CODE'),LAST
!--------------------------------------------------------------------------
! init excel
%EE.Init(TRUE, TRUE)
! add workbook to excel
%EE.AddWorkbook
%EE.ScreenUpdating(FALSE)
! write report title
#EMBED(%BeforeReportTitle,'xNK'),'EasyExcel report','3.Before Report Title'
%EE.Write(SR,SC,%ReportTitle)
Rows = RECORDS(ReportQueue)
! write fields description
#SET(%COUNTER,0)
#FOR(%Fields)
%EE.Write(SR+1,SC+%COUNTER,'%FieldDesc')
#IF(%ComputeTotal)
#CASE(%FieldMode)
#OF('Normal')
%EE.Write(SR+2+Rows,SC+%COUNTER,%FieldLabel:Total)
#OF('Computed')
%EE.Write(SR+2+Rows,SC+%COUNTER,%FieldLabelC:Total)
#ENDCASE
#ENDIF
#SET(%COUNTER,%COUNTER+1)
#ENDFOR
Cols = %COUNTER
! write queue
%EE.WriteQueue(SR+2,SC,ReportQueue,2)
! draw table
%EE.SelectRange(SR+1,SC,SR+1+Rows,SC+(Cols-1))
%EE.DrawTable(BoxMode:Table, LineStyle:Continuous, BorderWeight:Thin)
! set fields format
#SET(%COUNTER,0)
#FOR(%Fields)
!------ %FieldDesc
%EE.SelectRange(SR+1,SC+%COUNTER,SR+1+Rows,SC+%COUNTER)
#SET(%FORMAT,'')
#CASE(%FieldFormat)
#OF('Date')
#SET(%FORMAT,',''dd'',''mm'',''yyyy'',''.''')
#OF('Time')
#SET(%FORMAT,',''hh'',''mm'',''ss''')
#OF('Numeric')
#SET(%FORMAT,',2,true')
#ENDCASE
%EE.SetFormat(CellFormat:%FieldFormat%FORMAT)
%EE.SetHorizAlign(CellAlign:%FieldAlign)
#SET(%COUNTER,%COUNTER+1)
#ENDFOR
!-----------------------
! set font for all table
%EE.SelectRange(1,1,SR+1+Rows,SC+Cols)
%EE.SetFont('%BodyFont', %BodyFontSize)
%EE.AutoFit(XlFit:Horiz)
! report title format
%EE.SelectRange(SR,SC)
%EE.SetFont('%ReportFont',%ReportFontSize,,FONT:Bold)
! merge report title cell
%EE.SelectRange(SR,SC,SR,SC+(Cols-1))
%EE.MergeCells(CellAlign:Center,CellAlign:Center)
! table title format
%EE.SelectRange(SR+1,SC,SR+1,SC+(Cols-1))
%EE.DrawTable(BoxMode:Table, LineStyle:Continuous, BorderWeight:Thin)
%EE.SetHorizAlign(CellAlign:Center)
%EE.SetFont('%TitleFont',%TitleFontSize,,FONT:Bold)
%EE.SetColour(%TitleBGColor)
%EE.SetVertAlign(CellAlign:Center)
%EE.AutoFit(XlFit:Vert)
%EE.SetPageZoom(100)
%EE.SelectRange(SR,SC,SR,SC)
#EMBED(%BeforeEasyExcelKill,'xNK'),'EasyExcel report','4.Before EasyExcel Kill'
! screen update
%EE.ScreenUpdating(TRUE)
! kill excel
%EE.Kill
!--------------------------------------------------------------------------
#ENDAT
#!======================================================================================================================
#GROUP(%GetFieldLabel)
#CASE(%FieldMode)
#OF('Normal')
#RETURN(%FieldLabel & '-' & %FieldDesc)
#OF('Computed')
#RETURN(%FieldLabelC & '-' & %FieldDesc)
#ENDCASE
#RETURN ''
