Jump to content

UniDBgrid with SMExportToExcel


jemmyhatta

Recommended Posts

Hi All,

 

 

I have some need to try that component to export and import from DBGrid's data.

 

The result when I run the web-apps is the error message "Error in data export : Invalid class type class" which is showed up after running "execute export".

 

I there anyone in this forum ever used TSMExportToExcel component with UniDBgrid? Please share your experiences, and maybe some tips for me? Or any other solutions, using another component that suitable for export and import datas from UniDBgrid? Thanks.

 

 

 

 

Best Regards,

 

 

Jemmy Hatta

 

 

Link to comment
Share on other sites

i use it with dataset. and for column title i use Mydataset.Fields.DisplayLabel := myTitleColumn

if you deploy as dll. don't forget :

SMExport.Options:=SMExport.Options-[soShowMessage];

SMExport.AnimatedStatus:=false;

 

sample of my export function :

function TExportAO.SaveToXls : boolean;
var
  SMExport: TSMExportToXLS;
begin
  result:=false;
  SMExport := TSMExportToXLS.Create(nil);
  try
    SMExport.AnimatedStatus:=false;
    SMExport.Options:=SMExport.Options-[soShowMessage];
    SMExport.DataSet:= GenQuery1;//genquery1 is a fdquery i use firedac
    SMExport.ColumnSource := csDataSet;
    SMExport.AddTitle := True;
    SMExport.FileName := fFileName;
    SMExport.KeyGenerator:='My Company Name';
    GenTitleLabel;//this function set displaylabel for each column
    SMExport.Execute;
    result := (SMExport.ExportResult = erCompleted);
  finally
    SMExport.Free;
  end;
end;
Link to comment
Share on other sites

I also use the SMExport suite. I've not had any issues with it.

My export routine is pretty much as above

the dataset in my case is an Advantage Tdataset descendant.

 

If you add a designtime TXLSExport to  a datamodule and set its properties can you run the execute method (right click on the component) in the ide? 

Link to comment
Share on other sites

 

i use it with dataset. and for column title i use Mydataset.Fields.DisplayLabel := myTitleColumn

if you deploy as dll. don't forget :

SMExport.Options:=SMExport.Options-[soShowMessage];

SMExport.AnimatedStatus:=false;

 

sample of my export function :

function TExportAO.SaveToXls : boolean;
var
  SMExport: TSMExportToXLS;
begin
  result:=false;
  SMExport := TSMExportToXLS.Create(nil);
  try
    SMExport.AnimatedStatus:=false;
    SMExport.Options:=SMExport.Options-[soShowMessage];
    SMExport.DataSet:= GenQuery1;//genquery1 is a fdquery i use firedac
    SMExport.ColumnSource := csDataSet;
    SMExport.AddTitle := True;
    SMExport.FileName := fFileName;
    SMExport.KeyGenerator:='My Company Name';
    GenTitleLabel;//this function set displaylabel for each column
    SMExport.Execute;
    result := (SMExport.ExportResult = erCompleted);
  finally
    SMExport.Free;
  end;
end;

Amazing.....It Work.  Thanks a lot

Link to comment
Share on other sites

  • 7 months later...

When I pass a Client Data to Set having columns Names to be exported to an excel file;

Columns names will be displayed in the first row of the excel file .

I want to escape this behavior I mean I don't want to see the columns displayed in first row of the excel sheet

Is it possible ? did that happen if I set the property  SMExport.AddTitle := False;

Link to comment
Share on other sites

Hello, to export I use the following function.

 
procedure GerarExcel(Consulta:TPgQuery);

 

 

Procedure TMainForm.GerarExcel(Consulta:TPgQuery);

var
  coluna, linha: integer;
  excel: variant;
  valor: string;
  ArquivoDownload: string;
begin
  Application.ProcessMessages;
  DM.qryTemp1.Close;
  DM.qryTemp1.SQL.Clear;
  DM.qryTemp1.SQL.Add('select field1 as NameField1, field2 as NameField2');
  DM.qryTemp1.SQL.Add('from   NameTable');
  DM.qryTemp1.SQL.Add('where (Field1 = :p_Field1)');
  DM.qryTemp1.ParamByName('p_Field1').AsInteger := NameParameter;
  DM.qryTemp1.Open;
  if (not DM.qryTemp1.IsEmpty) then
  begin
    try
      excel := CreateOleObject('Excel.Application');
      excel.Workbooks.add(1);
    except
      Application.MessageBox('Excel Not installed or corruptedTell the support!!!', 'attention', MB_OK + MB_ICONEXCLAMATION + MB_DEFBUTTON1 + MB_SYSTEMMODAL);
      Abort;
    end;
 
    Consulta.First;
    try
      for linha := 0 to Consulta.RecordCount-1 do
      begin
        for coluna := 1 to Consulta.FieldCount do 
        begin
          valor:= Consulta.Fields[coluna-1].AsString;
          excel.cells [linha + 2, coluna] := valor;
        end;
        Consulta.Next;
      end;
 
      for coluna := 1 to Consulta.FieldCount do 
      begin
        valor := Consulta.Fields[coluna - 1].DisplayLabel;
        excel.cells[1,coluna] := valor;
      end;
 
      excel.columns.AutoFit; 
      excel.rows.AutoFit; 
 
      ArquivoDownload := ExtractFilePath(UniServerModule.LocalCachePath) + 'Metadados_' + 'hs' + FormatDateTime('HHMMSS',Now) + '.xls';
      Excel.WorkBooks[1].Sheets[1].SaveAs(ArquivoDownload);///Salvar em Cache
 
      UniSession.SendFile(ArquivoDownload);///Fazer o download  do arquivo
//      excel.visible := True;///Abrir o Excel
    except
      Application.MessageBox('Failed to Generate Metadata, Tell Technical Support !!!', 'attention', MB_OK + MB_ICONEXCLAMATION + MB_DEFBUTTON1 + MB_SYSTEMMODAL);
      Abort;
    end;
  end;
end;

 

I hope I have collaborated

Link to comment
Share on other sites

  • 2 years later...
On 11/29/2016 at 12:50 PM, delagoutte said:

i use it with dataset. and for column title i use Mydataset.Fields.DisplayLabel := myTitleColumn

if you deploy as dll. don't forget :

SMExport.Options:=SMExport.Options-[soShowMessage];

SMExport.AnimatedStatus:=false;

 

sample of my export function :


function TExportAO.SaveToXls : boolean;
var
  SMExport: TSMExportToXLS;
begin
  result:=false;
  SMExport := TSMExportToXLS.Create(nil);
  try
    SMExport.AnimatedStatus:=false;
    SMExport.Options:=SMExport.Options-[soShowMessage];
    SMExport.DataSet:= GenQuery1;//genquery1 is a fdquery i use firedac
    SMExport.ColumnSource := csDataSet;
    SMExport.AddTitle := True;
    SMExport.FileName := fFileName;
    SMExport.KeyGenerator:='My Company Name';
    GenTitleLabel;//this function set displaylabel for each column
    SMExport.Execute;
    result := (SMExport.ExportResult = erCompleted);
  finally
    SMExport.Free;
  end;
end;

hi, tnx

 

1- How to export only fields that are visible in DbGrid?

2- Why can't they be edited in Excel after being exported?

 

image.thumb.png.14cb8a15466a188b36964e2669594b46.png

 

 

Link to comment
Share on other sites

On 9/6/2019 at 11:17 AM, SayeyeZohor said:

1- How to export only fields that are visible in DbGrid?

i use a tstringlist with all field that are visible in the grid

After, i modify the dataset with a code like this :

MyQuery.Fields[i].Visible := (VisibleField.IndexOf(UpperCase(MyQuery.Fields[i].FieldName))<>-1);

 

On 9/6/2019 at 11:17 AM, SayeyeZohor said:

2- Why can't they be edited in Excel after being exported?

proposable because you open directly the file from you browser- > excel open the files in protected mode

Link to comment
Share on other sites

1 hour ago, delagoutte said:

i use a tstringlist with all field that are visible in the grid 

After, i modify the dataset with a code like this :


MyQuery.Fields[i].Visible := (VisibleField.IndexOf(UpperCase(MyQuery.Fields[i].FieldName))<>-1);

 

proposable because you open directly the file from you browser- > excel open the files in protected mode 

proposable because you open directly the file from you browser- > excel open the files in protected mode 

 

No it's not

Link to comment
Share on other sites

exemple :

myquery1.sql.text := 'select filed1, field2, field3 from mytable';
myquery = fdquery that is linked to a datasaource that is linked to my grid.
visiblefield.text := 'field3'+#13#10+'field2';  // it is a tstringlist, only field2 and field3 are visible on my grid and the order in the list is the order in the grid

    for i:= 0 to GenQuery1.FieldCount-1 do
    begin
      if (VisibleField.IndexOf(UpperCase(GenQuery1.Fields[i].FieldName))<>-1) then
      begin
        GenQuery1.Fields[i].Visible := True;
      end
      else
      begin
        GenQuery1.Fields[i].Visible := false;
      end;
    end;
// if a field is not visible , it is not exported
//reorder column in same order that in the grid
    for i := 0 to VisibleField.Count-1 do
    begin
      if GenQuery1.FieldDefs.IndexOf(VisibleField.Strings[i])<>-1 then
      begin
        GenQuery1.FieldByName(VisibleField.Strings[i]).Index := i;
      end;
    end;

 

  • Like 1
Link to comment
Share on other sites

7 hours ago, delagoutte said:

exemple :


myquery1.sql.text := 'select filed1, field2, field3 from mytable';
myquery = fdquery that is linked to a datasaource that is linked to my grid.
visiblefield.text := 'field3'+#13#10+'field2';  // it is a tstringlist, only field2 and field3 are visible on my grid and the order in the list is the order in the grid

    for i:= 0 to GenQuery1.FieldCount-1 do
    begin
      if (VisibleField.IndexOf(UpperCase(GenQuery1.Fields[i].FieldName))<>-1) then
      begin
        GenQuery1.Fields[i].Visible := True;
      end
      else
      begin
        GenQuery1.Fields[i].Visible := false;
      end;
    end;
// if a field is not visible , it is not exported
//reorder column in same order that in the grid
    for i := 0 to VisibleField.Count-1 do
    begin
      if GenQuery1.FieldDefs.IndexOf(VisibleField.Strings[i])<>-1 then
      begin
        GenQuery1.FieldByName(VisibleField.Strings[i]).Index := i;
      end;
    end;

 

myquery1
myquery
GenQuery1

What are these?

Link to comment
Share on other sites

  • 4 weeks later...

Hi.

 

testing SMExport, is OK.

If create the component in runtime, no problem, but  when place a excelexport component (non visual) in a form, I get this error:

Cannot assign a nil to a TFont

 

Anyone knows to solve it?

 

Thanks.

Link to comment
Share on other sites

Hi,

In this way I add a color to the cell, how could I do to add color to the entire row?


OnGetetCellParams(Sender: TObject;
  Field: TField; var Text: WideString; AFont: TFont; var Alignment: TAlignment;
  var Background: TColor;  var CellType: TCellType);

if Assigned(Field) and
   SameText(Field.FieldName, 'Empleado') and
   (Field.AsInteger > 48) then
    Background := clBlue;

 

Link to comment
Share on other sites

  • 2 weeks later...
  • 7 months later...
FUNCTION  TUniCheckListNezaratMaster2.SaveToXls : boolean;
var
  SMExport: TSMExportToXLS;
BEGIN
  result   := false;
  SMExport := TSMExportToXLS.Create(nil);
  TRY
    SMExport.AnimatedStatus := false;
    SMExport.Options        := SMExport.Options-[soShowMessage];
    SMExport.DataSet        := UQBazres;        //genquery1 is a fdquery i use firedac
    SMExport.ColumnSource   := csDataSet;
    SMExport.AddTitle       := True;
    SMExport.FileName       := 'filenameeee';   //fFileName;
    SMExport.KeyGenerator   := 'My Company Name';
    //GenTitleLabel;                            //this function set displaylabel for each column
    SMExport.Execute;
    result := (SMExport.ExportResult = erCompleted);
  FINALLY
    SMExport.Free;
  END;
END;

 

Link to comment
Share on other sites

On 9/6/2019 at 1:47 PM, SayeyeZohor said:

hi, tnx

 

1- How to export only fields that are visible in DbGrid?

2- Why can't they be edited in Excel after being exported?

 

image.thumb.png.14cb8a15466a188b36964e2669594b46.png

 

 

Excel / File / Options / Trust Center / Trust Center Setting / File Block Setting /

Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...