Free Support Forum - aspose.com

Time Format

I was wondering if someone could help me. I'm pulling my hair out on this one. I'm currently populating a predifined excel spreadsheet that has a few fields that has a format of Cool [H]:mm:ss, Style.Number = 46. When i put the data, a string, into the field it doesn't keep the format even if I define it. Here's a section of the code:

excel.Worksheets("Sheet1").Cells(CellRow, CellCol).Style.Number = 46 'formats Daily

excel.Worksheets("Sheet1").Cells(CellRow, CellCol + 1).Style.Number = 46 'formats MTD

excel.Worksheets("Sheet1").Cells(CellRow, CellCol).Style.HorizontalAlignment = TextAlignmentType.Right

If Not IsDBNull(MyReader("DSTAT")) Then excel.Worksheets("Sheet1").Cells(CellRow, CellCol).PutValue(Trim(MyReader("DSTAT"))) ' + ":00")

excel.Worksheets("Sheet1").Cells(CellRow, CellCol + 1).Style.HorizontalAlignment = TextAlignmentType.Right

If Not IsDBNull(MyReader("MTDSTAT")) Then excel.Worksheets("Sheet1").Cells(CellRow, CellCol + 1).PutValue(Trim(MyReader("MTDSTAT"))) ' + ":00")

Am i doing something wrong?

Hi,

Thanks for considering Aspose.

Could you please post your template file with source code here, so that we may sort out the problem.

And by the way are you using Aspose.Cells (4.0.3).

Thanks for your patience.

Regards

Amjad Sahi

Aspose Nanjing Team

I'm actually using Aspose.Excel v3.5.1.0.

Code:

excel.Worksheets("Sheet1").Cells(CellRow, CellCol).Style.HorizontalAlignment = TextAlignmentType.Right

If Not IsDBNull(MyReader("DSTAT")) Then excel.Worksheets("Sheet1").Cells(CellRow, CellCol).PutValue(Trim(MyReader("DSTAT"))) ' + ":00")

excel.Worksheets("Sheet1").Cells(CellRow, CellCol + 1).Style.HorizontalAlignment = TextAlignmentType.Right

If Not IsDBNull(MyReader("MTDSTAT")) Then excel.Worksheets("Sheet1").Cells(CellRow, CellCol + 1).PutValue(Trim(MyReader("MTDSTAT"))) ' + ":00")

excel.Worksheets("Sheet1").Cells(CellRow, CellCol).Style.Number = 46 'formats Daily

excel.Worksheets("Sheet1").Cells(CellRow, CellCol + 1).Style.Number = 46 'formats MTD

Hi,

Well, We checked your code a bit, I think your data is not coverted into DateTime format. You have provided string values to the cells on which you want to apply Time format. DataTime Number Format won't work on string data. I think that 's the problem with your code.

Please convert all your concerning data to DateTime format first and then put into the related cells. Or you may use Cell.PutValue(string, bool) method for the task.

If you still have some problems please let us know.

Regards

Amjad Sahi

Aspose Nanjing Team

The problem is the time data is cumlitive so it may be over 24 hrs. The format of the data is h:mm. The thing is when the user opens the report and double clicks on the cell it goes to the proper format.

Please try:

Workbook wb1 = new Workbook();

wb1.Worksheets[0].Cells[0, 0].PutValue(
DateTime.FromOADate(1) + TimeSpan.Parse((int.Parse("30") % 24).ToString() + ":00"));


wb1.Worksheets[0].Cells[0, 0].Style.Custom = "h:mm";

wb1.Save("d:\\test\\abc.xls");

Hi,

I think you may use Number format 22 to your desired results.

here is a sample code with an excel file:

Dim wb As New Excel()

wb.Open("d:\ftest.xls")

Dim sheet As Worksheet

sheet = wb.Worksheets(0)

Dim cells As Cells

cells = sheet.Cells

Dim cell As Cell

cell = cells("d4")

cell.PutValue(DateTime.Parse("18:12"))

cell.Style.Number = 22

wb.Save("d:\ftest1.xls")

Result is: 11/10/2006 18:12

Do you want the above kind of result?

Well, related Number format 46, we will sort out the things and will reponse you.

Regards

Amjad Sahi

Aspose Nanjing Team

Do you have code in VB?

FYI... I need to be able to have data greater than 23 hours that is allowed by the Timespan.parser allows.

Here it is:

Dim wb1 As Workbook = New Workbook()

wb1.Worksheets(0).Cells(0, 0).PutValue(
DateTime.FromOADate(1) + TimeSpan.Parse((Integer.Parse("30") % 24).ToString() + ":00"))
wb1.Worksheets(0).Cells(0, 0).Style.Number = 20

wb1.Save("d:\test\abc.xls")

The % is not vaild. But when I change it to mod I get the following error: Operator ‘+’ is not defined for types ‘Date’ and ‘System.TimeSpan’. Also I want to keep the time if it’s greator than 24.

Hi,

Please try the VB code:

Dim wb1 As Workbook = New Workbook()

Dim dTime As New System.DateTime()

Dim tSpan As New System.TimeSpan()

Dim result As System.DateTime

dTime = DateTime.FromOADate(1)

tSpan = TimeSpan.Parse((Integer.Parse("30") Mod 24).ToString() + ":00")

result = System.DateTime.op_Addition(dTime, tSpan)

wb1.Worksheets(0).Cells(0, 0).PutValue(result)

wb1.Worksheets(0).Cells(0, 0).Style.Number = 20

wb1.Save("d:\test\abc.xls")

Regards

Amjad Sahi

Aspose Nanjing Team

I don't think this will work with my data. because the data is formated like this "hh:mm" ie 7:35.

I am confused with your need. Could you please post a file to show your need and a file to show your current result? Thank you.

This is the file that I need. If you notice when you select the cell with the time format it puts it into a time format.

This is the file that I have. If you notice when you select the cell with the time format it shows it as it appears. If you were to double click that cell it will format it correctly.

Please try this piece of code:

Dim wb As Workbook = New Workbook()

Dim cell As Cell = wb.Worksheets(0).Cells(0, 0)

Dim day As Integer = 30 / 24
Dim hour As Integer = 30 Mod 24

Dim dateTime As DateTime = New DateTime(1900, 1, day, hour, 20, 0)
cell.PutValue(dateTime)
cell.Style.Custom = "Cool [H]:mm:ss"

wb.Save("d:\test\abc.xls")

Is there a way to set the focus on a particular cell?

Hi,

We do not support this feature, you have to manually set in your template file.

Regards

Amjad Sahi

Aspose Nanjing Team

it seems to error with data less than 13 hours. The results are wrong 37:50:00 (Tested code) v. 13:50:00 ( Actual). For some reason it’s adding a day. Is there a way to not include the day?

Dim wb As Workbook = New Workbook()

Dim cell As Cell = wb.Worksheets(0).Cells(0, 0)

Dim hourValue As Integer = 30

Dim day As Integer = hourValue \ 24
Dim hour As Integer = hourValue Mod 24

Dim dateTime As DateTime
If day = 0 Then
dateTime = New DateTime(1899, 12, 31, hour, 20, 0)
Else
dateTime = New DateTime(1900, 1, day, hour, 20, 0)
End If
cell.PutValue(dateTime)
cell.Style.Custom = "Cool [H]:mm:ss"

wb.Save("d:\test\abc.xls")