How to convert xml file to excel. Other Ways to Import XML Data

When developing an electronic document management system, it was necessary to implement functions for exporting data in popular formats. In particular, in the format Microsoft Excel. The export requirements were quite simple - to export data with a minimum of formatting, i.e. no merged cells, font games, etc. XLSX and Excel XML export formats.

In this case, I'll talk about Excel XML.

So, in any system operating with tabular data, sooner or later there is a need to export data. Export purposes are different:

To implement in the class a set of functions for writing cell and series values ​​is the main requirement, which implies the creation of functions for writing cell values ​​of the 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 must provide functions for writing data to disk and freeing random access memory for the next piece of data.

In addition to the described requirements, it was necessary to add service functions:

• Enable AutoFilter
• Compressing a file in zip .

Implementation

Public function writeDocumentProperties($organization = null, $user = null) ( fwrite($this->file, " "); if (!is_null($user)) ( fwrite($this->file, " ".$user->description.""); fwrite($this->file, " ".$user->description.""); ) $dt = new Datetime(); $dt_string = $dt->format("Y-m-d\TH:i:s\Z"); fwrite($this->file, " ".$dt_string.""); fwrite($this->file, " ".$dt_string.""); if (!is_null($organization)) fwrite($this->file, " ".$organization->name.""); fwrite($this->file, " 12.00"); fwrite($this->file, ""); }
True, it is in this function that the entities of the workflow 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 style information. In the Excel XML format, they are implemented very conveniently, so I just create a table with styles for strings, dates / times and hyperlinks.

Public function writeStyles() ( fwrite($this->file, ""); //default style fwrite($this->file, ""); //Datetime style fwrite($this->file, ""); fwrite($this->file, ""); fwrite($this->file, ""); //Hyperlink style fwrite($this->file, ""); //Bold fwrite($this->file, ""); fwrite($this->file, ""); }

Finished preparatory work, you can proceed to data recording. 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, " "); fwrite($this->file, strtr("

public function resetRow() ( $this->currentRow = array(); ) public function flushRow() ( fwrite($this->file, implode("", $this->currentRow)); unset($this-> currentRow); )
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. An example of a function for writing a numeric value:

Public function appendCellNum($value) ( ​​$this->currentRow = " ".$value.""; }
After recording all the data, it remains to close the worksheet and workbook.

Application

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->append CellString(""); ) ) $export->closeRow(); $export->flushRow(); ) //close all $export->closeWorksheet(); $export->closeWorkbook(); $export->closeWriter(); //zip file $export->zip(); $filename = $export->getZipFullFileName(); )
In my case, each row is written to disk, which is fine for now, but may need to change 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

XML is an extensible form of Markup Language. A file with this extension can store databases, application settings, software bundle data, and other information. Such documents are widely used among users, so the question of how to open XML arises quite often.

Using text editors

There is text information inside the XML document, so you don't need to pay to view it in a readable form. software. You can use a browser, any text editor built into Windows or from a third party, or special programs to work with the XML format.

Windows Notepad and its analogues

As part of Windows, initially there is a program that can work with any text - Notepad. You can find it in the list standard applications in the start menu. Together with it, another text editor is preinstalled - WordPad. It can also be used to view XML documents.

If you click on the XML file right click, then immediately below the “Open” item there will be a line “Change”. When you click on it, the contents of the document will be displayed in Notepad. If you can't open the file this way, or you want to use WordPad to view it, open the "Open with" menu.

You can also use third-party notepads to read and edit XML documents, such as NotePad++. It implements syntax highlighting, which may seem convenient when editing a file.

Microsoft Office suite

Instead of Notepad, WordPad and others similar programs you can use the applications from the package Microsoft office- Word and Excel.

1. Start Word.
2. Specify the path to the XML document.

The disadvantage of Word is that it is difficult to edit XML in it. Therefore, if you want to change some data, it is better to use Excel.

1. Launch Excel.
2. Expand the main menu, click "Open".
3. Select an XML document.
4. Specify to open it as an XML table.

If instead of the Microsoft Office application package, the OpenOffice application library is installed on the computer, then it's okay: you can open XML through OpenOffice Calc, an analogue of Excel.

XML editors

If it is necessary not only to see the contents of the tables, but also to edit them, then it is recommended to use special software, designed to work with the XML format. You can use the following editors:

• XML Editor by Oxygen
• XML Marker
• xsemmel
• EditiX Lite Version

The programs differ in their capabilities: some have an XSLT transformation function for transforming XML documents, hints in the form of highlighted code sections; others offer only viewing and minimal editing.

The choice depends on the needs and skills of the user, since the software is specialized. However, you can download the listed programs for free.

Browser View

If the computer suddenly did not have any text editor, or the XML does not open in readable form, you can use a browser or view the contents of the file online.

Browsers

Everything modern browsers support reading XML format. However, you need to understand that since the document does not contain information on how to display data, web browsers show them "as is". To use a browser to open (using Chrome as an example):

Similarly, the launch is performed through other browsers. The browser will open new inset, inside which the contents of the XML document will be displayed.

In Mozilla Forefox, you can open a file in another way:

If the file is corrupted, the browser may display an error message when trying to open the document. In this case, it is recommended to use one of the XML editors mentioned above.

If someone sends an XML file containing data in tables, you won't have to read all the text and all the angle bracket tags. You can load this document directly into Excel, tell Excel how to display this document, and work with the data using maps.

XML (Extensible Markup Language, lit. Extensible Markup Language) has become a common format for exchanging information over the past few years, and it's not uncommon for people and organizations to send XML files to each other. The simple structures that underlie XML make it extremely easy to exchange information, whether or not all parties use the same software and browsers. However, until recently, while common XML utilities have become widespread, filling the gap between XML documents and user interface it was still hard enough. Microsoft Excel makes this easy, at least for data in a table grid.

This trick uses Excel features, available only in Excel for Windows older than 2003. More early versions Excel does not support them; these features are not supported in current or future versions of Excel for Macintosh.

Let's start with the simple XML document shown in Listing 8.1.

// Listing 8.1. Simple XML document to parse in Excel< ?xml version-"1.0" encoding-"UTF-8"?> 2003-10-05 0596005385 34.95 200 Zork's Books 2003-10-05 0596002920 2003-10-05 0596002378 29.95 300 Zork's Books 2003-10-05 0596005385 34.95 10 Books of Glory 2003-10-05 0596002920 39.95 25 Books of Glory 2003-10-07 0596002378 29.95 5 Books of Glory 2003-10-18 0596002378 29.95 15 title wave 2003-10-21 0596002920 39.95 15 Books for You

This document can be opened directly in Excel with the command File → Open (File → Open). A dialog box will open (Fig. 8.1).

If you select the As an XML list radio button, you will see a warning that Excel will create own scheme for this document that does not have a schema (Figure 8.2).

When you click OK, you'll see how Excel has chosen to present the information in the document you open as a spreadsheet (Figure 8-3). Note that Excel expects the date format that is used for the date element, so dates imported as 2003-10-05 will be displayed as 10/5/2003.

Now that the document is loaded in Excel, you can process the data just like any other data in Excel - insert it into formulas, create named ranges, build charts based on content, and more. To help you, Excel has several built-in data analysis capabilities.

The drop-down lists in the column headings allow you to choose how the data is sorted (by default, the data is displayed in the order in which it is written in the source document). You can also turn on the display of the total row. Total (Total); To do this, you can use the List toolbar or right-click anywhere in the list and in context menu select the command List → Total Row (List → Total Row). When the summary line appears, you can select the type of summary information in the drop-down menu (Fig. 8.4).

Rice. 8.4. Selecting totals for an XML list in Excel

The data can be updated by adding information from an XML document with the same structure to the area being updated. If you have another document with this structure, you can right-click the list, select XML → Import from the context menu, and select the second document. In addition, after editing, the data can be exported back to an XML file by right-clicking the list and selecting XML → Export from the context menu. This makes Excel very handy tool editing simple XML documents with a tabular structure.

If the data is fairly simple, you can most often trust Excel to choose how the contents of the file are presented and use the default settings provided. If the data becomes more complex, especially if it contains dates or text that looks like numbers, then you might want to use XML Schemas to tell Excel how to read the data and what data will fit in the given map. For our document, the XML schema might look like Listing 8.2.

// Listing 8.2. Schema for book sales data< ?xml version="1.0" encoding="UTF-8"?>

Note that the date element is defined as a date, and the ISBN element is defined as a string, not an integer. If you start by opening this schema rather than the document, you will force Excel to load the document by keeping the leading zero in the ISBN.

This time, you'll create the list before loading the XML document, starting with a blank worksheet. You will need to open the XML Source task pane. If it's not already open, press Ctrl+Fl. Then, select XML Source from the drop-down list at the top of the task pane, and you should see something similar to Figure 1. 8.6.

To load the schema, click the XML Maps button. The XML Maps dialog box will open (Figure 8.7).

Click the Add button to open the schema and select the schema (Figure 8.8). If the schema does not restrict documents to a single start element, a dialog box will appear asking you to select a root element. Since the documents in this example start with a sales element, select "sales".

When you click OK, you will be warned about possible difficulties in interpreting schemas. XML Schema (XML Schema) is a huge specification that supports an extremely large number of structures that do not fit the way information is perceived in Excel, so Excel has some limitations.

In the XML Maps dialog box, Excel will report that the schema has been added to the spreadsheet. If you click the OK button, you will return to the main Excel window and a diagram showing the structure of the schema appears in the XML Source task pane. Now that you have a structure, you can create a list. The easiest way to do this, especially with smaller documents like ours, is to drag the sales icon onto cell A1.

Now, having equipped the house for data, it is necessary to populate it. You can click the Import XML Data button on the List toolbar, or you can right-click the list and select XML → Import from the shortcut menu. If you select a file that you've already opened before (in Listing 8.1), you'll see a result similar to Figure 1. 8.3. Notice the addition of leading zeros to the values, which are now text as they should be.

Elements can also be dragged individually if you want to swap them, or place different pieces of information in different places on the spreadsheet.

Support for XML maps and lists in Excel means you can create spreadsheets that work with the data that comes in separate files, with more flexibility than previous formats such as CSV (comma delimited) or tab delimited format.

Instead of connecting to a database to edit data interactively, the user will be able to edit the XML file while on the plane and deliver it to the customer immediately after landing. Perhaps the best thing about Excel's new XML features is their flexibility. As long as the data is organized into a structure that matches the grid of the table, Excel has very few rules about what kinds of XML can be passed there. With a few clicks and absolutely no programming, you can integrate XML data into spreadsheets.

Representing data based on entering a description with tags or program settings. It is impossible to open them for editing with a regular double click. This is due to the fact that the association with the extension is not set desired application, which is the default. But if you want a human-readable, editable spreadsheet file, you can open the XML file in Excel. In this case, no converters are needed that can convert formats between themselves. The only caveat: this feature is available only in versions of Office version 2003 and higher.

How to open XML in Excel: method one

Consider importing data based on Excel 2016 version. The first and easiest way is to initially run Excel program. When the application starts, instead of a greeting and a logo, it will display a special login window, in which there is a line "Open other books" in the menu on the left.

The browse point is then used, and in the new window, XML is selected as the format to open. After that, by the usual method, we find the desired file and press the open button. In this case, it does not recognize Text Document, containing descriptions and tags, but as the most common table. Naturally, the data can be edited as you wish, but more on that later.

How to open XML format in Excel: method two

Practically no different from the first another proposed method. XML file in Excel can be opened from file menu or use the shortcut Ctrl + O for this.

Again, first the type of format to be opened is selected, after that the desired file is found and the corresponding button is pressed.

Opening XML: Method Three

There are a few more XML methods in Excel. So, in the 2016 version of the program, you can use the menu top panel, where the Data section is selected, and then the get external data button is clicked.

In the drop-down menu, you just need to select the section "From other sources" and in the new menu use the line "From XML import". After that follows standard procedure search desired file followed by opening.

Editing, saving and exporting

When using any of these methods, the user gets the structure of the table. Editing is done in exactly the same way as it is done with standard files XLS. Sometimes, for the convenience of editing and saving data, it is advisable to use the menu for developers.

In this case, you can import not all the contents of the XML file, but only what is really needed, inserting information into the appropriate columns and rows, specifying the XML object as the data source. But for this, you need to log in to your account in the program itself using your registration with Microsoft.

You can save the modified file immediately in the original format by selecting the appropriate type from the list. From the file menu, if the object was saved in "native" excel format, you can select the export function, click on change file type and set XML as the final format.

If the user is too lazy to engage in such transformations, or at work he uses a version of Office below version 2003, to open this format in the form of a table, you will have to use a special converter. There are quite a lot of such programs now offered. In extreme cases, if this is not suitable, you can easily turn to specialized online services, where the format will be changed within a couple of tens of seconds. At the end of these actions, all that remains is to upload the finished result in XLS format to HDD, and then open it in Excel. However, in most cases, such actions are not required, since in the same Office 2003, the possibility of directly opening (importing) the XML format is already provided initially. And it seems that few people today use outdated Microsoft office products.

I'll tell you fast way creating an xml file from an excel spreadsheet!

XML documents are a very strong and powerful thing. With the help of one XML file You can fill the site with information in seconds! Indeed, in all modern engines (CMS) there is the possibility of importing from an xml file. So why am I.

As you know, *.xls formats (*.xlsx in 2007 office) are Microsoft formats Office Excel. 2003 office is a thing of the past, there is already 2010, but I work for 2007, and, therefore, I will talk based on it. Let's go!

1. We go to the Microsoft website, and download the add-in for working with XML. Download Excel 2003 Add-in: XML Tools Add-in . It does not weigh much, 397 KB.

2. Install it on your computer. There is nothing difficult to install. By default, the add-in is installed here: c:\Office Samples\OfficeExcel2003XMLToolsAddin

3. Now open Excel, go to the menu and select "Excel Options".

4. In the window that appears, on the left, select the "Add-ons" item, and at the bottom of the window, click on the "Go to ..." button

5. A new window will open in front of you, in it you need to click the "Browse ..." button. How it looks is shown below.

6. Now you need to find the installed XmlTools add-in (see ). Select it and click OK!

7. If you did everything right, you will see the following window! Feel free to click OK, the installation is complete!

8. You have an add-on tab in the top menu, and the XML Tools item on the left.

We figured out the installation, and now we go directly to converting (exporting) xls to xml.

1. To do this, open the file with the data that needs to be overtaken. 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? Everything is simple, if you have a header for the data (in my case it is), select Yes, and if it is not there, then No, respectively. Then click on the small rectangle in the top row.

3. Select the area of ​​the data to be converted and click on the button on the right in the window that appears. The previous window is returned, in which we click OK.

4. Your plate should change, you can say it will change, it looks like this for me:

6. In the "File type" drop-down list, select XML data, click "Save".

Congratulations, your file has been created!

I hope everything was stated in detail and clearly, but if you have any questions, write!