Re: Spreadsheet file handler creation

[Nicolas Malin <ni...@nereide.fr>]

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
>>>>
>>>
>>>
>>
>>
>
>

Re: Spreadsheet file handler creation

[Ron Wheeler <rw...@artifact-software.com>]

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: rwheeler@artifact-software.com
skype: ronaldmwheeler
phone: 866-970-2435, ext 102