openpyxl partice

0_openpyxl.md

動態render cells 透用style, merged.

1_write.py

from openpyxl import Workbook
from openpyxl.chart.layout import Layout, ManualLayout
from openpyxl import load_workbook
from openpyxl.styles import NamedStyle, Font, Border, Side
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
from openpyxl.utils import coordinate_from_string, column_index_from_string, get_column_letter, coordinate_to_tuple


font = Font(name='Arial',
                 size=11,
                 bold=False,
                 italic=False,
                 vertAlign=None,
                 underline='none',
                 strike=False,
                 color='FF000000')
fill = PatternFill("solid", fgColor="FF0000")


highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)

data = [
    [{
        'value': 'A1',
        'font': font,
        'fill': fill
    },{
        'value': 'B1',
        'font': font,
        'fill': fill
    }],
    [{
        'value': 'A2',
        'font': font,
        'fill': fill,
        'merge': {
            'rows': 1,
            'cols': 2,
        }
    },{
        'value': 'B2',
        'font': font,
        'fill': fill,
    }],
    [{
        'value': 'A3',
        'font': font,
        'fill': fill,
    },{
        'value': 'B3',
        #'font': font,
        #'fill': fill,
        'named_styled': highlight
    }]
]


wb = Workbook()
#wb.add_named_style(highlight)

ws = wb.active
for j,row in enumerate(data):
    for i, cell in enumerate(row):
        #print i, j
        coordinate = '%s%s' % (get_column_letter(i + 1), j + 1,)
        _cell =  ws[coordinate]
        _cell.value = cell['value']
        if 'named_styled' in cell:
            _cell.style = cell['named_styled']
        else:
            _cell.font = cell['font']
            _cell.fill = cell['fill']
        if 'merge' in cell:
            ws.merge_cells(
                start_row=j + 1,
                start_column= i + 1,
                end_row=j + cell['merge']['rows'], 
                end_column= i + cell['merge']['cols'])
        print _cell.coordinate
wb.save("tmp/output.xlsx") 

2_insert_at.py

def insert_col(x):
    """Insert a empty col at `x`
    """
    target = column_index_from_string(x) - 1 


    cols = reversed(list(enumerate(ws.columns)))
    for i, col in cols:
        print i,target
        #continue
        if i > target:
            print "move", i
            #continue
            for cell in col:
                x, y = coordinate_to_tuple(cell.coordinate)
                coordinate = '%s%s' % (get_column_letter(y + 1), x)
                print 'move', cell.coordinate

                ws[coordinate].value = cell.value
                ws[coordinate].font = copy.copy(cell.font)
                ws[coordinate].fill = copy.copy(cell.fill)
        elif i == target:
            print "reset", i
            for cell in col:
                print 'reset', cell.coordinate
                
                cell.font = st.fonts.DEFAULT_FONT
                cell.fill = st.fills.DEFAULT_EMPTY_FILL
                cell.value = None
            break


#ws['A1'] = None
#ws['A1'].font = st.fonts.DEFAULT_FONT
#ws['A1'].fill = st.fills.DEFAULT_EMPTY_FILL
#ws['A1'].
insert_col('B')
wb.save("tmp/output.xlsx")