有没有办法使用Python在Excel工作簿中拆分/取消合并单元格?我想要的是下面解释的-
结果应该是一个包含以下条目的新Excel文件-
使用xlrd的我的解决方案是将同一字符串复制到所有已合并的列中,如下所示-
[备注:“formatted_info = True”标志尚未在我使用的xlrd中实现,因此我无法直接获取已合并单元格的列表..我不应在设置中升级xlrd]
上述函数将返回以下Python字典 -
我会遍历目录并写入新的Excel表格。
然而,我希望有一种通用的方法来拆分合并单元格。
结果应该是一个包含以下条目的新Excel文件-
使用xlrd的我的解决方案是将同一字符串复制到所有已合并的列中,如下所示-
[备注:“formatted_info = True”标志尚未在我使用的xlrd中实现,因此我无法直接获取已合并单元格的列表..我不应在设置中升级xlrd]
def xlsx_to_dict():
workbook = xlrd.open_workbook(xlsfile)
worksheet_names = workbook.sheet_names()
for worksheet_name in worksheet_names:
worksheet = workbook.sheet_by_name(worksheet_name)
num_rows = worksheet.nrows - 1
num_cells = worksheet.ncols - 1
curr_row = -1
header_row = worksheet.row(0)
columns = []
for cell in range(len(header_row)):
value = worksheet.cell_value(0, cell)
columns.append(value)
cities = []
for row in range(1,num_rows):
value = worksheet.cell_value(row,0)
type = worksheet.cell_type(row,0)
if not value == "":
cities.append(value)
names = []
for row in range(1,num_rows):
value = worksheet.cell_value(row,1)
type = worksheet.cell_type(row,1)
if not value == "":
names.append(value)
current_city = cities[0]
result_dict = {}
for curr_row in range(1,num_rows):
row = worksheet.row(curr_row)
curr_cell = -1
curr_name = names[0]
while curr_cell < num_cells:
curr_cell += 1
cell_value = worksheet.cell_value(curr_row, curr_cell)
if cell_value in cities and curr_cell == 0:
current_city = cell_value
if not result_dict.has_key(current_city):
result_dict[current_city] = {}
continue
if cell_value == "" and curr_cell == 0:
continue
if cell_value in names and curr_cell == 1:
curr_name = cell_value
if not result_dict[current_city].has_key(curr_name):
result_dict[current_city][curr_name] = {}
continue
if cell_value == "" and curr_cell == 1:
continue
try:
result_dict[current_city][curr_name]['Phone'].append(cell_Value)
except:
result_dict[current_city][curr_name]['Phone'] = [cell_value]
上述函数将返回以下Python字典 -
{ 'New York' : { 'Tom' : [92929292, 33929] }, ........}
我会遍历目录并写入新的Excel表格。
然而,我希望有一种通用的方法来拆分合并单元格。