** Why is there something called "cell merging" in this world ... ** Because it looks good? Can you put together the same numbers? Is it because it shifts when printing?
** Please make it in a separate file! I don't want to do that with files intended for data processing! ** ** Even the Ministry of Internal Affairs and Communications says, "** Do not combine cells in the statistical table posted on e-Stat **"!
(Source) (Notation method for machine-readable data creation in statistical tables: https://www.soumu.go.jp/main_content/000723626.pdf)
If it's a storm of cell merging just before I thought "I'll do pretreatment", I'm 90% less motivated. Blank rows and blank columns can be deleted later, but it is difficult to find and remove the merged cells one by one. So I implemented a function to eliminate cell merging in python. ~~ I want to write a program that tracks humans who are actually useless cells combined to the ends of the earth ~~
{unmerge.py}
import openpyxl
import sys
class excel_operate:
def __init__(self, excel_dir):
self.excel_dir = excel_dir
self.workbook = openpyxl.load_workbook(self.excel_dir)
self.sheet_number = len(self.workbook.get_sheet_names())
self.sheet_contains = [self.workbook.worksheets[i] for i in range(self.sheet_number)]
self.merged_cells_list = [self.sheet_contains[i].merged_cells.ranges for i in range(self.sheet_number)]
def get_merged_cells_location(self):
self.merged_cells_location_list = [""]*self.sheet_number
for i in range(self.sheet_number):
self.merged_cells_location_list[i] = [format(self.merged_cells_list[i][j]) for j in range(len(self.merged_cells_list[i]))]
else:
print("The merged cells location was got!")
def break_merged_cells(self):
for i in range(self.sheet_number):
for j in range(len(self.merged_cells_list[i])):
self.sheet_contains[i].unmerge_cells(self.merged_cells_location_list[i][j])
else:
pass
else:
print('The all merged cells were unmerged!')
Excel = excel_operate(r"Excel path where you want to get rid of cell merges")
Excel.get_merged_cells_location()
Excel.break_merged_cells()
Excel.workbook.save("name.xlsx")
Please stop merging cells in an Excel file for data aggregation because it is really a request ...
Recommended Posts