JP2016224550A - Conversion method - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a conversion method for converting a file in a spreadsheet format to such a format that it can be used in Web.SOLUTION: Information of fixed values which are invariable and information of variable values which can vary in accordance with a calculation result are acquired from a file in a spreadsheet format, and a file of the fixed values and a file of the variable values are generated from the information of the fixed values and information of the variable values respectively. A calculation formula is calculated from information of the file of the variable values, and values of the calculation result of the calculation formula are inserted to the file of the fixed values by referring to reference destinations, thereby generating a file in a position coordinate format.SELECTED DRAWING: Figure 3

Description

  The present invention relates to a conversion method for converting a spreadsheet file into a format usable on the Web.

  A form used as an application form for financial or insurance related documents may be created as a spreadsheet file using spreadsheet software, for example, in an insurance company. This form file is distributed and used by each department and customer. There are many types of such forms. For this reason, when managing the forms collectively or maintaining the items described in the forms, maintenance must be performed for each form, and there are a wide variety of types, which is very laborious. Therefore, it is desirable that the file can be managed in a format that can be managed on the Web (for example, XML (Extensible Markup Language) format) instead of a spreadsheet file. Here, as an example of a technique for converting the file format, Patent Document 1 describes converting a spreadsheet file into an HTML (HyperText Markup Language) file.

JP 2005-339333 A

  When converting a spreadsheet file to another format, convert it into a general-purpose format such as CSV (Comma-Separated Values) format (comma-separated values) or TSV (Tab-Separated Values) format (tab-separated values). Then, it may be converted to the target format. However, the CSV format and the TSV format are obtained by dividing data with commas and tabs. In the CSV format and the TSV format, information such as coordinates and attributes of each cell is lost. In spreadsheet application software, functions and mathematical formulas are often used for total calculations. However, when converted to the CSV format or the TSV format, the mathematical formula and function information are lost. In addition, the spreadsheet application software can create a company logo, a background image, and the like using image data. However, when converted to the CSV format or the TSV format, such image data information cannot be supported.

  Patent Document 1 describes conversion of a spreadsheet file into an HTML file for symbols, radio buttons, and the like. However, Patent Document 1 does not describe conversion of calculation formulas included in the spreadsheet file.

  In view of the above-described problems, an object of the present invention is to provide a conversion method for converting a spreadsheet file into a format that can be used on the Web.

  The conversion method according to an aspect of the present invention acquires information on a fixed value whose value is determined and information on a variable value whose value can change depending on a calculation result based on a calculation formula, from a spreadsheet file. A file including the fixed value is generated from the fixed value information, and an operation based on the calculation formula is performed with reference to the reference destination value of the calculation formula from the variable value file information. A file including an operation result value is generated, and a position coordinate format file or a Web page is generated from the file including the fixed value and the file including the operation result value.

  According to the present invention, even if a calculation formula is set in a cell, a spreadsheet file can be easily converted into a file format that can be used on the Web. As a result, data such as an existing spreadsheet application file can be handled on the Web.

1 is a block diagram of an example of a computer system. It is a functional block diagram showing a schematic structure of a conversion system concerning a 1st embodiment of the present invention. It is explanatory drawing of the conversion process of the file of a spreadsheet type which concerns on the 1st Embodiment of this invention. It is explanatory drawing of the specific example of the conversion process of the file of the spreadsheet format which concerns on the 1st Embodiment of this invention.

  Hereinafter, embodiments of the present invention will be described with reference to the drawings. The conversion system according to the first embodiment of the present invention can be realized by software of a computer system 10 as shown in FIG. The computer system 10 includes, for example, a central processing unit (CPU) 11, a read only memory (ROM) 12, a random access memory (RAM) 13, a storage unit 14, a display unit 15, an input unit 16, and communication. Unit 17. Each signal is exchanged between the blocks via the bus 18.

  The CPU 11 controls each unit based on various control programs stored in the ROM 12. The CPU 11 controls each unit based on various information read from the storage unit 14, input from the input unit 16, and the like.

  The ROM 12 is a read-only memory and stores a boot program and the like. The RAM 13 is a memory used as a main memory (main storage device) of the CPU 11, and is used as a work area for programs executed in the CPU 11. The storage unit 14 includes an HDD (Hard Disk Drive) or the like, and stores an operating system, various application programs, and the like. In addition, various files are stored in the storage unit 14.

  The display unit 15 displays various information based on the control of the CPU 11. As the display unit 15, for example, an LCD (Liquid Crystal Display) or the like is used. The input unit 16 can use a keyboard or a mouse (pointing device) and accepts an operation input from the user. The communication unit 17 is connected to a network and transmits / receives data to / from an external device.

  FIG. 2 is a functional block diagram showing a schematic configuration of the conversion system 20 according to the first embodiment of the present invention. As shown in FIG. 2, the conversion system 20 according to the first embodiment of the present invention includes a file analysis unit 21 and a file synthesis unit 22. This conversion system 20 can be applied to the computer system 10 of FIG.

  The file analysis unit 21 analyzes a spreadsheet file. Here, as the spreadsheet file, for example, an Excel (registered trademark) file can be used. Based on the extraction rule definition, the file analysis unit 21 determines whether each spreadsheet file includes image data, fixed value data, and variable value data based on the character strings described in the file. Determine about the data. The file analysis unit 21 extracts elements of image data, fixed value data, and variable value data existing in the spreadsheet file from the spreadsheet file. Here, the image data is image data used for the title of the form, the logo of the company that issued the form, and the like. Fixed value data is data whose values are determined, such as the coordinates of each cell, the attributes, the position where a figure or character is laid out, and the data (numerical value, character) of each cell. The variable value data is data whose value can change depending on the calculation result, such as a function or a macro calculation formula. The file analysis unit 21 generates a PDF (Portable Document Format) file from the image data, generates an XML (Extensible Markup Language) file from the fixed value data, and generates an XML file from the variable value data.

  The file synthesizing unit 22 synthesizes the XML file of fixed value data and the XML file of variable value data generated by the file analyzing unit 21, and generates a file or Web page in a position coordinate format that can be used on the Web. . That is, the file composition unit 22 performs the calculation of the calculation formula of the variable value XML file. Then, the file composition unit 22 inserts the calculation result into an XML file of fixed value data, and generates an XML file in a position coordinate format. Further, the file composition unit 22 describes a link for inserting the image data of the PDF file as an object in the position coordinate format XML file. The spreadsheet file can be displayed on the Web by the XML file in the position coordinate format and the PDF file of the image data.

  As described above, in the conversion system 20 according to the first embodiment of the present invention, the spreadsheet file is converted into the position coordinate file, and the position coordinate file contains calculation formulas and function calculations. The result is inserted. Also, the image data can be displayed by linking the generated XML file in the position coordinate format and the PDF file of the image data. In the conversion system 20 according to the first embodiment of the present invention, not only fixed value data but also variable value data such as functions and macro calculation formulas, and image data are included in a spreadsheet file on the Web. It can be converted into a file or web page in an available position coordinate format. This Web page can be configured by HTML, a style sheet, a Java (registered trademark) script, or the like.

  Next, a spreadsheet processing file conversion process in the conversion system according to the first embodiment of the present invention will be described in detail.

  FIG. 3 is an explanatory diagram of a spreadsheet conversion process. In FIG. 3, a file 51 is a spreadsheet file to be converted. The file name of the file 51 is “application form.xlsx” and is in Excel (registered trademark) format. The file 51 includes four pages of worksheets 52a to 52d from layout 1P to layout 4P.

  An Excel (registered trademark) format file has an extension of xlsx, and is a format in which various files based on XML are compressed by ZIP. When this Excel (registered trademark) format file is decompressed, various files are acquired in a predetermined folder hierarchy. In FIG. 3, files 53a to 53d with file names “sheet1.xml” to “sheet4.xml” store information on worksheets 52a to 52d in a format based on XML.

  The file analysis unit 21 analyzes the files 53a to 53d in accordance with the extraction rule definition 54, extracts the elements / functions 55, and extracts a file for each element (a file 56 of Template.PDF, a file 57 of Template.xml, a Macro 57). .Xml file 58). That is, the spreadsheet application software is software that performs calculations by inputting characters, numerical values, and mathematical formulas into grid-like cells arranged in a table format. In addition, the spreadsheet application software can create a company logo, a background image, and the like using image data. Therefore, the spreadsheet file contains information on various elements such as the data of each cell, the coordinates of each cell, the attributes, the position where characters and figures are laid out, function and macro information, and image data. ing. Further, as described above, the Excel (registered trademark) format file is a format in which various files based on XML are compressed. A tag is attached to the XML format file, and various elements can be identified by this tag.

Template. In the PDF file 56, PDF data and layout information indicating a layout position are defined.
Template. The xml file 57 includes, for example, layout information and attribute information (“ID”, “typeface”, “character size”, “color”, “coordinate (position) of each object (character string, rectangle, circle / ellipse, ruled line). Etc.) is defined. Each object is assigned a unique number and is uniquely managed.
Macro. The xml file 58 stores the functions (formula information) in the spreadsheet file in a format that can be interpreted in the converted file format. The position of the function to be laid out and the expression information are associated by unique numbers in Template.XML and Macro.XML.

  The extraction rule definition 54 describes the type of target tag and a predetermined conversion rule. The file analysis unit 21 analyzes the files 53a to 53d based on the extraction rule definition 54, performs a conversion process, and generates a file corresponding to the element. Here, the file analysis unit 21 extracts elements as follows to generate a file.

(1) For image data included in a spreadsheet file (for example, a form title or a logo of a company that issued a form), Template. A pdf file 56 is generated and saved in PDF format. Further, identification information is given to each object.
(2) For fixed value data such as the coordinates of each cell, the attribute, the position where a figure or character is laid out, and the data of each cell (fixed character, fixed number, etc.), Template. Generate an xml file 57 and save it in XML format. At this time, identification information is given to the position of the cell corresponding to the variable value data. For each object, the same identification information as that of the object placed in the cell is assigned to the cell in which the object is placed.
(3) For variable value data such as calculation formulas such as functions and macros (including definitions for connecting characters), Macro. Generate an xml file 58 and save it in XML format. At this time, the calculation formula is converted so that the calculation can be executed in the Web format. Further, identification information corresponding to the cell is given to the calculation formula.

  For example, the extraction rule definition 54 is defined for “sheet”, “cell”, “auto shape”, and their “value”, “function”, and “attribute (color, etc.)”. A condition indicating whether to expand to “Template.xml” or “Macro.xml” is stored. In this extraction rule definition 54, not only the data to be expanded but also conversion settings for unnecessary information and the like can be defined. That is, as unnecessary information, for example, it is necessary for data in the file format before conversion, but is unnecessary in the file format after conversion, for example, a memo of the creator used in the file format before conversion. Etc.

For example, the description of the sheet of the spreadsheet file is as follows.
<TARGET SHEET = 'sheet1' USE = 'YES'>
Is defined as an output target.
For example, in the description of the cell, it is as follows.
<CELL ID = 'L43' USE = 'YES'>
<VALUE TO = 'XML'/>
<FUNCTION TO = 'MACRO'/>
<FILL TO = 'XML'/>
<BORDER TO = 'PDF'/>
</ CELL>
<VALUE TO = 'XML'/> follows Template. To xml, <FUNCTION TO = 'MACRO'/> is Macro. <BORDER TO = 'PDF'/> to Template. It is shown to expand to pdf.
For example, in the case of an auto shape,
<DRAWING ID = 'rId1' USE = 'YES'>
<VALUE TO = 'XML'/>
<FILL TO = 'XML'/>
<BORDER TO = 'XML'/>
</ DRAWING>
</ TARGET>
The deployment destination is defined according to the description. And in each expansion | deployment destination, it associates with common ID.

Examples of function substitution include the following descriptions.
The calculation formula “(L39 + L41 + L42) −L43” is used as it is as the calculation formula “(L39 + L41 + L42) −L43”.
In the case of the SUM function “(SUM (L42: L49))”, for example, the description “SUM (L42: L49)” is converted into the description “SUM (L42-L49)” and used. It is done.
In the case of character string concatenation, the description “sheet1! L42 &'like'” is converted to the description “COMBINE (sheet1.L42, 'like')” and used.
In the case of a date format description, the description “yyyy“ year ”m“ month ”d“ day ”” is converted into a description “FORMAT (yyyy year M month d day)”.
In the case of a numeric format, the description “#, ## 0” is converted into a description “FORMAT (#, ## 0)”.
That is, at least a part of the character string described in the file format before conversion and the character string described in the file format after conversion are associated with each other and stored as an extraction rule definition.

  Note that fixed value information displayed on the screen, such as individual cell data (fixed characters, fixed numbers, etc.), is treated as an image, and Template. You may make it include in the file 56 of pdf.

  The file composition unit 22 uses Template. xml file 57 and Macro. From the xml file 58, a position coordinate format file 59 (for example, a PRAS.xml file in FIG. 3) that can be used on the Web is generated. That is, the file composition unit 22 is a Macro. The calculation formula of the xml file 58 is executed, and the calculation result is referred to Template. Insert into the cell of the xml file 57. In addition, the file composition unit 22 refers to the identification information, and the Template. In a corresponding cell of the xml file 57, Template. Insert a link to the file 56 of the pdf.

  Generated PRAS. xml file 59 and Template. With the pdf file 56, a spreadsheet format screen can be displayed on the Web. In addition, this PRAS. xml file 59 and Template. From the pdf file 56, the typesetting engine 60 can generate an automatic typesetting PDF file 61 in accordance with a preset rule.

  FIG. 4 is an explanatory diagram of a specific example of such conversion processing. In FIG. 4, in the file 51 of “application form.xlsx”, the background 80 (the figure showing the characters “Billing details”) is image data. In the cells of the “billing date” portion 81 and the “name” portion 82, fixed values indicating the billing date and the characters of the name are input.

  In each cell (cell addresses I39 to I44) in the “product number” portion 83, fixed value data indicating the product number is input. A fixed value indicating unit price data is input to the cells (cell addresses J39 to J44) of the “unit price” portion 84. In each cell (cell addresses K39 to K44) in the “quantity” portion 85, a fixed value indicating unit price data is input. Each cell (cell addresses L39 to L44) in the “subtotal” portion 86 has a formula (J39 * K39 to J44 × showing a multiplication of the J column and the K column) that is a variable value for obtaining the subtotal. K44)) is input. A cell (cell function KSUM (K39: K44)) that is a variable value for calculating the total quantity is input to the cell (cell address K99) of the portion 87 of “total quantity”. Has been. In the cell (cell address L99) of the “total amount” portion 88, a function (a function indicating the total of the L columns (SUM (L39: L44))) for inputting the total amount is input. Has been.

  The file analysis unit 21 extracts elements, functions, and the like from the XML tag of the worksheet data, classifies them into image data, fixed value data, and variable value data. pdf file 56 and Template. xml file 57 and Macro. An xml file 58 is generated.

  The background description 80 is image data. From the data of this description 80, Template. A pdf file 56 is generated.

  Fixed value data is input to the cell of the description 81 of “billing date”, the description 82 of “name”, the description 83 of “product number”, the description 84 of “unit price”, and the description 85 of “quantity”. From these fixed value data, Template. An xml file 57 is generated. Also, Template. The xml file 57 stores layout information such as cell coordinates, characters, and images from pixel information. Further, identification information is assigned to cells having variable values such as mathematical formulas and functions. For example, (id: L39 to id: L44) is assigned as identification information to the cells (L39 to L44) of the “subtotal” portion.

  Variable value data such as a calculation formula or a function is input to the cell of the “subtotal” description 86, the “total quantity” description 87, and the “total amount” description 88. From such variable value data cells, Macro. An xml file 58 is generated. At this time, conversion is performed as necessary. That is, the file analysis unit 21 refers to the extraction rule definition 54 and converts a description character string using an XML tag as a key. For example, when “SUM (K39: K44)” is described as the calculation formula, the description of this function is converted into a description that can be interpreted in the Web format, such as (K39 + K40 to + K44). Each calculation formula is associated with identification information.

  The file composition unit 22 uses Template. xml file 57 and Macro. From the XML file 58, the position coordinate format PRAS. An xml file 59 is generated.

  That is, as shown in FIG. Identification information is assigned to variable cell locations such as mathematical formulas and functions in the xml file 57. For example, (id: L39 to id: L44) is assigned as identification information to the cells (cell addresses L39 to L44) of the “subtotal” portion 86. Further, (id: K99) is assigned as identification information to the total quantity portion 87 (cell address K99). (Id: L99) is assigned as identification information to the total amount portion 88 (cell address L99).

  On the other hand, Macro. In the xml file 58, (id: L39 to L44) is assigned as identification information of mathematical expressions (J39 * K39) to (J44 * K44). (Id: K99) is assigned as identification information of the mathematical formula (K39 + K40 to + K44). (Id: L99) is assigned as identification information of the mathematical formulas (L39 + L40 to + L44).

  The file synthesis unit 22 is a Macro. The mathematical formula of the xml file 58 is calculated. Then, the file composition unit 22 sends the calculation result to Template. It is inserted at the position of the corresponding identification information in the xml file 57. That is, the calculation results of (¥ 160, ¥ 20, ¥ 11, ¥ 15, ¥ 60, ¥ 12) are obtained by calculating the mathematical expressions (J39 * K39) to (J44 * K44). These calculation results are shown in Template. It is inserted at the position indicated by the identification information (id: L39 to id: L44) of the xml file 57. Further, (17) calculation results are obtained by calculating the mathematical formula (K39 + K40 to + K44). The result of this calculation is Template. It is inserted at the position indicated by the identification information (id: K99) of the xml file 57. Also, the calculation result of (¥ 278) is obtained by calculating the mathematical formula (L39 + L40 to + L44). The result of this calculation is Template. It is inserted at the position indicated by the identification information (id: L99) of the xml file 57.

  Further, identification information is given to each object. Further, the same identification information as that of the object arranged in the cell is given to the cell in which the object is arranged. With reference to this identification information, Template. At the position of the corresponding identification information in the xml file 57, Template. Link information to the pdf file 56 is added. In this way, PRAS. An xml file 59 is generated.

  PRAS. In the xml file 59, as shown in FIG. 4, calculation results of mathematical formulas and functions are displayed at corresponding cell positions by the Web. Then, the generated PRAS. xml file 59 and Template. By linking with the pdf file 56, the form image can be displayed on the Web.

  In MacroXML, character string control is possible in addition to the calculation formula. Therefore, in the present invention, not only the calculation formula but also “similar” after a certain character string can be converted into a file of the position coordinate display format by using the extraction rule definition.

  It is also possible to extract and convert a description such as a description “(sheet name)” for calculation by referring to a cell of another worksheet. Even a calculation formula that refers to another worksheet can be handled.

  In addition, in a spreadsheet file, data that is not normally displayed or not printed may be described in a cell (eg, a memo of the creator of a spreadsheet file). This can be dealt with by specifying the cell and “not converting”.

  In the above example, the spreadsheet file is converted to the XML format. In the case of the XML format, a style sheet is used for display on the Web, but it may be converted into a format that does not use a style sheet (HTML format).

  In the above example, data of individual cells (fixed characters, fixed numbers, etc.) and the like are stored in Template. It is included in the file 57 of the pdf. In the above example, ruled line data is converted to Template. It is included in the file 57 of the pdf. The fixed value information displayed on the screen is treated as an image, and Template. You may make it include in the file 56 of pdf.

It should be noted that a program for realizing all or part of the functions of the conversion method is recorded on a computer-readable recording medium, and the program recorded on the recording medium is read into a computer system and executed, thereby executing each part. Processing may be performed. Here, the “computer system” includes an OS and hardware such as peripheral devices.
Further, the “computer system” includes a homepage providing environment (or display environment) if a WWW system is used.
The “computer-readable recording medium” refers to a storage device such as a flexible medium, a magneto-optical disk, a portable medium such as a ROM and a CD-ROM, and a hard disk incorporated in a computer system. Furthermore, the “computer-readable recording medium” dynamically holds a program for a short time like a communication line when transmitting a program via a network such as the Internet or a communication line such as a telephone line. In this case, a volatile memory in a computer system serving as a server or a client in that case, and a program that holds a program for a certain period of time are also included. The program may be a program for realizing a part of the functions described above, and may be a program capable of realizing the functions described above in combination with a program already recorded in a computer system.

  The embodiment of the present invention has been described in detail with reference to the drawings. However, the specific configuration is not limited to this embodiment, and includes design changes and the like without departing from the gist of the present invention.

10: Computer system 20: Conversion system 21: File analysis unit 22: File composition unit

Claims (2)

From a spreadsheet file, obtain information on fixed values with fixed values and information on variable values whose values can change depending on the calculation result based on the formula.
Generate a file containing the fixed value from the fixed value information,
From the information of the variable value file, refer to the reference value of the calculation formula, perform an operation based on the calculation formula, and generate a file including the value of the calculation result of the calculation formula,
A position coordinate format file or a Web page is generated from the file including the fixed value and the file including the value of the calculation result. Furthermore, image information is acquired from the spreadsheet file,
Generating an image information file from the image information;
The conversion method according to claim 1, wherein a position coordinate format file or a Web page including the image information file is generated.

Images