+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: How to make a 3D List of Excel Spreadsheets? (/thread-32150.html)
How to make a 3D List of Excel Spreadsheets? - chatguy - Jan-23-2021
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
RE: How to make a 3D List of Excel Spreadsheets? - deanhystad - Jan-23-2021
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
RE: How to make a 3D List of Excel Spreadsheets? - chatguy - Jan-23-2021
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
RE: How to make a 3D List of Excel Spreadsheets? - chatguy - Jan-24-2021
(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
RE: How to make a 3D List of Excel Spreadsheets? - buran - Jan-24-2021
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.