Last Updated:

Export documents to Excel

This article is the first in a series of articles dedicated to exporting documents to MS Excel. In it, we will look at connecting to Excel, filling cells and the simplest design of the document.

I will not delve into the theory, talk about how the OLE mechanism works, let's start with the most important thing.

Connection.

To connect to and work with Excel, we'll need a variable of type Variant:

Excel:Variant;

Next, create an OLE object:

Excel:=CreateOleObject('Excel.Application');

Add a new book:

Excel.Workbooks.Add;

Showing Excel:

Excel.Visible:=true;

We will also need constants:

const
xlContinuous=1;
xlThin=2;
xlTop = -4160;
xlCenter = -4108;

Cell text.

Now we can get to any cell as follows:

Excel.ActiveWorkBook.WorkSheets[1].Cells[1, 2]:='Cell text (1,2)'; 

Range object, range selection, cell merging, alignment.

Imagine this situation: you need to combine several cells and align the text in them in the center.

Highlight:

Excel.ActiveWorkBook.WorkSheets[1].Range['A1:G1'].Select;

Combine:

Excel.ActiveWorkBook.WorkSheets[1].Range['A1:G1'].Merge;

And align:

Excel.Selection.HorizontalAlignment:=xlCenter;

Boundaries and word wrapping.

To begin with, select the desired range and then...

Show the boundaries:

Excel.Selection.Borders.LineStyle:=xlContinuous;
Excel.Selection.Borders.Weight:=xlThin;

And turn on the hyphenation by the words:

Excel.Selection.WrapText:=true;

Example.

An example can be downloaded here

Page settings.

Let's start with the page margins. First, in order to get to the page parameters, the Excel worksheet has a PageSetup object property that we will use. To set the field sizes, you need to change the corresponding PageSetup properties, here are these properties:

  • LeftMargin - Left margin
  • RightMargin - Right margin
  • TopMargin - Top margin
  • BottomMargin - Bottom margin

The value of the field sizes must be specified in pixels, which we are not very used to, so let's use the InchesToPoints function of the Application object, which translates the value in inches to the value in pixels. Now let's write a procedure that will connect Excel and set the fields to 0.44 inches (approximately 1 cm):

procedure Connect;
var
  Excel:Variant;
begin
  Excel:=CreateOleObject('Excel.Application');
  Excel.Workbooks.Add;

  Excel.ActiveSheet.PageSetup.LeftMargin:= Excel.Application.InchesToPoints(0.44);
  Excel.ActiveSheet.PageSetup.RightMargin:= Excel.Application.InchesToPoints(0.44);
  Excel.ActiveSheet.PageSetup.TopMargin:= Excel.Application.InchesToPoints(0.44);
  Excel.ActiveSheet.PageSetup.BottomMargin:= Excel.Application.InchesToPoints(0.44);
end;

Sometimes it is useful to be able to set the orientation of the page:

  Excel.ActiveSheet.PageSetup.Orientation:= 2;

Here, the orientation value = 2, which means landscape, in portrait orientation, set orientation to 1.

You have probably more than once come across such a report in which a table with a large number of rows is placed on several pages in such cases, end-to-end rows are very convenient, they are printed on each page of the report:

  Excel.ActiveSheet.PageSetup.PrintTitleRows:='$2:$3';

Here we specify the second and third lines to print on each page.

Fonts and colors.

To set the font and text size, select the desired range and set the Name property of the Font property of the Selection object or the Size property to resize:

  Excel.ActiveWorkBook.WorkSheets[1].Range['F1'].Select;
  Excel.Selection.Font.Name:='Courier New';
  Excel.Selection.Font.Size:=18;

If you want to set a bold or, for example, oblique style of writing text, set the appropriate properties:

Excel.ActiveWorkBook.WorkSheets[1].Range['G1'].Select;
   Excel.Selection.Font.Bold:=true; // For bold text
   Excel.Selection.Font.Italic:=true; // For italic text

To specify the color of the text, change the ColorIndex property of the same Font object:

  Excel.ActiveWorkBook.WorkSheets[1].Range['A1'].Select;
  Excel.Selection.Font.ColorIndex:=3;

Here are some color indexes:

  • Index - Color
  • 0 - Auto
  • 2 - White
  • 3 - Red
  • 5 - Blue
  • 6 - Yellow
  • 10 - Green

To change the background color of a cell, use the Interior object of the Selection property:

  Excel.ActiveWorkBook.WorkSheets[1].Range['H1'].Select;
   Excel.Selection.Interior.ColorIndex:=3; // Color

Footers.

To add a header or footer to a document, just specify its content:

  Excel.ActiveSheet.PageSetup.LeftFooter:='Left Footer';
   Excel.ActiveSheet.PageSetup.CenterFooter:='Center Footer';
   Excel.ActiveSheet.PageSetup.RightFooter:='Right Footer';
   Excel.ActiveSheet.PageSetup.LeftHeader:='Left Header';
   Excel.ActiveSheet.PageSetup.CenterHeader:='Center Header';
   Excel.ActiveSheet.PageSetup.RightHeader:='Right Header';

To change the font size, add the control symbol "&" and the font size to the header or footer:

  Excel.ActiveSheet.PageSetup.LeftFooter:='&7Левый нижний колонтитул';

That's all for now. Example to the article here.