I need to insert a row between other, using xlwt

Question

default table:

And this I want to get, after my script-manipulations

rb = xlrd.open_workbook('example.xls', formatting_info=True)
wb = copy(rb)
sheet = wb.get_sheet(0)
...?
wb.save('example.xls')

Thanks

Answer 1

solution with openpyxl:

def insert_rows(ws, row_idx, cnt, above=False, copy_style=True, copy_merged_columns=True, copy_data=True):
    """Inserts new (empty) rows into worksheet at specified row index.

    :param row_idx: Row index specifying where to insert new rows.
    :param cnt: Number of rows to insert.
    :param above: Set True to insert rows above specified row index.
    :param copy_style: Set True if new rows should copy style of immediately above row.
    :param copy_data: Set True if new rows should take on data or formula from immediately above row, filled with references new to rows. (changed from formulae only by Hillel)

    Usage:

    * insert_rows(2, 10, above=True, copy_style=False)

    """
    CELL_RE  = re.compile("(?P<col>\$?[A-Z]+)(?P<row>\$?\d+)")

    row_idx = row_idx - 1  if above else row_idx
    def replace(m):
        row = m.group('row')
        prefix = "$" if row.find("$") != -1 else ""
        row = int(row.replace("$", ""))
        row += cnt if row > row_idx else 0
        return m.group('col') + prefix + str(row)

    # First, we shift all cells down cnt rows...
    old_cells = set()
    old_fas = set()
    new_cells = dict()
    new_fas = dict()
    for c in ws._cells.values():

        old_coor = c.coordinate

        # Shift all references to anything below row_idx
        if c.data_type == Cell.TYPE_FORMULA:
            c.value = CELL_RE.sub(
                replace,
                c.value
            )
            # Here, we need to properly update the formula references to reflect new row indices
            if old_coor in ws.formula_attributes and 'ref' in ws.formula_attributes[old_coor]:
                ws.formula_attributes[old_coor]['ref'] = CELL_RE.sub(
                    replace,
                    ws.formula_attributes[old_coor]['ref']
                )

        # Do the magic to set up our actual shift
        if c.row > row_idx:
            old_coor = c.coordinate
            old_cells.add((c.row, c.col_idx))
            c.row += cnt
            new_cells[(c.row, c.col_idx)] = c
            if old_coor in ws.formula_attributes:
                old_fas.add(old_coor)
                fa = ws.formula_attributes[old_coor].copy()
                new_fas[c.coordinate] = fa

    for coor in old_cells:
        del ws._cells[coor]
    ws._cells.update(new_cells)

    for fa in old_fas:
        del ws.formula_attributes[fa]
    ws.formula_attributes.update(new_fas)

    # Next, we need to shift all the Row Dimensions below our new rows down by cnt...
    # CHANGED: for row in range(len(ws.row_dimensions) - 1 + cnt, row_idx + cnt, -1):
    for row in range(list(ws.row_dimensions)[-1] + cnt, row_idx + cnt, -1):
        new_rd = copy(ws.row_dimensions[row - cnt])
        new_rd.index = row
        ws.row_dimensions[row] = new_rd
        del ws.row_dimensions[row - cnt]

    # Now, create our new rows, with all the pretty cells
    # CHANGED: row_idx += 1
    new_row_idx = row_idx + 1
    for row in range(new_row_idx, new_row_idx + cnt):
        # Create a Row Dimension for our new row
        new_rd = copy(ws.row_dimensions[row-1])
        new_rd.index = row
        ws.row_dimensions[row] = new_rd

        # CHANGED: for col in range(1,ws.max_column):
        for col in range(ws.max_column):
            # CHANGED: col = get_column_letter(col)
            col = get_column_letter(col+1)
            cell = ws.cell('%s%d' % (col, row))
            cell.value = None
            source = ws.cell('%s%d' % (col, row-1))
            if copy_style:
                cell.number_format = source.number_format
                cell.font = source.font.copy()
                cell.alignment = source.alignment.copy()
                cell.border = source.border.copy()
                cell.fill = source.fill.copy()
            if copy_data:
                s_coor = source.coordinate
                cell.data_type = source.data_type
                if source.data_type == Cell.TYPE_FORMULA:
                    if s_coor in ws.formula_attributes and 'ref' not in ws.formula_attributes[s_coor]:
                        fa = ws.formula_attributes[s_coor].copy()
                        ws.formula_attributes[cell.coordinate] = fa
                    #print("Copying formula from cell %s%d to %s%d"%(col,row-1,col,row))
                    cell.value = re.sub(
                        "(\$?[A-Z]{1,3}\$?)%d" % (row-1),
                        lambda m: m.group(1) + str(row),
                        source.value
                    )
                else:
                    cell.value = source.value

    # Check for Merged Cell Ranges that need to be expanded to contain new cells
    for cr_idx, cr in enumerate(ws.merged_cell_ranges):
        ws.merged_cell_ranges[cr_idx] = CELL_RE.sub(
            replace,
            cr
        )

    # Merge columns of the new rows in the same way row above does
    if copy_merged_columns:
        for cr in ws.merged_cell_ranges:
            min_col, min_row, max_col, max_row = range_boundaries(cr)
            if max_row == min_row == row_idx:
                for row in range(new_row_idx, new_row_idx + cnt):
                    newCellRange = get_column_letter(min_col) + str(row) + ":" + get_column_letter(max_col) + str(row)
                    ws.merge_cells(newCellRange)