Opening an xlsx file-CodePudding

I've extracted an excel file by running the code below.

from zipfile import ZipFile
with ZipFile('HISTDATA_COM_XLSX_EURUSD_M12018.zip', 'r') as zipObj:
   zipObj.extractall()

After that, I wanted to open it. Due to being an xlsx file, I imported the openpyxl module and ran the code below.

from zipfile import ZipFile
import openpyxl

with ZipFile('HISTDATA_COM_XLSX_EURUSD_M12018.zip', 'r') as zipObj:
    zipObj.extractall()

ref_workbook = openpyxl.load_workbook('DAT_XLSX_EURUSD_M1_2018.xlsx')

Then, I received this error: "Workbook contains no default style, apply openpyxl’s default in Python." So I also imported the warnings module and ran the code below:

import warnings
from zipfile import ZipFile
import openpyxl

warnings.filterwarnings("ignore")

with ZipFile('HISTDATA_COM_XLSX_EURUSD_M12018.zip', 'r') as zipObj:
    zipObj.extractall()

ref_workbook = openpyxl.load_workbook('DAT_XLSX_EURUSD_M1_2018.xlsx')

But I received nothing in the output. Can anyone help? My operating system is Linux Ubuntu. The link of the file which I downloaded and extracted is: https://www.histdata.com/download-free-forex-historical-data/?/excel/1-minute-bar-quotes/eurusd/2018

P.S! I don't want to use the pandas module.

CodePudding user response：

Your code does appear to be returning a reference to a workbook, but you're not reading any data from it. You need to specify a sheet and read data from cells.

import warnings
from zipfile import ZipFile
import openpyxl

warnings.filterwarnings("ignore")

with ZipFile('HISTDATA_COM_XLSX_EURUSD_M12018.zip', 'r') as zipObj:
    zipObj.extractall()

ref_workbook = openpyxl.load_workbook('DAT_XLSX_EURUSD_M1_2018.xlsx')

sheet_2018 = ref_workbook['2018']
cell_A1 = sheet_2018['A1'].value
print(cell_A1)

cell_A1 = sheet_2018.cell(1, 1).value
print(cell_A1)

Output:

2018-01-01 17:00:00
2018-01-01 17:00:00

CodePudding user response：

You can use pyautogui to open files

from os import path
import pyautogui

filename = pyautogui.prompt("Filename: ")
print("Exists File: %s " % path.isfile(filename))
f = open(filename, "r")

You can use pyautogui to open files

CodePudding user response：

Update: when I set data_only=True I was able to read the workbook without the error. Also, you have to tell it to use the active worksheet, like so...

ref_workbook = openpyxl.load_workbook('DAT_XLSX_EURUSD_M1_2018.xlsx', data_only=True)
ws = ref_workbook.active #per the docs

The source code for the load_workbook function mentions a data_only argument. Considering its a simple dataset with no formatting in the original xlsx, my guess is that may help.

Source for load_workbook:
https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.0/openpyxl/reader/excel.py#L288