Срочно одному из клиентов потребовалось сделать отчет в Excel.
Все бы ничего, но количество строк больше 30 000 . Ole просто умирает при таком количестве строк. Ничего быстрее не придумал, как выгрузить в файл csv, а затем открыть его в Excel. Ниже - код программы.
/*******************************************************/
DEFINE VARIABLE chExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkSheet AS COM-HANDLE NO-UNDO.
DEF VAR cPath AS CHAR NO-UNDO.
DEF VAR cFile AS CHAR NO-UNDO.
DEF VAR cFullRange AS CHAR NO-UNDO.
DEF VAR iNumberOfRows AS INT NO-UNDO.
DEF VAR my-datetime AS DATETIME NO-UNDO.
DEF VAR s AS CHAR NO-UNDO.
DEF TEMP-TABLE tt-prep NO-UNDO
FIELD idMnn AS CHAR
FIELD MnnRus AS CHAR
FIELD MnnLat AS CHAR
FIELD idTrn AS CHAR
FIELD Trn AS CHAR.
FOR EACH TRADENAMEs , EACH prep
WHERE TRADENAMEs.id = prep.TRADENAMEID BREAK BY TRADENAMEs.id:
IF FIRST(TRADENAMEs.id) THEN
DO:
CREATE tt-prep.
ASSIGN
tt-prep.idTrn = 'Код ТРН'
tt-prep.Trn = 'ТРН'
tt-prep.idmnn = 'Код МНН'
tt-prep.MnnRus = 'МНН Русское'
tt-prep.MnnLat = 'МНН Латинское'.
END.
IF FIRST-OF(TRADENAMEs.id) THEN
DO:
CREATE tt-prep.
ASSIGN
tt-prep.idTrn = string(TRADENAMEs.id)
tt-prep.Trn = TRADENAMEs.NAME.
FOR EACH PREP_ACTMATTERS WHERE PREP_ACTMATTERS.PREPID = PREP.id,
EACH ACTMATTERS WHERE ACTMATTERS.id = PREP_ACTMATTERS.MATTERID:
ASSIGN
tt-prep.idmnn = string(ACTMATTERS.id)
tt-prep.MnnRus = ACTMATTERS.rusname
tt-prep.MnnLat = ACTMATTERS.latname.
END.
END.
END.
cPath = SESSION:TEMP-DIRECTORY.
my-datetime = DATETIME(TODAY, MTIME).
s = STRING(my-datetime).
s = REPLACE(s , '/' , '_').
s = REPLACE(s , ' ' , '_').
s = REPLACE(s , ':' , '-').
s = SUBSTRING(s, 1, LENGTH(s) - 4 ).
cFile = cpath + 'export' + USERID('ibol') + s + '.csv'.
OUTPUT TO value(cFile) CONVERT TARGET '1251'.
FOR EACH tt-prep:
iNumberOfRows = iNumberOfRows + 1.
EXPORT DELIMITER ';' tt-prep.
END.
OUTPUT CLOSE.
CREATE "Excel.Application" chExcel CONNECT NO-ERROR.
IF NOT VALID-HANDLE(chExcel) THEN
CREATE "Excel.Application" chExcel.
ASSIGN chExcel:WindowState = -4140 . /* Minimized */
chExcel:VISIBLE = FALSE .
chExcel:DisplayAlerts = FALSE.
chExcel:Workbooks:Open(cFile).
chWorkbook = chExcel:ActiveWorkbook.
chWorkSheet = chExcel:Sheets:ITEM(1).
cFullRange = "A1:" + 'E' + STRING(1).
chWorkSheet:Range(cFullRange):COLUMNS:BorderAround(1,-4138,-4105,-4105).
chWorkSheet:Range(cFullRange):Interior:ColorIndex = 20.
cFullRange = "A1:" + 'E' + STRING(iNumberOfRows).
/*chWorkSheet:Range(cFullRange):Borders:Weight = 1. */
chWorkSheet:Range(cFullRange):COLUMNS:AutoFit.
chWorkSheet:Range(cFullRange):EntireRow:AutoFit.
cFullRange = "A3:" + 'E' + STRING(iNumberOfRows).
chWorkSheet:Range(cFullRange):Borders:Weight = 1.
chWorkSheet:Range("A1":E):Select.
ASSIGN chExcel:DisplayAlerts = TRUE
chExcel:VISIBLE = TRUE
chExcel:WindowState = -4143 /* Maximized */.
/*
chWorkbook:CLOSE(0). */
RELEASE OBJECT chWorkSheet NO-ERROR.
RELEASE OBJECT chWorkbook NO-ERROR.
RELEASE OBJECT chExcel NO-ERROR.
ASSIGN chWorkSheet = ?
chWorkbook = ?
chExcel = ?.
