Support > Repository > Form > Excel form - list display
ja | en

Output list form data as Excel file. You can print to the printer using Excel.

Opens the list display screen of the customer model that set the Excel form function.As shown in Fig. 1, the "form (all)" button is prepared.

Figure 1 Form buttons prepared on the list screen

When you press this button, Excel format files will be downloaded.(Figure 2)

Figure 2 Excel file downloaded

Open the file in Excel.You can see that the list information for this customer is laid out.By printing this on a printer, we will output the form.

Figure 3 List information embedded in Excel
For form output in list display, not all data that matches the search condition, not the data currently displayed on the screen.(Example: If 100 data items are searched, and 10 items are displayed on the screen, 100 forms of report output are eligible.)

To define Excel form, follow the procedure below.

  1. Button setting and build by Designer.
  2. Layout design of Excel template file obtained by build
  3. (Build) Wagby application to set "form template"

This section explains the list of each procedure.

Designer settings

Open the "Screen> Form Output" tab.In the form output setting field, make the following settings.

Setting items Description Input example
Template file name It becomes the identifier of "form template" described later.Please choose a unique name with a combination of alphanumeric characters.It does not include extensions (. Xls or. Xlsx). printListCustomer1
Display screen Select the list display. List display
Output method Select Excel. Excel
Used library When Excel is specified as an output method, a combination of which library to use is presented, so select one.Normally, when using Excel, specify POI (as library). POI
indicate If you check this column, the form output will be valid. -
Display condition You can describe the display condition formula of the button."Screen function> Customize button name and display conditionIt explains with.(However, model item names can not be used in expressions.In the case of list display, there is no way to read out the value by specifying the primary key of the model.Functions that do not depend on model item names such as TODAY () can be used.) (Abbreviation)
Button name Specify the button name.When omitted, it becomes "" form "+ serial number".You can also use the formula to determine the button name.(However, model item names can not be used in expressions.In the case of list display, there is no way to read out the value by specifying the primary key of the model.Functions that do not depend on model item names such as TODAY () can be used.) -
Figure 4 Setting of form output

Layout design of Excel template file

When building after setting in Figure 4, the Excel file specified by the template file name is automatically generated in the work/report folder in the Wagby installation folder.(FIG. 5)

Figure 5 Automatically generated template file

When you open this template file in Excel, it looks like Figure 6.Without layout design, item names and placeholders are enumerated.

In the model definition, items specified as list displayIs used as a placeholder.

Figure 6 Excel file with item names and placeholders embedded

Here, the part starting with "$ {" and ending with "}" is called a placeholder.The structure of the placeholder is as follows.The last "#" indicates that more rows will be added.

$ {Model name _ lp. Item name. #}

The actual value is set in the placeholder.Please refer to "Placeholder" for details of placeholder description rule.

While maintaining this placeholder notation, we will design the form layout.Figure 7 shows an example layout.

Figure 7 Example of layout layout

Unlike the detail form in the template for the list form, the number of records is changed dynamically.Therefore placeholders are described on only one line.(Automatically added by the number of output records during form processing.)

Set up form template

Log on as a system administrator to the application you built.Set the "form template" in order to link the model with the form file in Figure 7.(FIG. 8)

Figure 8 Form Template Menu

Create a new report template.(Figure 9)

Fig. 9 New registration of form template

In the ID field, specify the form template ID (Figure 4) set in Designer.It does not include extensions (. Xls or. Xlsx).

Here, the applicable model is "customer".Please set the explanation field freely.

As a template file,Specify the Excel file that saved Figure 7.(FIG. 10, FIG. 11)

Figure 10 Setting up the form template
Figure 11 I registered a form template
The template file registered here is saved under the upload_dir folder.Detail is"String - File> Specification> Internal structurePlease read.

Example of operation

Figure 12 shows an example of changing the form name for the form prepared on the list display screen to "Excel print".

Figure 12 Changing the name of the form button

Definition method

Specify the button name on the Designer side.

Figure 13 Setting button names in Designer

You can make settings that do not display the form button until you execute the search process.By ensuring that search processing is always executed, it is recommended not to output all of the mass data.

Stop search processing immediately after search screen transition

"Screen> Search/List display screen> Search screen> Stop search processing immediately after search screen transitionEnable ".

Fig. 14 Stop the search process immediately after the search screen transition

List display button display condition

In "Screen> Form output", set the following expression as the button display condition of the list type of the report type.

RESULTSIZE() > 0

RESULTSIZE functionReturns the number of search results.

In the form template setting, you can specify the output file name.Set a fixed value.

Sheet name7.10

It becomes the Excel sheet name to be output.

  • Placeholders are not available.
  • Please do not include the extension.

Output file name

It becomes the Excel file name to be output.

  • Placeholders are not available.
  • Include extension in file name.(. Xls or. Xlsx)
This setting is reflected in the "filename" attribute in the "Content-Disposition" field of the HTTP response header.

Output file name (for batch form)

It is invalid in the list form.(Setting is ignored.)

Setting method

You can specify a placeholder.Specify the file name including the extension as well.Here is an example.

Figure 15 Specifying the output file name
Customer information list.xls
Customer information $ {customer_lp.customerid.0}. Xls

In the placeholder, "_lp" is added after the model name after the suffix.The last ".0" means the data of the first line.

If the output file name is blank, the template file name is used.

Items specified as placeholders are "List display itemsIt must be specified as.Designate it as a list display item and make it a hidden item when displayedYou can also.
Although the template file is an .xls format file, it is not possible to say that the output file name is .xlsx.To specify the output file name, use the template file extension.

You can also specify the output method in the form template.

You can select "display by browser" and "download".When it is blank, it becomes "display by browser".

Figure 16 Specifying output method

This setting corresponds to the "Content-Disposition" field in the HTTP response header."View by browser" is reflected as "inline" and "download" as "attachment" respectively.

"View in Browser" remained for compatibility with Wagby R6, but it does not work with the latest browser.This is due to enhanced browser security measures.Therefore, this setting is deprecated.
When Internet Explorer is used, dialogs may be displayed in duplicate when "download" is specified as the output method, but there is no hindrance in the operation.

It does not display on the list display screen, but you can define such that it is output to the list form. In Figure 17, the company name field is not specified as a list display.

Check "Output control> Other> Form> Output to list form" of model item detail definition.(FIG. 18)

Figure 17 In the model definition, the display of the company name item list is invalidated
Figure 18 Setting to output to list form

Do not write placeholders for items not to be output from the template for list form. Automatically generated list form templateIf you are using, remove the placeholder for that item.

Unlike the detail form in the template for the list form, the number of records is changed dynamically.Therefore placeholders are described on only one line.The generated web application side increases the number of records required.In this way, since the system adds lines, the layout design of the template file has some restrictions.

Figure 19 List of customer information Example of template for report

(1) The subscript (numeral part) of the place holder is described by #

The record to be inserted increases from 0 sequentially, but unlike the detailed form, here, the serial number part is expressed as "#" "wild card" and only one line is written : $ {Customer_lp.name. #}).

(2) Do not describe placeholders over multiple lines

Write placeholders on one line.It can not be written over multiple lines.The part below the line where the placeholder is written is judged as "footer" and it is described at the end of the record output.

(3) Constraint of footer part

The portion below the line specified by the placeholder is judged to be "footer" in the template of the list form, and it is copied at the end where the record was added.However, it has the following restrictions.

  • You can not combine cells in the footer part.Since cell binding information remains, it affects the cells of the record to be added.
  • Expressions can not be written.Expression information will be erased.Or an expression error will occur.
  • I can not paste the image.Since the image remains in the original cell position, the position of the image is displayed in a place different from the footer as a result.

These constraints do not apply above the placeholder line (header part).

(4) Handling of repetitive items

Repeat items are output as ", (comma)" delimiter.

(5) Handling of repetitive items

You can not output items in repeating containers.(JOIN functionYou can prepare an output item internally prepared by repeating containers into "one" character string using it and use it in the form.

(6) Handling model reference

The "value" part is output for model reference.The "code" part (the primary key value of the model) can not be specified.

(7) Handling of full-text search results

You can not specify the result of full-text search.

Single item grouping

If the values ​​of the columns are the same, you can "group (vertically)" the cells of that column.If you want to do such processing, give "$ J" to the placeholder.

$J${customer_lp.companyname.#}
Figure 20 Grouping specification

The output image looks like Figure 21.

Figure 21 Output image by grouping

Multiple item grouping

Multiple items can be grouped. By specifying $ G ○ (number), all items with the same number are grouped.

$G1${customer_lp.companyname.#}
$G1${customer_lp.deptname.#}
Figure 22 Grouping specification

The output image looks like Figure 23.If duplicate values ​​exist in the cell containing $ G1, cells are automatically joined.

Figure 22 Output image by grouping

specification

  • Grouping processing can not be joined in the row (horizontal) direction.
  • Detailed form is not supported.

Output error message (standard)

If there is no data and you press the print button, an error message like the one in Figure 24 is displayed.This is the standard behavior.

Fig. 23 When the data does not exist, press the print button
Figure 24 Error Message Appears in a Different Tab

Setting not to output error message

If it is set not to output error message, a file is generated as shown in Fig. 25.But the data section is empty.(FIG. 26)

Figure 25 Empty file is output (1)
Figure 26 Empty file is output (2)

Definition method

Activate "Display message when there is no output target data> Screen> Form output>".(It is enabled by default.)

Figure 27 Setting to display a message when there is no output target data

The Excel form output function is realized using Apache POI or JExcel API which is an open source Excel generation library.

Normally select "POI"."JExcelAPI" is left for backward compatibility, but it is not used aggressively.

Template file

  • The extension of the Excel file used as a template file is ".xls" or ".xlsx".[important point...]
  • The file name of the template file should be alphanumeric characters only.Japanese characters can not be specified.
  • In the template Excel file, the following functions can not be used.
    • Pivot table
    • drop-down list
    • Password protection
  • If you set a macro in the template Excel file, it may not be output correctly.In this case please adjust by changing the contents of the macro etc.
  • If it does not print properly, please check whether the print area is set on the first sheet of the form template.
  • When setting a value from one placeholder to one cell, the upper limit of the number of characters is R10.10.3 is 1024 characters.From R7.11 onwards the upper limit is 32,767 characters.

When. Xlsx is used as a template file

There are the following restrictions when using the extension ". Xlsx".

  • Can not include cell comment in template file.

Execution environment

  • It is not necessary to install Excel on a server machine that runs Wagby application.Generate an Excel file directly on the memory of the server machine.For that reason, it consumes a lot of memory temporarily during output processing.In an environment where the number of concurrent connections is large, in an operation environment where the form output concentrates, be sure to install more memory.
  • In order to open an Excel file, Excel or OpenOffice is required for each user's PC (terminal).