[Python, Excel] Eliminate cell merging

Lamentation

** 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) image.png (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

[Python, Excel] Eliminate cell merging
Excel with Python
Read Excel name / cell range with Python VBA
Run python from excel
Handle Excel with python
Python programming in Excel
Operate Excel with Python (1)
Operate Excel with Python (2)
Operate Excel with Python openpyxl
Let's run Excel with Python
[Python] Eliminate Chrome Webdriver errors
Aim python library master (12) excel
solver> Link> Solve Excel Solver with python
Python pywin32 (win32com) Excel operation memorandum
Create an Excel file with Python3
Let's play with Excel with Python [Beginner]
Handle Excel CSV files with Python
Excel graph creation using python xlwings
web coder tried excel in Python
Eliminate WARNING when starting Python IDLE
Excel aggregation with Python pandas Part 1