xlwings cell value wrong

Question

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)

pandas read it as the actual value of the cell (you can test on excel, - just change the cell format to general). So if you want to have time in Pandas, you must convert the integer value to time. — NoobVB, Sep 18 '22 at 09:43
not sure if it will help with the current situation, but you can try to read excel sheet to pandas directly, without xlwings, - pandas does try to guess the datatype of the column, or you can assign the type manually — NoobVB, Sep 18 '22 at 10:01
Add this line to your code just after **excelData = xw.Book(path)**; **print(excelData.sheets(sheetName)[''].number_format)** where is the coordinate of the cell in Excel with this value, e.g. A1. It will probably print **'mm:ss.000'** which is the format of the cell in Excel changing the display from 0.000965925925925926 to 1:23.456. — moken, Sep 18 '22 at 11:20
@moken that works but how do I convert the float number with that format that the same number (1:23.456) gets displayed? — Frog, Sep 18 '22 at 11:33

score 0 · Accepted Answer · answered Sep 18 '22 at 16:33

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

xlwings cell value wrong

1 Answers1