大蟒蛇python教程共享Python+pandas编写命令行脚本操作excel的tips详情

import logging  from logging.handlers import timedrotatingfilehandler  from logging.handlers import rotatingfilehandler  class log(object):      stand = "stand"   # 输出到控制台      file = "file"     # 输出到文件      all = "all"       # 输出到控制台和文件        def __init__(self, mode=stand):          self.log_format = "%(asctime)s - %(levelname)s - %(message)s"          self.logger = logging.getlogger()          self.init(mode)      def debug(self, msg):          self.logger.debug(msg)      def info(self, msg):          self.logger.info(msg)      def warning(self, msg):          self.logger.warning(msg)      def error(self, msg):          self.logger.error(msg)      def init(self, mode):          self.logger.setlevel(logging.debug)          if mode == "stand":              # 输出到控制台 ------              self.stand_mode()          elif mode == "file":              # 输出到文件 --------              self.file_mode()          elif mode == "all":              # 输出到控制台和文件              self.stand_mode()              self.file_mode()      def stand_mode(self):          stand_handler = logging.streamhandler()          stand_handler.setlevel(logging.debug)          stand_handler.setformatter(logging.formatter(self.log_format))          self.logger.addhandler(stand_handler)        def file_mode(self):          '''          filename：日志文件名的prefix；          when：是一个字符串，用于描述滚动周期的基本单位，字符串的值及意义如下：           “s”: seconds           “m”: minutes           “h”: hours           “d”: days           “w”: week day (0=monday)           “midnight”: roll over at midnight          interval: 滚动周期，单位有when指定，比如：when='d',interval=1，表示每天产生一个日志文件；          backupcount: 表示日志文件的保留个数；          '''          # 输出到文件 -----------------------------------------------------------          # 按文件大小输出          # file_handler = rotatingfilehandler(filename="my1.log", mode='a', maxbytes=1024 * 1024 * 5, backupcount=10, encoding='utf-8')  # 使用rotatingfilehandler类，滚动备份日志          # 按时间输出          file_handler = timedrotatingfilehandler(filename="my.log", when="d", interval=1, backupcount=10,                                                  encoding='utf-8')          file_handler.setlevel(logging.debug)          file_handler.setformatter(logging.formatter(self.log_format))          self.logger.addhandler(file_handler)  log = log(mode=log.stand)

from utils.logutil import log  if __name__ == '__main__':      log.debug("debug msg")      log.info("info msg")      log.warning("warning msg")      log.error("error msg")

# 使用说明 -----------------------------------  time.sleep(1)  print('===========================================================')  print('简单说明：使用正则表达式拆分excel表中不规范的作者，初步提取对应需求字段')  print('ps:')  print('1.文件夹下需要有一个excel（只放一个，名称随意），其中一列“作者”保存着待拆分的作者')  print('2.拆分后的excel将新增几列拆分结果列，以 <作者>[拆分] 作为列名标记')  print('===========================================================')  time.sleep(1)  # ------------------------------------------

# 输入操作路径 ----------------------------------------------------------------  operate_dir = input('请输入excel目录（旺柴）:')  # d:pycharmprojectsspiders图片下载工具excel  operate_dir = os.path.abspath(operate_dir)  # operate_dir = os.path.abspath(r'c:userscxstar46desktop正则表达式题名拆分测试')  # -----------------------------------------------------------------------------

# 检测excel数量，只能放一个，当只有一个excel时，提取它的路径excel_path -------  log.info('检查路径下的文件格式...')  excel_name = none  excel_count = 0  file_list = os.listdir(operate_dir)  for file in file_list:      if file.endswith('.xlsx') or file.endswith('.xlx'):          excel_count += 1          excel_name = file  if excel_count == 0:      log.error('文件夹下没有excel')      input('按任意键退出...')      raise exception(0)  if excel_count > 1:      log.error("无法读取excel，文件夹下有多个excel，或者excel未关闭")      input('按任意键退出...')      raise exception(0)    # print(excel_name)  # raise exception(1212)  # ----------------------------------------------------------------------  # print(excel_path)  # print(img_dir)    # 读取excel ----------------------------------------  excel_path = os.path.join(operate_dir, excel_name)  # print(excel_path)  try:      df = pd.read_excel(excel_path)      df = df.where(df.notnull(), none)  except exception as e:      log.error(e)      log.error('文件不存在或已损坏...')      input('按任意键退出...')      raise exception(0)  # -------------------------------------------------    # 打印excel行数  print(df.shape[0])

# 备份原始excel表 --------------------------------------------------------------  log.info('备份excel...')  bak_dir = '封面上传前的备份'   # 备份文件夹的名称  file_list = os.listdir(operate_dir)  if bak_dir not in file_list:      os.makedirs(os.path.join(operate_dir, bak_dir))  bak_excel_path = os.path.join(os.path.join(operate_dir, bak_dir), "{}_{}".format(datetime.datetime.now().strftime("%y-%m-%d_%h-%m-%s"), excel_name))  shutil.copyfile(excel_path, bak_excel_path)  # -----------------------------------------------------------------------------

try:      raise exception("执行业务报错")  except exception as e:      import traceback      log.error(traceback.format_exc())	# 记录异常信息  input('执行完毕，按任意键继续...')

cover_ruid_col_name = "封面ruid"    # 没有封面ruid这一列就创建  if cover_ruid_col_name not in df.columns.values:      df.loc[:, cover_ruid_col_name] = none

# 读取excel  excel_path = './封面上传测试.xlsx'  df = pd.read_excel(excel_path)  review_col = "审核结果"  # 没有“审核结果”这一列就创建  if review_col not in df.columns.values:      df.loc[:, review_col] = none  for i in range(df.shape[0]):    	# 打印进度 ---------------------------------------------      log.info("----------------------------------")      log.info("当前进度： {} / {}".format(i+1, df.shape[0]))      # ----------------------------------------------------  	# 执行表格插入业务  	# 判断业务  	# 吧啦吧啦  	# 业务执行结果插入原表  	df.loc[i, "审核结果"] = "好耶"        # 阶段性保存 ----------------------------      save_space = 200	# 每执行两百行保存一次      if i+1 % save_space == 0 and i != 0:          df.to_excel(excel_path, index=0)          log.info("阶段性保存...")      # -------------------------------------