FlexCel 3.x, 5.x and cell's margin

FLX TMS FlexCel for VCL & FMX
1

Good afternoon to all,

   j got the last version of flexcel, but missing the grid.
For this reason j stil use the version 3 on delphi xe 2.
Now j've a problem (could be big for me !!), in my program j generate a repot with quickreport and, with it, j can print or convert it to pdf.
Where is the problem??
the problem is that j need to make an excel file like the the report; with selected cells with margin and so on.
At this moment j can put all the values in the rigth cells (columns title and rows value) but how j can do, for example, make a rectangle from R=1 C=1 to R=5 C=6 with it's border, inside the rectangle eliminate cells border in order to get the name and adress on 5 rows, and another one on R=1 C=10 R=3 C=13 with it's own border ??
 
J'm using the flexcell grid, flexcellimport and XLSAdapter. (version 3)
 
Thank for all your reply !!
 
Best regard
Daniele
 
2

Hi,

In FlexCel 3, to draw a border over a range of cells, you need to manually set the format of every cell to the corresponding border. While in FlexCel 5 there is a simpler way, drawing a border isn't too complex in v3 either. 
you can use a method like the following:
procedure DrawBorder(fi: TFlexCelImport; r1, c1, r2, c2: integer; color: TColor);
var
  fmt: TFlxFormat;
  r, c: integer;
begin
   //Top left corner
   fi.GetCellFormatDef(r1 , c1, fmt);
   fmt.Borders.Left.Style := fbs_Thin;
   fmt.Borders.Left.ColorIndex := fi.NearestColorIndex(color);
   fmt.Borders.Top.Style := fbs_Thin;
   fmt.Borders.Top.ColorIndex := fi.NearestColorIndex(color);
   fi.CellFormat[r1, c1] := fi.AddFormat(fmt);

   //Top right corner
   fi.GetCellFormatDef(r1 , c2, fmt);
   fmt.Borders.Right.Style := fbs_Thin;
   fmt.Borders.Right.ColorIndex := fi.NearestColorIndex(color);
   fmt.Borders.Top.Style := fbs_Thin;
   fmt.Borders.Top.ColorIndex := fi.NearestColorIndex(color);
   fi.CellFormat[r1, c2] := fi.AddFormat(fmt);

   //Bottom left corner
   fi.GetCellFormatDef(r2 , c1, fmt);
   fmt.Borders.Left.Style := fbs_Thin;
   fmt.Borders.Left.ColorIndex := fi.NearestColorIndex(color);
   fmt.Borders.Bottom.Style := fbs_Thin;
   fmt.Borders.Bottom.ColorIndex := fi.NearestColorIndex(color);
   fi.CellFormat[r2, c1] := fi.AddFormat(fmt);

   //Bottom right corner
   fi.GetCellFormatDef(r2 , c2, fmt);
   fmt.Borders.Right.Style := fbs_Thin;
   fmt.Borders.Right.ColorIndex := fi.NearestColorIndex(color);
   fmt.Borders.Bottom.Style := fbs_Thin;
   fmt.Borders.Bottom.ColorIndex := fi.NearestColorIndex(color);
   fi.CellFormat[r2, c2] := fi.AddFormat(fmt);


   //left and right lines
   for r := r1 + 1 to r2 - 1 do
   begin
     fi.GetCellFormatDef(r , c1, fmt);
     fmt.Borders.Left.Style := fbs_Thin;
     fmt.Borders.Left.ColorIndex := fi.NearestColorIndex(color);
     fi.CellFormat[r, c1] := fi.AddFormat(fmt);

     fi.GetCellFormatDef(r , c2, fmt);
     fmt.Borders.Right.Style := fbs_Thin;
     fmt.Borders.Right.ColorIndex := fi.NearestColorIndex(color);
     fi.CellFormat[r, c2] := fi.AddFormat(fmt);
   end;

   //top and bottom lines
   for c := c1 + 1 to c2 - 1 do
   begin
     fi.GetCellFormatDef(r1 , c, fmt);
     fmt.Borders.Top.Style := fbs_Thin;
     fmt.Borders.Top.ColorIndex := fi.NearestColorIndex(color);
     fi.CellFormat[r1, c] := fi.AddFormat(fmt);

     fi.GetCellFormatDef(r2 , c, fmt);
     fmt.Borders.Bottom.Style := fbs_Thin;
     fmt.Borders.Bottom.ColorIndex := fi.NearestColorIndex(color);
     fi.CellFormat[r2, c] := fi.AddFormat(fmt);
   end;

end;

 And call it like this:

procedure TForm35.FormCreate(Sender: TObject);
begin
   FlexCelImport1.NewFile(1);
   DrawBorder(FlexCelImport1, 1, 1, 5, 6, clBlack);
   FlexCelImport1.Save('r:\test.xls');
end;


I am not 100% sure I understood the question, if what you wanted was to merge cells instead, then you can use:

FlexCelImport1.MergeCells(1,1,10,10);
or similar.

But if it is for formatting (borders, cell backgrounds, etc), just get the cell format with FlexCelImport.GetCellFormatDef, modify the format, and set the cell format again (as in my example above).

If I understood wrong the question, please explain me a little more what you want to do, it is most likely you will be able to do it with FlexCel 3.

Regards,
   Adrian.
3

An exceptional piece of code. I got rid of a lot of my questions.

Thanks Adrian.