XLwings formatting

The code below show a couple of functions I set up for formatting in XLwings. I was regularly applying some standard colours and borders etc. so decided that the easiest way to do with was to create my own functions that I can then call from another script. I have examples of this in the code below and the sample spreadsheet can be found here. 

The code:

	# -*- coding: utf-8 -*-
	"""
	Created on Sat Nov 18 13:04:46 2017
	@author: Gary.Manley
	"""
	import xlwings as xw
	from xlwings import constants
	### setting up RGB Colours
	def rgb(col):
	if col == 'Turq':
	R = 0
	G = 165
	B = 185
	elif col == 'Blue':
	R = 55
	G = 70
	B = 150
	elif col == 'Pink':
	R = 230
	G = 30
	B = 105
	elif col == 'Oran':
	R = 245
	G = 150
	B = 20
	elif col == 'Yell':
	R = 255
	G = 220
	B = 50
	elif col == 'Pur':
	R = 130
	G = 55
	B = 140
	elif col == 'DBlu':
	R = 64
	G = 86
	B = 106
	elif col == 'Grey':
	R = 128
	G = 128
	B = 128
	elif col == 'White':
	R = 255
	G = 255
	B = 255
	elif col == 'Black':
	R = 0
	G = 0
	B = 0
	return(R,G,B)
	### formats range in XLwings
	def formatrange(xlrange,fontcol,backcol,border_weight,border_style,bot_border,align):
	### set up background colour
	xw.Range(xlrange).color = rgb(backcol)
	### Set up Font Colour
	xw.Range(xlrange).api.Font.Color = xw.utils.rgb_to_int(rgb(fontcol))
	### set up border weight
	if border_weight > 0:
	xw.Range(xlrange).api.Borders(1).Weight = border_weight ### sets vertical
	xw.Range(xlrange).api.Borders(2).Weight = border_weight ### sets vertical
	xw.Range(bot_border).api.Borders(3).Weight = border_weight ### sets horizontal top needs to be one row more
	### set up border style
	if border_style > 0:
	xw.Range(xlrange).api.Borders(1).LineStyle = border_style
	xw.Range(xlrange).api.Borders(2).LineStyle = border_style
	xw.Range(bot_border).api.Borders(3).LineStyle = border_style
	### set up alignment
	if align =='Center':
	xw.Range(xlrange).api.HorizontalAlignment = constants.HAlign.xlHAlignCenter
	if align =='Right':
	xw.Range(xlrange).api.HorizontalAlignment = constants.HAlign.xlHAlignRight
	if align =='Left':
	xw.Range(xlrange).api.HorizontalAlignment = constants.HAlign.xlHAlignLeft

view raw XLWingsFormat.py hosted with ❤ by GitHub

Using this code

	# -*- coding: utf-8 -*-
	"""
	Created on Sat Oct 28 20:21:04 2017
	@author: garym
	"""
	import xlwings as xw
	## I know that doing it this way is not recommended for code that gets shared but it works for me for this purpose
	import sys
	sys.path.append(r'C:\Users\garym\Documents\Python From Work\Python\Other')
	import XLFormatting as XL
	## Open Workbook
	wb = xw.Book()
	## Set value to Hellow World
	xw.Range('A1').value = 'Hello World'
	xw.Range('B5').value = 'Abc 123'
	## Format the range using the function
	XL.formatrange('A1','White','Black',1,1,'A1:A2','Right')
	XL.formatrange('B5','Pink','Turq',1,2,'B5:B6','Center')
	## Autofit the columns
	xw.Range('A1:AI100').autofit()
	## Save workbook
	filename = (r'C:\Users\garym\Documents\Python From Work\Python\Other\test.xlsx')
	wb.save(filename)
	wb.close()

view raw XLformatTest.py hosted with ❤ by GitHub