Python Lab 15: Manage Excel Files Seamlessly with Python. Learn efficient methods for handling Excel files and enhancing data processing workflows.

Python Lab 15: Excel File Management using Python

Lab 15: Excel File Management using Python

<div class="whitepaper-body-page">

<h4 class="wp-block-heading">Opening existing file, adding data, saving Data in  a new Workbook</h4>



<p>To perform operation on an excel file we need to download and install a library called <span class="has-inline-color has-vivid-red-color">openpyxl</span></p>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text"> pip3 install openpyxl</span></div></div></div><div class="enlighter-raw"> pip3 install openpyxl</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="shell" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group=""> pip3 install openpyxl</pre>



<div class="wp-block-pb-accordion-item c-accordion__item js-accordion-item" data-initially-open="false" data-click-to-close="true" data-auto-close="true" data-scroll="false" data-scroll-offset="0"><button id="at-66452" class="c-accordion__title js-accordion-controller c-accordion__title--button" role="button" tabindex="0" aria-controls="ac-66452" aria-expanded="false">Show Output</button><div id="ac-66452" class="c-accordion__content" hidden="hidden">
<pre class="wp-block-code"><code>Collecting openpyxl
  Using cached openpyxl-3.0.4-py2.py3-none-any.whl (241 kB)
Requirement already satisfied: jdcal in c:\users\lenovo\appdata\local\programs\python\python38\lib\site-packages (from openpyxl) (1.4.1)
Requirement already satisfied: et-xmlfile in c:\users\lenovo\appdata\local\programs\python\python38\lib\site-packages (from openpyxl) (1.0.1)
Installing collected packages: openpyxl
Successfully installed openpyxl-3.0.4</code></pre>
</div></div>



<p>To use open an existing excel file and perform excel operations in the file, we need to use load_workbook() function and Workbook() class in our script:</p>



<ul><li><code>from openpyxl import load_workbook(), Workbook</code> This command import only the load_workbook function and Workbook() class from the openpyxl library</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-k0">from</span><span class="enlighter-text"> openpyxl </span><span class="enlighter-k0">import</span><span class="enlighter-text"> load_workbook,Workbook </span></div></div></div><div class="enlighter-raw">from openpyxl import load_workbook,Workbook </div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">from openpyxl import load_workbook,Workbook </pre>



<p>To open the excel file and perform excel operations on the file using python, we need to create an object of the file using load_workbook() function and make the same object an instance of the Workbook() class</p>



<ul><li><code>load_workbook()</code>  function creates an object to read the excel file</li><li><code>Workbook()</code> creates an instance of the Workbook class of the same object to perform excel operations.</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">mywb = </span><span class="enlighter-m0">load_workbook</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&#39;students1.xlsx&#39;</span><span class="enlighter-g1">)</span><span class="enlighter-text"> </span></div></div><div class=""><div><span class="enlighter-text">mywb = </span><span class="enlighter-m0">Workbook</span><span class="enlighter-g1">()</span></div></div></div><div class="enlighter-raw">mywb = load_workbook(&#39;students1.xlsx&#39;) 
mywb = Workbook()</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">mywb = load_workbook(&#39;students1.xlsx&#39;) 
mywb = Workbook()</pre>



<p>To work with sheetnames in the workbook, the following commands are used</p>



<ul><li><code>sheetnames</code>  Attribute returns the existing sheet names in the workbook</li><li><code>create_sheet()</code> Function creates a new sheet in the excel workbook at a specified position</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">mywb.sheetnames</span></div></div><div class=""><div><span class="enlighter-text">mywb.</span><span class="enlighter-m1">create_sheet</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&#39;accounts&#39;</span><span class="enlighter-text">,</span><span class="enlighter-n1">2</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">mywb.sheetnames
mywb.create_sheet(&#39;accounts&#39;,2)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">mywb.sheetnames
mywb.create_sheet(&#39;accounts&#39;,2)</pre>



<figure class="wp-block-image size-large"><img decoding="async" loading="lazy" width="311" height="56" src="https://rstforum.net/wp-content/uploads/2020/07/excelacc.png" alt="" class="wp-image-14954" srcset="https://rstforum.net/wp-content/uploads/2020/07/excelacc.png 311w, https://rstforum.net/wp-content/uploads/2020/07/excelacc-300x54.png 300w" sizes="(max-width: 311px) 100vw, 311px"></figure>



<p>To select a  sheet in the workbook and edit the values in cells of the sheet, the following commands are used:</p>



<ul><li><code>sheet_object = workbook_object[&#39;sheetname&#39;]</code> Creates an object for the sheetname specified using the workbook_object</li><li><code>cell_object = sheet_object[&#39;cell_block&#39;]</code> Creates an object for the cell which needs to be edited specified using the sheet_object</li><li><code>cell_object.value</code> Attribute returns the data stored in the cell</li><li><code>cell_object.value = &quot;data&quot;</code> Appends “data” into the cell_object </li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">mysh = mywb</span><span class="enlighter-g1">[</span><span class="enlighter-s0">&#39;accounts&#39;</span><span class="enlighter-g1">]</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">mycell = mysh</span><span class="enlighter-g1">[</span><span class="enlighter-s0">&#39;B6&#39;</span><span class="enlighter-g1">]</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">mycell.value</span></div></div><div class=""><div><span class="enlighter-text">mycell.value = </span><span class="enlighter-s0">&quot;RST Forum&quot;</span></div></div></div><div class="enlighter-raw">mysh = mywb[&#39;accounts&#39;]
mycell = mysh[&#39;B6&#39;]
mycell.value
mycell.value = &quot;RST Forum&quot;</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">mysh = mywb[&#39;accounts&#39;]
mycell = mysh[&#39;B6&#39;]
mycell.value
mycell.value = &quot;RST Forum&quot;</pre>



<figure class="wp-block-image size-large"><img decoding="async" loading="lazy" width="298" height="176" src="https://rstforum.net/wp-content/uploads/2020/07/excelcell.png" alt="" class="wp-image-14955"></figure>



<p>To select multiple cells and append data in all cells, the following commands are used</p>



<ul><li><code>block_object = sheet_object[block_range]</code> Creates an object for multiple block cells of a sheet specified in the block_range</li><li><code>for variable_row in block_range:</code> Iterates every row using for loop</li><li><code>for variable_cell in variable_row:</code> Iterates every cell in  iterated row</li><li><code>variable_cell.value = &quot;data&quot;</code> Appends data to the selected cell object</li><li><code>workbook_object.save(filename)</code> Saves the edited data in the workbook_object in the specified excel file</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">myblock = mysh</span><span class="enlighter-g1">[</span><span class="enlighter-s0">&#39;F1:H10&#39;</span><span class="enlighter-g1">]</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text"></span><span class="enlighter-k1">for</span><span class="enlighter-text"> eachrow </span><span class="enlighter-k0">in</span><span class="enlighter-text"> myblock:</span></div></div><div class=""><div><span class="enlighter-text">    </span><span class="enlighter-k1">for</span><span class="enlighter-text"> eachcell </span><span class="enlighter-k0">in</span><span class="enlighter-text"> eachrow:</span></div></div><div class=""><div><span class="enlighter-text">        eachcell.value = </span><span class="enlighter-s0">&#39;PyExcel&#39;</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">mywb.</span><span class="enlighter-m1">save</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&#39;newstudents.xlsx&#39;</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">myblock = mysh[&#39;F1:H10&#39;]
for eachrow in myblock:
    for eachcell in eachrow:
        eachcell.value = &#39;PyExcel&#39;
mywb.save(&#39;newstudents.xlsx&#39;)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">myblock = mysh[&#39;F1:H10&#39;]
for eachrow in myblock:
    for eachcell in eachrow:
        eachcell.value = &#39;PyExcel&#39;
mywb.save(&#39;newstudents.xlsx&#39;)</pre>



<figure class="wp-block-image size-large"><img decoding="async" loading="lazy" width="533" height="288" src="https://rstforum.net/wp-content/uploads/2020/07/excelblockcells.png" alt="" class="wp-image-14956" srcset="https://rstforum.net/wp-content/uploads/2020/07/excelblockcells.png 533w, https://rstforum.net/wp-content/uploads/2020/07/excelblockcells-480x259.png 480w" sizes="(min-width: 0px) and (max-width: 480px) 480px, (min-width: 481px) 533px, 100vw"></figure>



<figure class="wp-block-image size-large"><img decoding="async" loading="lazy" width="98" height="118" src="https://rstforum.net/wp-content/uploads/2020/07/excel.png" alt="" class="wp-image-14082"></figure>



<h4 class="wp-block-heading">Read saved data from Excel file using Python</h4>



<p>To read saved data from an excel workbook, we need to load the workbook using an object in python:</p>



<ul><li><code>path_object = path of the excel workbook</code> Give the location of the file</li><li><code>workbook_object = load_workbook(path_object) </code>To use the workbook in python, workbook_object is created</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">path = </span><span class="enlighter-s0">&quot;/home/rst/students1.xlsx&quot;</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">students1 = openpyxl.</span><span class="enlighter-m1">load_workbook</span><span class="enlighter-g1">(</span><span class="enlighter-text">path</span><span class="enlighter-g1">)</span><span class="enlighter-text"> </span></div></div></div><div class="enlighter-raw">path = &quot;/home/rst/students1.xlsx&quot;
students1 = openpyxl.load_workbook(path) </div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">path = &quot;/home/rst/students1.xlsx&quot;
students1 = openpyxl.load_workbook(path) </pre>



<p>To select the active sheet in the workbook, an object is create with the help of  the active attribute of the load_workbook function</p>



<p><code>active_sheet_object = workbook_object.active</code> Attribute loads active sheet in the memory and can be accessed using the active_sheet_object</p>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">active_sheet = students1.active</span></div></div></div><div class="enlighter-raw">active_sheet = students1.active</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">active_sheet = students1.active</pre>



<p>Another way of selecting a cell is to pass the row and column number that can provide the location information of the cell. The first row and column number start from 1 and not 0. Cell object can be created using the following function:</p>



<ul><li><code>cell_object= active_sheet_object.cell(row = integer, column = integer)</code> Function creates a cell object by using it’s row and column number.</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">cell = active_sheet.</span><span class="enlighter-m1">cell</span><span class="enlighter-g1">(</span><span class="enlighter-text">row = </span><span class="enlighter-n1">1</span><span class="enlighter-text">, column = </span><span class="enlighter-n1">1</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">cell = active_sheet.cell(row = 1, column = 1)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">cell = active_sheet.cell(row = 1, column = 1)</pre>



<p>To print the value of the cell</p>



<ul><li><code>cell_object.value</code> Attribute returns the value of the cell</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-m0">print</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&quot;Value of the cell:&quot;</span><span class="enlighter-text">, cell.value</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">print(&quot;Value of the cell:&quot;, cell.value)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">print(&quot;Value of the cell:&quot;, cell.value)</pre>



<div class="wp-block-pb-accordion-item c-accordion__item js-accordion-item" data-initially-open="false" data-click-to-close="true" data-auto-close="true" data-scroll="false" data-scroll-offset="0"><button id="at-6908" class="c-accordion__title js-accordion-controller c-accordion__title--button" role="button" tabindex="0" aria-controls="ac-6908" aria-expanded="false">Show Output</button><div id="ac-6908" class="c-accordion__content" hidden="hidden">
<pre class="wp-block-code"><code>(&#39;Value of the cell:&#39;, u&#39;Roll &#39;)</code></pre>
</div></div>



<p>To print the total number of rows</p>



<ul><li><code>active_sheet_object.max_row</code> Attribute returns total rows in the active sheet</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">max_row = active_sheet.max_row</span></div></div><div class=""><div><span class="enlighter-text"></span><span class="enlighter-m0">print</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&quot;Total number of rows&quot;</span><span class="enlighter-text">, active_sheet.max_row</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">max_row = active_sheet.max_row
print(&quot;Total number of rows&quot;, active_sheet.max_row)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">max_row = active_sheet.max_row
print(&quot;Total number of rows&quot;, active_sheet.max_row)</pre>



<div class="wp-block-pb-accordion-item c-accordion__item js-accordion-item" data-initially-open="false" data-click-to-close="true" data-auto-close="true" data-scroll="false" data-scroll-offset="0"><button id="at-21813" class="c-accordion__title js-accordion-controller c-accordion__title--button" role="button" tabindex="0" aria-controls="ac-21813" aria-expanded="false">Show Output</button><div id="ac-21813" class="c-accordion__content" hidden="hidden">
<pre class="wp-block-code"><code>(&#39;Total number of rows&#39;, 4)</code></pre>
</div></div>



<p>To print the total number of columns</p>



<ul><li><code>active_sheet_object.max_column</code> Attribute returns total columns in the active sheet</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">max_col = active_sheet.max_column</span></div></div><div class=""><div><span class="enlighter-text"></span><span class="enlighter-m0">print</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&quot;Total number of columns&quot;</span><span class="enlighter-text">, active_sheet.max_column</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">max_col = active_sheet.max_column
print(&quot;Total number of columns&quot;, active_sheet.max_column)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">max_col = active_sheet.max_column
print(&quot;Total number of columns&quot;, active_sheet.max_column)</pre>



<div class="wp-block-pb-accordion-item c-accordion__item js-accordion-item" data-initially-open="false" data-click-to-close="true" data-auto-close="true" data-scroll="false" data-scroll-offset="0"><button id="at-51718" class="c-accordion__title js-accordion-controller c-accordion__title--button" role="button" tabindex="0" aria-controls="ac-51718" aria-expanded="false">Show Output</button><div id="ac-51718" class="c-accordion__content" hidden="hidden">
<pre class="wp-block-code"><code>(&#39;Total number of columns&#39;, 5)</code></pre>
</div></div>



<p>To print all column names</p>



<ul><li><code>for <span class="has-inline-color has-vivid-purple-color">variable </span>in range(1, max_col_object + 1):</code> Iterates every column from 1st column till last column</li><li><code>cell_columns_object = active_sheet_object.cell(row = integer, column = <span class="has-inline-color has-vivid-purple-color">variable</span>)</code>Function selects every cell in the iterated column and can be accessed using the cell_columns_object variable</li><li><code>cell_columns_object.value</code> Attribute returns the value of the iterated cell_columns_object</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-k1">for</span><span class="enlighter-text"> i </span><span class="enlighter-k0">in</span><span class="enlighter-text"> </span><span class="enlighter-m0">range</span><span class="enlighter-g1">(</span><span class="enlighter-n1">1</span><span class="enlighter-text">, max_col + </span><span class="enlighter-n1">1</span><span class="enlighter-g1">)</span><span class="enlighter-text">:</span></div></div><div class=""><div><span class="enlighter-text"> cells_column = active_sheet.</span><span class="enlighter-m1">cell</span><span class="enlighter-g1">(</span><span class="enlighter-text">row = </span><span class="enlighter-n1">1</span><span class="enlighter-text">, column = i</span><span class="enlighter-g1">)</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text"> </span><span class="enlighter-m0">print</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&quot;Names of the columns&quot;</span><span class="enlighter-text">, cells_column.value</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">for i in range(1, max_col + 1):
 cells_column = active_sheet.cell(row = 1, column = i)
 print(&quot;Names of the columns&quot;, cells_column.value)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">for i in range(1, max_col + 1):
 cells_column = active_sheet.cell(row = 1, column = i)
 print(&quot;Names of the columns&quot;, cells_column.value)</pre>



<div class="wp-block-pb-accordion-item c-accordion__item js-accordion-item" data-initially-open="false" data-click-to-close="true" data-auto-close="true" data-scroll="false" data-scroll-offset="0"><button id="at-56385" class="c-accordion__title js-accordion-controller c-accordion__title--button" role="button" tabindex="0" aria-controls="ac-56385" aria-expanded="false">Show Output</button><div id="ac-56385" class="c-accordion__content" hidden="hidden">
<pre class="wp-block-code"><code>(&#39;Names of the columns&#39;, u&#39;Roll &#39;)
(&#39;Names of the columns&#39;, u&#39;Name&#39;)
(&#39;Names of the columns&#39;, u&#39;Subject&#39;)
(&#39;Names of the columns&#39;, u&#39;Batch Date&#39;)
(&#39;Names of the columns&#39;, u&#39;Attendance&#39;)</code></pre>
</div></div>



<p>To print all values of first column</p>



<ul><li><code>for <span class="has-inline-color has-vivid-purple-color">variable </span>in range(1, max_row_object + 1):</code> Iterates every row from 1st row till last row</li><li><code>cell_row_object = active_sheet_object.cell(row = <span class="has-inline-color has-vivid-purple-color">variable</span>, column = integer )</code>Function selects every cell in the iterated row and can be accessed using the cell_row_object variable</li><li><code>cell_row_object.value</code> Attribute returns the value of the iterated cell_row_object</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-k1">for</span><span class="enlighter-text"> i </span><span class="enlighter-k0">in</span><span class="enlighter-text"> </span><span class="enlighter-m0">range</span><span class="enlighter-g1">(</span><span class="enlighter-n1">1</span><span class="enlighter-text">, max_row + </span><span class="enlighter-n1">1</span><span class="enlighter-g1">)</span><span class="enlighter-text">:</span></div></div><div class=""><div><span class="enlighter-text"> cells_row = active_sheet.</span><span class="enlighter-m1">cell</span><span class="enlighter-g1">(</span><span class="enlighter-text">row = i, column = </span><span class="enlighter-n1">1</span><span class="enlighter-g1">)</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text"> </span><span class="enlighter-m0">print</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&quot;Values of the 1st column:&quot;</span><span class="enlighter-text">, cells_row.value</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">for i in range(1, max_row + 1):
 cells_row = active_sheet.cell(row = i, column = 1)
 print(&quot;Values of the 1st column:&quot;, cells_row.value)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">for i in range(1, max_row + 1):
 cells_row = active_sheet.cell(row = i, column = 1)
 print(&quot;Values of the 1st column:&quot;, cells_row.value)</pre>



<div class="wp-block-pb-accordion-item c-accordion__item js-accordion-item" data-initially-open="false" data-click-to-close="true" data-auto-close="true" data-scroll="false" data-scroll-offset="0"><button id="at-29950" class="c-accordion__title js-accordion-controller c-accordion__title--button" role="button" tabindex="0" aria-controls="ac-29950" aria-expanded="false">Show Output</button><div id="ac-29950" class="c-accordion__content" hidden="hidden">
<pre class="wp-block-code"><code>(&#39;Values of the 1st column:&#39;, u&#39;Roll &#39;)
(&#39;Values of the 1st column:&#39;, 10)
(&#39;Values of the 1st column:&#39;, 20)
(&#39;Values of the 1st column:&#39;, 30)</code></pre>
</div></div>



<p>To print a particular row value</p>



<p><code>for <span class="has-inline-color has-vivid-purple-color">variable </span>in range(1, max_col_object + 1):</code> Iterates every column from 1st column till last column<br><code>cell_object = active_sheet_object.cell(row = integer, column = <span class="has-inline-color has-vivid-purple-color">variable</span>)</code> Function selects every cell in the iterated column and can be accessed using the cell_object variable<br><code>cell_object.value</code> Attribute returns the value of the iterated cell_columns_object</p>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-k1">for</span><span class="enlighter-text"> i </span><span class="enlighter-k0">in</span><span class="enlighter-text"> </span><span class="enlighter-m0">range</span><span class="enlighter-g1">(</span><span class="enlighter-n1">1</span><span class="enlighter-text">, max_col</span><span class="enlighter-g1">)</span><span class="enlighter-text">:</span></div></div><div class=""><div><span class="enlighter-text"> cell_obj = active_sheet.</span><span class="enlighter-m1">cell</span><span class="enlighter-g1">(</span><span class="enlighter-text">row = </span><span class="enlighter-n1">2</span><span class="enlighter-text">, column = i</span><span class="enlighter-g1">)</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text"> </span><span class="enlighter-m0">print</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&quot;Value of the row 2, column i:&quot;</span><span class="enlighter-text">,cell_obj.value</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">for i in range(1, max_col):
 cell_obj = active_sheet.cell(row = 2, column = i)
 print(&quot;Value of the row 2, column i:&quot;,cell_obj.value)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">for i in range(1, max_col):
 cell_obj = active_sheet.cell(row = 2, column = i)
 print(&quot;Value of the row 2, column i:&quot;,cell_obj.value)</pre>



<div class="wp-block-pb-accordion-item c-accordion__item js-accordion-item" data-initially-open="false" data-click-to-close="true" data-auto-close="true" data-scroll="false" data-scroll-offset="0"><button id="at-30579" class="c-accordion__title js-accordion-controller c-accordion__title--button" role="button" tabindex="0" aria-controls="ac-30579" aria-expanded="false">Show Output</button><div id="ac-30579" class="c-accordion__content" hidden="hidden">
<pre class="wp-block-code"><code>(&#39;Value of the row 2, column i:&#39;, 10)
(&#39;Value of the row 2, column i:&#39;, u&#39;John Doe&#39;)
(&#39;Value of the row 2, column i:&#39;, u&#39;Python&#39;)
(&#39;Value of the row 2, column i:&#39;, u&#39;1st July&#39;)
(&#39;Value of the row 2, column i:&#39;, u&#39;Present&#39;)</code></pre>
</div></div>



<h4 class="wp-block-heading">Create and save data in a new Workbook</h4>



<p>To create an excel workbook using python, we need to create an object using of the <code>Workbook()</code> class of the openpyxl library:</p>



<ul><li><code> from openpyxl import Workbook</code> Imports<span class="has-inline-color has-vivid-red-color"> Workbook()</span> class from <span class="has-inline-color has-vivid-red-color">openpyxl</span> library</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-k0">from</span><span class="enlighter-text"> openpyxl </span><span class="enlighter-k0">import</span><span class="enlighter-text"> Workbook</span></div></div></div><div class="enlighter-raw">from openpyxl import Workbook</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">from openpyxl import Workbook</pre>



<p>To create a new object of the Workbook() class, following command is used</p>



<p><code>new_object = Classname()</code> Creates an instance of the class.</p>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text"> new_mywb = </span><span class="enlighter-m0">Workbook</span><span class="enlighter-g1">()</span></div></div></div><div class="enlighter-raw"> new_mywb = Workbook()</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group=""> new_mywb = Workbook()</pre>



<p>To select and change the title of the active sheet, the following command is used</p>



<ul><li><code>active_sheet_object = new_object.active</code> Attribute selects the active sheet and can be accessed using the active_sheet_object variable</li><li><code>title_object = active_sheet_object.title</code> Attribute selects the title of the active sheet and can be accesses using the title_object variable</li><li><code>print(&quot;active sheet title:&quot; + title_object)</code> Function prints the title of the active sheet</li><li><code>new_title = &quot;Python&quot;</code> New title of string data-type </li><li><code>active_sheet_object.title = new_title</code> Assigns the title attribute the value of new_title variable</li><li><code>print(&quot;sheet name is renamed as: &quot; + title_object)</code> Function prints the updated title of the active sheet</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">my_sheet = new_wb.active </span></div></div><div class=""><div><span class="enlighter-text">title = my_sheet.title </span></div></div><div class=""><div><span class="enlighter-text"></span><span class="enlighter-m0">print</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&quot;active sheet title: &quot;</span><span class="enlighter-text"> + title</span><span class="enlighter-g1">)</span><span class="enlighter-text"> </span></div></div><div class=""><div><span class="enlighter-text">title = </span><span class="enlighter-s0">&quot;python&quot;</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">my_sheet.title = title  </span></div></div><div class=""><div><span class="enlighter-text"></span><span class="enlighter-m0">print</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&quot;sheet name is renamed as: &quot;</span><span class="enlighter-text"> + title</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">my_sheet = new_wb.active 
title = my_sheet.title 
print(&quot;active sheet title: &quot; + title) 
title = &quot;python&quot;
my_sheet.title = title<br>print(&quot;sheet name is renamed as: &quot; + title)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">my_sheet = new_wb.active 
title = my_sheet.title 
print(&quot;active sheet title: &quot; + title) 
title = &quot;python&quot;
my_sheet.title = title<br>print(&quot;sheet name is renamed as: &quot; + title)</pre>



<div class="wp-block-pb-accordion-item c-accordion__item js-accordion-item" data-initially-open="false" data-click-to-close="true" data-auto-close="true" data-scroll="false" data-scroll-offset="0"><button id="at-67942" class="c-accordion__title js-accordion-controller c-accordion__title--button" role="button" tabindex="0" aria-controls="ac-67942" aria-expanded="false">Show Output</button><div id="ac-67942" class="c-accordion__content" hidden="hidden">
<pre class="wp-block-code"><code>active sheet title: Sheet
sheet name is renamed as: python
</code></pre>
</div></div>



<p>To insert values in cells of the active sheet, the following commands are used</p>



<ul><li>Cell objects also have row, column and coordinate attributes that provide location information for the cell.</li><li>Note: The first row or column integer is 1, not 0. Cell object is created by using sheet object’s cell() method.</li><li><code>cell_object = active_sheet_object.cell(row = integer, column =integer)</code></li><li><code>cell_object &quot;RST&quot;</code> Writes values to cells.</li><li><code>cell_object = active_sheet_object[&#39;A2&#39;]</code> Once have a Worksheet object, one can access a cell object by its name also. A2 means column = 1 &amp; row = 2.</li><li><code>cell_object = active_sheet_object [&#39;B2&#39;]</code> B2 means column = 2 &amp; row = 2.</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">c1 = my_sheet.</span><span class="enlighter-m1">cell</span><span class="enlighter-g1">(</span><span class="enlighter-text">row = </span><span class="enlighter-n1">1</span><span class="enlighter-text">, column = </span><span class="enlighter-n1">1</span><span class="enlighter-g1">)</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">c1.value = </span><span class="enlighter-s0">&quot;RST&quot;</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">  </span></div></div><div class=""><div><span class="enlighter-text">c2 = my_sheet.</span><span class="enlighter-m1">cell</span><span class="enlighter-g1">(</span><span class="enlighter-text">row= </span><span class="enlighter-n1">1</span><span class="enlighter-text"> , column = </span><span class="enlighter-n1">2</span><span class="enlighter-g1">)</span><span class="enlighter-text"> </span></div></div><div class=""><div><span class="enlighter-text">c2.value = </span><span class="enlighter-s0">&quot;Forum&quot;</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">  </span></div></div><div class=""><div><span class="enlighter-text">c3 = my_sheet</span><span class="enlighter-g1">[</span><span class="enlighter-s0">&#39;A2&#39;</span><span class="enlighter-g1">]</span><span class="enlighter-text"> </span></div></div><div class=""><div><span class="enlighter-text">c3.value = </span><span class="enlighter-s0">&quot;Cisco&quot;</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">  </span></div></div><div class=""><div><span class="enlighter-text">c4 = my_sheet</span><span class="enlighter-g1">[</span><span class="enlighter-s0">&#39;B2&#39;</span><span class="enlighter-g1">]</span><span class="enlighter-text"> </span></div></div><div class=""><div><span class="enlighter-text">c4.value = </span><span class="enlighter-s0">&quot;Red Hat&quot;</span></div></div></div><div class="enlighter-raw">c1 = my_sheet.cell(row = 1, column = 1)
c1.value = &quot;RST&quot;

c2 = my_sheet.cell(row= 1 , column = 2) 
c2.value = &quot;Forum&quot;

c3 = my_sheet[&#39;A2&#39;] 
c3.value = &quot;Cisco&quot;

c4 = my_sheet[&#39;B2&#39;] 
c4.value = &quot;Red Hat&quot;</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">c1 = my_sheet.cell(row = 1, column = 1)
c1.value = &quot;RST&quot;

c2 = my_sheet.cell(row= 1 , column = 2) 
c2.value = &quot;Forum&quot;

c3 = my_sheet[&#39;A2&#39;] 
c3.value = &quot;Cisco&quot;

c4 = my_sheet[&#39;B2&#39;] 
c4.value = &quot;Red Hat&quot;
</pre>



<p>To save the new workbook object data in file, the following commands are used <code>new_workbook_object.save(&#39;filename&#39;)</code> Saves and writes the object to the specified file name.</p>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">newwb.</span><span class="enlighter-m1">save</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&quot;prac.xlsx&quot;</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">newwb.save(&quot;prac.xlsx&quot;)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">newwb.save(&quot;prac.xlsx&quot;)</pre>



<figure class="wp-block-image size-large"><img decoding="async" loading="lazy" width="1024" height="252" src="https://rstforum.net/wp-content/uploads/2020/07/excel2-1024x252.png" alt="" class="wp-image-14267" srcset="https://rstforum.net/wp-content/uploads/2020/07/excel2-1024x252.png 1024w, https://rstforum.net/wp-content/uploads/2020/07/excel2-980x241.png 980w, https://rstforum.net/wp-content/uploads/2020/07/excel2-480x118.png 480w" sizes="(min-width: 0px) and (max-width: 480px) 480px, (min-width: 481px) and (max-width: 980px) 980px, (min-width: 981px) 1024px, 100vw"></figure>



<h4 class="wp-block-heading">Python Script to change cell dimensions in Excel Workbook</h4>



<p>To change the cell dimensions in an excel sheet, the following commands are used:</p>



<ul><li><code>active_sheet_object.row_dimensions[Row Number].height = value</code> Changes the height of the specified row with the value</li><li><code>active_sheet_object.column_dimensions[Column Name].width = value</code> Changes the width of the column with the value</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">sheet.row_dimensions</span><span class="enlighter-g1">[</span><span class="enlighter-n1">1</span><span class="enlighter-g1">]</span><span class="enlighter-text">.height = </span><span class="enlighter-n1">70</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">sheet.column_dimensions</span><span class="enlighter-g1">[</span><span class="enlighter-s0">&#39;B&#39;</span><span class="enlighter-g1">]</span><span class="enlighter-text">.width = </span><span class="enlighter-n1">20</span></div></div></div><div class="enlighter-raw">sheet.row_dimensions[1].height = 70
sheet.column_dimensions[&#39;B&#39;].width = 20</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">sheet.row_dimensions[1].height = 70
sheet.column_dimensions[&#39;B&#39;].width = 20
</pre>



<figure class="wp-block-image size-large"><img decoding="async" loading="lazy" width="909" height="341" src="https://rstforum.net/wp-content/uploads/2020/07/excel4.png" alt="" class="wp-image-14270" srcset="https://rstforum.net/wp-content/uploads/2020/07/excel4.png 909w, https://rstforum.net/wp-content/uploads/2020/07/excel4-480x180.png 480w" sizes="(min-width: 0px) and (max-width: 480px) 480px, (min-width: 481px) 909px, 100vw"></figure>



<h4 class="wp-block-heading"> Python Script to merge cells in Excel Workbook</h4>



<p>To merge and unmerge the cells in an excel sheet, the following commands are used</p>



<ul><li> merge cell from A2 to D4 i.e. A2, B2, C2, D2, A3, B3, C3, D3, A4, B4, C4 and D4 . </li><li>merge cell from C6 to D6</li><li><code>active_sheet_object.merge_cells(&#39;Range of cells to merge&#39;)</code>  A2:D4′ merges 12 cells into a single cell.</li><li><code>active_sheet_object.cell(row = integer, column = integer).value = &#39;data&#39;</code>Write data into merged cells</li><li><code>workbook_object.save(&#39;filename&#39;)</code> Save and write the edited object to an excel file.</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">sheet.</span><span class="enlighter-m1">merge_cells</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&#39;A2:D4&#39;</span><span class="enlighter-g1">)</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">sheet.</span><span class="enlighter-m1">cell</span><span class="enlighter-g1">(</span><span class="enlighter-text">row = </span><span class="enlighter-n1">2</span><span class="enlighter-text">, column = </span><span class="enlighter-n1">1</span><span class="enlighter-g1">)</span><span class="enlighter-text">.value = </span><span class="enlighter-s0">&#39;Twelve cells join together.&#39;</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">sheet.</span><span class="enlighter-m1">merge_cells</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&#39;C6:D6&#39;</span><span class="enlighter-g1">)</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">sheet.</span><span class="enlighter-m1">cell</span><span class="enlighter-g1">(</span><span class="enlighter-text">row = </span><span class="enlighter-n1">6</span><span class="enlighter-text">, column = </span><span class="enlighter-n1">3</span><span class="enlighter-g1">)</span><span class="enlighter-text">.value = </span><span class="enlighter-s0">&#39;Two merge cells.&#39;</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text"> wb.</span><span class="enlighter-m1">save</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&#39;mergeXL.xlsx&#39;</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">sheet.merge_cells(&#39;A2:D4&#39;)
sheet.cell(row = 2, column = 1).value = &#39;Twelve cells join together.&#39;
sheet.merge_cells(&#39;C6:D6&#39;)
sheet.cell(row = 6, column = 3).value = &#39;Two merge cells.&#39;
 wb.save(&#39;mergeXL.xlsx&#39;)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">sheet.merge_cells(&#39;A2:D4&#39;)
sheet.cell(row = 2, column = 1).value = &#39;Twelve cells join together.&#39;
sheet.merge_cells(&#39;C6:D6&#39;)
sheet.cell(row = 6, column = 3).value = &#39;Two merge cells.&#39;
 wb.save(&#39;mergeXL.xlsx&#39;)


</pre>



<figure class="wp-block-image size-large"><img decoding="async" loading="lazy" width="890" height="348" src="https://rstforum.net/wp-content/uploads/2020/07/excel7.png" alt="" class="wp-image-14272" srcset="https://rstforum.net/wp-content/uploads/2020/07/excel7.png 890w, https://rstforum.net/wp-content/uploads/2020/07/excel7-480x188.png 480w" sizes="(min-width: 0px) and (max-width: 480px) 480px, (min-width: 481px) 890px, 100vw"></figure>



<h4 class="wp-block-heading">Python Script to unmerge cells in Excel Workbook</h4>



<p>To  unmerge the cells in an excel sheet, the following commands are used</p>



<ul><li>unmerge cells from A2 to D4 i.e. A2, B2, C2, D2, A3, B3, C3, D3, A4, B4, C4 and D4 . </li><li>unmerge cells from C6 to D6</li><li><code>active_sheet_object.unmerge_cells(&#39;Range of cells to unmerge&#39;)</code>  A2:D4′ merges 12 cells into a single cell.</li><li><code>active_sheet_object.cell(row = integer, column = integer).value = &#39;data&#39;</code>Write data into merged cells</li><li><code>workbook_object.save(&#39;filename&#39;)</code> Save and write the edited object to an excel file.</li></ul>



<div class="enlighter-default enlighter-v-standard enlighter-t-minimal enlighter-hover enlighter-linenumbers "><div class="enlighter-toolbar-top enlighter-toolbar"><div class="enlighter-btn enlighter-btn-raw" title="Plain text"></div><div class="enlighter-btn enlighter-btn-copy" title="Copy to clipboard"></div><div class="enlighter-btn enlighter-btn-window" title="Open code in new window"></div><div class="enlighter-btn enlighter-btn-website" title="EnlighterJS 3 Syntax Highlighter"></div></div><div class="enlighter" style=""><div class=""><div><span class="enlighter-text">sheet.</span><span class="enlighter-m1">unmerge_cells</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&#39;A2:D4&#39;</span><span class="enlighter-g1">)</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">sheet.</span><span class="enlighter-m1">unmerge_cells</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&#39;C6:D6&#39;</span><span class="enlighter-g1">)</span><span class="enlighter-text"></span></div></div><div class=""><div><span class="enlighter-text">wb.</span><span class="enlighter-m1">save</span><span class="enlighter-g1">(</span><span class="enlighter-s0">&#39;unmergeXL.xlsx&#39;</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">sheet.unmerge_cells(&#39;A2:D4&#39;)
sheet.unmerge_cells(&#39;C6:D6&#39;)
wb.save(&#39;unmergeXL.xlsx&#39;)</div><div class="enlighter-toolbar-bottom enlighter-toolbar"></div></div><pre class="EnlighterJSRAW enlighter-origin" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">sheet.unmerge_cells(&#39;A2:D4&#39;)
sheet.unmerge_cells(&#39;C6:D6&#39;)
wb.save(&#39;unmergeXL.xlsx&#39;)</pre>



<figure class="wp-block-image size-large"><img decoding="async" loading="lazy" width="857" height="345" src="https://rstforum.net/wp-content/uploads/2020/07/excel6.png" alt="" class="wp-image-14271" srcset="https://rstforum.net/wp-content/uploads/2020/07/excel6.png 857w, https://rstforum.net/wp-content/uploads/2020/07/excel6-480x193.png 480w" sizes="(min-width: 0px) and (max-width: 480px) 480px, (min-width: 481px) 857px, 100vw"></figure>

</div>