Greetings!
I've butchered various programming languages in the past, learning to hack my way through simple problems, but have never really become proficient in any of them. This year I decided that I would actually learn Python and stick with it. So, I'm a newb, but dedicated to continuing to learn.
My son-in-law helped me to create a solution for a previous employers of mine, which he wrote in Perl. I thought that, perhaps, that would be a nice basic challenge for me to redo in Python since I already had a basic idea of what might be involved, though the code would be much different.
The project takes an Excel workbook (only 1 sheet) that has rows of data with one column (named Reorder) containing values that are some number of alpha characters (less than 6, historically) and a single digit.
The desired outcome would be that all records would be filtered according to that digit on the end; all that ended with a '1' would be Group1, with a '2' would be Group2... and so on.
Once that was accomplished, each group would be sorted by the alpha values in the Reorder column.
All of the Group1 records would be put into a new Excel workbook named for the Group of records (e.g. Group1.xlsx, Group2.xlsx... etc).
Inside each of these Group workbooks would be separate worksheets (alphabetized) for each unique alpha/digit string (e.g. ABCDE1, BCD1, MNOP1, -OR- ABCDE2, JKM2, MNOP2... etc).
I've gotten through the baby steps of checking for the existence of the source folder and creating it if not there (originally set up so that different people could use the program the in exactly the same way, regardless of what computer it was on). I also have it check to make sure that there is one, and only one .xlsx file in this folder.
I've gotten it to create a new folder in the working folder that is named after the excel file without extension. Copy the original Excel file to that folder, and then change the working folder to that new folder.
From there, I've been able to get openpyxl assign a variable for the source workbook, assign a variable for the worksheet... and now I've sort of gotten lost.
In Perl, my son-in-law used (from Excel::Writer::XLSX) functions 'maxcol' and 'maxrow', so I tried to use max_col and max_row with openpyxl, but wasted a lot of time before realizing that they return the maximum rows and columns in Excel::Writer, but seem to assign them in openpyxl. I also wasted time reading older posts which referenced deprecated commands.
I've read through the documentation at https://openpyxl.readthedocs.io/en/stable/ but haven't gleaned much that seems like it would help.
I have found wb.calculate_dimension(), which I guess I can take the output from. Not sure how to go about the separating and sorting either.
Have I chosen the best tool with openpyxl, or is there something else I should be looking at? Does anyone know of a comprehensive listing of all of the functions/commands and their current names and usages within openpyxl? I have found precious little thus far.
I'll post an example of the type of data in a screenshot.
If you're still here, thanks for even reading this far! Any and all help is appreciated.
Joe
![[Image: TestImmuneData.xlsx%20-%20Excel.png?dl=0]](https://www.dropbox.com/s/5dpelnig7oryy7m/TestImmuneData.xlsx%20-%20Excel.png?dl=0)
Looks like you'll have to click the broken image icon to display the image. :(
I've butchered various programming languages in the past, learning to hack my way through simple problems, but have never really become proficient in any of them. This year I decided that I would actually learn Python and stick with it. So, I'm a newb, but dedicated to continuing to learn.
My son-in-law helped me to create a solution for a previous employers of mine, which he wrote in Perl. I thought that, perhaps, that would be a nice basic challenge for me to redo in Python since I already had a basic idea of what might be involved, though the code would be much different.
The project takes an Excel workbook (only 1 sheet) that has rows of data with one column (named Reorder) containing values that are some number of alpha characters (less than 6, historically) and a single digit.
The desired outcome would be that all records would be filtered according to that digit on the end; all that ended with a '1' would be Group1, with a '2' would be Group2... and so on.
Once that was accomplished, each group would be sorted by the alpha values in the Reorder column.
All of the Group1 records would be put into a new Excel workbook named for the Group of records (e.g. Group1.xlsx, Group2.xlsx... etc).
Inside each of these Group workbooks would be separate worksheets (alphabetized) for each unique alpha/digit string (e.g. ABCDE1, BCD1, MNOP1, -OR- ABCDE2, JKM2, MNOP2... etc).
I've gotten through the baby steps of checking for the existence of the source folder and creating it if not there (originally set up so that different people could use the program the in exactly the same way, regardless of what computer it was on). I also have it check to make sure that there is one, and only one .xlsx file in this folder.
I've gotten it to create a new folder in the working folder that is named after the excel file without extension. Copy the original Excel file to that folder, and then change the working folder to that new folder.
From there, I've been able to get openpyxl assign a variable for the source workbook, assign a variable for the worksheet... and now I've sort of gotten lost.
In Perl, my son-in-law used (from Excel::Writer::XLSX) functions 'maxcol' and 'maxrow', so I tried to use max_col and max_row with openpyxl, but wasted a lot of time before realizing that they return the maximum rows and columns in Excel::Writer, but seem to assign them in openpyxl. I also wasted time reading older posts which referenced deprecated commands.
I've read through the documentation at https://openpyxl.readthedocs.io/en/stable/ but haven't gleaned much that seems like it would help.
I have found wb.calculate_dimension(), which I guess I can take the output from. Not sure how to go about the separating and sorting either.
Have I chosen the best tool with openpyxl, or is there something else I should be looking at? Does anyone know of a comprehensive listing of all of the functions/commands and their current names and usages within openpyxl? I have found precious little thus far.
I'll post an example of the type of data in a screenshot.
If you're still here, thanks for even reading this far! Any and all help is appreciated.
Joe
Looks like you'll have to click the broken image icon to display the image. :(