2

My code is as below, and the text.xlsx file looks like: text.xlsx file screenshot.

import xlwings as xw
import datetime

app = xw.App(visible=False,add_book=False)
file_path = r'D:\test.xlsx'
wb = app.books.open(file_path)
sht = wb.sheets[0]
cell = sht.range((1, 1)).value
print(cell)

wb.close()
app.quit()

In the test file, the datetime in 'A1' is (1895, 10, 24, 23, 2, 24), which stores in excel as -1528.96, but I don't care about this datetime right now, I just want to get the excel number -1528.96 in my code, but it failed.

The print message is:

1895-10-24 23:02:24

Expected meassage is:

-1528.96

Also tried: https://stackoverflow.com/a/9574948/12525445, but the result is -1527.04

Many thanks for any clue!

Community
  • 1
  • 1
F ONE
  • 23
  • 4
  • Did you see the answer in the linked question? [here](https://stackoverflow.com/a/24410858/198536) – wallyk Jan 15 '20 at 02:08
  • @wallyk Thank you, but I want to get the excel datetime number as a float type not the formatted datetime string. – F ONE Jan 15 '20 at 02:25
  • In theory, we should add the `value2` property. For now just do: `sht.range((1, 1)).api.Value2` – Felix Zumstein Jan 15 '20 at 10:50
  • @FelixZumstein Thanks a lot! This is exactly what I am looking for. BTW, where can I learn this `api` API, the xlwings documentation seems to be no such part. – F ONE Jan 15 '20 at 11:51
  • On Windows, it's pretty much standard VBA object model, so usually you start at the official microsoft docs: In your case: https://learn.microsoft.com/en-us/office/vba/api/excel.range.value2 – Felix Zumstein Jan 15 '20 at 15:45
  • @FelixZumstein - If you get a chance, I think you should gather the knowledge from these comments and post it as an answer. – John Y Jan 15 '20 at 22:45

1 Answers1

2

The canonical way of getting the underlying float from an Excel date would be to call the value2 property. Unfortunately, we haven't implemented this just yet in xlwings, so you'll need to drop down to pywin32, if you are on Windows:

sht.range((1, 1)).api.Value2

Learn more about how to handle missing features here: http://docs.xlwings.org/en/stable/missing_features.html

The syntax on Windows for .api is really pywin32 syntax which again is almost the same as the official VBA way of doing it. In your specific case, you can refer to the official Microsoft docs for Value2: https://learn.microsoft.com/en-us/office/vba/api/excel.range.value2

Felix Zumstein
  • 6,737
  • 1
  • 30
  • 62