Convert file to xml. Create and edit XML file in Excel. Using valuable XML

After importing XML data, mapping the data to cells in a worksheet, and making changes to the data, you often need to export or save the data as an XML file.

Important:

XML data export (max 65,536 lines)

XML data export (more than 65,536 lines)

Find the difference between the total number of lines in the file and the number 65537. Let's denote this number as x.

Delete x lines from the beginning of the Excel sheet.

Export the sheet to an XML data file (see the previous section for the procedure).

Click the button Close, but do not save sheet. Then open the Excel sheet again.

Delete all data after the entire x, and then export it as an XML data file (see the previous section of the procedure).

This will ensure that the rest of your data is not lost. At this point, you have two export XML files that can be combined to create a duplicate of the original sheet.

Saving XML Data in Mapped Cells in an XML Data File

If you want backward compatibility with earlier versions of XML functionality, you can save the file as an XML data file instead of using the command Export.

Note: If the sheet contains titles or labels that are different from the names of the XML elements in the XML map, then when you export or save the XML data Excel application uses XML element names.

Common Issues When Exporting XML Data

When exporting XML data, messages similar to the following may appear.

This XML Map can be exported, but some required elements are not mapped

This message may appear for the following reasons.

The XML map associated with this XML table has one or more required elements that are not associated with it.

A hierarchical list of items in the Source XML Task Pane indicates the presence of required items by placing a red asterisk on the right upper corner icon to the left of each item. To map the item you want, drag it onto the sheet where you want it to appear.

The element is a recursive structure.

A typical example of a recursive structure is a hierarchy of employees and managers in which the same XML elements are nested at multiple levels. Although you could match all items in the XML Source task pane, Excel does not support recursive structures that are more than one level, so they cannot match all items.

The XML table contains mixed content.

Mixed content occurs when an element contains a child element and plain text outside of the child element. This is often the case when formatting tags (such as bold tags) are used to mark data within an element. The child element can be displayed (if supported in Excel), but the text content is lost when importing data and is not available when exporting, that is, it is not used in either direct or reverse operation.

Unable to export XML maps in workbook

The XML map cannot be exported if the relationship of the associated element with other elements cannot be preserved. The relationship may not last for the following reasons.

The schema definition of the matched item is contained in a sequence with the following attributes:

attribute maxoccurs not equal to 1;

a sequence contains more than one direct child or includes another compound object as such an element.

Non-repeating siblings with the same repeating parent are mapped to different XML tables.

Multiple repeating elements are mapped to the same XML table, and the repetition is not defined by an ancestor.

Child elements different parent elements are mapped to the same XML table.

In addition, you cannot export an XML Map if it contains one of the following XML Schema constructs.

List of lists. One list of items has another list of items.

Unnormalized data. An XML table contains an element that, according to the definition, must appear once in the schema (the maxoccurs assigned the value 1). When you add such an element to an XML table, Excel will populate a table column with multiple instances of it.

Choice. The matched item is part of the schematic design .

Announcement

XML Document File Format

Designed as a format for storing textual data, XML is a document that is understood not only by humans, but also by machines. XML is a platform independent language that was created to store a variety of data. With its simplicity and ease of use, this language is not inferior in popularity to the HTML language. It is also quite common on the Internet. The fact that XML files can be easily edited by the simplest text editors, only increases its popularity.

Technical details of XML files

An XML document is a sequence of characters in which each Unicode character can be found in every single document. This Unicode code that makes up an XML document is split into tokens and textual content based on simple syntax rules. This format has an important advantage over HTML: XML format supports arbitrary tagging to clearly define the data that such tags cover.

More information on the XML format

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 tell you about Excel XML.

So, in any system operating with tabular data, early or later, there is a need to export data. The export goals are different:

Implementing in the class a set of functions for writing the values of cells and a series is the main requirement, which implies the creation of functions for writing the values of cells 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 writeable amount, but it must provide functions for writing data to disk and freeing random access memory for the next chunk of data.

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

Enabling AutoFilter
Compressing the file to zip.

Implementation

First of all, when creating a class, I check the final file name and ask for the number of columns and rows. The file must have the correct name, and the folder where 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 the 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, " "); 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 title is the style information. They are implemented very conveniently in the Excel XML format, so I just create a table with styles for strings, date / time and hyperlink.

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,""); }

Completed 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, " "); fwrite ($ this-> file, strtr ("

File extension	.xml
File category
Example file	(252.17 KiB)
Related programs	Microsoft Visual Studio 2013 JAPISoft EditiX Wattle XMLwriter MacroMates TextMate

", array (" (col_count) "=> $ this-> colCount," (row_count) "=> $ this-> rowCount)));)
But writing the rows is a more interesting process. The class should work quickly and process an unlimited amount of data, because records can be one hundred thousand or even a million! If you want speed, work with memory, if you want an unlimited amount of data, work with a 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 to disk. Their use together allows you to strike a balance between speed and the amount of memory used.

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 to write a numeric value:

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

Application

The use of the described class is based on exporting data using the CArrayDataProvider provider. However, assuming that the amount of exported data may be very large, a special CDataProviderIterator iterator was used, which iterates over the returned data by 100 records (you can specify a different number of records).

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, for now, is quite acceptable, but in the future, it may need to be changed. 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, on own experience saw how important it is to assume the possibility of large amounts of data in 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 export time for 1000 records to 0.5 seconds!
Especially for this publication, I measured export indicators.
Exported 1626 records from 9 attributes that represent information about closed incidents (see TSM).

The original view of the exported table

Result

(sorry, the picture disappears after posting)

Export figures

Final file size: 1 312 269
Compressed file size: 141 762
Elapsed time: approximately 0.5 sec

Anyone interested can get the source code of my class for free. Just remember to correct the function. writeDocumentProperties to decouple from the organization and user entities of the workflow system, or use their similar entities with the corresponding properties.

The question of how to save with 1C in XML file, worries people who work with company documentation and reports. These professionals include accountants. Today's standards imply the storage of most of the enterprise information in the 1C program. At the same time, the formation of documents in Excel remains in demand, because it is necessary for data processing, exchange of accounts with counterparties. The use of modern and progressive methods of transferring information between 1C and Excel greatly saves the time of the person who is doing this.

An unloading procedure is provided for the quick implementation of this operation. Going into 1C, the user selects the "File" section, then "Upload". On the hard disk of the computer or on a removable device, a specific location is selected where the file will be sent, and its format. If you need an Excel file, choose the XML format. It takes a long time to upload large documents. Before starting it, it is advisable to figure out what specific amount of information you are interested in and unload it. This is achieved by selecting the necessary blocks when working in 1C. If you need to add new positions to the existing data in XML, this is done by unloading into an existing document, during which the merge occurs. When you need to add multiple lines, it is easier to do this via the clipboard.

How to transfer data from XML file to 1C?

More often, during the work of an enterprise, you need to carry out the reverse procedure: get an Excel file in 1C. Let's see how to do this. Modern versions of 1C offer a similar procedure through the "Import" or "Download" tab. They are located in the same File category. The user selects the desired document on a computer or removable disk, after which it is loaded into the 1C database. 1C developers have provided for a "quarantine" zone, where files are first sent after downloading from other programs. Such a zone is needed to pre-check the validity of the data and especially the formatting. If the formatting is knocked down, you first need to restore it using special services or manually. Loading data with broken formatting in 1C leads to crashes and distortions in the database.

If there are difficulties in loading data or the format is constantly getting lost, then you need to check the relevance of the versions of the 1C and Excel programs. If you use modern versions, then the chance of problems is minimal.

Setting up serialization when porting to 1C

Serialization is the process of converting data from 1C to Excel and vice versa. The serialization procedure starts automatically when the user chooses to import or export data from 1C. It will not hurt to pre-configure the transfer and serialization of files. It is possible to select the file format, bit depth and other parameters that will help to avoid errors when exchanging data between different programs.

To upload a large number of files from Excel to 1C, you cannot do with standard copying. For such purposes, a special bootloader is being developed that connects to 1C and controls the boot procedure. Often a company has to order such a program for data transfer from developers, taking into account the specifics of a particular enterprise. It is costly, but pays off due to the fact that the download speed of salary files and other documents is noticeably increased, and errors during the transfer disappear.

Using web services to transfer data to 1C

To upload data to 1C, you can use specialized web services. They help to create an Excel file in 1C and do not require financial costs. You can upload information with their help once, or you can use such services all the time. It is advisable to choose services that are compatible with your versions of programs, then the likelihood of failures will become low.

The work of sending data with 1C 7.7 does not imply special education, but requires skills and experience. If an untrained employee gets down to business, it is advisable to control him so that the organization's database is not filled with inaccurate or damaged information.

In any data transfer procedure, great attention should be paid to the integrity of the data structure. As part of this, it is advisable to follow the recommendations below.

Before starting the download, make sure that the software versions are compatible.

Load data into the 1C "quarantine" zone so that unverified data does not immediately fall into a single database and do not distort it.

Check the format of the data that was received from Excel. If the format was knocked off during the transfer, it will take time to restore it. If this is not done, then individual cells can be summed up in an unpredictable way: quantity of goods with mass, price with total value. This will disrupt the statistics and data analysis system.

It is advisable that the downloaded data be checked by an experienced employee before being sent to the database: the owner of the enterprise, the chief accountant or another responsible person.

It is helpful for staff to develop and communicate file upload rules. Often errors and failures occur due to carelessness, absent-mindedness, negligence of individual employees. They enter data from two cells into one, use different abbreviations, different signs (period and comma) when writing fractional numbers, make other similar oversights that greatly confuse the format and lead to unwanted crashes and errors. For the management of the enterprise, it is worth developing a unified data recording format, then there will be fewer problems when transferring through a loader or manually. Automation of a company with the help of 1C developments assumes that employees of all levels will use a single program for data entry. Additional training can be useful if individual staff do not know how to use such a system or make mistakes when entering values.

Today, developers of programs like 1C offer constant updates that simplify work with data, so it doesn't hurt to check for updates and download them for the program. This will help you leverage the latest developments to simplify the loading and unloading of information.

I'll tell you quick way creating an xml file from the Excel table!

XML documents are very powerful and powerful. With the help of one XML file, you can fill your site with information in a matter of seconds! Indeed, in all modern engines (CMS) there is the ability to import from a hml file. So what am I doing.

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 tell 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.

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 the "Excel Options" item.

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 ..."

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-on (watch). Select it and click OK!

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

8. You have an add-in tab in the top menu, and XML Tools 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 to be overtaken. Then we 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, it is), select Yes, and if it does not exist, then accordingly No. Then click on the small rectangle on the top row.

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 is returned, in which we press OK.