Borders in Excel File
In this example I am going to show you how to create borders in excel file. I will create two sheets in the same excel file. In one sheet I will show how to create borders around rows and columns without writing any text. In the other sheet I will show you how to create borders around rows and columns with text.
The output excel file extension is .xsl. Borders around text are required when you want to emphasize some particular texts from other texts in the excel file. For example, you may want to apply border around headers of the columns, so it can be easily distinguished from other normal texts.
Prerequisites
Python 3.9.1, xlwt 1.3.0 (pip install xlwt)
Create Borders in Excel
Now I will show you how to create borders around text or around empty rows/columns in the excel file.
Make sure xlwt package exists in your system for writing to the excel file. If it does not exist then you can install it by using the command pip install xlwt.
easyxf() function is found in the documentation and has the following details:
xlwt.Style.easyxf(strg_to_parse='', num_format_str=None, field_sep=', ', line_sep=';', intro_sep=':', esc_char='\\', debug=False)This function is used to create and configure XFStyle objects for use with (for example) the Worksheet.write() method.
It takes a string to be parsed to obtain attribute values for Alignment, Borders, Font, Pattern and Protection objects.
Various synonyms including color/colour, center/centre and gray/grey are allowed. Case is irrelevant (except maybe in font names). - may be used instead of _.
Example: font: bold on; align: wrap on, vert centre, horiz center
Parameters: num_format_str –
To get the “number format string” of an existing cell whose format you want to reproduce, select the cell and click on Format/Cells/Number/Custom. Otherwise, refer to Excel help.
Examples: "#,##0.00", "dd/mm/yyyy"
Returns: An XFstyle object.
First I am creating styles for borders which will be used while writing to the excel sheet:
tl = easyxf('border: left thick, top thick')
t = easyxf('border: top thick')
tr = easyxf('border: right thick, top thick')
r = easyxf('border: right thick')
br = easyxf('border: right thick, bottom thick')
b = easyxf('border: bottom thick')
bl = easyxf('border: left thick, bottom thick')
l = easyxf('border: left thick')Next I have created Workbook object:
w = Workbook()Then create a sheet in the excel file:
ws = w.add_sheet('Border')The add_sheet() function has the following structure:
add_sheet(sheetname, cell_overwrite_ok=False)This method is used to create Worksheets in a Workbook.
Parameters:
sheetname – The name to use for this sheet, as it will appear in the tabs at the bottom of the Excel application.
cell_overwrite_ok – If True, cells in the added worksheet will not raise an exception if written to more than once.
Returns:
The Worksheet that was added.
Finally, write to the excel sheet:
ws.write(1,1,style=tl)
ws.write(1,2,style=t)
ws.write(1,3,style=tr)
ws.write(2,3,style=r)
ws.write(3,3,style=br)
ws.write(3,2,style=b)
ws.write(3,1,style=bl)
ws.write(2,1,style=l)The output of the above code will be similar to the following image:
The write() function has the following format:
write(r, c, label='', style=<xlwt.Style.XFStyle object>)This method is used to write a cell to a Worksheet.
Parameters:
r – The zero-relative number of the row in the worksheet to which the cell should be written.
c – The zero-relative number of the column in the worksheet to which the cell should be written.
label:
The data value to be written.
An int, long, or Decimal instance is converted to float.
A unicode instance is written as is. A bytes instance is converted to unicode using the encoding, which defaults to ascii, specified when the Workbook instance was created.
A datetime, date or time instance is converted into Excel date format (a float representing the number of days since (typically) 1899-12-31T00:00:00, under the pretence that 1900 was a leap year).
A bool instance will show up as TRUE or FALSE in Excel.
None causes the cell to be blank: no data, only formatting.
An xlwt.Formula instance causes an Excel formula to be written.
style:
A style, also known as an XF (extended format), is an XFStyle object, which encapsulates the formatting applied to the cell and its contents.
XFStyle objects are best set up using the easyxf() function. They may also be set up by setting attributes in Alignment, Borders, Pattern, Font and Protection objects then setting those objects and a format string as attributes of an XFStyle object.
Now I will put borders around texts, the borders are around empty cells. I have created another sheet to create borders around texts.
ws = w.add_sheet('Border and Data')
ws.write(1,1,'Second Row Second Column',style=tl)
ws.write(1,2,'Second Row Third Column',style=t)
ws.write(1,3,'Second Row Fourth Column',style=tr)
ws.write(2,3,'Third Row Fourth Column',style=r)
ws.write(3,3,'Fourth Row Fourth Column',style=br)
ws.write(3,2,'Fourth Row Third Column',style=b)
ws.write(3,1,'Fourth Row Second Column',style=bl)
ws.write(2,1,'Third Row Second Column',style=l)The output of the above code snippets will be similar to the following image:
Finally the following line of code write to the excel file:
w.save('borders.xls')The save() function has the following format:
save(filename_or_stream)This method is used to save the Workbook to a file in native Excel format.
Parameters: filename_or_stream – This can be a string containing a filename of the file, in which case the excel file is saved to disk using the name provided. It can also be a stream object with a write method, such as a StringIO, in which case the data for the excel file is written to the stream.
Hope you got an idea how to create borders in the excel file using Python program.