<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/remove-password-protection-from-excel-sheets-using-r/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://ryouready.wordpress.com/2018/05/06/remove-password-protection-from-excel-sheets-using-r/"> &quot;R&quot; you ready?</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Most data scientists wished that all data lived neatly managed in some DB. However, in reality, Excel files are ubiquitous and often a common way to disseminate results or data within many companies. Every now and then I found myself in the situation where I wanted to protect Excel sheets against users accidentally changing them. A few months later, however, I found that I sometimes had forgotten the password I used. The <em>“good”</em> thing is that protecting Excel sheets by password is far from safe and access can be recovered quite easily. The following works for <em>.xlsx</em> files as of Excel 2016 and I suppose for 2013 and as well.</p>
<p>Before implementing the steps in R, I will outline how to remove the password protection <em>“by hand”</em>. The R way is simply the automation of these steps. The first thing one needs to understand is that a .xlsx file is just a <a rel="nofollow" href="https://stackoverflow.com/questions/11082278/how-to-properly-assemble-a-valid-xlsx-file-from-its-internal-sub-components" target="_blank">collection of folders and files in a zip container</a>. If you unzip a .xlsx file (e.g. using 7-Zip) you get the following folder structure (sorry, German UI):<span></span></p>
<p><img data-orig-file="https://ryouready.files.wordpress.com/2018/05/1_bis_3_unzip_xlsx.png?w=450&amp;h=183&amp;fit=602%2C183" data-orig-size="1122,401" data-medium-file="https://ryouready.files.wordpress.com/2018/05/1_bis_3_unzip_xlsx.png?w=450&amp;h=183&amp;fit=602%2C183?w=300" data-attachment-id="1189" data-image-meta="{" data-image-description="" src="https://ryouready.files.wordpress.com/2018/05/1_bis_3_unzip_xlsx.png?w=450&h=183&fit=602%2C183" data-permalink="https://ryouready.wordpress.com/2018/05/06/remove-password-protection-from-excel-sheets-using-r/1_bis_3_unzip_xlsx/" data-large-file="https://ryouready.files.wordpress.com/2018/05/1_bis_3_unzip_xlsx.png?w=450&amp;h=183&amp;fit=602%2C183?w=500" alt="" data-image-title="1_bis_3_unzip_xlsx" data-comments-opened="1" class="wp-image-1189" data-recalc-dims="1">In the folder <em>./xl/worksheets</em> we find one XML file for each Excel sheet. The sheet’s password protection is encoded directly in the sheet. While there used to be the plain password text in the XML in former versions, now, we find the hashed password (see part marked in yellow below). In order to get rid of the password protection, we simply can remove the whole <em>sheetProtection</em> node from the XML. We can do that in any text editor and save the file.</p>
<p><img data-orig-file="https://ryouready.files.wordpress.com/2018/05/4_xml_sheet_1.png?w=450&amp;h=239&amp;fit=548%2C239" data-orig-size="1167,516" data-medium-file="https://ryouready.files.wordpress.com/2018/05/4_xml_sheet_1.png?w=450&amp;h=239&amp;fit=548%2C239?w=300" data-attachment-id="1190" data-image-meta="{" data-image-description="" src="https://ryouready.files.wordpress.com/2018/05/4_xml_sheet_1.png?w=450&h=239&fit=548%2C239" data-permalink="https://ryouready.wordpress.com/2018/05/06/remove-password-protection-from-excel-sheets-using-r/4_xml_sheet_1/" data-large-file="https://ryouready.files.wordpress.com/2018/05/4_xml_sheet_1.png?w=450&amp;h=239&amp;fit=548%2C239?w=500" alt="" data-image-title="4_xml_sheet_1" data-comments-opened="1" class="wp-image-1190" data-recalc-dims="1"></p>
<p>As the last step, we need to recreate the <em>.xlsx</em> file by creating a zip folder that contains our modified XML file (German UI again).</p>
<p><img data-orig-file="https://ryouready.files.wordpress.com/2018/05/5_zip_modified_files.png?w=450&amp;h=215&amp;fit=545%2C215" data-orig-size="1097,471" data-medium-file="https://ryouready.files.wordpress.com/2018/05/5_zip_modified_files.png?w=450&amp;h=215&amp;fit=545%2C215?w=300" data-attachment-id="1191" data-image-meta="{" data-image-description="" src="https://ryouready.files.wordpress.com/2018/05/5_zip_modified_files.png?w=450&h=215&fit=545%2C215" data-permalink="https://ryouready.wordpress.com/2018/05/06/remove-password-protection-from-excel-sheets-using-r/5_zip_modified_files/" data-large-file="https://ryouready.files.wordpress.com/2018/05/5_zip_modified_files.png?w=450&amp;h=215&amp;fit=545%2C215?w=500" alt="" data-image-title="5_zip_modified_files" data-comments-opened="1" class="wp-image-1191" data-recalc-dims="1"></p>
<p>Finally, we change the file extension from <em>.zip</em> back to <em>.xlsx</em> and voilá, we get an Excel file without password protected sheets. Programming the steps outlined above in R is quite straightforward. The steps are commented in the <a rel="nofollow" href="https://gist.github.com/markheckmann/03ef7cede3701c3e0cbdba9d3e9af5a1" target="_blank">GitHub gist</a> below.</p>
<p></p>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://ryouready.wordpress.com/2018/05/06/remove-password-protection-from-excel-sheets-using-r/"> &quot;R&quot; you ready?</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/Ma1E2yCvm14" height="1"></a></div>