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