By using XML and Excel, you can manage workbooks and data in ways that were previously impossible or very difficult. By using XML maps, you can easily add, identify, and extract specific pieces of business data from Excel documents.
For example, an invoice that contains the name and address of a customer or a report that contains last quarter's financial results are no longer just static reports. You can easily import this information from databases and applications, revise it, and export it to the same or other databases and applications. Extend the functionality of existing Excel templates by mapping XML elements onto existing cells. This makes it easier to get XML data into and out of your templates without having to redesign them.
The following diagram shows how the different files and operations work together when you use XML with Excel. Essentially, there are five phases to the process:. Adding an XML schema file. Importing an XML data file. Entering data, moving mapped cells, and leveraging Excel functionality, while preserving XML structure and definitions. Exporting revised data from mapped cells to an XML data file. You can create or open a workbook in Excel, attach an XML schema file. After you map the XML elements to your worksheet, you can import and export XML data into and out of the mapped cells.
When you add an XML schema file. In general, XML maps are used to create mapped cells and to manage the relationship between mapped cells and individual elements in the XML schema. In addition, these XML maps are used to bind the contents of mapped cells to elements in the schema when you import or export XML data files. There are two kinds of mapped cells that you can create: single-mapped cells and repeating cells which appear as XML tables.
To make designing your worksheet more flexible, you can drag the mapped cells anywhere on a worksheet and into any order — even one different from the XML schema.
You can also choose which elements to map and not map. An XML map can only contain one root element. If you add a schema that defines more than one root element, you are prompted to choose the root element to use for the new XML map. The following diagram shows the main features of this task pane. The following table summarizes each type of XML element that Excel can work with and the icon that is used to represent each type of element.
A single-mapped cell is a cell that has been mapped to a nonrepeating XML element. You create a single-mapped cell by dragging a nonrepeating XML element from the XML Source task pane onto a single cell in your worksheet. When you drag a nonrepeating XML element onto the worksheet, you can use a smart tag to choose to include the XML element name as a heading above or just to the left of the single-mapped cell, or you can use an existing cell value as a heading.
XML tables are similar in appearance and functionality to Excel tables. Drag one or more repeating elements from the XML Source task pane to a worksheet.
You can change these to any column headings that you want. However, the original XML element names are always used when you export data from the mapped cells. This option works as long as the multiple fields are dropped on the same row, one adjacent to the other. When this option is cleared, each element appears as its own XML table. My Data Has Headings When selected, existing heading data is used as column headings for repeating elements that you map to your worksheet.
When this option is cleared, the XML element names are used as column headings. However, XML tables do have some limitations regarding how they can be arranged on the worksheet. XML tables are row-based, meaning that they grow from the header row down. You cannot add new entries above existing rows. Just as in an Excel table, formulas in an XML table are filled down the column when new rows are added to the table.
An XML map and its data source information are saved with the Excel workbook, not a specific worksheet. Furthermore, if you save your workbook as a macro-enabled Excel Office Open XML Format File, this map information can be viewed through Microsoft Notepad or through another text-editing program. If you want to keep using the map information but remove the potentially sensitive data source information, you can delete the data source definition of the XML schema from the workbook, but still export the XML data, by clearing the Save data source definition in workbook check box in the XML Map Properties dialog box, which is available from the Map Properties command in the XML group on the Developer tab.
If you delete a worksheet before you delete a map, the map information about the data sources, and possibly other sensitive information, is still saved in the workbook. If you are updating the workbook to remove sensitive information, make sure that you delete the XML map before you delete the worksheet, so that the map information is permanently removed from the workbook. When you import data, you bind the data from the file to an XML map that is stored in your workbook.
Validate data against schema for import and export Specifies whether Excel validates data against the XML map when importing data. Overwrite existing data with new data Specifies whether data is overwritten when you import data. Click this option when you want to replace the current data with new data, for example, when up-to-date data is contained in the new XML data file.
Append new data to existing XML tables Specifies whether the contents of the data source are appended to the existing data on the worksheet. Click this option, for example, when you are consolidating data from several similar XML data files into an XML table, or you do not want to overwrite the contents of a cell that contains a function.
When you import XML data, you may want to overwrite some mapped cells but not others. For example, some mapped cells may contain formulas and you don't want to overwrite the formula when you import an XML file. There are two approaches that you can take:. Unmap the elements that you don't want overwritten, before you import the XML data.
After you import the XML data, you can remap the XML element to the cells containing the formulas, so that you can export the results of the formulas to the XML data file. In this "Import" XML map, don't map elements to the cells that contain formulas or other data that you don't want overwritten. Use another XML map for exporting the data. Failed to load latest commit information. View code. It supports scenarios such as: High-performance generation of word-processing documents, spreadsheets, and presentations.
Document modification, such as adding, updating, and removing content and metadata. Search and replace content using regular expressions.
Splitting up shredding a file into multiple files, and combining multiple files into a single file. OpenXml DocumentFormat. Linq DocumentFormat. Releases 23 v2. Dec 17, Packages 0 No packages published. Used by You signed in with another tab or window.
Reload to refresh your session. You signed out in another tab or window.
0コメント