WO2014175595A1

WO2014175595A1 - Method for uniting and dividing excel files having same form by using excel file uniting and dividing system

Info

Publication number: WO2014175595A1
Application number: PCT/KR2014/003306
Authority: WO
Inventors: 유춘호
Original assignee: Yu Chun Ho
Priority date: 2013-04-25
Filing date: 2014-04-16
Publication date: 2014-10-30
Also published as: KR101321599B1

Abstract

The present invention relates to a method for readily uniting and dividing Excel files having the same form in a PC or mobile device with the Excel program installed by means of an Excel file uniting and dividing system.

Description

Collecting and Separation Method Using Collecting Separation System of Excel Files with Same Format

The present invention relates to a method of collecting and separating using an Excel file collection and separation system having the same format, and more particularly, to easily collect and separate an Excel file having the same format, the PC or mobile installed Excel The present invention relates to a collection and separation method using a collection separation system of Excel files having the same format that can be collected and separated in a device.

In general, statistical data is the most important data base for decision making. Rapid and accurate data on statistics of administration is a very important factor in establishing good policies.

Such data statistics should be collected (aggregated or collected) from the various reporting agencies.

Such data should be collected quickly and easily and its accuracy and reliability should be ensured.

It is common to use a FORM to facilitate and speed up the collection of such data, and to use standard aggregate forms to increase accuracy and reliability.

This standard aggregation form is completed only when there are many members of the organization because it is prepared, supplemented and modified through systematic procedures.

In addition, when data is entered into the standard aggregation form, the revision is repeated by the author (input), and after the payment is made according to the procedure set by the organization, it is reported to the aggregater (requester). Many reported data are collected, aggregated, and analyzed by the aggregator.

The data collection process according to the prior art provides a written standard aggregate form as a document such as a fax machine, and the receiving side manually processes the process of recording and reporting data by rewriting the standard aggregate form in the same manner. Therefore, there is a problem that the operation of the task is very complicated, difficult, time-consuming and inaccurate in the process of collecting.

In order to solve some of the problems of the prior art, a standard aggregate format is created on the Internet as a document (HYPERTEXT MARKUP LANGUAGE) as shown in FIG. There are ways to enter and report.

The improved prior art uses the Internet (INTERNET), which has the advantage of being relatively quick to aggregate, but it is inconvenient when creating complex forms because it uses simple forms by HTML documents. Problems such as a drop in accuracy due to an error still remain.

In addition, the HTML standard aggregate format occupies one record of the database per cell data, thus requiring a large number of tables and a large number of records in the database, thereby increasing the data capacity. As the data capacity is large, the transfer time and the memory capacity to be stored are large. There is still an uncomfortable problem that cannot be copied and applied from existing data already reported.

On the other hand, if the original file itself is saved to reduce the data capacity, the data collection rate has to be read one by one and processed at a time. There was a virtually impossible issue

In addition, since data cannot be directly input and modified on the web of the Internet, real-time aggregation is not possible.

Therefore, the standard aggregation form based on XML is created and formula is applied on the web of the Internet, so the receiving party or reporter does not rewrite the form, and when collecting the data, the accuracy is reconsidered and confirmed in real time. There is a need to develop skills.

Related prior art is Korean Patent No. 10-1063826.

The present invention can easily collect and separate the Excel file having the same format, and the method of collecting and separating using the collection and separation system of the Excel file having the same format that can be collected and separated from the PC or mobile device that is installed Excel It is to provide.

The technical problems to be achieved by the present invention are not limited to the technical problems mentioned above.

In order to achieve the above object, the method of collecting and separating using an Excel file collecting and separating system having the same format according to the present invention loads an Excel file in which data is stored in a setting analysis management unit, and externally sets an environment related to collecting or separating. Setting and inputting the input value, and verifying and analyzing the input value in the setting analysis management unit; and selecting or collecting the data displayed on the screen whether to collect or separate the data of the file loaded in the setting analysis management unit. Selecting an item as an input device, determining whether the selected item inputted by the setting analysis management unit is collected or separated; and collecting the Excel file through the collecting unit when the selected item determined by the setting analysis management unit is collected Examine the sheets, sort them, combine them and save them as a combined result file in the result storage And storing the contents of the execution process and the result in the log storage unit as a log file, and if the selection item determined by the setting analysis management unit is separation, separating the data of the Excel file through the separation unit, and separating the separated data. The method may include generating the data of the Excel file as a test file, storing the result as a separate result file in the result storage unit, and storing the execution process and the contents of the result as a log file in the log storage unit.

Specifically, in the verifying and analyzing step, the configuration analysis management unit loads an Excel file or a single Excel file having a plurality of identical formats, and sets the input device to the configuration analysis management unit to collect the environment setting values according to the collection and separation. Through the input, the environment setting value is verified and analyzed to determine whether the input of the environment setting value input through the input device is normal.

In the storing of the Excel file collected in the result storage unit, the data to be collected in the collection target sheet of the plurality of Excel files having the same format is set to be stored in the collection unit, and the format is matched. After the inspection is completed, the collected sheet data to be collected may be sorted and collected and stored as an Excel file collected in the result storage unit.

When collecting the data of the Excel file in the collecting unit, the job name, the folder to be collected, the collecting method, the title cell range, the data range and the range to be summed, etc. When the range is not constant, when the sheet data is gathered sideways, when you generate only the summed result without adding the sheet, when you import only the cells you need, when you import only the cells with specific cell values, when you import only the rows that meet the criteria And verifying whether the environment setting value for the case of importing only necessary cells from multiple sheets is correct, checking whether all the Excel files are received, checking whether the sheet names to be collected in each Excel file are set correctly, The Excel file that has an error at the time of inspection is automatically excluded, and the order in which the Excel file is collected through the criteria set in the collecting unit. Can be collected automatically converts the file name.

The storing of the result as an Excel file separated in the result storage unit may be performed by separating the data of the Excel file through a separator and storing the separated file in the separator when the selected item determined by the setting analysis manager is separated. The data of the separated Excel file is generated as a test file, and the separation unit tests whether the data is separated to meet the separation conditions, and the data of the separated Excel file that is the result of the test by the separation unit is separated into the result storage unit. Can be saved as the Excel file.

When separating the data of the Excel file in the separating unit designates an Excel file to perform the separation operation, and when separating into groups based on the value of a specific column that is a separation condition, when separating into groups based on a certain range In the case of separating into a different format file on the basis of a specific line and when separating multiple sheets into separate files, it is set as an environment setting value and separated into a separate Excel file through the setting value, and the data of the Excel file is separated. After separation, the original file may be stored as a separate copy file, and the separated Excel file may be automatically generated after the separation so that the separated data can be easily displayed and verified.

As described above, the present invention can easily collect and separate Excel files having the same format, thereby improving work efficiency.

In addition, since it is possible to collect and separate from a PC or a mobile device that has Excel installed, there is no cost of using expensive servers and web programs.

1 is a flow chart of a method of collecting and separating using a collection and separation system of the Excel file having the same format according to an embodiment of the present invention.

2 is a view showing a collection separation structure of the collection and separation method using the collection and separation system of the Excel file having the same format according to the present invention.

Hereinafter, with reference to the accompanying drawings will be described in detail a preferred embodiment of the present invention. Like elements in the figures are denoted by the same reference numerals wherever possible. In addition, detailed descriptions of well-known functions and configurations that may unnecessarily obscure the subject matter of the present invention will be omitted.

1 is a view showing a method of collecting and separating using an Excel file collection and separation system according to an embodiment of the present invention, wherein the configuration analysis management unit 10 verifies an environment setting regarding collection and separation of Excel files. And analyzing (S10), selecting and inputting a collection or separation item displayed on the screen as an input device, and determining the collection or separation in the setting analysis management unit (S20), and the collection is selected. In the case of collecting sheet data of the Excel file through the collecting unit 20, verifying and sorting the sheet data, and storing the sorted sheet data in the result storage unit 40 (S30), and if the separation is selected Separating the corresponding sheet data of the Excel file through 30, and testing the sheet data of the separated Excel file and storing it as a separate Excel file (S40).

As shown in FIG. 1 and FIG. 2, the configuration analysis management unit 10 loads an Excel file in which data is stored in the configuration analysis management unit 10. And setting an environment setting regarding collection or separation through an external input device, and verifying and analyzing the input input value in the setting analysis management unit 10.

The setting analysis management unit 10 loads an Excel file in which sheet data is stored, stores an environment setting value according to collection or separation of the loaded Excel file, and checks whether the environment setting value is correct for a user input through an input device. Verify and analyze the environment settings that have been verified.

That is, in the verifying and analyzing step, the configuration analysis management unit 10 loads an Excel file or one Excel file having a plurality of identical formats, and sets the environment setting values according to the collection and separation to the configuration analysis management unit 10. It inputs through the input device, and verifies and analyzes the environment setting value to check whether the input of the environment setting value input through the input device is normal.

When the configuration setting value for collecting or separating the Excel file is set in the setting analysis management unit 10 as described above, a selection item for selecting whether to collect or separate the Excel file loaded in the setting analysis management unit 10 is displayed on the screen. If it is selected, the setting analysis management unit 10 determines and executes the corresponding job.

When the collection is selected from the selection items displayed on the screen, the setting analysis management unit 10 determines that the data of the Excel file loaded is collected and transmits the sheet data of the Excel file to the collecting unit 20.

The collecting unit 20 examines sheet data, which is a collection target of the transmitted Excel file, checks whether the sheet data to be collected is in the same format, and sorts and collects the same in the collecting unit 20 if the same format is used. If not, it will be treated as an error and excluded from the collected sheet data.

In the case of sheet data having the same format in the collecting unit 20, the sheet data to be collected are sorted to arrange the sheet data at the time of collecting.

The sorted sheet data is collected in the collecting unit 20 and stored in the result storage unit 40 as a file such as an Excel file or PDF in the collected state.

When the Excel file is collected by the collecting unit 20, a job name, a folder to be collected, a collecting method, a title cell range, a data range, and a range to be combined are specified. Is inconsistent, when the sheet data is gathered sideways, produces only aggregated results without adding sheets, imports only the cells you need, imports only cells with specific cell values, imports only rows that meet the criteria, and Check if the configuration value for importing only the cells required from multiple sheets is correct in the collecting unit 20, check whether all Excel files have been received, and whether the sheet names to be collected in each Excel file are set correctly. Check and exclude the Excel file that has an error at the time of inspection, and search for the file name in the order in which the Excel file is collected through the criteria set in the collecting unit 20. It will be automatically converted, collected and saved.

When the separation is selected from the selection items displayed on the screen, the desired sheet data can be selected from the Excel file loaded in the setting analysis management unit 10, and the selected sheet data is transmitted to the separation unit 30 to separate the separation unit 30. ), The sheet data of the Excel file is separated from the original Excel file.

The sheet data of the separated Excel file is generated as a test file to be compared with the existing Excel file in the separating unit 30, and whether the test file is separated to meet the separation conditions, whether the sheet data is to be separated or not. The part 30 is inspected.

Then, the separated sheet data verified by the separator 30 is stored in the result storage unit 40 as a separate Excel file or a PDF file.

When the separation unit 30 separates the data of the Excel file, it designates an Excel file to perform a separation operation, and when separating into groups based on a value of a specific column as a separation condition, separating into groups based on a predetermined range. If you want to separate into a different format file based on a specific row and if you want to separate multiple sheets into separate files, set it as an environment setting value and separate it into a separate Excel file through the setting value. After the data is separated, the original file is stored as a separate copy file in the result storage unit 40, and the separated data is displayed on the copy file so that it is easy to check and automatically generate an Excel file for verification.

The setting analysis management unit 10, the collecting unit 20, the separating unit 30, and the result storage unit 40 may be generated when the log storage unit 50 is connected and collected or separated, or when the environment setting or the Excel file is stored. Save errors and results in a log file so that users can check them.

The present invention of the method as described above is configured to enable the simultaneous collection and separation of the sheet data of the Excel file in a separate device, such as a PC or mobile device installed with the Microsoft Excel program.

When setting the environment for collection or separation in the setting analysis management unit 10, which range of which sheet is collected in what way, and which one Excel file is separated on which sheet based on which specific column or another format of Excel file After inputting the cell value into which to separate, the setting analysis management unit 10 analyzes whether the settings are correctly inputted, and stores the collected separation environment as a file for later use. All Excel files are displayed on the screen so that they can be managed at once.

A log storage unit 50 is connected to the setting analysis management unit 10. The log storage unit 50 stores an error or a normal message appearing in various types of Excel collection and separation setting file generation, modification and collection and separation as a log. It performs the function.

In the collecting unit 20 and the separating unit 30, the sheet data of the file may be collected and separated again, and then separated and collected again. If the collection is selected in the selection item, the range to be collected is constant or not constant. And if you add each sheet to the right, you can edit and detach the Excel file again after collecting.

The Excel file modified by the user after being collected or separated is automatically converted to collect or separate the settings in the collecting unit 20 or the separating unit 30 when the sheet data is distributed to the lower user. The function is automatically converted into an Excel file and stored in the result storage unit 40.

The collecting unit 20 and the separating unit 30 perform operations on the selected item by dividing the Excel file loaded by the setting analysis management unit into collecting and separating, and collecting the unit 20 when the selected item is collecting. ), You can create, modify, and delete the collection management details that manage the collected Excel file.

The collecting unit 20 checks whether all collected files are actually stored in a folder to be collected, and checks whether an Excel file of a reporting institution is missing, and checks the collected Excel file name after checking whether the sheet name to be collected is correctly written. Sort by the collection criteria set in the collecting unit 20 and automatically converts the file name is stored in the result storage unit 40, and all the logs generated in this process is automatically stored by the log storage unit 50.

The collection method 20, the sheet to be collected, and the range to be collected are set by the setting analysis management unit 10, and the collection unit 20 performs the actual collection on the generated file. The collecting unit 20 stores the collecting result in an Excel file or a PDF file, and the location and file name of the collected Excel file are automatically added to the setting file through the collecting unit 20. Of course, all the logs generated at this time are automatically stored by the log storage unit 50.

In the case in which the sheet data separation of the Excel file is selected in the configuration analysis management unit 10, the separation method 30 reads the separation method, the Excel file and the sheet to be separated, the group column to be separated, and the cells to be separated. ), The actual detach run takes place.

At this time, the result storage unit 40 stores the separation result in an Excel file or a PDF file, and the location of the folder in which the separated result is stored is automatically stored in addition to the setting file through the separation unit 30.

In addition, the separation unit 30 creates an Excel file of the same copy as the original Excel file before being separated so that it can be checked which data is separated so that all logs generated at the time of storing the result are stored by the log storage unit 50. It is saved automatically.

The present invention configured as described above can easily collect and separate Excel files having the same format, thereby improving work efficiency.

The collection and separation method using the collection and separation system of the Excel file having the same format as described above is not limited to the configuration and operation of the embodiments described above. The above embodiments may be configured such that various modifications may be made by selectively combining all or part of the embodiments.

[Description of the code]

10: setting analysis management unit 20: collecting unit

30: separation unit 40: result storage unit

50: log storage unit

Claims

Loading an Excel file in which data is stored in a configuration analysis management unit, setting an environment setting regarding collection or separation through an external input device, and verifying and analyzing the input input value in the configuration analysis management unit;

Selecting a collection or separation selection item displayed on the screen to collect or separate the data of the file loaded by the configuration analysis management unit as an input device, and determining whether the selection item input by the configuration analysis management unit is collection or separation; step;

If the selection items determined by the setting analysis management unit are collected, the collection target sheet of the Excel file is inspected through the collecting unit, sorted, collected, stored as a result file collected in the result storage unit, and performed in the log storage unit. Storing the contents of the process and the results in a log file; And

If the selection item determined by the setting analysis management unit is a separation, the data of the Excel file is separated through a separation unit, and the data of the separated Excel file is generated as a test file and the result is separated into a result file separated into a result storage unit. And collecting and separating the Excel file having the same format as the log storage unit.
The method according to claim 1,

In the verifying and analyzing step, an Excel file or a single Excel file having a plurality of identical formats is loaded in the configuration analysis management unit, and the environment setting values according to the collection and separation are input to the configuration analysis management unit through the input device. And collecting and separating using an Excel file collecting and separating system having the same format for verifying whether or not the input of the environmental setting value input through the input device is normal by verifying and analyzing the environmental setting value.
The method according to claim 1,

In the storing of the Excel file collected in the result storage unit, the data to be collected in the collection target sheet of the plurality of Excel files having the same format is set to be stored in the collection unit, and the format is matched. And collecting and separating the collected sheet data, which has been inspected, by sorting and collecting the collected sheet data, and storing the collected sheet data as an Excel file collected in a result storage unit.
The method according to claim 3,

When collecting the data of the Excel file in the collecting unit, the job name, the folder to be collected, the collecting method, the title cell range, the data range and the range to be summed, etc. When the range is not constant, when the sheet data is gathered sideways, when you generate only the summed result without adding the sheet, when you import only the cells you need, when you import only the cells with specific cell values, when you import only the rows that meet the criteria And verifying whether the environment setting value for the case of importing only necessary cells from multiple sheets is correct, checking whether all the Excel files are received, checking whether the sheet names to be collected in each Excel file are set correctly, The Excel file that has an error at the time of inspection is automatically excluded, and the order in which the Excel file is collected through the criteria set in the collecting unit. Collection and separation method using a separate collecting systems excel file with the same format that collects and automatically converts the file name.
The method according to claim 1,

The storing of the result as an Excel file separated in the result storage unit may be performed by separating the data of the Excel file through a separator and storing the separated file in the separator when the selected item determined by the setting analysis manager is separated. The data of the separated Excel file is generated as a test file, and the separation unit tests whether the data is separated to meet the separation conditions, and the data of the separated Excel file that is the result of the test by the separation unit is separated into the result storage unit. Collecting and separating method using the collecting and separating system of the Excel file having the same format to save the Excel file.
The method according to claim 5,

When separating the data of the Excel file in the separating unit designates an Excel file to perform the separation operation, and when separating into groups based on the value of a specific column that is a separation condition, when separating into groups based on a certain range In the case of separating into a different format file on the basis of a specific line and when separating multiple sheets into separate files, it is set as an environment setting value and separated into a separate Excel file through the setting value, and the data of the Excel file is separated. After the separation, the original file is stored as a separate copy file, and the separated data is collected and collected using an Excel file collection system of the same format that automatically generates an Excel file for verification after separation. And separation method.