UniDBgrid with SMExportToExcel

[jemmyhatta]

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

 

 

[delagoutte]

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;

[Harry Rogers]

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? 

[jemmyhatta]

Amazing.....it work.  Thanks.

[jemmyhatta]

 

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

[kort]

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;

[delagoutte]

it is correct. if you set addtitle to false you won't have title in your export

[p2rcoder]

is this a free component? can't find it in google...

 

also, Is there easy way to copy data from Excel and paste it in grid at unigui application?

[delagoutte]

is this a free component? can't find it in google...

 

it is not free but it is not very expensive.

http://www.scalabium.com/sme/

 

also, Is there easy way to copy data from Excel and paste it in grid at unigui application?

 

i don't know if there is an esay way

[Luciano Benito]

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 corrupted, Tell 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

[SayeyeZohor]

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?

 

 

 

[delagoutte]

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

[SayeyeZohor]

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

[SayeyeZohor]

Quote

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

Can you explain more?

[delagoutte]

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;

 

[SayeyeZohor]

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?

[delagoutte]

sorry it is the same thing : a TFDQuery

[SayeyeZohor]

tnx ...

[molla2005b]

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?

 

 

 

http://forums.unigui.com/index.php?/topic/12566-unidbgrid-how-to-export-to-xlsxmlcsv-etc/&do=findComment&comment=66920

[jahlxx]

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.

[ygerdel]

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;

 

[ygerdel]

hi,

Is there a way to export a picture to Excel with SMExport?

[SayeyeZohor]

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;

 

[SayeyeZohor]

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?

 

 

 

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