Jump to content

import & Export File Excel


knopix

Recommended Posts

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 
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 4 weeks later...

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 !

Link to comment
Share on other sites

  • Administrators

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.

Link to comment
Share on other sites

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;
 
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...