XLS to PDF #VALUE

FLX TMS FlexCel for VCL & FMX
1

Using Delphi 10.3.3

With Flexcel, I have open an existing XLS with simple formulas, filled it with values and the save xls is great.  I also created a PDF with Flexcel and the PDF shows #VALUE while the XLS shows the correct amount.  The difference is the first value has a bracket for negatives.


(100,000)     50,000     #VALUE     << shows up in PDF   (formula was A1-B1)
(100,000)     50,000     (50,000)   << shows up in XLS      

the (100,000) is entered in as SetCellValue(1,1,'(100,000)')

Why does it work fine in Excel but when going to the PDF it gives an error?
Thank you.

Benny


2

Hi,

One problem is that SetCellValue(1,1,'(100,000)') will enter the string "(100,000)" into A1, not the number -100,000.
Can you try doing instead:



fmt := xls.GetCellVisibleFormatDef(1, 1);
fmt.Format := '#,##0 _$;-#,##0 _$';
Xls.SetCellValue(1, 1, -100000, xls.AddFormat(fmt));

This should enter the number -100000, not the string, and the formulas should work. Note that I used a default format string for showing negative numbers in Excel: If you want a different format just create an empty file in Excel, set the cell format of cell A1 to what you want, and open the file in APIMate to get the format string (that's what I did above by the way)
3

Thank you for the quick response.  I will give it a try.

4

Is there any funtion that Flexcel can take a 

B1 = (10,000)
C1 = 5

D1 = B1 * C1

Excel = (50,000)
Flexcel = #VALUE!

I tried 
D1 = Value(B1) * Value(C1)
Excel = (50,000)
Flexcel = #VALUE!

5

Hi,

Can you send me the file to adrian@tmssoftware.com so I can verify the problem you are having?
6

Your previous post has been helpful as I have been able to use that as a workaround, but I will send you the file because it will come again as an issue.  But, in the future releases, maybe this can be fixed since it seems really simple.  My code has to do a lot of extra work now to make it work, but at least it will work!