streamlit-app,Excel数据处理

使用streamlit 创建的一个Excel数据处理程序。

import streamlit as st
import pandas as pd
from io import BytesIO
import re

st.set_page_config(
        page_title="Excel数据处理",
)

st.header("Excel数据处理")

with st.expander("Help"):
    st.write("建议使用xlsx格式，第一列禁止空数据单元格，建议添加ID编号，防止处理时候遗漏数据")

def ToExcel(df):
    output=BytesIO()
    writer=pd.ExcelWriter(output,engine="openpyxl")
    df.to_excel(writer,index=False,sheet_name="result")
    writer.close()
    processed_data=output.getvalue()
    return processed_data

def ex_tel(msg):
    pattern = re.compile(r'1[3456789]\d{9}')
    matches=pattern.findall(msg)
    if len(matches)>0:
        return ",".join(matches)
    return None

def ex_idcard(msg):
    pattern = re.compile(r'\d{17}[Xx0-9]{1}')
    matches = pattern.findall(msg)
    if len(matches) > 0:
        return ",".join(matches)
    return None

upload_file=st.file_uploader(label="上传Excel文件",type=["xlsx","xls"],help ="请核查Excel是否有异常，如加密、数据错行等")
if upload_file is not None:
    try:
        df=pd.read_excel(upload_file,dtype="str")
        st.write("数据预览：")
        st.write(df.head(4))
        cols=df.columns
        option1=st.selectbox(label="选择要处理列:",options=cols)
        st.write("当前选择操作列名：",option1)
        option2 = st.selectbox(label="选择数据操作:", options=("提取号码到新列","提取身份证到新列"))
        if st.button("执行解析方法",type="primary"):
            if option2=="提取号码到新列":
                st.write(">>",option2)
                df["号码提取结果"]=df[option1].apply(ex_tel)
                result = ToExcel(df)
                st.download_button(label="下载处理结果", data=result, file_name='df_result.xlsx')
            elif option2=="提取身份证到新列":
                st.write(">>",option2)
                df["身份证提取结果"] = df[option1].apply(ex_idcard)
                result=ToExcel(df)
                st.download_button(label="下载处理结果",data=result,file_name= 'df_result.xlsx')
            else:
                st.warning("处理异常")

    except Exception as e:
        st.write(e)