Time Format

BryanC · October 10, 2006, 9:11am

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?

amjad.sahi · October 10, 2006, 10:13am

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

BryanC · October 10, 2006, 10:39am

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

amjad.sahi · October 10, 2006, 10:44pm

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

BryanC · October 11, 2006, 4:12am

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.

Laurence · October 11, 2006, 5:07am

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");

amjad.sahi · October 11, 2006, 5:22am

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

BryanC · October 11, 2006, 5:25am

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.

Laurence · October 11, 2006, 5:54am

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")

BryanC · October 11, 2006, 6:36am

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.

amjad.sahi · October 11, 2006, 11:06am

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

BryanC · October 11, 2006, 11:27am

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

Laurence · October 11, 2006, 5:59pm

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.

BryanC · October 12, 2006, 3:58am

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.

BryanC · October 12, 2006, 4:00am

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.

Laurence · October 12, 2006, 6:26am

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")

BryanC · October 12, 2006, 9:09am

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

amjad.sahi · October 12, 2006, 10:36am

Hi,

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

Regards

Amjad Sahi

Aspose Nanjing Team

BryanC · October 12, 2006, 11:35am

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?

Laurence · October 12, 2006, 5:54pm

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")