Support > Repository > Form > Excel form - Detail view
ja | en

Details Detailed form data is output as an Excel file. You can print to the printer using Excel.

Open the details display screen of the customer model that set the Excel form function.As shown in Fig. 1, "Form" button is prepared.

Figure 1 Form button prepared on detail 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 detailed information on this customer is laid out.By printing this on a printer, we will output the form.

Figure 3 Detailed information embedded in Excel

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"

The details of each procedure will be explained here.

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). printCustomer1
Display screen Select Detail view. Detail View
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. (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. -
Figure 4 Setting of form output
There is no upper limit on the number of form setting numbers.When you press the "+" button, the setting field is added.

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.

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.

$ {Model name _ p. 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

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 in which the button name for the form prepared on the detail display screen is changed 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 also specify the output method in the form template.When it is blank, it becomes "display by browser".

Figure 16 Specifying output method

Display in browser [deprecated]

This setting corresponds to the "Content-Disposition" field in the HTTP response header."Displayed by browser" is "inline".

This setting remained for compatibility with Wagby R6, but it has stopped working with the latest browser.This is due to enhanced browser security measures.Therefore, the "display in browser" setting is deprecated.

download

This setting corresponds to the "Content-Disposition" field in the HTTP response header."Download" will be "attachment".

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.

Save to server

Writes the form file to the specified folder on the server (where the Wagby application is running).When this setting is made, please specify "output folder" together.Folder on serverSpecify.

Figure 17 Specifying the output folder

If "Save to Server (overwrite)" is selected, if the form with the same name already has been output, overwrite the file.

If you select "Save to Server (Do not overwrite)", if you have already output the same form name, add "_ 1" "_ 2" ... to the file name.(*)

After saving the file, display a message on the screen.If saving fails, an error message is displayed on the screen.

When finding a file with the same name, "_ 1" is added to the file name.If "_ 1" also exists, it increases the number.Because of this mechanism, it is not a skip number.The file with the largest number is the latest file.

Placeholder for file name

The following format can be used for the file name of the form template.

Placeholder Description An example
${username} Logon Account ${username}.xls
${createdatestr} String representing output date time.Format yyyyMMddHHmmss. ${createdatestr}.xls

Placeholders can be combined.For example, you can specify $ {username} $ {createdatestr}. Xls.

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.

  • It is reflected in both detail form and collective form.
  • Placeholders are available.
  • You can use the special notation $ {sheet} as a placeholder.Indicates the sheet number.
  • Please do not include the extension.
  • This setting is invalid for PDF output.(It will be ignored.)

Output file name

It becomes the Excel file name to be output.Also, it becomes the file name of each Excel file when expanded bulk form is expanded.

  • You can use placeholders for file names.
  • 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)7.10

Output multiple forms of data at onceIt becomes the file name of when.

  • Please do not include the extension.
  • Kanji can also be used.
This setting is reflected in the "filename" attribute in the "Content-Disposition" field of the HTTP response header.

Setting method

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

Figure 14 Specifying output file name (1)
Figure 15 Specifying output file name (2)
Customer information.xls
Customer information $ {customer_p.customerid}. Xls

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

Make sure to include the model name as placeholders used for output file names.Also, expressions can not be written.(String operation such as "+" can not be performed.)
(False) $ {name} + "_" + $ {currenttime} + "_" + $ {userid} + ". Xls"
(Positive) $ {customer_p.name} _ $ {customer_p.currenttime} _ $ {customer_p.userid}. Xls

If you want to decide the output file name by a formula, it is a good idea to prepare an item called output file name in the model definition.The item name is described in this setting column.Example $ {customer_p.generatedfilename}
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.

In the detail form template, items of repetitive items (including containers) and foreign key child models areOnly as much as described in the templateIt is output.

Here is an example.In the templates shown below, the items of the negotiation history are repeated container items.

Fig. 18 Detailed form template for customer information

In this repeating container field, the lines from $ {customer_p.report.0.rid} to $ {customer_p.report.7.rid} are specified as placeholders.

Figure 19 The negotiation history part of the template - Fixed length specification

If there is only one record in the DB, the value of the record is inserted in the line of $ {customer_p.report.0.rid}, and after the line of $ {customer_p.report.1.rid} is replaced with blank I will.

On the other hand, if there are nine or more records on the DB, the first to the eighth are output to the form but the ninth and later will not be output.In this case, increase the number of lines of the template beforehand and respond by adding placeholders as well.

We will explain how to extend and reduce the display layout by the number of data of repeating items (including containers).

Write the following notation in the placeholder.

$ {Model name _ p. Container name. # Item name}

For example, write as $ {customer_p.report. #. Rid} or $ {customer_p.report. #. Mdate}.I will describe this in one line.(It can not be written over multiple lines.)

Figure 20 The negotiation history part of the template - Variable length specification

You can also specify multiple repeating containers in one template.The form image will stretch according to the number of output.

Cell binding constraints

One empty data line is displayed in the container part so that the cell binding does not shift even in the form output of data in which no repeating containers are registered.(This will cause cell binding to work.)

When using this method, it is not possible to describe (Excel) = SUM (D4: D5) etc in the cell in order to obtain the total value of repeated container.The cell range of the SUM function is fixed.(We can not deal with stretching this part.)
In such a case, separately, please calculate the total on Wagby side and post it to Excel form.
Or use repeated containers as fixed length.(It works correctly by matching with the range of cells targeted for SUM function.)

Even when you display "externally referenced data" (data using a foreign key) in the detail form template, as many as the specified number of times are displayed as with the repeating item.

For external reference data, the placeholder is the model name of the external reference.
Also, the external reference model on the detail screen uses a model for list display.The model name is "xxx_lp".

For example, if you associate a support model with a customer model with a foreign key, it becomes the following notation.(The number at the end is a fixed designation method.)

${support_lp.xxx.0}
Figure 21 Displaying the contents of the model of the external key cooperation called "support" in the details form of the customer

Automatically generated template file

The template file of the foreign key child model contained in the detailed form (parent model) is generated with the naming rule named showList <child> LpIn <parent> .xls.It is prepared in the work/report folder shown in Figure 5.

By copying the template part (Fig. 22) of this file and pasting it to the template of the parent model, you can create a layout like the one in Fig. 21.

Figure 22 Template file for foreign key child model to include in detail form

You can specify "New registration", "Copy registration", "Update" with the option of "Form output> Display screen".A form output button is prepared on the registration and update screen.

Fig. 23 Form output> Display screen setting (1)
Figure 24 Form output> Display screen setting (2)

Example of execution

The form output button prepared on the registration screen and update screen has the following operation.

  • The value (in the process of being changed) on the screen is output as a form instead of the value stored in the database.
  • After the button is pressed, it returns to its own screen (registration screen or update screen).
Figure 25 Prepare a form output button on the registration screen

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 compatibility with Wagby R6, but it is deprecated (not supported).It is going to be abolished in the future Wagby.

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.
  • "Display format" on the form (Excel) side can not be specified in the Wagby definition file."Type" to be outputted to a form conforms to the definition of the definition file type.To display the format of the form data, please use "format cell" in the form template file (Excel).
  • Despite specifying the format setting, it may not be effective.In that case, please divide it from the cell or sheet for data output and the part of the form template, and make settings to refer to the outputted data in the template.For details, refer to "Points of template file creationPlease read.We will show you how to separate the data output sheet from the sheet of the form template.
  • If it does not print properly, please check whether the print area is set on the first sheet of the form template.
  • You can not set expressions such as macros in the "print range" of the form template.(Even if you set it, Wagby can not execute the expression, so it will be ignored.)
  • 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).