Geek asks and answers

R1C1 in openpyxl

I am trying to set conditional formatting in openpyxl to emulate highlighting duplicate values. With this simple code, I should be able to highlight consecutive duplicates (but not the first value in a repeating sequence).

from pandas import *
data = DataFrame({'a':'a a a b b b c b c a f'.split()})
wb = ExcelWriter('test.xlsx')
data.to_excel(wb)
ws = wb.sheets['Sheet1']

from openpyxl.style import Color, Fill
# Create fill
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID

ws.conditional_formatting.addCellIs("B1:B1048576", 'equal', "=R[1]C", True, wb.book, None, None, redFill)
wb.save()

However, when I open it in Excel, I get an error related to conditional formatting, and the data is not highlighted as expected. Can openpyxl handle R1C1 style links?

+3
source share
2 answers

With respect to highlighting to search for duplicates of sequential values, the desired formula

=AND(B1<>"",B2=B1)

With a range starting with B2 (aka, B2: B1048576)

- , -, 1.8.3 openpyxl, 1.9.

from openpyxl import Workbook
from openpyxl.style import Color, Fill
wb = Workbook()
ws = wb.active
ws['B1'] = 1
ws['B2'] = 2
ws['B3'] = 3
ws['B4'] = 3
ws['B5'] = 7
ws['B6'] = 4
ws['B7'] = 7

# Create fill
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID

dxfId = ws.conditional_formatting.addDxfStyle(wb, None, None, redFill)
ws.conditional_formatting.addCustomRule('B2:B1048576',
   {'type': 'expression', 'dxfId': dxfId, 'formula': ['AND(B1<>"",B2=B1)']})
wb.save('test.xlsx')

:

  • :

    (: B, B1) > 1

  • , :

    ($ B $2: $2, 2) > 1

  • , :

    (1: 2, 2) > 1

RC - openpyxl excel RC-, , . , excel R1C1 A1 , R1C1 A1, .

+3

Openpyxl Excel RC.

A1, , =B2 ( ).

, Excel .

, . $B$2 B1.

, A1, Openpyxl.

+2

More articles:


All Articles