xlwings cell value wrong-CodePudding

in excel I have a cell with the value 1:23.456 with the the format m:ss.000 (minute, seconds and milliseconds)

now I want to read the value with python xlwings and pandas:

import xlwings as xw
import pandas as pd

excelData = xw.Book(path)
data = pd.DataFrame(excelData.sheets[sheetName].used_range.value)
print(data[20][7])

but instead of 1:23.456 I get 0.000965925925925926 as the output.

How do I get the right number as the output? (1:23.456)

CodePudding user response：

I don't know of a simple, built-in way to do this. However, you can change the format once the data is in the DataFrame (I assume there will be a column of data that needs to be changed:

val = data[20][7] # Change this to the range of data you need to be altered.

(dt.datetime.min   dt.timedelta(val)).time().strftime("%M:%S.%f")

The number you are seeing is the ratio of the time to a full day.

The code above starts at the minimum time dt.datetime.min and adds on the time of your value dt.timedelta(val), then converts this to the format you want (but with 6 decimals). If you specifically want 3, you could follow the steps of this answer