Note: This article has served its purpose and will soon be retired. To avoid "Page not found" errors, we remove links that we know about. If you have created links to this page, please remove them and together we will ensure data consistency across the web.
If you need to create an XML data file and an XML schema file from a range of cells on a worksheet, you can use version 1.1 of the XML Tools for Excel 2003 add-in to extend existing XML capabilities in Microsoft Excel 2007 and later.
Note: This add-in was developed for Excel 2003. The documentation and user interface refer to lists, which are called Excel tables in versions of the application later than Excel 2003.
For more information about working with this add-in, see Using the XML Tools Add-in version 1.1 for Excel 2003 .
Step 2: Convert a Range of Cells to an XML Table
Click the button OK.
In the highlighted line of the VBA code module, remove "50" from the line. In other words, change:
XMLDoc as MSXML2 . DOMDocument50
To whom:
XMLDoc as Msxml2. DomdocumentPress F5 to search for the next line containing the text "XMLDoc As msxml2.DOMDocument50", click OK and change the line as in the previous paragraph.
Press F5 again to find and change other instances of the string.
If you no longer see the VBA error message after pressing F5, close the Visual Basic Editor to return to the workbook. The range of cells will be converted to an XML table.
Note: To display all XML maps in a workbook, on the tab Developer in Group XML press the button Source to display the XML Source task pane. At the bottom of the XML Source task pane, click XML Maps.
If tab Developer is not visible, follow the first three steps in the next section to add it to the Excel ribbon.
Enter the data for which you want to create an XML data file and an XML schema file. The data must be presented in a tabular format in columns and rows (so-called regular data).
On the tab add-ons in Group Menu commands click the arrow next to the caption XML tools and press the button Convert Range to XML List.
Enter the range of cells containing the data to be converted as an absolute reference in the text box.
In field The first row contains the column names select No if the first line contains data, or Yes if the first row contains column headings, and click the button OK.
Excel will automatically generate the XML schema, bind the cells to the schema, and create the XML table.
Important: If the Visual Basic Editor opens and displays a Visual Basic for Applications (VBA) error message, follow these steps:
Step 3: Export the XML table to an XML data (XML) file
Note: When creating XML maps and exporting data to Excel as XML files, there is a limit to the number of rows that can be exported. When exporting to an XML file from Excel, you can save up to 65,536 rows. If the file contains more than 65,536 rows, Excel will only be able to export the first rows (number of rows mod 65,537). For example, if a worksheet contains 70,000 rows, Excel will export 4464 rows (70,000 mod 65,537). We recommend following one of the following tips: 1) use the XLSX format; 2) save the file in "XML 2003 Table (*.xml)" format (this will lose the mappings); 3) delete all lines after 65536 and then export again (this will keep the mappings but will lose the lines at the end of the file).
Step 4: Save the XML Schema in an XML Schema (.xsd) file
Note: This page is translated automatically, so it may contain inaccuracies and grammatical errors. It is important to us that this article is useful to you. Was the information helpful? For convenience also (in English).
If you are working on a local computer, then a document created in Microsoft Office is saved by default in a binary format that has the extension .doc, .xls, .ppt, etc., depending on the application in which it was created. The Word document can be saved as HTML(Hypertext Markup Language) and XML (extensible Markup Language- extensible markup language) and publish it on the corporate network or on the Internet by selecting from the menu File command Save as Web Page(Save As Web Page). Field File type(Save as type) dialog box of the command to save the file in such formats as: Webpage(Web Page), Web page with filter(Web Page, Filtered) and Web archive(web archive). Format Webpage(Web Page) retains all of the document's editing capabilities in HTML format. Format Web page with filter(Web Page, Filtered) removes part of the HTML code and reduces the file size. The Single file Web Page format allows you to send content (text and graphics) in one *.mht, *.mhtml file.
When using any of the above formats, all formatting options are preserved in each document. Users on a corporate network or on the Internet who do not have Microsoft Office installed on their computers can view files saved in HTML or XML formats in a browser or other Internet viewers.
Office provides high fidelity in a formatted document browser window that looks exactly the same as it does in the application window. The settings for charts and drawings, such as colors and line characteristics, are saved. A novice user who does not know how to program can create a web page in Office 2003 and publish it to a web server without losing the quality and functionality of the document. Automatic detection of HTML and XML encoding allows you to see the correct characters in any language environment.
Features of saving files in HTML format
There are static and dynamic HTML formats. The static format is used in cases where the data rarely changes, the dynamic format is used when it is necessary to display the latest data at the time of publication. Note that Access 2002 already supported converting objects to two dynamic HTML formats: ASP ( Active Server Pages) and IDC/HTX. The asp file is used for dynamic web pages, the idc file contains a query in the form of an SQL statement, and the htx file contains formatting commands and instructions. Instead of data in files with the htx extension. templates are included that indicate where to insert data from a query in a file with the idc extension.
When you save a document in HTML format, Office 2003 creates one HTML file (for example, otchet.htm) and a folder of the same name in the specified directory that includes all document support files and a control main file. Each figure, spreadsheet, presentation slide, set of auxiliary data are placed in a separate file. All graphics are automatically converted to .GIF, JPG, JPEG or .PNG depending on the content. For example, line art is saved as a GIF, while photographs are saved as a JPEG. When moving such documents, you must copy both the main HTML file and the folder with auxiliary files. (See "File Types Used to Display Pictures and Graphics on a Web Page" below for details.)
To make it easier to edit non-raster graphics with appropriate programs, when you convert an Office 2003 document to HTML, graphics are saved in both native and browser formats.
As an example, here is how to convert an Access database object to dynamic HTML format:
- In the window Database(Database) select the desired object with a mouse click and select from the menu File(File) command Export(Export To).
- In the dialog box Export object(Export To) in the field File type(Save as type) select the line Microsoft IIS 1-2 or Microsoft ASP Pages (Microsoft Active Server Pages).
- Select the folder where the exported file will be saved and enter the file name in Latin letters in the field File name(File name).
- Click the button Export(export).
- When using the Active Server Pages format, a dialog box will appear .asp file output setting(Microsoft Active Server Pages Output Options), in which specify the name of the ODBC data source. It will be used on the web server to access the data. Enter the address of the server that will host the .asp file.
Using Valuable XML
Office 2003 supports the XML language ( extensible Markup Language- extensible markup language). This language provides intuitive, powerful tools for teamwork and is widely used in the exchange of information on the Internet, regardless of the platform used, data format. The XML format was developed on the basis of HTML as a format for universal data representation in corporate networks and on the Internet. It has more features than HTML. Any Office document can be represented in this format.
XML makes it easy to retrieve data from Office 2003 documents into other XML applications. You can separate the contents of a document from the boundaries of its binary format. The content is made available for automatic data collection and analysis processes, with subsequent adjustment. An XML document can consist of several files of different types, for example, an XML file containing data; an ASP file containing a web page for viewing a document; xsd file containing a description of the data structure, etc. A single document can be viewed in Internet Explorer version 5 and above. The HTML file contains a script that loads data from an XML source.
Unlike generic HTML tags, XML tags can be modified by developers and therefore must be passed to the software that uses them. Microsoft intends to use XML forms exclusively in the corporate market. Because some browsers do not work with XML, Word provides the ability to save a document as a filtered Web page without using XML tags. To use this feature in the menu Service(Tools) select command Options(Options) and then open the tab Are common(General), press the button Web Document Options(Web Options) and go to the tab Reviewers(Browsers), select or clear the Disable features not supported by this browser check box. A set of XML tags that define the elements of a document is called a schema ( schema).
Saving and opening XML files in Word 2003 can be used to integrate with enterprise core business data. The graphical linking tool in Excel allows you to link a user-defined XML schema to cells in an Excel spreadsheet.
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 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 up RAM for the next portion of data.
In addition to the described requirements, it was necessary to add service functions:
- Enable AutoFilter
- Compressing a 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 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 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 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, "
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, " 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 the source code of my class for free. Just don't forget to fix the function writeDocumentProperties to get rid of the organization and user workflow system entities, or use their own similar entities with the appropriate properties. XML to Excel and vice versa, let's take a closer look at the types of these files. Here it should be noted right away that Excel documents have many formats, while XML is the file format. Therefore, do not confuse these two concepts. Microsoft Excel is a powerful calculation program with many useful features. Microsoft Excel is a special utility designed for creating and editing tables. You can already do anything with them: create databases, charts, formulas and other types of data. This is a very powerful tool, so we will not discuss all its capabilities. Today our goal is somewhat different, and we will not deviate from the topic of today's discussion. XML files, for easier understanding, are designed to store, exchange various data between utilities. This markup language is especially often used when transmitting information over the Internet. And when you need to transfer tabular data from Excel to the site, for example, then you will need to convert your document so that it displays correctly. Therefore, let's not delay the moment of truth and immediately proceed to the operation. Dear friends, in fact, file conversion is a fairly simple task. However, some users may experience problems when transferring data, so today we will look at several ways to implement our plan. Therefore, we gain patience and proceed to action: Before translating XML into Excel, you need to open it first. Let's take a look at this short process below. So let's get started: Dear readers, today we have learned not only how to convert XML files to Excel and vice versa, but also how to create and open them when versions are incompatible. We hope that everything worked out for you and there are no more questions left. Do not forget to share your opinion and experience in the comments: perhaps you know a simple way to convert. Tell other users if you managed to translate one type of document. If it worked, please share the method you used. I have been working with XML files for a long time, but only now I asked myself the question: How to 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 at school and, probably, there is no 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 appropriate tool that can be recommended to ordinary users for creating and editing 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 must be performed when creating an XML data file in Excel. I think that in earlier versions of Excel it will also work. Let's take a few simple steps to create an XML data file. Let's consider them on the example of creating an event data file. 1. First you need to create and fill in a data table in Excel in accordance with the data structure. Please use the menu item for this Insert/Table. 2. Let's create the schema of the XML file. To do this, in a text editor, enter the 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 Preferences. 4. It remains to match the elements of the scheme 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 cell of the table, press the right mouse button, select XML/Export... and save the XML file with the desired name. Save the Excel file for later editing and additions. In the following paragraphs 1.-4. will not need to be done! P.S. Why did I write this banal note? Firstly, so as not to forget yourself, and secondly, I think that this information will be useful to users, starting with the next version 0.6 ;-)", array("(col_count)"=>$this->colCount, "(row_count)"=>$this->rowCount))); )
But here the recording of the series is a more interesting process. The class must work quickly and process an unlimited amount of data, because there can be hundreds of thousands or even a million records! If you want speed - work with memory, if you want an unlimited amount of 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. Their joint use allows you to maintain a balance between speed and the 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. An example of a function for writing a numeric value:
After recording all the data, it remains 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 amount of exported data can be very large, a special iterator CDataProviderIterator is used, which iterates over 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 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
By the way, I have learned from my own experience how important it is to assume the possibility of the existence 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! By changing the query to use the CArrayDataProvider, we 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 representing information about closed incidents (see ITSM). Initial view of the exported table
Result
(sorry, picture disappears after posting) Export indicators
Final file volume: 1 312 269
Compressed file size: 141 762
Time taken: approx. 0.5
sec How to Convert Excel to XML
How to translate XML to Excel
Summing up
