Microsoft Excel Report Tool
 

How to format reports using Microsoft Excel

XLReportGen is a template-based reporting tool that retrieves data from database and fills Microsoft ® Excel ® report with them.

Creating a report template directly in Micosoft Excel

To make a report using XLReportGen, 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. How to create a new workbook, refer to Microsoft Excel Help.

Creating a worksheet for a 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. The report template is a blank report. XLReportGen will execute a SQL statement, retrieve data from database, and fill them into the report.

For a fixed table report, the format of the worksheet is the same as the format in the report, you reserve same rows/columns in the report template as in the report. For a variable table report, you just need to reserve some rows/columns in the report template for one or two records. XLReportGen will automatically add some rows/columns according to the records.

Formatting a report template

XLReportGen supports ALL formatting features within native Microsoft Excel, such as cell formatting, formulas, filtering and sorting, drawing and pictures, charts, multiple sheets, page setup, headers and footers, preview and printing, VBA, macros, and more.

  • 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.

  • Rotate text and borders
  • The data in a column is often very narrow while the label for the column is much wider. Instead 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.

  • 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.

  • Formatting cells based on specific conditions
  • The conditional format is a format, such as cell shading or font color, that Microsoft Excel automatically applies to cells if a specified condition is true. You can monitor formula results or other cell values by applying conditional formats. For example, you can apply green text color to the cell if sales exceed forecast and red shading if sales fall short.

  • Formulas as formatting criteria
  • You can compare the values of the selected cells to a constant or to the results of a formula. To evaluate data in cells outside the selected range or to examine multiple sets of criteria, you can use a logical formula to specify the formatting criteria.

  • 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.

Outputing a report to another file format

XLReportGen is a converter too. You can convert Microsoft Excel workbook to and from other formats, such as HTML, XML, CSV, text, DBF, DIF, and Lotus 1-2-3. To output a report to another file format, you must specify the file type of the report in the XRF file.

XLReportGen supports all file formats that Microsoft Excel supports. The supported file formats include:

  • Microsoft Excel Workbook (.xls)
  • Microsoft Excel Template (.xlt)
  • Microsoft Excel Add-In (.xla)
  • HTML (.htm .html)
  • Web Archive (.mht .mhtml)
  • XML Spreadsheet (.xml)
  • CSV (comma delimited) (.csv)
  • CSV (comma delimited) (Macintosh) (.csv)
  • CSV (comma delimited) (MS-DOS) (.csv)
  • CSV (comma delimited) (Windows) (.csv)
  • Text (Tab-delimited) (.txt)
  • Text (Tab-delimited) (Macintosh) (.txt)
  • Text (Tab-delimited) (MS-DOS) (.txt)
  • Formatted Text (Space-delimited) (.prn)
  • Text (Tab-delimited) (Windows) (.txt)
  • Unicode Text (.txt)
  • Microsoft Excel 2.0 Worksheet (.xls)
  • Microsoft Excel 2.0 Worksheet Far East (.xls)
  • Microsoft Excel 3.0 Worksheet (.xls)
  • Microsoft Excel 4.0 Worksheet (.xls)
  • Microsoft Excel 4.0 Workbook (.xlw)
  • Microsoft Excel 5.0/95 Workbook (.xlw)
  • Microsoft Excel 97-2003 & 5.0/95 Workbook (.xls)
  • DBF 2 (dBASE II) (.dbf)
  • DBF 3 (dBASE III) (.dbf)
  • DBF 4 (dBASE IV) (.dbf)
  • DIF (data interchange format) (.dif)
  • SYLK (symbolic link format) (.slk)
  • WD1 (1-2-3) (.wd1)
  • WK1 (1-2-3) (.wk1)
  • WK1, ALL (1-2-3) (.wk1)
  • WK1, FMT (1-2-3) (.wk1)
  • WK3 (1-2-3) (.wk3)
  • WK3, FM3 (1-2-3) (.wk3)
  • WK4 (1-2-3) (.wk4)
  • WKS (Works) (.wks)
  • Works Far East (.wks)
  • WQ1 (Quattro Pro/DOS) (.wq1)

 


Copyright © 2004 - 2014 LJZsoft Corporation. All rights reserved.