Spreadsheet file handler creation

[Leila Mekika]

Hello everyone,

We had to do a spreadsheet file handler for a customer project and
decided to use current csv renderer to generate a datafile, then convert
it to xls with Workbook library.
It turns out to be very long when extracted file reaches a big load of
data...

In order to resolve this, i am currently trying to make a new handler
that generate a html table file which can be imported as spreadsheet in
most office software suite (i've made a JIRA
<https://issues.apache.org/jira/browse/OFBIZ-6931>). Cells are formatted
via css (found this
<http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting>).
The generated file is an html file with application/xls response
content-type, since i didnt' find any "html to ods" or "html to xls"
converter. I think it's because it can be done from the software suite
interface, but i was wondering: does anyone know of a library that can
transform html to spreadsheet file ?

Does anyone has another suggestion than the html generation to achieve a
fast spreadsheet file rendering ?
Thanks for any idea or advice

Leila Mekika

[Shi Jinghai-3]

I think POI is the right way, you can handle an excel template file directly without any conversion.

It's in my plan (maybe in May) to contribute a PriCat component which can import/export excel file for EDI purpose.

Kind Regards,

Shi Jinghai


-----邮件原件-----
发件人: Leila [mailto:[hidden email]]
发送时间: 2016年3月18日 18:05
收件人: [hidden email]
主题: Spreadsheet file handler creation

Hello everyone,

We had to do a spreadsheet file handler for a customer project and
decided to use current csv renderer to generate a datafile, then convert
it to xls with Workbook library.
It turns out to be very long when extracted file reaches a big load of
data...

In order to resolve this, i am currently trying to make a new handler
that generate a html table file which can be imported as spreadsheet in
most office software suite (i've made a JIRA
<https://issues.apache.org/jira/browse/OFBIZ-6931>). Cells are formatted
via css (found this
<http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting>).
The generated file is an html file with application/xls response
content-type, since i didnt' find any "html to ods" or "html to xls"
converter. I think it's because it can be done from the software suite
interface, but i was wondering: does anyone know of a library that can
transform html to spreadsheet file ?

Does anyone has another suggestion than the html generation to achieve a
fast spreadsheet file rendering ?
Thanks for any idea or advice

Leila Mekika

[Ron Wheeler]

ADTransform will do most (perhaps all) of what you want.

It will read a spreadsheet (or a CSV file), let you manipulate the data,
verify it and then produce a set of XML files.
I have done a partial script to take multiple spreadsheets and create
OFBiz XML entity files.

The OFBiz XML structure is fairly complex and to load a simple
spreadsheet  containing a list of customers with their names, addresses,
email, phone numbers starts with a pretty simple spreadsheet but ends up
with many, many XML files. Spreadsheets with Customers, Customer Service
employees and SIC codes generate 19 XML files!


Ron


On 18/03/2016 6:05 AM, Leila wrote:

> Hello everyone,
>
> We had to do a spreadsheet file handler for a customer project and
> decided to use current csv renderer to generate a datafile, then
> convert it to xls with Workbook library.
> It turns out to be very long when extracted file reaches a big load of
> data...
>
> In order to resolve this, i am currently trying to make a new handler
> that generate a html table file which can be imported as spreadsheet
> in most office software suite (i've made a JIRA
> <https://issues.apache.org/jira/browse/OFBIZ-6931>). Cells are
> formatted via css (found this
> <http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting>).
> The generated file is an html file with application/xls response
> content-type, since i didnt' find any "html to ods" or "html to xls"
> converter. I think it's because it can be done from the software suite
> interface, but i was wondering: does anyone know of a library that can
> transform html to spreadsheet file ?
>
> Does anyone has another suggestion than the html generation to achieve
> a fast spreadsheet file rendering ?
> Thanks for any idea or advice
>
> Leila Mekika
>

--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102

[Leila Mekika]

Thanks Shi for your answer,

Sorry i didn't express myself well: in fact i was refering to POI when i
said that we use to convert the csv to xls with workbook library.
But we've got memory issues with some files so i try to find an other
way :-)

Leila

Le 19/03/2016 01:31, Shi Jinghai a écrit :

> I think POI is the right way, you can handle an excel template file directly without any conversion.
>
> It's in my plan (maybe in May) to contribute a PriCat component which can import/export excel file for EDI purpose.
>
> Kind Regards,
>
> Shi Jinghai
>
>
> -----邮件原件-----
> 发件人: Leila [mailto:[hidden email]]
> 发送时间: 2016年3月18日 18:05
> 收件人:[hidden email]
> 主题: Spreadsheet file handler creation
>
> Hello everyone,
>
> We had to do a spreadsheet file handler for a customer project and
> decided to use current csv renderer to generate a datafile, then convert
> it to xls with Workbook library.
> It turns out to be very long when extracted file reaches a big load of
> data...
>
> In order to resolve this, i am currently trying to make a new handler
> that generate a html table file which can be imported as spreadsheet in
> most office software suite (i've made a JIRA
> <https://issues.apache.org/jira/browse/OFBIZ-6931>). Cells are formatted
> via css (found this
> <http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting>).
> The generated file is an html file with application/xls response
> content-type, since i didnt' find any "html to ods" or "html to xls"
> converter. I think it's because it can be done from the software suite
> interface, but i was wondering: does anyone know of a library that can
> transform html to spreadsheet file ?
>
> Does anyone has another suggestion than the html generation to achieve a
> fast spreadsheet file rendering ?
> Thanks for any idea or advice
>
> Leila Mekika
>
>

[Leila Mekika]

Hello Ron,

i didn't know about ADTransform and will look at it. Actually, my goal
is to make an export file and it seems to allow spreadsheet output.
But i already had the need to make a spreadsheet to xml conversion for
import purpose, so i'll keep that in mind (and your post in adtransform
website)
Thanks !

Leila

Le 19/03/2016 02:38, Ron Wheeler a écrit :

> ADTransform will do most (perhaps all) of what you want.
>
> It will read a spreadsheet (or a CSV file), let you manipulate the
> data, verify it and then produce a set of XML files.
> I have done a partial script to take multiple spreadsheets and create
> OFBiz XML entity files.
>
> The OFBiz XML structure is fairly complex and to load a simple
> spreadsheet  containing a list of customers with their names,
> addresses, email, phone numbers starts with a pretty simple
> spreadsheet but ends up with many, many XML files. Spreadsheets with
> Customers, Customer Service employees and SIC codes generate 19 XML
> files!
>
>
> Ron
>
>
> On 18/03/2016 6:05 AM, Leila wrote:
>> Hello everyone,
>>
>> We had to do a spreadsheet file handler for a customer project and
>> decided to use current csv renderer to generate a datafile, then
>> convert it to xls with Workbook library.
>> It turns out to be very long when extracted file reaches a big load
>> of data...
>>
>> In order to resolve this, i am currently trying to make a new handler
>> that generate a html table file which can be imported as spreadsheet
>> in most office software suite (i've made a JIRA
>> <https://issues.apache.org/jira/browse/OFBIZ-6931>). Cells are
>> formatted via css (found this
>> <http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting>).
>> The generated file is an html file with application/xls response
>> content-type, since i didnt' find any "html to ods" or "html to xls"
>> converter. I think it's because it can be done from the software
>> suite interface, but i was wondering: does anyone know of a library
>> that can transform html to spreadsheet file ?
>>
>> Does anyone has another suggestion than the html generation to
>> achieve a fast spreadsheet file rendering ?
>> Thanks for any idea or advice
>>
>> Leila Mekika
>>
>
>

[Ron Wheeler]

It can also output HTML files fully annotated for CSS styling.

It also can create reports using JasperReport.

It can read CSV (with and without headers) and Excel files.

It can be extended by plug-ins to read anything that you can parse into
row and columns
Webservice and SQL connections are possible sources for data.


Ron




On 21/03/2016 9:16 AM, Leila Mekika wrote:

> Hello Ron,
>
> i didn't know about ADTransform and will look at it. Actually, my goal
> is to make an export file and it seems to allow spreadsheet output.
> But i already had the need to make a spreadsheet to xml conversion for
> import purpose, so i'll keep that in mind (and your post in
> adtransform website)
> Thanks !
>
> Leila
>
> Le 19/03/2016 02:38, Ron Wheeler a écrit :
>> ADTransform will do most (perhaps all) of what you want.
>>
>> It will read a spreadsheet (or a CSV file), let you manipulate the
>> data, verify it and then produce a set of XML files.
>> I have done a partial script to take multiple spreadsheets and create
>> OFBiz XML entity files.
>>
>> The OFBiz XML structure is fairly complex and to load a simple
>> spreadsheet  containing a list of customers with their names,
>> addresses, email, phone numbers starts with a pretty simple
>> spreadsheet but ends up with many, many XML files. Spreadsheets with
>> Customers, Customer Service employees and SIC codes generate 19 XML
>> files!
>>
>>
>> Ron
>>
>>
>> On 18/03/2016 6:05 AM, Leila wrote:
>>> Hello everyone,
>>>
>>> We had to do a spreadsheet file handler for a customer project and
>>> decided to use current csv renderer to generate a datafile, then
>>> convert it to xls with Workbook library.
>>> It turns out to be very long when extracted file reaches a big load
>>> of data...
>>>
>>> In order to resolve this, i am currently trying to make a new
>>> handler that generate a html table file which can be imported as
>>> spreadsheet in most office software suite (i've made a JIRA
>>> <https://issues.apache.org/jira/browse/OFBIZ-6931>). Cells are
>>> formatted via css (found this
>>> <http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting>).
>>> The generated file is an html file with application/xls response
>>> content-type, since i didnt' find any "html to ods" or "html to xls"
>>> converter. I think it's because it can be done from the software
>>> suite interface, but i was wondering: does anyone know of a library
>>> that can transform html to spreadsheet file ?
>>>
>>> Does anyone has another suggestion than the html generation to
>>> achieve a fast spreadsheet file rendering ?
>>> Thanks for any idea or advice
>>>
>>> Leila Mekika
>>>
>>
>>
>
>

--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102

[Nicolas Malin-2]

Hi Ron, I reviewed the proposal on Jira
https://issues.apache.org/jira/browse/OFBIZ-6931

It's simple and faster, can you check it if you have a better idea with
an other externe tools to send a flow on spread software through the
screen engine ?

Nicolas

Le 21/03/2016 14:56, Ron Wheeler a écrit :

> It can also output HTML files fully annotated for CSS styling.
>
> It also can create reports using JasperReport.
>
> It can read CSV (with and without headers) and Excel files.
>
> It can be extended by plug-ins to read anything that you can parse
> into row and columns
> Webservice and SQL connections are possible sources for data.
>
>
> Ron
>
>
>
>
> On 21/03/2016 9:16 AM, Leila Mekika wrote:
>> Hello Ron,
>>
>> i didn't know about ADTransform and will look at it. Actually, my
>> goal is to make an export file and it seems to allow spreadsheet output.
>> But i already had the need to make a spreadsheet to xml conversion
>> for import purpose, so i'll keep that in mind (and your post in
>> adtransform website)
>> Thanks !
>>
>> Leila
>>
>> Le 19/03/2016 02:38, Ron Wheeler a écrit :
>>> ADTransform will do most (perhaps all) of what you want.
>>>
>>> It will read a spreadsheet (or a CSV file), let you manipulate the
>>> data, verify it and then produce a set of XML files.
>>> I have done a partial script to take multiple spreadsheets and
>>> create OFBiz XML entity files.
>>>
>>> The OFBiz XML structure is fairly complex and to load a simple
>>> spreadsheet  containing a list of customers with their names,
>>> addresses, email, phone numbers starts with a pretty simple
>>> spreadsheet but ends up with many, many XML files. Spreadsheets with
>>> Customers, Customer Service employees and SIC codes generate 19 XML
>>> files!
>>>
>>>
>>> Ron
>>>
>>>
>>> On 18/03/2016 6:05 AM, Leila wrote:
>>>> Hello everyone,
>>>>
>>>> We had to do a spreadsheet file handler for a customer project and
>>>> decided to use current csv renderer to generate a datafile, then
>>>> convert it to xls with Workbook library.
>>>> It turns out to be very long when extracted file reaches a big load
>>>> of data...
>>>>
>>>> In order to resolve this, i am currently trying to make a new
>>>> handler that generate a html table file which can be imported as
>>>> spreadsheet in most office software suite (i've made a JIRA
>>>> <https://issues.apache.org/jira/browse/OFBIZ-6931>). Cells are
>>>> formatted via css (found this
>>>> <http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting>).
>>>> The generated file is an html file with application/xls response
>>>> content-type, since i didnt' find any "html to ods" or "html to
>>>> xls" converter. I think it's because it can be done from the
>>>> software suite interface, but i was wondering: does anyone know of
>>>> a library that can transform html to spreadsheet file ?
>>>>
>>>> Does anyone has another suggestion than the html generation to
>>>> achieve a fast spreadsheet file rendering ?
>>>> Thanks for any idea or advice
>>>>
>>>> Leila Mekika
>>>>
>>>
>>>
>>
>>
>
>

[Ron Wheeler]

I focused on creating the XML since it is a big problem to transfer data
exported  from other systems.

The data in the XML files that loads the OFBiz demos and base systems is
pretty sparse and I am sure that a lot of this is caused by the
difficulty of taking tabular data in a spreadsheet and generating all of
the entities.

On the output side, I would investigate using ADTransform to execute
queries using the OFBiz WebService interface to produce spreadsheets,
html reports, visualizations.
I have not looked at the OFBiz webservice interface and it may be that
several services might have to be used to acquire all of the data
required to fill in a spreadsheet.

ADTransform can do table lookups to merge data.

For example, if I wanted to have a spreadsheet with customer info as
well as the name and contact info for the sales rep,
it would require 4 steps:
- webservice call to extract the sales reps, assuming that OfBiz has a
webservice that can be queried to get what you want.
- webservice call to extract the customers, assuming that OfBiz has a
webservice that can be queried to get what you want.
- lookup operation to produce the denormalized table by looking up each
sales person and copying the desired fields to the customer.
This is a single operation that requires the name of the "datastore"
holding the master file(customer), the name of the datastore holding the
lookup table(salesrep), the keys to match (salesrep name or id) and the
list of columns in the salesrep datastore to copy each customer row.
- output the extended customer table to Excel or CSV (both are supported)

I would probably produce an HTML report as well just to have an easy way
to look at the data.
If there are other mappings to convert codes to meaningful information
that is possible.
Filtering is available to split data based on the contents of certain
fields (list of customers in France separated from international)
Fields can be concatenated, sequence numbers can be added.

I have not written the Webservice plug-in but it should be pretty
straightforward.
- open the connection,
- extract the data into rows and fields
- put each row in a the datastore mapping the webservice field names to
the desired names in the datastore (if they are not the same).

This would follow the same pattern as the plug-in that reads CSV files
without all of the screwing about to skip rows before and after optional
headers, etc.

ADTransform is a standalone program that can be run as a batch job or
spawned as a service.
There is an API for writing plug-ins.
Plug-ins can be added without affecting the core ADTransform jar files.
Plug-ins are referenced through Spring in the workflow configuration so
you can use any plug-in that is in a jar file in the ADTransform bin folder.
Plug-in exist now that can manipulate local files and directories and
use ftp to move data to and from external systems.
There is an e-mail plug-in.

It produces an error log and audit trail that are written to be read by
a non-programmer. "Duplicate customer number 1345 found in rows 7 and 456".

Does this help?

Ron



On 14/04/2016 3:27 PM, Nicolas Malin wrote:

> Hi Ron, I reviewed the proposal on Jira
> https://issues.apache.org/jira/browse/OFBIZ-6931
>
> It's simple and faster, can you check it if you have a better idea
> with an other externe tools to send a flow on spread software through
> the screen engine ?
>
> Nicolas
>
> Le 21/03/2016 14:56, Ron Wheeler a écrit :
>> It can also output HTML files fully annotated for CSS styling.
>>
>> It also can create reports using JasperReport.
>>
>> It can read CSV (with and without headers) and Excel files.
>>
>> It can be extended by plug-ins to read anything that you can parse
>> into row and columns
>> Webservice and SQL connections are possible sources for data.
>>
>>
>> Ron
>>
>>
>>
>>
>> On 21/03/2016 9:16 AM, Leila Mekika wrote:
>>> Hello Ron,
>>>
>>> i didn't know about ADTransform and will look at it. Actually, my
>>> goal is to make an export file and it seems to allow spreadsheet
>>> output.
>>> But i already had the need to make a spreadsheet to xml conversion
>>> for import purpose, so i'll keep that in mind (and your post in
>>> adtransform website)
>>> Thanks !
>>>
>>> Leila
>>>
>>> Le 19/03/2016 02:38, Ron Wheeler a écrit :
>>>> ADTransform will do most (perhaps all) of what you want.
>>>>
>>>> It will read a spreadsheet (or a CSV file), let you manipulate the
>>>> data, verify it and then produce a set of XML files.
>>>> I have done a partial script to take multiple spreadsheets and
>>>> create OFBiz XML entity files.
>>>>
>>>> The OFBiz XML structure is fairly complex and to load a simple
>>>> spreadsheet  containing a list of customers with their names,
>>>> addresses, email, phone numbers starts with a pretty simple
>>>> spreadsheet but ends up with many, many XML files. Spreadsheets
>>>> with Customers, Customer Service employees and SIC codes generate
>>>> 19 XML files!
>>>>
>>>>
>>>> Ron
>>>>
>>>>
>>>> On 18/03/2016 6:05 AM, Leila wrote:
>>>>> Hello everyone,
>>>>>
>>>>> We had to do a spreadsheet file handler for a customer project and
>>>>> decided to use current csv renderer to generate a datafile, then
>>>>> convert it to xls with Workbook library.
>>>>> It turns out to be very long when extracted file reaches a big
>>>>> load of data...
>>>>>
>>>>> In order to resolve this, i am currently trying to make a new
>>>>> handler that generate a html table file which can be imported as
>>>>> spreadsheet in most office software suite (i've made a JIRA
>>>>> <https://issues.apache.org/jira/browse/OFBIZ-6931>). Cells are
>>>>> formatted via css (found this
>>>>> <http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting>).
>>>>> The generated file is an html file with application/xls response
>>>>> content-type, since i didnt' find any "html to ods" or "html to
>>>>> xls" converter. I think it's because it can be done from the
>>>>> software suite interface, but i was wondering: does anyone know of
>>>>> a library that can transform html to spreadsheet file ?
>>>>>
>>>>> Does anyone has another suggestion than the html generation to
>>>>> achieve a fast spreadsheet file rendering ?
>>>>> Thanks for any idea or advice
>>>>>
>>>>> Leila Mekika
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102