One of the tasks that I concider annoying is to provide table exports in the only tool that anybody can use...Excel.

To come up with a more permanent solution, I developed this session for BaanIV. With a bit of sluething it should work in any Baan version.
This session will export any baan table, optionaly using selection criteria, to an excel XML sheet. Dates, enums and what not are all properly converted.

Oh, the attached screenshots are in Dutch but it has an option for multiple languages and comes in English too.

Enjoy!

Has anyone tried this out? I imported this into our environment using Import data dictionary session and copied the forms from its present language to english. Upon running the session "Baan Data Export Tool" i get the error "Error Reading From Dump".

Did i miss anything?
Apart from that, when i click on the edit form button on the Maintain Forms, i just get a blank box with no fields for both the forms in Baan Data Export Tool session.

sk

I have problems also. I have sent Francesco an email asking for assistance.

Received a message from Francesco - he will try to retrieve a copy of the correct dump.

I can't help raising a wry smile of sympathy with your situation - we know what you're going through.
My record for reverse engineering a VB macro using Safari ODBC drivers into raw informix sql is:
Before: three days
After: seven seconds.
Revenge is sweet.............. and I shall watch this thread with great interest...

As pointed out, my dumpfile did not include the source or the forms for this session. Oops, sorry, this must have been an update and not a full dump.

So to set things straight, here are the forms (Dutch only, I'm afraid) and the missing source.


|******************************************************************************
|* Data Export Tool
|* Francesco Frentrop
|* 2007-07-11
|******************************************************************************
|* Main table , Form Type 4
|******************************************************************************

|****************************** declaration section ***************************
declaration:

table tttaad100 | Company data
table tttadv101 | Modules
table tttadv112 | Package VRC's
table tttadv130 | Descriptions per language
table tttadv140 | Display Labels per PVRC
table tttadv400 | Domains
table tttadv401 | Enum Values
table tttadv402 | Enum Descriptions
table tttadv420 | Table Definitions
table tttadv421 | Table Indexes
table tttadv422 | Table Fields

domain tcclan lang | Language
domain ttaad.pacc PACC | Package VRC
domain ttadv.vers version
domain ttadv.rele release
domain ttadv.cust customer

string sTABLE(9) | Table
string sVRC(10) | VRC

string expr(5020) | Expression string
string strBuffer BASED

long sql | SQL ID
long exp_id | Expression ID
long app_id | Applicaion ID
long ret
long fp | File Pointer
long fp_log | File Pointer to log file

string bdet.author(32) | Author
string bdet.create.date(20) | Creation Date
string bdet.version(7) | XML version No
string datum.style(24)
string time.style(24)

double width.factor
long EXCEL.LIMIT
long error.status

string local.dir(128) | Directory for export

string sdat(12)
string syear(4)
string smonth(2)
string sday(2)
string shr(2)
string smin(2)
string ssec(2)
string rkey(12)

|*********************** Table data **********************************
long field.count
long row.count
long row.len

long format.row.type
long format.row.name
long format.row.type.len
long format.row.name.len
long format.row.header
long format.row.header.len
long format.row.width
long format.row.width.len
long format.row.cdom
long format.row.cdom.len
long format.row.cpac
long format.row.cpac.len

string format.row(101, 1024)


|*********************** Messages **********************************
string error1(255)
string error2(255)
string error3(255)
string error4(255)
string error5(255)
string error6(255)
string error7(255)
string error8(255)
string error9(255)
string error10(255)

string status1(255)
string status2(255)
string status3(255)
string status4(255)
string status5(255)
string status6(255)
string status7(255)
string status8(255)
string status9(255)

|********************** form fields ***********************************
extern domain tcmcs.str2 cpac.f
extern domain tcmcs.str3 cmod.f
extern domain tcmcs.str3 flno.f

extern domain ttyeno layout.on
extern domain ttyeno limit.on
extern domain ttyeno timestamp.on

extern domain tcmcs.str50 status
extern domain tcmcs.long rcd.counter
extern domain tcmcs.long limit.no
extern domain tcmcs.str80 table.name

extern domain tcmcs.str8 form.index(11)
extern domain tcmcs.str50 value.f(11)
extern domain tcmcs.str50 value.t(11)
extern domain tcmcs.str4 form.from(11)
extern domain tcmcs.str4 form.to(11)
extern domain tcmcs.str16 form.desc(11)
extern domain ttyeno index.active(11)
domain tcmcs.long form.type(11)

|********************** includes **************************************
#pragma used dll ottdllbw

#include <bic_tt>


Before.Program:

PACC = curr.pacc$
lang = language$

error.status = 0

format.row.type = 9
format.row.name = 1
format.row.type.len = 2
format.row.name.len = 8
format.row.header = 11
format.row.header.len = 70
format.row.width = 81
format.row.width.len = 7
format.row.cdom = 88
format.row.cdom.len = 12
format.row.cpac = 100
format.row.cpac.len = 2

set.error.messages()
set.status.messages()

sdat = dte$()
syear = "20" & sdat(5;2)
smonth = sdat(1;2)
sday = sdat (3;2)
shr = sdat(7;2)
smin = sdat(9;2)
ssec = sdat(11;2)

ret = tt.user(logname$, bdet.author)
bdet.create.date = syear & "-" & smonth & "-" & sday & "T" & shr & ":" & smin & ":" & ssec & "Z"
bdet.version = "11.6568"
datum.style = " ss:StyleID=""s23"""
time.style = " ss:StyleID=""s24"""
width.factor = 7.27

local.dir = "C:\Temp\"

EXCEL.LIMIT = 65536
set.status(1)

|****************************** form section **********************************

form.all:
form.1:
init.form:
limit.on = ttyeno.no
layout.on = ttyeno.yes
timestamp.on = ttyeno.no
refresh()
display.all()

|get.screen.defaults()
form.2:
before.form:
ret = load.indexes()
if ret = -1 then
Message("Bestoa nie!!") | TODO
endif

|****************************** choice section ********************************

choice.cont.process:
on.choice:
start.process()


|****************************** field section *********************************

field.cpac.f:
check.input:
if len(strip$(cpac.f)) <> 2 then
set.input.error(error7, cpac.f)
endif


field.cmod.f:
check.input:
if len(strip$(cmod.f)) <> 3 then
set.input.error(error7, cmod.f)
endif
before.zoom:
ttadv101.cpac = cpac.f
field.flno.f:
check.input:
while len(flno.f) < 3
flno.f = "0" & flno.f
endwhile
before.zoom:
ttadv420.cpac = cpac.f
ttadv420.cmod = cmod.f
when.field.changes:
rkey = cmod.f & flno.f
determine.vrc()

select ttadv130.desc
from ttadv130
where ttadv130._compnr = 000
and ttadv130.cpac = :cpac.f
and ttadv130.kdes = ttadv.kdes.table
and ttadv130.rkey = :rkey
and ttadv130.clan = :lang
as set with 1 rows
selectdo
table.name = ttadv130.desc
refresh()
display.all()
endselect

|****************************** function section ******************************

functions:

function start.process()
{
string tmp.file(128)
string local.path(128)
string file.name(20)

status = ""
error.status = 0
rcd.counter = 0

determine.vrc()

tmp.file = creat.tmp.file$( bse.tmp.dir$() )

fp = seq.open(tmp.file, "a")
fp_log = seq.open(bse.dir$() & "\log\cccom9000.log", "w")

set.status(2)

process.table()

set.status(8)
ret = seq.close(fp)
ret = seq.close(fp_log)

if not error.status then
if timestamp.on = ttyeno.yes then
file.name = sTABLE & dte$()
else
file.name = sTABLE
endif

if layout.on = ttyeno.yes then
file.name = file.name & ".xls"
else
file.name = file.name & ".csv"
endif

local.path = strip$(local.dir) & strip$(file.name)

ret = server2client(tmp.file, strip$(local.path), 1, 0)
app_id = app_start("excel.exe " & local.path, "", "", "", "")
set.status(9)
else
Message("No data to be exported. Error Status: %d", error.status)
endif
}

function void determine.vrc()
{
long result, sequ

sequ = 1
result = 0

while result = 0

select *
from ttadv112
where ttadv112._compnr = 000
and ttadv112.pacc = :PACC
and ttadv112.cpac = :cpac.f
and ttadv112.sequ = :sequ
selectdo
version = ttadv112.vers
release = ttadv112.rele
customer = ttadv112.cust

select ttadv420.*
from ttadv420
where ttadv420._compnr = 000
and ttadv420.cpac = :ttadv112.cpac
and ttadv420.cmod = :cmod.f
and ttadv420.flno = :flno.f
and ttadv420.vers = :version
and ttadv420.rele = :release
and ttadv420.cust = :customer
and ttadv420.expi = ttyeno.no
selectdo
result = 1
sTABLE = ttadv420.cpac & ttadv420.cmod & ttadv420.flno
sVRC = version & release & customer
selectempty
sequ = sequ + 1
endselect
selectempty
result = -1
message(error4, PACC, cpac.f, sequ)
error.status = 4
endselect

endwhile
}

function process.table()
{
| I know, select is strictly speaking not necessary here

select ttadv420.*
from ttadv420
where ttadv420._compnr = 000
and ttadv420.cpac = :ttadv112.cpac
and ttadv420.cmod = :cmod.f
and ttadv420.flno = :flno.f
and ttadv420.vers = :version
and ttadv420.rele = :release
and ttadv420.cust = :customer
and ttadv420.expi = ttyeno.no
selectdo
process.data()
endselect
}

function void process.data()
{
long x
string strParse(2048)
string from.value(50)
string to.value(50)

set.status(3)

ret = get.table.info(sTABLE, field.count, row.count, row.len)
if ret then
error.status = 3
message(error3, ret)
else
set.status(4)
if layout.on = ttyeno.yes then
create.xml.header()
endif

if layout.on = ttyeno.yes then
for x = 1 to field.count
ret = seq.puts(" <Column ss:Width=""" & strip$(format.row(format.row.width, x;format.row.width.len)) & """/>", fp)
endfor
endif

write.header()

free.mem(strBuffer)
alloc.mem(strBuffer,row.len)

build.expr()

ret = seq.puts(expr, fp_log)

strParse = "select * from " & sTABLE
ret = 0
for x = 1 to 11
if (strip$(form.index(1, x)) <> "") and (strip$(value.t(1, x)) <> "") then
if ret = 0 then
from.value = " where "
ret = 1
else
from.value = " and "
endif
on case form.type(x)
case DB.LONG:
case DB.DOUBLE:
case DB.INTEGER:
case DB.FLOAT:
if strip$(value.f(1, x)) = "" then
from.value = from.value & strip$(form.index(1, x)) & " >= " & "0"
else
if isdigit(strip$(value.f(1, x))) then
from.value = from.value & strip$(form.index(1, x)) & " >= " & strip$(value.f(1, x))
else
Message(error8, form.index(1, x))
endif
endif
if isdigit(strip$(value.t(1, x))) then
to.value = " and " & strip$(form.index(1, x)) & " <= " & strip$(value.t(1, x))
else
message(error8, form.index(1, x))
endif
break
case DB.STRING:
case DB.MULTIBYTE:
from.value = from.value & strip$(form.index(1, x)) & " >= " & """" & strip$(value.f(1, x)) & """"
to.value = " and " & strip$(form.index(1, x)) & " <= " & """" & strip$(value.t(1, x)) & """"
break
default:
from.value = ""
to.value = ""
Message(error9, form.index(1, x))
endcase
strParse = strParse & strip$(from.value) & strip$(to.value)
endif
endfor
if limit.on = ttyeno.yes then
strParse = strParse & " as set with " & str$(limit.no) & " rows"
endif

ret = seq.puts(strParse, fp_log)

sql = sql.parse(strParse)

if not sql then
error.status = 1
Message(Error1)
goto SKIP
endif

set.status(5)
sql.exec(sql)
error.bypass = 1
while (true)
ret = sql.fetch(sql)
on case ret
case eendfile:
break
case enorec:
error.status = 6
message(error6, strParse)
case enotable:
error.status = 10
message(error10, sTABLE)
case 0:
rcd.counter = rcd.counter + 1
display("rcd.counter")
|refresh()
strBuffer = s.expr$(exp_id)
write.row()
if rcd.counter = EXCEL.LIMIT then
error.status = 5
message(error5)
break
else
continue
endif
default:
error.status = 2
message(error2, ret, strParse)

endcase
break
endwhile
error.bypass = 0

set.status(6)

sql.break(sql)
sql.close(sql)
sql = 0

free.mem(strBuffer)
expr.free(exp_id)
expr = ""

if layout.on = ttyeno.yes then
create.xml.footer()
endif
SKIP:
endif
}

function long get.table.info(string table.name(8), ref long no_fields, ref long no_rows, ref long row_len)
{
long result
long no_keys, no_columns, int_length, real_length
string domain_name(14), default_val(1)
long offset, size, dept, type, flag
string column.name(18)

string cpac(2)
string cmod(3)
string flno(3)
domain ttadv.clab clab

result = 0
no_fields = 0

cpac = table.name(1;2)
cmod = table.name(3;3)
flno = table.name(6;3)

ret = rdi.table(table.name, no_keys, no_columns, int_length, real_length)
row_len = int_length * 1.1 | Add 10% fudge because it doesn't work @TODO@

select *
from ttadv422
where ttadv422._compnr = 000
and ttadv422.cpac = :cpac
and ttadv422.cmod = :cmod
and ttadv422.flno = :flno
and ttadv422.vers = :version
and ttadv422.rele = :release
and ttadv422.cust = :customer
order by ttadv422.fdno
selectdo
no_fields = no_fields + 1
type = get.field.type(table.name, ttadv422.fdnm)
format.row(format.row.name, no_fields) = ttadv422.fdnm
format.row(format.row.type, no_fields) = str$(type)

if strip$(ttadv422.clab) <> "" then
clab = ttadv422.clab
else
clab = ttadv422.cpac & ttadv422.cmod & ttadv422.flno & "." & ttadv422.fdnm
endif

format.row(format.row.header, no_fields) = get.field.desc(lang, cpac.f, clab)

column.name = table.name & "." & ttadv422.fdnm
ret = rdi.column(column.name, domain_name, offset, size, dept, type, flag, default_val)
if size = 0 then
size = 1
endif
if type = DB.ENUM then
size = 12
endif
format.row(format.row.width, no_fields) = str$(size * width.factor)

format.row(format.row.cdom, no_fields) = ttadv422.cdom
format.row(format.row.cpac, no_fields) = ttadv422.pacd
if (type = DB.BITSET) or (type = DB.COMBINED) then
no_fields = no_fields - 1
endif
selectempty
result = 1
endselect

return(result)
}

function void write.header()
{
string header(2048)
long x

if layout.on = ttyeno.yes then
ret = seq.puts(" <Row ss:StyleID=""s22"">", fp)
for x = 1 to field.count
ret = seq.puts(" <Cell><Data ss:Type=""String"">" & strip$(format.row(format.row.header, x;format.row.header.len)) & "</Data></Cell>", fp)
endfor
ret = seq.puts(" </Row>", fp)
else
for x = 1 to field.count
header = header & strip$(format.row(format.row.header, x;format.row.header.len)) & ";"
endfor
ret = seq.puts(strip$(header), fp)
endif
}

function void write.row()
{
string tmp.field(1024)
string data.type(12)
string read.char(1)
string cell.style(24)
string cdom(12)
string cpac(2)
long type
long x, y
long cnst
long buffer.len

if layout.on = ttyeno.yes then
ret = seq.puts(" <Row>", fp)

y = 1

for x = 1 to field.count
tmp.field = ""
read.char = ""

strBuffer = strip$(strBuffer)
buffer.len = len(strBuffer)

while read.char <> ";" and y <= buffer.len
read.char = strBuffer(y;1)
if read.char <> ";" then
tmp.field = tmp.field & read.char
endif
y = y + 1
endwhile

type = lval(format.row(format.row.type, x;format.row.type.len))
cdom = format.row(format.row.cdom, x;format.row.cdom.len)
cpac = format.row(format.row.cpac, x;format.row.cpac.len)

ret = xml.prep(tmp.field)

on case type
case DB.BYTE:
data.type = "Number"
cell.style = ""
break
case DB.DATE:
data.type = "DateTime"
cell.style = datum.style
format.date.for.excel(tmp.field, data.type, cell.style)
break
case DB.TIME:
data.type = "DateTime"
cell.style = time.style
format.time.for.excel(tmp.field, data.type, cell.style)
break
case DB.DOUBLE:
data.type = "Number"
cell.style = ""
break
case DB.ENUM:
cnst = lval(tmp.field)
if cnst <> 0 then
select *
from ttadv401
where ttadv401._compnr = 000
and ttadv401.cpac = :cpac
and ttadv401.cdom = :cdom
and ttadv401.cnst = :cnst
as set with 1 rows
selectdo
select *
from ttadv402
where ttadv402._compnr = 000
and ttadv402.clan = :lang
and ttadv402.cpac = :ttadv401.cpac
and ttadv402.cdom = :ttadv401.cdom
and ttadv402.vers = :ttadv401.vers
and ttadv402.rele = :ttadv401.rele
and ttadv402.cust = :ttadv401.cust
and ttadv402.ctnm = :ttadv401.ctnm
as set with 1 rows
selectdo
tmp.field = ttadv402.edes
selectempty
tmp.field = "#" & ttadv401.ctnm
endselect
selectempty
tmp.field = "##" & cpac & "." & cdom & "." & "##" & tmp.field
endselect
else
tmp.field = ""
endif
data.type = "String"
cell.style = ""
break
case DB.FLOAT:
data.type = "Number"
cell.style = ""
break
case DB.INTEGER:
data.type = "Number"
cell.style = ""
break
case DB.LONG:
data.type = "Number"
cell.style = ""
break
case DB.STRING:
data.type = "String"
cell.style = ""
break
case DB.MULTIBYTE:
data.type = "String"
cell.style = ""
break
default:
data.type = "String"
cell.style = ""
endcase

ret = seq.puts(" <Cell" & cell.style & "><Data ss:Type=" & """" & strip$(data.type) & """" & ">" & strip$(tmp.field) & "</Data></Cell>", fp)
endfor

ret = seq.puts(" </Row>", fp)
else
ret = seq.puts(strBuffer, fp)
endif
}

function void format.date.for.excel(ref string num.str, ref string type.str, ref string style.str)
{
long date.val

long year
long month
long day

string syear(4)
string smonth(2)
string sday(2)

date.val = lval(num.str)
if date.val < 693596 then
num.str = ""
type.str = "String"
style.str = ""
else
num.to.date(date.val, year, month, day)
syear = str$(year)
if month < 10 then
smonth = "0" & str$(month)
else
smonth = str$(month)
endif
if day < 10 then
sday = "0" & str$(day)
else
sday = str$(day)
endif

num.str = syear & "-" & smonth & "-" & sday & "T00:00:00.000"
endif
}

function void format.time.for.excel(ref string num.str, ref string type.str, ref string style.str)
{
long num.val
long hrs
long mins
long secs

num.val = lval(num.str)

if num.val < 1 then
num.str = ""
type.str = "String"
style.str = ""
else
secs = num.val \ 3600
mins = (num.val - secs) \ 60
hrs = (num.val - secs - (mins * 60)) / 60.0
num.str = "1899-12-31T" & str$(hrs) & ":" & str$(mins) & ":" & str$(secs) & ".000"
endif
}
function void build.expr()
{
long fld.type
string fld.name(8)
long x

expr = ""

for x = 1 to field.count
fld.name = format.row(format.row.name, x;format.row.name.len)
fld.type = lval(format.row(format.row.type, x;format.row.type.len))
add.field.to.row(fld.name, fld.type)
endfor
exp_id = expr.compile(expr)
}

function domain ttcdes get.field.desc(domain tclang language, domain ttadv.cpac package, domain ttadv.clab label)
{
domain ttcdes cdes

select ttadv140.desc
from ttadv140
where ttadv140._compnr = 000
and ttadv140.clan = :language
and ttadv140.cpac = :package
and ttadv140.clab = :label
as set with 1 rows
selectdo
cdes = ttadv140.desc
endselect

| OR??
| ret = getlabel (lang, ttadv422.cpac, clab, leng, lhgt, vers, rele, cust)

return(strip$(cdes))
}

function long get.field.type(string table.name(8), string field.name(8))
{
long offset, size, dept, type, flag
string domain_name(14), default_val

ret = rdi.column(table.name & "." & field.name, domain_name, offset, size, dept, type, flag, default_val)

return(type)
}

function add.field.to.row(domain ttadv.fdnm field, long type)
{
string action(24)
string close.bracket(6)

action = ""
close.bracket = ")"
on case type
case DB.BYTE:
action = "str(val(" |-
close.bracket = "))"
break
case DB.INTEGER: |+
action = "str("
break
case DB.LONG: |+
action = "str("
break
case DB.FLOAT:
action = "str("
break
case DB.TIME:
action = "str("
break
case DB.DOUBLE: |+
action = "str("
break
case DB.STRING: |+
action = "strip("
break
case DB.DATE: |+
action = "str("
break
case DB.TEXT:
action = "str("
break
case DB.ENUM: |+
action = "str("
break
case DB.BITSET:
action = "skip"
break
case DB.COMBINED:
action = "skip"
break
case DB.MULTIBYTE: |+
action = "strip("
break
default:
action = "skip"
endcase

if expr <> "" then
expr = expr & " & "";"" & "
endif

if strip$(action) <> "skip" then
expr = expr & action & sTABLE & "." & strip$(field) & close.bracket
else
|expr = expr & """;"""
endif
}

function void create.xml.header()
{
ret = seq.puts("<?xml version=""1.0""?>", fp)
ret = seq.puts("<?mso-application progid=""Excel.Sheet""?>", fp)
ret = seq.puts("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""", fp)
ret = seq.puts(" xmlns:o=""urn:schemas-microsoft-com:office:office""", fp)
ret = seq.puts(" xmlns:x=""urn:schemas-microsoft-com:office:excel""", fp)
ret = seq.puts(" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""", fp)
ret = seq.puts(" xmlns:html=""http://www.w3.org/TR/REC-html40"">", fp)
ret = seq.puts(" <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">", fp)
ret = seq.puts(" <LastAuthor>" & strip$(bdet.author) & "</LastAuthor>", fp)
ret = seq.puts(" <Created>" & bdet.create.date & "</Created>", fp)
ret = seq.puts(" <LastSaved>" & bdet.create.date & "</LastSaved>", fp)
ret = seq.puts(" <Version>" & bdet.version & "</Version>", fp)
ret = seq.puts(" </DocumentProperties>", fp)
ret = seq.puts(" <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">", fp)
ret = seq.puts(" <WindowHeight>12660</WindowHeight>", fp)
ret = seq.puts(" <WindowWidth>19020</WindowWidth>", fp)
ret = seq.puts(" <WindowTopX>120</WindowTopX>", fp)
ret = seq.puts(" <WindowTopY>120</WindowTopY>", fp)
ret = seq.puts(" <ProtectStructure>False</ProtectStructure>", fp)
ret = seq.puts(" <ProtectWindows>False</ProtectWindows>", fp)
ret = seq.puts(" </ExcelWorkbook>", fp)
ret = seq.puts(" <Styles>", fp)
ret = seq.puts(" <Style ss:ID=""Default"" ss:Name=""Normal"">", fp)
ret = seq.puts(" <Alignment ss:Vertical=""Bottom""/>", fp)
ret = seq.puts(" <Borders/>", fp)
ret = seq.puts(" <Font/>", fp)
ret = seq.puts(" <Interior/>", fp)
ret = seq.puts(" <NumberFormat/>", fp)
ret = seq.puts(" <Protection/>", fp)
ret = seq.puts(" </Style>", fp)
ret = seq.puts(" <Style ss:ID=""s21"">", fp)
ret = seq.puts(" <Font x:Family=""Swiss"" ss:Bold=""1""/>", fp)
ret = seq.puts(" </Style>", fp)
ret = seq.puts(" <Style ss:ID=""s22"">", fp)
ret = seq.puts(" <Font x:Family=""Swiss"" ss:Bold=""1""/>", fp)
ret = seq.puts(" </Style>", fp)
ret = seq.puts(" <Style ss:ID=""s23"">", fp)
ret = seq.puts(" <Font x:Family=""Swiss"" ss:Bold=""0""/>", fp)
ret = seq.puts(" <NumberFormat ss:Format=""[$-413]dd\ mmm\ yyyy;@""/>", fp)
ret = seq.puts(" </Style>", fp)
ret = seq.puts(" <Style ss:ID=""s24"">", fp)
ret = seq.puts(" <Font x:Family=""Swiss"" ss:Bold=""0""/>", fp)
ret = seq.puts(" <NumberFormat ss:Format=""Short Time""/>", fp)
ret = seq.puts(" </Style>", fp)
ret = seq.puts(" </Styles>", fp)
ret = seq.puts(" <Worksheet ss:Name=""BDET - " & sTABLE & """>", fp)
ret = seq.puts(" <Table>", fp)
}

function void create.xml.footer()
{
ret = seq.puts(" </Table>", fp)
ret = seq.puts(" <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">", fp)
ret = seq.puts(" <PageSetup>", fp)
ret = seq.puts(" <PageMargins x:Bottom=""0.984251969"" x:Left=""0.78740157499999996""", fp)
ret = seq.puts(" x:Right=""0.78740157499999996"" x:Top=""0.984251969""/>", fp)
ret = seq.puts(" </PageSetup>", fp)
ret = seq.puts(" <Print>", fp)
ret = seq.puts(" <ValidPrinterInfo/>", fp)
ret = seq.puts(" <PaperSizeIndex>9</PaperSizeIndex>", fp)
ret = seq.puts(" <HorizontalResolution>600</HorizontalResolution>", fp)
ret = seq.puts(" <VerticalResolution>600</VerticalResolution>", fp)
ret = seq.puts(" </Print>", fp)
ret = seq.puts(" <Selected/>", fp)
ret = seq.puts(" <FreezePanes/>", fp)
ret = seq.puts(" <FrozenNoSplit/>", fp)
ret = seq.puts(" <SplitHorizontal>1</SplitHorizontal>", fp)
ret = seq.puts(" <TopRowBottomPane>2</TopRowBottomPane>", fp)
ret = seq.puts(" <ActivePane>2</ActivePane>", fp)
ret = seq.puts(" <Panes>", fp)
ret = seq.puts(" <Pane>", fp)
ret = seq.puts(" <Number>3</Number>", fp)
ret = seq.puts(" <ActiveRow>7</ActiveRow>", fp)
ret = seq.puts(" <ActiveCol>5</ActiveCol>", fp)
ret = seq.puts(" </Pane>", fp)
ret = seq.puts(" </Panes>", fp)
ret = seq.puts(" <ProtectObjects>False</ProtectObjects>", fp)
ret = seq.puts(" <ProtectScenarios>False</ProtectScenarios>", fp)
ret = seq.puts(" </WorksheetOptions>", fp)
ret = seq.puts(" </Worksheet>", fp)
ret = seq.puts("</Workbook>", fp)
}

function void set.status(long stat)
{
on case stat
case 1:
status = status1
break
case 2:
status = status2
break
case 3:
status = status3
break
case 4:
status = status4
break
case 5:
status = status5
break
case 6:
status = status6
break
case 7:
status = status7
break
case 8:
status = status8
break
case 9:
status = status9
break
endcase

refresh()
display.all()
}


function void set.error.messages()
{
on case lval(lang)
case 1:
error1 = "Onjuiste query gegenereerd door BDET"
error2 = "Error %d heeft zich voorgedaan bij het uitvoeren van de query: %s"
error3 = "Fout %d bij verzamelen tabelgegevens."
error4 = "Geen VRK data in %s voor pakket %s, volgnr %d. Tabel bestaat niet."
error5 = "Tabel heeft meer records dan Excel kan verwerken."
error6 = "De query '%s' gaf geen records terug."
error7 = "Invoer niet juist: '%s'"
error8 = "Index %s heeft een numerieke waarde nodig."
error9 = "Index %s kan niet worden verwerkt door BDET."
error10= "Tabel %s bestaat niet in het huidige bedrijf."
break
default:
error1 = "BDET generated illegal query"
error2 = "Error %d occurred during execution of query: %s"
error3 = "Error %d collecting table data."
error4 = "No VRC data in %s for package %s, sequence %d. Table does not exist."
error5 = "Number of records in table exceed Excel limitation."
error6 = "The query '%s' did not return any records."
error7 = "Input not valid: '%s'"
error8 = "Index %s requires a numeric value."
error9 = "BDET can not parse index %s."
error10= "Table %s does not exist in the current company."
break
endcase
}

function void set.status.messages()
{
on case lval(lang)
case 1:
status1 = "Initialiseren"
status2 = ""
status3 = "Tabelgegevens verzamelen"
status4 = "Kopgegevens aanmaken"
status5 = "Regels verwerken"
status6 = "Voetregel aanmaken"
status7 = ""
status8 = "Programma afsluiten"
status9 = "Gereed"
break
default:
status1 = "Initialising"
status2 = ""
status3 = "Collecting table data"
status4 = "Creating header"
status5 = "Processing lines"
status6 = "Creating footer"
status7 = ""
status8 = "Closing application"
status9 = "Ready"
break
endcase
}

function long xml.prep(ref string inp.string)
{
long x, result, a
string outp.string(255)
string c

result = 0
outp.string = ""

for x = 1 to len(inp.string)
c = inp.string(x; 1)
a = asc(c)
if a <> 32 and (a < 39 or (a > 57 and a < 65) or a > 122) then
outp.string = outp.string & "&#" & str$(a) & ";"
result = x
else
outp.string = outp.string & c
endif
endfor
inp.string = strip$(outp.string)
return(result)
}

function long load.indexes()
{
long rv | return value
long x, y

domain ttadv.clab clab

string field.name.t(15)
string field.name.f(15)

rv = 0

for x = 1 to 11
form.index(1,x) = ""
value.f(1,x) = ""
value.t(1,x) = ""
index.active(x) = ttyeno.no
endfor

select *
from ttadv421
where ttadv421._compnr = 000
and ttadv421.cpac = :cpac.f
and ttadv421.cmod = :cmod.f
and ttadv421.flno = :flno.f
and ttadv421.vers = :version
and ttadv421.rele = :release
and ttadv421.cust = :customer
and ttadv421.acti = ttyeno.yes
order by ttadv421.indn
selectdo
rv = 1
| Add index fields to index array on form
for x = 1 to 24
if strip$(ttadv421.part(1, x)) <> "" and ttadv421.part(1,x;3) <> "c00" and ttadv421.part(1,x;3) <> "cmb" then
for y = 1 to 11
if form.index(1, y) = ttadv421.part(1, x) then
y = 12
else
if strip$(form.index(1, y)) = "" then
form.index(1, y) = ttadv421.part(1, x)
clab = get.label(form.index(1, y))
form.desc(1, y) = get.field.desc(lang, cpac.f, clab)
form.type(y) = get.field.type(cpac.f & cmod.f & flno.f, ttadv421.part(1, x))
y = 12
endif
endif
endfor
else
x = 25
endif
endfor
selectempty
| No index found for this table (or table does not exist)
rv = -1
selecteos
endselect

for x = 1 to 11
field.name.f = "value.f(" & str$(x) & ")"
field.name.t = "value.t(" & str$(x) & ")"
if strip$(form.index(1, x)) <> "" then
form.from(1,x) = "From"
inputfield.visible(field.name.f)
form.to(1, x) = "To"
inputfield.visible(field.name.t)
else
form.from(1,x) = ""
inputfield.invisible(field.name.f)
form.to(1, x) = ""
inputfield.invisible(field.name.t)
endif
endfor

refresh()
display.all()

return(rv)
}

function domain ttadv.clab get.label(domain ttadv.fdnm field.name)
{
domain ttadv.clab rv

select *
from ttadv422
where ttadv422._compnr = 000
and ttadv422.cpac = :cpac.f
and ttadv422.cmod = :cmod.f
and ttadv422.flno = :flno.f
and ttadv422.vers = :version
and ttadv422.rele = :release
and ttadv422.cust = :customer
and ttadv422.fdnm = :field.name
selectdo
if strip$(ttadv422.clab) <> "" then
rv = ttadv422.clab
else
rv = cpac.f & cmod.f & ttadv422.flno & "." & ttadv422.fdnm
endif
selectempty
rv = ""
endselect

return(rv)
}

While I'm at it:

Export tabel = Export table (bet you didn't see that one coming)
Conversie naar Excel XML formaat = Conversion to Excel XML format(1)
Maximaal aantal export regels = Maximum number of export lines (2)
Tijdvermelding op bestandsnaam = Time stamp on file name (3)

Veld = Field
Omschrijving = Description
Criteria = ...

So much for Dutch 1.01. Baan traditionals can change the English any way they like as long as it's no longer comprehensible ;)

(1) Uncheck this box to get a regular fast-n-dirty ASCII dump
(2) large tables can take a few minutes to process. This is the preview button
(3) The temporary file on the client's computer will be re-used every time to save on the clutter. Adding a unique time-stamp allows exports to be re-used or to do multiple exports without having to close excel each time (file in use)

Thanks for the tool and the language lesson. :) I just used the script to rebuild the forms in english. I just tested on small sets.

can post the english copy.. how do i import in...? i am new in baan..

Actually if I was you - I would just create a session and then copy the script into the new session. Then from the form picture and the script you can create your own form. The script was the important piece.

Hi Francesco,

Thanks for useful gift.

I am curious to know, how form two fields will be activated.

Please help.

regards,

-Suhas

When you click on form2 it does the load.indexes routine. This will load the form 2 fields. On form 2 you should have fields set up as arrays - like the attached (keep in mind I have added a few things). Hope I answered your question.

Nice work, thanks for this. So far it has worked on all the table that I have tried.

This is wonderful.

Francesco: I have a request - where did you find all the documentation related to creating the XML output for EXCEL - is this in a textbook I can buy? Or is it all 'get your hands dirty' type of experience?

Thanks.

Hi

Nice utility but my question is what abt big tables like tfgld418 etc. which have millions of records

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.

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

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

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 (http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats)

Save to SpreadsheetML and Extracting Data (http://www.brainbell.com/tutorials/ms-office/excel/Save_To_SpreadsheetML_And_Extracting_Data.htm)

MS has extensive documentation @ Microsoft Office File Formats Documentation (http://go.microsoft.com/fwlink/?LinkId=119249)

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 -
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)
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 -
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 (http://www.baanboard.com/programmers_manual_baanerp_help_functions_programmable_dialogs_synopsis) which can simplify it.
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() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_form_and_form_field_operations_set_list_values_for_field)
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 (http://www.baanboard.com/baanboard/showthread.php?t=5329&highlight=ttstpcalendar)

Return Field for Calendar (http://www.baanboard.com/baanboard/showthread.php?t=9778&highlight=WTHANG)

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 .

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

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 .

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 (http://www.codeproject.com/KB/aspnet/Export_to_Excel.aspx?fid=401377&df=90&mpp=25&noise=3&sort=Position&view=Quick&select=2764013#xx2764013xx) 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.
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>");

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

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

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.

Hitesh Thanks for your response. I have meanwhile adjusted the session last night to make it job compatible. I couldn't resist... :)

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