Format Col

FLX TMS FlexCel for VCL & FMX
1

Hello All.


I need help to find a way to format a collumn as number.
I am using SetCellValue to fill many collumns with a string value and, after complete, i need to change the text collumn to number.

Many thanks.
2

Hi,
Use code like this:


            XlsFile xls = new XlsFile(1, true);
            xls.SetCellValue(1, 1, 17.3);
            TFlxFormat fmt = xls.GetFormat(xls.GetColFormat(1));
            fmt.Format = "0.00";
            xls.SetColFormat(1, 1, xls.AddFormat(fmt), true);
            xls.Save("h:\\test.xlsx");





3

Sorry, after answering I realized this is the delphi forum, and the answer was for C#. But well, it is basically the same for delphi. Here is the code:


  xls := TXlsFile.Create(1, true);
  try
    xls.SetCellValue(1, 1, 17.3);
    fmt := xls.GetFormat(xls.GetColFormat(1));
    fmt.Format := '0.00';
    xls.SetColFormat(1, 1, xls.AddFormat(fmt), true);
    xls.Save('h:\test.xlsx');
  finally
    xls.Free;
  end;

4

Thank you for your help.

The problem persist because i am passing a string in SetCellValue because i don't know if the value that comes from database is a string or number.
I have to use something like: xls.SetCellValue(1, 1, '17.3');

Thanks in advance.


5

Hi,


When using SetCellValue, you can pass either a number or a string to the cell.
So you can do xls.SetCellValue(1,1,17.3) to enter a number, and xls.SetCellValue(1,1,'17.3') to enter a string.

This is the preferred way to enter values, and if your database column has a numeric column (as it should), then you can just do xls.SetCellValue(1, 1, SomeDbField.Value); and it will enter the number.

Now, if the database stores the numbers as strings, then you need to do:

xls.SetCellFromString(1,1,'17.3');

SetCellFromString behaves more like Excel, trying to guess the datatype of the string from its contents. But guessing is not an exact science, and it could lead to some subtle errors: For example the string "1/2/2000" could be jan 2 or feb 1 depending on your locale. Or a string like "1.2" could be a german date (feb 1) or the number 1.2.  Also 1.200 could be 1.2 or 1200 depending in the decimal separator.

For those reasons, if the data in the database is stored with the correct datatypes, you should use that and SetCellValue. But if you have a string stored in the database, then the only solution is to use SetCellFromString.
6

Many Thanks, Adrian.