Using MS Excel XML files

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Using MS Excel XML files

Mark Arnold

Hello,

I am using dbunit with xlsx files for both loading test data into tables (input data) and checking results (expected data).

I prefer xlsx over csv/xml and other text-based formats because it allows me to make my data files a lot more comprehensible by using things like formatting (colors, etc), comments, etc.
However, what I lose is being able to compare different versions of a file's history in a version management system (CVS/git/SVN/etc)

A way to get both would be by saving the Excel file in MS Excel's XML format.

Is it possible to load those, or can Excel files only be used in the binary XLS/XLSX formats?

Thank you,

Mark


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Using MS Excel XML files

Barnabas Davoti
Hi Mark,

I think it's hard to justify "the extra trouble" with Excel. I'd only go
for that if it's a must have end user requirement. It's not fun to
convert the XLSX's XML format. It's complex, lack of semantics and quite
low level.

You can add comments to XML and if you really really want something
higher level - instead of using Excel - design a semantic XML grammar,
which will be your editorial data format, use a proper XML editor (like
Oxygen), add stylesheet, so you can add colors (etc) to your data. Then
you get a high level editorial environment, control over the data via
XML validation and it'll be very easy to convert this XML to DBUnit XML
via XSLT for ex.

BR.

Barna


On 2017-06-26 18:08, Mark Arnold wrote:

> Hello,
>
> I am using dbunit with xlsx files for both loading test data into tables
> (input data) and checking results (expected data).
>
> I prefer xlsx over csv/xml and other text-based formats because it
> allows me to make my data files a lot more comprehensible by using
> things like formatting (colors, etc), comments, etc.
> However, what I lose is being able to compare different versions of a
> file's history in a version management system (CVS/git/SVN/etc)
>
> A way to get both would be by saving the Excel file in MS Excel's XML
> format.
>
> Is it possible to load those, or can Excel files only be used in the
> binary XLS/XLSX formats?
>
> Thank you,
>
> Mark
>
>
>
> ------------------------------------------------------------------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
>
>
>
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Using MS Excel XML files

Ryan Eberly
Barna,
  I hear your concerns.  And I agree the parsing the xlsx's would be non-trivial.   However, Apache POI does that for you quite nicely.   We've implemented some excel spreadsheet integrations in-house quite easily.


FWIW

- Ryan



On Wed, Jun 28, 2017 at 3:56 AM, Barnabas Davoti <[hidden email]> wrote:
Hi Mark,

I think it's hard to justify "the extra trouble" with Excel. I'd only go for that if it's a must have end user requirement. It's not fun to convert the XLSX's XML format. It's complex, lack of semantics and quite low level.

You can add comments to XML and if you really really want something higher level - instead of using Excel - design a semantic XML grammar, which will be your editorial data format, use a proper XML editor (like Oxygen), add stylesheet, so you can add colors (etc) to your data. Then you get a high level editorial environment, control over the data via XML validation and it'll be very easy to convert this XML to DBUnit XML via XSLT for ex.

BR.

Barna



On 2017-06-26 18:08, Mark Arnold wrote:
Hello,

I am using dbunit with xlsx files for both loading test data into tables
(input data) and checking results (expected data).

I prefer xlsx over csv/xml and other text-based formats because it
allows me to make my data files a lot more comprehensible by using
things like formatting (colors, etc), comments, etc.
However, what I lose is being able to compare different versions of a
file's history in a version management system (CVS/git/SVN/etc)

A way to get both would be by saving the Excel file in MS Excel's XML
format.

Is it possible to load those, or can Excel files only be used in the
binary XLS/XLSX formats?

Thank you,

Mark



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot



_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Using MS Excel XML files

Mark Arnold
So it sounds to me like
a) POI can already process MS Excel XML files, and
b) dbUnit is already using POI to load binary XLSX files

That sounds like it should be relatively easy to get dbUnit to read MS
Excel XML files - at least assuming that POI's API does make it
transparent to whoever is using it if the processed file is XML or
binary XLSX.
Maybe it is already possible and I just dont know how?

Or am I completely wrong?

Thanks,

Mark


On 06/28/2017 04:58 AM, Ryan Eberly wrote:

> Barna,
>   I hear your concerns.  And I agree the parsing the xlsx's would be
> non-trivial. However, Apache POI does that for you quite nicely.  
> We've implemented some excel spreadsheet integrations in-house quite
> easily.
>
> https://poi.apache.org/spreadsheet/index.html
>
> FWIW
>
> - Ryan
>
>
>
> On Wed, Jun 28, 2017 at 3:56 AM, Barnabas Davoti <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     Hi Mark,
>
>     I think it's hard to justify "the extra trouble" with Excel. I'd
>     only go for that if it's a must have end user requirement. It's
>     not fun to convert the XLSX's XML format. It's complex, lack of
>     semantics and quite low level.
>
>     You can add comments to XML and if you really really want
>     something higher level - instead of using Excel - design a
>     semantic XML grammar, which will be your editorial data format,
>     use a proper XML editor (like Oxygen), add stylesheet, so you can
>     add colors (etc) to your data. Then you get a high level editorial
>     environment, control over the data via XML validation and it'll be
>     very easy to convert this XML to DBUnit XML via XSLT for ex.
>
>     BR.
>
>     Barna
>
>
>
>     On 2017-06-26 18:08, Mark Arnold wrote:
>
>         Hello,
>
>         I am using dbunit with xlsx files for both loading test data
>         into tables
>         (input data) and checking results (expected data).
>
>         I prefer xlsx over csv/xml and other text-based formats because it
>         allows me to make my data files a lot more comprehensible by using
>         things like formatting (colors, etc), comments, etc.
>         However, what I lose is being able to compare different
>         versions of a
>         file's history in a version management system (CVS/git/SVN/etc)
>
>         A way to get both would be by saving the Excel file in MS
>         Excel's XML
>         format.
>
>         Is it possible to load those, or can Excel files only be used
>         in the
>         binary XLS/XLSX formats?
>
>         Thank you,
>
>         Mark
>
>
>
>         ------------------------------------------------------------------------------
>         Check out the vibrant tech community on one of the world's most
>         engaging tech sites, Slashdot.org! http://sdm.link/slashdot
>
>
>
>         _______________________________________________
>         dbunit-user mailing list
>         [hidden email]
>         <mailto:[hidden email]>
>         https://lists.sourceforge.net/lists/listinfo/dbunit-user
>         <https://lists.sourceforge.net/lists/listinfo/dbunit-user>
>
>
>     ------------------------------------------------------------------------------
>     Check out the vibrant tech community on one of the world's most
>     engaging tech sites, Slashdot.org! http://sdm.link/slashdot
>     _______________________________________________
>     dbunit-user mailing list
>     [hidden email]
>     <mailto:[hidden email]>
>     https://lists.sourceforge.net/lists/listinfo/dbunit-user
>     <https://lists.sourceforge.net/lists/listinfo/dbunit-user>
>
>
>
>
> ------------------------------------------------------------------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
>
>
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Using MS Excel XML files

Ryan Eberly
Sounds right to me.

On Wed, Jun 28, 2017 at 1:36 PM, Mark Arnold <[hidden email]> wrote:
So it sounds to me like
a) POI can already process MS Excel XML files, and
b) dbUnit is already using POI to load binary XLSX files

That sounds like it should be relatively easy to get dbUnit to read MS Excel XML files - at least assuming that POI's API does make it transparent to whoever is using it if the processed file is XML or binary XLSX.
Maybe it is already possible and I just dont know how?

Or am I completely wrong?

Thanks,

Mark


On 06/28/2017 04:58 AM, Ryan Eberly wrote:
Barna,
  I hear your concerns.  And I agree the parsing the xlsx's would be non-trivial. However, Apache POI does that for you quite nicely.   We've implemented some excel spreadsheet integrations in-house quite easily.

https://poi.apache.org/spreadsheet/index.html

FWIW

- Ryan



On Wed, Jun 28, 2017 at 3:56 AM, Barnabas Davoti <[hidden email] <mailto:[hidden email]>> wrote:

    Hi Mark,

    I think it's hard to justify "the extra trouble" with Excel. I'd
    only go for that if it's a must have end user requirement. It's
    not fun to convert the XLSX's XML format. It's complex, lack of
    semantics and quite low level.

    You can add comments to XML and if you really really want
    something higher level - instead of using Excel - design a
    semantic XML grammar, which will be your editorial data format,
    use a proper XML editor (like Oxygen), add stylesheet, so you can
    add colors (etc) to your data. Then you get a high level editorial
    environment, control over the data via XML validation and it'll be
    very easy to convert this XML to DBUnit XML via XSLT for ex.

    BR.

    Barna



    On 2017-06-26 18:08, Mark Arnold wrote:

        Hello,

        I am using dbunit with xlsx files for both loading test data
        into tables
        (input data) and checking results (expected data).

        I prefer xlsx over csv/xml and other text-based formats because it
        allows me to make my data files a lot more comprehensible by using
        things like formatting (colors, etc), comments, etc.
        However, what I lose is being able to compare different
        versions of a
        file's history in a version management system (CVS/git/SVN/etc)

        A way to get both would be by saving the Excel file in MS
        Excel's XML
        format.

        Is it possible to load those, or can Excel files only be used
        in the
        binary XLS/XLSX formats?

        Thank you,

        Mark



        ------------------------------------------------------------------------------
        Check out the vibrant tech community on one of the world's most
        engaging tech sites, Slashdot.org! http://sdm.link/slashdot



        _______________________________________________
        dbunit-user mailing list
        [hidden email]
        <mailto:[hidden email]>
        https://lists.sourceforge.net/lists/listinfo/dbunit-user
        <https://lists.sourceforge.net/lists/listinfo/dbunit-user>


    ------------------------------------------------------------------------------
    Check out the vibrant tech community on one of the world's most
    engaging tech sites, Slashdot.org! http://sdm.link/slashdot
    _______________________________________________
    dbunit-user mailing list
    [hidden email]
    <mailto:[hidden email]>
    https://lists.sourceforge.net/lists/listinfo/dbunit-user
    <https://lists.sourceforge.net/lists/listinfo/dbunit-user>




------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Using MS Excel XML files

Jeff Jensen-2
If you make it work, please create a feature request with merge request to add it to dbUnit.


On Wed, Jun 28, 2017 at 8:05 PM, Ryan Eberly <[hidden email]> wrote:
Sounds right to me.

On Wed, Jun 28, 2017 at 1:36 PM, Mark Arnold <[hidden email]> wrote:
So it sounds to me like
a) POI can already process MS Excel XML files, and
b) dbUnit is already using POI to load binary XLSX files

That sounds like it should be relatively easy to get dbUnit to read MS Excel XML files - at least assuming that POI's API does make it transparent to whoever is using it if the processed file is XML or binary XLSX.
Maybe it is already possible and I just dont know how?

Or am I completely wrong?

Thanks,

Mark


On 06/28/2017 04:58 AM, Ryan Eberly wrote:
Barna,
  I hear your concerns.  And I agree the parsing the xlsx's would be non-trivial. However, Apache POI does that for you quite nicely.   We've implemented some excel spreadsheet integrations in-house quite easily.

https://poi.apache.org/spreadsheet/index.html

FWIW

- Ryan



On Wed, Jun 28, 2017 at 3:56 AM, Barnabas Davoti <[hidden email] <mailto:[hidden email]>> wrote:

    Hi Mark,

    I think it's hard to justify "the extra trouble" with Excel. I'd
    only go for that if it's a must have end user requirement. It's
    not fun to convert the XLSX's XML format. It's complex, lack of
    semantics and quite low level.

    You can add comments to XML and if you really really want
    something higher level - instead of using Excel - design a
    semantic XML grammar, which will be your editorial data format,
    use a proper XML editor (like Oxygen), add stylesheet, so you can
    add colors (etc) to your data. Then you get a high level editorial
    environment, control over the data via XML validation and it'll be
    very easy to convert this XML to DBUnit XML via XSLT for ex.

    BR.

    Barna



    On 2017-06-26 18:08, Mark Arnold wrote:

        Hello,

        I am using dbunit with xlsx files for both loading test data
        into tables
        (input data) and checking results (expected data).

        I prefer xlsx over csv/xml and other text-based formats because it
        allows me to make my data files a lot more comprehensible by using
        things like formatting (colors, etc), comments, etc.
        However, what I lose is being able to compare different
        versions of a
        file's history in a version management system (CVS/git/SVN/etc)

        A way to get both would be by saving the Excel file in MS
        Excel's XML
        format.

        Is it possible to load those, or can Excel files only be used
        in the
        binary XLS/XLSX formats?

        Thank you,

        Mark



        ------------------------------------------------------------------------------
        Check out the vibrant tech community on one of the world's most
        engaging tech sites, Slashdot.org! http://sdm.link/slashdot



        _______________________________________________
        dbunit-user mailing list
        [hidden email]
        <mailto:[hidden email]>
        https://lists.sourceforge.net/lists/listinfo/dbunit-user
        <https://lists.sourceforge.net/lists/listinfo/dbunit-user>


    ------------------------------------------------------------------------------
    Check out the vibrant tech community on one of the world's most
    engaging tech sites, Slashdot.org! http://sdm.link/slashdot
    _______________________________________________
    dbunit-user mailing list
    [hidden email]
    <mailto:[hidden email]>
    https://lists.sourceforge.net/lists/listinfo/dbunit-user
    <https://lists.sourceforge.net/lists/listinfo/dbunit-user>




------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user