CODEWITHSUNDEEP
pythonopenpyxl
jiy
jiy

Reputation: 21

openpyxl copy from one workbook to another

I am trying to copy the cells from one workbook to another, to do some manipulation. While i am able to assign style/value and assign it to new workbook i am unable to assign the font,fill from the existing workbook to new workbook. Below is the snippet of my code

from openpyxl import load_workbook
from openpyxl import Workbook
File = load_workbook(filename='testcopy.xlsx')
FileSheets = File.get_sheet_names()
AcSheet = File.active

write_file = Workbook()
wr_ac_sheet = write_file.active
wr_ac_sheet['A1'].value = AcSheet['A1'].value
wr_ac_sheet['A1'].style = AcSheet['A1'].style
write_file.save('copied_excel.xlsx')

This works fine, but if i use 

wr_ac_sheet['A1'].font= AcSheet['A1'].font

i get the below error

 File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 90, in write_data
stylesheet = write_stylesheet(self.workbook)
File "C:\Python27\lib\site-packages\openpyxl\styles\stylesheet.py", line 206, in write_stylesheet
stylesheet.fonts = wb._fonts
File "C:\Python27\lib\site-packages\openpyxl\descriptors\sequence.py", line 27, in __set__
seq = [_convert(self.expected_type, value) for value in seq]
File "C:\Python27\lib\site-packages\openpyxl\descriptors\base.py", line 59, in _convert
raise TypeError('expected ' + str(expected_type))
TypeError: expected <class 'openpyxl.styles.fonts.Font'>

If i try to print the existing workbook font, i get this, so it's reading properly

print wr_ac_sheet['A1'].font

<openpyxl.styles.fonts.Font object>
Parameters:
name='Calibri', charset=None, family=2.0, b=False, i=False, strike=None, 
outline=None, shadow=None, condense=None, color= 
<openpyxl.styles.colors.Color 
 object>
Parameters:
tint=0.0, auto=None, theme=1L, rgb=None, indexed=None, type='theme', 
extend=None, sz=11.0, u=None, vertAlign=None, scheme='minor'

i would like to assign all the properties of the cell (similar to format painter) to the new workbook, any guidance on how to do that?

Upvotes: 2

Views: 6253

Answers (2)

airween
airween

Reputation: 6193

Why do you set the explicit style?

#!/usr/bin/python3
# -*- coding: utf8 -*-

import sys
import openpyxl
import warnings

warnings.simplefilter("ignore")

xlsxfile = sys.argv[1]
xlscopy = sys.argv[2]

wb = openpyxl.load_workbook(xlsxfile)

sheet = wb['Sheet1']
val = sheet['J7'] # set up an initial value, eg. 34

val.value = 2
wb.save(xlscopy)

This works for me for big and complex formatted XLSX files.

Upvotes: 0

Charlie Clark
Charlie Clark

Reputation: 19507

As explained in the documentation you need to copy the style information.

Upvotes: 0

Related Questions