[Migrated content. Thread originally posted on 07 July 2009]
From my ever hard working buddy Claudio. A lot of examples on how to manipulate cells in Excel.
IDENTIFICATION DIVISION.
PROGRAM-ID. ExcelSimple.
* Compile with one of the following commandlines,
* accordingly with your Office version
*
* ccbl32 -ga -si 2003 -sx 2000 ExcelSimple.cbl
* ccbl32 -ga -sx 2003 -si 2000 ExcelSimple.cbl
*
*
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.
* COPY "excel.def". | 2003
* COPY "excel-2000.def". | 2000
decimal-point is comma.
DATA DIVISION.
WORKING-STORAGE SECTION.
77 olExcel HANDLE OF APPLICATION.
77 olWrkBk HANDLE OF WORKBOOK.
77 olWrkSh HANDLE OF WORKSHEET.
77 olWrkShNEW HANDLE OF WORKSHEET.
77 oRange HANDLE OF RANGE.
77 oFont HANDLE OF @Font.
77 oInterior HANDLE OF Interior.
77 MY-STRING PIC X(80).
77 ws-count PIC 9(5).
77 H-VARIANT USAGE HANDLE.
77 RESULT-CODE PIC S9.
77 ws-date PIC X(10).
77 WS-SEPARATOR PIC X.
PROCEDURE DIVISION.
Main.
CREATE Application OF Excel
HANDLE IN olExcel.
*This is for training purposes only. If you don't want to see
*Excel, remove this line.
MODIFY olExcel @Visible = 1.
MODIFY olExcel Workbooks::Add()
GIVING olWrkBk.
INQUIRE olWrkBk Worksheets::Item(1) IN
olWrkSh.
*Set the value of a cell.
MODIFY olWrkSh Range("A1")::Value = "Last Name".
*Get the value of a cell.
INQUIRE olWrkSh Range("A1")::Value IN MY-STRING.
*Force a recalculation of a sheet
* MODIFY olWrkSh Range("A1")::Calculate().
* INIZIO ***********************************************************
* FORMATTAZIONE CELLE
*
MODIFY olWrkSh Range("C4")::Value = "A".
MODIFY olWrkSh Range("D4")::Value = "B".
MODIFY olWrkSh Range("C5")::Value = "Dream Theater".
MODIFY olWrkSh Range("C6")::Value = "Blind Guardian".
MODIFY olWrkSh Range("D5")::Value = "Heaven & Hell".
MODIFY olWrkSh Range("D6")::Value = "Queensryche".
* Columns("C:D").Select
* Columns("C:D").EntireColumn.AutoFit
MODIFY olWrkSh Range("C:D")::Columns()::Select().
MODIFY olWrkSh Range("C:D")::EntireColumn()::AutoFit().
* Range("C5").Select
* Selection.Font.Bold = True
* MODIFY olWrkSh Range("C5")::Select() | this causes MAV
* GIVING oRange. | this causes MAV
* MODIFY oRange @Font::Bold = 1. | this causes MAV
INQUIRE olWrkSh Range("C5")::Font
IN oFont.
MODIFY oFont Bold = 1.
* alternative method for Bold
MODIFY olWrkSh Range("A1:B1")::Font()::Bold(1)
* Range("D5").Select
* Selection.Font.Italic = True
INQUIRE olWrkSh Range("D5")::Font
IN oFont.
MODIFY oFont Italic = 1.
* Range("D6").Select
* With Selection.Font
* .Color = -16776961
* .TintAndShade = 0
* End With
INQUIRE olWrkSh Range("D6")::Font
IN oFont.
MODIFY oFont Color = -16776961.
* Range("C6").Select
* With Selection.Interior
* .Pattern = xlSolid
* .PatternColorIndex = xlAutomatic
* .Color = 65535
* .TintAndShade = 0
* .PatternTintAndShade = 0
* End With
INQUIRE olWrkSh Range("C6")::Interior
IN oInterior.
MODIFY oInterior Pattern = xlSolid
PatternColorIndex = xlAutomatic
Color = 65535.
* Columns("C:D").Select
* With Selection
* .HorizontalAlignment = xlCenter
* .VerticalAlignment = xlCenter
* .WrapText = False
* .Orientation = 0
* .AddIndent = False
* .IndentLevel = 0
* .ShrinkToFit = False
* .ReadingOrder = xlContext
* .MergeCells = False
* End With
MODIFY olWrkSh
Range("C4:D6")::HorizontalAlignment = xlCenter
MODIFY olWrkSh
Range("C4:D6")::VerticalAlignment = xlCenter.
* With Selection.Borders(xlEdgeBottom)
* .LineStyle = xlContinuous
* .Weight = xlThin
* .ColorIndex = xlAutomatic
* End With
MODIFY olWrkSh Range("C5:D6")::BorderAround(
BY NAME LineStyle = xlContinuous
BY NAME Weight = xlThin
BY NAME ColorIndex = xlAutomatic
)
* FINE *************************************************************
* FORMATO NUMERO **************************************************
MODIFY olWrkSh Range("A5")::Value = 12345,99.
MODIFY olWrkSh Range("A5")::NumberFormat = "0,00".
* MODIFY olWrkSh Range("A17")::NumberFormat = "General"
* MODIFY olWrkSh Rows(1)::NumberFormat = "hh:mm:ss"
* MODIFY olWrkSh Columns("C")::NumberFormat =
* "$#,##0.00_);[Red]($#,##0.00)"
* FORMATO DATA ****************************************************
* set the value of a date into a cell
* objExcel.Cells(1,1).NumberFormat = "mm.dd.yyyy"
*
* 01 is the day, 02 is the month
*
MOVE "01/02/2003" TO ws-date.
MODIFY olWrkSh Range("A2")::Value = ws-date.
INQUIRE olWrkSh Range("A2")::NumberFormat IN ws-date.
IF ws-date(1:2) = "gg"
MOVE "01/02/2003" TO ws-date
ELSE
MOVE "02/01/2003" TO ws-date
END-IF
MODIFY olWrkSh Range("A2")::Value = ws-date.
MODIFY olWrkSh Range("A2")::NumberFormat = "gg/MM/aaaa".
* LARGHEZZA COLONNA ***********************************************
* [URL]msdn.microsoft.com/.../URL]
MODIFY olWrkSh Range("F1")::ColumnWidth = 4
MODIFY olWrkSh Range("F1")::RowHeight = 30
*
* END *************************************************************
*
* INIZIO ***********************************************************
* Aggiungi un foglio a destra
MODIFY olWrkBk Worksheets::Add()
GIVING olWrkShNEW.
INQUIRE olWrkBk Worksheets::Count IN ws-count
INQUIRE olWrkBk Worksheets::Item(ws-count) IN
olWrkSh.
MODIFY olWrkShNEW @Move(BY NAME After olWrkSh)
* FINE *************************************************************
*
* INIZIO ***********************************************************
* Copia un foglio
*
* Sheets("Sheet1").Select
* Sheets("Sheet1").Copy After:=Sheets(3)
INQUIRE olWrkBk Worksheets::Item(1) IN
olWrkSh.
MODIFY olWrkSh @Copy(BY NAME After olWrkSh)
* FINE *************************************************************
*
* INIZIO ***********************************************************
* INQUIRE DEL SEGNO DI SEPARAZIONE DECIMALE
*
* INQUIRE olWrkBk @Application::DecimalSeparator | 2003
* IN WS-SEPARATOR | 2003
* FINE *************************************************************
*
DESTROY oFont.
DESTROY olWrkSh.
DESTROY olWrkShNEW.
*Enforce a close without save, or Excel will prompt you.
MODIFY olWrkBk @Close(BY NAME SaveChanges 0).
DESTROY olWrkBk.
MODIFY olExcel Quit().
*This next line is very important, otherwise Excel will stick in
*memory.
DESTROY olExcel.
GOBACK.
