We help IT Professionals succeed at work.

Reporting in MS Excel

trekkee
trekkee asked
on
//print totals
sheet.cells[ i + 2, 3 ] := 'Total (' + class_code + ')';
sheet.cells[ i + 2, 4 ] := class_time_total;
sheet.cells[ i + 2, 5 ] := class_total;

//set the TOTAL row range
startrange := 'C' + IntToStr( i + 2 );
endrange := 'E' + IntToStr( i + 2 );
range := x.Workbooks[ 1 ].Worksheets[ 'Delphi Data' ].Range[ startrange + ':' + endrange ];

//format TOTAL row
range.columns.font.size := 11;
range.columns.font.bold := true;


*** In the above code, how could I format the row/cells to have a border on the top? or bottom?
Comment
Watch Question

Commented:
Hi,

Here is an example which comes from a project I wrote a year ago :

  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Interior.Colorindex := 15;
  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Interior.Pattern := xlSolid;
  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Borders.LineStyle := xlContinuous;
  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Borders.Weight := xlThin;
  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Borders.ColorIndex := xlAutomatic;
  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Font.Bold := True;

You will have to modify the FCurrentWorksheet by x.Workbooks[ 1 ].Worksheets[ 'Delphi Data' ].  My sample will modify the color of the cell, put the font in bold, and also set a border.

To set the properties of a specific border you will have to use something like :


  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Borders( xlEdgeBottom ).LineStyle := xlContinuous;
  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Borders( xlEdgeBottom ).Weight := xlThin;
  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Borders( xlEdgeBottom ).ColorIndex := xlAutomatic;

for the bottom border or

  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Borders( xlEdgeTop ).LineStyle := xlContinuous;
  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Borders( xlEdgeTop ).Weight := xlThin;
  FCurrentWorksheet.Cells.Item[ aRow, aCol ].Borders( xlEdgeTop ).ColorIndex := xlAutomatic;

Other possibilities are : xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlInsideHorizontal, or xlInsideVertical.

For further information I would suggest having a look at the VBA help ( open excel, press ALT + F11 to open up the VBA code editor, create a new procedure where you write activecell.borders, select borders and press F1, if the help is installed it will show you all info concerning the Borders Object ).  The VBA help seems to be a very good source, though sometimes the Delphi equivalent is somewhat different.


I hope that this information will be of some help to you.


Best regards,



Stefaan

Explore More ContentExplore courses, solutions, and other research materials related to this topic.