An Excel table import and export method and device based on apache.poi

Abstract

The present invention discloses an Excel table import and export method and device based on apache.poi, which relates to the field of java technology applications; based on apache.poi, an Excel table import and export method is provided using java classes, wherein the Excel table import method includes an import method without sheet page selection and an import method with sheet page selection, and the Excel table export method includes an export method without custom sheet page names and an export method with custom sheet page names. The method of the present invention simplifies the operation of importing and exporting excel tables, and can realize the import or export of data by passing in fewer parameters.

Description

Technical Field

[0001] The present invention discloses a method and a device, relating to the field of java technology applications, specifically an Excel table import and export method and device based on apache.poi. Background Art

[0002] Existing java tools for apache.poi table processing can achieve various processes for excel tables, but the process is cumbersome. Although many frameworks come with Excel table import and export tools based on the apache.poi toolkit, such tools rely too much on the rules set by the framework, have more parameters to pass, and the process of importing and exporting tables is not simplified enough. Summary of the Invention

[0003] In view of the problems of the prior art, the present invention provides an Excel table import and export method and device based on apache.poi, which simplifies the operation of importing and exporting excel tables. By passing fewer parameters, the import or export of data can be achieved.

[0004] The specific solution proposed by the present invention is as follows:

[0005] The present invention provides an Excel table import and export method based on apache.poi. Based on apache.poi, java classes are used to provide Excel table import and export methods. The Excel table import method includes an import method without sheet page selection and an import method with sheet page selection. The Excel table export method includes an export method without custom sheet page name and an export method with custom sheet page name.

[0006] When importing an Excel table, it is judged whether the sheet page name is empty. If so, the import method without sheet page selection is called, relevant parameters are passed, and an empty string is passed to represent the default table sheet page name.

[0007] Otherwise, the import method with sheet page selection is called, relevant parameters are passed, and the custom imported sheet page name is passed to select the sheet page.

[0008] When exporting an Excel table, it is judged whether the sheet page name is empty. If so, the export method without custom sheet page name is called, relevant parameters are passed, and an empty string is passed to represent the default table sheet page name.

[0009] Otherwise, call the export method that includes the custom sheet name, pass the relevant parameters, and pass the custom sheet name.

[0010] Further, when importing an Excel table in the described Excel table import and export method based on apache.poi, it includes:

[0011] Create a wb object through poi and write the file stream into the wb object.

[0012] Judge whether the sheet name is empty. If it is, call the import method that does not include sheet selection and set the default table sheet name.

[0013] Declare the relevant parameters returned by the import method that does not include sheet selection. The relevant parameters include a map object, a data list, and header data.

[0014] Obtain the header data and the values of each row and column according to the number of data rows, and after processing, put them into the map object and return.

[0015] Further, when importing an Excel table in the described Excel table import and export method based on apache.poi, it includes:

[0016] Create a wb object through poi and write the file stream into the wb object.

[0017] Judge whether the sheet name is empty. If it is not, call the import method that includes sheet selection and customize the imported sheet name.

[0018] Declare the relevant parameters returned by the import method that includes sheet selection. The relevant parameters include a map object, a data list, and header data.

[0019] Obtain the header data and the values of each row and column according to the number of data rows, and after processing, put them into the map object and return.

[0020] Further, when exporting an Excel table in the described Excel table import and export method based on apache.poi, it includes:

[0021] Create empty wb and sheet table-related objects through poi.

[0022] Judge whether the sheet name is empty. If it is, call the export method that does not include the custom sheet name and set the default sheet name.

[0023] Pass relevant parameters through the export method that does not include a custom sheet name. The relevant parameters include a data list, a table header, and an export path.

[0024] Among them, set the table width according to the relevant parameters, create a table header, loop through the table header to create cell objects, input the values of the table header according to the cells, loop through the data list, and input the data.

[0025] Pass in the export path, create an out object, and write the table to the export path.

[0026] Furthermore, when exporting an Excel table in the described Excel table import and export method based on apache.poi, it includes:

[0027] Create empty wb and sheet table-related objects through poi.

[0028] Judge whether the sheet name is empty. If not, call the export method that includes a custom sheet name, and select a custom sheet name.

[0029] Pass relevant parameters through the export method that includes a custom sheet name. The relevant parameters include a data list, a table header, and an export path.

[0030] Among them, set the table width according to the relevant parameters, create a table header, loop through the table header to create cell objects, input the values of the table header according to the cells, loop through the data list, and input the data.

[0031] Pass in the export path, create an out object, and write the table to the export path.

[0032] The present invention also provides an Excel table import and export device based on apache.poi, including a java tool module. The java tool module includes an import module and an export module.

[0033] The java tool module is based on apache.poi and provides Excel table import and export methods using java classes. Among them, the Excel table import method includes an import method without sheet selection and an import method with sheet selection. The Excel table export method includes an export method without a custom sheet name and an export method with a custom sheet name.

[0034] When the import module imports an Excel table, judge whether the sheet name is empty. If so, call the import method without sheet selection, pass relevant parameters, and pass an empty string to represent the default table sheet name.

[0035] Otherwise, call the import method including sheet page selection, pass relevant parameters, and pass the custom imported sheet page name for sheet page selection.

[0036] When the export module exports an Excel table, it determines whether the sheet page name is empty. If so, it calls the export method that does not include the custom sheet page name, passes relevant parameters, and passes an empty string to represent the default table sheet page name.

[0037] Otherwise, call the export method that includes the custom sheet page name, pass relevant parameters, and pass the custom sheet page name.

[0038] Furthermore, when the import module in the Excel table import and export device based on apache.poi imports an Excel table, it includes:

[0039] Create a wb object through poi and write the file stream into the wb object.

[0040] Determine whether the sheet page name is empty. If so, call the import method that does not include sheet page selection and the default table sheet page name.

[0041] Declare the relevant parameters returned by the import method that does not include sheet page selection. The relevant parameters include a map object, a data list, and header data.

[0042] Obtain the header data and the values of each row and column according to the number of data rows, and after processing, put them into the map object and return.

[0043] Furthermore, when the import module in the Excel table import and export device based on apache.poi imports an Excel table, it includes:

[0044] Create a wb object through poi and write the file stream into the wb object.

[0045] Determine whether the sheet page name is empty. If not, call the import method that includes sheet page selection and the custom imported sheet page name.

[0046] Declare the relevant parameters returned by the import method that includes sheet page selection. The relevant parameters include a map object, a data list, and header data.

[0047] Obtain the header data and the values of each row and column according to the number of data rows, and after processing, put them into the map object and return.

[0048] Further, when the export module in the Excel table import and export device based on apache.poi exports an Excel table, it includes:

[0049] Create empty wb and sheet table-related objects through poi,

[0050] Judge whether the sheet name is empty. If it is, call the export method that does not include the custom sheet name, and set the default sheet name.

[0051] Pass relevant parameters through the export method that does not include the custom sheet name. The relevant parameters include the data list, the table header, and the export path.

[0052] Among them, set the table width according to the relevant parameters, create the table header, loop through the table header to create cell objects, input the values of the table header according to the cells, loop through the data list, and input the data.

[0053] Pass in the export path, create an out object, and write the table to the export path.

[0054] Further, when the export module in the Excel table import and export device based on apache.poi exports an Excel table, it includes:

[0055] Create empty wb and sheet table-related objects through poi,

[0056] Judge whether the sheet name is empty. If it is not, call the export method that includes the custom sheet name, and select the custom sheet name.

[0057] Pass relevant parameters through the export method that includes the custom sheet name. The relevant parameters include the data list, the table header, and the export path.

[0058] Among them, set the table width according to the relevant parameters, create the table header, loop through the table header to create cell objects, input the values of the table header according to the cells, loop through the data list, and input the data.

[0059] Pass in the export path, create an out object, and write the table to the export path.

[0060] The beneficial effects of the present invention are:

[0061] The present invention provides an Excel table import and export method based on apache.poi, which simplifies the operation of importing and exporting Excel tables. By passing in fewer parameters, the import or export of data can be achieved. Description of the Drawings

[0062] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0063] Figure 1 It is a schematic diagram of the process for importing a table in the method of the present invention.

[0064] Figure 2 It is a schematic diagram of the process for exporting a table in the method of the present invention.

[0065] Figure 3 It is a schematic diagram of the local display interface for importing a table.

[0066] Figure 4 It is a schematic diagram of the local display interface for exporting a table. Detailed implementation manners

[0067] The following further illustrates the present invention in conjunction with the drawings and specific embodiments, so that those skilled in the art can better understand the present invention and be able to implement it, but the embodiments cited do not limit the present invention.

[0068] The present invention provides an Excel table import and export method based on apache.poi. Based on apache.poi, Java classes are used to provide Excel table import and export methods. The Excel table import method includes an import method without sheet selection and an import method with sheet selection. The Excel table export method includes an export method without a custom sheet name and an export method with a custom sheet name.

[0069] When importing an Excel table, it is judged whether the sheet name is empty. If so, the import method without sheet selection is called, relevant parameters are passed, and an empty string is passed to represent the default table sheet name.

[0070] Otherwise, the import method with sheet selection is called, relevant parameters are passed, and the custom imported sheet name is passed for sheet selection.

[0071] When exporting an Excel table, it is judged whether the sheet name is empty. If so, the export method without a custom sheet name is called, relevant parameters are passed, and an empty string is passed to represent the default table sheet name.

[0072] Otherwise, call the export method that includes the custom sheet name, pass the relevant parameters, and pass the custom sheet name.

[0073] The method of the present invention is a tool based on poi, which realizes free import and export of data and simplifies the import and export process of tables.

[0074] In specific applications, in some embodiments of the method of the present invention, the java class ExcelUtil is improved for the import and export of two-dimensional tables in excel.

[0075] The specific method may include:

[0076] TImport(MultipartFile file);

[0077] TImport(MultipartFile file, String sheetName);

[0078] TExport(List<Map<String, String>> maps, List <string>heads, String path);

[0079] TExport(List<Map<String, String>> maps, List <string>heads, String path, String sheetName);

[0080] Among them, the import methods include:

[0081] TImport(MultipartFile file)

[0082] Parameter: MultipartFile file / / The file to be imported.

[0083] This method is the import method for Excel and does not include the selection of sheet pages.

[0084] The import is achieved by calling TImport. When calling, in addition to passing relevant parameters, the second parameter passes an empty string to provide the default table sheet page.

[0085] TImport(MultipartFile file, String sheetName)

[0086] Parameter: MultipartFile file / / The file to be imported.

[0087] String sheetName / / The custom sheet page name for import.

[0088] This method is the import method for Excel and includes the selection of sheet pages.

[0089] When importing an Excel table, refer to the following steps:

[0090] Create a wb object through poi's Workbook, WorkbookFactory.create(), and file.getInputStream() methods, and write the file stream into the wb object;

[0091] By judging whether sheetName is empty, further set whether it is the default sheet page name or use the custom sheet page name;

[0092] By declaring the relevant parameters to be returned:

[0093] map: The returned object (HashMap<>(String, Object))

[0094] datas: The list of imported data (List<Map<String, Object>>)

[0095] heads: The header data of the import (List <object>);

[0096] Obtain the number of data rows through the sheet.getPhysicalNumberOfRows() method. If the number of data rows is greater than 0, then through the private method getHeads() of the class, pass in the obtained sheet, and preferentially obtain the header data:

[0097] Declare a poi Row object to obtain the first row of the sheet as the Row of the header,

[0098] Obtain the total number of data columns in this row through the row.getPhysicalNumberOfCells() method,

[0099] Loop through the number of columns. By declaring a poi Cell object and using the row.getCell() method and passing in the column number, obtain each cell,

[0100] Judge whether the cell cell is empty, and pass in the cell object through the private getCellValue() method of the class to obtain the value of the cell. Obtain the cell type through cell.getCellTypeEnum(), and obtain the value according to the corresponding method of obtaining the value;

[0101] Start looping from the second row to obtain each row. Through the read heads and the values obtained for each row and each column, form a HashMap object and put it into the datas list;

[0102] Put all the processed data into a map object for return, and at the same time generate a unique id.

[0103] When referring to importing data, take Figure 3 an excel two-dimensional table as an example to obtain the code for creating an Excel file:

[0104]

[0105]

[0106] Pass the obtained file into the TImport method to implement data import:

[0107]

[0108] The import result is:

[0109]

[0110] The export methods include:

[0111] TExport(List<Map<String, String>> maps, List <string>heads, String path)

[0112] Parameters:

[0113] List<Map<String, String>> maps / / The list of data to be exported,

[0114] List <string>heads / / Headers of the table to be exported,

[0115] String path / / Export path.

[0116] This method is for Excel export and does not include customizing the sheet name.

[0117] Among them, the export is achieved by calling TExport. When calling, in addition to passing the maps, heads, and path parameters, the fourth parameter is passed an empty string to provide the naming of the default sheet.

[0118] TExport(List<Map<String, String>> maps, List <string>heads, String path, String sheetName)

[0119] Parameters:

[0120] List<Map<String, String>> maps / / The data list to be exported,

[0121] List <string>heads / / Headers of the table to be exported,

[0122] String path / / Export path,

[0123] String sheetName / / Customized sheet name.

[0124] When importing an Excel table, refer to the following steps:

[0125] Create empty wb and sheet table-related objects through poi's Workbook and Sheet;

[0126] Judge whether sheetName is empty. If it is empty, set the default sheet name, otherwise use the customized sheet name;

[0127] Pass relevant parameters through the export method that does not include the customized sheet name. The relevant parameters include the data list, headers, and export path. Among them, set the table width through the sheet.setColumnWidth() method,

[0128] Create the head column of the headers through sheet.createRow(), then loop through the parameter heads, create cell objects through head..createCell(), and put the values of the headers into the cells through the setCellValue() method of the cells,

[0129] Loop through the parameter maps, starting from the second column of the table, and put the data into the table in the same way as the method of setting the headers,

[0130] Call OutputStream in the java.io package, pass in the path, and create the out object,

[0131] Call the write method of Workbook to write the table to the export path.

[0132] Refer to the code for setting the list of headers to be exported and the data list when exporting data:

[0133]

[0134] Call TExport(), passing in the list of headers, the data list, and the export path:

[0135] ExcelUtil.TExport(maps, heads, path: "D: / / test2.xlsx");

[0136] For the export effect, refer to Figure 4 .

[0137] By using the method of the present invention through the relevant methods of TImport in ExcelUtil, it is only necessary to input the Excel file obtained from the front end into it to realize the import of two-dimensional table data. No other processing is required, which simplifies the import process.

[0138] By using the relevant methods of TExport in ExcelUtil, input the processed object list, the string list composed of the headers of the two-dimensional table, and the necessary export path into it to realize the export of the two-dimensional table. The export object is set to the type of Map<String, String>, which is also selected for convenient and flexible data processing, simplifying the export process.

[0139] The present invention also provides an Excel table import and export device based on apache.poi, including a Java tool module, and the Java tool module includes an import module and an export module.

[0140] The Java tool module is based on apache.poi and uses Java classes to provide Excel table import and export methods. The Excel table import methods include an import method without sheet page selection and an import method with sheet page selection. The Excel table export methods include an export method without custom sheet page name and an export method with custom sheet page name.

[0141] When the import module imports an Excel table, it judges whether the sheet page name is empty. If so, it calls the import method without sheet page selection, passes relevant parameters, and passes an empty string to represent the default table sheet page name.

[0142] Otherwise, it calls the import method with sheet page selection, passes relevant parameters, and passes the custom imported sheet page name for sheet page selection.

[0143] When the export module exports an Excel table, it judges whether the sheet page name is empty. If so, it calls the export method without custom sheet page name, passes relevant parameters, and passes an empty string to represent the default table sheet page name.

[0144] Otherwise, it calls the export method with custom sheet page name, passes relevant parameters, and passes the custom sheet page name.

[0145] For the information interaction, execution process, etc. among the modules in the above device, since they are based on the same concept as the method embodiment of the present invention, the specific content can be referred to the description in the method embodiment of the present invention and will not be elaborated here.

[0146] Similarly, the device of the present invention simplifies the operation of importing and exporting Excel tables. By passing in fewer parameters, the import or export of data can be achieved.

[0147] It should be noted that not all steps and modules in the above processes and device structures are necessary. Some steps or modules can be ignored according to actual needs. The execution order of each step is not fixed and can be adjusted according to needs. The system structures described in the above embodiments can be physical structures or logical structures. That is, some modules may be implemented by the same physical entity, or some modules may be implemented by multiple physical entities separately, or some components in multiple independent devices may be jointly implemented.

[0148] The above-described embodiments are only preferred embodiments given to fully illustrate the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or transformations made by those skilled in the art on the basis of the present invention are within the protection scope of the present invention. The protection scope of the present invention is subject to the claims.< / string> < / string> < / string> < / string> < / object> < / string> < / string>

Claims

1. An Excel table import and export method based on apache.poi, characterized in that Based on apache.poi, use Java classes to provide methods for importing and exporting Excel tables. The Excel table import methods include an import method without sheet selection and an import method with sheet selection. The Excel table export methods include an export method without a custom sheet name and an export method with a custom sheet name. When importing an Excel table, determine whether the sheet name is empty. If it is, call the import method without sheet selection, pass relevant parameters, and pass an empty string to represent the default table sheet name. Otherwise, call the import method with sheet selection, pass relevant parameters, and pass the custom imported sheet name for sheet selection. When exporting an Excel table, determine whether the sheet name is empty. If it is, call the export method without a custom sheet name, pass relevant parameters, and pass an empty string to represent the default table sheet name. Otherwise, call the export method with a custom sheet name, pass relevant parameters, and pass the custom sheet name.

2. The method for importing and exporting Excel tables based on apache.poi according to claim 1 is characterized in that When importing the Excel table, it includes: Create a wb object through poi and write the file stream into the wb object. Determine whether the sheet name is empty. If it is, call the import method without sheet selection, the default table sheet name. Declare the relevant parameters returned by the import method without sheet selection. The relevant parameters include a map object, a data list, and header data. Obtain the header data and the values of each row and column according to the number of data rows, and place them in the map object after processing and return.

3. A method for importing and exporting Excel tables based on apache.poi according to claim 1, characterized in that When importing the Excel table, it includes: Create a wb object through poi and write the file stream into the wb object. Determine whether the sheet name is empty. If it is not, call the import method with sheet selection, the custom imported sheet name. Declare the relevant parameters returned by the import method with sheet selection. The relevant parameters include a map object, a data list, and header data. Obtain the header data and the values of each row and column according to the number of data rows, and place them in the map object after processing and return.

4. A method for importing and exporting Excel tables based on apache.poi according to claim 1, characterized in that When exporting the Excel table, it includes: Create empty wb and sheet table-related objects through poi. Determine whether the sheet name is empty. If it is, call the export method without a custom sheet name and set the default sheet name. Pass the relevant parameters through the export method without a custom sheet name. The relevant parameters include a data list, a header, and an export path. Among them, set the table width according to the relevant parameters, create a header, loop through the header to create cell objects, input the header values according to the cells, loop through the data list, and input the data. Pass in the export path, create an out object, and write the table to the export path.

5. The method for importing and exporting Excel tables based on apache.poi according to claim 1, characterized in that When exporting to an Excel table, it includes: Create empty wb and sheet table-related objects through poi, Judge whether the sheet page name is empty. If not, call the export method including the custom sheet page name and select the custom sheet page name, Pass relevant parameters through the export method including the custom sheet page name. The relevant parameters include the data list, table header, and export path, Where set the table width according to the relevant parameters, create the table header, loop through the table header to create cell objects, input the values of the table header according to the cell, loop through the data list, and input the data, Pass in the export path, create an out object, and write the table to the export path.

6. An Excel table import and export device based on apache.poi, characterized in that It includes a Java tool module. The Java tool module includes an import module and an export module, The Java tool module is based on apache.poi and provides Excel table import and export methods using Java classes. Among them, the Excel table import methods include the import method without sheet page selection and the import method with sheet page selection. The Excel table export methods include the export method without custom sheet page name and the export method with custom sheet page name, When the import module imports an Excel table, judge whether the sheet page name is empty. If so, call the import method without sheet page selection, pass relevant parameters, and pass an empty string to represent the default table sheet page name, Otherwise, call the import method with sheet page selection, pass relevant parameters, and pass the custom imported sheet page name for sheet page selection, When the export module exports an Excel table, judge whether the sheet page name is empty. If so, call the export method without custom sheet page name, pass relevant parameters, and pass an empty string to represent the default table sheet page name, Otherwise, call the export method with custom sheet page name, pass relevant parameters, and pass the custom sheet page name.

7. The Excel table import and export device based on apache.poi according to claim 6, characterized in that When the import module imports an Excel table, it includes: Create a wb object through poi and write the file stream into the wb object, Judge whether the sheet page name is empty. If so, call the import method without sheet page selection, the default table sheet page name, Declare the returned relevant parameters through the import method without sheet page selection. The relevant parameters include the map object, data list, and table header data, Obtain the table header data and the values of each row and column, and put them into the map object for return after processing.

8. The Excel table import and export device based on apache.poi according to claim 6, characterized in that When the import module imports an Excel table, it includes: Create a wb object through poi and write the file stream into the wb object, Judge whether the sheet page name is empty. If not, call the import method with sheet page selection, the custom imported sheet page name, Declare the relevant parameters returned by the import method including sheet page selection. The relevant parameters include a map object, a data list, and header data. Obtain the header data and the values of each row and column according to the number of data rows, and after processing, put them into the map object and return.

9. The Excel table import and export device based on apache.poi according to claim 6, characterized in that When the export module exports an Excel table, it includes: Create empty wb and sheet table-related objects through poi. Judge whether the sheet page name is empty. If it is, call the export method that does not include a custom sheet page name and set the default sheet page name. Pass relevant parameters through the export method that does not include a custom sheet page name. The relevant parameters include a data list, a header, and an export path. Among them, set the table width according to the relevant parameters, create a header, loop through the header to create cell objects, input the header values according to the cells, loop through the data list, and input the data. Pass in the export path, create an out object, and write the table to the export path.

10. The Excel table import and export device based on apache.poi according to claim 6, characterized in that When the export module exports an Excel table, it includes: Create empty wb and sheet table-related objects through poi. Judge whether the sheet page name is empty. If it is not, call the export method that includes a custom sheet page name and select a custom sheet page name. Pass relevant parameters through the export method that includes a custom sheet page name. The relevant parameters include a data list, a header, and an export path. Among them, set the table width according to the relevant parameters, create a header, loop through the header to create cell objects, input the header values according to the cells, loop through the data list, and input the data. Pass in the export path, create an out object, and write the table to the export path.

Images