Home

XLReportCom User Manual

Contents

1. 12 AT ABOUT REPORTS Enn eh MUERTE M RUIN I OVID I I RUE SENE GUNT I S 12 4 2 ABOUT REPORT 12 4 3 EXGEL BASIC CONCEPTS ecce te a o eee d an bade Wek Seco Bean bees an Beene an beds Dek eeu pee bee eo Eee 12 4 3 1 Workbooks and Worksheets cccccccccccccccssssesceccccccusssescececcccsssseseececesscusasseccesecessuueseeceesseuaas 13 ASP OPE TAE chased cease ion eee eines k nka 13 Pa M IO SIN Mr eo DDR na Se TR ees LI BL IDE TUE 13 4 3 4 Headers and Footers a n nhe esse se e esiste 13 d 3 osPage Breaks Soit et eR e e e E Dre ins title 14 4 3 6 Drawings Pictures and nennen ener 14 Z3 Charts pO RUE ERR m ERE 14 43 0 FOrmalting a dai ae eR nan RD ER SR ER EEEMURE nee 14 AA TABLE REPORTS LIS si Mann el lee anse A An ed ds an en en is M een 15 4 4 1 About Table Reports eR e ag e Dei 15 4 4 2 Creating a Worksheet for a Fixed Table Report 16 4 4 3 Creating a Worksheet for a Variable Table Report ss 16 4 4 4 Formatting a Cell for Pictures 18 ASS CHARTS Sn 19 4051 About Charis zorras daten eda ae 19 4 3 2 Creating a Blank Chart iade et ea a eet eee 19 CHAPTER 5 R
2. 78 7 3 2 Inserting pictures int a YepOrt 3s 79 CHAPTER 8 HINTS AND TIPS 80 CHAPTER 9 LICENSE AND SUPPORTA A 84 9T LICENSE E 84 9 2 TECHNICAL S UPPORT eet a e E OD EE NEES Sau filed 85 IV Chapter 1 Introduction 1 1 Overview XLReportCom is a solution that generates reports using Microsoft Excel Using Microsoft Excel and XLReportCom you can create all kinds of reports quickly and easily XLReportCom includes an ActiveX DLL and an executable file that can be used to develop your applications It will significantly accelerate your application development XLReportCom is a template based solution To create a report you need to create a report template file first The report template file is a Microsoft Excel workbook that defines the layouts and formats of a report XLReportCom retrieves data from data source and fills data into Excel workbooks 1 2 Features XLReportCom includes the following features W Using Microsoft Excel as your reporting tool Just use Microsoft Excel as your reporting tool You design reports like layouts formats and styles directly using Microsoft Excel And you will get reports in Microsoft Excel spreadsheet format as a result Microsoft Excel is powerful flexible and familiar You do n
3. record and two means some rows columns for two records Default is 1 FillOrder Optional An integer that indicates the order in which XLReportCom fills data If the value is zero XLReportCom will insert entire rows and fill data by rows 1 means to insert entire columns and fill data by columns 2 means to insert range and fill data by rows 3 means to insert range and fill data by columns Default is 0 ImageList Optional A string that indicates which data source fields are the picture files The mageList is the list of data source fields separated by the character You can identify a field using the name of field or the index number of field but not simultaneously In data source you stored the path and file name of the picture not the picture The file path can be a relative path an absolute path or a URL If it is a relative path the base path is the path of the 25 worksheet PageBreakOptional A string that indicates the page breaks The unit of page length is that means record For example 6r or 6 means that XLReportCom will insert a page break 6 records Default is that means no page break NoData Optional An integer that represents an option when no data are returned from data source If the value is 1 XLReportCom will delete the range when no data are returned If the value is 2 XLReportCom will delete the sheet when no data are returned Default is 0 It means to do nothing Examp
4. XLReportCom will replace SalesDate in SQL statements with 1996 05 01 replace Category with Dairy Products and then submit SQL statements to data sources 6 1 4 Creating an XRF file The XRF file is a text file You can create and modify an XRF file in XLReportGen or a text editor Sometimes you want to make an file programmatically You can write a program to create an file using C perl or DOS shell and then run XLReportCom to generate report The two steps can be written into a batch file 1 Write a program to make the file as you need 2 Write a batch file to call the program and ExcelReport exe For example you write a batch file runrpt bat as follows changexrf is an executable file that reads template txt and output template xrf First runrpt bat call changexrf to make the XRF file and then call ExcelReport exe to generate the report echo off if 1 goto usage 41 goto process usage echo Usage runrpt ReportDate echo ReportDate Date format YYYY MM DD goto EOF process changexrf 1 template txt template xrf ExcelReport template xrf 1 6 1 5 Using parameters You can use parameters the XRF file You can pass values to XLReportCom when it processes an XRF file XLReportCom will replace the parameter names with the actual values You can use the parameters in the SQL statements and the paths and names of the files To use a parameter you mu
5. Category Example Get the information from the database table and column that you identify when the report is generated 1 Defining parameters Define parameters as follows Name1 Database Title1 Database Name Default Name2 Table Title2 Table Name Default2 Name3 Column Title3 Column Name Default3 2 Using parameters You can use the parameters Database Table and Column in SQL statements For example USE Database or DATABASE Database SELECT Column FROM Table Example Use parameters in the path and name of the report file and the log file 44 1 Defining parameter Define a parameter as follows Name CustomerlD Title Customer ID Default C000001 2 Using a parameter ReportFileName reportWeport CustomerlD xls LogFileName logWeport CustomerlD log or ReportFileName report Customer D report xls LogFileName log Customer D report log 6 1 6 Converting files You can convert a file from Microsoft Excel to another file format or from another file format to Microsoft Excel For example the template file is a Lotus 1 2 3 file with wk3 extension and the report file is a HTML file with extension For most file formats Microsoft Excel converts only the active sheet To convert the other sheets open the template file switch to the sheet you want to save and save it The file formats XLReportCom supports can be of these What file format XLReportCom supports
6. an file CROIRE RR EEE Ee eke CN ERGO KIT KORR E E Rok Compare with Last Month by Categories KK AKA OOHEROHERIOHEROHER OH ODER OH IEA Drop table tmp category sales F9_1 EXECSQL DROP TABLE tmp_category_sales Create table tmp_category_sales F9_2 EXECSQL CREATE TABLE tmp category sales CategoryID INTEGER Quantity INTEGER Amount MONEY Get the sales amount by categories in the current month F9_3 EXECSQL INSERT INTO tmp category sales CategoryID Quantity Amount SELECT p CategoryID SUM d Quantity Sum d UnitPrice d Quantity 1 d Discount FROM Orders o OrderDetails d Products WHERE o OrderlD d OrderlD AND d ProductID p ProductID AND YEAR o OrderDate YEAR 1996 04 01 68 AND MONTH o OrderDate 1996 04 01 GROUP BY p CategoryID Show the sales amount by categories in the current month F9_4 REPORT sheet Report9 type var cell B8 reserve 2 SELECT c CategoryName IIF ISNull t Quantity 0 t Quantity ITF IsNull t Amount 0 t Amount FROM Categories c LEFT JOIN tmp_category_sales t ON c CategoryID t CategoryID ORDER BY c CategoryName Delete from table tmp_category_sales F9_5 EXECSQL DELETE FROM tmp_category_sales Get the sales amount by categories in the last month F9_6 EXECSQL INSERT INTO tmp category sales CategoryID Quantity Amount SELECT p CategoryID SUM d Quantity Sum d
7. object Required The object is the XLReport object Recordset Required An object variable that represents the ADODB Recordset object to provides data Before calling this method please keep the current record position to the first record Worksheet Required An object variable that represents the Excel Worksheet object to be filled data 24 CellList Required A string that represents the list of cells separated by the character For example A2 B2 B3 D2 D3 The cells in the CellList should correspond to the data source fields in the recordset The value of the first field is put into the first cell and the value of the second field is put into the second Range Optional A string that indicates the range in the worksheet to be used for the records XLReportCom will repeat the range for each record You can reference a range of cells like 2 4 or B2 D5 The default range is the area that includes all cells for the records Copy Optional An integer that indicates whether XLReportCom will copy the range for each record If the value is zero XLReportCom will insert the blank rows columns of the range for each record Otherwise it will copy the source range and insert the copied range for each record Reserve Optional An integer that indicates the number of records for which you reserved some rows columns in the report template for the report Possible values are 1 or 2 One means you reserve some rows columns for
8. 0 Then If FunctionType lt gt 0 Then Ignore errors of EXECSQL MsgBox ErrObj Description voExclamation App ProductName mintErrCount mintErrCount 1 End If End If End Sub 5 3 6 FunctionProgress Event Occurs periodically during a function processing Syntax Private Sub object_FunctionProgress ByVal Progress As Long ByVal RecordCount As Long object The object is the XLReport object Progress long that indicates the number of records that have currently been processed RecordCount long that indicates the total number of records Example 537 Private Sub mxirpt_FunctionProgress ByVal Progress As Long ByVal RecordCount As Long frmWait IblRecordCnt Caption Format Progress 0 amp amp Format RecordCount 0 End Sub 5 4 Error Messages The following table lists the trappable errors for the XLReport Object Value 2147221493 2147221492 2147221491 2147221490 2147221489 2147221488 2147221487 2147221486 2147221485 2147221473 2147221453 2147221451 2147221450 2147221403 2147221393 2147221392 2147221391 2147221383 Description The file XrfFileName does not exist The file XrfFileName is not an ExcelReport file Error in reading the file XrfFileName Report template file TemplateFileName does not exist The report file is not named correctly Failed to create the report file ReportFileName Failed to open the template file TemplateFileName Fa
9. SQL section in the file you can use comments A comment is the characters followed by any sequence of characters including new lines 81 followed by the characters You cannot nest comments To add totals or subtotals you can use the functions of Microsoft Excel or aggregate functions in SQL statement To group data in a report you should use GroupTableReport method or Group Table Report function In Group Table Report function in the XRF file the order of groups should be in accordance with the order of ORDER BY clause in the SQL statement You can create reports with pictures using XLReportCom You should store the path and name of the graphics file in the database identify the image fields in the report function and specify the positioning option and size in the report template file To convert from pixels to points it is depend on the screen resolution If you have a 96 dpi screen Windows PC 4 pixels are equal to 3 points It is very useful to create a temporary table You can prepare data using INSERT UPDATE DELETE INSERT SELECT and then make a report using REPORT function You can write a program to make an file using perl or DOS shell and then run ExcelReport exe to generate report The two steps can be written into a batch file In general group variable table report is slower than non group variable table 82 report But if the ranges of al
10. The sqlstatement is a SQL statement You can use comments in SQL section A comment is the characters followed by any sequence of characters including new lines followed by the characters You cannot nest comments 6 2 2 Data Source Section The Data Source section contains information how to connect to data 49 sources lt gt 2 lt 2 gt Name 10 lt name10 gt These settings specify the names of data sources you want to connect to specifies the name of the first data source Name2 specifies the name of the second data source You can define up to 10 data sources in one file You can make a connection to a data source using an ODBC data source name or a connection string Even if you use a connection string to make a connection you should define a name that you can reference in functions User1 lt username1 gt User2 lt username2 gt User10 lt username 10 gt These settings specify the user names If you use an ODBC data source name to make a connection you should define user name and password If you use a connection string to make a connection XLReportCom will ignore the setting User1 specifies the user name of the first data source User2 specifies the user name of the second data source They are optional settings If defined default user and password in ODBC data source you may not define them Password1 lt passwo
11. 2 XREEIEEREFERENGE 2 5 E E ne Q ama q 48 0 2 1XRF Ftl O T 48 6 2 2 Datd Source SectiOn c is Etpe der epu eee tst emule 49 6 2 3 PILED Section en ere DR RE RETE EM Ge eI OR ee HaT E Seo Eve ie ve 52 0 24 PARAMETER Y Se6tiOR a ER oec teret e u Q vere de ise Se eph Ete da o q erii ees 53 6 3 FUNCTION REFERENCE aan aaa E REI OA d Ee ERR EUR ee fat i ERR REN RN 54 6 3 1 Fixed Table ia e a ere PR 54 6 3 2 Varidble T ble Report sett e ede e E t m ede tee 57 6 3 3 Group Table Report Ed eee ede resp eed Eee Prati ttd edet 60 0 3 4 Name s RE v DG CR OE REG e ROG RE D CER REGES 63 0 39 EXECS OPER 65 CHAPTER 7 ADVANCED REPORTS 67 7 1 EXECUTING MULTIPLE SQL STATEMENTS eee 67 HZ USING EXCEL FORMULAS na na Sn prre RI MR HERRERA Pe IEEE Ere E 70 7 3 SORTING GROUPING AND 73 LIT Sorting AGIAN etit 73 FB ZT OCDUIG sve e tt Secu uate des 74 7 3 3 Grouping data and Subreports eee 74 i etie De eee one deitas 75 TA CHARTING ra et eene m nee dyes 76 TA PICTURES E MH 78 7 5 1 Inserting pictures into a report template
12. 5 T 10 2 Write the code your application Set con New ADODB Connection Set rec New ADODB Recordset con ConnectionString Data Source Report Sample con Open strSQL SELECT LEFT CompanyName 1 CompanyName ContactName Phone amp Phone amp Fax Address CityName amp amp CountryName PostalCode FROM Customers Cities Countries WHERE Customers CityCode Cities CityCode AND Customers CountryCode Cities CountryCode AND Customers CountryCode Countries CountryCode ORDER BY CompanyName rec Open strSQL con xlrpt GroupTableReport Recordset rec Worksheet xlWorksheet CellList A6 B7 C7 D7 D8 E7 E8 E9 Range 6 9 Group1 1 PageBreak 6r rec Close 3 Generate the report 30 Customer Name ContactName FhonelFux Address BJA T Alfred Futterkizin Piara Aunders Prone 0 0074327 Obar Str amp B CHINESE Berlin Gemia 9 127400 12 1 i Esparedadas y helados Arma Prone F 555 47 29 Avda de la 227272 2 Fax 255 3745 Meco DF Mexico 15 EDM 1418 15 Gerglands Christina Bargund Pron 521 12 3465 Gerguesvagen B 16 821 72 24 Lula Sweden AT 18 18 See Hanna Ilocos Phone Db 1H EHEL Gr a Fax 0021 09924 Mannheim Germany E 5 2 4 SetExcelName Method Gets data from a recordset object an
13. BeginDate EndDate rec Close 5 2 5 ExcelReport Method Generates the reports based on the templates and a XRF file The XRF file tells XLReportCom how to get data from data sources and how to put data into the reports Syntax object ExcelReport Application XrfFile Param1 Param10 object Required The object is the XL Report object Application Required An object variable that represents the 239 Excel Application object XrfFile Required A string that represents the XRF file You can include a full path Param1 Param10 Optional A string that represents the paramters These parameters have been defined in the XRF file Example This example uses ExcelReport method to make the report Customer List 1 Create the template customer list xls using Microsoft Excel 2 Create the file customer list xrf using a text editor 3 Write the code in your application Set New Excel Application Set New XLReport Call xirpt ExcelReport xlApp customer list xrf 5 3 Events 5 3 1 BeforeConnect Event Occurs before a connection starts Syntax Private Sub object BeforeConnect User D As String Password As String DataSource As String Connection As ADODB Connection object The object is the XLReport object UserID A string that represents a user name for the connection Password string that represents a password for the connection DataSource string that represents a
14. Description Extension xlOpenXML Workbook 51 Excel Workbook 8 xlOpenXMLWorkbookMacroEnabled 52 Excel Macro enabled xlsm Workbook 2 50 Excel Binary Workbook xlsb xlExcel8 56 Excel 97 2003 Workbook xls xlWorkbookNormal 4143 Excel 97 2003 xls WorkbookNormal xlOpenXMLTemplateMacroEnabled 53 Excel Macro enabled Workbook Template xlOpenXMLTemplate 54 Excel Template xltx xITemplate 17 Excel 97 2003 Template xlOpenXMLAdaln 55 Excel Add in xlam XIAddIn 18 Excel 97 2003 Add In xla XIHtml 44 Web Page htm html xlWebArchive 45 Single File Web Page mht mhtml xIXMLSpreadsheet 46 XML Spreadsheet xml xICSV 6 CSV comma delimited CSV xICSVMac 22 CSV comma delimited CSV Macintosh xICSVMSDOS 24 CSV comma delimited CSV MS DOS xICSVWindows 23 CSV comma delimited CSV Windows xlCurrentPlatformText 4158 Text Tab delimited txt xITextMac 19 Text Tab delimited txt Macintosh xIText MSDOS 21 Text Tab delimited txt 47 MS DOS xIText Windows 20 Text Tab delimited txt Windows xITextPrinter 36 Formatted Text prn Space delimited xlUnicodeText 42 Unicode Text txt xlExcel5 39 Microsoft Excel 5 0 95 xlw Workbook XIDIF 9 DIF data interchange dif format xISYLK 2 SYLK symbolic link slk format Note Some of these file formats may not be available to you depending on the language support U S English for example that you ve selected or in
15. IMAGE argument specifies the fields are picture files The fieldlist is the list of data source fields separated by the character You can identify a field using the name of field or the index number of field but not simultaneously In data source you stored the path and file name of the picture not the picture The file path can be a relative path an absolute path or a URL If it is a relative path the base path is the path of the report template file The RESERVE argument specifies the number of the records for which you reserved some rows columns in the report template for the report The reserverecords represents the number of the records you reserved in the 58 report template Possible values are 1 2 One means you reserved some rows columns for one record and two means some rows columns for two records Default is 1 The PAGEBREAK argument specifies the page breaks The unit of page length is that means record For example 6r or 6 means that XLReportCom will insert a page break per 6 records Default is no page break The NODATA argument specifies an option when no data are returned from data source If the value is delrange XLReportGen will delete the range when no data are returned If the value is delsheet XLReportGen will delete the sheet when no data are returned Default is to do nothing The CONNECT argument specifies the connection to a data source The CONNECT can takes a string that expresse
16. XP and Windows 2003 you do not need to install MDAC_TYP because it is preinstalled OS To install just run mdac_typ exe and follow the instructions 2 3 Uninstalling XLReportCom 1 Double click the Add Remove Programs icon in the Windows Control c3 Panel 2 Do one of the following For Windows 2000 Windows XP and Windows 2003 Edition Click XLReportCom in the Currently installed programs box and then click the Change Remove button For Windows 98 and Windows 4 0 Click XLReportCom on the Install Uninstall tab and then click the Add Remove button 3 Follow the instructions on the screen to complete uninstalling the program 2 4 XLReportCom dll XLReportCom dll is an ActiveX DLL that provides XLReport object You can write a program to work with the object Before you can use the XLReport object you must create a reference to the object And you should create references to Microsoft Excel Object Library too To create a reference to the XLReport object 1 Do one of the following W For Visual Basic 6 0 From the Project menu choose References W For Microsoft Excel Visual Basic For Application From the Tools menu choose References 2 In the References dialog box select XLReportCom You can use the Browse button to search for XLReportCom dll 4 In the References dialog box Select Microsoft Excel Object Library to create their references 5 Declare an object variable of
17. add subtotals you can use the functions of Microsoft Excel or aggregate functions in SQL statement 1 You can add sub totals using the math functions of Microsoft Excel such as SUM 1 The range of the SUM function should contain the cells for the details in the report template file 2 The range of SUM function must contain at least one row column that is not included in the range for the details For example the row 13 is for the details you should add blank row 14 and write the function as SUM H13 H14 If you 75 want to show the blank row the report you may hide the row 3 You should use the relative references For example SUM H13 H14 Microsoft Excel will change the function automatically when XLReportCom adds some rows in the report 2 If you want to have a total and sub totals 1 You can add the total using SUMIF function The range of SUMIF function must contain one row column that is not included in the range of the group For example the range of the group is rows 1 15 you should add blank row 16 and write the function as SUMIF G G Subtotal H1 H16 You may hide the blank row 2 You can add the total using the aggregate function in SQL statement You must add the total first using a Fixed Table report function before you use the Variable Table report function Because the cell address of the total field will change after you use Variable Table report function 3 You can add sub total
18. cells for the details For RANGE argument XLReportCom will insert the blank rows columns of the range for each record For COPYRANGE argument it will copy the original range and insert the copied range for each record But if the range of any group is not same as the range of the details RANGE is same as COPYRANGE The GROUP argument specifies the group of the report The grouplist is the list of data source fields separated by the character You can identify a field using the name or index number of the field but not simultaneously In one report there may be up to 10 groups Notes the order of the groups should be in accordance with the order of the ORDER BY clause in the SQL statement 61 The GROUPRANGE argument follows the GROUP argument and specifies the range of the group in the worksheet For example the grouprange of level 1 must follow the group of level 1 and the grouprange of level 2 must follow the group of level 2 XLReportCom will repeat the group range for each group The range of the group should contain the range of the details and the area that includes all cells for this group You reference a group range like 2 4 or B2 D5 For example there are two groups the range of the group one contains all cells for the group one and the range of the group two and the range of the group two contains all cells for the group two and the range of the details The default range is the area that includes all cel
19. de la Constituci n 2222 M xico D F Mexico 5021 5 2 3 GroupTableReport Method Generates a variable table report based on a template and groups data in the report In a variable table report the number of rows or columns in the table is unfixed and it is variable as the number of the result records XLReportCom gets data from a recordset object copy the group range for each group and 27 copy the detail range for each record Syntax object GroupTableReport Recordset Worksheet CellList Range FillOrder ImageList PageBreak NoData Group1 GroupRangelt Group10 GroupRange10 object Required The object is the XLReport object Recordset Required An object variable that represents the ADODB Recordset object to provides data Before calling this method please keep the current record position to the first record Worksheet Required An object variable that represents the Excel Worksheet object to be filled data CellList Required A string that represents the list of cells separated by the character For example A2 B2 B3 D2 D3 The cells in the CellList should correspond to the data source fields in the recordset The value of the first field is put into the first cell and the value of the second field is put into the second Range Optional A string that indicates the range in the worksheet to be used for the details XLReportCom will repeat the range for each record You can reference a range of
20. footer on each worksheet If you create a new custom header or footer it replaces any other custom header or footer on the worksheet 4 3 5 Page Breaks Microsoft Excel will break pages automatically You can manually insert horizontal or vertical page breaks 4 3 6 Drawings Pictures and Diagrams You can add graphics to your worksheets and charts to make them more visually appealing to create eye catching reports or to add emphasis For example you can display a logo on your worksheet create a flowchart and use graphics in chart data markers You can make your worksheet interactive by using graphic objects as hyperlinks or by assigning buttons to macros 4 3 7 Charts Charts are visually appealing and make it easy for users to see comparisons patterns and trends in data To create a chart you must first enter the data for the chart on the worksheet Then select that data and create a chart A chart is linked to the worksheet data it s created from and is updated automatically when you change the worksheet data 4 3 8 Formatting You can use these formatting features of Microsoft Excel to effectively display your data W Format text and individual characters To make text stand out you can format all of the text in a cell or selected characters You can set font color alignment of the text 14 m Rotate text and borders The data in a column is often very narrow while the label for the column is much wider Ins
21. formula of total amount is changed to SUM D7 D14 Category Name Quantity Amount Seafood oo 669 9 316 54 7 3 Sorting Grouping and Totaling 7 3 1 Sorting data Sorting means placing data in some kind of order to help you find and evaluate it For example you may want to have a customer list sorted alphabetically by name or by country To sort your data you may use SQL Use the ORDER BY clause to have your results displayed in a sorted order SELECT EmployeelD LastName 273 FirstName HireDate FROM Employees ORDER BY HireDate ascending sort In the example above results will come back in ascending order by hire date To explicitly specify ascending or descending order add ASC or DESC to the end of your ORDER BY clause The following is an example of a descending order sort ORDER BY HireDate DESC descending sort 7 3 2 Totaling You can sum the values count all the values or only those values that are distinct from one another and determine the maximum minimum average To add totals there are two ways 1 You can add the totals using the math functions of Microsoft Excel such as SUM For more detail information refer to Using Excel Formulas in this document 2 You can use the aggregate functions in SQL statement such as COUNT SUM AVG MAX MIN 1 In the fixed table report you can add a total directly using a separate SQL 2 In the variable table report you must
22. is put into the second name The CONNECT argument specifies the connection to a data source The CONNECT can takes a string that expresses a data source name or a number that expresses a data source index The index number of data source is the sequential number defined in the XRF file and starts at 1 The default implies the first data source The sglstatement is a SQL statement such as a SELECT statement Example This example uses Name function to assign the values of min_date and max_date to the names BeginDate and EndDate F1 NAME NAME BeginDate EndDate SELECT min_date max_date FROM 64 6 3 5 ExecSQL Executes SQL statement but no data is returned to the report Syntax ExecSQL salstatement Arguments datasource The CONNECT argument specifies the connection to a data source The CONNECT can takes a string that expresses a data source name or a number that expresses a data source index The index number of data source is the sequential number defined in the file and starts at 1 The default implies the first data source The sqlstatement is SQL statement that can be DDL Data Definition Language DML Data Manipulation Language and even DCL Data Control Language Using EXECSQL function you can open a database create a temporary table insert data into a temporary table update data execute a stored procedure and drop a table It is very useful to create a temporar
23. one more worksheets which you can use to organize various kinds of related information You can enter and edit data on several worksheets simultaneously and perform calculations based on data from more than one worksheet When you create a chart you can place the chart on the same worksheet as its related data or on a separate chart sheet Worksheet is the primary document that you use in Microsoft Excel to store and work with data It also called a spreadsheet A worksheet consists of cells that are organized into columns and rows a worksheet is always stored ina workbook 4 3 2 Formulas Formulas are equations that perform calculations on values in your worksheet A formula starts with an equal sign A formula can contain any or all of the following functions references operators and constants You can perform calculations using formulas 4 3 3 Names name is word or string of characters that represents a cell range of cells formula or constant value Use easy to understand names such as Products to refer to hard to understand ranges such as Sales C20 C30 4 3 4 Headers and Footers Headers and footers are areas in the top and bottom margins of a worksheet You can add a header and footer on each worksheet You can insert a page number date and time graphic file name in a header and footer and change 13 the font in header footer text You can have only custom header and one custom
24. run ExcelReport exe as follows excelreport c excelreport myreport xrf 1996 05 01 Dairy Products 2 6 Run Time Files You can distribute royalty free the run time files of XLReportCom with your applications The run time files are files your application must have in order to work correctly after installation The following are the run time files you need to distribute File Description xlreportcom dll The XLReportCom ActiveX DLL It must be registered xconv cfg The file contains the information of the file format If you are using ExcelReport method to convert files you should include it and copy it to the same directory as xlreportcom dll scrrun dll Microsoft script runtime XLReportCom used some functions in this file It should be copied to Windows System directory and must be registered To register a DLL file use regsvr32 exe For example regsvr32 exe s C Program Files LJZsoft XLReportCom XLReportCom dil Chapter 3 Quick Start 3 1 Learning how to use XLReportCom You can teach yourself how to use XLReportCom by choosing from the methods available in this section You can study the samples included with XLReportCom You use the detailed descriptions and instructions in this document 3 2 Sample Database XLReportCom comes with Sample mdb a sample database you can use when learning the program Sample mdb is a Microsoft Access database Virtually all o
25. the object s class Dim As XLReport 6 Assign an object reference to the variable by using the New keyword in a LAs Set statement Set New XLReport 2 5 ExcelReport exe ExcelReport exe is an executable program that developed using XLReportCom dll It likes XLReportGen command line and can read an XRF file to create an Excel report The syntax of command is excelreport file name D U1 user1 P1 pwd1t U10 user10 P10 pwd10 pa2 pa10 xrf file name Specifying an XRF xrf file that tells XLReportCom how to get data from data sources and how to put data into a report D Display the generated report with Microsoft Excel U1 user1 Specify the user names user1 is the user name of the first U10 user10 data source user2 is the user name of the second data SOUICE P1 pwd1 Specify the passwords pwdi is the password of the first data P10 pwd10 source pwd2 is the password of the second data source pa pa O values of the parameters defined in the XRF file You can use parameters SQL statements XL ReportCom will replace the names of the parameters in a SQL statement with the actual values before it executes the SQL statement You can use no more than 10 parameters in one report For example you have defined two parameters in your XRF file The first parameter is the sales date and the second is the category of the product You can
26. to put data into a report Using the XRF file it will simplify your development 6 1 2 Using an XRF file with XLReport Object XLReport object provides the ExcelReport method to generate a report based on an XHF file For example you have created the XRF file myreport xrf and the template file In the XRF file there are two parameters The first parameter is the sales date SalesDate and the second is the category of the products Category You can call ExcelReport method to generate the report Set New Excel Application Set New XLReport Call xirpt ExcelReport xlApp c excelreport myreport xrf 1996 05 01 Dairy Products XLReportCom will replace SalesDate SQL statements with 1996 05 01 replace Category with Dairy Products and then submit SQL statements to data sources 40 6 1 3 Using an XRF file in command line In the XLReportCom there is an executable file ExcelReport exe that can read an file to generate a report It is the same as XLReportGen command line For example you have created the XRF file myreport xrf and the template file In the XRF file there are two parameters The first parameter is the sales date SalesDate and the second is the category of the products Category You can run ExcelReport exe in command line mode as follows excelreport c excelreport myreport xrf 1996 05 01 Dairy Products
27. Ci Country Contact B2 2 Write the code in your application Set con New ADODB Connection Set rec New ADODB Recordset con ConnectionString Data Source Report Sample con Open strSQL SELECT CompanyName CityName CountryName ContactName FROM Customers Cities Countries WHERE Customers CityCode Cities CityCode AND Customers CountryCode Cities CountryCode AND Customers CountryCode Countries CountryCode ORDER BY CompanyName CityName CountryName rec Open strSQL con VarTableReport Recordset rec Worksheet xlWorksheet CellList A3 Reserve 2 rec Close 3 5 Creating a Report with ExcelReport exe 1 Create a template In Microsoft Excel create a report template file named custlist xls Static values and any Excel features included in the template will be included in the generated report The template file you have created as follows EE SSS B E D 2 Create an XRF file Create an XRF file named custlist xrf using XLReportGen or a text editor The following is the content of the XRF file ExcelReport Version 2 0 Data Source Name1 Report Sample File ReportTemplateFileName custlist xls ReportFileName Report custlist xls LogFileName Log custlist log SQL F1 Report sheet 1 cell A3 reserve 2 SELECT CompanyName CityName CountryName 10 ContactName FROM Customers Cities Countries WHERE Customers CityCode
28. Cities CityCode AND Customers CountryCode Cities CountryCode AND Customers CountryCode Countries CountryCode ORDER BY CompanyName CityName CountryName 3 Run ExcelReport exe excelreport c report custlist xrf 11 Chapter 4 Report Templates 4 1 About Reports The report generated by XLReportCom is a Microsoft Excel workbook that contains one or more worksheets The layouts formats and styles of the report are defined by a report template and the data of the report are got from databases such as Oracle DB2 4 2 About Report Templates To make a report using XLReportCom you should create a report template first This report template is a Microsoft Excel workbook that defines the layouts formats and styles of the report In the Microsoft Excel report template you can input static contents such as titles descriptions comments a cover a company logo format the static content and define the format of the cells you will fill data XLReportCom will generate the report based on the report template file All static contents and the layouts formats and styles defined in the report template file will be brought to the final report file 4 3 Excel Basic Concepts If you have known these concepts of Microsoft Excel please skip this section For more detail information about Microsoft Excel refer to Microsoft Excel Help 4 3 1 Workbooks Worksheets A Microsoft Excel workbook is file that contains
29. EFERENCE 21 SL OBIECTS D 21 DLT XLReport t e aer nt 21 2 2 METHODS sS NRA ter ee Loue toc Us 21 5 2 1 FixTableReport Method 21 3 2 2 VarTableReport Method ss usines sda aaa eaae u usuy 24 5 2 3 GroupTableReport Method 27 5 2 4 SetExcelName Method y er er hee E b SER REDE RS 31 3 2 9 ExcelRep rt Method eeepc 32 5 VENTS sent der ete t e e d tete are it sa es 33 9 3 1 BeforeConnect Event sereisas era ER ones sde ins ele it 33 9 3 2 FemplateO pen Event oe aae eer de rii er 34 3 33 ReportComplete EVent ugue ebrei aon eq Ree 35 5 3 4 FunctionBeforeExectue Event ss 35 5 3 5 FunctionAfterExectue Event etat et erae eet P deed 36 5 3 6 FunctionProgyess Event io ue e ee ect be 37 54 ERROR MESSAGES suu aap anun luan SS p Rd 38 CHAPTER 6 XRF 40 6 1 USING ARE RILES enero UR OA OR tene Pur ren re E S atis 40 Od L About XRF saa ted ee n e ul eT ee sin 40 6 1 2 Using an XRF file with XLReport Object 40 6 1 3 Using an XRF file in command line ss 41 0 1 4 Creating XRF files ip 41 6 1 5 Using parameters eres 42 6 1 6 Converting fesses ane ad D a a 45 6
30. The FILLORDER argument specifies the order in which XLReportGen fills data 57 Possible values are row col rowrange colrange row means to insert entire rows and fill data by rows col means to insert entire columns and fill data by columns rowrange means to insert range and fill data by rows colrange means to insert range and fill data by columns Default is row The CELL argument specifies the positions where data values will be inserted The celllist is the list of cells separated by the character For example A2 B2 B3 D2 D3 The cells in the celllist should correspond to the data source fields in the SQL statement The value of the first field is put into the first cell and the value of the second field is put into the second cell XLReportCom will use the next cell if you omit a cell except the first cell If FILLORDER row the next cell is the right cell If FILLORDER col the next cell is the below cell The RANGE or COPYRANGE argument specifies the range in the worksheet to be used for the records XLReportCom will skip or repeat the range for each record You can reference a range of cells like 2 4 or B2 D5 The default range is the area that includes all cells for the records For RANGE argument XLReportCom will insert the blank rows columns of the range for each record For COPYRANGE argument it will copy the original range and insert the copied range for each record The
31. Unicode Text txt Office2000 xIExcel2 16 Microsoft Excel 2 0 xls Office97 Worksheet xlExcel2FarEast 27 Microsoft Excel 2 0 xls Office97 Worksheet Far East xIExcel3 29 Microsoft Excel 3 0 xls Office97 Worksheet xIExcel4 33 Microsoft Excel 4 0 xls Office97 Worksheet xIExcel4 Workbook 35 Microsoft Excel 4 0 xlw Office97 Workbook xlExcel5 39 Microsoft Excel 5 0 95 xlw Office97 Workbook 9795 43 Microsoft Excel 97 2003 amp xls Office2000 5 0 95 Workbook XIDBF2 7 DBF 2 dBASE Il dbf Office97 XIDBF3 8 DBF 3 dBASE III dbf Office97 XIDBF4 11 DBF 4 dBASE dbf Office97 XIDIF 9 DIF data interchange dif Office97 format XISYLK 2 SYLK symbolic link format slk Office97 46 XIWJ2WD1 14 WD1 1 2 3 wd1 Office97 XIWK1 5 WK1 1 2 3 wk1 Office97 XIWK1ALL 31 WK1 ALL 1 2 3 wk1 Office97 XIWK1 FMT 30 WK1 FMT 1 2 3 wk1 Office97 XIWK3 15 WK3 1 2 3 wk3 Office97 XIWK3FM3 32 WK3 FM3 1 2 3 wk3 Office97 XIWK4 38 WK4 1 2 3 wk4 Office97 XIWKS 4 WKS Works wks Office97 XIWorks2FarEast 28 Works Far East wks Office97 XIWQ1 34 WQ1 Quattro Pro DOS wq1 Office97 For Microsoft Excel 2007 please copy xconv2007 cfg to xconv cfg This file contains the information of file formats for Microsoft Excel 2007 File Format Name Value
32. UnitPrice d Quantity 1 d Discount FROM Orders o OrderDetails d Products WHERE o OrderlD d OrderlD AND d ProductID p ProductID AND o OrderDate gt DateAdd m 1 1996 04 01 AND o OrderDate lt 1996 04 01 GROUP BY p CategoryID 69 Show the sales amount by categories the last month F9_7 REPORT sheet Report9 type fix cell E8 SELECT IIF IsNull t Quantity 0 t Quantity IIF IsNull t Amount 0 t Amount FROM Categories c LEFT JOIN tmp category sales t ON c CategoryID t CategoryID ORDER BY c CategoryName 3 Generate the report EUR PEER IEEE DIRE SERIEN RUE AC Compare with Last Month by Categories 8 Limited Current Month Last Month Quantity Amount Quantity Amount Category Name Dairy Products 581 13 685 33 506 511 454 50 roduce Seafood 669 931655 584 1253112 0 E 288 104 854 1 103 519 69 7 2 Using Excel Formulas Formulas are equations that perform calculations on values in your worksheet A formula starts with an equal sign For example the following formula multiplies 2 by 3 and then adds 5 to the result 5 2 3 A formula can also contain any or all of the following functions references operators and constants For more detail information about formulas functions and references refer to Microsoft Excel Help 70 In report template file you can use all kind of Microsoft Excel formulas An
33. XLReportCom User Manual Version 1 4 2009 07 Copyright 2007 2009 LJZsoft Corporation All rights reserved Contents CHAPTER 1INTRODUGCTIOQON esos ce eco euet s eo dati ao vast eo vo a eee odsusvessssateos SUPR Ve e uuu ra OE ge neo aro 1 VA OVER VIEW CC 1 T2 EEATURESGZa 4 cobs cect shun a tee e Ted GI as Gb en NS Mec Be ele edd aa 1 CHAPTER 2 INSTALLATION AND STARTUP 3 2 1 SOFTWARE REQUIREMENTS Se 3 2 2 INSTALEING XLREPORTEOM L uuu 3 2 3 UNINSTAELINGXLREPORTCOM 2 a saa isu namna codes revente Soles oe uten oe ede evt eae ee dete e Poe dense 3 2 4 XLREPORTCOM DLL PR 4 2 5 EXCEEREPORT EXE inan SSL ede ce ete be ne so 5 2 6 RUNTIME PLES irre ROTE VATI ER RS a a HES PORTER EE EVER D 6 CHAPTER 3 QUICK START 5 eeees coe eene ae saco e aaa sdeseioes 7 3 1 LEARNING HOW TO USE 7 3 2 SAMPLE DATABASE hei edi eec eei ec dec edere e ete di de decide dcr sp eds 7 3 5 0 P en ne en 8 3 4 CREATING A REPORT PROGRAMMATICALLY 9 3 5 CREATING A REPORT WITH 10 CHAPTER 4 REPORT TEMPLATES
34. add the total first using a Fixed Table report function before you use the Variable Table report function Because the cell address of the total field will change after you use Variable Table report function 7 3 3 Grouping data and Subreports Grouped data is data that is sorted and broken up into meaningful groups In a customer list for example a group might consist of all those customers living FA the same Region To group data in a report you should use GROUP TABLE REPORT function For more detail information refer to GroupTableReport Method and Group Table Report in this document Using the feature of grouping data you can make subreports within a report A subreport would typically be used to perform one to many lookups such as Customer Order OrderDetails To make sub reports within the main report 1 Write a JOIN SQL statement to access data from two or more tables For example you can join Customers Orders and OrderDetails tables 2 Use GROUP TABLE REPORT function For more detail information refer to the samples invoice xrf product_catalog xrf and sales_detail xrf within XLReportCom 7 3 4 Subtotaling A subtotal is a summary that totals or sums numeric values in a group You can sum the values in each group count all the values in each group and determine the maximum minimum average in each group For example determine the total sales per sales representative in a sales report To
35. atement Example This example uses Fixed Table Report function to make the report Top 5 Employees for Sales F1 REPORT sheet Report6 type fix cell B7 SELECT TOP 5 e FirstName e LastName SUM d Quantity sum d UnitPrice d Quantity 1 d Discount AS SalesAmount FROM Orders o OrderDetails d Products Employees WHERE o OrderlD d OrderlD AND d ProductlD p ProductID AND o EmployeelD e EmployeelD AND YEAR o OrderDate 1996 AND MONTH o OrderDate 04 GROUP BY e FirstName e LastName ORDER BY 3 DESC 56 6 3 2 Variable Table Report Uses VarTableReport method to generate a variable table report In a variable table report the number of rows or columns in the table is unfixed and it is variable as the number of the result records XLReportCom executes a SQL statement to get data from data source inserts some blank rows columns or copy a range for each record then fills data into the cells of a worksheet Syntax Report salstatement Arguments TYPE SHEET sheet FILLORDER fillorder CELL celllist RANGE range IMAGE fieldlist RESERVE reserverecords PAGEBREAK pagelength NODATA nodataoption CONNECT datasource The TYPE argument specifies the report type var means a variable table report Default is var The SHEET argument identifies a worksheet in the report template The sheet is the name or index number of the worksheet The index number starts at 1
36. aved in plain text 51 6 2 3 FILE Section FILE section contains information about files ReportTemplateFileName lt templatefilename gt This setting specifies the name of the report template file lt templatefilename gt value is the name and path of the report template file The file path can be a relative path or an absolute path If it is a relative path the base path is the path of the XRF file ReportFileName lt reportfilename gt This setting specifies the name of the report file lt reportfilename gt value is the name and path of the report file The file path can be a relative path or an absolute path If it is a relative path the base path is the path of the XRF file In lt reportfilename gt you can use parameters ReportFile Type reportfiletype This setting specifies the type of the report file reportfiletype value is the name or value of the file format For example xICSV 6 What file format XLReportCom supports is dependent on your Microsoft Excel ProtectReport Y N This setting specifies whether the report generated is protected If the value is Y the report is protected and can not be modified If the value is N the report is not protected Default is N ProtectionPassword lt protectionpassword gt This setting specifies the password that is used to protect the report 52 lt protectionpassword gt value is the password This setting is valid when ProtectReport is Y If
37. cells like 2 4 or 2 05 The default range is the area that includes all cells for the details FillOrder Optional An integer that indicates the order in which XLReportCom fills data If the value is zero XLReportCom will insert entire rows and fill data by rows 1 means to insert entire columns and fill data by columns 2 means to insert range and fill data by rows 3 means to insert range and fill data by columns Default is O ImageList Optional A string that indicates which data source fields are the 2 28 66 om picture files The mageList is the list of data source fields separated by the character You can identify a field using the name of field or the index number of field but not simultaneously In data source you stored the path and file name of the picture not the picture The file path can be a relative path an absolute path or a URL If it is a relative path the base path is the path of the worksheet PageBreakOptional A string that indicates the page breaks The unit of page length is r or g r means record g1 means group one g2 means group two For example 6r or 6 means that XLReportCom will insert a page break per 6 records 101 or 1g means a page break per group one and 191 6 means a page break per group one or 6 records Default is that means no page break NoData Optional An integer that represents an option when no data are returned from data source If the va
38. cutes a SQL statement directly puts the result data into cells in the table Variable table report The number of rows or columns in the table is unfixed and it is variable as the number of result records When XLReportCom executes a SQL statement it repeats the table rows or columns for each record or group and then puts data into cells of the table 4 4 2 Creating a Worksheet for a Fixed Table Report For a fixed table report you need to create a worksheet in the report template file according to the report The format of the worksheet is the same as the format in the report but the cells that should be filled data into are blank When XLReportCom executes a SQL statement the data values from data source will be filled into these cells A B 1 3 The fixed table defined The fixed table filled data in the report template file by rows in the report file 4 4 3 Creating a Worksheet for a Variable Table Report For a variable table report you also need to create a worksheet in the report 16 template file according to the report But you just need to reserve some rows columns in the worksheet for one or two records XLReportCom will add some rows columns according to the number of the records returned from data source Item Id Item 10 3 3 3 3 3 3 3 3 The variable rows table defined The variable rows table filled data in the report templa
39. d then all formulas in the report template file will be brought to the final report file Example Show Unit Price Quantity Discount and Amount The Amount will be changed if an end user changes Unit Price Quantity or Discount You can use a formula to show Amount 1 Create a template file as follows and define the formula C2 D2 1 E2 in cell F2 You must use the relative reference F2 Y amp C2 D2 1 E2 C D CustName Product UnitPrice Quantity Discount Amount 0 00 2 Write the report function as follow and use COPYRANGE to copy the formula to all following cells for each record For the first record XLReportCom will directly put data into row 2 For the other records it will copy row 2 to the current row and then put data into the current row So the formula in cell F2 will copy to cell F3 and Microsoft Excel will automatically change the formula to C3 D3 1 E3 F1 Report sheet Sheet1 cell A2 copyrange 2 2 SELECT c CompanyName AS Customer p ProductName d Quantity d UnitPrice d Discount FROM Orders o Customers c OrderDetails d Products p WHERE o CustomerlD c CustomerlD 71 AND d OrderlD AND d ProductlD p ProductID AND YEAR o OrderDate YEAR 1996 04 01 AND MONTH o OrderDate MONTH 1996 04 01 ORDER BY 1 2 3 Generate the report A B D E F 1 CustHame Product UnitPrice Quantity Discount Amount 2 Bon app Carnarv
40. d assigns the values the names defined in the Excel workbook XLReportCom will just fetch the first record no matter how many records are returned from data source Syntax object SetExcelName Recordset Workbook NameList object Required The object is the XLReport object Recordset Required An object variable that represents the ADODB Recordset object to provides data Worksheet Required An object variable that represents the Excel Worksheet object to be filled data NameList Required A string that represents the names you want assign values to The NameList is the list of names separated by the character For example BeginDate EndDate means two names BeginDate and EndDate that should be defined in the report template The names in the list should correspond to the fields in the SQL statement The value of the first field is put into the first name and the value of the second field is put into the second 31 This example uses SetExcelName method to assign the values of fields to names 1 Define the names BeginDate and EndDate in the report template in Microsoft Excel 2 Write the code in your application Set con New ADODB Connection Set rec New ADODB Recordset con ConnectionString Data Source Report Sample con Open strSQL SELECT min_date max_date FROM rec Open strSQL con xlrpt SetExcelName Recordset rec Worksheet xlWorksheet NameList
41. data source name for the 333 Connection The ADODB Connection object Example Private Sub mxlrpt BeforeConnect UserlD As String Password As String DataSource As String Connection As ADODB Connection Connection ConnectionTimeout 15 Connection CursorLocation adUseClient End Sub 5 3 2 TemplateOpen Event Occurs when a template workbook is opened Syntax Private Sub object_TemplateOpen ByVal Workbook As Excel Workbook object The object is the XLReport object Workbook An object variable that represents the Excel Workbook object to be opened Example Private Sub mxlrpt TemplateOpen ByVal Workbook As Excel Workbook Dim i As Integer With Workbook If Application Visible Then For i Worksheets Count To 1 Step 1 Worksheets i DisplayPageBreaks False 34 End If End With End Sub 5 3 3 ReportComplete Event Occurs when all report generating process is completed Syntax Private Sub object_ReportComplete ByVal Workbook As Excel Workbook object The object is the XLReport object Workbook An object variable that represents the Excel Workbook object Example Private Sub mxlrpt ReportComplete ByVal Workbook As Excel Workbook Close the workbook and do not display the report when get errors If mintErrCount gt 0 Then Workbook Close End If End Sub 5 3 4 FunctionBeforeExectue Event Occurs before a function is executed Syntax Private Sub object_FunctionBe
42. dd sophisticated colorful charts in your reports For example you can see at a glance whether sales are falling or rising over quarterly periods or how the actual sales compare to the projected sales You can create a chart on its own sheet or as an embedded object on a worksheet 4 5 2 Creating a Blank Chart To create a chart in the report using XLReportCom you need to add a chart in the report template file The chart will be brought into the report file with the same chart type display option number format titles data labels and legends To add a chart in the template file 1 Open the report template file using Microsoft Excel 2 Enter the sample data for the chart on the worksheet 3 Select that data and use the Chart Wizard to step through the process of choosing the chart type and the various chart options or use the Chart toolbar to create a basic chart that you can format later 4 Customize the chart For example change the chart type colors lines fills number formats titles data labels and legends in charts 19 5 After you have finished the customization delete data from the chart You should keep a blank chart in the report template file You will put data into the worksheet using XLReportCom For more detail information refer to Microsoft Excel Help 20 Chapter 5 API Reference 5 1 Objects 5 1 1 XLReport Object Represents the XLReportCom XLReport is the main class for report generat
43. eat the range for each record You can reference a range of cells like 2 4 or 2 05 The default range is the area that includes all cells for the records Copy Optional An integer that indicates whether XLReportCom will copy the range for each record If the value is zero XLReportCom will skip the rows columns of the range for each record Otherwise it will copy the original range to the range where data will be filled for each record FillOrder Optional An integer that indicates the order in which XLReportCom fills data If the value is zero XLReportCom will fill data by rows 1 means to fill data by columns Default is O ImageList Optional A string that indicates which data source fields are the picture files The mageList is the list of data source fields separated by the character You can identify a field using the name of field or the index number of field but not simultaneously In data source you stored the path and file name of the picture not the picture The file path can be a relative path an absolute path or a URL If it is a relative path the base path is the path of the 22 worksheet PageBreakOptional A string that indicates the page breaks The unit of page length is that means record For example 6r or 6 means that XLReportCom will insert a page break per 6 records Default is that means no page break Example This example uses FixTableReport method to make the report Top 5 Employee
44. emplate in Microsoft Excel For example you want to display a logo in your report You can insert the logo graphics file 78 into the report template For more information about adding pictures to worksheets refer to Microsoft Excel Help 7 5 2 Inserting pictures into a report Except for inserting the static pictures during report design you may insert pictures during report building process XLReportCom can put the graphics files into the report and support all graphics file format that Microsoft Excel support To insert pictures into a report using XLReportCom you should do as follows 1 Store the paths and names of the graphics files in the database You store the paths and names of the picture files in database do not store the pictures The file path can be a relative path an absolute path or a URL For example you store images emp1 jpg in Photo field 2 Specify the positioning option and size in the report template To specify the positioning option and size you should write a formatting expression into the cell in the report template file XLReportCom will get the text of the cell and insert a picture into the cell according to the instruction in the format expression 3 Write the report function an file and identify the image fields using the IMAGE argument For example F1 Report sheet Employee Profile image photo 4 Use XLReportCom to generate report with pictures XLReportCom will submi
45. f the examples in this manual are based on Sample mdb data The sample reports access the sample database through the ODBC data source name Report Sample When you install XLReportCom you can choose to add the ODBC data source name And you also can add the ODBC data source name manually To create the System DSN Report Sample do as follows 1 Click the Windows Start button choose Settings and then click Control Panel 2 On computers running Microsoft Windows 2000 or later double click Administrative Tools and then double click Data Sources ODBC The ODBC Data Source Administrator dialog box appears On computers running previous versions of Microsoft Windows double click 32 bit ODBC or ODBC 3 Select the System DSN tab and then press Add button 4 Choose Microsoft Access Driver mdb then press Finish button 5 In the ODBC Microsoft Access Setup dialog box type Report Sample the Data Source Name box 6 Press the Select button and browse to select Sample mdb 7 Press OK button to close the ODBC Microsoft Access Setup dialog box 8 Press OK button to close the ODBC Data Source Administrator dialog box 3 3 Samples After XLReportCom is installed some samples are installed too Use these samples to learn XLReportCom The samples include a sample database VB sample programs VBA sample programs and sample reports They are located in the Application Data LJZsoft under All Users or yo
46. foreExectue ByVal FunctionNo As String ByVal FunctionType As Integer ByVal SQLNo As Long ByVal SQL Text As 2352 String object The object is the XLReport object FunctionNoA string that represents the label of the function FunctionTyp An integer that represents the type of the function 0 means ExecSQL function 1 means Name function 2 means Report function SQLNo A long that represents the number of SQL statements SQLText string that contains the SQL statement Example Private Sub mxlrpt FunctionBeforeExectue ByVal FunctionNo As String ByVal FunctionType As Integer ByVal SQLNo As Long ByVal SQLText As String frmWait IblFunctionNo FunctionNo frmWait lbISQLCount SQLNo End Sub 5 3 5 FunctionAfterExectue Event Occurs after a function is executed Syntax Private Sub object_FunctionAfterExectue ByVal FunctionNo As String ByVal FunctionType As Integer ByVal SQLNo As Long ByVal ErrObj As ErrObject object The object is the XLReport object FunctionNoA string that represents the label of the function FunctionTyp integer that represents the type of the function 0 means 36 ExecSQL function 1 means Name function 2 means Report function SQLNo A long that represents the number of SQL statements ErrObj The Err object Example Private Sub mxlrpt FunctionAfterExectue ByVal FunctionNo As String ByVal FunctionType As Integer ByVal SQLNo As Long ByVal ErrObj As ErrObject If ErrObj Number lt gt
47. iled to save the report file Failed to save the report file Not support the file format FileFormat The ADODB Recordset object is closed Syntax error The sheet SheetName does not exist The Excel Workbook object is not set The Excel Worksheet object is not set Syntax error There is a lack of the parameter NAME Syntax error There is a lack of the parameter CELL Syntax error It is not a valid cell for the parameter CELL Syntax error Failed to parse cell list The range or copyrange should be Range 38 2147221382 2147221373 2147221372 2147221363 2147221362 2147221361 2147221360 Syntax error Failed to parse range Range Syntax error Failed to parse image Can not find field mageField in the image list Syntax error Failed to parse image Syntax error Failed to parse group Group Can not find field GroupField Syntax error Failed to parse group The grouprange of group N should be Range Syntax error Failed to parse grouprange 39 Chapter 6 XRF Files 6 1 Using XRF files 6 1 1 About XRF files Like XLReportGen XLReportCom also can read an XRF file to generate a report The XRF file is a text file with an xrf extension It contains information such as the name of the report template file the name of the report file the name of the log file data sources parameters and functions The XRF file tells XLReportCom how to get data from data sources and how
48. ion using XLReportCom Using the XLReport Object The following example creates an XLReport object in another application and then generates a report using an file Dim xlrpt As XLReport Set New XLReport xlrpt ExcelReport customer_list xrf 5 2 Methods 5 2 1 FixTableReport Method Generates a fixed table report based on a template In a fixed table report the number of rows and columns is fixed XLReportCom gets data from a recordset object and directly fills data into the cells of a worksheet Syntax object FixTableReport Recordset Worksheet CellList Range Copy FillOrder ImageList PageBreak 21 object Required The object is the XLReport object Recordset Required An object variable that represents the ADODB Recordset object to provides data Before calling this method please keep the current record position to the first record Worksheet Required An object variable that represents the Excel Worksheet object to be filled data CellList Required A string that represents the list of cells separated by the character For example A2 B2 B3 D2 D3 The cells in the CellList should correspond to the data source fields in the recordset The value of the first field is put into the first cell and the value of the second field is put into the second Range Optional A string that indicates the range in the worksheet to be used for the records XLReportCom will skip or rep
49. is dependent on your Microsoft Excel For example Microsoft Excel 2003 supports XML but Microsoft Excel 97 2000 does not support it For more information about converting files please refer to Microsoft Excel Help The file xconv cfg is located in the XLReportCom directory contains the information of the file format You can expand it if your Microsoft Excel supports more file formats File Format Name Value Description Extension Converter xlWorkbookNormal 4143 Microsoft Excel Workbook XIs Office97 xITemplate 17 Template XIt Office97 45 XlAddin 18 Microsoft Excel Add In xla Office97 XIHtml 44 Web Page htm html Office2000 xIWebArchive 45 Single File Web Page mht mhtml Office2003 xIXMLSpreadsheet 46 XML Spreadsheet xml Office2003 XICSV 6 CSV comma delimited CSV Office97 xICSVMac 22 CSV comma delimited CSV Office97 Macintosh xICSVMSDOS 24 CSV comma delimited CSV Office97 MS DOS xICSVWindows 23 CSV comma delimited CSV Office97 Windows xlCurrentPlatformText 4158 Text Tab delimited txt Office97 19 Text Tab delimited txt Office97 Macintosh xlTextMSDOS 21 Text Tab delimited txt Office97 MS DOS xlTextWindows 20 Text Tab delimited txt Office97 Windows xITextPrinter 36 Formatted Text prn Office97 Space delimited xlUnicodeText 42
50. is fixed XLReportCom executes a SQL statement to get data from data source and directly fills data into the cells of a worksheet Syntax Report salstatement Arguments TYPE fix SHEET sheet FILLORDER fillorder CELL celllist RANGE range IMAGE fieldlist PAGEBREAK pagelength CONNECT datasource 54 The TYPE argument specifies the report type fix means fixed table report The SHEET argument identifies a worksheet in the report template The sheet is the name or index number of the worksheet The index number starts at 1 The FILLORDER argument specifies the order in which XLReportCom fills data Possible values are row or col row means to fill data by rows and col means to fill data by columns Default is row The CELL argument specifies the positions where data values will be inserted The celllist is the list of cells separated by the character For example A2 B2 B3 D2 D3 The cells in the celilist should correspond to the data source fields in the SQL statement The value of the first field is put into the first cell and the value of the second field is put into the second cell XLReportCom will use the next cell if you omit a cell except the first cell If FILLORDER row the next cell is the right cell If FILLORDER col the next cell is the below cell The RANGE or COPYRANGE argument specifies the range in the worksheet to be used for the records XLReportC
51. l groups are same as the range of details it is faster It may take a lot of time to add pagebreaks If you change the default printer or delete all printers on your computer it will probably impact the performance XLReportCom supports Microsoft Excel 2007 You can use xlsx file as report file and template file Please copy xconv2007 cfg to xconv cfg 83 Chapter 9 License and Support 9 1 License Your Agreement to This License You should carefully read the following terms and conditions before using installing copying or distributing this software Your use installation copying or distribution XLReportCom indicates your acceptance of this agreement License NO WARRANTY XLReportCom IS DISTRIBUTED AS IS NO WARRANTY OF ANY KIND IS EXPRESSED OR IMPLIED THE AUTHOR WILL NOT BE LIABLE FOR DATA LOSS DAMAGES LOSS OF PROFITS OR ANY OTHER KIND OF LOSS WHILE USING OR MISUSING THIS SOFTWARE Evaluation License XLReportCom is not free software You may use this software for evaluation purposes without charge for a period of 30 days If you use this software after the 30 day evaluation period you must purchase it You may copy the evaluation version of this software and documentation as you wish and give exact copies of the original evaluation version to anyone and distribute the evaluation version of the software and documentation in its unmodified form via electronic means You are specifica
52. le This example uses VarTableReport method to make the report Mail Label 1 Create the template in Microsoft Excel 1 2 XYZ Limited Co 2 3 XYZ Building 88 AAA Street BBB District 4 Beijing China 100123 Limited Enn LC 6 SEN 8 pS 10 AES ere o Cmm 2 Write the code in your application Set con New ADODB Connection Set rec New ADODB Recordset con ConnectionString Data Source Report Sample con Open strSQL SELECT CompanyName Address CityName amp amp CountryName PostalCode FROM Customers Cities Countries WHERE 396 Customers CityCode Cities CityCode AND Customers CountryCode Cities CountryCode AND Customers CountryCode Countries CountryCode ORDER BY CompanyName rec Open strSQL con Recordset rec Worksheet xlWorksheet CellList B7 B8 B9 B10 1 11 Copy 1 PageBreak 4r rec Close 3 Generate the report eee E mim C9 18 19 20 21 22 To XYZ Limited Co XYZ Building No 58 AAA Street BBB District Beijing China 100123 Alfreds Futterkiste Obere Str 57 Berlin Germany 12209 sssssss s L L To XYZ Limited Co XYZ Building 88 AAA Street BBB District Beijing China 100123 Ana Trujillo Emparedados y helados Avda
53. lly prohibited from charging or requesting donations without permission from the author 84 Developer License The software is licensed per developer This means that each developer using the software needs one license The developer may use the software on one or more computers You may develop your application that bundles or makes use of the software directly indirectly You can not use the software to build competitive products of any kind like XLReportGen You may not resell rent lease sub license or distribute the software alone The software must be distributed as a component of an application and bundled with an application or with the application s installation files You may distribute royalty free the run time files of the software with your applications You need to duly inform your customers that they are not allowed to use the software independently from your application 9 2 Technical Support If you encounter any problems in usage of XLReportCom and need the technical support Go to our support web site at http www lizsoft com support htm Send email to support 2ljzsoft com 85
54. ls for this group and the range or group range for the lower level group The IMAGE argument specifies the fields are picture files The fieldlist is the list of data source fields separated by the character You can identify a field using the name of field or the index number of field but not simultaneously In data source you stored the path and file name of the picture not the picture The file path can be a relative path an absolute path or a URL If it is a relative path the base path is the path of the report template file The PAGEBREAK argument specifies the page breaks The unit of page length is or g r means record 91 means group 92 means group two For example 6r or 6 means that XLReportCom will insert a page break per 6 records 101 or 1g means a page break per group one and 191 6 means a page break per group one or 6 records Default is that means no page break The NODATA argument specifies an option when no data are returned from data source If the value is delrange XLReportGen will delete the range when no data are returned If the value is delsheet XLReportGen will delete the sheet when no data are returned Default is to do nothing The CONNECT argument specifies the connection to a data source The 62 CONNECT can takes string that expresses data source name a number that expresses a data source index The index number of data source is the
55. lue is 1 XLReportCom will delete the range when no data are returned If the value is 2 XLReportCom will delete the sheet when no data are returned Default is 0 It means to do nothing Group1 Group10Optional A string that indicates the group that is the list of data source fields separated by the character You can identify a field using the name of field or the index number of field but not simultaneously In one report there may be up to 10 groups Notes the order of groups should be in accordance with the order of ORDER BY clause in the SQL statement GroupRange1 GroupRange10 Optional A string that indicates the range of the group in the worksheet XL ReportCom will repeat the range for each group The range of the group should contain the range of the details and the area that includes all cells for this group You reference a group range like 2 4 or B2 D5 For example there are two groups the range of the group one contains all cells for the group one and the range of the group two and the range of the group two contains all cells for the group two and the range of the 29 details The default range is the area that includes all cells for this group and the range or the group range for the lower level group Example This example uses GroupTableReport method to make the report Customer Profile 1 Create the template in Microsoft Excel Customer Contact PhunelF Address 3
56. om will skip or repeat the range for each record You can reference a range of cells like 2 4 or B2 D5 The default range is the area that includes all cells for the records For RANGE argument XLReportCom will skip the rows columns of the range for each record For COPYRANGE argument it will copy the original range to the range where data will be filled for each record The IMAGE argument specifies the data source fields are picture files The fieldlist is the list of data source fields separated by the character You can identify a field using the name of field or the index number of field but not simultaneously In data source you stored the path and file name of the picture not the picture The file path can be a relative path an absolute path or a URL If it is a relative path the base path is the path of the report template file The PAGEBREAK argument specifies the page breaks The unit of page 55 length is that means record For example 6r or 6 means that XLReportCom will insert a page break per 6 records Default is no page break The CONNECT argument specifies the connection to a data source The CONNECT can takes a string that expresses a data source name or a number that expresses a data source index The index number of data source is the sequential number defined in the XRF file and starts at 1 The default implies the first data source The sqlstatement is a SQL statement such as a SELECT st
57. on Tigers 30 00 50 0 1 500 00 3 Bon app 15 00 T 0 108 00 4 Bon app Wimmers gute Semmelkn de 8 00 27 0 212 80 5 B s Beverages Boston Crab Meat 10 00 15 0 147 00 B Beverages Gnocchi di nonna Alice 20 00 30 0 603 00 Example Add totals such as Total Quantity Total Amount You can use the math functions of Microsoft Excel such as SUM 1 Create a report template file as follows define the formula of total quantity as SUM C7 C8 in cell C9 and the formula of total amount as SUM D7 D8 in cell D9 You must use the relative reference 09 Y amp SUM D7 D8 Quantity Amount BEN X Q mme Total 0 0 00 2 Write the report function as follow When XLReportCom insert some rows according to the records Microsoft Excel will automatically change the formulas QF2 REPORT sheet Report2 type var cell B7 reserve 2 SELECT c CategoryName SUM d Quantity Sum d UnitPrice d Quantity 1 d Discount FROM Orders o OrderDetails d Products pr Categories WHERE o OrderlD d OrderlD AND d ProductiD p ProductID AND p CategoryID AND YEAR o OrderDate YEAR 1996 04 01 AND MONTH o OrderDate MONTH 1996 04 01 GROUP BY c CategoryName ORDER BY c CategoryName 3 The following is the generated report The formula of total quantity is changed to SUM C7 C14 and the
58. or more detail information about charting refer to the sample monthly_sales xrf within XLReportCom Example This example uses REPORT function to create the chart Sales by Categories 1 Create a report template with a blank worksheet and a blank chart The worksheet of the chart defined in the report template as follws 09 z amp SUM D7 D8 Quantity Amount ____1___ 9 Total l 2 Write the report function in an XRF file The function puts data into the worksheet that provides data for the chart F2 REPORT sheet Report2 type var cell B7 reserve 2 SELECT c CategoryName SUM d Quantity Sum d UnitPrice d Quantity 1 d Discount FROM Orders o OrderDetails d Products uu Categories WHERE o OrderlD d OrderlD AND d ProductlD p ProductID AND p CategoryID AND YEAR o OrderDate YEAR 1996 04 01 AND MONTH o OrderDate MONTH 1996 04 01 GROUP BY c CategoryName ORDER BY c CategoryName 3 Generate the worksheet and the chart Category Name Quantity Amount airy Products 581 13 685 3 roduce eafood PB 99316 54 otal O Beverages B Condiments O Confections ODairy Products E Grains Cereals 7 5 Pictures 7 5 1 Inserting pictures into a report template To make eye catching reports you can add pictures to your reports You can directly insert pictures into the report t
59. ot need to buy and learn extra reporting tools m Making report template directly using Microsoft Excel The main advantage of using XLReportCom is based on the fact that all formatting is done directly in Microsoft Excel You can take full advantage of Microsoft Excel including cell formatting formulas filtering and sorting drawing and pictures charts multiple sheets page setup headers and footers preview and printing VBA macros and more 1 Accessing to databases using SQL XLReportCom executes SQL statements to extract data from database Supports all type SQL DML DDL and DCL Multiple SQL statements can be executed in one report building process You can perform queries on databases insert data into databases and create database objects like tables The power of SQL can be harnessed for maximum efficiency in reporting m Using ADO to access and manipulate data sources Using ADO XLReportCom can access and manipulate a wide variety of data sources such as Oracle DB2 Sybase Informix Microsoft SQL Server Teradata MySQL Microsoft Access dBase W Integrating Microsoft Excel into your application XLReportCom includes an ActiveX DLL for building application Developers can save time and meet their users needs by integrating the report processing power of XLReportCom into their applications Command line program XLReportCom includes a command line program ExcelReport exe You can use the program to create report
60. rd1 gt Password2 lt password2 gt 50 Password10 lt password10 gt These settings specify the user passwords If you use an ODBC data source name to make a connection you should define user name and password If you use a connection string to make a connection XLReportCom will ignore the setting Password1 specifies the password of the first data source Password2 specifies the password of the second data source They are optional settings If defined default user and password in ODBC data source you may not define them ConnectionString 1 lt connectionstring 1 gt ConnectionString2 lt connectionstring2 gt ConnectionString 10 lt connectionstring 10 gt These settings specify the connection strings If you defined a connection string XLReportCom will make a connection to the data source using the connection string and ignore the settings of the name user and password But you must define a name that you can reference in functions ConnectionString1 specifies the connection string of the first data source ConnectionString2 specifies the connection string of the second data SOUICE They are optional settings If no connection string XLReportCom will make a connection to data source using the ODBC data source name EncryptPassword Y N This setting specifies how to save the passwords of the data sources If the value is Y passwords will be saved in an encrypted format If the value is N the passwords will be s
61. rmatting expression into the cell in the report template file XLReportCom will get the text of the cell and insert a picture into the cell according to the instruction in the format expression The format expression for pictures as follows placement size The placement specifies the positioning option and can be one of the following values The default value is MNS Values Description MAS Move and size with cells MNS Move but don t size with cells NMS Don t move or size with cells The size specifies the size of a picture Possible values are STRETCH Wnnn or and Hnnn STRETCH means that the picture is resized to fit within the cell W100 means that the width of the picture is set to 100 points H50 means that the height of the picture is set to 50 points The default means the original size If you just specify the width or height of the picture not both XLReportCom will retain the original proportions of the picture when XLReportCom resize it Example w84 18 Remarks On the supposition that the original picture is size 144 x 168 points XLReportCom will insert a picture set the positioning option to Move but don t size with cells set the height of the picture to 72 points and the width to 84 points 4 5 Charts 4 5 1 About Charts Charts are visually appealing and make it easy for users to see comparisons patterns and trends in data You can use Microsoft Excel to a
62. s a data source name or a number that expresses a data source index The index number of data source is the sequential number defined in the XRF file and starts at 1 The default implies the first data source The sqlstatement is SQL statement such as a SELECT statement Example This example uses Variable Table Report function to make the report Mail Label F1 Report sheet Mail Label type var cell B7 B8 B9 B10 copyrange 1 11 pagebreak 4r SELECT CompanyName Address CityName amp amp CountryName PostalCode FROM Customers Cities Countries WHERE Customers CityCode Cities CityCode 59 AND Customers CountryCode Cities CountryCode AND Customers CountryCode Countries CountryCode ORDER BY CompanyName 6 3 3 Group Table Report Uses GroupTableReport method to generate a variable table report and group data In a variable table report the number of rows or columns in the table is unfixed it is variable as the number of the result records XLReportCom executes a SQL statement to get data from data source copy the group range for each group copy the detail range for each record then fills data into the worksheet Syntax Report salstatement Arguments TYPE var SHEET sheet FILLORDER fillorder CELL celllist RANGE range GROUP grouplist GROUPRANGE grouprange IMAGE fieldlist PAGEBREAK pagelength NODATA nodataoption CONNECT datasource The TYPE arg
63. s for Sales 1 Create the template in Microsoft Excel Quantity Amount 2 Write the code in your application Set con New ADODB Connection Set rec New ADODB Recordset con ConnectionString Data Source Report Sample con Open strSQL SELECT TOP 5 e FirstName e LastName SUM d Quantity Sum d UnitPrice d Quantity 1 d Discount AS SalesAmount FROM Orders o OrderDetails d Products p Employees e WHERE o OrderlD d OrderlD AND d ProductlD p ProductID AND o EmployeelD e EmployeelD AND YEAR o OrderDate 1996 AND MONTH o OrderDate 04 GROUP BY e FirstName e LastName ORDER BY 3 DESC rec Open strSQL con 253 xlrpt FixTableReport Recordset rec Worksheet xlWorksheet CellList B7 rec Close 3 Generate the report Employee Name Quantity Amount 1 Nancy Davolio 24 827 4 2 Laura Callahan 20 728 1 3 Janet Leverling 16 360 1 4 Andrew Fuller 13 937 6 5 8 298 45 5 2 2 VarTableReport Method Generates a variable table report based on a template In a variable table report the number of rows or columns in the table is unfixed and it is variable as the number of the result records XLReportCom gets data from a recordset object inserts some blank rows columns or copy a range for each record then fills data into the cells of a worksheet Syntax object VarTableReport Recordset Worksheet CellList Range Copy Reserve FillOrder ImageList PageBreak NoData
64. s too It does not require programming It is enough if you know how to use Microsoft Excel and how to write SQL Various reporting capabilities XLReportCom provides various reporting capabilities including sorting data grouping data subreports totaling and summarizing data formatting charting and pictures It is easy to create simple reports and you can create complex reports Chapter 2 Installation and Startup 2 1 Software Requirements Microsoft Windows 95 Windows 98 Windows NT Windows 2000 Windows XP Windows 2008 Windows Vista or later Microsoft Office 97 98 Office 2000 Office XP Office 2003 or later 2 2 Installing XLReportCom Run the installation program and follow the instructions to complete XLReportCom installation If you don t have Microsoft Office installed please install it first If your environment is Windows 95 98 and Office 97 and you don t have VB6 0 run time files installed please install it For Windows 2000 Windows XP Windows 2003 and Office 2000 or later you do not need to install VB6 0 run time files because they are included in OS and Office To install VB6 0 run time files just run vbrun60sp5 exe and follow the instructions If you don t have ODBC Driver installed for the database you want to access please install it If your OS is Windows 95 98 and you don t have Microsoft Data Access Components 2 0 MDAC_TYP or later installed please install it For Windows 2000 Windows
65. s using the aggregate function in SQL statement too 1 Use aggregate function and GROUP BY clause get summary data for each group and insert results into a temporary table 2 If you have the different kinds of summaries repeat the step 1 and insert results into another temporary table 3 Use group table report function and join the detail data and the summary data using JOIN The summary fields must be included in the group list For more detail information please refer to the samples invoice xrf and sales_detail xrf within XLReportCom 7 4 Charting Charts are visually appealing and make it easy for users to see comparisons patterns and trends in data You can use Microsoft Excel to add sophisticated colorful charts in your reports For example you can see at a glance whether 76 sales are falling or rising over quarterly periods or how the actual sales compare to the projected sales To create a chart in a report you should create the chart in the template file You can create a chart on its own sheet or as an embedded object on a worksheet For more detail information how to create chart refer to Microsoft Excel Help To create a chart in the report template file you may use some sample data Using sample data you can set the various chart options After you have made the report template delete the sample data When you generate the report XLReportCom will put data into the report and you will get the chart F
66. sample data You can use formulas to perform calculations in a report template file XLReportCom is a converter too Besides Microsoft Excel workbook you can generate a report in other file format such as HTML XML Lotus 1 2 3 CSV 80 text You also can convert data from database to other file format You can protect the generated report so that it can not be modified You can edit an XRF file xrf with a text editor such as Notepad In an file for the report template file report file and log file it is possible to give a relative path If it is a relative path the base path is the path of the XRF file In an file you can use parameters in the SQL statements To use parameters you must define them first In an file you can use parameters in the paths and names of the report file template file and log file To use parameters you must define them first You should be careful to define a unique name for each parameter because XLReportCom will replace all strings that are the same as the names of the parameters It is a good choice a name begins with the character such as ReportDate If you get some errors when you run ExcelReport exe you can check the default log file ExcelReport log under the XLReportCom program directory If you do not define the log file in the XRF file or can not create the log file defined you can find log information in the ExcelReport log In the
67. sequential number defined in the XRF file and starts at 1 The default implies the first data source The sqlstatement is SQL statement such as a SELECT statement Example This example uses Group Table Report function to make the report Customer Profile F1 Report sheet Customer Profile cell A6 B7 C7 D7 D8 E7 E8 E9 range 6 9 group 1 pagebreak SELECT LEFT CompanyName 1 CompanyName ContactName Phone amp Phone Fax amp Fax Address CityName amp amp CountryName PostalCode FROM Customers Cities Countries WHERE Customers CityCode Cities CityCode AND Customers CountryCode Cities CountryCode AND Customers CountryCode Countries CountryCode ORDER BY CompanyName 6 3 4 Name Executes a SQL statement and assigns the values to the names defined in the Excel workbook XLReportCom will just fetch the first record no matter how 63 many records are returned from data source Syntax Name sqistatement Arguments NAME namelist CONNECT datasource The NAME argument specifies the names you want assign values to The namelist is the list of names separated by the character For example BeginDate EndDate means two names BeginDate and EndDate that should be defined in the report template The names in the list should correspond to the fields in the SQL statement The value of the first field is put into the first name and the value of the second field
68. st define it first If you have defined a parameter name you can use it in SQL statements In fact XLReportCom will replace all strings that are the same as the names of the parameters You should be careful to define a unique name for each parameter It is a good choice a name begins with the character Example Input an order id to get the order information The field OrderlD is numeric type 1 Defining a parameter Define a parameter as follows Name OrderlD Title Order ID 2210248 Default 10360 2 Using a parameter 42 You can use the parameter SQL statements For example SELECT o OrderlD 0 0rderDate SUM d UnitPrice d Quantity 1 d Discount AS Amount FROM Orders o OrderDetails d WHERE o OrderlD d OrderlD AND o OrderID OrderlD GROUP BY o OrderlD o OrderDate Example Define two parameters The first parameter is the sales date and the second is the category of the products The field OrderDate is date type and CategoryName is char type 1 Defining parameters Define parameters as follows Name1 SalesDate Title1 Sales Date Default1 1996 05 01 Name2 Category Title2 Category of Products Default2 2 Using parameters You can use the parameters SalesDate Category in SQL statements For example SELECT FROM Orders OrderDetails Products Categories AND OrderDate SalesDate 43 AND CategoryName LIKE Category For Microsoft Jet SQL LIKE
69. stalled 6 2 XRF File Reference 6 2 1 XRF File Format The layout of an XRF file is as the following ExcelReport Version 2 0 Data Source ExcelReport is the flag of the XRF file Version 2 0 is the version of the XRF file An file contains several sections The sections of Data Source File and Parameter consist of a group of related settings The sections and 48 settings are listed in the XRF file in the following format section name keyname value In this example Section name is the name of a section The enclosing brackets are required and the left bracket must be in the leftmost column on the screen The keyname value statement defines the value of each setting A keyname is the name of a setting It can consist of any combination of letters and digits and must be followed immediately by an equal sign The value can be an integer a string or a quoted string depending on the setting You can include comments in these sections You must begin each line of a comment with a semicolon The SQL section consists of functions Each function is begin with the character Syntax functionno functionname arguments salstatement The functionno is the label of the function The functionname represents a function The arguments define various properties for the function An argument takes the form Namez Value The argument value can be delimited by single or double quotes
70. t the SQL statement and get the data from database read the graphics files according to the paths and names and insert them into the report If the path and file name of the picture is XLReportCom will return 7 XLReportCom will return Error if it does not find the file of the picture For more detail information about pictures refer to the samples employee profile xrf product catalog xrf within XLReportCom 79 Chapter 8 Hints and Tips You can run ExcelReport exe in command line The format is lt xrf file name gt d u1 user1 p1 pwd1 pa2 For example excelreport c excelreport monthlysales xrf 199605 ExcelReport exe can be scheduled with Windows Scheduled Tasks or other tools The process of generating reports can be fully automated periodically or on events XLReportCom comes with a sample database VB sample programs VBA sample programs and sample reports You can use them when learning the program To use the samples you must add a data source named Report Sample to specify the sample database To make a report template you can use some sample data It is very useful especially for formatting After you have made the report template you delete the sample data To create a chart in the report template file you can use some sample data Using sample data you can set the various chart options After you have made the report template you delete the
71. te file by rows in the report file One record from data source can be put into two or more rows columns To do this you need to create a repeat range that includes two or more rows columns The format of the last row column border can be different from the others For example the outside borders used double lines and the inside borders used single lines To do this you should reserve the blank rows columns for 2 records When XLReportCom inserts some blank rows columns the new rows columns will inherit the format of the first row column in the reserved rows columns XLReportCom will repeat the range for each record Ranges can be nested The inside range is the detail range for detail record and the external range is the group range for group XLReportCom will repeat the inside range for each record and repeat the group range for each group 17 4 4 4 Formatting Cell for Pictures To enhance the visual impact of your report you can insert pictures into your report XLReportCom supports many popular graphics file formats bitmap JPG GIF PNG TIFF and so For the graphics file formats XLReportCom supports refer to Microsoft Excel Help You should store the path and name of the graphics files in the database and identify the image fields in the report function XLReportCom will read the graphics files and insert them into the cells in the report file To specify the positioning option and size you should write a fo
72. tead of creating unnecessarily wide columns or abbreviated labels you can rotate text and apply borders that are rotated to the same degree as the text Add borders colors and patterns To distinguish between different types of information in a worksheet you can apply borders to cells shade cells with a background color or shade cells with a color pattern m Number formats You can use number formats to change the appearance of numbers including dates and times without changing the number behind the appearance The number format does not affect the actual cell value that Microsoft Excel uses to perform calculations m Conditional formatting The conditional format is a format such as cell shading or font color that Excel automatically applies to cells if a specified condition is true m Style The style is a combination of formatting characteristics such as font font size and indentation that you name and store as a set When you apply a style all of the formatting instructions in that style are applied at one time 4 4 Table Reports 4 4 1 About Table Reports A table is made up of rows and columns of cells that you can fill with text and graphics Tables are often used to make reports and organize and present 15 information XLReportCom supports two types of table reports fixed table report variable table report Fixed table report The number of rows and columns in the table is fixed When XLReportCom exe
73. there is not this setting and ProtectReport is Y a random password will be created LogFileName lt logfilename gt This setting specifies the name of the log file lt logfilename gt value is the name and path of the log file The file path can be a relative path or an absolute path If it is a relative path the base path is the path of the XRF file In lt logfilename gt you can use parameters 6 2 4 PARAMETER Section PARAMETER section contains information about parameters Name1 lt name1 gt Name2 lt name2 gt Name 10 lt name10 gt These settings specify the names of the parameters Name1 specify the name of the first parameter Name2 specifies the name of the second parameter You can define up to 10 parameters XRF file Title1 lt title1 gt Title2 lt title2 gt Title 1 0 lt title 10 gt These settings specify the titles of the parameters Title1 specifies the title of the first parameter Title2 specifies the title of the second parameter 53 Default1 lt default1 gt Default2 lt default2 gt Default1 0 lt default10 gt These settings specify the default values of the parameters Default1 specifies the default value of the first parameter Default2 specifies the default value of the second parameter 6 3 Function Reference 6 3 1 Fixed Table Report Uses FixTableReport method to generate a fixed table report In a fixed table report the number of rows and columns
74. ument specifies the report type var means a variable table report Default is var 60 The SHEET argument identifies a worksheet in the report template The sheet is the name or index number of the worksheet The index number starts at 1 The FILLORDER argument specifies the order in which XLReportGen fills data Possible values are row col rowrange or colrange row means to insert entire rows and fill data by rows col means to insert entire columns and fill data by columns rowrange means to insert range and fill data by rows colrange means to insert range and fill data by columns Default is row The CELL argument specifies the positions where data values will be inserted The celllist is the list of cells separated by the character For example A2 B2 B3 D2 D3 The cells in the celllist should correspond to the data source fields in the SQL statement The value of the first field is put into the first cell and the value of the second field is put into the second cell XLReportCom will use the next cell if you omit a cell except the first cell If FILLORDER row the next cell is the right cell If FILLORDER col the next cell is the below cell The RANGE or COPYRANGE argument specifies the range in the worksheet to be used for the details XLReportCom will skip or repeat the range for each record You can reference a range of cells like 2 4 or B2 D5 The default range is the area that includes all
75. ur profile folder XLReportCom was tested with Microsoft Office 2007 Please download the sample reports for Microsoft Office 2007 from our website Directory Description commonappdata LJZsoft Common Sam Contains the sample database pleDatabase Sample mdb commonappdata LJZsoft XLReportCom Contains the report template files Samples ExcelReport xls and the XRF files xrf commonappdata LJZsoft XLReportCom Contains the sample programs for Samples VB VB6 0 commonappdata LJZsoft XLReportCom Contains the sample programs for Samples VBA Microsoft Excel VBA commonappdata is the path to the Application Data folder under All Users If you install XLReportCom without administrative privileges commonappdata is the path to the Application Data folder under the current user The Application Data folder is usually at Windows 95 98 C windows AIl Users Application Data FR Windows C WinNT Profiles All Users Application Data Windows 2000 XP C Documents and Settings All Users Application Data Windows Vista C ProgramData 3 4 Creating a Report Programmatically 1 Create a template In Microsoft Excel create a report template file named custlist xls Static values and any Excel features included in the template will be included in the generated report The template file you have created as follows B C Customer List Customer
76. y table and prepare data for REPORT function Example This example uses ExecSQL functions to create a table and add some records into the table No result is returned to the report F1 EXECSQL CREATE TABLE min_date DATE max_date DATE 65 F2 EXECSQL INSERT INTO tmpO SELECT 66 Chapter 7 Advanced Reports 7 1 Executing multiple SQL statements In one report building process XLReportCom can execute multiple SQL statements This enables you to 1 Create a report like building block You may divide one report into several parts and respectively use the different SQL statements to make each part of the report You can use the different queries to get the data located in the different tables or databases 2 Create a complex report using the temporary table First you create a temporary table Second use several SQL statements to prepare data in the temporary table You can execute INSERT UPDATE DELETE INSERT SELECT statements And then put the prepared data from the temporary table into your report 3 Create one report file with several reports For example you may create one workbook with several worksheets Example This example executes multiple SQL statements to create a report 1 Create the template in Microsoft Excel Current Month Last Month Quantity Amount Quantity Amount ae ae ener To 0 0 o Category Hame 2 Write SQL statements

Download Pdf Manuals

Related Search

Related Contents

Xense orange fresh.indd  ユーザーズマニュアル（詳細編） - 三菱電機エンジニアリング株式会社  Tripod Adaptor VCT-55I  

Copyright © All rights reserved. Failed to retrieve file