# easierexcel
EasierExcel allows for an easy way to get and update cell values within Excel sheets.
OpenPyXL is used to do the bulk while easierexcel makes it much easier to use.
76% Test Coverage
Python 3.11+
## Quick Start
Install easierexcel using pip:
```bash
$ pip install easierexcel
```
### Example Table
| Name | Birth Month | Age | Null |
| ------- | ----------- | --- | ---- |
| John | John | 31 | null |
| Michael | June | 31 | null |
| Brian | August | 30 | null |
| Rob | July | 34 | null |
| Allison | September | 32 | null |
### Code Example
```python
from easierexcel import Excel, Sheet
# class init
excel = Excel('example_excel.xlsx')
# formatting options
options = {
"shrink_to_fit_cell": True,
"header": {"bold": True, "font_size": 16},
"default_align": "center_align",
"left_align": [
"Name",
],
"percent": [
"%",
"Percent",
"Discount",
"Rating Comparison",
"Probable Completion",
],
"currency": ["Price", "MSRP", "Cost"],
"integer": ["ID", "Number"],
"date": ["Last Updated", "Date"],
}
example = Sheet(excel, "Name", sheet_name="Example", options=options)
# deleting
example.delete_column("Null")
example.delete_row("John")
# adding a new line
data = {
"Name":"Billy",
"Birth Month":"December",
"Age":5,
}
example.add_new_line(cell_dict=data)
# accessing and updating
example.get_cell("Michael", "Birth Month") # -> June
example.update_cell("Michael", "Birth Month", "April")
example.get_cell("Michael", "Birth Month") # -> April
excel.save() # Saves the excel file
```
### Final Table
| Name | Birth Month | Age |
| ------- | ----------- | --- |
| Michael | April | 31 |
| Brian | August | 30 |
| Rob | July | 34 |
| Allison | September | 32 |
| Billy | December | 5 |
## Documentation
### Excel Class
Allows retreiving, adding, updating, deleting and formatting cells within Excel.
#### **init** Function
`filename` is the path to the excel file.
`use_logging` allows disabling all logs when running.
`log_file` sets the path for logging.
`log_level` Sets the logging level of this logger.
level must be an int or a str.
#### save Function
Backs up the excel file before saving the changes if `backup` is True.
It will keep trying to save until it completes in case of permission
errors caused by the file being open.
`use_print` determines if info for the saving progress will be printed.
`force_save` can be used to make sure a save occurs.
#### open_excel Function
Opens the current excel file if it still exists and then exits.
Saves changes if `save` is True.
The `test` arg is only used for testing.
### Sheet Class
#### **init** Function
Allows interacting with any one sheet within the excel_object given.
`excel_object` Excel object created using Excel class.
`column_name` Name of the main column you intend to use for
identifying rows.
`sheet_name` Name of the sheet to use.
`options` used to determine auto formatting.
#### create_dataframe Function
Creates a panda dataframe using the current used sheet.
`date_cols` sets the columns with dates.
`na_vals` sets what should be considered N/A values that are ignored.
#### indirect_cell Function
Returns a string for setting an indirect cell location to a cell.
If you want the cell to be relative to column names then set `cur_col`
to the column name the formula is going into and `ref_col` to the
column name you are wanting to reference.
If you know it is simply references a cell that is 3 to the right or
left then just give `left` or `right` that value. Only one direction
can be greater than 0.
You can also use `manual_set` to set the indirect cell offset manually
using a positive or negative number.
#### get_column_index Function
Creates the column index.
#### get_row_index Function
Creates the row index based on `col_name`.
#### list_in_string Function
Returns True if any entry in the given `list` is in the given `string`.
Setting `lowercase` to True allows you to make the check
set all to lowercase.
#### get_row_col_index Function
Gets the row and column index for the given values if they exist.
Will return the `row_value` and `column_value` if they are
numbers already.
#### extract_hyperlink Function
Extracts the hyperlink target from a `cell_value` with the hyperlink
formula.
This is only needed if excel has not applied the hyperlink yet.
This often happens when you click on the cell with the hyperlink
formula.
#### get_cell_by_key Function
Gets the cell value based on the `row_key` and `column_key`.
This is basically the index in excel for columns and rows.
If the cell is a hyperlink that is currently clickable,
the hyperlink target will be returned.
#### get_cell Function
Gets the cell value based on the `row_value` and `column_value`.
If the cell is a hyperlink that is currently clickable,
the hyperlink target will be returned.
#### update_index Function
Updates the current row with the `column_key` in the row_idx variable.
#### update_cell_by_key Function
Updates the cell based on `row_key` and `col_key` to `new_val`.
This is basically the index in excel for columns and rows.
Returns True if cell was updated and False if it was not updated.
`replace` allows you to determine if a cell will have its
existing value changed if it is not None.
#### update_cell Function
Updates the cell based on `row_val` and `col_val` to `new_val`.
Returns True if cell was updated and False if it was not updated.
`replace` allows you to determine if a cell will have its
existing value changed if it is not None.
#### add_new_line Function
Adds cell_dict onto a new line within the excel sheet.
The column_name must be given a value.
If dictionary keys match existing columns within the set sheet,
it will add the value to that column.
#### delete_row Function
Deletes row by `column_value`.
#### delete_column Function
Deletes column by `column_name`.
#### set_border Function
Sets the given `cell` border to cover all sides with the given `style`.
#### set_fill Function
Sets the given `cell` to have fill with `color` and `fill_type`
#### set_style Function
Sets the given `cell` to the given `format` or general by default.
#### format_picker Function
Determines what formatting to apply to a column.
#### get_column_formats Function
Gets the formats to use for each column.
#### format_header Function
Formats the top header of the sheet.
#### format_cell Function
Formats a cell based on the `column` name using `row_i` and `col_i`.
#### format_row Function
Formats the entire row by `row_identifier`
#### format_all_cells Function
Auto formats all cells.
Raw data
{
"_id": null,
"home_page": "https://github.com/Concrete18/easierexcel",
"name": "easierexcel",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "excel",
"author": "Concrete18",
"author_email": "<michaelericson19@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/b7/04/127b1326659c330373c333d5ecd0bfda6f1d35378db9d4b08fe00a96fa59/easierexcel-0.10.1.tar.gz",
"platform": null,
"description": "# easierexcel\r\n\r\nEasierExcel allows for an easy way to get and update cell values within Excel sheets.\r\n\r\nOpenPyXL is used to do the bulk while easierexcel makes it much easier to use.\r\n\r\n76% Test Coverage\r\n\r\nPython 3.11+\r\n\r\n## Quick Start\r\n\r\nInstall easierexcel using pip:\r\n\r\n```bash\r\n$ pip install easierexcel\r\n```\r\n\r\n### Example Table\r\n\r\n| Name | Birth Month | Age | Null |\r\n| ------- | ----------- | --- | ---- |\r\n| John | John | 31 | null |\r\n| Michael | June | 31 | null |\r\n| Brian | August | 30 | null |\r\n| Rob | July | 34 | null |\r\n| Allison | September | 32 | null |\r\n\r\n### Code Example\r\n\r\n```python\r\n from easierexcel import Excel, Sheet\r\n\r\n # class init\r\n excel = Excel('example_excel.xlsx')\r\n\r\n # formatting options\r\n options = {\r\n \"shrink_to_fit_cell\": True,\r\n \"header\": {\"bold\": True, \"font_size\": 16},\r\n \"default_align\": \"center_align\",\r\n \"left_align\": [\r\n \"Name\",\r\n ],\r\n \"percent\": [\r\n \"%\",\r\n \"Percent\",\r\n \"Discount\",\r\n \"Rating Comparison\",\r\n \"Probable Completion\",\r\n ],\r\n \"currency\": [\"Price\", \"MSRP\", \"Cost\"],\r\n \"integer\": [\"ID\", \"Number\"],\r\n \"date\": [\"Last Updated\", \"Date\"],\r\n }\r\n example = Sheet(excel, \"Name\", sheet_name=\"Example\", options=options)\r\n\r\n # deleting\r\n example.delete_column(\"Null\")\r\n example.delete_row(\"John\")\r\n\r\n # adding a new line\r\n data = {\r\n \"Name\":\"Billy\",\r\n \"Birth Month\":\"December\",\r\n \"Age\":5,\r\n }\r\n example.add_new_line(cell_dict=data)\r\n\r\n # accessing and updating\r\n example.get_cell(\"Michael\", \"Birth Month\") # -> June\r\n\r\n example.update_cell(\"Michael\", \"Birth Month\", \"April\")\r\n\r\n example.get_cell(\"Michael\", \"Birth Month\") # -> April\r\n\r\n excel.save() # Saves the excel file\r\n```\r\n\r\n### Final Table\r\n\r\n| Name | Birth Month | Age |\r\n| ------- | ----------- | --- |\r\n| Michael | April | 31 |\r\n| Brian | August | 30 |\r\n| Rob | July | 34 |\r\n| Allison | September | 32 |\r\n| Billy | December | 5 |\r\n\r\n## Documentation\r\n\r\n### Excel Class\r\n\r\nAllows retreiving, adding, updating, deleting and formatting cells within Excel.\r\n\r\n#### **init** Function\r\n\r\n`filename` is the path to the excel file.\r\n\r\n`use_logging` allows disabling all logs when running.\r\n\r\n`log_file` sets the path for logging.\r\n\r\n`log_level` Sets the logging level of this logger.\r\nlevel must be an int or a str.\r\n\r\n#### save Function\r\n\r\nBacks up the excel file before saving the changes if `backup` is True.\r\n\r\nIt will keep trying to save until it completes in case of permission\r\nerrors caused by the file being open.\r\n\r\n`use_print` determines if info for the saving progress will be printed.\r\n\r\n`force_save` can be used to make sure a save occurs.\r\n\r\n#### open_excel Function\r\n\r\nOpens the current excel file if it still exists and then exits.\r\n\r\nSaves changes if `save` is True.\r\n\r\nThe `test` arg is only used for testing.\r\n\r\n### Sheet Class\r\n\r\n#### **init** Function\r\n\r\nAllows interacting with any one sheet within the excel_object given.\r\n\r\n`excel_object` Excel object created using Excel class.\r\n\r\n`column_name` Name of the main column you intend to use for\r\nidentifying rows.\r\n\r\n`sheet_name` Name of the sheet to use.\r\n\r\n`options` used to determine auto formatting.\r\n\r\n#### create_dataframe Function\r\n\r\nCreates a panda dataframe using the current used sheet.\r\n\r\n`date_cols` sets the columns with dates.\r\n\r\n`na_vals` sets what should be considered N/A values that are ignored.\r\n\r\n#### indirect_cell Function\r\n\r\nReturns a string for setting an indirect cell location to a cell.\r\n\r\nIf you want the cell to be relative to column names then set `cur_col`\r\nto the column name the formula is going into and `ref_col` to the\r\ncolumn name you are wanting to reference.\r\n\r\nIf you know it is simply references a cell that is 3 to the right or\r\nleft then just give `left` or `right` that value. Only one direction\r\ncan be greater than 0.\r\n\r\nYou can also use `manual_set` to set the indirect cell offset manually\r\nusing a positive or negative number.\r\n\r\n#### get_column_index Function\r\n\r\nCreates the column index.\r\n\r\n#### get_row_index Function\r\n\r\nCreates the row index based on `col_name`.\r\n\r\n#### list_in_string Function\r\n\r\nReturns True if any entry in the given `list` is in the given `string`.\r\n\r\nSetting `lowercase` to True allows you to make the check\r\nset all to lowercase.\r\n\r\n#### get_row_col_index Function\r\n\r\nGets the row and column index for the given values if they exist.\r\n\r\nWill return the `row_value` and `column_value` if they are\r\nnumbers already.\r\n\r\n#### extract_hyperlink Function\r\n\r\nExtracts the hyperlink target from a `cell_value` with the hyperlink\r\nformula.\r\n\r\nThis is only needed if excel has not applied the hyperlink yet.\r\nThis often happens when you click on the cell with the hyperlink\r\nformula.\r\n\r\n#### get_cell_by_key Function\r\n\r\nGets the cell value based on the `row_key` and `column_key`.\r\nThis is basically the index in excel for columns and rows.\r\n\r\nIf the cell is a hyperlink that is currently clickable,\r\nthe hyperlink target will be returned.\r\n\r\n#### get_cell Function\r\n\r\nGets the cell value based on the `row_value` and `column_value`.\r\n\r\nIf the cell is a hyperlink that is currently clickable,\r\nthe hyperlink target will be returned.\r\n\r\n#### update_index Function\r\n\r\nUpdates the current row with the `column_key` in the row_idx variable.\r\n\r\n#### update_cell_by_key Function\r\n\r\nUpdates the cell based on `row_key` and `col_key` to `new_val`.\r\nThis is basically the index in excel for columns and rows.\r\n\r\nReturns True if cell was updated and False if it was not updated.\r\n\r\n`replace` allows you to determine if a cell will have its\r\nexisting value changed if it is not None.\r\n\r\n#### update_cell Function\r\n\r\nUpdates the cell based on `row_val` and `col_val` to `new_val`.\r\n\r\nReturns True if cell was updated and False if it was not updated.\r\n\r\n`replace` allows you to determine if a cell will have its\r\nexisting value changed if it is not None.\r\n\r\n#### add_new_line Function\r\n\r\nAdds cell_dict onto a new line within the excel sheet.\r\nThe column_name must be given a value.\r\n\r\nIf dictionary keys match existing columns within the set sheet,\r\nit will add the value to that column.\r\n\r\n#### delete_row Function\r\n\r\nDeletes row by `column_value`.\r\n\r\n#### delete_column Function\r\n\r\nDeletes column by `column_name`.\r\n\r\n#### set_border Function\r\n\r\nSets the given `cell` border to cover all sides with the given `style`.\r\n\r\n#### set_fill Function\r\n\r\nSets the given `cell` to have fill with `color` and `fill_type`\r\n\r\n#### set_style Function\r\n\r\nSets the given `cell` to the given `format` or general by default.\r\n\r\n#### format_picker Function\r\n\r\nDetermines what formatting to apply to a column.\r\n\r\n#### get_column_formats Function\r\n\r\nGets the formats to use for each column.\r\n\r\n#### format_header Function\r\n\r\nFormats the top header of the sheet.\r\n\r\n#### format_cell Function\r\n\r\nFormats a cell based on the `column` name using `row_i` and `col_i`.\r\n\r\n#### format_row Function\r\n\r\nFormats the entire row by `row_identifier`\r\n\r\n#### format_all_cells Function\r\n\r\nAuto formats all cells.\r\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Easy viewing/editing of Excel Files",
"version": "0.10.1",
"project_urls": {
"Homepage": "https://github.com/Concrete18/easierexcel"
},
"split_keywords": [
"excel"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "ea052fc5cd19ed667e1dfb61af52eda2ff186817247e99250207be4a2afbf243",
"md5": "df81e1c5fee73bf1985b795625bb82c8",
"sha256": "ecfbd980e1e52c3b0123e4d992f4f71055f36ea4e31534fe0ecd0cf9d96ce89e"
},
"downloads": -1,
"filename": "easierexcel-0.10.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "df81e1c5fee73bf1985b795625bb82c8",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 19568,
"upload_time": "2023-11-16T19:34:24",
"upload_time_iso_8601": "2023-11-16T19:34:24.447811Z",
"url": "https://files.pythonhosted.org/packages/ea/05/2fc5cd19ed667e1dfb61af52eda2ff186817247e99250207be4a2afbf243/easierexcel-0.10.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "b704127b1326659c330373c333d5ecd0bfda6f1d35378db9d4b08fe00a96fa59",
"md5": "c93e49c77fca2c1be48e087d76634673",
"sha256": "296a2ee3b0ed70d2ec1db7c6cf33068b15ab2e67bb8d7b18cd52a327cbd42992"
},
"downloads": -1,
"filename": "easierexcel-0.10.1.tar.gz",
"has_sig": false,
"md5_digest": "c93e49c77fca2c1be48e087d76634673",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 15090,
"upload_time": "2023-11-16T19:34:27",
"upload_time_iso_8601": "2023-11-16T19:34:27.813920Z",
"url": "https://files.pythonhosted.org/packages/b7/04/127b1326659c330373c333d5ecd0bfda6f1d35378db9d4b08fe00a96fa59/easierexcel-0.10.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-11-16 19:34:27",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "Concrete18",
"github_project": "easierexcel",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "easierexcel"
}
JSON
