Implement your Excel report with Delphi

zhaozj2021-02-16  88

Excel is a familiar table processing software that is more familiar, with it to do free report vectors.

l From the user's angle:

The carrier itself has many features such as script support, formula editing, template support, format, layout design, and these functions are familiar to most users. The user training session is saved; most office computers are all installed with Office software, so the reports of their output can be browsed by any computer with Office software;

l From the developer angle:

You can access a large number of programming interfaces through OLE, almost all operations can be controlled by programs; techniques such as macro, VBA can easily write report design modules.

the whole idea:

The program gets Excel's Sheet object through ole. Traverse each cell, the elements in the template are replaced with variables, tables, etc., generate the final report. The elements can be divided into variable elements, data set elements, data field elements, and other marker elements. They are all characterized in cells, and the format can be freely defined, but to ensure that this format does not conflict with the body.

Such as:

# Valueaa # (Variable Name Add to ##, perform corresponding processing in the program when traversing to its cells)

Report function implementation:

First create an Excel template, as shown below:

Template is saved as template.xlt

Now the template has, the function now need to implement is to analyze this template, replace the elements in the template to the variable or data table that will be output in the program.

Let's take a simple template analysis class (TXLTemplateanalyzer):

Public method:

GenerateReport // Set of templates to generate the final report.

RegisterDataSet // Added data sets in the DataSet list.

Registerparam // Adds variables in the PARAM list.

RemoveDataSet // Remove the dataset in the DataSet list.

RemoveParam // Remove the variable in the PARAM list.

SetsheetObject // Set the template's Sheet object

Private member variable:

FBoundLEFT, FBoundRight / / Save left and right column boundaries

FdataSetList // The private member used to maintain the data set list.

FParamlist // The private member used to maintain the list of variables.

FEXCELSHEETOBJECT // The Sheet object of the template is operated.

Fcurrentrow // Currently handled line number

Fcurrentcol // Currently handled list number

Private tool method:

ProcessDataStelement // Handling Data Set Elements

GetTemplateBoundary // Get boundary index marks

ParsorfieldNameFroMELEment // extracts field names from template elements

Findparam // Find variables for specific NAME from fParamlist

FindDataSet // Find a specific Name of DataSet from DataSetList

IStamplateElement // Judgment is an element

IsDataSetFieldElement // is the data field element

IsDataSetBegineElement // is the DataSet start element

IsControlTAG / / Whether to control element isparamelement // is a variable element

event:

Onparamelement

OnDataSetoperationElement

OnControllelement

OnDataSetfieldElement

OneElement

Write here may already know my intention: In fact, in such a variety of members, their core is both of generateReport and ProcessDataStelement.

(The following code is just a descriptive script code, can't compile in Delphi)

GenerateReport:

------------------ Circular Travel Cells ---------------------

Fcurrentrow: = IROW;

Fcurrentcol: = ICOL; / / Save the ranks that are currently processing

Cellobj: = fExcelsheetObject.cells [iRow, ICOL]; // Take the front cell object

CellValue: = CellValue.Value; // Take a single element

If iStamplateElement (CellValue) // Judgment is a template element

Begin

If isdataSetBegineLement (CellValue) THEN / / Judgment Whether to start element for a dataset

Begin

ProcessDataStelement (CellValue); // Handling Data Set Elements

END;

If ISPAREMEMENT THEN (CELVALUE) THEN / / Judgment is a variable element

Cellobj.value: = FINDPARAM (CellValue); / / Find the variable value, fill in the current cell

IF Iscontroltag (Cellvalue) THEN

....................

END;

------------------ Traversed cycle end ---------------------

Implementation here, the report is completed.

ProcessDataStelement:

Adataset: = FINDDATASET (CELLVALUE);

For j: = 0 to adataset.recordcount - 1 DO

Begin

FEXCELSHEETOBJECT.ROWS [Fcurrentrow J] .delete; // Remove the current line (that is, # Table.Begin () #)

FEXCELSHEETOBJECT.ROWS [Fcurrentrow J] .insert; // New

FEXCELSHEETOBJECT.ROWS [Fcurrentrow J] .copy (fExcelsheetObject.rows [Fcurrentrow 1]);

For i: = fboundleft to fboundLEFT DO

Begin

FIELDCELLSTR: = fExcelsheetObject.Rows [Fcurrentrow J] .cell [i] .value; // Get data field elements

FieldName: = ParsorfieldNameFromElement (Fieldcellstr); // Analyze the field name from the element

FEXCELSHEETOBJECT.ROWS [Fcurrentrow J] .cell [i]: = adataset.fieldbyname (fieldName) .Value; // The value of the // The value of the corresponding field is assigned to the cell end;

Adataset.next;

end

Fcurrentrow: = fcurrentrow j;

Here, the core function of this template analysis is basically completed, I think if the implementation code of this class is complete, you should make this class into a Component, and register to Delphi's IDE for future use. The events mentioned above are used to inform the customer in real time which Element and Cell currently being processed, and the customer can do a further special processing in this event (for example, the output value of the above template is less than the completion plan number). For red, etc.), very flexible, but because of the code is neat, I didn't add the code here. In addition, in the program, you can add an object of various Chart, Shape and other objects of Excel in the report. These will write it later.

TXLTemplateAnalyzer class is called:

Begin

1 Register the data set and variable first.

Templtanalyzer.registerDataSet (Dataset1, 'Table);

Templtanalyzer.RegisterParam (now, 'Date');

Templtanalyzer.registerparam (Totalqu, 'TotalQuantity');

Templtanalyzer.registerparam (Totalplan, 'TotalPlan);

Templtanalyzer.registerParam (Totalper, 'Totalpercence);

2 generate reports

Templtanalyzer.generatReport;

END; Reference document: MSDN: Microsoft Developer Net

Ok, everyone may wish to try it according to my idea, I hope that this article can help you.

The population is not good, it is the first to write documents, it will inevitably be inappropriate, welcome everyone to correct, I will continue to work hard.

转载请注明原文地址:https://www.9cbs.com/read-12171.html

New Post(0)