Forum > Spanish

Formulas en FsSpreadSheet

(1/2) > >>

jmosquera:
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:
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
--- End quote ---

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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---var  MyWorkbook: TsWorkbook;  MyWorksheet: TsWorksheet;begin  MyWorkbook := TsWorkbook.Create;  try    MyWorkbook.Options := MyWorkbook.Options + [      boReadFormulas,   // necessary to read formulas      boAutoCalc        // automatically recalculate worksheet if values change, or trigger recalculation manually    ];    MyWorkbook.ReadFromFile(path_to_workbook_file, ....);    ...

jmosquera:
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:
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.

jmosquera:
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.

Navigation

[0] Message Index

[#] Next page

Go to full version