python实现对Excle表格的读写

<span>#</span><span> 将<a href="https://www.gaodaima.com/tag/excel" title="查看更多关于excel的文章" target="_blank">excel</a>表格内容导入到<a href="https://www.gaodaima.com/tag/tab" title="查看更多关于tab的文章" target="_blank">tab</a>les列表中</span>
<span>def</span><span> import_excel(tab):
    </span><span>#</span><span> 创建一个空列表，存储Excel的数据</span>
    tables =<span> []
    </span><span>for</span> rown <span>in</span> range(1<span>, tab.nrows):
        <a href="https://www.gaodaima.com/tag/array" title="查看更多关于array的文章" target="_blank">array</a> </span>= {<span>"</span><span>设备名称</span><span>"</span>: <span>""</span>, <span>"</span><span>框</span><span>"</span>: <span>""</span>, <span>"</span><span>槽</span><span>"</span>: <span>""</span>, <span>"</span><span>端口</span><span>"</span>: <span>""</span>, <span>"</span><span>onuid</span><span>"</span>: <span>""</span>, <span>"</span><span>认证密码</span><span>"</span>: <span>""</span>, <span>"</span><span>load</span><span>"</span>: <span>""</span>, <span>"</span><span>checkcode</span><span>"</span>: <span>""</span><span>}
        array[</span><span>"</span><span>设备名称</span><span>"</span>] =<span> tab.cell_value(rown, 0)
        array[</span><span>"</span><span>框</span><span>"</span>] = tab.cell_value(rown, 1<span>)
        array[</span><span>"</span><span>槽</span><span>"</span>] = tab.cell_value(rown, 2<span>)
        array[</span><span>"</span><span>端口</span><span>"</span>] = tab.cell_value(rown, 3<span>)
        array[</span><span>"</span><span>onuid</span><span>"</span>] = tab.cell_value(rown, 4<span>)
        array[</span><span>"</span><span>认证密码</span><span>"</span>] = tab.cell_value(rown, 9<span>)
        array[</span><span>"</span><span>load</span><span>"</span>] = tab.cell_value(rown, 10<span>)
        array[</span><span>"</span><span>checkcode</span><span>"</span>] = tab.cell_value(rown, 11<span>)
        tables.append(array)
    </span><span>return</span><span> tables
</span><span>#</span><span> 导入需要读取Excel表格的路径</span>
data = xlrd.open_workbook(r<span>"</span><span>G:	est.xlsx</span><span>"</span><span>)
table </span>=<span> data.sheets()[0]
</span><span>for</span> i <span>in</span><span> import_excel(table):
     </span><span>print</span>(i)

www#gaodaima.com来源gao($daima.com搞@代@#码(网搞代码

<span>import</span><span> pandas as pd
</span><span>#</span><span> 要事先下载好xlwt和openpyxl模块</span>
<span>def</span><span> export_excel(tab):
    </span><span>#</span><span> 将字典列表转换为DataFrame</span>
    pf =<span> pd.DataFrame(list(tab))
    </span><span>#</span><span> 指定字段顺序</span>
    order = [<span>"</span><span>设备名称</span><span>"</span>, <span>"</span><span>框</span><span>"</span>, <span>"</span><span>槽</span><span>"</span>, <span>"</span><span>端口</span><span>"</span>, <span>"</span><span>onuid</span><span>"</span>, <span>"</span><span>认证密码</span><span>"</span>, <span>"</span><span>load</span><span>"</span>, <span>"</span><span>checkcode</span><span>"</span><span>]
    pf </span>=<span> pf[order]
    </span><span>#</span><span> 将列名替换为中文</span>
    columns_map =<span> {
        </span><span>"</span><span>设备名称</span><span>"</span>: <span>"</span><span>设备名称</span><span>"</span><span>,
        </span><span>"</span><span>框</span><span>"</span>: <span>"</span><span>框</span><span>"</span><span>,
        </span><span>"</span><span>槽</span><span>"</span>: <span>"</span><span>槽</span><span>"</span><span>,
        </span><span>"</span><span>端口</span><span>"</span>: <span>"</span><span>端口</span><span>"</span><span>,
        </span><span>"</span><span>onuid</span><span>"</span>: <span>"</span><span>onuid</span><span>"</span><span>,
        </span><span>"</span><span>认证密码</span><span>"</span>: <span>"</span><span>认证密码</span><span>"</span><span>,
        </span><span>"</span><span>load</span><span>"</span>: <span>"</span><span>load</span><span>"</span><span>,
        </span><span>"</span><span>checkcode</span><span>"</span>: <span>"</span><span>checkcode</span><span>"</span><span>
    }
    pf.rename(columns</span>=columns_map, inplace=<span>True)
    </span><span>#</span><span> 指定生成的Excel表格路径</span>
    file_path = pd.ExcelWriter(<span>"</span><span>G:	est1.xlsx</span><span>"</span><span>)
    </span><span>#</span><span> 替换空单元格</span>
    pf.fillna(<span>"</span> <span>"</span>, inplace=<span>True)
    </span><span>#</span><span> 输出</span>
    pf.to_excel(file_path, encoding=<span>"</span><span>utf-8</span><span>"</span>, index=<span>False)
    </span><span>#</span><span> 保存表格</span>
<span>    file_path.save()<br>
export_excel(tables)</span>

<span>def</span> export_excel(data, fileName):  <span>#</span><span> xlsxwriter库储存数据到excel</span>
    workbook = xw.Workbook(fileName)  <span>#</span><span> 创建工作簿</span>
    worksheet1 = workbook.add_worksheet(<span>"</span><span>sheet1</span><span>"</span>)  <span>#</span><span> 创建子表</span>
    worksheet1.activate()  <span>#</span><span> 激活表</span>
    title = [<span>"</span><span>设备名称</span><span>"</span>, <span>"</span><span>框</span><span>"</span>, <span>"</span><span>槽</span><span>"</span>, <span>"</span><span>端口</span><span>"</span>, <span>"</span><span>onuid</span><span>"</span>, <span>"</span><span>认证密码</span><span>"</span>, <span>"</span><span>load</span><span>"</span>, <span>"</span><span>checkcode</span><span>"</span>]  <span>#</span><span> 设置表头</span>
    worksheet1.write_row(<span>"</span><span>A1</span><span>"</span>, title)  <span>#</span><span> 从A1单元格开始写入表头</span>
    i = 2  <span>#</span><span> 从第二行开始写入数据</span>
    <span>for</span> j <span>in</span><span> range(len(data)):
        insertData </span>= [data[j][<span>"</span><span>设备名称</span><span>"</span>], data[j][<span>"</span><span>框</span><span>"</span>], data[j][<span>"</span><span>槽</span><span>"</span>], data[j][<span>"</span><span>端口</span><span>"</span>], data[j][<span>"</span><span>onuid</span><span>"</span>], data[j][<span>"</span><span>认证密码</span><span>"</span><span>],
                      data[j][</span><span>"</span><span>load</span><span>"</span>], data[j][<span>"</span><span>checkcode</span><span>"</span><span>]]
        row </span>= <span>"</span><span>A</span><span>"</span> +<span> str(i)
        worksheet1.write_row(row, insertData)
        i </span>+= 1<span>
    workbook.close()  </span><span>#</span><span> 关闭表</span>
<span>
 export_excel(import_excel(table), </span><span>"</span><span>G:	est1.xlsx</span><span>"</span>)