Excel Import Mark

  • Last update:  2023-06-12
  • Overview

    Version

    Plugin Version

    Minimum Version Requirement for FineReport

    Functional Change

    V 1.7.0

    V 11.0.2

    /

    V 1.7.1

    V 11.0.5

    Fixed some bugs.

    Application Scenarios

    Excel Import Mark allows you importing data from Excel through manual marking and intelligent identification. On the basis of integrating all the original Excel import logic and import methods, the operations are more intelligent, the functions are more powerful, and the scenarios are more abundant.

    Terms Explanation

    · Title Area: the header area in the table

    · Content Area: the data section in the table

    · Parts outside the content area: the parts outside the Content Area

    Functions

    Supported scenarios:

    · Three import methods: Clear, Increment, and Cover.

    · Two import directions: Vertical and Horizontal.

    · Import multiple sheets.

    · Customize data of Actual Value or Display Value from Excel.

    · Convert imported value to actual value according to the set format and storing them in the warehouse.

    Unsupported scenarios:

    · Mobile Preview

    · Import data in break region.

    · Import encrypted Excel.

    · Import images and formulas in Excel.

    · Import data with accounting formats in Excel.

    · Enter formula in Data Entry Preview when you select Clear as the import method.

    · Identify cells in header with slashes.

    · Import data in merge cells of content areas. If you import data into these cells, they will be automatically split into a list.

    · Use lower versions than Microsoft Excel 2007. If you use the the lower versions of WPS Excel and Microsoft Excel, the compatibility may not be guaranteed.

    · There is a conflict between the custom display column plugin and the number scroller plugin.

    Plugin Introduction

    Installing the Plugin

    Click to download the plugin: com.fr.plugin.mark.excel.import.v11-1.8.1.zip

    For details about installing designer plugins, see: Designer Plugin Management.

    For details about installing server plugins, see: Server Plugin Management.

    Template Design Guideline

    · The columns (rows) in the template that require data import need to match the corresponding data column headers in Excel, and will be matched according to the headers during import process.

    · The cells in the content area of the template need to be set as expansible.

    · When there are dataset fields in the content area of the template, they need to be set as list to display.

    · When you import data in parts outside the content area, you need to initialize the cells that need to be imported in the designer (set any cell and save settings). If the cells are not initialized, the import process will fail.

    Basic Operation Steps (Required)

    Note: The following steps are required and cannot be omitted, otherwise the import process will fail.

    1. Enter the configuration page: Click Template > Excel Import Mark.

    Note: If there are multiple sheets in the template to import, each sheet needs to be configured separate import mark setting, otherwise you cannot select that sheet without mark when importing.

    2. Excel import mark settings: select the title area and content area.

    1.gif

    Format: A1:D2 refers to a 4 x 2 cell area with A1 as the starting point and D2 as the endpoint.

    3. Add the Mark the import button to complete the configuration.

    Click Template > Web Attributes > Data Entry Settings, select Individually set for the template, and double-click Mark the import to add it to the toolbar.

    4. Preview the template and test the effect of business import.

    1111.GIF

    Advanced Operation Steps (Optional)

    1. Configuring the Excel Import Mark Settings

    Note: The configuration items in the sheet settings only take effect on the current sheet, while the configuration items in the template settings take effect on all sheets of the current template.

    Name

    Explanation

    Application Scenario

    Direction of Import

    Defaults to Vertical.

    According to the direction of data expansion, it is divided into Vertical and Horizontal. Vertical applies to most Excel import scenarios.

    Vertical: When previewing, the title is horizontal, but the data needs to be imported vertically.

     

    Horizontal: When previewing, the title is vertical, but the data needs to be imported horizontally.

     

    Intelligently Identify Excel title area and content area.

    Defaults to enable intelligent identification.

    Title Area is used to improve identification efficiency. The more precise the marked range is, the faster the identification will be. You can choose not to fill it out, or fill it imprecisely.

    It is applicable to most scenarios and can effectively reduce your costs in operation.

    Import method

    Defaults to Clear.

    There are three import methods: Clear, Increment, and Cover, which support multiple selections.

    1. Clear

    Before importing, data in the content area will be cleared first and be replaced with the new-imported content. Other areas will not be cleared.

    Note: It only clears the data in the page. If you want to clear the corresponding database table data, see Clear the Database Table Before Importing Excel.

    2. Cover

    When you import data from Excel, the new data will cover the data on the same location of the page. If there are more rows/columns of data on the page than in Excel, the excess part will be retained on the page.

    3. Increment

    The content imported from Excel will be added as new rows behind the end row.

     

    1. When selecting only one import method, you will default to importing according to this method. If you don't understand the import method, this method is suitable for you.

    2. When selecting multiple import methods, you need to choose a method during import process according to actual situations. If you understand the import method, this method is suitable for you.

    Support importing parts outside the content area

    Not selected by default.

    After selection, the part outside the content area in Excel will be imported, matching according to the location during the import process.

    It is suitable for scenarios of importing Excel with fixed content. It not only imports data in the rows/columns of the title area and content area, but also import other content such as signatures or dates according to their locations.

    Convert data to actual values to submit

    Not selected by default.

    After selection, the values imported from Excel can be converted into the desired actual values according to the custom Display-Data Dictionary and then stored in the database. For example, if you import male and female, 1 and 2 are entered into the warehouse correspondingly. For details, see: Excel Import Display Value Conversion.

    After selection, the imported values will also be converted to actual values for Data Validation.

    Applicable to scenarios where data needs to be converted to another value based on custom rules when entering the database and submitted. For example, if you import male and female, 1 and 2 are entered into the database correspondingly.


    Import logic of multiple sheets

    Defaults to Match by sheet name.

    There are three types of multiple sheets import logic: Match by sheet name and Match by sheet position and Match manually.

    Match by sheet name: import according to the sheet page names.

    Match by sheet position: import according to the order of the sheet pages.

    Match manually: business personnel can customize matching relationships on the front end.

    By default, import by intelligent identification results

    Defaults to be enabled.

    1. When enabled, no dialog box will pop up during import process. And it will import directly according to the import mark settings in the template Excel.

    2. When disabled, a dialog box will pop up during import process, and then you can confirm and modify the import configuration.


    2. Cell Setting

    Location

    Explanation

    Application Scenario

    Default as display value.

    You can customize Import Content in Excel Import as Display Value or Actual Value.

    In Excel, cells have Actual Value and Display Value. It is hoped that when importing, one of them can be customized.


    3. Setting During Import Process

    Set the entrance as clicking the Mark the import in the toolbar when previewing, according to the settings of Excel Import Mark in the template, and different configuration item combinations will be presented.

    Name

    Explanation

    Excel sheet name

    Automatically reads all sheets in Excel for selection, and imports the selected sheets according to the settings.

    If there is only one sheet in Excel, it will be automatically selected.

    Title Area

    Used to mark the title area in Excel, such as A1:D1.

    If the template is set to Intelligently identify Excel title area and content areas, it will automatically identify the title area of Excel and you can to modify it.

    Note: Does not support importing in break areas.

    Content Area

    Used to mark the content area in Excel, such as A2:D2.  

    If the template is set to Intelligently identify Excel title area and content areas, it will automatically identify the title area of Excel and you can to modify it.

    Note: Does not support importing in break areas.

    Save

    Similar to temporary storage, it will remember the import settings based on the username and template name.

    After selection, when you import Excel next time, the previous settings will be retained. If you do not select this option, all import settings for this template configured by the user will be cleared.

    If you tick this option, the Intelligently identify Excel title area and content areas setting will no longer take effect, and the saved settings will be prioritized for reading.

    Different Import Scenarios and Recommended Configurations

    According to the Flexibility Configured by the Business Front-end

    Business Front-end Confirmation Item

    Designer Import Configuration

    Operation

    Direct import, no need for confirmation and modification (high convenience, low flexibility).

    Default settings are used except for the title area and content area.

     

    1ACEA9D5-4095-4B7F-AF94-65298860119F.GIF

    You can customize part of configurations according to demands (moderate cost).

    Import according to the intelligent identification results.

    1. Customize import logic of multiple sheets.

    2. Customize the import method of every sheet.

     

    3. Customize import logic snd import methods at the same time. (select the above two options together)

    1. You need to match manually

    555.GIF

    2. You need to choose import methods

    333.GIF

    3. You need to need to match manually and choose import methods

    666.GIF


    You can customize part of configurations according to demands (moderate cost).

    Default matching relationship for multiple sheets and only one import method selected.

    Choose only one import method and select Match by sheet name by default. During the operation process, you need to confirm title or content.

    Note: Supports selecting Clear as import method and Match by sheet name by default. If you need to select other options, you need to confirm in the front-end.

    8656E738-CD2C-4922-AB2A-8C91C6F90FED.GIF

    Customize all import sheets (high flexibility, low convenience), and you can:

    1. Customize matching relationship when importing multiple sheets.

    2. Confirm whether the Excel title area and content area identified intelligently are correct, and you can modify manually.

    3. Customize the import method for each sheet

    111.GIF

    According to the Import Scenario (By Default, Import by the Intelligent Identification Results)

    Import Scenario

    Designer Import Mark

    Business Import Operation

    Download Sample Files

    Single sheet, single row header

     

    Simply configure according to the basic operation steps.

     

    1111.GIFDefault intelligent identification results, and business front-end confirmation is not needed.

     

    Single Sheet with Single Row Header-Excel.xls

    Single Sheet with Single Row Header.cpt.cpt

    Single sheet, single row header and parts outside the content area

     

    Need to enable Support importing parts outside of the content area function.

     

    2222.GIFDefault intelligent identification results, and business front-end confirmation is not needed.

     

    Single Sheet, Single Row Header and Parts Outside the Content Area.cpt

    Single Sheet, Single Row Header and Parts Outside the Content Area-Excel.xls


    Free-entry report, without expansible cells.

     

    Use the headline as the title area, blank lines as the content area, and other fixed cells as the parts outside the content area.

     

     

     

    3333.GIF

    Free-entry Report.xlsx

    Free-entry Report.cpt


    Multi-level header

     

    Use the headline as the parts outside the content area, multi-level headers as the title area.

    4444.GIF

    Multi-level Header-Sales Budget Excel.xlsx

    Multi-level Header-Sales Budget.cpt


    Horizontal import

     

    The direction of import is Horizontal.

     

    5555.GIF

    Horizontal Import.xlsx

    Horizontal Import.cpt

     

    Interface and Data Storage

    JS Interface

    FR.MarkExcelImport.markImportExcel()

    Scenario: Import with custom button.

    Import Configuration Storage Location

    After selecting Save during the import process, the relevant information will be stored in the file finedb.

    fine_excel_mark_conf table:

    fine_excel_mark_sheet_conf table:

    Other Common Questions

    For details, see Common Problems and Solutions for Excel Import.


    Attachment List


    Theme: Data Entry
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy