I have been working with XML files for a long time, but only now I asked myself: How can I create and edit XML files in MS Excel using standard tools? Excel is a convenient and intuitive tool for creating, editing and processing various data. Excel is studied in school and, probably, there is not a person who knows how to work on a PC and does not know the basics of working in Excel. Therefore, in my opinion, this is the most suitable tool that can be recommended ordinary users to create and edit data files for various applications, including Web applications. I have MS Excel 2013 installed at home and using its example I will describe the simple procedures that need to be performed when creating an XML data file in Excel. I think that in earlier Excel versions this will work too. To create an XML data file, let's take a few simple steps. Let's look at them using the example of creating an event data file.
1. First, you need to create and fill out a data table in Excel in accordance with the data structure. Please use the menu item for this Insert/Table.
2. Let's create an XML file schema. To do this, in a text editor we enter following lines and save them in a file.
XHTML
|
3. Now open the tab Developer, press the button Source and specify the XML source file. If necessary, enable the menu item Developer in Excel Settings.
4. It remains to compare the elements of the diagram and the columns of the table by dragging the elements to the corresponding column headings.
5. And the last step: export the table data to an XML file using the context menu. Stand on any table cell, right-click, select XML/Export… and save the XML file with the desired name.
Save the Excel file for later editing and addition. Subsequently, paragraphs 1.-4. there will be no need to do this!
P.S. Why did I write this banal note? Firstly, so as not to forget, and secondly, I think that this information will be useful to users, starting with the next version 0.6 ;-)
XML is a universal format for working with data. It is supported by many programs, including those from the DBMS field. Therefore, converting information to XML is important precisely from the point of view of interaction and data exchange between various applications. Excel is just one of the programs that work with tables, and can even perform manipulations with databases. Let's figure out how to convert Excel files to XML.
Converting data to XML format is not such a simple process, since during this process a special schema (schema.xml) must be created. However, to convert information into simple file of this format It is enough to have the usual saving tools in Excel at hand, but to create a well-structured element you will have to tinker a lot with drawing up a diagram and connecting it to the document.
Method 1: Simple Saving
In Excel, you can save data in XML format simply by using the menu "Save as…". True, there is no guarantee that all programs will then work correctly with a file that was created in this way. And this method does not work in all cases.
Thus, the file conversion from Excel to XML format will be completed.
Method 2: Developer Tools
You can convert the Excel format to XML using the developer tools on the program tab. Moreover, if the user does everything correctly, then at the output he will receive, in contrast to previous method, a full-fledged XML file that will be interpreted correctly third party applications. But it must be said right away that not every beginner may have enough knowledge and skills to immediately learn how to convert data in this way.
- By default, the Developer Tools tab is disabled. Therefore, first of all, you need to activate it. Go to the tab "File" and click on the item "Options".
- In the parameters window that opens, move to the subsection "Customize the Ribbon". On the right side of the window, check the box next to the value "Developer". After that, click on the button "OK" located at the bottom of the window. The developer tools tab is now enabled.
- Next we open Excel spreadsheet in the program in any convenient way.
- Based on it, we have to create a diagram that can be generated in any text editor. For these purposes, you can use regular Windows Notepad, but it is better to use a specialized application for programming and working with markup languages Notepad++. Let's launch this program. We create a diagram in it. In our example, it will look as shown below in the screenshot of the Notepad++ window.
As you can see, the opening and closing tag for the document as a whole is "data-set". The tag plays the same role for each line "record". For the schema it will be enough if we take only two rows of the table, and do not manually translate it all into XML. The name of the opening and closing tag of a column can be arbitrary, but in this case, for convenience, we preferred to simply translate the Russian-language names of the columns into English language. After the data is entered, we simply save it through the functionality text editor anywhere on your hard drive in XML format called "schema".
- Let's go again to Excel program since already open table. Moving to the tab "Developer". On the ribbon in the toolbox "XML" click on the button "Source". In the field that opens on the left side of the window, click on the button "XML Maps...".
- In the window that opens, click on the button "Add…".
- The source selection window opens. Go to the directory for the location of the diagram compiled earlier, select it and click on the button "Open".
- After the elements of the diagram have appeared in the window, drag them using the cursor into the corresponding cells of the table column names.
- Right-click on the resulting table. IN context menu go step by step step by step "XML" And "Export…". After that, save the file in any directory.
As you can see, there are two main conversion methods XLS files and XLSX to XML format Microsoft program Excel. The first of them is extremely simple and consists of an elementary saving procedure with a given extension through the function "Save as…". The simplicity and clarity of this option are undoubtedly advantages. But it has one very serious drawback. The conversion is carried out without taking into account certain standards, and therefore a file converted in this way may simply not be recognized by third-party applications. The second option involves drawing up an XML map. Unlike the first method, a table converted using this schema will meet all XML quality standards. But, unfortunately, not every user will be able to quickly understand the nuances of this procedure.
Let's look at the procedure for converting a database object into XML format:
- In the database window, select the required object by left-clicking and select the command File, Export(File, Export) or right-click on the object and select the command from the context menu Export(Export).
- A dialog box will appear Export an object(Export To). In the dropdown list Types(Save as type) select the element XML Documents(XML Documents). In field File name(File name) enter the name HTML document and press the button Export(Export).
- A dialog box will appear XML export(Export XML) (Fig. 12.9).
- This dialog box contains three checkboxes for the three components of an XML document: data, data schema, and data presentation. (For more information, see the “XML Format in Microsoft Office 2002” section of this chapter). Select the check boxes for the parts of the XML document that you want to export. For example, if you select the checkboxes simultaneously data (XML)(Data (XML)) and data schema(Schema of the data), the data schema will be exported and saved separately from the XML data file, in an XSD file. For tables, you can export only the data or its structure, or the data and structure together and not export the table view as a Web page: checkbox data presentation(XSL) (Presentation of your data (XSL)) can be removed.
Rice. 12.9. Dialog window XML export
Comment
The Data (XML) check box should only be cleared if you only want to export a new representation of the database object and the data has previously been exported to an XML file. Otherwise, when you open the resulting Web page, you will receive a message about an error connecting to the data source, and the page itself will be almost empty.
- to install Extra options conversion, click the button Additionally(Advanced) (Fig. 12.10).
Rice. 12.10. Tab Data dialog box XML export
- In addition, this dialog box contains two more tabs for the two remaining components of the XML document: data schema and data representation. On each of these tabs, you can select a checkbox that allows you to export the corresponding part of the XML document, specify the path and name of the file in which this part of the document will be saved, and set some additional parameters:
- on the tab Data(Data) using dropdown list Encoding(Encoding) you can select the encoding to save the page contents;
- on the tab Scheme(Schema) you can use radio buttons to choose whether to export the data structure to a separate XSD file or embed it in an XML file along with the data;
Rice. 12.11. Account report opened in database
- on the tab Presentation(Presentation) using the switches, you can select the presentation format of the Web page: HTML processed on the client side or ASP server side. If the exported object contains pictures that you want to include in the resulting document, you can specify the name of the folder in which they are located. On the same tab there is a field for entering the name of the XSL file with a description of the page design. If specified file does not exist, Access 2002 will create an XSL file describing the external representation of the document (a developer can later use this as the basis for developing an XSLT file to convert the XML document to another format).
If required Additional Information about representing data in XML format, click the button Reference(Help). Information about developing and converting XML documents can be found on the Microsoft Developer Web site: http://msdn.microsoft.com/.
Rice. 12.12. The "Invoice" report, the data of which is converted into XML format, opened in Internet Explorer
- Click OK.
- If the proposed transformation parameters have not been changed, three files will be created as a result: XML - with data, XSL - with styles, and HTM - with a representation of the converted object. To open the resulting Web page, you need to open an HTM or ASP file in Internet Explorer, depending on which presentation format you selected.
Unfortunately, subforms and reports, and associated graphics, will not be added to the XML document obtained from the database object. However, simple objects with embedded graphics exported to XML will look almost exactly the same as the original ones in Access. You can verify this by comparing, for example, the original and the Invoice report converted to XML format from the Northwind database (Figures 12.11 and 12.12).
If there is a lot of data in the data source of the selected object, loading the Web page into the Internet browser may take a significant amount of time.
Comment
Since the data itself contained in such a Web document is stored entirely in the XML file, there is no need to access the database from which the data was originally taken (as, for example, is the case when converting a report to XML format). This means that that to provide access to such a document, it is enough to place only the mentioned set of files containing a description of this document on the Web server. On the other hand, this means that changing the data in such a document is only possible using applications that have access to the XML file and are able to perceive this format.
When developing an electronic document management system, it was necessary to implement functions for exporting data in popular formats. In particular, in Microsoft Excel format. The requirements for export were quite simple - export data with a minimum of formatting, i.e. no merged cells, playing with fonts, etc. Export formats XLSX and Excel XML.
In this case, I’ll tell you about Excel XML.
So, in any system that operates on tabular data, sooner or later the need to export data arises. Export purposes are different:
Implementing a set of functions in the class for recording cell and row values is the main requirement, which involves creating functions for recording cell values specified types and the ability to write the finished series to a file.
The ability to work with an unlimited amount of data - of course, the export class itself cannot be responsible for the volume being written, but it should provide functions for writing data to disk and releasing it random access memory for the next piece of data.
In addition to the described requirements, it was necessary to add service functions:
- Enabling AutoFilter
- Compress the file in zip.
Implementation
First of all, when creating a class, I check the final file name and request the number of columns and rows. The file must have a valid name, and the folder in which it will be saved must exist. Everything is as usual.The Excel XML format allows you to save information about the user who created it in the file, therefore, when creating a header, I write down the name of the organization, information about the user and the date the file was created.
Public function writeDocumentProperties($organization = null, $user = null) ( fwrite($this->file, "
True, it is in this function that the entities of the document management system are used - organization (organization) and user (user). Replacing these entities with, say, string values is not a problem.
The most interesting part of the header is the styling information. They are implemented very conveniently in the Excel XML format, so I simply create a table with styles for strings, date/time and hyperlinks.
Public function writeStyles() ( fwrite($this->file, "
Having completed the preparatory work, you can proceed to recording data. Opening a worksheet is just a couple of tags, just at this moment information about the number of columns and rows is used.
Public function openWorksheet() ( fwrite($this->file, " Public function resetRow() ( $this->currentRow = array(); ) public function flushRow() ( fwrite($this->file, implode("", $this->currentRow)); unset($this-> currentRow); ) Public function appendCellNum($value) ( $this->currentRow = " Public function exportExcelXML($organization, $user, &$filename) ( $this->_provider = new CArrayDataProvider(/*query*/); Yii::import("ext.AlxdExportExcelXML.AlxdExportExcelXML"); $export = new AlxdExportExcelXML ($filename, count($this->_attributes), $this->_provider->getTotalItemCount() + 1); $export->openWriter(); $export->openWorkbook(); $export->writeDocumentProperties($ organization, $user); $export->writeStyles(); $export->openWorksheet(); //title row $export->resetRow(); $export->openRow(true); foreach ($this->_attributes as $code => $format) $export->appendCellString($this->_objectref->getAttributeLabel($code)); $export->closeRow(); $export->flushRow(); //data rows $rows = new CDataProviderIterator($this->_provider, 100); foreach ($rows as $row) ( $export->resetRow(); $export->openRow(); foreach ($this->_attributes as $code => $format) ( switch ($format->type) ( case "Num": $export->appendCellNum($row[$code]); /*other types*/ default: $export->appendCellString(""); ) ) $export->closeRow(); $export->flushRow(); ) //close all $export->closeWorksheet(); $export->closeWorkbook(); $export->closeWriter(); //zip file $export->zip(); $filename = $export->getZipFullFileName(); ) Anyone interested can get it source my class for free. Just remember to correct the function writeDocumentProperties to unlink from the organization and user document management system entities, or use your own similar entities with the corresponding properties. I'll tell you quick way creating an xml file from an excel table! XML documents are a very strong and powerful thing. With one XML file you can fill your website with information in a matter of seconds! After all, all modern engines (CMS) have the ability to import from an XML file. So what am I talking about? As you know, *.xls formats (*.xlsx in the 2007 office) are formats Microsoft Office Excel. The 2003 office is a thing of the past, there is already 2010, but I work on 2007, and therefore I will talk based on it. Let's go! 1. Go to the Microsoft website and download the add-on for working with XML. Download Excel 2003 Add-in: XML Tools Add-in. It doesn't weigh much, 397 KB. 3. Now open Excel, go to the menu and select “Excel Options”. 4. In the window that appears, on the left, select the “Add-Ins” item, and at the bottom of the window, click on the “Go...” button. 5. A new window will open in front of you, in which you need to click the “Browse...” button. What it looks like is shown below. 6. Now you need to find the installed XmlTools add-on (see). Select it and click OK! 7. If you did everything correctly, you will see the following window! Feel free to click OK, installation is complete! 8. You now have an add-on tab in the top menu, and an XML Tools item on the left. We've sorted out the installation, and now let's move directly to converting (exporting) xls to xml. 1. To do this, open the file with the data that needs to be distilled. Then select the first item in the drop-down menu “Convert a Range to an XML List...” 2. A small window will open in front of you, what do you need in it? There are radio buttons, No and Yes, what are they for? It’s simple, if you have a header for the data (in my case there is one), select Yes, and if you don’t have it, then No. Then click on the small rectangle in the top line. 3. Select the area of data that we are converting and click on the button on the right in the window that appears. The previous window returns and click OK. 4. Your sign should change, one might say transform, for me it looks like this: 6. In the “File type” drop-down list, select XML data and click “Save”. Congratulations, your file has been created! I hope everything was presented in detail and clearly, but if you have any questions, write!", array("(col_count)"=>$this->colCount, "(row_count)"=>$this->rowCount))); )
But recording rows is a more interesting process. The class must work quickly and process an unlimited amount of data, because there can be a hundred thousand or even a million records! If you want speed, work with memory; if you want unlimited data, work with disk. To reconcile the requirements, I implemented the resetRow and flushRow functions.
The first one clears the current row, after which it can be filled with data again, and the second one writes the current row to an open file on disk. Using them together allows you to maintain a balance between speed and amount of memory used.
Each cell is written with a function corresponding to the data type, namely appendCellxxx, where xxx is the data type. Valid data types: Num, String, Real, DateTime, Date, Time, Link. Example of a function for writing a numeric value:
After recording all the data, all that remains is to close the worksheet and workbook.Application
The use of the described class is based on data export using the CArrayDataProvider provider. However, assuming that the volume of exported data may be very large, a special iterator CDataProviderIterator is used, which iterates through the returned data by 100 records (you can specify a different number of records).
In my case, each row is written to disk, which is quite acceptable for now, but may require changes in the future. For example, it would be wise to save not every row, but every ten or even a hundred rows at a time. Then the export speed will increase.Speed
By the way, I learned from my own experience how important it is to assume the possibility of the existence of large volumes of data during a batch operation such as export.
Initially, I tried to export data using CActiveDataProvider, which required about 240 seconds when exporting 1000 records! Changing the query to use CArrayDataProvider reduced the time to export 1000 records to 0.5 seconds!
I measured export indicators especially for this publication.
Exported 1626
records from 9
attributes representing information about closed incidents (see ITSM). Initial view of the exported table
Result
(sorry, the picture disappears after publication) Export figures
Final file size: 1 312 269
Compressed file size: 141 762
Time taken: approx. 0.5
sec 
2. Install it on your computer. There is nothing difficult to install. By default, the add-in is installed here: c:\Office Samples\OfficeExcel2003XMLToolsAddin
