获取合并/合并的单元格值 [英] Get combined/merged cells value

问题描述

我正在编码一个新的python脚本，该脚本需要从google表格中提取数据，但是有许多要合并/合并的单元格，并且只有这次合并的左上角的单元格才有值.在所有合并的单元格上都具有该值很重要.

I'm coding a new python script that need to extract data from google sheets, but there are many cells which are merged/combined, and only the top-left cell from this merge has the value. It's important to have that value on all the merged cells.

我该怎么办?

Python 3.8.5 + gspread 3.6.0

Python 3.8.5 + gspread 3.6.0

注意:每个注释试图获取..."，其下面的代码应返回与先前代码相同的值.

电子表格测试: https:///docs.google.com/spreadsheets/d/17Dyxufu1y1ouBCPkf5Y7Vt1UW70WroK0Moy_DD7bZKc/edit?usp=sharing

用于重现该问题的代码:

Code for reproducing the problem:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import os
import pprint

here = os.path.dirname(os.path.abspath(__file__))
secret = os.path.join(here, 'credentials.json')

scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name(secret, scope)

client = gspread.authorize(creds)

sheet = client.open_by_key('17Dyxufu1y1ouBCPkf5Y7Vt1UW70WroK0Moy_DD7bZKc')
ws = sheet.sheet1
pp = pprint.PrettyPrinter()

#getting the FIRST text
result = ws.acell('A1')
pp.pprint('A1: '+result.value)

#trying to get the SAME text on the cell col+1
result = ws.acell('A2')
pp.pprint('A2: '+result.value)

#getting the 'simple_cell'
result = ws.acell('C2')
pp.pprint('C2: '+result.value)

#getting the 'row_merged'
result = ws.acell('D2')
pp.pprint('D2: '+result.value)

#trying to get 'row_merged' on row+1
result = ws.acell('E2')
pp.pprint('E2: '+result.value)

#getting the 'col_merged'
result = ws.acell('D6')
pp.pprint('D6: '+result.value)

#trying to get 'col_merged' on col+1
result = ws.acell('D7')
pp.pprint('D7: '+result.value)

输出如下:

('A1: just to confirm, the value "row_merged" has been put to cell D2 originally. Value "col_merged" is in D6 initially. Since it's merged, the expected result should be found on all merged cells')
'A2: '
'C2: simple cell'
'D2: row_merged'
'E2: '
'D6: col_merged'
'D7: '
PS C:\Users\joaov\Desktop>

关键是:A2必须等于A1 ... E2 = D2，D7 == D6 ...但是似乎无法用gspread处理合并的单元格.

The point is: A2 must be equals to A1... E2=D2, D7==D6... But it seems there's no way of handling merged cells with gspread.

推荐答案

我相信您的目标如下.

• 您想使用python的gspread从合并的单元格中检索值.

例如，在示例电子表格中，单元格"A1:L12"被合并.在这种条件下，当从合并的单元格"A1:L12"中检索值时，将返回"A1:L12".使用Sheets API，只是为了确认，值"row_merged"已最初放置到单元格D2中.值"col_merged"最初在D6中.由于已合并，因此应在所有合并的单元格上找到预期结果，仅在单元格"A1"中检索到.因此，为了从"A1:L12"的所有单元中检索相同的值，需要将这些值放入单元"A2:L12"中.不幸的是，在Sheets API中似乎没有任何方法可以直接实现这种情况.因此，在这个答案中，我想使用脚本提出这个建议.

In your sample Spreadsheet, for example, the cells "A1:L12" are merged. Under this condition, when the values are retrieved from the merged cells of "A1:L12" using Sheets API, just to confirm, the value "row_merged" has been put to cell D2 originally. Value "col_merged" is in D6 initially. Since it's merged, the expected result should be found on all merged cells is retrieved only cell "A1". So in order to retrieve the same values from all cells of "A1:L12", it is required to put the values to the cells "A2:L12". Unfortunately, it seems that there are no methods for directly achieving this situation in Sheets API. So, in this answer, I would like to propose this using a script.

顺便说一句，关于脚本中的 result = ws.acell('A2')，此单元格未合并，并且为空.因此，在这种情况下，我认为空值是正确的.我以为您可能想要检查单元格"B1".在此答案中，也要考虑这一点.

By the way, about result = ws.acell('A2') in your script, this cell is not merged and it's empty. So in this case, I think that the empty value is correct. I thought that you might have wanted to check the cell "B1". In this answer, this is also considered.

spreadsheet_id = '###'  # Please set the Spreadsheet ID.
sheet_name = 'Sheet1'  # Please set the sheet name.

client = gspread.authorize(credentials)
access_token = client.auth.token
url = "https://sheets.googleapis.com/v4/spreadsheets/" + \
    spreadsheet_id + "?fields=sheets&ranges=" + sheet_name
res = requests.get(url, headers={"Authorization": "Bearer " + access_token})
obj = res.json()
# print(obj['sheets'][0]['merges'])

sheet = client.open_by_key(spreadsheet_id)
ws = sheet.worksheet(sheet_name)

# 1. All values are retrieved.
values = ws.get_all_values()

# 2. Put the values to the merged cells.
if 'merges' in obj['sheets'][0].keys():
    for e in obj['sheets'][0]['merges']:
        value = values[e['startRowIndex']][e['startColumnIndex']]
        rows = len(values)
        if rows < e['endRowIndex']:
            for i in range(0, e['endRowIndex'] - rows):
                values.append([''])
        for r in range(e['startRowIndex'], e['endRowIndex']):
            cols = len(values[r])
            if cols < e['endColumnIndex']:
                values[r].extend([''] * (e['endColumnIndex'] - cols))
            for c in range(e['startColumnIndex'], e['endColumnIndex']):
                values[r][c] = value

# For A1
print('A1: '+values[0][0])

# For B1
# output: just to confirm, the value "row_merged" has been put to cell D2 originally. Value "col_merged" is in D6 initially. Since it's merged, the expected result should be found on all merged cells
print('B1: '+values[0][1])

# For C2
# output: simple cell
print('C2: '+values[1][2])

# For D2
# output: row_merged
print('D2: '+values[1][3])

# For E2
# output: row_merged
print('E2: '+values[1][4])

# For D6
# output: col_merged
print('D6: '+values[5][3])

# For D7
# output: col_merged
print('D7: '+values[6][3])

注意:

• 在此示例脚本中，"spreadsheets.get"方法在Sheets API中，通过从gspread的 client = gspread.authorize(credentials)检索到的访问令牌与请求一起使用.
• 在此示例脚本中，将在列表中处理值.因此，当您从合并的单元格中检索值时，请从列表 values 中检索它们.

Note:

• In this sample script, the method of "spreadsheets.get" in Sheets API is used with requests using the access token retrieved from client = gspread.authorize(credentials) of gspread.
• In this sample script, the values are processed in the list. So when you retrieved the values from the merged cells, please retrieve them from the list values.
• 方法:sheetssheets.get

这篇关于获取合并/合并的单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！