Spreadsheet documents contain data in a row and column format and it will help to analyze and manipulate data easily. The main advantages of a spreadsheet are that it requires minimal training, is customizable, and is free to use. Most organizations are commonly using spreadsheet documents for business purposes.
Here in this blog, we can discuss about how can we connect, read and write style formats of spreadsheet files using python.
There are some libraries available for this like xlrd and openpyxl libraries.
Here we can take a look at how to use the openpyxl library to connect with spreadsheet documents and read and write style formats. Using the openpyxl library we can access files with xlsx/xlsm/xltx/xltm extensions.
We can use the load_workbook function of openpyxl when we need to access an already created spreadsheet file.
load_workbook(filename = 'c:/files/myfiles/myspreadsheet.xlsx’)- if we have the file on our disk then we can specify the filename parameter to access the spreadsheet document.
Sometimes we need to access the spreadsheet file from the document content. We can take a look at how we can do that.attachment = self.env['ir.attachment'].search([('id', '=', args[0])])
bin_data = base64.b64decode(attachment.datas)
data = io.BytesIO(bin_data)
wb = openpyxl.load_workbook(data)
sheet = wb.worksheets[0]
wb.copy_worksheet(sheet)
sheet = wb.active
row_count = sheet.max_row
column_count = sheet.max_column
Here I have got the spreadsheet data from attachment.datas and decoded this binary string to normal form.
bytesIO - used to manipulate the binary data in memory. It helps to create file-like objects.
openpyxl.load_workbook(data) - Using the load_workbook we can open an existing workbook of a spreadsheet and read, and write to this spreadsheet.
Copy_worksheet - to create copies of an existing worksheet.
Wb.worksheets[0] - To get the worksheet object
wb.active-To get the active worksheet.
ws1 = wb.create_sheet(“") - we can create a new worksheet using this create_sheet().
sheet.max_row - To get the total number of rows in a spreadsheet file.
sheet.max_column - To get the total number of columns in a spreadsheet file.
We have used some styling tools like bold italics to change the view of our content. Now we can discuss how to read and write styles from spreadsheets using openpyxl. For that, we can import PatternFill, Border, Side, Alignment, Protection, Font, Color, fill from openpyxl.styles. These elements contain corresponding styles for example ‘font’ element have the bold italic underline details of each cells.
We can read styles from cell like the below examples
cell_value = sheet.cell(1,1)
value = cell_value.value
bold = cell_value.font.bold
italics = cell_value.font.italic
underline = cell_value.font.underline
alignment = cell_value.alignment.horizontal
vertical_align = cell_value.alignment.vertical
Sheet.cell - To access each cell
Cell_value.value - To get the value stored in the cell
Here I have read bold italic horizontal, vertical alignment from spreadsheet file using font and alignment.
For writing styles to a cell we can use the below code block
cell_value = sheet.cell(1, 1)
cell_value.font = Font(size=20, bold=True,
italic=False, underline="single")
cell_value.alignment = Alignment(horizontal=’center’,
vertical=’bottom’)
wb.save(Path(Path.home(), 'sample.xlsx'))
We can dynamically set these size bold horizontal values by mentioning variables with values instead of direct values.
Cell_value.font =Font(size=fontsize, bold=bold_value). These variables contain dynamic values.
We can save these spreadsheet files using wb.save and if we want to mention the path dynamically we can use path.home() which is imported from ‘pathlib’ library.
In this way, we can connect, read and write content from spreadsheet files using openpyxl library.