Lazarus

Other Languages => Spanish => Topic started by: jmosquera on March 01, 2018, 02:51:28 pm

Title: Formulas en FsSpreadSheet
Post by: jmosquera on March 01, 2018, 02:51:28 pm
Tengo un libro de excel en blanco, contiene formulas en algunas columnas (P hasta la Z). Cuando abro el libro y lo lleno desde un DataSet (Columnas A hasta la J), las columnas con las formulas quedan en blanco. Que puedo hacer?

Estoy empleando el siguiente codigo para llenar el libro

  while not SQLQuery1.EOF do begin
    for col := 0 to SQLQuery1.FieldCount - 1 do
     MyWorkSheet.WriteText(fil, col, SqlQuery1.Fields[col].AsString);
    inc(fil);
    SQLQuery1.Next;
   end;
Title: Re: Formulas en FsSpreadSheet
Post by: wp on March 01, 2018, 03:02:41 pm
Machine-translated:
Quote
I have a blank Excel workbook, it contains formulas in some columns (P to Z). When I open the book and fill it from a DataSet (Columns A through J), the columns with the formulas are blank. What can I do?

I am using the following code to fill the book

You don't show how you load the workbook. Probably you did not set the options needed (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Formulas).

Code: Pascal  [Select][+][-]
  1. var
  2.   MyWorkbook: TsWorkbook;
  3.   MyWorksheet: TsWorksheet;
  4. begin
  5.   MyWorkbook := TsWorkbook.Create;
  6.   try
  7.     MyWorkbook.Options := MyWorkbook.Options + [
  8.       boReadFormulas,   // necessary to read formulas
  9.       boAutoCalc        // automatically recalculate worksheet if values change, or trigger recalculation manually
  10.     ];
  11.     MyWorkbook.ReadFromFile(path_to_workbook_file, ....);
  12.     ...
Title: Re: Formulas en FsSpreadSheet
Post by: jmosquera on March 01, 2018, 03:59:20 pm
Thanks for your help, with the change the formulas are no longer erased, only that when checking the contents of the file some do not calculate anything, look:

    MyWorkBook  := TsWorkbook.Create;
    MyWorkbook.Options := MyWorkbook.Options + [
         boReadFormulas,   // necessary to read formulas
         boAutoCalc        // automatically recalculate worksheet if values change, or trigger recalculation manually
       ];
    MyWorkBook.ReadFromFile('Radicador\plantilla_radicador.xls', sfExcel8);
    MyWorkSheet := MyWorkBook.GetWorksheetByName('Radicador');
  fil := 1;

The following formulas don't work:

Column P:   =SI.ERROR(BUSCARV(S2;Variables!$K$2:$L$163;2;FALSO);"")
Column R:   =SI(J2="GD";E2;SI(J2="GC";F2;""))
Column S:   =SI(J2="GD";5;10)

In each cells appears #¡VALOR!

  col := 0;
  while not SQLQuery1.EOF do begin
    for col := 0 to SQLQuery1.FieldCount - 1 do
     MyWorkSheet.WriteText(fil, col, Trim(SqlQuery1.Fields[col].AsString));

    inc(fil);
    SQLQuery1.Next;
   end;
  MyWorkbook.WriteToFile(SaveDialog1.FileName + STR_EXCEL_EXTENSION, sfExcel8);
  MyWorkbook.Free;
  SQLQuery1.Close;




Title: Re: Formulas en FsSpreadSheet
Post by: wp on March 01, 2018, 04:26:56 pm
These formulas are not implemented, I've never seen them. Could it be that these are localized formulas (Spanish "si" = English "if")? In this case replace the SI by IF - fpspreadsheets requires English function names. If SI.ERROR is IF.ERROR then I must disappoint you: not implemented.
Title: Re: Formulas en FsSpreadSheet
Post by: jmosquera on March 01, 2018, 04:41:39 pm
The process is the following. I have an empty excel book, only some cells have formulas, and these work correctly, what I need is from Lazarus to open the original file, fill some cells so that the formulas are calculated, and finally save it with another name.
I live in Colombia (South America), the formulas appear in Spanish.
Title: Re: Formulas en FsSpreadSheet
Post by: wp on March 01, 2018, 05:00:21 pm
Yes I understand. When you enter Spanish formulas in Excel the formulas are stored in the file in English. Therefore, fpspreadsheet only has to deal with English formulas. All formulas that you write in your fpspreadsheet-Lazarus code must be in English.

I am attaching a demo which follows your steps
TinyPortal © 2005-2018