Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
XML List of lists in Excel
I have an xml file in the form
<Executive <ExecNameJohn Smith</ExecName <ExecTitleVP</ExecTitle <Subordinates <Employee <EmpNameAlice Waters</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameJohn Thomas</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameMichael Rhodes</EmpName <EmpTitleSales Rep</EmpTitle </Employee </Subordinates </Executive It contains lists of repeating lists. I can import the xml into Excel 2003 and 2007, but when I try to export the file as xml I get a message "Cannot save or export XML data. The XML maps in this workbook are not exportable." Does Excel not support lists of lists? This is pretty basic XML hierarchy stuff. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
XML List of lists in Excel
Hi
I copied your data to Notepad and saved as Test.xml. I opened it in XL2003 and it opened fine. I saved it as XML as Copy of Test.xml and it worked fine. On opening the new Copy of Test.xml in Notepad it produced the following <?xml version="1.0"? <?mso-application progid="Excel.Sheet"? <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office" <LastAuthorRoger Govier</LastAuthor <Created2006-12-15T00:29:20Z</Created <Version11.8107</Version </DocumentProperties <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office" <DownloadComponents/ <LocationOfComponents HRef="file:///D:\"/ </OfficeDocumentSettings <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel" <WindowHeight7425</WindowHeight <WindowWidth14715</WindowWidth <WindowTopX480</WindowTopX <WindowTopY585</WindowTopY <MaxChange0.00001</MaxChange <Iteration/ <ProtectStructureFalse</ProtectStructure <ProtectWindowsFalse</ProtectWindows </ExcelWorkbook <Styles <Style ss:ID="Default" ss:Name="Normal" <Alignment ss:Vertical="Bottom"/ <Borders/ <Font/ <Interior/ <NumberFormat/ <Protection/ </Style <Style ss:ID="s21" <Font ss:Bold="1"/ </Style <Style ss:ID="s22" <Font ss:Bold="1"/ <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/ </Style </Styles <Worksheet ss:Name="test" <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="5" x:FullColumns="1" x:FullRows="1" <Column ss:Width="59.25"/ <Column ss:Index="3" ss:Width="177.75"/ <Column ss:Width="170.25"/ <Row <Cell ss:StyleID="s21"<Data ss:Type="String"/Executive</Data</Cell </Row <Row <Cell ss:StyleID="s22"<Data ss:Type="String"/ExecName</Data</Cell <Cell ss:StyleID="s22"<Data ss:Type="String"/ExecTitle</Data</Cell <Cell ss:StyleID="s22"<Data ss:Type="String"/Subordinates/Employee/EmpName</Data</Cell <Cell ss:StyleID="s22"<Data ss:Type="String"/Subordinates/Employee/EmpTitle</Data</Cell </Row <Row <Cell<Data ss:Type="String"John Smith</Data</Cell <Cell<Data ss:Type="String"VP</Data</Cell <Cell<Data ss:Type="String"Alice Waters</Data</Cell <Cell<Data ss:Type="String"Sales Rep</Data</Cell </Row <Row <Cell<Data ss:Type="String"John Smith</Data</Cell <Cell<Data ss:Type="String"VP</Data</Cell <Cell<Data ss:Type="String"John Thomas</Data</Cell <Cell<Data ss:Type="String"Sales Rep</Data</Cell </Row <Row <Cell<Data ss:Type="String"John Smith</Data</Cell <Cell<Data ss:Type="String"VP</Data</Cell <Cell<Data ss:Type="String"Michael Rhodes</Data</Cell <Cell<Data ss:Type="String"Sales Rep</Data</Cell </Row </Table <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel" <Selected/ <Panes <Pane <Number3</Number <ActiveRow7</ActiveRow <ActiveCol1</ActiveCol </Pane </Panes <ProtectObjectsFalse</ProtectObjects <ProtectScenariosFalse</ProtectScenarios </WorksheetOptions </Worksheet </Workbook I have never used the import or export of XML before, but it all looks OK to me. -- Regards Roger Govier "Dean" wrote in message ... I have an xml file in the form <Executive <ExecNameJohn Smith</ExecName <ExecTitleVP</ExecTitle <Subordinates <Employee <EmpNameAlice Waters</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameJohn Thomas</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameMichael Rhodes</EmpName <EmpTitleSales Rep</EmpTitle </Employee </Subordinates </Executive It contains lists of repeating lists. I can import the xml into Excel 2003 and 2007, but when I try to export the file as xml I get a message "Cannot save or export XML data. The XML maps in this workbook are not exportable." Does Excel not support lists of lists? This is pretty basic XML hierarchy stuff. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
XML List of lists in Excel
You'll notice that the structure of the XML in your example has changed
though. In my original example, I had a single top level element <Executive with three sub-elements <Employees. In your resulting file, the <Executive element is repeated for each of the <Employee elements. I need to maintain the original structure which should work using the Export XML... option, but unfortunately it doesn't work. Using the Export XML... option creates a simple "XML data only" file. Saving the file as XML, as you did, creates all the additional schema information and even changes the tag names. I can import and re-export the following correctly: <Subordinates <Employee <EmpNameAlice Waters</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameJohn Thomas</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameMichael Rhodes</EmpName <EmpTitleSales Rep</EmpTitle </Employee </Subordinates But if I add the Parent <Executive element, then it doesn't work. "Roger Govier" wrote: Hi I copied your data to Notepad and saved as Test.xml. I opened it in XL2003 and it opened fine. I saved it as XML as Copy of Test.xml and it worked fine. On opening the new Copy of Test.xml in Notepad it produced the following <?xml version="1.0"? <?mso-application progid="Excel.Sheet"? <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office" <LastAuthorRoger Govier</LastAuthor <Created2006-12-15T00:29:20Z</Created <Version11.8107</Version </DocumentProperties <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office" <DownloadComponents/ <LocationOfComponents HRef="file:///D:\"/ </OfficeDocumentSettings <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel" <WindowHeight7425</WindowHeight <WindowWidth14715</WindowWidth <WindowTopX480</WindowTopX <WindowTopY585</WindowTopY <MaxChange0.00001</MaxChange <Iteration/ <ProtectStructureFalse</ProtectStructure <ProtectWindowsFalse</ProtectWindows </ExcelWorkbook <Styles <Style ss:ID="Default" ss:Name="Normal" <Alignment ss:Vertical="Bottom"/ <Borders/ <Font/ <Interior/ <NumberFormat/ <Protection/ </Style <Style ss:ID="s21" <Font ss:Bold="1"/ </Style <Style ss:ID="s22" <Font ss:Bold="1"/ <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/ </Style </Styles <Worksheet ss:Name="test" <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="5" x:FullColumns="1" x:FullRows="1" <Column ss:Width="59.25"/ <Column ss:Index="3" ss:Width="177.75"/ <Column ss:Width="170.25"/ <Row <Cell ss:StyleID="s21"<Data ss:Type="String"/Executive</Data</Cell </Row <Row <Cell ss:StyleID="s22"<Data ss:Type="String"/ExecName</Data</Cell <Cell ss:StyleID="s22"<Data ss:Type="String"/ExecTitle</Data</Cell <Cell ss:StyleID="s22"<Data ss:Type="String"/Subordinates/Employee/EmpName</Data</Cell <Cell ss:StyleID="s22"<Data ss:Type="String"/Subordinates/Employee/EmpTitle</Data</Cell </Row <Row <Cell<Data ss:Type="String"John Smith</Data</Cell <Cell<Data ss:Type="String"VP</Data</Cell <Cell<Data ss:Type="String"Alice Waters</Data</Cell <Cell<Data ss:Type="String"Sales Rep</Data</Cell </Row <Row <Cell<Data ss:Type="String"John Smith</Data</Cell <Cell<Data ss:Type="String"VP</Data</Cell <Cell<Data ss:Type="String"John Thomas</Data</Cell <Cell<Data ss:Type="String"Sales Rep</Data</Cell </Row <Row <Cell<Data ss:Type="String"John Smith</Data</Cell <Cell<Data ss:Type="String"VP</Data</Cell <Cell<Data ss:Type="String"Michael Rhodes</Data</Cell <Cell<Data ss:Type="String"Sales Rep</Data</Cell </Row </Table <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel" <Selected/ <Panes <Pane <Number3</Number <ActiveRow7</ActiveRow <ActiveCol1</ActiveCol </Pane </Panes <ProtectObjectsFalse</ProtectObjects <ProtectScenariosFalse</ProtectScenarios </WorksheetOptions </Worksheet </Workbook I have never used the import or export of XML before, but it all looks OK to me. -- Regards Roger Govier "Dean" wrote in message ... I have an xml file in the form <Executive <ExecNameJohn Smith</ExecName <ExecTitleVP</ExecTitle <Subordinates <Employee <EmpNameAlice Waters</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameJohn Thomas</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameMichael Rhodes</EmpName <EmpTitleSales Rep</EmpTitle </Employee </Subordinates </Executive It contains lists of repeating lists. I can import the xml into Excel 2003 and 2007, but when I try to export the file as xml I get a message "Cannot save or export XML data. The XML maps in this workbook are not exportable." Does Excel not support lists of lists? This is pretty basic XML hierarchy stuff. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
XML List of lists in Excel
Hi
I understand what you are now saying, but as this is my first time of using XML I'm afraid I can't offer any help. I merely tried to import and export to see if it worked in XL2003 on my machine. Hopefully, someone else will be able to offer some support. -- Regards Roger Govier "Dean" wrote in message ... You'll notice that the structure of the XML in your example has changed though. In my original example, I had a single top level element <Executive with three sub-elements <Employees. In your resulting file, the <Executive element is repeated for each of the <Employee elements. I need to maintain the original structure which should work using the Export XML... option, but unfortunately it doesn't work. Using the Export XML... option creates a simple "XML data only" file. Saving the file as XML, as you did, creates all the additional schema information and even changes the tag names. I can import and re-export the following correctly: <Subordinates <Employee <EmpNameAlice Waters</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameJohn Thomas</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameMichael Rhodes</EmpName <EmpTitleSales Rep</EmpTitle </Employee </Subordinates But if I add the Parent <Executive element, then it doesn't work. "Roger Govier" wrote: Hi I copied your data to Notepad and saved as Test.xml. I opened it in XL2003 and it opened fine. I saved it as XML as Copy of Test.xml and it worked fine. On opening the new Copy of Test.xml in Notepad it produced the following <?xml version="1.0"? <?mso-application progid="Excel.Sheet"? <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office" <LastAuthorRoger Govier</LastAuthor <Created2006-12-15T00:29:20Z</Created <Version11.8107</Version </DocumentProperties <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office" <DownloadComponents/ <LocationOfComponents HRef="file:///D:\"/ </OfficeDocumentSettings <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel" <WindowHeight7425</WindowHeight <WindowWidth14715</WindowWidth <WindowTopX480</WindowTopX <WindowTopY585</WindowTopY <MaxChange0.00001</MaxChange <Iteration/ <ProtectStructureFalse</ProtectStructure <ProtectWindowsFalse</ProtectWindows </ExcelWorkbook <Styles <Style ss:ID="Default" ss:Name="Normal" <Alignment ss:Vertical="Bottom"/ <Borders/ <Font/ <Interior/ <NumberFormat/ <Protection/ </Style <Style ss:ID="s21" <Font ss:Bold="1"/ </Style <Style ss:ID="s22" <Font ss:Bold="1"/ <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/ </Style </Styles <Worksheet ss:Name="test" <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="5" x:FullColumns="1" x:FullRows="1" <Column ss:Width="59.25"/ <Column ss:Index="3" ss:Width="177.75"/ <Column ss:Width="170.25"/ <Row <Cell ss:StyleID="s21"<Data ss:Type="String"/Executive</Data</Cell </Row <Row <Cell ss:StyleID="s22"<Data ss:Type="String"/ExecName</Data</Cell <Cell ss:StyleID="s22"<Data ss:Type="String"/ExecTitle</Data</Cell <Cell ss:StyleID="s22"<Data ss:Type="String"/Subordinates/Employee/EmpName</Data</Cell <Cell ss:StyleID="s22"<Data ss:Type="String"/Subordinates/Employee/EmpTitle</Data</Cell </Row <Row <Cell<Data ss:Type="String"John Smith</Data</Cell <Cell<Data ss:Type="String"VP</Data</Cell <Cell<Data ss:Type="String"Alice Waters</Data</Cell <Cell<Data ss:Type="String"Sales Rep</Data</Cell </Row <Row <Cell<Data ss:Type="String"John Smith</Data</Cell <Cell<Data ss:Type="String"VP</Data</Cell <Cell<Data ss:Type="String"John Thomas</Data</Cell <Cell<Data ss:Type="String"Sales Rep</Data</Cell </Row <Row <Cell<Data ss:Type="String"John Smith</Data</Cell <Cell<Data ss:Type="String"VP</Data</Cell <Cell<Data ss:Type="String"Michael Rhodes</Data</Cell <Cell<Data ss:Type="String"Sales Rep</Data</Cell </Row </Table <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel" <Selected/ <Panes <Pane <Number3</Number <ActiveRow7</ActiveRow <ActiveCol1</ActiveCol </Pane </Panes <ProtectObjectsFalse</ProtectObjects <ProtectScenariosFalse</ProtectScenarios </WorksheetOptions </Worksheet </Workbook I have never used the import or export of XML before, but it all looks OK to me. -- Regards Roger Govier "Dean" wrote in message ... I have an xml file in the form <Executive <ExecNameJohn Smith</ExecName <ExecTitleVP</ExecTitle <Subordinates <Employee <EmpNameAlice Waters</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameJohn Thomas</EmpName <EmpTitleSales Rep</EmpTitle </Employee <Employee <EmpNameMichael Rhodes</EmpName <EmpTitleSales Rep</EmpTitle </Employee </Subordinates </Executive It contains lists of repeating lists. I can import the xml into Excel 2003 and 2007, but when I try to export the file as xml I get a message "Cannot save or export XML data. The XML maps in this workbook are not exportable." Does Excel not support lists of lists? This is pretty basic XML hierarchy stuff. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting data from a sub-totalled list to other lists | Excel Discussion (Misc queries) | |||
auto updating list | Excel Worksheet Functions | |||
Help with Lists... | Excel Discussion (Misc queries) | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) |