Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Code & Utilities

User login

Frontpage Sponsor

Main

Google search


Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
33%
Installation Wizard into new VRC
39%
Manual into existing VRC
6%
Manual into new VRC
22%
Total votes: 49

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #16  
Old 29th July 2008, 15:36
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,096
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
I believe the program limits the number of rows exported to the excel limit(65536). On my version as you typed in the index values to extract it displayed the number of rows that would be exported - but it still limited you to 65536.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #17  
Old 29th August 2008, 11:26
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
Francesco,

Nice work! I created my "own version" based on your code, along with some adjustments/optimalizations. Still want to optimize it a bit, for instance allowing zoom on the selection fields, counting number of records to be selected on the fly, allowing entering local patch on form, etc.

It works fine, only thing is: when I export from any table only a few records, everything goes fine, but as soon as the selection is a bit larger, I get the error 'Problems During Load - Table' when Excel tries to open the file. It seems related to the amount of charachters in the file, rather than the number of records exported. In other words if each record contains only 1 field, it works fine upto approx. 500 records, yet if each record contains 40 fields, it will work fine only for approx. 5 records...

Anyone an idea what causes this? It must be some Excel-XML tagging that goes wrong...

Thanks in advance!


Regards,
Eli Nager
__________________
Member of Baan Board

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #18  
Old 18th September 2008, 10:01
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
I see nobody "bit" yet on my question in my previous post (error 'problems during load - table' when Excel tries to open the file...), and I didn't yet have the time to look for the solution.

I optimized the session a bit more here and there, for instance enabling it to work for Tools tables, fixed some beauty issues, etc. My next planned optimization is to enable selecting on dates, both num and utc, and enums. Both are now only possible by entering the numeric value, which isn't very practical... Also zooming from selection fields would be great, and in general I would love to have the dynamic selection fields on the form assigned with the correct domain attributes like field length, input length, alligning, etc.

In other words, when entering a selection of areas, which is a string of 3 positions, I want the field to show 3 positions and also allow only three, activate the zoom on areas. When entering a Tools Patch which is a right aligned string, I want the input to be right aligned as well. When entering an enum, it would be great to be able to select the desired from-to values from an enum drop down list (although I doubt if this is possible), and when selecting a date, the field should behave like a date field, i.e. allowing to enter '01092008' or 01 for first day of month or '+10' for +10 days, etc. Also the zoom to the BaaN calendar will be active...

After my next 'bored' night, I will let you know what I achieved...


Regards,
En
__________________
Member of Baan Board

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #19  
Old 18th September 2008, 20:36
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,061
NPRao will become famous soon enough
Baan: Baan 4-5,5.2(Reger),LN-6.1,Infor LN-10.x - DB: Oracle,MS-SQL - OS: HPUX, Linux, Windows
En,

I am working on my version which is more in a DLL mode to simplify for our usage and to make it generic. I faced similar issues when the Excel program could not open the file and found it to be usually tag issues.

I am referring to the links -

Microsoft Office XML formats

Save to SpreadsheetML and Extracting Data

MS has extensive documentation @ Microsoft Office File Formats Documentation

Francesco's program is good but works for table based data and I like to make a generic one so one can generate Excel based spreadsheets/reports or use with a dynamic query on a table like his program.

My version to create Excel sheet requires only 3-4 calls -
Code:
fp = seq.open(fname, "w")
...
create.xml.header(fp, "NP_Test")
|write.header.row(fp, col.names)
write.row(fp, col.names, data.types, styleids)
...
write.row(fp, col.values, data.types, styleids2, 3, 5)
create.xml.footer(fp)
e = seq.close(fp)
view.with.excel(fname)
Quote:
In other words, when entering a selection of areas, which is a string of 3 positions, I want the field to show 3 positions and also allow only three, activate the zoom on areas.
You can use some of the predefined variables to achieve this -
Quote:
string attr.adju(1) 4 "l" align left, "r" align right, "c" align center
string attr.conv(1) 4 "u" convert to upper case, "l" convert to lower case
long attr.dbmaxlen 4R Maximum length of database field.
long attr.imax 4R Maximum input length.
long attr.maxlen 4R Maximum display length of current field.
long attr.minlen 4 Minimum length of current field.
or the rdi* and tt.align.according.domain() functions etc.

Instead of using the static session, you might be better off creating your windows screen using low level functions create.object() so can you have more control on field creations on the fly based on datatype. The latest tools have the new functions - Programmable dialogs which can simplify it.
Quote:
it would be great to be able to select the desired from-to values from an enum drop down list (although I doubt if this is possible)
You can use - set.list.values.for.field()
Quote:
when selecting a date, the field should behave like a date field, i.e. allowing to enter '01092008' or 01 for first day of month or '+10' for +10 days, etc. Also the zoom to the BaaN calendar will be active...
Refer to the links -

Use of session ttstpcalendar

Return Field for Calendar
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #20  
Old 22nd September 2008, 17:49
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,853
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
dynamic fields

Quote:
Originally Posted by en@frrom View Post

I optimized the session a bit more here and there, for instance enabling it to work for Tools tables, fixed some beauty issues, etc. My next planned optimization is to enable selecting on dates, both num and utc, and enums. Both are now only possible by entering the numeric value, which isn't very practical... Also zooming from selection fields would be great, and in general I would love to have the dynamic selection fields on the form assigned with the correct domain attributes like field length, input length, alligning, etc.

In other words, when entering a selection of areas, which is a string of 3 positions, I want the field to show 3 positions and also allow only three, activate the zoom on areas. When entering a Tools Patch which is a right aligned string, I want the input to be right aligned as well. When entering an enum, it would be great to be able to select the desired from-to values from an enum drop down list (although I doubt if this is possible), and when selecting a date, the field should behave like a date field, i.e. allowing to enter '01092008' or 01 for first day of month or '+10' for +10 days, etc. Also the zoom to the BaaN calendar will be active...

After my next 'bored' night, I will let you know what I achieved...


Regards,
En
If u dont have time develop something on ur own and wish to use something readily available and done by others, our baan2excel project on sourceforge.net already has all these (dates , enum/set , domain specific zooms , user defined finctions ,case conversions , field alignment.domain range validations , legal / illegal characters etc ). The reference programme for this is pdll91420 .

Our ERPJewels is completely separate set of programs in tu module with all new tables , domains , labels in tu module only (which one can import in customized VRC ) and further it has very good integration with any baan report. So u can provide XL sheets on the fly from any report , authorize it to any user and it's there in the repositiory for use in future.

There is good online help for the same also .

Though this tool works with ascii files , but can very well be adopted for xml structures .
Reply With Quote
Sponsored Links
  #21  
Old 23rd September 2008, 08:43
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
Heya Hitesh,

Thanks for your offer. I am sure your tools are jewels indeed, however I find it much more fun to play around with it on my own than getting a ready made tool... :) And since this is no high priority requirement but rather a nice-to-have optimizer, I am not so bothered by my current lack of time...

Thanks again and good luck!
En
__________________
Member of Baan Board

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #22  
Old 23rd September 2008, 18:16
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,853
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
baan2excel

Hi En ,

Thanks for the reply.

Being developer myself I know the joys of developent by self and hence can very well understand and appreciate ur preferences. Goodluck to u in these endevors .
Reply With Quote
  #23  
Old 4th November 2008, 02:56
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,061
NPRao will become famous soon enough
Baan: Baan 4-5,5.2(Reger),LN-6.1,Infor LN-10.x - DB: Oracle,MS-SQL - OS: HPUX, Linux, Windows
Quote:
I believe the program limits the number of rows exported to the excel limit(65536). On my version as you typed in the index values to extract it displayed the number of rows that would be exported - but it still limited you to 65536.
Mark,

I found a neat solution on the site: Exporting Data into Excel in Multiple Worksheets to keep a counter to keep track of the number of rows and when it hits the limit close the current worksheet and start with the next one.
PHP Code:
foreach (DataRow x in source.Tables[0].Rows){
rowCount++;
//if the number of rows is > 63000 create a new page to continue output
if (rowCount == 63000){

rowCount 0;
sheetCount++;
excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");
excelDoc.Write("<Worksheet ss:Name=\"Report_Sheet" sheetCount "\">");
excelDoc.Write("<Table>");
excelDoc.Write("<Row>"); 
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #24  
Old 23rd March 2010, 12:15
mpchheda's Avatar
mpchheda mpchheda is offline
Senior Member
 
Join Date: May 2008
Location: Mumbai
Posts: 105
mpchheda is on a distinguished road
Baan: Baan IV C4 - DB: SQL7 - OS: Window NT
Output file formate

I have imported and installed the session. It is working fine.
But while opening excel, i am getting error - file (tablename.xls) not found in c:\tmp. Please check the file name.
Our baan BSE is - D:\baan and tmp is D:\baan\tmp

can anybody guide me to configure the correct output path in script ?
Regards,
Manish
Reply With Quote
  #25  
Old 2nd February 2011, 15:51
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
New challenge: make compatible for running in JOB

Hey all,

Whoever (still) uses this session, I have a nice new challenge: make this session compatible for running in job mode. With the current code this session can no way run in job. I would love to make this session available fro job.process. It will then not offer selection ranges, but will dump the entire table.

Due to lack of time I cannot do it myself, but would be greatful if someone would find it helpful as well, and would invest in it...

Best regards,
Eli Nager
__________________
Member of Baan Board

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #26  
Old 3rd February 2011, 17:35
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,853
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
ideas

We already have program which can very well take xl exports in job mode (even considering/changing selection parameters intelligently like changing from to period dates , fiscal period based on system date etc) . So I basically dont need & know this program.

However based on our experience many years back I c only one challenge i see in job mode is that a program can not be zoomed or activated . We had solved this problem will converting the session functionality to dll and that had resolved the problem permanently.

Also if baan enums and dates are ok to u in tools export format , plain tools export (ttaad4226m000 ) with comma separator followed by batch script to rename the .S file to .csv can very well serve a purpose.

May be this help you or any volunteer to solve this challenge.

Last edited by Hitesh Shah : 3rd February 2011 at 17:37. Reason: clarity
Reply With Quote
  #27  
Old 3rd February 2011, 17:48
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
Hitesh Thanks for your response. I have meanwhile adjusted the session last night to make it job compatible. I couldn't resist... :)
__________________
Member of Baan Board

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #28  
Old 2nd September 2011, 14:00
Esther's Avatar
Esther Esther is offline
Junior Member
 
Join Date: Sep 2007
Location: Spain
Posts: 16
Esther is on a distinguished road
Baan: IV - DB: oracle - OS: -
I know it's an old thread, but i have to say that i've been adapting this session and i'm very impressed with it's working. It's very very useful.

Thanks for your work Francesco :)
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Not authorized to run as user baan positive Tools Administration & Installation 7 29th June 2004 08:56
Baan World Users conference in Orlando chadwickh General Discussion & Chat 7 3rd November 2001 21:55
BSP-US (LLC) added as a Baan Education Alliance svandenh General Discussion & Chat 1 20th August 2001 19:44


All times are GMT +2. The time now is 20:01.


©2001-2018 - Baanboard.com - Baanforums.com