Excel table exporting method and system based on Java, medium and equipment

Abstract

The invention relates to the field of computer data processing, and discloses a Java-based Excel table exporting method and system, a medium and equipment, and the method comprises the steps: importing an Apache POI library into a Java project, determining to-be-exported data and parameters, a storage path and a file name of an Excel file, and forming a data set by the data and the parameters; the method comprises the steps of creating a Workbook object to serve as an Excel file, and creating at least one Sheet object in the Workbook to serve as a worksheet in the Excel file; the method comprises the following steps of: traversing a data set to be exported, creating a Row object for each row of data, adding a corresponding Cell object in Row as a new cell object, and creating a multi-row and multi-column Excel table according to use requirements; and writing data in the cells, and exporting the Excel form file according to the save path and the file name. The method has the advantages of simplicity and convenience in operation, high efficiency, good compatibility and the like.

Description

Technical Field

[0001] The present invention relates to the technical field of computer data processing, and in particular to a method, system, medium and device for exporting an Excel table based on Java. Background Art

[0002] With the development of informatization, data processing and analysis play an increasingly important role in all walks of life. As a widely used spreadsheet software, Excel's data import and export functions are of great significance for data processing and analysis. However, traditional Excel table export methods usually rely on specific software or plug-ins, which have problems such as complex operation, low efficiency, and poor compatibility. Summary of the invention

[0003] In view of the above problems, the purpose of the present invention is to provide a Java-based Excel table export method, system, medium and device, which has the advantages of simple operation, high efficiency and good compatibility.

[0004] To achieve the above-mentioned purpose, in the first aspect, the technical solution adopted by the present invention is: a Java-based Excel table export method, which includes: importing the Apache POI library in the Java project, determining the data to be exported and the parameters, save path and file name of the Excel file, and forming a data set with the data and parameters; creating a Workbook object as an Excel file, and creating at least one Sheet object in the Workbook as a worksheet in the Excel file; traversing the data set to be exported, creating a Row object for each row of data, adding the corresponding Cell object in the Row as a new cell object, and creating a multi-row and multi-column Excel table according to usage needs; writing data in the cell, and exporting the Excel table file according to the save path and file name.

[0005] Furthermore, the Apache POI library can create, read, and modify Microsoft Office format files; The data to be exported includes database queries and in-memory collections; The parameters of an Excel file include the file's style, format, and font.

[0006] Furthermore, create at least one Sheet object in the Workbook, including: Create a new Excel workbook object through the XSSFWorkbook class in the Apache POI library, and then use the createSheet method to create a new worksheet object in the workbook.

[0007] Furthermore, create a Row object for each row of data, and add the corresponding Cell object in the Row as a new cell object, including: Use the createRow method to create a new row object in the worksheet, and use the createCell method to create a new cell object in the row; Use the CellStyle class to set the value and style of each Cell object.

[0008] Further, it also includes: setting styles for specific cells or cell regions, including fonts, colors, and borders; Set the style and format of the entire Sheet or Workbook.

[0009] Further, export the Excel spreadsheet file according to the save path and file name, including: Use the FileOutputStream class to write the workbook object into the corresponding file to export the Excel spreadsheet file; The exported Excel spreadsheet file can be opened and edited in any software that supports Excel format.

[0010] Furthermore, it also includes exception handling steps to ensure that the program can be saved and exited if an error occurs during runtime.

[0011] In the second aspect, the technical solution adopted by the present invention is: a Java-based Excel table export system, which includes: an initialization module, which imports the Apache POI library in the Java project, determines the data to be exported and the parameters, save path and file name of the Excel file, and the data and parameters constitute a data set; a Sheet creation module, which creates a Workbook object as an Excel file, and creates at least one Sheet object in the Workbook as a worksheet in the Excel file; a row and cell creation module, which traverses the data set to be exported, creates a Row object for each row of data, adds the corresponding Cell object in the Row as a new cell object, and creates a multi-row and multi-column Excel table according to usage needs; an export module, which writes data in the cell and exports the Excel table file according to the save path and file name.

[0012] In a third aspect, the technical solution adopted by the present invention is: a computer-readable storage medium storing one or more programs, wherein the one or more programs include instructions, and when the instructions are executed by a computing device, the computing device executes any one of the above methods.

[0013] In a fourth aspect, the technical solution adopted by the present invention is: a computing device, comprising: one or more processors, a memory and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, and the one or more programs include instructions for executing any of the above methods.

[0014] The present invention adopts the above technical solution, which has the following advantages: 1. The present invention is easy to operate and can realize automatic export of Excel tables through Java programming language without relying on specific software or plug-ins. The operation is simple and easy.

[0015] 2. The present invention is highly efficient and utilizes the API provided by the Apache POI library to operate Excel files. It has efficient data processing capabilities and can quickly generate a large number of Excel tables.

[0016] 3. The present invention has good compatibility. The exported Excel file adopts the standard Excel format and can be opened and edited in any software supporting the Excel format, thus having good compatibility.

[0017] In summary, the present invention can improve the efficiency and flexibility of data processing and has important practical significance and application value. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] Figure 1 It is a flow chart of a method for exporting an Excel table based on Java in an embodiment of the present invention. DETAILED DESCRIPTION

[0019] In order to make the purpose, technical solution and advantages of the embodiment of the present invention clearer, the technical solution of the embodiment of the present invention will be clearly and completely described below in conjunction with the drawings of the embodiment of the present invention. Obviously, the described embodiment is a part of the embodiment of the present invention, not all of the embodiments. Based on the described embodiment of the present invention, all other embodiments obtained by ordinary technicians in this field belong to the scope of protection of the present invention.

[0020] It should be noted that the terms used herein are only for describing specific embodiments and are not intended to limit exemplary embodiments according to the present invention. As used herein, unless the context clearly indicates otherwise, the singular form is also intended to include the plural form. In addition, it should be understood that when the terms "comprising" and / or "including" are used in this specification, it indicates the presence of features, steps, operations, devices, components and / or combinations thereof.

[0021] In one embodiment of the present invention, a Java-based Excel table export method is provided. In this embodiment, Figure 1 As shown, the method comprises the following steps: 1) Import the Apache POI library into the Java project, determine the data to be exported and the parameters, save path and file name of the Excel file, and the data and parameters constitute a data set; 2) Create a Workbook object as an Excel file, and create at least one Sheet object in the Workbook as a worksheet in the Excel file; 3) Traverse the data set to be exported, create a Row object for each row of data, add the corresponding Cell object in the Row as a new cell object, and create an Excel table with multiple rows and columns as needed; 4) Write data in the cells and export the Excel spreadsheet file according to the save path and file name.

[0022] In step 1) above, the Apache POI library can create, read, and modify Microsoft Office format files; import the Apache POI library into the Java project so that you can use the API it provides to operate Excel files.

[0023] In this embodiment, the data to be exported includes database queries and collections in memory; the parameters of the Excel file include the style, format, and font of the file.

[0024] Specifically, import the dependency of the Apache POI library. In a Maven project, you can add the following dependency to the pom.xml file: <dependency> <groupid> org.apache.poi< / groupid> <artifactid> poi< / artifactid> <version> 4.1.2< / version> < / dependency> <dependency> <groupid> org.apache.poi< / groupid> <artifactid> poi-ooxml< / artifactid> <version> 4.1.2< / version> < / dependency> In the above step 2), at least one Sheet object is created in the Workbook, specifically: a new Excel workbook object is created through the XSSFWorkbook class in the Apache POI library, and then a new worksheet object is created in the workbook using the createSheet method.

[0025] Specifically, for example, to create a new Excel workbook object through the XSSFWorkbook class: Workbookworkbook = new XSSFWorkbook(); Create a new worksheet in a workbook using the createSheet method. For example: Sheet sheet = workbook.createSheet("Sheet1").

[0026] In the above step 3), a Row object is created for each row of data, and the corresponding Cell object is added to the Row as a new cell object, including the following steps: 3.1) Use the createRow method to create a new row object in the worksheet, use the createCell method to create a new cell object in the row, and set its value; For example, Row row = sheet.createRow(0); Use the createCell method to create a new cell object in the row and set its value: Cell cell = row.createCell(0); cell.setCellValue("Hello").

[0027] 3.2) Use the CellStyle class to set the value and style of each Cell object. In this embodiment, the setCellValue method is used to set the content of the cell, which can include text, numbers, formulas, etc.

[0028] For example: CellStyle cellStyle = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true); font.setColor(IndexedColors.RED.getIndex()); cellStyle.setFont(font); cell.setCellStyle(cellStyle).

[0029] In this embodiment, the following steps are also included: 3.3) Set styles for specific cells or cell areas, including fonts, colors, and borders; 3.4) Set the style and format of the entire Sheet or Workbook.

[0030] In the above step 4), the Excel spreadsheet file is exported according to the saving path and file name, specifically: the FileOutputStream class is used to write the workbook object into the corresponding file to realize the export of the Excel spreadsheet file; the Excel spreadsheet file exported by the present invention can be opened and edited in any software that supports the Excel format.

[0031] In this example, the Excel file is saved and the FileOutputStream class is used to save the workbook to a file, for example: try (FileOutputStream fileOut = new FileOutputStream("output.xlsx")){ workbook.write(fileOut).

[0032] In this embodiment, data is written into the cell, for example: cell.setCellValue("Hello, World!"); In this embodiment, an Excel file is exported, for example: FileOutputStream outputStream = new FileOutputStream("output.xlsx"); workbook.write(outputStream); workbook.close().

[0033] In the above embodiments, the step of exception handling is also included to ensure that the program can be saved and then exited when an error occurs during operation. The exception handling method in the prior art can be used for the exception handling, which will not be described in detail here.

[0034] In summary, when the present invention is used, an Excel file named output.xlsx will be created in the project directory, which contains a cell with red bold text "Hello". This is the basic step of exporting Excel files using Java. Using the Apache POI library, you can further operate workbooks, worksheets, and cells, such as adding data, setting formats, merging cells, etc.

[0035] In one embodiment of the present invention, a Java-based Excel table export system is provided, which includes: Initialization module, importing Apache POI library in Java project, determining the data to be exported and the parameters, saving path and file name of Excel file, and forming a data set with the data and parameters; Sheet creates a module that creates a Workbook object as an Excel file and creates at least one Sheet object in the Workbook as a worksheet in the Excel file. The row and cell creation module traverses the data set to be exported, creates a Row object for each row of data, adds the corresponding Cell object in the Row as a new cell object, and creates an Excel table with multiple rows and columns according to usage needs; Export module, write data in the cell, and export the Excel spreadsheet file according to the save path and file name.

[0036] In the above embodiment, the Apache POI library can create, read and modify Microsoft Office format files; The data to be exported includes database queries and in-memory collections; The parameters of an Excel file include the file's style, format, and font.

[0037] In the above embodiment, creating at least one Sheet object in the Workbook includes: creating a new Excel workbook object through the XSSFWorkbook class in the Apache POI library, and then using the createSheet method to create a new worksheet object in the workbook.

[0038] In the above embodiment, a Row object is created for each row of data, and a corresponding Cell object is added to the Row as a new cell object, including: Use the createRow method to create a new row object in the worksheet, and use the createCell method to create a new cell object in the row; Use the CellStyle class to set the value and style of each Cell object.

[0039] In this embodiment, it also includes: setting a style for a specific cell or cell area, including a font, color, and border; Set the style and format of the entire Sheet or Workbook.

[0040] In the above embodiment, exporting the Excel spreadsheet file according to the save path and file name includes: Use the FileOutputStream class to write the workbook object into the corresponding file to export the Excel spreadsheet file; The exported Excel spreadsheet file can be opened and edited in any software that supports Excel format.

[0041] In the above embodiment, the export method of the present invention also includes an exception handling step to ensure that the method can save and exit when an error occurs during runtime.

[0042] The system provided in this embodiment is used to execute the above-mentioned method embodiments. Please refer to the above-mentioned embodiments for specific processes and detailed contents, which will not be repeated here.

[0043] A computing device provided in one embodiment of the present invention may be a terminal, which may include: a processor, a communications interface, a memory, a display screen, and an input device. The processor, the communications interface, and the memory communicate with each other through a communication bus. The processor is used to provide computing and control capabilities. The memory includes a non-volatile storage medium and an internal memory, wherein the non-volatile storage medium stores an operating system and a computer program, and the computer program is executed by the processor to implement the methods in the above embodiments; the internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The communication interface is used to communicate with an external terminal in a wired or wireless manner, and the wireless manner may be implemented through WIFI, a management network, NFC (near field communication) or other technologies. The display screen may be a liquid crystal display screen or an electronic ink display screen, and the input device may be a touch layer covered on the display screen, or a key, trackball or touchpad provided on the housing of the computing device, or an external keyboard, touchpad or mouse, etc. The processor may call the logic instructions in the memory.

[0044] In addition, the logic instructions in the above-mentioned memory can be implemented in the form of software functional units and can be stored in a computer-readable storage medium when sold or used as an independent product. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product, which is stored in a storage medium and includes several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: various media that can store program codes, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0045] In one embodiment of the present invention, a computer program product is provided, wherein the computer program product includes a computer program stored on a non-transitory computer-readable storage medium, wherein the computer program includes program instructions. When the program instructions are executed by a computer, the computer can execute the methods provided by the above-mentioned method embodiments.

[0046] In one embodiment of the present invention, a non-transitory computer-readable storage medium is provided, wherein the non-transitory computer-readable storage medium stores server instructions, and the computer instructions enable a computer to execute the methods provided in the above embodiments.

[0047] The above embodiment provides a computer-readable storage medium, whose implementation principle and technical effect are similar to those of the above method embodiment, and will not be repeated here.

[0048] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0049] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture including an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0050] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0051] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A Java-based Excel table export method, characterized in that: include: Import the Apache POI library into the Java project, determine the data to be exported and the parameters, save path and file name of the Excel file, and form a data set with the data and parameters; Create a Workbook object as an Excel file, and create at least one Sheet object in the Workbook as a worksheet in the Excel file; Traverse the data set to be exported, create a Row object for each row of data, add the corresponding Cell object in the Row as a new cell object, and create an Excel table with multiple rows and columns as needed; Write data in the cell and export the Excel spreadsheet file according to the save path and file name.

2. The Java-based Excel table export method as claimed in claim 1, characterized in that: The Apache POI library can create, read, and modify Microsoft Office format files; The data to be exported includes database queries and in-memory collections; The parameters of an Excel file include the file's style, format, and font.

3. The Java-based Excel table export method as claimed in claim 1, characterized in that: Create at least one Sheet object in the Workbook, including: Create a new Excel workbook object through the XSSFWorkbook class in the Apache POI library, and then use the createSheet method to create a new worksheet object in the workbook.

4. The Java-based Excel table export method as claimed in claim 1, characterized in that: Create a Row object for each row of data, and add the corresponding Cell object in the Row as a new cell object, including: Use the createRow method to create a new row object in the worksheet, and use the createCell method to create a new cell object in the row; Use the CellStyle class to set the value and style of each Cell object.

5. The Java-based Excel table export method as claimed in claim 4, characterized in that: Also includes: Set styles for specific cells or cell ranges, including fonts, colors, and borders; Set the style and format of the entire Sheet or Workbook.

6. The Java-based Excel table export method according to claim 1, characterized in that: Export Excel spreadsheet files according to the save path and file name, including: Use the FileOutputStream class to write the workbook object into the corresponding file to export the Excel spreadsheet file; The exported Excel spreadsheet file can be opened and edited in any software that supports Excel format.

7. The Java-based Excel table export method according to claim 1, characterized in that: It also includes exception handling steps to ensure that the program can save and exit if an error occurs during runtime.

8. A Java-based Excel table export system, characterized in that: include: Initialization module, importing Apache POI library in Java project, determining the data to be exported and the parameters, saving path and file name of Excel file, and forming a data set with the data and parameters; Sheet creates a module that creates a Workbook object as an Excel file and creates at least one Sheet object in the Workbook as a worksheet in the Excel file. The row and cell creation module traverses the data set to be exported, creates a Row object for each row of data, adds the corresponding Cell object in the Row as a new cell object, and creates an Excel table with multiple rows and columns according to usage needs; Export module, write data in the cell, and export the Excel spreadsheet file according to the save path and file name.

9. A computer-readable storage medium storing one or more programs, characterized in that: The one or more programs include instructions, which, when executed by a computing device, cause the computing device to perform any one of the methods of claims 1 to 7.

10. A computing device, characterized in that include: One or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, and the one or more programs include instructions for executing any one of the methods described in claims 1 to 7.

Images