Images disappear sometimes

Hi,

we use FlexCel to create images from Excel files. First, a TFlexCelReport is prepared, then a TXlsFile is created, the Excel file is loaded, the report runs over the file, and finally a TUIImage is rendered.

We have problems with images embedded in those Excel files. Sometimes they disappear completely in the rendered TUIImage, as if they were not present in the Excel file. When they do so, ALL images are not visible.

I have seen two scenarios:

  1. When there is something with the Excel file, the export fails generally. For example, when a Excel row is too small, all images disappear. We have to adjust the height of the row, then it works.
  2. Some very rare times we have not changed the Excel file, but the images do disappear in one export. This happens only one time, all other exports before & after work as expected. I cannot reproduce this.

The question is, what can we do to make the image creation more reliable? Is there a way to detect the situation that images are not shown? Is there a way to see WHY those images are not shown?

procedure TTemplatePrinterFlexcel.DrawFromExcel(const xlsPath: string; const Bitmap: TBitmap; const Report: TFlexCelReport = nil);
   var
    xlsFile: TXlsFile;
    image: TUIImage;
    rowCount, colCount, tmpRow: Integer;
    I1: Integer;
    totalWidthInPx,
    scaleFactor: Double;
   begin
    xlsFile := TXlsFile.Create(xlsPath);
    try
      xlsFile.PrintToFit := False;
      xlsFile.PrintNumberOfHorizontalPages := 1;
      xlsFile.PrintNumberOfVerticalPages := 0;
      xlsFile.PrintHCentered := True;
      xlsFile.PrintPaperSize := TPaperSize.Undefined;

      // Erstes Sheet ausw├Ąhlen
      xlsFile.ActiveSheet := 1;

      // Ersetzung durchf├╝hren
      if Assigned(Report) then Report.Run(xlsFile);

      rowCount := xlsFile.RowCount;
      colCount := xlsFile.ColCount;

      // rowCount betrachtet nur Zellen mit Daten oder Hintergrund, daher
      // wird gepr├╝ft ob eingebettete Objekte noch weitere Zellen belegen.
      for I1 := 1 to xlsFile.ObjectCount do
         begin
          tmpRow := xlsFile.GetObjectAnchor(I1).Row2;
          if tmpRow > rowCount then
             begin
              rowCount := tmpRow;
             end;
         end;

      // Get the real Pixel width of the to-be-rendered area, so we can scale it to
      // the targeted width.
      totalWidthInPx := 0.0;
      for I1 := 1 to colCount do
         begin
          totalWidthInPx := totalWidthInPx + (xlsFile.GetColWidth(I1) / TExcelMetrics.ColMult(xlsFile));
         end;

      scaleFactor := FPrinter.MaxWidth / totalWidthInPx;

      image := xlsFile.RenderCells(
          1,             //  row1 As Integer,
          1,             //  col1 As Integer,
          rowCount,      //  row2 As Integer,
          colCount,      //  col2 As Integer,
          True,          //  drawBackground As Boolean, drawBackground true bezieht sich auf den ZellenHintergrund (nicht das
                         //  Hintergrundbild), so kann der zu rendernde Bereich durch Zellenhintergrund erweitert werden.
          91.25,         //  dpi As Double, DPI und scaleFactor legt die Rendergr├Â├če fest.
          TUISmoothingMode.None,    //  aSmoothingMode As TUISmoothingMode,
          TUIInterpolationMode.Low, //  aInterpolationMode As TUIInterpolationMode,
          False,         //  antiAliased As Boolean,
          scaleFactor,   //  aPrintScale As Double,
          False,         //  forceBackgroundTransparent As Boolean,
          True           //  exportObjects As Boolean
      );

      try
        if not Assigned(image) then raise Exception.Create('Error in rendering Cells, image not assigned.');

        Bitmap.SetSize(Round(image.Width), Round(Image.Height));
        Bitmap.Canvas.BeginScene;
        try
          image.ToNativeImage(Bitmap.Canvas, 1.00);
        finally
          Bitmap.Canvas.EndScene;
        end;
      finally
        image.Free;
      end;
    finally
      xlsFile.Free;
    end;
   end;

Thanks!
Dominik

Hi,
I am not sure on what is happening, images should be reliable, if they are supported. I see that you are using TBitmap.Canvas.BeginScene so I think this is firemonkey? Is it a Windows app or some other platform? Note that in other platforms other than Windows, EMF and WMF won't work, since the graphics engines outside windows don't understand those images.

Can you send me to adrian@tmssoftware.com some examples of Excel files that don't work so I can get a better idea? If possible, also attach the original image you used to embed.

Hi @adrian,

thanks for your response. I have created a minimal template to demonstrate the problem:

Template.xlsx (17.7 KB)

Use it as it is, and no image is printed. Give more height for row 8, and the images gets printed.

unit Unit1;

interface

uses
  System.SysUtils,
  System.Types,
  System.UITypes,
  System.Classes,
  System.Variants,

  FMX.Types,
  FMX.Controls,
  FMX.Forms,
  FMX.Graphics,
  FMX.Dialogs,
  FMX.Controls.Presentation,
  FMX.StdCtrls,

  FMX.FlexCel.Core,

  FlexCel.Report,
  FlexCel.XlsAdapter,
  FlexCel.Render;

type
  TForm1 = class(TForm)
    btn1: TButton;
    procedure btn1Click(Sender: TObject);
  private
    procedure DrawFromExcel(const xlsPath: string; const Bitmap: TBitmap; const Report: TFlexCelReport);
  end;

var
  Form1: TForm1;

implementation

{$R *.fmx}

procedure TForm1.DrawFromExcel(const xlsPath: string; const Bitmap: TBitmap; const Report: TFlexCelReport);
   var
    xlsFile: TXlsFile;
    image: TUIImage;
    rowCount, colCount, tmpRow: Integer;
    I1: Integer;
    totalWidthInPx,
    scaleFactor: Double;
   begin
    xlsFile := TXlsFile.Create(xlsPath);
    try
      xlsFile.PrintToFit := False;
      xlsFile.PrintNumberOfHorizontalPages := 1;
      xlsFile.PrintNumberOfVerticalPages := 0;
      xlsFile.PrintHCentered := True;
      xlsFile.PrintPaperSize := TPaperSize.Undefined;

      // Erstes Sheet ausw├Ąhlen
      xlsFile.ActiveSheet := 1;

      // Ersetzung durchf├╝hren
      if Assigned(Report) then Report.Run(xlsFile);

      rowCount := xlsFile.RowCount;
      colCount := xlsFile.ColCount;

      // rowCount betrachtet nur Zellen mit Daten oder Hintergrund, daher
      // wird gepr├╝ft ob eingebettete Objekte noch weitere Zellen belegen.
      for I1 := 1 to xlsFile.ObjectCount do
         begin
          tmpRow := xlsFile.GetObjectAnchor(I1).Row2;
          if tmpRow > rowCount then
             begin
              rowCount := tmpRow;
             end;
         end;

      // Get the real Pixel width of the to-be-rendered area, so we can scale it to
      // the targeted width.
      totalWidthInPx := 0.0;
      for I1 := 1 to colCount do
         begin
          totalWidthInPx := totalWidthInPx + (xlsFile.GetColWidth(I1) / TExcelMetrics.ColMult(xlsFile));
         end;

      scaleFactor := 576 / totalWidthInPx;

      image := xlsFile.RenderCells(
          1,             //  row1 As Integer,
          1,             //  col1 As Integer,
          rowCount,      //  row2 As Integer,
          colCount,      //  col2 As Integer,
          True,          //  drawBackground As Boolean, drawBackground true bezieht sich auf den ZellenHintergrund (nicht das
                         //  Hintergrundbild), so kann der zu rendernde Bereich durch Zellenhintergrund erweitert werden.
          91.25,         //  dpi As Double, DPI und scaleFactor legt die Rendergr├Â├če fest.
          TUISmoothingMode.None,    //  aSmoothingMode As TUISmoothingMode,
          TUIInterpolationMode.Low, //  aInterpolationMode As TUIInterpolationMode,
          False,         //  antiAliased As Boolean,
          scaleFactor,   //  aPrintScale As Double,
          False,         //  forceBackgroundTransparent As Boolean,
          True           //  exportObjects As Boolean
      );

      try
        if not Assigned(image) then raise Exception.Create('Error in rendering Cells, image not assigned.');

        Bitmap.SetSize(Round(image.Width), Round(Image.Height));
        Bitmap.Canvas.BeginScene;
        try
          image.ToNativeImage(Bitmap.Canvas, 1.00);
        finally
          Bitmap.Canvas.EndScene;
        end;
      finally
        image.Free;
      end;
    finally
      xlsFile.Free;
    end;
   end;

procedure TForm1.btn1Click(Sender: TObject);
   var
    Report: TFlexCelReport;
    Bitmap: TBitmap;
   begin
    Bitmap := TBitmap.Create();
    try
      Report := TFlexCelReport.Create();
      try
        Report.SetValue('BelegNummer', 123);

        DrawFromExcel('Template.xlsx', Bitmap, Report);
      finally
        Report.Free;
      end;

      Bitmap.SaveToFile(Format('print_%s_%.4d.png', [FormatDateTime('yyyymmddhhnnsszzz', Now), Random(9999)]));
    finally
      Bitmap.Free;
    end;
   end;

end.

// edit: yes, FMX. It happens on Windows and Android. FlexCel 7.13.0.0.

Thanks!
Dominik

Thanks for the file, we will investigate what is going on. Just another question, which version of Delphi are you using? In a first test I couldn't reproduce it in Delphi 11, but it does happen in XE (I tried both the first and last versions we support). Also only in FMX, which kind of points to a bug in older Delphis and FMX. We are going to check it in all supported Delphi versions anyway, but if you are seeing this in Delphi 11, then there might be something else going on that we need to investigate.

Hey @adrian, we are using Delphi 11, but without the patches / updates. I have now installed Delphi 11.1 and Delphi 11.1 Patch 1, but the problem is still there :frowning:

// edit: I have now installed FlexCel 7.14, bug is still there...

We've investigated it a little bit more, and indeed there is a bug in the RenderObject method that causes this (it had tried it with FlexCelImgExport, which is the most common way to render files, and that was ok, that's why I couldn't reproduce it).

But now it should be fixed, I hope we can finish the new release by the end of next week.

1 Like