I've been doing some big data work, and I've been writing a script to automate the formatting of a 15,000+ row .csv into a formatted excel file. I've been using Openpyxl for most of the formatting work.
I need to merge the server name cells in the Hostname column with the empty cells below it, but can't seem to get it working. In the table below, I need to merge Dev Server with all of the empty cells below it (to stop at RAS Server). Then merge RAS Server with the empty cells below that, and Prod Server with the empty cells below that.
The issue I'm having is I can't seem to specify the right for loop that identifies a cell with a string, iterates through each of the cells below it (merging with the empty cells), and then stopping and starting a new merged cell at the next cell containing a string.
Specified parameters/cell numbers cannot work here - the real table is 15,000+ lines long, and the amount of 'installed software' on each server ranges from 25-200+ per server. To make things better, the real server names also have no consistent naming pattern or scheme.
Is this possible? Any help or direction would be much appreciated.
| Hostname | Installed Software |
|---|---|
| Dev Server | Microsoft Word |
| Microsoft Excel | |
| Microsoft Teams | |
| Visual Studio Code | |
| Discord | |
| RAS Server | Microsoft Word |
| Spotify | |
| Log4j | |
| Prod Server | Adobe Photoshop |
| Unreal Engine | |
| Adobe PDF Reader | |
| Steam | |
| Adobe Illustrator | |
| Hyper-V |
wb = openpyxl.load_workbook("Test.xlsx") # Load Workbook
ws = wb["Sheet1"] # Load Worksheet
total_rows = [] # Used to enumerate the total number of rows
# in the Worksheet
for i in range (1,20000):
if ws["B" + str(i)].value != None:
total_rows.append(i) # If the cell has a string, the
# cell row number is appended to
# total_rows. Indexing the last
# number in total_rows will give
# you the total number of rows
cells_with_strings = []
for i in range (1,(total_rows[-1])):
if ws["A" + str(i)].value != None:
cells_with_strings.append(int(i))
# Iterates through each cell in column A, and appends
# the row numbers of cells containing text to cells_with_strings
merge_cell_range = len(cells_with_strings) - 1
for i in range (0, (merge_cell_range)):
ws.merge_cells("A" + str(cells_with_strings[i]) + ":" + "A" + str((cells_with_strings[(i+1)])-1))
# Using the values in cell_with_strings, it merges the cells in the rows
# represented by the values in cells_with_strings. This works for all
# merges except for the last merge.
final_merge = []
for i in range ((cells_with_strings[-1]), ((cells_with_strings[-1]) + 9999)):
if ws["B" + str(i)].value != None:
final_merge.append(int(i))
ws.merge_cells("A" + str(final_merge[0]) + ":" + "A" + str(final_merge[-1]))
# The last row merge requires different code to the iteration merge.
wb.save("Test.xlsx")