Kama Sutra with Worksheet and Range objects in Excel

Doing search queries “delphi excel range” over and over again, I found tremendous poverty, stupidity and unprincipledness of the proposed examples of ready-made program code. At best, we are talking about a couple-three operations of outputting to an arbitrary cell, all sorts of nuances such as formatting and access to Excel objects can be ... not something to forget. The authors offer to transfer the code generated by recording the macro, taking into account the Delphi syntax, into the application code that prints the report. Moreover, in most of the examples, late binding is used, which hides some of the most terrible moments of the transfer, but this code works far from identical to the code on VBA and far from all operations are processed properly. As a result, we get unnecessarily cumbersome source code, compiled only under a certain version of Delphi / RAD Studio and working only with a certain version of the Office.

Who has not encountered such a design, parsing other people's sources?

Worksheet.Range[Worksheet.Cells[i, 1], Worksheet.Cells[i, MaxCols]].MergeCells := True; Worksheet.Range[Worksheet.Cells[i, 1], Worksheet.Cells[i, MaxCols]].HorizontalAlignment := xlCenter; Worksheet.Range[Worksheet.Cells[i, 1], Worksheet.Cells[i, MaxCols]].VerticalAlignment := xlCenter; for j := 1 to MaxCols do Worksheet.Cells[i + 1, j].Value := j; 

In this case, in a certain cycle, line by line are combined and aligned in the center of the cell from column A to MaxCols and in the next line the numbers of the columns are printed. And it seems like it works sometimes, and sometimes it does not work. For me, for example, the cells are combined, and alignment causes an error. And the cells are allocated strictly this way in any example. And what if the wrong Worksheet gets into the Range parameters? And what if we still have 100,500 operations to do with this range?

Sometimes it is saved, of course, with the construction, but it is also not perfect. Alignment in it still does not work. And not in all languages ​​it is available. The roots of evil lie in the matrix addressing of cells and in late binding. Rewrite the code for early binding:
')

 Worksheet.Range[Worksheet.Cells.Item[i, 1], Worksheet.Cells.Item[i, MaxCols]].MergeCells := True; Worksheet.Range[Worksheet.Cells.Item[i, 1], Worksheet.Cells.Item[i, MaxCols]].HorizontalAlignment := xlCenter; Worksheet.Range[Worksheet.Cells.Item[i, 1], Worksheet.Cells.Item[i, MaxCols]].VerticalAlignment := xlCenter; for j := 1 to MaxCols do Worksheet.Cells.Item[i + 1, j].Value[xlRangeValueDefault] := j; 

The nightmare continues. Added ugly .Item. However, the alignment worked. Okay, let's not talk about sad things. The real report building on Excel from Delphi opens when you get to know the Range object, or rather its Resize and Offset properties. Our piece of code turns into a rather elegant:

 //   Range := Worksheet.Cells.Item[i, 1].Resize[1, MaxCols],    :( Range := Worksheet.Range[Worksheet.Cells.Item[i, 1], EmptyParam].Resize[1, MaxCols]; Range.MergeCells := True; Range.HorizontalAlignment := xlCenter; Range.VerticalAlignment := xlCenter; Range := Range.Resize[1, 1]; for j := 1 to MaxCols do Range.Offset[1, j - 1].Value2 := j; 

For those who want to experiment, I also suggest the option of auto-filling cells:

 //   for: Range := Range.Offset[1, 0].Resize[1, 1]; Range.Value2 := 1; Range.AutoFill(Range.Resize[1, MaxCols], xlFillSeries); 

Well, in order not to bother with wrapping Worksheet.Cells.Item in Worksheet.Range, you should consider creating a report template with named ranges. This will also save on software code formatting. But about it - next time.