LinuxQuestions.org - Convert from XML in excel format to Excel XLS file CPAN module

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Convert from XML in excel format to Excel XLS file CPAN module (https://www.linuxquestions.org/questions/programming-9/convert-from-xml-in-excel-format-to-excel-xls-file-cpan-module-907657/)

Perseus

10-11-2011 05:08 PM

Convert from XML in excel format to Excel XLS file CPAN module

Hi guys,

I' trying to use "Spreadsheet::WriteExcel::FromXML" to convert xml in excel format to binary xls.

I'm trying to follow and understand simple examples, and once that try with with my real xml files.
The examples I follow from CPAN are here ExcelTemplate and here Spreadsheet-WriteExcel-FromXML

I've installed both modules, Spreadsheet::WriteExcel and Spreadsheet::WriteExcel::FromXML but following
those examples don't work. I receive errors "Must define a title attribute for worksheet!" and
I'm not sure because the example they use has wroksheet name="tester"

example file.xml is:

Code:

  <workbook>

      <worksheet name="tester">

          <cell text="$HOME" />

          <cell text="$PATH" />

      </worksheet>

  </workbook>

May somebody help me with a example to follow for me to know how to do it correctly?

Much appreciated any help.

Grettings

Sergei Steshenko

10-12-2011 10:06 AM

Quote:

Originally Posted by Perseus (Post 4495891)

Hi guys,
...
I receive errors "Must define a title attribute for worksheet!"
...

What is the entity issuing the error message ? I.e. is it your Perl code or Excel while loading produced by you Perl code ?

paulsm4

10-12-2011 11:05 AM

Hi, Ophiuchus (I mean "Perseus" ;)) -

You copied this example verbatim:

http://search.cpan.org/~rbo/Excel-Te...el/Template.pm

It didn't work; it failed with this error:

http://www.unix.com/shell-programmin...t-xml-xls.html

If the author's own example doesn't work, that doesn't bode well for you trying to do anything with the module yourself, does it?

'Sounds like you ought to contact the authors ....
... or find a different example :)

Perseus

10-12-2011 06:37 PM

Quote:

Originally Posted by Sergei Steshenko (Post 4496594)

What is the entity issuing the error message ? I.e. is it your Perl code or Excel while loading produced by you Perl code ?

Hi Sergei,

I got that message in display after execute Perl code.

Quote:

Originally Posted by paulsm4

.......
...

Hi paulsm,

Yes, I've been trying to get some help in this issue without success so far. Sorry for that, just in case.

Grettings.

paulsm4

10-12-2011 07:38 PM

Hi, again -

Quote:

Q: If the author's own example doesn't work, that doesn't bode well for you trying to do anything with the module yourself, does it?

A: Yes, I've been trying to get some help in this issue without success so far.

If you can't even get the example code to run, then you're probably not going to have much luck writing your own code with that module.

One other approach, besides contacting the author, is to download the source and try to debug the module itself.

Q: Why not look for a different module?
Or, more to the point, look for TWO different modules:

1) an Excel module (to read and write arbitrary data from your spreadsheet)
2) an XML module (to parse and manipulate any/all data that happens to be XML)?

Sergei Steshenko

10-12-2011 09:43 PM

Quote:

Originally Posted by Perseus (Post 4496958)

Hi Sergei,

I got that message in display after execute Perl code.
...

So locate the part of Perl code producing the message. Maybe it's a simple error in the module, or something in your code. I.e. you need to understand what check fails and as a result produces this error message.

Perseus

10-12-2011 09:45 PM

Quote:

Originally Posted by paulsm4

One other approach, besides contacting the author, is to download the source and try to debug the module itself.

Well, I'm very beginner in Unix/Bash etc to do that :D. I would like to write my own code about that really.

Quote:

Originally Posted by paulsm4

1) an Excel module (to read and write arbitrary data from your spreadsheet)

I've made an Excel Macro to convert all xml files in folder(there are few thousands) into xls files, but
I found that Excel needs to open each file to convert it. That action will slow a lot the convertion of all files.

I've been asking Excel Experts in forums and until now I don't have a method in which Excel processes all files without open them, this is an advantage of Unix script, because is not needed to open files to process them, then execution is faster.

Quote:

Originally Posted by paulsm4

2) an XML module (to parse and manipulate any/all data that happens to be XML)?

With help of this forum I've done a shell script to process all those files to reformat into Excel XML format,
but they remain with XML extension, not in original binary Excel format.

Thanks for the help so far

Perseus

10-13-2011 01:09 AM

Quote:

Originally Posted by Sergei Steshenko (Post 4497037)

Hi Sergei,

I found a russian forum that somebody shows a xml sample file that works with the FromXML
module, the sample is below.

I could change title by name within one file in the FromXML.pm module and it works, but If I add more things to
the xml file (still being a very small and simple xml) more errors begin again. I think is really needed from the
author the structure of xml file which will be correctly processed by the module, or discover it for myself, thing that could
take me undefined time I think.

The sample which is processed correctly is:
Reference: http://forum.pskovonline.ru/index.php?showtopic=22168

Code:

<?xml version="1.0" ?>

<!DOCTYPE workbook SYSTEM "../FromXML.dtd">



<workbook>

  <worksheet title="Заголовок прайса">

    <format name="heading" bold="1" bg_color="yellow" align="center" font="Courier" size="12" italic="1" />

    <row format="heading"> <!-- Заголовок таблицы -->

      <cell>id</cell>

      <cell>товар</cell>

      <cell>цена</cell>

    </row>



    <row> <!-- Элементы -->

      <cell type="number">1111</cell>

      <cell>Имя товара</cell>

      <cell>53534</cell>

    </row>

  </worksheet>

</workbook>

Thanks for all help. Dont worry, I'll try to find how to do it or see if it better directly in Excel
even could take long time.

Grettings

Sergei Steshenko

10-13-2011 08:05 AM

Quote:

Originally Posted by Perseus (Post 4497127)

Code:

<?xml version="1.0" ?>

<!DOCTYPE workbook SYSTEM "../FromXML.dtd">



<workbook>

  <worksheet title="Заголовок прайса">

    <format name="heading" bold="1" bg_color="yellow" align="center" font="Courier" size="12" italic="1" />

    <row format="heading"> <!-- Заголовок таблицы -->

      <cell>id</cell>

      <cell>товар</cell>

      <cell>цена</cell>

    </row>



    <row> <!-- Элементы -->

      <cell type="number">1111</cell>

      <cell>Имя товара</cell>

      <cell>53534</cell>

    </row>

  </worksheet>

</workbook>

Thanks for all help. Dont worry, I'll try to find how to do it or see if it better directly in Excel
even could take long time.

Grettings

If you don't read Russian, I translate for you a portion of http://forum.pskovonline.ru/index.ph...dpost&p=427072 :

"According to the source, tag can have the following values: worksheet, row, cell, format, range, margins"

- don't know whether it's of any help.

Perseus

10-14-2011 12:39 PM

Quote:

Originally Posted by Sergei Steshenko (Post 4497410)

Hi Sergei,

Many thanks for do that, certainly it helps me what you translated for me.

Much appreciated.

Grettings

All times are GMT -5. The time now is 05:58 AM.