Importing xml file in spreadsheet

#1

Hello,

Does anyone know how to get xml file that includes formatting (bolding, colors, etc) to display in the spreadsheet widget?

I have generated xml files that show as follows in Excel

image
image1054×523 73.3 KB

in the webix spreadsheet widget it displays as follows:

image
image1050×607 45.9 KB

the xml file example:

<?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">
  <LastAuthor>paw</LastAuthor>
  <Created>2024-1-18T11:38:28Z</Created>
  <LastSaved>2024-1-18T11:38:28Z</LastSaved>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Color="#000000"/>
   <NumberFormat/>
  </Style>
  <Style ss:ID="s30">
   <Alignment ss:Horizontal="CenterAcrossSelection" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Color="#FFFFFF" ss:Bold="1"/>
   <Interior ss:Color="#800000" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s31">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Color="#FFFFFF" ss:Bold="1"/>
   <Interior ss:Color="#000080" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s32">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Color="#FFFFFF" ss:Bold="1"/>
   <Interior ss:Color="#000080" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s33">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
  </Style>
  <Style ss:ID="s34">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="mm/dd/yyyy;@"/>
  </Style>
  <Style ss:ID="s35">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="##########"/>
  </Style>
  <Style ss:ID="s36">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
  </Style>
  <Style ss:ID="s37">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="mm/dd/yyyy;@"/>
  </Style>
  <Style ss:ID="s38">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="##########"/>
  </Style>
  <Style ss:ID="s39">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
  </Style>
  <Style ss:ID="s40">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="mm/dd/yyyy;@"/>
  </Style>
  <Style ss:ID="s41">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="##########"/>
  </Style>
  <Style ss:ID="s42">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
  </Style>
  <Style ss:ID="s43">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="mm/dd/yyyy;@"/>
  </Style>
  <Style ss:ID="s44">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="##########"/>
  </Style>
  <Style ss:ID="s45">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="###################################"/>
  </Style>
  <Style ss:ID="s46">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="###################################"/>
  </Style>
  <Style ss:ID="s47">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="###################################"/>
  </Style>
  <Style ss:ID="s48">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="###################################"/>
  </Style>
  <Style ss:ID="s49">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="####################"/>
  </Style>
  <Style ss:ID="s50">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="####################"/>
  </Style>
  <Style ss:ID="s51">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="####################"/>
  </Style>
  <Style ss:ID="s52">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="####################"/>
  </Style>
  <Style ss:ID="s53">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="##"/>
  </Style>
  <Style ss:ID="s54">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="##"/>
  </Style>
  <Style ss:ID="s55">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="##"/>
  </Style>
  <Style ss:ID="s56">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="##"/>
  </Style>
  <Style ss:ID="s57">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="###_);[Red](###)"/>
  </Style>
  <Style ss:ID="s58">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="###_);[Red](###)"/>
  </Style>
  <Style ss:ID="s59">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10"/>
   <NumberFormat ss:Format="###_);[Red](###)"/>
  </Style>
  <Style ss:ID="s60">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Font ss:FontName="Arial" ss:Size="10" ss:Bold="1"/>
   <NumberFormat ss:Format="###_);[Red](###)"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="fac" ss:Protected="0">
 <Table>
  <Column ss:AutoFitWidth="1" ss:Width="93.5"/>
  <Column ss:AutoFitWidth="1" ss:Width="192.5"/>
  <Column ss:AutoFitWidth="1" ss:Width="192.5"/>
  <Column ss:AutoFitWidth="1" ss:Width="110"/>
  <Column ss:AutoFitWidth="1" ss:Width="27.5"/>
  <Column ss:AutoFitWidth="1" ss:Width="55"/>
  <Column ss:AutoFitWidth="1" ss:Width="60.5"/>
   <Row>
    <Cell ss:StyleID="s30"><Data ss:Type="String">111 &#45; Company 11</Data></Cell>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
   </Row>
   <Row>
    <Cell ss:StyleID="s30"><Data ss:Type="String">Facilities List</Data></Cell>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
   </Row>
   <Row>
    <Cell ss:StyleID="s30"><Data ss:Type="String">Run On&#58; 01&#47;18&#47;2024 at 11&#58;38 AM by paw</Data></Cell>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
   </Row>
   <Row>
    <Cell ss:StyleID="s30"><Data ss:Type="String"> </Data></Cell>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s30"/>
   </Row>
   <Row>
    <Cell ss:StyleID="s31"><Data ss:Type="String">Short</Data></Cell>
    <Cell ss:StyleID="s31"/>
    <Cell ss:StyleID="s31"/>
    <Cell ss:StyleID="s31"/>
    <Cell ss:StyleID="s31"/>
    <Cell ss:StyleID="s31"/>
    <Cell ss:StyleID="s31"><Data ss:Type="String">Fac</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s31"><Data ss:Type="String">Description</Data></Cell>
    <Cell ss:StyleID="s31"><Data ss:Type="String">Description</Data></Cell>
    <Cell ss:StyleID="s31"><Data ss:Type="String">Name</Data></Cell>
    <Cell ss:StyleID="s31"><Data ss:Type="String">City</Data></Cell>
    <Cell ss:StyleID="s31"><Data ss:Type="String">St</Data></Cell>
    <Cell ss:StyleID="s31"><Data ss:Type="String">Zip Code</Data></Cell>
    <Cell ss:StyleID="s31"><Data ss:Type="String">No</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Circ Stock</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Primary facility</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Star Newspapers Inc&#46;</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Bloomington</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">MN</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">64502</Data></Cell>
    <Cell ss:StyleID="s59"><Data ss:Type="Number">1</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Maintnance</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Secondary facility</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Facility &#35;2</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Bloomington</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">MN</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">64502</Data></Cell>
    <Cell ss:StyleID="s59"><Data ss:Type="Number">10</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s33"><Data ss:Type="String">MaintStock</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Cable Inventory</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Star Newspapers Inc&#46;</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">St&#46; Joseph</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">MO</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">64502</Data></Cell>
    <Cell ss:StyleID="s59"><Data ss:Type="Number">100</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Depot 1</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Circulation Depot 1</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Circulation Depot &#35;1</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">WSP</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">MN</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">55118</Data></Cell>
    <Cell ss:StyleID="s59"><Data ss:Type="Number">201</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Depot 2</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Circulation Depot 2</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Circulation Depot 2</Data></Cell>
    <Cell ss:StyleID="s33"/>
    <Cell ss:StyleID="s33"/>
    <Cell ss:StyleID="s33"/>
    <Cell ss:StyleID="s59"><Data ss:Type="Number">202</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Depot 3</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Circulation Depot 3</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Circulation Depot 3</Data></Cell>
    <Cell ss:StyleID="s33"/>
    <Cell ss:StyleID="s33"/>
    <Cell ss:StyleID="s33"/>
    <Cell ss:StyleID="s59"><Data ss:Type="Number">203</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <FreezePanes/>
   <FrozenNoSplit/>
   <SplitHorizontal>6</SplitHorizontal>
   <TopRowBottomPane>6</TopRowBottomPane>
   <ActivePane>2</ActivePane>
   <Panes>
    <Pane>
     <Number>3</Number>
    </Pane>
    <Pane>
     <Number>2</Number>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

Thanks for any help you can provide,
Pieter

#2

Hello @Pieter ,
It would be helpful if you could send us an example (best in our snippet tool) of the way you are loading your xml file so that we could get the result from the screenshot.
Thank you in advance.

#3

hello,

the docdata object received from the server contains the following properties:
(docdata.data contains the 64bit encoded xml file)

DeleteAfter: false
Download: false
Print: false
ShowOnScreen: true
data: "PD94bWwgdmVyc2lvbj0iMS4wIj8+DQo8P21zby1hcHBsaWNhd…
doccat: “Report Export”
extdockey: “85242726.8016114”
fileext: “xml”
filename: “Facilities_exp.xml”
rotation: 0
title: “Facilities_exp.xml - Facilities List (Facilities_exp.xml)”
type: “excel”
value: “Facilities List (Facilities_exp.xml)”
viewed: true

Here the code used to load the xml file:
(vwr is the webix spreadsheet widget)

var data = atob(docdata.data);
var blob = new Blob([data.toString()], {type: “application/excel”});
var file = new File([blob],docdata.value,{type:docdata.fileext,lastModified: Date.now()});
if (data) vwr.parse(file,“excel”);

#4

Hello @Pieter ,

SheetJS (a third-party library we use for importing/exporing Excel) perceives XML as a valid Excel file so the file is imported/parsed. However, while in the real xlsx files SheetJS can find styles and import it, in the case of xml files, unfortunately, it cannot and as a result the file is imported without styles.

What we can recommend you in this case is

  1. get data from xml file in any way (when using uploader, this step can be done in onBeforeFileAdd event)
  2. transform the data to the Spreadsheet-readable JSON format: Data Loading and Saving of UI Complex Widgets, SpreadSheet Webix Docs
  3. parse the JSON to Spreadsheet as “json”
#5

OK. Thanks for looking into it