There are different modules in which we are handling the excel. Mostly we are using xlrd , xlswriter, etc.
Here we are discussing the functions in xlswriter. Because in odoo mostly use the excel for generating different types of reports. By using the xlswriter we can only write data to an excel
Workbook() :- will create an excel workbook object
Eg: my_excel = xlswriter.Workbook(‘First sheet’)
This will create an workbook with sheet name as ‘First sheet’ and my_excel is the object of the excel.
add_worksheet() :- This will create a worksheet in a workbook.
Eg: worksheet1 = my_excel.add_worksheet(‘New sheet’)
write() :- This function is used to write data to a particular cell in which first 2 arguments are row and column respectively. And the third argument is the value.
Eg: worksheet1.write(0, 0, ‘Names’)
This will write Names to the cell A1 of that particular sheet.
We can also add formula to a particular cell by using the write()
Eg: worksheet1.write(2, 1, ‘=sum(B0:B2)’)
This will sum the values of B0, B1 and B2
insert_image() :- This function is used to insert image into a cell.
Eg: worksheet1.insert_image(‘A1’, ‘cybrosys.png’)
The file path can also be specified with the image name. So that it will load the image from that path.
write_number() :- This function is used to write an integer or float to the cell.
Eg : worksheet1.write_number(0, 0, 123456)
write_blank() :- This function is used to write blank space into a cell. The cell will be an empty cell. Ie, a none value.
Eg : worksheet1.write_blank(0,0, None)
write_datetime() :- This function is used to write the datetime details into a cell.
Eg: date_time = datetime.datetime.strptime('2013-01-23', '%Y-%m-%d')
worksheet1.write_blank(0, 0, date_time)
write_url() :- This function is used to write a url into a cell. In normal usage both write() and write_url() are same but by using the write_url() you can use a link and a string name separately..
Eg: worksheet1.write_url(0, 0, 'https://www.cybrosys.com', string='Cybrosys')
set_row() :- This function is used to set the row height of a row.
Eg: worksheet1.set_row(0, 20)
set_column() :- This function is used to set the width of columns in a particular range.
Eg: worksheet1.set_column(0, 2, 20)
data_validation() :- This function is used to construct an Excel data validation. The data validation can be applied to a single cell or a range of cells. With Row/Column notation you must specify all four cells in the range: (first_row, first_col, last_row, last_col). If you need to refer to a single cell set the last_ values equal to the first_ values. With A1 notation you can refer to a single cell or a range of cells.
Eg : worksheet.data_validation(0, 0, 4, 1, {parameters})
There are different parameters for the data_validation() which are below
validate
| The validate parameter is used to set the type of data that you wish to validate. It is always required and it has no default value. Allowable values are: integer, decimal, list, date, time, length, custom, any
|
criteria
| The criteria parameter is used to set the criteria by which the data in the cell is validated. It is almost always required except for the list, custom and any validate options. It has no default value. The allowed values are between, not between, ==, !=, <, >, <=, >=
|
value
| The value parameter is used to set the limiting value to which the criteria is applied. It is always required and it has no default value. You can also use the synonyms minimum or source to make the validation a little clearer and closer to Excel’s description of the parameter
|
maximum
| The maximum parameter is used to set the upper limiting value when the criteria is either 'between' or 'not between':
|
ignore_blank
| The ignore_blank parameter is used to toggle on and off the ‘Ignore blank’ option in the Excel data validation dialog. When the option is on the data validation is not applied to blank data in the cell. It is on by default
|
dropdown
| The dropdown parameter is used to toggle on and off the ‘In-cell dropdown’ option in the Excel data validation dialog. When the option is on a dropdown list will be shown for list validations. It is on by default. |
input_title
| The input_title parameter is used to set the title of the input message that is displayed when a cell is entered. It has no default value and is only displayed if the input message is displayed. The maximum title length is 32 characters. |
input_message
| The input_message parameter is used to set the input message that is displayed when a cell is entered. It has no default value
|
show_input
| The show_input parameter is used to toggle on and off the ‘Show input message when cell is selected’ option in the Excel data validation dialog. When the option is off an input message is not displayed even if it has been set using input_message. It is on by default. |
error_title
| The error_title parameter is used to set the title of the error message that is displayed when the data validation criteria is not met. The default error title is ‘Microsoft Excel’. The maximum title length is 32 characters. |
error_message
| The error_message parameter is used to set the error message that is displayed when a cell is entered. The default error message is “The value you entered is not valid. A user has restricted values that can be entered into the cell
|
error_type
| The error_type parameter is used to specify the type of error dialog that is displayed. There are 3 options are 'stop', 'warning' and 'information'. The default value is 'stop'.
|
show_error
| The show_error parameter is used to toggle on and off the ‘Show error alert after invalid data is entered’ option in the Excel data validation dialog. When the option is off an error message is not displayed even if it has been set using error_message. It is on by default.
|
condition_format() :- This function is used to add formatting to a cell or range of cells based on user-defined criteria.
Eg: worksheet1.conditional_format('B3:K12', {'type': 'cell',
'criteria': '>=',
'value': 50,
'format': format1})
Type
| Parameters
|
| Cell | Criteria |
| Value |
| Minimum |
| Maximum |
| Format |
| Date | Criteria |
| Value |
| Minimum |
| Maximum |
| Format |
| Time_period | Criteria |
| Format |
| Text | Criteria |
| Value |
| Format |
| Average | Criteria |
| Format |
| Duplicate | Format |
| Unique | Format |
| Top | Criteria |
| Value |
| Format |
| Bottom | Criteria |
| Value |
| Format |
| Blanks | Format |
| No_blanks | Format |
| Errors | Format |
| Formula | Criteria |
| Format |
| 2_color_scale | Min_type |
| Max_type |
| Min_value |
| Max_value |
| Min_color |
| Max_color |
| 3_color_scale | min_type |
| mid-type |
| max_type |
| min-value |
| mid_value |
| max_value |
| min_color |
| mid_color |
| max_color |
| data_bar | min_type |
| max_type |
| min_value |
| max_value |
| bar_only |
| bar_color |
| bar_solid |
| bar_negative_color |
| bar_border_color |
| bar_negative_border_color |
| bar_negative_color_same |
| bar_negative_border_color_same |
| bar_no-border |
| bar_direction |
| bar_axis_position |
| bar_axis_color |
| data_bar_2010 |
| icon_set | icon_style |
| reverse_icons |
| icons |
| icons_only |
write_comment() :- This function is used to add a comment to a cell. A comment is indicated in Excel by a small red triangle in the upper right-hand corner of the cell. Moving the cursor over the red triangle will reveal the comment.
Eg: worksheet1.write_comment(0, 0, ‘My comment in the cell’)
merge_range() :- This function is used to merge different cell in excel. Also this will write data to the merged cells.
Eg: worksheet1.merge_range(0, 0, 2, 2, ‘Merged Cells’, format_ogject)
freeze_panes() :- This function is used to freeze the row and column in excel.
Eg: worksheet1.freeze_panes(1, 1)
This will freeze the first row and column of the sheet.
split_panes() :- This function is used to divide the excel into horizontal or vertical regions known as panes. The splits between the panes will be visible to the user and each pane will have its own scroll bars.
right_to_left() :- This function is used to change the default direction of the worksheet from left-to-right, with the A1 cell in the top left, to right-to-left, with the A1 cell in the top right
Eg: worksheet1.right_to_left()
set_tab_color() :- This function is used to set the tab color of the excel.
Eg: worksheet1.set_tab_color(‘red’)
add_format() :- Used to set format to a cell
Eg :- format = my_excel.add_format({'bold': True})
Here in the above example format is the object of the created format. We can pass different style formats as dictionary keys and values.
Few formats are below:
Category
| Description
| Property
| Method Name
|
Font
| Font type
| ‘font_name’
| set_font_name()
|
| Font size
| ‘font_size’
| set_font_size()
|
| Font color
| ‘font_color’ | set_font_color()
|
| Bold
| ‘bold’
| set_bold()
|
| Italic
| ‘italic’
| set_italic()
|
| Underline
| ‘underline’
| set_underline()
|
| Strikeout
| ‘font_strikeout’ | set_font_strikeout()
|
| Super/Subscript
| ‘font_script’
| set_font_script()
|
Number
| Numeric format
| ‘num_format’
| set_num_format()
|
Protection
| Lock cells
| ‘locked’
| set_locked()
|
| Hide formulas
| ‘hidden’
| set_hidden()
|
Alignment
| Horizontal align
| ‘align’
| set_align()
|
| Vertical align
| ‘valign’
| set_align()
|
| Rotation
| ‘rotation’
| set_rotation()
|
| Text wrap
| ‘text_wrap’
| set_text_wrap()
|
| Reading order
| ‘reading_order’
| set_reading_order()
|
| Justify last
| ‘text_justlast’
| set_text_justlast()
|
| Center across
| ‘center_across’
| set_center_across()
|
| Indentation
| ‘indent’ | set_indent()
|
| Shrink to fit’
| ‘shrink’
| set_shrink()
|
Pattern
| Cell pattern
| ‘pattern’
| set_pattern()
|
| Background color
| ‘bg_color’
| set_bg_color()
|
| Foreground color
| ‘fg_color’
| set_fg_color()
|
Border
| Cell border
| ‘border’
| set_border()
|
| Bottom border
| ‘bottom’
| set_bottom()
|
| Top border
| ‘top’
| set_top()
|
| Left border
| ‘left’
| set_left()
|
| Right border
| ‘right’
| set_right()
|
| ‘Border color
| ‘border_color’ | set_border_color()
|
| Bottom color
| ‘bottom_color’
| set_bottom_color()
|
| Top color
| ‘top_color’
| set_top_color()
|
| Left color
| ‘left_color’ | set_left_color()
|
| Right color
| ‘right_color’
| set_right_color()
|
I hope you understand different functions that are used in odoo to prepare different excel formats by using the python xlswriter package.
You can also check Odoo Accounting Reports in Excel