Incorrect formatting TimeOnly values

Aspose.Cells Product Family
1

Hello,

In my project I’m exporting some columns containing TimeOnly values. I format the cells using the Style class. For example:

var cellsFactory = new CellsFactory();
var style = cellsFactory.CreateStyle();

style.SetCustom("h:mm", false);
style.SetPatternColor(BackgroundType.Solid, Color.Yellow, Color.Yellow);
style.Font.IsBold = true;

for (int row = 0; row < 5; row++)
{
    for (int col = 0; col < 5; col++)
    {
        var cell = worksheet.Cells[row, col];
        cell.PutValue(new TimeOnly(13, 37));
        cell.SetStyle(style);
    }
}

However once opened in Excel the values appear as regular strings.
image.png (3.4 KB)

Is the TimeOnly datatype not supported?

Best regards,
Erik

Working example: timeonly.zip (801 Bytes)

2

@El_Pato_Luca,

I tested your scenario/case a bit. I used your sample code segment in a .NET6.0 (Windows) project on Windows 10 using Aspose.Cells for .NET 22.11. It works fine and the output Excel file (attached) is fine tined.
excelFile.zip (6.8 KB)

3

@Amjad_Sahi Thanks for your response.

In the excel sheet you included in your answer happens exactly what I’m posting: the output appears as a string, hence the left outlining of the values. Once you double click the value and then leave the cell Excel notices the value is a time-value and then aligns the value correctly, which is to the right.

Please see attached screenshot. After opening the Aspose output in Excel I double clicked the lower right cell and immediatly after clicked outside the cell. That’s when Excel applies the correct formatting.

image.png (23.7 KB)

4

@El_Pato_Luca,

Thanks for the screenshot and details.

I now understand your issue. The DateTime values are inserted as strings. Either you convert to DateTime by yourselves before inserting into cells. Alternatively, you may do it via relevant PutValue() overload. Please change the line of code:
cell.PutValue(new TimeOnly(13, 37));
to:
cell.PutValue(new TimeOnly(13, 37).ToString(), true);

it will work fine.

Let us know if you still have any issue.

5

The code below also works, but you must know how to convert the TimeOnly to the correct double value:

    cell.PutValue(new TimeOnly(12,34).ToTimeSpan().TotalDays);

@Aspose
Could you add an overload for PutValue(TimeOnly value) so that Aspose supports this out of the box.
Same for DateOnly

6

@hromkes,

You are doing ok, so either you may use my suggested line of code or your own line of code.

Aspose.Cells supports generic types. Since there are many Date/Time structs in .NET, so supporting each and every type might not be possible. Anyways, we will further check and update you.

7

@hromkes

We might add some overload methods for some concrete classes but we also need to take the compatibility into account for our APIs for different environments, such as the frameworks. So not all types can be supported automatically. The best way for you is to convert the value to common classes by yourselves that have been supported, such as DateTime or number, etc.

Moreover, we found TimeOnly belongs to .NET6.0, so, we cannot support it across all frameworks and currently you need to handle it by yourselves.