Printing method and device of Excel-based printing template system

Abstract

The invention provides a printing method and device of an Excel-based printing template system. The method comprises the following steps: making an Excel template according to a receipt printing template requirement of a unit; carrying out the binding of the Excel template; the Excel template obtains data from a database, data filling is carried out through the Apache POI technology, and a complete Excel file filled with the data is generated; converting the Excel file into an HTML (Hypertext Markup Language) code; matching all bound functions in the HTML code through a regular expression, running corresponding function code blocks, and replacing the bound functions with function running results; according to the printing size configured by the template, a script is embedded in an HTML code, and the scaling is adjusted; and the browser executes the printing operation to complete printing, so that the user can quickly print, and the operation difficulty is reduced.

Description

Technical Field

[0001] The present invention relates to the technical field of Excel printing, and in particular to a printing method and device based on an Excel printing template system. Background Art

[0002] In modern information systems, the printing function is essential to support online and offline document approval work. Printing documents in information systems usually involves the following steps and precautions:

[0003] 1. Integrated printing function:

[0004] Information systems (such as ERP, CRM, financial management systems, etc.) usually have built-in or integrated printing functions. Make sure the printing module is enabled in the system and is compatible with the printer.

[0005] 2. Printing template design:

[0006] Template creation: Use the template design tool provided by the system to create or customize a document printing template that meets your needs. The template should include company logo, document information (such as order number, date, customer information) and other required fields.

[0007] Field Mapping: Map the data fields in the system with the fields in the template to ensure that the data is correctly filled in when printing.

[0008] 3. Print configuration:

[0009] Printer Settings: Select the default printer in the system and set the printer type (such as thermal printer, laser printer, etc.).

[0010] Paper Settings: Set the type and size of printing paper (such as A4 paper, thermal paper, envelope, etc.).

[0011] Print format: Set the print format including margins, fonts, line spacing, etc.

[0012] 4. Printing operation:

[0013] Select documents: Select the documents to be printed in the information system (such as invoices, delivery notes, purchase orders, etc.).

[0014] Preview printing: Most systems provide a print preview function, which allows you to view the document effect before printing and check for errors or omissions.

[0015] Confirm and Print: Click the Print button to actually print.

[0016] The existing system is a printing system designed based on Jasper. The Jasper printing system is an open source printing system developed based on Java and is designed to generate reports in various formats. Although the Jasper printing system has many advantages in report generation, it also has many disadvantages and limitations:

[0017] 1. Steep learning curve

[0018] Complexity: JasperReports provides powerful functions, but this also means that its learning curve is steep. Beginners may need to spend a lot of time to understand and master its usage, report design, and configuration options.

[0019] Documentation: Although there is some documentation and community support, for complex reporting requirements, the official documentation may not be sufficient, and users need to rely more on community support and third-party tutorials.

[0020] 2. Difficulty of customization and expansion

[0021] Limited flexibility: Although JasperReports provides some customization capabilities, implementing highly customized reports may require in-depth programming skills, especially when you need to modify default behavior or implement specific formatting.

[0022] Plugin ecosystem: JasperReports may have relatively few plugins and extension ecosystems, and users may need to develop some functions themselves.

[0023] 3. Difficulty in debugging

[0024] Troubleshooting: If you encounter errors or problems during report design and generation, the debugging process may be complicated, especially if the error message is not clear or detailed enough.

[0025] Log analysis: JasperReports relies on log output to troubleshoot problems, but the log information may sometimes not be detailed enough, making it difficult to locate the problem.

[0026] 4. Limitations of design tools

[0027] User Interface: JasperReports design tools (such as Jaspersoft Studio), while powerful, may have an interface that is not modern enough and has certain limitations in terms of user-friendliness and intuitiveness.

[0028] Design efficiency: When designing complex reports, layout and formatting can take a lot of time, especially when the report contains complex subreports, charts, and dynamic content.

[0029] 5. Integration Difficulty

[0030] Integration with other systems: JasperReports cannot be directly integrated with external systems. You need to install JasperReports separately to design templates. This is a relatively high barrier for customers to use. Summary of the invention

[0031] The technical problem to be solved by the present invention is to provide a printing method and device based on an Excel printing template system, which facilitates users to print quickly and reduces the difficulty of operation.

[0032] In a first aspect, the present invention provides a printing method of a printing template system based on Excel, comprising:

[0033] Step 1: Create an Excel template according to the unit's document printing template requirements; and bind the Excel template;

[0034] Step 2: The Excel template obtains data from the database and fills the data using Apache POI technology to generate an Excel file filled with complete data;

[0035] Step 3, converting the Excel file into HTML code;

[0036] Step 4: Match all bound functions in the HTML code through regular expressions, run the corresponding function code blocks, and replace the bound functions with the function running results; embed the script in the HTML code to adjust the scaling according to the print size configured in the template;

[0037] Step 5: The browser executes the print operation to complete printing.

[0038] In a second aspect, the present invention provides a printing device based on an Excel printing template system, comprising:

[0039] Template binding module, to make Excel templates according to the unit's document printing template requirements; and bind the Excel templates;

[0040] In the data filling module, the Excel template obtains data from the database and fills the data using Apache POI technology to generate an Excel file filled with complete data;

[0041] A code conversion module converts the Excel file into HTML code;

[0042] The calculation setting module matches all bound functions in the HTML code through regular expressions, runs the corresponding function code blocks, and replaces the bound functions with the function running results; according to the printing size configured by the template, a script is embedded in the HTML code to adjust the scaling ratio;

[0043] Print module, the browser executes the print operation to complete printing.

[0044] One or more technical solutions provided in the embodiments of the present invention have at least the following technical effects or advantages:

[0045] 1. High flexibility: Customized format: Excel provides a wealth of formatting functions, which can customize the font, color, border, background color, etc. of cells to meet the printing needs of various documents or reports; Easy to use: No programming knowledge is required, users can adjust the table layout, adjust the column width, set paging, etc. through simple drag and drop operations, which is convenient for quickly generating and printing various documents.

[0046] 2. Convenient data management: Data input and editing: Excel provides a friendly interface and tools to facilitate users to input and edit data, and can filter, sort, group and other operations as needed; Formulas and calculations: Excel's powerful formula and function functions allow users to automatically calculate totals, averages, maximum and minimum values, etc. before printing, thereby improving work efficiency.

[0047] 3. Efficient batch printing: Batch generation of documents: By reading data from the database and filling it into Excel tables, you can easily generate a large number of documents, such as invoices, orders, etc., and then print them in batches to reduce the time of manual operations; Label printing: Using Excel, you can quickly design and print labels, such as barcode labels, address labels, etc., which is especially useful in logistics and warehousing management.

[0048] 4. Easy to integrate and expand: Integration with other applications: Excel can easily exchange data with other software (such as ERP system, CRM system), so that data can be imported from other systems into Excel for format adjustment and printing.

[0049] 5. Print preview and proofreading: Print preview: Before actual printing, users can perform print preview in Excel to view the actual printing effect and promptly discover and correct possible formatting problems; Adjust headers and footers: Users can customize header and footer content (such as date, page number, company logo, etc.) to ensure that the printed documents or reports meet the standards of the enterprise or organization.

[0050] 6. Cost saving: Reduce paper waste: Through preview and format adjustment, paper waste caused by incorrect format or content exceeding the page can be reduced; Reduce IT costs: Compared with using dedicated report generation software, Excel-based printing solutions are less expensive and are especially suitable for small and medium-sized enterprises or projects with limited budgets.

[0051] The above description is only an overview of the technical solution of the present invention. In order to more clearly understand the technical means of the present invention, it can be implemented according to the contents of the specification. In order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] The present invention will be further described below in conjunction with embodiments with reference to the accompanying drawings.

[0053] Figure 1 is a flow chart of the method in Embodiment 1 of the present invention;

[0054] Figure 2 It is a schematic diagram of the structure of the device in the second embodiment of the present invention. DETAILED DESCRIPTION

[0055] The technical solution in the embodiment of the present application has the following general idea:

[0056] The Excel-based printing template system can easily format data into printouts that meet the requirements. For users, Excel has almost no threshold for use, and what you see is what you get. The following are the basic steps and key points for designing such a system:

[0057] 1. Define requirements

[0058] Determine the print content: clarify the data content that needs to be printed, including field name, data format, etc.

[0059] Define template types: Determine how many templates are needed (such as invoice templates, report templates, etc.) and what the format and style requirements are for each template.

[0060] Output format: determines whether the print output is single page, multiple pages, whether batch printing is supported, etc.

[0061] 2. Design Excel Template

[0062] Create basic templates: Use Excel to create one or more basic template files. The templates contain the required formats, styles, table layouts, borders, unit logos and other fixed elements.

[0063] Placeholders: Use placeholders (such as {name}, {date}, {amount}) in templates to identify data locations that need to be filled in the future.

[0064] Naming conventions: Name template files to make them easy to identify and manage.

[0065] 3. Print and Export

[0066] Print Settings: Configure print settings such as paper size, margins, print orientation, etc.

[0067] Print preview: Provides a print preview function so that users can view the output effect before printing. In the existing Excel table, there is no data to fill in, so the preview is meaningless; however, when previewing the present invention, the data will be automatically filled in, and then previewed again, the result print form will be presented.

[0068] Export options: Provide options to export documents to PDF or other formats for users to save and send.

[0069] 4. User Interface

[0070] Select data and template: Design a user interface that allows the user to select the data source and the desired print template.

[0071] Parameter settings: Users can enter some dynamic parameters in the interface, which will be used to generate the final print output.

[0072] Preview and Print: Provide a preview button so that users can check the effect before printing; the print button can directly start the printing process.

[0073] 5. Testing and Optimization

[0074] Test templates and data replacement: Test the templates in different usage scenarios to ensure that the data can be filled and printed correctly.

[0075] User feedback: Collect user feedback and continuously optimize the usability and functionality of the template system.

[0076] 6. System Integration

[0077] Online integration: Users do not need to install office or wps when editing Excel templates. The system provides an online Excel editor. Users can edit Excel online directly through the browser and deeply integrate with the business system.

[0078] From a technical perspective, this solution uses a technical solution that generates an Excel file based on the Excel template configured by the user and the filled data, and then converts the Excel file into Html code through technical means, and finally submits the Html code to the browser for printing, which can achieve lossless printing. Because the final generated HTML code is text-based, the code can be flexibly modified to expand other complex requirements, such as QR codes, barcodes, pictures, etc.

[0079] The specific technical implementation plan is as follows:

[0080] 1. Excel template creation

[0081] 1. Template design

[0082] Offline users can create templates based on office, wps, libreoffice, and other online excel editors according to the unit's document printing template requirements.

[0083] 2. Field Binding

[0084] For filling in data, ordinary fields are bound through {field name}, and floating tables are bound through {list.field name}; a floating table is a table, the data is read from the database, and then automatically filled according to the fields bound to the columns, and the number of rows in the database is equal to the number of rows in the Excel table.

[0085] 3. Function Application

[0086] The template engine has built-in regular functions, which are bound by function name (field name), such as the common summation function: Sum(list.number).

[0087] In addition, some commonly used functions are built-in: QR code function QrCode (field name), barcode function BarCode (field name), image function Image (field name), etc.

[0088] 2. Fill in Excel data

[0089] The Excel template fetches data from the database and fills in the data using Apache POI technology to generate an Excel file filled with complete data.

[0090] 3. Convert Excel to HTML code

[0091] Because the browser cannot print Excel files directly, they need to be converted into HTML code. This solution uses Spire XLS technology to convert Excel files into HTML code.

[0092] 4. HTML code preprocessing

[0093] 1. Function preprocessing

[0094] Use regular expressions to match all bound functions in the HTML code, run the corresponding function code block, and replace the bound function with the function running result.

[0095] Normal data: Direct replacement.

[0096] Image category: converted to base64 Label.

[0097] 2. Template size scaling

[0098] Dynamically embed the print size in the HTML code according to the template configuration <script>脚本，调整缩放比例。

[0099] / / 缩放页面

[0100] vartabLeWidth＝document.querySelector("tabLe").offsetWidth；

[0101] var scale＝${widthMillimetre} / 10*37.8*(1 / window.devicePixeLRatio) / tableWidth；

[0102] vartableWrappers＝document.querySelectorAll("body>div")；

[0103] for(var i＝日；i<tableWrappers.Length；1++)

[0104] vartabLeWrapper＝tabLeWrappers[i]；

[0105] tableWrapper.styLe.zoom＝scale；

[0106] }

[0107] / / 调整上下边距

[0108] var paddingTopBottom＝${paddingMillimetre} / 10*37.8*(1 / window.devicePixeLRatio)；

[0109] for(var i＝0；i<tabLeWrappers.Length；1++)

[0110] vartabLeWrapper＝tabLeWrappers[i]；

[0111] tabLeWrapper.styLe.paddingTop＝paddingTopBottom+"px"；

[0112] tabLeWrapper.styLe.paddingBottom＝paddingTopBottom+"px"；

[0113] }

[0114] 五、浏览器打印

[0115] 前端点击打印，后端返回的是自带缩放脚本、包含完整样式和数据填充的html代码。前端通过在页面构建一个不可见的iframe并渲染html代码。渲染完成调用iframe.contentWindow.print()提交给浏览器打印。

[0116] 实施例一

[0117] 如图1所示，本实施例提供一种基于Excel的打印模板系统的打印方法，包括：

[0118] 步骤1、根据单位的单据打印模板需求进行Excel模板制作；并将Excel模板进行绑定；

[0119] 步骤2、Excel模板通过从数据库获取数据，并利用Apache POI技术进行数据填充，生成填充完数据完整的Excel文件；

[0120] 步骤3、将所述Excel文件转换成HTML代码；

[0121] 步骤4、通过正则表达式匹配出HTML代码中所有绑定的函数，运行对应的函数代码块，并将函数运行结果替换绑定的函数；根据模板配置的打印尺寸，在HTML代码中，嵌入script脚本，调整缩放比例；

[0122] 步骤5、浏览器执行打印操作，完成打印。

[0123] 本实施地，优选地，所述步骤1具体为：根据单位的单据打印模板需求进行Excel模板制作；将Excel模板中的字段通过字段名的方式进行绑定，浮动表通过list.字段名进行绑定；设置所需的函数，并进行绑定。

[0124] 本实施地，优选地，所述步骤2具体为：根据字段名以及list.字段名从数据库中获取对应的数据，并利用Apache POI技术进行数据填充，生成填充完数据完整的Excel文件。

[0125] 本实施地，优选地，所述步骤3具体为：采用Spire XLS技术将所述Excel文件转换成HTML代码。

[0126] 基于同一发明构思，本申请还提供了与实施例一中的方法对应的装置，详见实施例二。

[0127] 实施例二

[0128] 如图2所示，在本实施例中提供了一种基于Excel的打印模板系统的打印装置，包括：

[0129] 模板绑定模块，根据单位的单据打印模板需求进行Excel模板制作；并将Excel模板进行绑定；

[0130] 填充数据模块，Excel模板通过从数据库获取数据，并利用Apache POI技术进行数据填充，生成填充完数据完整的Excel文件；

[0131] 转换代码模块，将所述Excel文件转换成HTML代码；

[0132] 计算设置模块，通过正则表达式匹配出HTML代码中所有绑定的函数，运行对应的函数代码块，并将函数运行结果替换绑定的函数；根据模板配置的打印尺寸，在HTML代码中，嵌入script脚本，调整缩放比例；

[0133] 打印模块，浏览器执行打印操作，完成打印。

[0134] 本实施地，优选地，所述模板绑定模块具体为：根据单位的单据打印模板需求进行Excel模板制作；将Excel模板中的字段通过字段名的方式进行绑定，浮动表通过list.字段名进行绑定；设置所需的函数，并进行绑定。

[0135] 本实施地，优选地，所述填充数据模块具体为：根据字段名以及list.字段名从数据库中获取对应的数据，并利用Apache POI技术进行数据填充，生成填充完数据完整的Excel文件。

[0136] 本实施地，优选地，所述转换代码模块具体为：采用Spire XLS技术将所述Excel文件转换成HTML代码。

[0137] 由于本发明实施例二所介绍的装置，为实施本发明实施例一的方法所采用的装置，故而基于本发明实施例一所介绍的方法，本领域所属人员能够了解该装置的具体结构及变形，故而在此不再赘述。凡是本发明实施例一的方法所采用的装置都属于本发明所欲保护的范围。

[0138] 虽然以上描述了本发明的具体实施方式，但是熟悉本技术领域的技术人员应当理解，我们所描述的具体的实施例只是说明性的，而不是用于对本发明的范围的限定，熟悉本领域的技术人员在依照本发明的精神所作的等效的修饰以及变化，都应当涵盖在本发明的权利要求所保护的范围内。< / script>

Claims

1. A printing method based on an Excel printing template system, characterized in that: include: Step 1: Create an Excel template according to the unit's document printing template requirements; And bind the Excel template; Step 2: The Excel template obtains data from the database and fills the data using Apache POI technology to generate an Excel file filled with complete data; Step 3, converting the Excel file into HTML code; Step 4: Match all bound functions in the HTML code through regular expressions, run the corresponding function code blocks, and replace the bound functions with the function running results; embed the script in the HTML code to adjust the scaling according to the print size configured in the template; Step 5: The browser executes the print operation to complete printing.

2. The printing method of the Excel-based printing template system according to claim 1, characterized in that: The step 1 specifically includes: making an Excel template according to the unit's document printing template requirements; binding the fields in the Excel template by field name, and binding the floating table by list.field name; Set up the required functions and make the bindings.

3. The printing method of the Excel-based printing template system according to claim 2, characterized in that: The step 2 is specifically as follows: obtaining corresponding data from the database according to the field name and list.field name, and filling the data using Apache POI technology to generate an Excel file filled with complete data.

4. The printing method of the Excel-based printing template system according to claim 1, characterized in that: The step 3 specifically includes: using Spire XLS technology to convert the Excel file into HTML code.

5. A printing device based on an Excel printing template system, characterized in that: include: Template binding module, to make Excel templates according to the unit's document printing template requirements; And bind the Excel template; In the data filling module, the Excel template obtains data from the database and fills the data using Apache POI technology to generate an Excel file filled with complete data; A code conversion module converts the Excel file into HTML code; The calculation setting module matches all bound functions in the HTML code through regular expressions, runs the corresponding function code blocks, and replaces the bound functions with the function running results; according to the printing size configured by the template, a script is embedded in the HTML code to adjust the scaling ratio; Print module, the browser executes the print operation to complete printing.

6. The printing device of the Excel-based printing template system according to claim 5, characterized in that: The template binding module specifically includes: making an Excel template according to the unit's document printing template requirements; binding the fields in the Excel template by field name, and binding the floating table by list.field name; Set up the required functions and make the bindings.

7. The printing device of the Excel-based printing template system according to claim 6, characterized in that: The data filling module specifically obtains corresponding data from the database according to the field name and list.field name, and fills the data using Apache POI technology to generate an Excel file filled with complete data.

8. The printing device of the Excel-based printing template system according to claim 5, characterized in that: The code conversion module specifically uses Spire XLS technology to convert the Excel file into HTML code.

Images