Python Forum

Hi-
I've been failing greatly trying to get different ideas working, or finding how to do this.

Is there a snippet of code I may please use (using something like XLRD, Pandas or OpenPyxl) to allow me to make a 3D-List of multiple Excel spreadsheets in an XLSX file, pls?

i.e.: I'd like to read in multiple Excel tabs/worksheets in an XLSX file, such as:

-----TAB#1-----
data-1a data-1b
data-2a data-2b

-----TAB#2-----
data-3a data-3b
data-4a data-4b

...and turn it into a 3D List like:

[ [ 'TAB#1', ['data-1a', 'data-1b'], ['data-2a', 'data-2b'] ] [ 'TAB#2', ['data-3a', 'data-3b'], ['data-4a', 'data-4b'] ] ]

Thank you so much,
CG

So you want a list of spreadsheets?
data[sheet][row][column]?

Sheet 0    Sheet 1    Sheet 2
1, 2, 3,   9, 8, 7,   a, b, c,
4, 5, 6,   6, 5, 4,   d, e, f,
7, 8, 9    3, 2, 1    g, h, i
data[0][2][2] == 9
data[1][0][0] == 9
data[2][1][1] == e

Going by the example you gave, please if possible, for myList to be populated similar to:

myList = [ [ 'Sheet 0', ['1','2','3'], ['4','5','6'], ['7','8','9'] ], [ 'Sheet 1', ['9','8','7'], ['6','5','4'], ['3','2','1'] ], [ 'Sheet 2', ['a','b','c'], ['d','e','f'], ['g','h','i'] ] ]

Thanks much in advance
CG

(Jan-23-2021, 11:04 PM)deanhystad Wrote: [ -> ]So you want a list of spreadsheets?
data[sheet][row][column]?

Sheet 0    Sheet 1    Sheet 2
1, 2, 3,   9, 8, 7,   a, b, c,
4, 5, 6,   6, 5, 4,   d, e, f,
7, 8, 9    3, 2, 1    g, h, i
data[0][2][2] == 9
data[1][0][0] == 9
data[2][1][1] == e

Hi Dean-

Ohh wow---Although my response in reply #3 above, would be ideal, I re-read your reply and that would also work great! Thank you!!

If it's done via the way you've mentioned:
1) Is there a line(or lines) of python w/ a module that currently populates the "data" list the way you mention?
2) If so, is there a way of deciphering the sheet name of "0", "1", and "2" in the left-most list, in your example above?

Thanks again!
CG

Your "desired" data structure does not make sense at all. Using heterogeneous lists only complicates access later on. Using a dict of lists of lists may be a bit better but still unnecessary when you can work for example with Workbook/Worksheet objects. What would you do with this data structure? It smells like XY problem to me.