{"id":2887,"date":"2022-06-01T18:26:49","date_gmt":"2022-06-01T12:56:49","guid":{"rendered":"https:\/\/sheetstips.com\/?p=2887"},"modified":"2022-06-14T15:08:29","modified_gmt":"2022-06-14T09:38:29","slug":"working-with-excel-files-in-python-using-xlwings","status":"publish","type":"post","link":"https:\/\/sheetstips.com\/working-with-excel-files-in-python-using-xlwings\/","title":{"rendered":"Working with Excel files using Python Xlwings: Learn Excel Python Integration"},"content":{"rendered":"<p class=\"root-block-node\" data-changed=\"false\" data-paragraphid=\"15\"><strong>Use Xlwings To Read Excel:<\/strong> If you want to read or write the Python data using Excel, then using the Xlwings function is the right option for you. This is because most people would like to view or understand the data from Excel rather than open the code background such as Python. Using the Xlwings function, one can easily call the Python data into Excel. In other words, the operations that are executed in Python are displayed in Excel using the Xlwings function.<\/p>\n<p class=\"root-block-node\" data-changed=\"false\" data-paragraphid=\"16\">In this article, let us discuss how to use the Xlwings function in Excel. Read further to find out more.<\/p>\n<h2 class=\"root-block-node\" data-changed=\"false\" data-paragraphid=\"17\">What Can You Do With Xlwings?<\/h2>\n<p>Xlwings is a Python module that allows you to easily invoke\/call Python from Excel and vice versa. It quickly creates reading and writing to and from Excel using Python. It can also be customized to function as a Python Server for Excel, allowing Python and Excel to exchange data synchronously. Xlwings simplifies Excel automation with Python and may be used to generate an automatic report, create Excel embedded functions, manipulate Excel or CSV databases, and so on.<\/p>\n<p><strong>Installation of Xlwings:<\/strong><\/p>\n<p>Use the following command to install the Xlwings module:<\/p>\n<div>\n<pre>pip\u00a0install\u00a0xlwings<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<pre>Looking in indexes: https:\/\/pypi.org\/simple, https:\/\/us-python.pkg.dev\/colab-wheels\r\n\/public\/simple\/\r\nCollecting xlwings\r\nDownloading xlwings-0.27.8-py3-none-any.whl (1.0 MB)\r\n|\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 1.0 MB 5.1 MB\/s \r\nInstalling collected packages: xlwings\r\nSuccessfully installed xlwings-0.27.8<\/pre>\n<p>To begin using Xlwings, there are a few basic steps that must be followed practically every time. This includes opening an Excel file, viewing the available sheets and then selecting one.<\/p>\n<p>Here we will use Sheet1(it has only 1 sheet) of the following Excel spreadsheet as an example.<\/p>\n<div>\n<div><strong>SampleExcelFile:<\/strong><\/div>\n<\/div>\n<\/div>\n<div><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2858 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/SampleExcelFile.png\" alt=\"SampleExcelFile\" width=\"442\" height=\"335\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/SampleExcelFile.png 442w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/SampleExcelFile-300x227.png 300w\" sizes=\"(max-width: 442px) 100vw, 442px\" \/><\/div>\n<div><\/div>\n<h2>Working with Excel files using Python Xlwings<\/h2>\n<ul>\n<li><a href=\"#Reading_Excel_File_and_Selecting_a_Specific_Cell_value\">Reading Excel File and Selecting a Specific Cell value<\/a><\/li>\n<li><a href=\"#Performing_Multiple_Operations\">Performing Multiple Operations<\/a><\/li>\n<li><a href=\"#Inserting_Data_Into_an_Excel_File\">Inserting Data Into an Excel File<\/a><\/li>\n<\/ul>\n<h3 id=\"Reading_Excel_File_and_Selecting_a_Specific_Cell_value\">Method #1: Reading Excel File and Selecting a Specific Cell value<\/h3>\n<p><strong>Approach:<\/strong><\/p>\n<ul>\n<li>Import xlwings module using the import keyword<\/li>\n<li>Pass the excel spreadsheet file path to the Book() function of xlwings module to open an excel file and store it in a variable<\/li>\n<li>Apply sheets attribute on the above workbook object to get all the sheets present in the given excel file and store it in another variable<\/li>\n<li>Print all the available sheets present in the given excel file<\/li>\n<li>Select the first Sheet(Sheet1) of the above workbook object.<\/li>\n<li>Select any particular cell value from the selected sheet of the given Excel File.<\/li>\n<li>The Exit of the Program.<\/li>\n<\/ul>\n<p><strong>Below is the implementation:<\/strong><\/p>\n<div>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\"># Import xlwings module using the import keyword\r\nimport xlwings \r\n\r\n# Pass the excel spreadsheet file path to the Book() function of xlwings \r\n# module to open an excel file and store it in a variable\r\nworkbookObj = xlwings.Book('SampleExcelFile.xlsx')\r\n\r\n# Apply sheets attribute on the above workbook object to get all the \r\n# sheets present in the given excel file and store it in another variable\r\nall_worksheets = workbookObj.sheets\r\n# Print all the available sheets present in the given excel file\r\nprint(\"The all available Sheets present in the given excel file :\\n\", all_worksheets)\r\n\r\n# Select the first Sheet(Sheet1) of the above workbook object.\r\nnewWorksheet  = all_worksheets[0]\r\n\r\n# Select any particular cell value from a selected sheet of the given Excel File \r\nA2cell_value = newWorksheet.range(\"A2\").value\r\nprint(\"A2 Cell value in Sheet1 of the SampleExcelFile:\", A2cell_value)\r\n<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<pre>PS C:\\Users\\cirus\\Desktop\\crud&gt; python C:\\Users\\cirus\\Desktop\\crud\\sample.py\r\nThe all available Sheets present in the given excel file :\r\nSheets([&lt;Sheet [SampleExcelFile (3).xlsx]Sheet1&gt;])\r\nA2 Cell value in Sheet1 of the SampleExcelFile: 2121.0<\/pre>\n<\/div>\n<div>\n<div>\n<div>\n<h3 id=\"Performing_Multiple_Operations\">Method #2: Performing Multiple Operations<\/h3>\n<p><strong>Approach:<\/strong><\/p>\n<ul>\n<li>Import xlwings module using the import keyword<\/li>\n<li>Open some random Excel File using the Book() function of xlwings module and select a specific sheet from it and store it in a variable<\/li>\n<li>Here we select the first Sheet(Sheet1) of the given Excel File<\/li>\n<li>Select a particular cell value from selected sheet of the given Excel File using the value attribute<\/li>\n<li>Here we select the values of A1, and A4 cell values<\/li>\n<li>Select all the rows and print them.<\/li>\n<li>Select all the columns and print them.<\/li>\n<li>Select a 2-Dimensional range of data from the excel sheet and print the result.<\/li>\n<li>The Exit of the Program.<\/li>\n<\/ul>\n<p><strong>Below is the implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\"># Import xlwings module using the import keyword\r\nimport xlwings \r\n\r\n# Open some random Excel File using the Book() function of xlwings module\r\n# and select a specific sheet from the it and store it in a variable\r\n# Here we select the first Sheet(Sheet1) of the given Excel File \r\nnewWorksheet = xlwings.Book(\"SampleExcelFile.xlsx\").sheets['Sheet1']\r\n\r\n# Select a particular cell value from selected sheet of the given Excel File \r\n# using the value attribute\r\n# Here we select the values of A1, A4 cell values\r\nA1cell_value = newWorksheet.range(\"A1\").value\r\nA4cell_value = newWorksheet.range(\"A4\").value\r\nprint(\"A1 cell Value = \", A1cell_value)\r\nprint(\"A4 cell Value = \", A4cell_value)\r\n\r\n# Select all the rows and print them\r\nall_rows = newWorksheet.range(\"3:3\").value\r\nprint(\"The all Rows:\\n\", all_rows)\r\n# Select all the columns and print them\r\nall_cols = newWorksheet.range(\"C:C\").value\r\nprint(\"The all Columns:\\n\", all_cols)\r\n\r\n# Select a 2-Dimensional range of data from the excel sheet and print the result\r\ntwoDim_table = newWorksheet.range(\"A1:C4\").value\r\nprint(\"2-Dimensional range of data from the Excel sheet:\\n\", twoDim_table)\r\n\r\n<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<pre>A1 cell Value = EmployeeId\r\nA4 cell Value = 2123.0<\/pre>\n<\/div>\n<pre>None, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None, None, None, None, None,\r\nNone, None, None, None, None, None, None, None, None,.......<\/pre>\n<\/div>\n<pre>2-Dimensional range of data from the Excel sheet: \r\n[['EmployeeId', ' EmployeeName', ' Salary'], [2121.0, 'Danny', 25000.0], \r\n [2122.0, 'Sindhu', 50000.0], [2123.0, 'Steve', 40000.0]]<\/pre>\n<h3 id=\"Inserting_Data_Into_an_Excel_File\">Method #3: Inserting Data Into an Excel File<\/h3>\n<p><strong>Approach:<\/strong><\/p>\n<ul>\n<li>Import xlwings module using the import keyword<\/li>\n<li>Open some random Excel File using the Book() function of xlwings module and select a specific sheet from the it and store it in a variable<\/li>\n<li>Here we select the first Sheet(Sheet1) of the given Excel File<\/li>\n<li>Insert data into the A1 cell using the value attribute<\/li>\n<li>Insert multiple values into the B1 cell using the value attribute<\/li>\n<li>Insert 2-Dimensional data values into the C1 cell using the value attribute.<\/li>\n<li>The Exit of the Program.<\/li>\n<\/ul>\n<p><strong>Below is the implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\"># Import xlwings module using the import keyword\r\nimport xlwings \r\n\r\n# Open some random Excel File using the Book() function of xlwings module\r\n# and select a specific sheet from the it and store it in a variable\r\n# Here we select the first Sheet(Sheet1) of the given Excel File \r\nnewWorksheet = xlwings.Book(\"SampleExcelFile.xlsx\").sheets['Sheet1']\r\n\r\n# Inserting data into A1 cell using the value attribute\r\nnewWorksheet.range(\"A1\").value = \"Welcome\"\r\n  \r\n# Inserting multiple values into B1 cell using the value attribute\r\nnewWorksheet.range(\"B1\").value = \"to\", \"the\"\r\n  \r\n# Inserting 2-Dimensional data values into C1 cell using the value attribute\r\nnewWorksheet.range(\"C1\").value = [[\"SheetsTips\"],[\"Btechgeeks\"]]\r\n\r\n<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2913 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/xlwings.jpg\" alt=\"xlwings\" width=\"578\" height=\"304\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/xlwings.jpg 578w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/xlwings-300x158.jpg 300w\" sizes=\"(max-width: 578px) 100vw, 578px\" \/><\/p>\n<p>Now that you are provided with all the necessary information on how to use the Xlwings function in Google Spreadsheet. Xlwings allows us to create interactive apps that employ Excel spreadsheets as the GUI (graphical user interface), with Python handling the backend tasks that may be accessed by clicking a button in Excel or using a UDF (user-defined function).<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Use Xlwings To Read Excel: If you want to read or write the Python data using Excel, then using the Xlwings function is the right option for you. This is because most people would like to view or understand the data from Excel rather than open the code background such as Python. Using the Xlwings &#8230; <a title=\"Working with Excel files using Python Xlwings: Learn Excel Python Integration\" class=\"read-more\" href=\"https:\/\/sheetstips.com\/working-with-excel-files-in-python-using-xlwings\/\" aria-label=\"More on Working with Excel files using Python Xlwings: Learn Excel Python Integration\">Read more<\/a><\/p>\n","protected":false},"author":3,"featured_media":3125,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[182],"tags":[],"jetpack_featured_media_url":"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Working-with-Excel-files-using-Python-Xlwings.png","jetpack-related-posts":[{"id":1971,"url":"https:\/\/sheetstips.com\/change-value-in-excel-using-python\/","url_meta":{"origin":2887,"position":0},"title":"Change value in Excel using Python","author":"David","date":"May 12, 2022","format":false,"excerpt":"Here, we will use Python to update a value in an Excel spreadsheet. 1)Opening an Excel File: openpyxl is a Python package that allows you to read and write Excel xlsx\/xlsm\/xltx\/xltm files. It originated from the lack of an existing library that allowed Python to read and write the Office\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Change value in Excel using Python using xlwt","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/01\/Change-value-in-Excel-using-Python-using-xlwt.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2212,"url":"https:\/\/sheetstips.com\/convert-a-tsv-file-to-excel-using-python\/","url_meta":{"origin":2887,"position":1},"title":"Convert a TSV file to Excel using Python","author":"David","date":"May 23, 2022","format":false,"excerpt":"The full form of a TSV file is a tab separated values file. TSV files are widely used with spreadsheet applications for transferring data across databases. A TSV file usually stores a data table by separating columns using tabs. Also, each record that is under the TSV file will have\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Convert a TSV file to Excel using Python","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Convert-a-TSV-file-to-Excel-using-Python.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2199,"url":"https:\/\/sheetstips.com\/convert-csv-to-excel-using-pandas-in-python\/","url_meta":{"origin":2887,"position":2},"title":"How To Convert CSV to Excel in Python Using Pandas? (4 Easy Methods)","author":"David","date":"May 23, 2022","format":false,"excerpt":"If you have a dataset in CSV format and want to perform some mathematical operations, then converting them to an Excel file is extremely important. The conversion of CSV to Excel is made easy when using Python Pandas. Pandas can convert any file format, from CSV to PDF to Excel\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Convert CSV to Excel using Pandas in Python","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Convert-CSV-to-Excel-using-Pandas-in-Python.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2345,"url":"https:\/\/sheetstips.com\/python-split-given-list-and-insert-in-excel-file\/","url_meta":{"origin":2887,"position":3},"title":"Python Split given List and Insert in Excel File &#8211; List to Excel Python Pandas","author":"David","date":"May 18, 2022","format":false,"excerpt":"List To Excel using Python Pandas: If you are searching for a tutorial on how to split a given list in Python and insert it into Excel using Python Pandas, then this page is for you. Using Python Pandas, it is very easy to split the given text into columns\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Split given List and Insert in Excel File","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Split-given-List-and-Insert-in-Excel-File.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2881,"url":"https:\/\/sheetstips.com\/reading-excel-file-using-python\/","url_meta":{"origin":2887,"position":4},"title":"Reading an Excel File and Performing More Operations on it using Python","author":"David","date":"May 31, 2022","format":false,"excerpt":"Reading Excel Files in Python using Pandas: If you are an Excel user who wants to retrieve the data from the spreadsheet in Python, then this page is for you. To read the Excel data in Python, we can use the Pandas read_excel() function. The read_excel() function in Python will\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Reading an Excel File and Performing More Operations on it using Python","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Reading-an-Excel-File-and-Performing-More-Operations-on-it-using-Python.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1956,"url":"https:\/\/sheetstips.com\/python-script-to-automate-refreshing-an-excel-spreadsheet\/","url_meta":{"origin":2887,"position":5},"title":"Python Script to Automate Refreshing an Excel Spreadsheet","author":"David","date":"May 12, 2022","format":false,"excerpt":"Let us see how to automate refreshing an Excel Spreadsheet with Python We'll utilize the pywin32 module to open the Excel application and workbooks. pywin32 : The pywin32 package has been around for a long time. In fact, Mark Hammond and Andy Robinson published a book on this subject in\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Script to Automate Refreshing an Excel Spreadsheet","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/01\/Python-Script-to-Automate-Refreshing-an-Excel-Spreadsheet.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2887"}],"collection":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/comments?post=2887"}],"version-history":[{"count":30,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2887\/revisions"}],"predecessor-version":[{"id":3401,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2887\/revisions\/3401"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media\/3125"}],"wp:attachment":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media?parent=2887"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/categories?post=2887"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/tags?post=2887"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}