python读取xlsx文件
<p>python操作xlsx格式文件</p><p>一准备工作：</p>
<p><img src="https://img2020.cnblogs.com/blog/1510016/202006/1510016-20200606102405007-927999575.png" alt=""></p>
<p><img src="https://img2020.cnblogs.com/blog/1510016/202006/1510016-20200606102811170-375389605.png" alt="" width="1049" height="80"></p>
<p> </p>
<p> </p>
<p>二 xlrd库读取</p>
<p>首先安装xlrd库，安装方法：pip install xlrd</p>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 0, 255, 1)">import</span><span style="color: rgba(0, 0, 0, 1)"> xlrd
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">打开excel</span>
wb = xlrd.open_workbook(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">test_user_data.xlsx</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">按工作簿定位工作表</span>
sh = wb.sheet_by_name(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">TestUserLogin</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(sh.nrows)<span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">有效数据行数</span>
<span style="color: rgba(0, 0, 255, 1)">print</span>(sh.ncols)<span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">有效数据列数</span>
<span style="color: rgba(0, 0, 255, 1)">print</span>(sh.cell(0,0).value)<span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">输出第一行第一列的值</span>
<span style="color: rgba(0, 0, 255, 1)">print</span>(sh.row_values(0))<span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">输出第一行的所有值</span><span style="color: rgba(0, 128, 0, 1)">
#</span><span style="color: rgba(0, 128, 0, 1)">将数据和标题组合成字典</span>
<span style="color: rgba(0, 0, 255, 1)">print</span>(dict(zip(sh.row_values(0),sh.row_values(1<span style="color: rgba(0, 0, 0, 1)">))))
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">遍历excel，打印所有数据</span>
<span style="color: rgba(0, 0, 255, 1)">for</span> i <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> range(sh.nrows):
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(sh.row_values(i))</pre>
</div>
<p>输出结果：</p>
<p><img src="https://img2020.cnblogs.com/blog/1510016/202006/1510016-20200606103017312-1175085135.png" alt=""></p>
<p> </p>
<p> 三：pandas库读取</p>
<p>1.安装pandas： pip install pandas</p>
<p>2.代码如下</p>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 0, 255, 1)">import</span><span style="color: rgba(0, 0, 0, 1)"> pandas as pd
df </span>= pd.read_excel(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">test_user_data.xlsx</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
data</span>=<span style="color: rgba(0, 0, 0, 1)">df.values
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">获取到所有的值:\n{}</span><span style="color: rgba(128, 0, 0, 1)">"</span>.format(data))</pre>
</div>
<p>结果如下：</p>
<p><img src="https://img2020.cnblogs.com/blog/1510016/202006/1510016-20200606132451307-416638564.png" alt=""></p>
<p> 3操作行列</p>
<p>读取列数</p>
<p> </p>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 0, 255, 1)">import</span><span style="color: rgba(0, 0, 0, 1)"> pandas as pd
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">读取第一列、第二列、第四列</span>
df = pd.read_excel(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">test_user_data.xlsx</span><span style="color: rgba(128, 0, 0, 1)">'</span>,sheet_name=<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">TestUserLogin</span><span style="color: rgba(128, 0, 0, 1)">'</span>,usecols=<span style="color: rgba(0, 0, 0, 1)">)
data </span>=<span style="color: rgba(0, 0, 0, 1)"> df.values
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(data)</pre>
</div>
<p>读取行数</p>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 0, 255, 1)">import</span><span style="color: rgba(0, 0, 0, 1)"> pandas as pd
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">读取第一行</span>
df = pd.read_excel(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">test_user_data.xlsx</span><span style="color: rgba(128, 0, 0, 1)">'</span>,sheet_name=<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">TestUserLogin</span><span style="color: rgba(128, 0, 0, 1)">'</span>,nrows=1<span style="color: rgba(0, 0, 0, 1)">)
data </span>=<span style="color: rgba(0, 0, 0, 1)"> df.values
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(data)</pre>
</div>
<p> </p>
</div>
<div id="MySignature" role="contentinfo">
<p>本文来自博客园，作者：Harry_666，转载请注明原文链接：https://www.cnblogs.com/harry66/p/13054389.html</p><br><br>
来源：https://www.cnblogs.com/harry66/p/13054389.html
頁:
[1]