Calc cell type

Eike Rathke erack at redhat.com
Thu Jul 26 17:21:49 UTC 2018


Hi Vikas,

On Wednesday, 2018-07-25 10:15:26 +0530, Vikas Mahato wrote:

> I am trying to write some datatransformations related to date-time for calc.
> 
> Is there any way to determine whether a column stores date or time value? I
> was only able to find CELLTYPE_STRING, CELLTYPE_NONE, CELLTYPE_VALUE etc in
> global.hxx but none related to date or time.

As Mike already mentioned, there are none because date|datetime is just
a serial date number value, days (and time as fraction) since a given
(and configurable) null date and just the display format makes it an
actual date.

To check if a cell of CELLTYPE_VALUE or CELLTYPE_FORMULA is formatted as
date|time|datetime use

    sal_uInt32 nFormat = rDoc.GetNumberFormat( rDoc.GetNonThreadedContext(), aAdr);
    SvNumFormatType eType = rDoc.GetFormatTable()->GetType( nFormat);
    bool bIsDateOrTimeOrBoth = (eType == SvNumFormatType::DATE) ||
                               (eType == SvNumFormatType::TIME) ||
                               (eType == SvNumFormatType::DATETIME);

If you're interested in only date or only time formatted values adapt.
If the type is of type date|datetime then obtain the double value from
the cell and add it to the nulldate:

    double fValue = ...;    // time in days as stored at the cell
    DateTime aDateTime( rDoc.GetFormatTable()->GetNullDate());
    aDateTime.AddTime( fValue);

Note that a DATE formatted value may still have a fraction denoting time
in its double value, you'd need to truncate the value to calculate with
full days. Same is true for a TIME formatted value that may be >= 1.0
for a duration of more than 24 hours or in fact display only the time
portion of an actual datetime value. Hard to decide in that case.

({As an aside, the number formatter when editing such a value uses
a purely arbitrary rule to decide whether to offer a time input line
(for up to 32k hours or 3.7 years) or datetime input line (starting at
1903-09-26 for a 1899-12-30 nulldate):

    if (fabs( fValue) * 24 < 0x7fff)
        time...
    else
        datetime...
})

If you need data pilot / pivot table specific handling then take a look
at sc/source/core/data/dp*.cxx especially sc/source/core/data/dputil.cxx

  Eike

-- 
LibreOffice Calc developer. Number formatter stricken i18n transpositionizer.
GPG key 0x6A6CD5B765632D3A - 2265 D7F3 A7B0 95CC 3918  630B 6A6C D5B7 6563 2D3A
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: not available
URL: <https://lists.freedesktop.org/archives/libreoffice/attachments/20180726/4d2866f3/attachment.sig>


More information about the LibreOffice mailing list