Merging multiple rows from the middle does not work

Issue #670 invalid
bhavani created an issue

Merging the multiple rows does not work as expected. Below is the code snippet

for col in xrange(min_col, max_col + 1): 
    for row in xrange(min_row, max_row + 1): 
        if not (row == min_row and col == min_col): 
            # PHPExcel adds cell and specifically blanks it out if it doesn't exist 
            self._get_cell('%s%s' % (get_column_letter(col), row)).value = None 
            self._get_cell('%s%s' % (get_column_letter(col), row)).merged = True

For example, Assume I have a range of B-Q columns (16 columns and n rows), if I want to merge the cells from E4 to F5. The above code will fail in the merge. Please let me if it is correct. It would merge only E4,E5,F4 and F5 instead of E4-E17 and F1-F5 in the excel file. Attaching the sample file to take a look at the merge. My aim is to merge from E4-Q6 but it did not merge B5,C5 etc.

Comments (13)

  1. CharlieC

    I don't understand this code which seems to be avoiding the openpyxl API. Please provide a more complete example with a source file.

  2. bhavani reporter

    Sorry, I pasted the wrong code. It is in the below procedure which gets the cells to be merged under the util__init__.py file. The columns cells should be selected based on the position. If I want to merge C4:E6 and total number of columns are 17 then it should merged C4-Q4,A5-Q5 and A6-E6. But it is not doing because of the min and max col being fixed it always merges only certain rows.

    def rows_from_range(range_string):

    for row in range(min_row, max_row + 1):
    yield tuple('%s%d' % (get_column_letter(col), row) for col in range(min_col, max_col+1))

  3. CharlieC

    I don't understand your problem. This function works exactly as it should. Why should A6 be in the range C4:E6?

    from openpyxl.utils import rows_from_range
    rows = rows_from_range("C4:E6")
    print(list(rows))
    [('C4', 'D4', 'E4'), ('C5', 'D5', 'E5'), ('C6', 'D6', 'E6')]
    
  4. bhavani reporter

    In the above picture, if I have minimum and maximum number of columns say "B"-"F" and the rows as 6. If I want to merge C4:E6 then the tool should merge the cells in between the specified range based on the column minimum, col maximum, start position and end position. For example, it should merge C4,D4,E4,F4(row-4),B5,C5,D5,E5,F5(row-5),B6,C6,D6 and E6(row-6) . Why does it not include the B column in row 5 and 6. The cells in between should be merged. According to my perception, 1) Before the merge it should check for the row position, if they are same then the merge happens in the same row (which is working fine) 2) Before the merge it should check for the row position, for example if the rows starts at 4 and ends at 5 then each row should have a different min column and max column based on the position so that it includes all the cells in a row correctly.

    Please let me know your inputs as well. This is my perception how the merge should work. I hope it would help you to enhance your tool and also helps us to use your tool. I am thinking the range of rows proc needs to be changed inorder to support this feature. min_col_pos = 1 max_col_pos = 17 start_column, start_row, end_column, end_row = range_boundaries(range_string) min_row = start_row max_row = end_row
    if (start_row == end_row): min_col = start_column max_col = end_column for row in range(min_row, max_row + 1):
    yield tuple('%s%d' % (get_column_letter(col), row) for col in range(min_col, max_col+1)) elif (end_row == start_row +1): min_col = start_column max_col = max_col_pos for row in range(min_row, max_row + 1):
    yield tuple('%s%d' % (get_column_letter(col), row) for col in range(min_col, max_col+1)) min_col = min_col_pos max_col = end_column else: min_col = start_column max_col = max_col_pos for row in range(min_row, max_row + 1):
    yield tuple('%s%d' % (get_column_letter(col), row) for col in range(min_col, max_col+1)) min_col = min_col_pos if(row == max_row): max_col = end_column The min_col_pos and max_col_pos are the minimum and maximum columns positions in an excel file of the user.

    Thanks, Bhavani.

  5. CharlieC

    Krishna,

    you obviously don't understand how cell ranges, and thus merging, work: B can never be in the range C4:E6. Please feel tree to fork the project and make it work the way you think it should.

  6. bhavani reporter

    Hi Eric,

    Yes. I was wrong in understanding how the cells are merged in excel. I would close this issue. Thank you for the immediate response.

  7. Log in to comment