the columns are ordered by the displayed order when export to excel.

I want to export a table to an excel file. Because tableToExcel cannot export the table by the displayed column order, I write the following code. It works, but there are several issues which I would like to get help from you.

1. I can only export to .csv. Is it possible to export to .xls?

2. It is slow because the value of some columns in my table are set in the code for displaying the table row, then I have to display row by row while I exporting. If I don't do that, my csv file cannot get those column value. I use:
TableSelectPlus(pTblID,i)
ExecuteProcess(pTblID,trtAffichageLigne)
Is it any other way to get it faster?

3. I have to remove TAB, CR, and Comma for a column value, otherwise it will mix up the format when I open my csv with excel. Is it possible to keep it but still get correct format?

4. Is it possible to simplify my code? I found it is ugly.

Thank you in advance.

PROCEDURE VIRTUAL ExportData(pTblID is string = :ID+".Tbl")
LOCAL
FileName is string

FileName = fSelect(ApSvc:irExp, :ID+".csv", "Select a file", ...
"(*.csv)" + TAB + "*.csv" + CR + ...
"All" + TAB + "*.*", "csv", fselCreate)
IF NOT (FileName ~= "") THEN
WHEN EXCEPTION IN
i,j is int
tblRow is int=TableCount(pTblID)
tblAllCol is int=TableCount(pTblID,toColonne)
tblCol is int=0
sMyValues is string
fID is int=fCreate(FileName)

myColAllArray is array of tblAllCol string
resColDisplay is int
colName is string
//save col into mycolallarray in the order of display subscript
FOR j=1 TO tblAllCol
colName=TableEnumColumn(pTblID,j)
IF {colName,indChamp}..Visible THEN
resColDisplay=TableColumnSubscript(pTblID,colName,tcpDisplay)
myColAllArray[resColDisplay]=TableEnumColumn(pTblID,j)
tblCol++
END
END
myColArray is array of tblCol string
MyArray is array of tblRow by tblCol string
tblCol=0
ArrayAddLine(MyArray)
//remove the columns which doesn't show and put the column name to my colarray
FOR j=1 TO tblAllCol
IF myColAllArray[j]<>"" THEN
tblCol++
myColArray[tblCol]=myColAllArray[j]
MyArray[1,tblCol]={myColAllArray[j],indChamp}..Title
IF MyArray[1,1]="ID" THEN MyArray[1,1]="Id"
END
END

FOR i=1 _TO_ tblRow
//because the value of some of the columns are set in Code for displaying a table row,if i don't do it, there will be no value in csv file.
TableSelectPlus(pTblID,i)
ExecuteProcess(pTblID,trtAffichageLigne)
ArrayAddLine(MyArray)
FOR j=1 TO tblCol
IF {myColArray[j],indChamp}..Type=typNum THEN
MyArray[i+1,j]={myColArray[j],indChamp}
ELSE
MyArray[i+1,j]={myColArray[j],indChamp}..DisplayedValue
END
MyArray[i+1,j]=Replace(MyArray[i+1,j],CR," ")
MyArray[i+1,j]=Replace(MyArray[i+1,j],TAB," ")
MyArray[i+1,j]=Replace(MyArray[i+1,j],",","")
END

END
sMyValues=ArrayToCSV(MyArray)
fWrite(fID,sMyValues)
fClose(fID)

DelayBeforeClosing(ApSvc::MsgTimeOut)
Info( StringBuild("Export to %1 completed.", FileName) )
DelayBeforeClosing()
DO
DelayBeforeClosing(ApSvc::MsgTimeOut)
Info( StringBuild("Export to %1 was unsuccessful.", FileName) )
DelayBeforeClosing()
END
END



Message forwarded from pcsoft.us.windev

Hi Jenny...

1. you can by using automation syntax against the excel activeX object

2. Put the code used to calculate the values to display in your columns inside procedures. From the row display code, call these procedure and display their return value. From your export code, call the procedure and export the returned value. This way you save the whole display time

3. If you export to xls directly, you shouldn't have this problem anymore

4. Didn't have the time to look in details

Best regards

Fabrice Harari
WinDev, WebDev, WinDev Mobile video courses