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">'students1.xlsx'</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('students1.xlsx')
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('students1.xlsx')
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">'accounts'</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('accounts',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('accounts',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['sheetname']</code> Creates an object for the sheetname specified using the workbook_object</li><li><code>cell_object = sheet_object['cell_block']</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 = "data"</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">'accounts'</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">'B6'</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">"RST Forum"</span></div></div></div><div class="enlighter-raw">mysh = mywb['accounts']
mycell = mysh['B6']
mycell.value
mycell.value = "RST Forum"</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['accounts']
mycell = mysh['B6']
mycell.value
mycell.value = "RST Forum"</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 = "data"</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">'F1:H10'</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">'PyExcel'</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">'newstudents.xlsx'</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">myblock = mysh['F1:H10']
for eachrow in myblock:
for eachcell in eachrow:
eachcell.value = 'PyExcel'
mywb.save('newstudents.xlsx')</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['F1:H10']
for eachrow in myblock:
for eachcell in eachrow:
eachcell.value = 'PyExcel'
mywb.save('newstudents.xlsx')</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">"/home/rst/students1.xlsx"</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 = "/home/rst/students1.xlsx"
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 = "/home/rst/students1.xlsx"
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">"Value of the cell:"</span><span class="enlighter-text">, cell.value</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">print("Value of the cell:", 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("Value of the cell:", 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>('Value of the cell:', u'Roll ')</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">"Total number of rows"</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("Total number of rows", 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("Total number of rows", 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>('Total number of rows', 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">"Total number of columns"</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("Total number of columns", 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("Total number of columns", 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>('Total number of columns', 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">"Names of the columns"</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("Names of the columns", 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("Names of the columns", 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>('Names of the columns', u'Roll ')
('Names of the columns', u'Name')
('Names of the columns', u'Subject')
('Names of the columns', u'Batch Date')
('Names of the columns', u'Attendance')</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">"Values of the 1st column:"</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("Values of the 1st column:", 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("Values of the 1st column:", 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>('Values of the 1st column:', u'Roll ')
('Values of the 1st column:', 10)
('Values of the 1st column:', 20)
('Values of the 1st column:', 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">"Value of the row 2, column i:"</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("Value of the row 2, column i:",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("Value of the row 2, column i:",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>('Value of the row 2, column i:', 10)
('Value of the row 2, column i:', u'John Doe')
('Value of the row 2, column i:', u'Python')
('Value of the row 2, column i:', u'1st July')
('Value of the row 2, column i:', u'Present')</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("active sheet title:" + title_object)</code> Function prints the title of the active sheet</li><li><code>new_title = "Python"</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("sheet name is renamed as: " + 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">"active sheet title: "</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">"python"</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">"sheet name is renamed as: "</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("active sheet title: " + title)
title = "python"
my_sheet.title = title<br>print("sheet name is renamed as: " + 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("active sheet title: " + title)
title = "python"
my_sheet.title = title<br>print("sheet name is renamed as: " + 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 "RST"</code> Writes values to cells.</li><li><code>cell_object = active_sheet_object['A2']</code> Once have a Worksheet object, one can access a cell object by its name also. A2 means column = 1 & row = 2.</li><li><code>cell_object = active_sheet_object ['B2']</code> B2 means column = 2 & 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">"RST"</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">"Forum"</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">'A2'</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">"Cisco"</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">'B2'</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">"Red Hat"</span></div></div></div><div class="enlighter-raw">c1 = my_sheet.cell(row = 1, column = 1)
c1.value = "RST"
c2 = my_sheet.cell(row= 1 , column = 2)
c2.value = "Forum"
c3 = my_sheet['A2']
c3.value = "Cisco"
c4 = my_sheet['B2']
c4.value = "Red Hat"</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 = "RST"
c2 = my_sheet.cell(row= 1 , column = 2)
c2.value = "Forum"
c3 = my_sheet['A2']
c3.value = "Cisco"
c4 = my_sheet['B2']
c4.value = "Red Hat"
</pre>
<p>To save the new workbook object data in file, the following commands are used <code>new_workbook_object.save('filename')</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">"prac.xlsx"</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">newwb.save("prac.xlsx")</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("prac.xlsx")</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">'B'</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['B'].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['B'].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('Range of cells to merge')</code> A2:D4′ merges 12 cells into a single cell.</li><li><code>active_sheet_object.cell(row = integer, column = integer).value = 'data'</code>Write data into merged cells</li><li><code>workbook_object.save('filename')</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">'A2:D4'</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">'Twelve cells join together.'</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">'C6:D6'</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">'Two merge cells.'</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">'mergeXL.xlsx'</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">sheet.merge_cells('A2:D4')
sheet.cell(row = 2, column = 1).value = 'Twelve cells join together.'
sheet.merge_cells('C6:D6')
sheet.cell(row = 6, column = 3).value = 'Two merge cells.'
wb.save('mergeXL.xlsx')</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('A2:D4')
sheet.cell(row = 2, column = 1).value = 'Twelve cells join together.'
sheet.merge_cells('C6:D6')
sheet.cell(row = 6, column = 3).value = 'Two merge cells.'
wb.save('mergeXL.xlsx')
</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('Range of cells to unmerge')</code> A2:D4′ merges 12 cells into a single cell.</li><li><code>active_sheet_object.cell(row = integer, column = integer).value = 'data'</code>Write data into merged cells</li><li><code>workbook_object.save('filename')</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">'A2:D4'</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">'C6:D6'</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">'unmergeXL.xlsx'</span><span class="enlighter-g1">)</span></div></div></div><div class="enlighter-raw">sheet.unmerge_cells('A2:D4')
sheet.unmerge_cells('C6:D6')
wb.save('unmergeXL.xlsx')</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('A2:D4')
sheet.unmerge_cells('C6:D6')
wb.save('unmergeXL.xlsx')</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>