[PYTHON] I tried using openpyxl

I read Excel as a template, edited it, and wanted to output it, so I tried using OpenPyXL, so I wrote down the result of using it as a memo. See below for basic usage. https://openpyxl.readthedocs.io/en/stable/

Borders of merged cells disappear

Some of the borders of the merged cells disappear with load → save.

from openpyxl import load_workbook
wb = load_workbook('./test.xlsx')
wb.save('./test2.xlsx')

before:

after:

When I read the document, it seems that there is no choice but to give up because it is such a thing. http://openpyxl.readthedocs.io/en/default/styles.html#styling-merged-cells

So, in excel to load, you have to draw a ruled line without merging and merge with merge_cells () after loading. .. Troublesome.

Copy style

It seems that it can be done using copy. http://openpyxl.readthedocs.io/en/default/styles.html#copying-styles

However, I wasn't sure what to do if I wanted to copy all the styles, so when I looked it up, it was suggested that I specify border or font and copy it. http://stackoverflow.com/questions/23332259/copy-cell-style-openpyxl#answer-34838233

for row in default_sheet.rows:
    for cell in row:
        new_cell = new_sheet.cell(
                   row=cell.row_idx, col=cell.col_idx, value= cell.value)
        if cell.has_style:
            new_cell.font = cell.font
            new_cell.border = cell.border
            new_cell.fill = cell.fill
            new_cell.number_format = cell.number_format
            new_cell.protection = cell.protection
            new_cell.alignment = cell.alignment

To be honest, I'm sorry, I don't know all the styles, so when I looked at the source code of openpyxl, I found target_cell._style = copy (source_cell._style) in ʻopenpyxl.worksheet.copier._copy_cells`. I found it. https://bitbucket.org/openpyxl/openpyxl/src/644ea21bb4056f93184d3e743f4abf05c51f84af/openpyxl/worksheet/copier.py?at=default&fileviewer=file-view-default#copier.py-54

So, if you want to copy all the styles, follow the steps below.

to_cell._style = copy(cell._style)

Or rather, when copying cells, I think you should refer to ʻopenpyxl.worksheet.copier._copy_cells`. https://bitbucket.org/openpyxl/openpyxl/src/644ea21bb4056f93184d3e743f4abf05c51f84af/openpyxl/worksheet/copier.py?at=default&fileviewer=file-view-default#copier.py-46:60

row_dimensions are set from the first

When copying from a template row by row, you may want to get the row height and set it to the destination row, but in that case you need to access row_dimensions in the worksheet. However, row_dimensions on the first line is not in row_dimensions [0] but in row_dimensions [1], so be careful not to make a mistake.

Specify a range and copy

I wanted to copy and output the rows from the template sheet for the number of certain data, so I implemented a function to specify the range and copy.

def copy_cell_range(from_sheet, from_range_string, to_sheet, to_cell_string='A1'):
    to_cell = to_sheet[to_cell_string]
    to_row = to_cell.row
    to_col = to_cell.col_idx

    for row_num, row in enumerate(from_sheet[from_range_string]):
        if not row:
            continue

        #Copy line height
        to_row_dimension = to_sheet.row_dimensions[to_row + row_num]
        from_row_dimension = from_sheet.row_dimensions[row[0].row]
        to_row_dimension.height = from_row_dimension.height

        for col_num, cell in enumerate(row):
            to_cell = to_sheet.cell(row=to_row + row_num, column=to_col + col_num)

            #Copy the value of cell
            to_cell.value = cell.value

            #Copy cell style
            if cell.has_style:
                to_cell._style = copy(cell._style)

I think row_dimension can also be copied, but it is unconfirmed. If you need something other than cell style, you can copy it.