Reading in Delphi XML generated in Excel

Question

I am generating an XML by Excel itself (file type XML Sheet 2003).

The generated file has the following XML data standard:

<Row>
<Cell><Data ss:Type="String">Williams</Data></Cell>
<Cell ss:StyleID="s63"><Datass:Type="Number">10644</Data></Cell>
<Cell><Data ss:Type="String">UK</Data></Cell>
<Cell><Data ss:Type="String">Qtr 2</Data></Cell>
</Row>

Creating a project in Delphi, using a Dataset and a Datasource, and making the default configuration, Delphi cannot read the fields of this XML, as occurs when we have a file in the pattern below. What to do to convert the Excel spreadsheet into an XML standard that Delphi understands, or how to interpret this XML standard?

<ROW NOME="PAMELA" DT_NASCIMENTO="19521002" DT_CADASTRO="20031020" TELEFONE="2548874"
CELULAR="93712225" EMAIL="[email protected]"/>

Answer 1

This model referred to

<ROW NOME="PAMELA" DT_NASCIMENTO="19521002" DT_CADASTRO="20031020" TELEFONE="2548874"
CELULAR="93712225" EMAIL="[email protected]"/>

is the Clientdataset data schema and differs from the above-mentioned model, which comes from Excel.

To read the Excel Schema, you must use a XMLDocument and not the TClientDataSet

As in the following example:

var
  XMLDocument: IXMLDocument;
  MainNode, RowNode: TXMLNode;
  I, J: Integer;
begin
  XMLDocument := TXMLDocument.Create(nil);
  try
    XMLDocument.FileName := 'C:\Caminho\Para\Seu\Arquivo.xml';
    XMLDocument.Active := True;

    MainNode := XMLDocument.DocumentElement;

    for I := 0 to MainNode.ChildNodes.Count - 1 do
    begin
      RowNode := MainNode.ChildNodes[I];

      for J := 0 to RowNode.ChildNodes.Count - 1 do
      begin
        //Cada item aqui é uma célula e vc pode obter o valor
      end;
    end;

    XMLDocument.Active := False;
  finally
    XMLDocument := nil; //Ele é uma interface e é liberado automaticamente
  end;

Answer 2

I was able to make Delphi read the xml generated by Excel correctly:

vXMLDoc := TXMLDocument.Create(self);
vXMLDoc.LoadFromFile('Exemplo.xml');

J := vXMLDoc.DocumentElement.ChildNodes.Count;
Memo1.Clear;

for K := 0 to j -1 do
begin
  NodeRec := vXMLDoc.DocumentElement.ChildNodes[K];  
  Memo1.Lines.Add(NodeRec.ChildNodes[0].text + ' -' + 
  NodeRec.ChildNodes[1].text  + '-' 
  + NodeRec.ChildNodes[2].text+ ' -' + NodeRec.ChildNodes[3].text);     
end;

Answer 3

You can use the utility Xmlmapper for map the data from the XML file to a Tclientdataset. From there you can import the data into Tclientdataset and manipulate.