CODEWITHSUNDEEP
progress-4gl
Maha
Maha

Reputation: 19

Excel Export as Text Using Progress 4GL

I need help with an Excel Export. I'm trying to export a column as text using Progress 4GL. I need numbers in the column which have a leading "0" that excel keeps deleting when opens.

I tried it with using STRING function to make the variable to be String before it goes to export. It did not work. Is there any other way to export with leading 0s?

Upvotes: 0

Views: 2484

Answers (4)

Yaisa
Yaisa

Reputation: 11

You can set the format of the cell, something like this:

h-excel:Range("A12")::numberformat = FILL("0",x).

where x would be the length of the variable you want to insert.

Upvotes: 0

AquaAlex
AquaAlex

Reputation: 380

When you import manually into excel select the columns as TEXT and not GENERAL, then the leading zero will not dissapear

Upvotes: 0

Rodolfo Gonçalves
Rodolfo Gonçalves

Reputation: 156

If you're writing directly to Excel, you can put a ' character at the beginning of the number, and then Excel will interpret it as number formatted with text.

You can see it in action here:

def var ch-excel      as com-handle no-undo.
def var ch-wrk        as com-handle no-undo.

create "Excel.Application" ch-excel no-error.
ch-excel:visible       = no no-error.
ch-excel:DisplayAlerts = no no-error.

ch-wrk = ch-excel:workbooks:add.

ch-excel:cells(1,1) = "'01".
ch-wrk:SaveAs("c:\temp\test.xlsx", 51, "", "", false, false, ) no-error. /* 51 = xlOpenXMLWorkbook */

ch-excel:DisplayAlerts = yes.
ch-excel:quit().
release object ch-wrk.
release object ch-excel.

Since I've be using excel to generate reports for a while, I've create a small lib that generates an excel based on a temp-table definition, and I think it might be helpful, you can check it up at: https://github.com/rodolfoag/4gl-excel

Upvotes: 0

TerryB
TerryB

Reputation: 629

I assume that you are saving the file in progress as a CSV and when the file is opened in Excel it loses the leading 0.

When outputting the string you can enclose it as follows so that excel reads it in as a string.

put unformatted '="' string("00123") '"'

Upvotes: 1

Related Questions