(SOLVED) Excel export

[xinyiman]

Question, I have a recordset export to excel, how can I do with lazarus? There is a method of cross-platform?

[garlar27]

Excel is not cross-platform.

There's an Office Automation in the wiki.

[JD]

Try FpSpreadsheet. Remember that both OpenOffice & LibreOffice read & write Excel (xls)files

http://wiki.lazarus.freepascal.org/FPSpreadsheet

You don't need to have Excel installed AFAIK.

JD

[xinyiman]

Thank you

[xinyiman]

I built the following unit, but I get the following error why?

myexcel.pas(1,1) Fatal: Can't find unit fpspreadsheet used by MyExcel

Code: [Select]

unit MyExcel;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, fpspreadsheet, fpsallformats, fpspreadsheet_pkg, sqldb;

const OUTPUT_FORMAT = sfExcel5;

function ExportToExcel(MiaQuery: string; MyDir: string; NomeFile: string; NomeFoglio: string): boolean;

implementation

     function ExportToExcel(MiaQuery: string; MyDir: string; NomeFile: string; NomeFoglio: string): boolean;
     var
      ret: BOOLEAN;
        rst: TSQLQuery;
        MyWorkbook: TsWorkbook;
        MyWorksheet: TsWorksheet;
        MyFormula: TsRPNFormula;
        Riga, i, Qta: integer;
     begin
           ret:=TRUE;
             try
               try
                  rst.Create;
                  rst.SQL.Text:=MiaQuery;
                  rst.Open;
                  if not rst.EOF then
                  begin
                        Riga:=0;
            // Create the spreadsheet
            MyWorkbook := TsWorkbook.Create;
            MyWorksheet := MyWorkbook.AddWorksheet(NomeFoglio);
            Qta:=rst.Fields.Count;
                        //stampo la testata
                        for i:=0 to Qta-1 do
                        begin
                             MyWorksheet.WriteUTF8Text(Riga, i, rst.Fields.FieldByNumber(i).Name);
                        end;
                        Inc(Riga);
            //stampo i dati sul foglio excel
rst.First;
while not rst.EOF do
begin
                             for i:=0 to Qta-1 do
                             begin
                                  if (rst.Fields.FieldByNumber(i).DataType=ftFloat) then
                                       MyWorksheet.WriteNumber(0, 0, rst.Fields.FieldByNumber(i).AsFloat)
                                  else if (rst.Fields.FieldByNumber(i).DataType=ftInteger) then
                                       MyWorksheet.WriteNumber(0, 0, rst.Fields.FieldByNumber(i).AsInteger)
                                  else if (rst.Fields.FieldByNumber(i).DataType=ftString) then
                                       MyWorksheet.WriteUTF8Text(Riga, i, rst.Fields.FieldByNumber(i).AsString);
                             end;
                             Inc(Riga);
     rst.Next;
end;

            // Save the spreadsheet to a file
            MyWorkbook.WriteToFile(MyDir + NomeFile + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
            MyWorkbook.Free;
   end
   else
   begin
    ret:=FALSE;
   end;
               rst.Close;
                   rst.Free;
              finally                               
        end;
      except
            ret:=FALSE;
            end;
      end;
      ExportToExcel:=ret;
     end;

end.



[JD]

I built the following unit, but I get the following error why?

myexcel.pas(1,1) Fatal: Can't find unit fpspreadsheet used by MyExcel

Code: Pascal  [Select][+][-]

1. unit MyExcel;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, fpspreadsheet, fpsallformats, fpspreadsheet_pkg, sqldb;
9.  
10. const OUTPUT_FORMAT = sfExcel5;
11.  
12. function ExportToExcel(MiaQuery: string; MyDir: string; NomeFile: string; NomeFoglio: string): boolean;
13.  
14. implementation
15.  
16.      function ExportToExcel(MiaQuery: string; MyDir: string; NomeFile: string; NomeFoglio: string): boolean;
17.      var
18.         ret: BOOLEAN;
19.         rst: TSQLQuery;
20.         MyWorkbook: TsWorkbook;
21.         MyWorksheet: TsWorksheet;
22.         MyFormula: TsRPNFormula;
23.         Riga, i, Qta: integer;
24.      begin
25.              ret:=TRUE;
26.              try
27.                try
28.                   rst.Create;
29.                   rst.SQL.Text:=MiaQuery;
30.                   rst.Open;
31.                   if not rst.EOF then
32.                   begin
33.                         Riga:=0;
34.                         // Create the spreadsheet
35.                         MyWorkbook := TsWorkbook.Create;
36.                         MyWorksheet := MyWorkbook.AddWorksheet(NomeFoglio);
37.                         Qta:=rst.Fields.Count;
38.                         //stampo la testata
39.                         for i:=0 to Qta-1 do
40.                         begin
41.                              MyWorksheet.WriteUTF8Text(Riga, i, rst.Fields.FieldByNumber(i).Name);
42.                         end;
43.                         Inc(Riga);
44.                         //stampo i dati sul foglio excel
45.                         rst.First;
46.                         while not rst.EOF do
47.                         begin
48.                              for i:=0 to Qta-1 do
49.                              begin
50.                                   if (rst.Fields.FieldByNumber(i).DataType=ftFloat) then
51.                                        MyWorksheet.WriteNumber(0, 0, rst.Fields.FieldByNumber(i).AsFloat)
52.                                   else if (rst.Fields.FieldByNumber(i).DataType=ftInteger) then
53.                                        MyWorksheet.WriteNumber(0, 0, rst.Fields.FieldByNumber(i).AsInteger)
54.                                   else if (rst.Fields.FieldByNumber(i).DataType=ftString) then
55.                                        MyWorksheet.WriteUTF8Text(Riga, i, rst.Fields.FieldByNumber(i).AsString);
56.                              end;
57.                              Inc(Riga);
58.                              rst.Next;
59.                         end;
60.  
61.                         // Save the spreadsheet to a file
62.                         MyWorkbook.WriteToFile(MyDir + NomeFile + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
63.                         MyWorkbook.Free;
64.                    end
65.                    else
66.                    begin
67.                         ret:=FALSE;
68.                    end;
69.                    rst.Close;
70.                    rst.Free;
71.                 finally                                                
72.                 end;
73.                 except
74.                         ret:=FALSE;
75.                 end;
76.         end;
77.         ExportToExcel:=ret;
78.      end;
79.  
80. end.
81.  

Is the path to fpspreadsheet explicitly included in the Compiler Options -> Paths -> Other Unit Files?

[xinyiman]

Solved with this (my) code

Code: [Select]

unit MyExcel;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, fpspreadsheet, fpsallformats, sqldb, db;

const OUTPUT_FORMAT = sfExcel5;

function ExportToExcel(MiaQuery: TDataSource; MyDir: string; NomeFile: string; NomeFoglio: string; var Errore: string): boolean;

implementation

     function ExportToExcel(MiaQuery: TDataSource; MyDir: string; NomeFile: string; NomeFoglio: string; var Errore: string): boolean;
     var
      ret: BOOLEAN;
        rst: TDataSet;
        MyWorkbook: TsWorkbook;
        MyWorksheet: TsWorksheet;
        Riga, i, Qta: integer;
     begin
             Errore:='';
           ret:=TRUE;
             try
               try
                  rst:=MiaQuery.DataSet;
                  if not rst.EOF then
                  begin
                        Riga:=0;
            // Create the spreadsheet
            MyWorkbook := TsWorkbook.Create;
            MyWorksheet := MyWorkbook.AddWorksheet(NomeFoglio);
            Qta:=rst.Fields.Count;
                        //stampo la testata
                        for i:=0 to Qta-1 do
                        begin
                             if (rst.FieldDefs.Items[i].Name='') then
                                MyWorksheet.WriteUTF8Text(Riga, i, 'Campo' + IntToStr(i))
                             else
                                 MyWorksheet.WriteUTF8Text(Riga, i, rst.FieldDefs.Items[i].Name);
                             MyWorksheet.WriteUsedFormatting(Riga, i, [uffBold]); //metto la cella in grassetto
                        end;
                        Inc(Riga);
            //stampo i dati sul foglio excel
rst.First;
while not rst.EOF do
begin
                             for i:=0 to Qta-1 do
                             begin
                                  if (rst.Fields[i].DataType=ftFloat) then
                                       MyWorksheet.WriteNumber(0, 0, rst.Fields[i].AsFloat)
                                  else if (rst.Fields[i].DataType=ftInteger) then
                                       MyWorksheet.WriteNumber(0, 0, rst.Fields[i].AsInteger)
                                  else if (rst.Fields[i].DataType=ftString) then
                                       MyWorksheet.WriteUTF8Text(Riga, i, rst.Fields[i].AsString);
                             end;
                             Inc(Riga);
     rst.Next;
end;

            // Save the spreadsheet to a file
            MyWorkbook.WriteToFile(MyDir + NomeFile + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
            MyWorkbook.Free;
   end
   else
   begin
    ret:=FALSE;
   end;
              finally                               
        end;
      except
                        on E: Exception do
                        begin
                             Errore:=E.Message;
                             ret:=FALSE;
                        end;
            end;
                ExportToExcel:=ret;
      end;

end.



[felipemdc]

Is the path to fpspreadsheet explicitly included in the Compiler Options -> Paths -> Other Unit Files?

One can also add the package to the project

[xinyiman]

In what sense?

[felipemdc]

In what sense?

See here: http://wiki.lazarus.freepascal.org/Lazarus_Packages#Use_your_package_in_your_project

[xinyiman]

Ok, the problem is that it has the following dependencies and do not know if the game worth the candle. I do if someone takes the time to package it and I am happy I authorize (must be mentioned that the code is mine). All of them

dependencies:

fpspreadsheet, fpsallformats, sqldb, db

[fabiopesaju]

thankyou xinyiman, your code help me so much

[wp]

During the three years since the original posting a lot has changed with fpspreadsheet. See http://wiki.lazarus.freepascal.org/FPSpreadsheet.

[fabiopesaju]

I only modified / added the following lines:

Code: [Select]

            if (rst.Fields[i].DataType = ftFloat) then
              MyWorksheet.WriteNumber(Riga, i, rst.Fields[i].AsFloat)
            else if (rst.Fields[i].DataType = ftInteger) then
              MyWorksheet.WriteNumber(Riga, i, rst.Fields[i].AsInteger)
            else if (rst.Fields[i].DataType = ftString) then
              MyWorksheet.WriteUTF8Text(Riga, i, rst.Fields[i].AsString)
            else if (rst.Fields[i].DataType = ftDateTime) then
              MyWorksheet.WriteDateTime(Riga, i, rst.Fields[i].AsDateTime, 'dd-mm-yyyy')
            else if (rst.Fields[i].DataType = ftDate) then
              MyWorksheet.WriteDateTime(Riga, i, rst.Fields[i].AsDateTime, 'dd-mm-yyyy'); 

do I need to change something else? something important?

[wp]

do I need to change something else?

No, I think that's fine. I just wanted to say that, for example, there is an export component now which frees you from iterating through the dataset, checking the field types etc. (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Dataset_export)