python: excel 换行符(_x000D_)

 

    data4 = dataframe1.loc[4:34]
    for idx, datavalue in data4.iterrows():
        #strnum=Common.Utils.Utils.getAnnualLeave(data)
        print("[{}]: {}".format(idx, datavalue))
        slist=datavalue.tolist()
        hbll=BLL.EmpLoyeeHolidaysGet.EmpLoyeeHolidaysGet() #病假4小時_x000D_事假0.5小時 未处理
        #print(slist)
        for i in range(len(slist)):
            #strvalue=str(slist[i]).replace(r'\s+|\\n', ' ', regex=True)  _x000D_
            strvalue = str(slist[i]).replace('_x000D_', ' ')  # 规换单元格的换行符，否则处理不了正确数据
            strnums = hbll.getHolidays(strvalue)  # float str
            for sn in range(len(strnums)):
                  print("str:",strnums[sn].HolidayName,strnums[sn].WorkTime)

        print("类型：",type(datavalue))
    print("*************")

　　

 

    insura=[]
    objlist=[]
    datalist = []
    dulist=[]
    # 查询某文件夹下的文件名
    folderPath = Path(r'C:\\Users\\geovindu\\PycharmProjects\\pythonProject2\\')
    fileList = folderPath.glob('*.xls')
    for i in fileList:
        stname = i.stem
        print(stname)
    # 查询文件夹下的文件  print(os.path.join(path, "User/Desktop", "file.txt"))
    dufile = ReadExcelData.ReadExcelData.ReadFileName(folderPath, 'xls')
    for f in dufile:
        fileurl = os.path.join(folderPath, f)
        dulist1 = ReadExcelData.ReadExcelData.ReadDataFile(fileurl)  # object is not callable 变量名称冲突的原因
        for duobj in dulist1:
            dulist.append(duobj)
        print(os.path.join(folderPath, f))

    ylsum = 0  # 养老
    llsum = 0  # 医疗
    totalsum = 0  # 一年费用
    for geovindu in dulist:
        # duobj = Insurance.Insurance
        print(geovindu)
        name = geovindu.getInsuranceName()
        duname = name.convert_dtypes()
        # yname = duname['Unnamed: 2']
        print(type(duname))
        print("保险类型：", duname)  # class 'pandas.core.series.Series
        strname = pd.Series(duname).values[0]
        coas1 = geovindu.getInsuranceCost()
        # coast = int(geovindu.getInsuranceCost())
        coas = coas1.convert_dtypes()
        coast = pd.Series(coas).values[0]  # int(coas)
        # print("casa",int(coas))
        totalsum = totalsum + coast
        if (strname == "养老"):
            ylsum = ylsum + coast
        if (strname == "医疗"):
            llsum = llsum + coast
        print("费用：", coast)
        month = int(geovindu.getIMonth())
        print("月份：", month)
        datalist.append([strname, coast, month])
        #SQLServerDAL.SQLclass.insertStr(strname, coast, month)  # 插入数据库中
    print("一年养老", ylsum)
    print("一年医疗", llsum)
    print("一年费用", totalsum)
    # https: // pandas.pydata.org / pandas - docs / stable / reference / api / pandas.DataFrame.groupby.html
    # 导出数据生成EXCEL
    dataf = pd.DataFrame(datalist, columns=['保险类型', '交费金额', '交费月份'])  # 增加列名称
    dataf2 = pd.DataFrame({"统计类型": ["一年养老", "一年医疗", "一年费用"], "金额": [ylsum, llsum, totalsum]})
    dataf.sort_values('交费月份', inplace=True)  # 指定列排序
    print(sqldf('''SELECT 交费金额,交费月份 FROM dataf group by 交费月份 LIMIT 25'''))
    #staicmont=sqldf('''SELECT 交费金额,交费月份 FROM dataf group by 交费月份 LIMIT 25''')
    # 交费用分份统计
    # print(sqldf('''SELECT 交费金额,交费月份 FROM dataf group by 交费月份  LIMIT 25'''))
    staicmonth = sqldf('''SELECT 交费金额,交费月份 FROM dataf group by 交费月份 LIMIT 25''')

    with pd.ExcelWriter('geovindu.xlsx') as writer:
        dataf.to_excel(writer, sheet_name='2023年保险费用详情', index=False)
        dataf2.to_excel(writer, sheet_name='保险统计', index=False)
        staicmonth.to_excel(writer, sheet_name='月份统计', index=False)