How can merge same data cell?

Question

I made some script for data modify and write xlsx.

My text file has below data.

WWN,Server Name,Server IP,SP port1,Storage Group1,SP port2,Storage Group2,LUN Count,LUN Capacity
8A:34,11B2,14,SP A_4,11B2,SP B_4,11B2,13,133
8A:36,11B2,14,SP A_8,11B2,SP B_8,11B2,13,133
8A:38,11B6,15,SP A_4,11B6,SP B_4,11B6,13,133
8A:3A,11B6,15,SP A_8,11B6,SP B_8,11B6,13,133

I want same data cell merged using key data. Key data is Storage Group1. if Storage Group1cell has same data, will be merge LUN count LUN Capacity.

below is my script not work.

import sys
import pandas as pd
import xlsxwriter

##Add workbook and worksheet
workbook = xlsxwriter.Workbook('emc_pool.xlsx', {'strings_to_numbers':  True})
worksheet = workbook.add_worksheet(u'Host')

##Add Cell Style
bold = workbook.add_format({'bold': True, 'border': 1})
border = workbook.add_format({'border': 1})

merge_format = workbook.add_format({'bold': True, 'border': 1,'align': 'center', 'valign': 'vcenter'})

##aliase information
port_file = 'last_emc.txt'
port_table = pd.read_table(port_file, encoding='utf_8', sep=',', header=None, names=['WWN','Server Name','Server IP','SP port1','Storage Group1','SP port2','Storage Group2','LUN Count','LUN Capacity'], lineterminator='\n')

worksheet.merge_range('A1:G1', u'Host', merge_format)
worksheet.write('A2', 'Server Name', bold)
worksheet.write('B2', 'Server IP', bold)
worksheet.write('C2', 'WWN', bold)
worksheet.write('D2', 'SP port1', bold)
worksheet.write('E2', 'Storage Group1', bold)
worksheet.write('F2', 'SP port2', bold)
worksheet.write('G2', 'Storage Group2', bold)
worksheet.write('H2', 'LUN count', bold)
worksheet.write('I2', 'LUN Capacity', bold)

data = (port_table)

row = 2
col = 0

data_dict = {}
count = 0
for index, temp_data in data.iterrows():
        data_line = data_dict.setdefault(temp_data[4], [0])
        data_line[0] += (count + 1)
        for key, data in data_dict.items():
                if index != 0:
                        worksheet.write(row, col, temp_data[1], border)
                        worksheet.write(row, col + 1, temp_data[2], border)
                        worksheet.write(row, col + 2, temp_data[0], border)
                        worksheet.write(row, col + 3, temp_data[3], border)
                        for temp_data[4] in [key]:
                                worksheet.merge_range(int(row) + map(int,data), col + 7, temp_data[7], border)
                                worksheet.merge_range(int(row) + map(int,data), col + 8, temp_data[8], border)
                        else:
                                worksheet.merge_range(row, col + 7, temp_data[7], border)
                                worksheet.merge_range(row, col + 8, temp_data[8], border)
                        worksheet.write(row, col + 4, temp_data[4], border)
                        worksheet.write(row, col + 5, temp_data[5], border)
                        worksheet.write(row, col + 6, temp_data[6], border)
                        row += 1



## Clocse Workbook
workbook.close()

I want make result below picture.

The sample code throws an error in Python 2 and 3. The Python 2 error is `TypeError: unsupported operand type(s) for +: 'int' and 'list' ...`. — jmcnamara, Aug 08 '17 at 10:16

score 2 · Accepted Answer · edited May 03 '18 at 13:25

Question: I want same data cell merged using key data

Solution using openpyxl, for instance

Precondition: Ordered in key data

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
# Select First Worksheet
ws = wb.worksheets[0]

# Append Data from File
with open('last_emc.txt') as fh:
    for row_data in fh:
        ws.append(row_data[:-1].split(','))

key_column = 5
merge_columns = [8, 9]
start_row = 2
max_row = ws.max_row
key = None

# Iterate all rows in `key_colum`
for row, row_cells in enumerate(ws.iter_rows(min_col=key_column, min_row=start_row,
                                             max_col=key_column, max_row=max_row),
                                start_row):
    if key != row_cells[0].value or row == max_row:
        # moved line below this if
        # if row == max_row: row += 1 
        if not key is None:
            for merge_column in merge_columns:
                ws.merge_cells( start_row=start_row, start_column=merge_column,
                                end_row=row - 1, end_column=merge_column)

                ws.cell(row=start_row, column=merge_column).\
                    alignment = Alignment(horizontal='center', vertical='center')

            start_row = row

        key = row_cells[0].value
    #moved below line here as it was merging last two rows content even if the values differ.
    if row == max_row: row += 1 

wb.save("emc_pool.xlsx")

Output:

Tested with Python: 3.4.2 - openpyxl: 2.4.1

thank you so much. But your script occurred error. ' File "last_excel.py", line 30, in alignment = Alignment(horizontal='center', vertical='center') NameError: name 'Alignment' is not defined ' — KJ9, Aug 16 '17 at 07:15
@1KJ9: Missing `import`, updated Answer. Read about `.\`: [How can I do a line break (line continuation) in Python?](https://stackoverflow.com/q/53162/7414759) — stovfl, Aug 16 '17 at 08:11

How can merge same data cell?

1 Answers1