* * *

Author Topic: Formulas en FsSpreadSheet  (Read 921 times)

jmosquera

  • Newbie
  • Posts: 3
Formulas en FsSpreadSheet
« 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;

wp

  • Hero Member
  • *****
  • Posts: 4602
Re: Formulas en FsSpreadSheet
« Reply #1 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.     ...
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

jmosquera

  • Newbie
  • Posts: 3
Re: Formulas en FsSpreadSheet
« Reply #2 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;





wp

  • Hero Member
  • *****
  • Posts: 4602
Re: Formulas en FsSpreadSheet
« Reply #3 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

jmosquera

  • Newbie
  • Posts: 3
Re: Formulas en FsSpreadSheet
« Reply #4 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.

wp

  • Hero Member
  • *****
  • Posts: 4602
Re: Formulas en FsSpreadSheet
« Reply #5 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
  • File "template.xlsx" is a "template" file. It has an "IF" formula in cell B1: if cell A1 is equal to "Test" then B1 should display "Test found", otherwise "Test NOT found". If you open "template.xlsx" in Excel, B2 will show "Test NOT found" because A1 is empty.
  • Run the attached demo. It loads the template and writes the text "Test" into cell A1, and stores is as "changed.xlsx".
  • If you open "changed.xlsx" in Excel, cell B1 displays "Test found" because cell A1 is "Test" now.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus