import & Export File Excel

[knopix]

helpme,Farshad Mohajeri.

how to export database to excel and how to import file excel to database?

[rencarnacion]

You can use Flexcel or  XLSreadWrite.

You can get Flexcel in tmssoftware.com and XLSReadWrite  in http://www.axolot.com/components/xlsrwii20.htm

Both of them you can export data from a DataSet  or read an Excel files 

 

Ronny Encarnacion 

[knopix]

thanks Ronny Encarnacion, there are examples of projects?

 

 

 

 

Regards

[rencarnacion]

rocedure TfrmImportarIncidencias.ProcesarHojaExcel(Res: integer);

var

  Vmes: integer;

  I:    integer;

  C:    integer;

  R:    integer;

  VColumna01: integer;

  VColumna02: integer;

begin

  case Res of

    mrYes:

    begin

      try

        UniMainModule.dbConecta.BeginTrans;

        UniMainModule.QryRelacionMeses.Open;

        UniMainModule.QryIncidenciasMensuales.Open;

        UniMainModule.QryIncidenciasMensualesTemporal.Open;

 

        with UniMainModule, QryGeneral, SQL do

        begin

          Close;

          Text := ' DELETE IncidenciasMensualesTemporal';

          ExecSQL;

 

        end;

 

        if CboxMeses.ItemIndex >= 0 then

          Vmes := CboxMeses.ItemIndex + 1

        else

          Vmes := 1;

 

        for I := Vmes to 12 do

        begin

 

          for R := 2 to XLSRead.MaxRow do

          begin

            if UniMainModule.QryRelacionMeses.Locate('mes', Vmes, []) then

            begin

              VColumna01 := UniMainModule.QryRelacionMeses.FieldByName(

                'Columna01').Value;

 

              VColumna02 := UniMainModule.QryRelacionMeses.FieldByName(

                'Columna02').Value;

 

              with UniMainModule.QryIncidenciasMensualesTemporal do

              begin

                Insert;

                FieldByName('Agno').Value     := CboxAnos.Text;

                FieldByName('IDEquipo').Value := XLSRead.CellValue[R, 1];

                FieldByName('IDProducto').Value := XLSRead.CellValue[R, 2];

                FieldByName('IdRegion').Value := XLSRead.CellValue[R, 4];

                FieldByName('Mes').Value      := I;

                FieldByName('PorcentajeAnual').Value := XLSRead.CellValue[R, 5];

                FieldByName('UnidadesAnual').Value := XLSRead.CellValue[R, 6];

                FieldByName('PrecioFarmacia').Value := XLSRead.CellValue[R, 7];

                FieldByName('IncidenciaUnidadEquipo').Value := XLSRead.CellValue[R, 8];

                FieldByName('ValorIncidenciaEquipo').Value := XLSRead.CellValue[R, 9];

                FieldByName('IncidenciaMensualEquipo').Value :=

                  XLSRead.CellValue[R, VColumna01];

                FieldByName('PorcentajeRegionEquipo').Value :=

                  XLSRead.CellValue[R, VColumna02];

                Post;

              end;

            end;

          end;

        end;

        UniMainModule.dbConecta.CommitTrans;

        if VerificaIncidencia then

        begin

          ShowMessage('Proceso Compleado con Exito');

          UniMainModule.qryBorrarIncidenciasMensuales.ExecSQL;

          UniMainModule.QryInsertaIncidencias.ExecSQL;

        end;

 

 

 

      except

        on E: Exception do

        begin

          ShowMessage('Operacion no pudo ser ejecutada por el Siguiente Mensaje :' +

            E.Message);

          UniMainModule.dbConecta.RollbackTrans;

        end;

      end;

    end;

  end;

end;

 

I'm Using Flexcel to Import Data from Excel Sheet to Database, If You need more help About It, Let me Know

 

 

Ronny Encarnacion 

[knopix]

what components you use? there are examples of sample project?

 

 

 

 

 

 

Regards

[knopix]

I've downloaded the component XLSReadWriteII 5, XLSReadWriteII 4, XLSSpreadSheet Delphi XE2, which software you use? I've installed xlsrwii5_dxe2 after  install an additional component,XLSDbRead51
XLSReadWriteII51, XLSExportHTML51 in ToolPallet

[rencarnacion]

I'm using Flexcel component from TMSsoftware,   www.tmssoftware.com

[knopix]

Do you have a component tms? may I ask if there are software of, purchase or free?

[Farshad Mohajeri]

Do you have a component tms? may I ask if there are software of, purchase or free?

 

It is not free:

 

http://www.tmssoftware.com

[knopix]

thank you for the information 

Farshad Mohajeri

 what no other alternative but to use tms component for import and export excel file?

[heromo80]

In XE2 you must activate packages Microsoft Office 2000 samples... and you will can view this page for most info.. 

 

http://delphiallimite.blogspot.com/2008/11/leyendo-datos-de-hojas-de-clculo-de.html

 

[bruno-pere]

Hi!

 

I use ADO (dbGO) to read from excel files and *I think it is possible* to write too. But I don't know and didn't test.

 

THIS CODE DOES NOT NEED EXCEL INSTALLED IN THE COMPUTER.

 

1) Put a TADOConnection;

2) Use the connection string like my ini file;

3) Inside the application, use format command to pass the the file name to connection string:

    arq is the full path file name.

    ADOConn.ConnectionString := Format(ConnStr,[arq]);
    ADOConn.Open; // CLOSE THE FILE FIRST

4) Read like this:

   ADOQryEx.Close;

   ADOQryEx.SQL.Text := 'select * from [DRE$C8:R186]';

   ADOQryEx.Open;

   value := ADOQryEx.FieldByName('F1').AsFloat;
 

And so on.

 

The connection string is something like:

 

From my ini file.

 

' ************** SOURCE **************
' OLEDB Connection (Microsoft Activex Data Objects)
' %s will be changed by the file name chosen by the user inside the application
ExcelConnString=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1"

' command sample
SourceQueryCC=select * from [Receita$C3:C3]
 

It is working fine here to read.

 

Maybe it is less work to buy a component instead if the sheet is too complex.

 

Bye!

 

Bruno

[knopix]

thank you very much

agmoro7622

for you helping me

 

 

Regards

[knopix]

thank you very much Bruno,there are examples of projects that I'm no sample projects?

 

 

 

 

 

 

Regards

[knopix]

hi

agmoro7622

What applications can be used with component unigu webbase ExcelApplication1?export database to excel and import excel file to database.thanks

[knopix]

Hi

I still can not import data and export data to excel anyone can help to provide a sample project in unigui?

 

 

 

 

Regards

[alfhabetagama]

Hi

I still can not import data and export data to excel anyone can help to provide a sample project in unigui?

 

 

 

 

Regards

I have the same problem, export to excel. Could anyone help me?

[estrify]

Accidentally, I found this page and I remembered this post. I have not tested or tried to do anything with it, but if anyone is interested, it could be a good starting point to investigate...

Regards,

 

https://github.com/stormtek/Ext.ux.Exporter

[knopix]

Thank you very much Estrify

 

 

 

 

 

Regards

[mmurgas]

could you give an example of the latter, I know delphi, but very no java

 

thanks

 

----

 

http://www.delphiaccess.com/forum/trucos-y-consejos-16/exportar-ado-a-csv/

[knopix]

how to import excel file into mysql database in a table? whether taken per sheet or all sheets? when in the excel file there are 3 sheets all existing data files as well as whether it can be imported into mysql table? help me

 

 

 

 

 

 

 

Regards

[bruno-pere]

knopix, my example in this thread is what you want...

 

put two adoconnections in the form, one to your MySQL database and the other to your excel file (see my comment to know the connection string).

 

put two adoqueries in the form, one to your MySQL table linked to your MySQL adoconnection and the other linked to your Excel Adoconnection.

 

open both connections.

 

open boths queries.

 

loop excel query while inserting in mysql query.

 

Sure there are other ways, but you can start doing this.

 

bye!

 

Bruno

[adragan]

Export to Excel is a big problem. My experience with it is quite bad.

3 users calling in the same time some report in Excel crush down a quad processor server.

If they don't limit the no or records returned you can wait for 30 min and nothing happenes

webapp goes in session timeout, not to mention sharing viloation and other errors.

 

The solution , if I can call it like that, is to generate *,csv files and send them to the client.

They get 100 times faster generated and Excel knows how to play with them.

Simple, clear and fast ! Who wants more can play locally with colors, lines sorting and such.

 

 

Here is a piece of code :

 

 

 

procedure TUniMainModule.ExportExcel(ADataSet:TDataSet);

  var

             ts:TStringList;

            sir:string;

            i,j:integer;

       FileName:string;

            csv:string;

  begin

  if ADataSet.Active then

     begin

     ts:=TStringList.Create;

     try

        with ADataSet do

             begin

             DisableControls;

             First;

             j:=Fields.Count;

             while not EOF do

                   begin

                   sir:='';

                   for i:= 0 to j - 1 do sir:=sir + Fields.AsString+',';

                   ts.Add(sir);

                   Next;

                   end;

             EnableControls;      

             end;

     FileName := 'F' + FormatDateTime('hhmmss', Now()) + '.csv'; // Create a unique name for report.

     ts.SaveToFile(UniServerModule.LocalCachePath + FileName);

     finally

        ts.Free;

     end;

 

     csv:=UniServerModule.LocalCachePath + FileName;

     UniMainModule.SendFile(csv);

     end;

  end;

 

 

That's all !

[Farshad Mohajeri]

Export to Excel is a big problem. My experience with it is quite bad.

3 users calling in the same time some report in Excel crush down a quad processor server.

If they don't limit the no or records returned you can wait for 30 min and nothing happenes

webapp goes in session timeout, not to mention sharing viloation and other errors.

 

Your export component should not use MS Excel itself to export. It must use native code to do the task such as TMS Flexcel.

[AlbertoVesx]

This is how I export a dataset to excel using flexcel from tmssoftware. This example has the basic.

var
  FileName: string;
  XLS: TExcelFile;
  Format: Tflxformat;
  F1: integer;
  Row: integer;
begin
  if not DataSet.IsEmpty then
  begin
    XLS := TXlsFile.Create(True);
    try
      XLS.NewFile;
      format := XLS.getDefaultFormat;
      format.Font.Name := 'Times New Roman';
      format.Font.Color := clwhite;
      format.FillPattern.Pattern := TFlxPatternStyle.Solid;
      format.FillPattern.FgColor := $00AE631E;
     
      F1 := archivoxls.AddFormat(formato);
      //Apply f1 format to the first row and first two columns
      XLS.SetCellFormat(1,1,f1);
      XLS.SetCellFormat(1,2,f1);
      // set name of the fields
      XLS.SetCellValue(1, 1, 'NAME FIELD ONE');
      XLS.SetCellValue(1, 2, 'NAME FIELD TWO');
    
      row := 2;
      DataSet.First;
      while NOT DataSet.Eof do
      begin
        XLS.SetCellValue(row, 1, DateToStr(DataSet['FIELDONE'])); //TYPE DATE
        XLS.SetCellValue(row, 2, DataSet['FIELDTWO']);            //TYPE STRING
        Inc(row);
        DataSet.Next;
      end;
      FileName := FormatDateTime('YYMMDD_hhmmnnss',now)+username+'.xls';
      XLS.Save(UniServerModule.LocalCachePath + filename);
      UniSession.SendFile(UniServerModule.LocalCachePath + filename);
    finally
      FreeAndNil(XLS);
    end;
  end;