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