Working image
1, There is information such as start date / end date, selection items in pull-down in the cell range defined by the name in Excel 2, The range defined by the name of Excel is about 2.3 lines 3, Read a large amount of data such as iterative processing for each store from the sheet Previous memo 4, python uses OpenPyxL module 5, Data required for scraping will be read from Excel
The name of Excel is "test name range" The Excel file name is "test.xlsx" Use book-level names (so that they don't have the same name at sheet level)
Get a range of cells with an excel name
import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
my_range = wb.defined_names['Test name range']
dests = my_range.destinations
cells = []
for title, coord in dests: #The sheet name is taken in the title.
ws = wb[title]
cells.append(ws[coord])
C=[]
for row in cells:
for aa in row:
C.append([col.value for col in aa])
Since C is a list of lists, the upper left cell of the Excel name range is It can be used with C [0] [0]. This is fine when the name range is small.
If you put a cell range in a Variant type variable, you can use it like C (1,1). Unlike python, it starts from 1. Excel often opens many files at the same time and the operation is messed up. It is safer to specify by book → sheet → name.
Get the definition of the name of excel
Dim C As Variant
'The menu sheet has a test name range
C = ThisWorkbook.Sheets("menu").Range("Test name range")
You can get the value of the upper left cell with C (1, 1).
If you can read the name of Excel in detail, scraping operation will be easier.
Recommended Posts