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: image

after: image

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.

Recommended Posts

I tried using openpyxl
I tried using argparse
I tried using anytree
I tried using aiomysql
I tried using Summpy
I tried using coturn
I tried using "Anvil".
I tried using Hubot
I tried using ESPCN
I tried using Ipython
I tried using PyCaret
I tried using cron
I tried using ngrok
I tried using face_recognition
I tried using Jupyter
I tried using PyCaret
I tried using Heapq
I tried using doctest
I tried using folium
I tried using jinja2
I tried using folium
I tried using time-window
[I tried using Pythonista 3] Introduction
I tried using easydict (memo).
I tried face recognition using Face ++
I tried using Random Forest
I tried using BigQuery ML
I tried using Amazon Glacier
I tried using git inspector
[Python] I tried using OpenPose
I tried using magenta / TensorFlow
I tried using AWS Chalice
I tried using Slack emojinator
I tried using Rotrics Dex Arm # 2
I tried using Rotrics Dex Arm
I tried using GrabCut of OpenCV
I tried using Thonny (Python / IDE)
I tried server-client communication using tmux
I tried reinforcement learning using PyBrain
I tried deep learning using Theano
Somehow I tried using jupyter notebook
[Kaggle] I tried undersampling using imbalanced-learn
I tried shooting Kamehameha using OpenPose
I tried using the checkio API
[Python] I tried using YOLO v3
I tried asynchronous processing using asyncio
I tried PyQ
I tried AutoKeras
I tried papermill
I tried django-slack
I tried Django
I tried spleeter
I tried cgo
I tried using Amazon SQS with django-celery
I tried using Azure Speech to Text.
I tried using Twitter api and Line api
I tried playing a ○ ✕ game using TensorFlow
I tried using YOUTUBE Data API V3
I tried using Selenium with Headless chrome
I tried drawing a line using turtle
[Kaggle] I tried ensemble learning using LightGBM