excel处理

s1,s2,s3,s4,s5,s6,s7 = [],[],[],[],[],[],[] 
def process_one(path):
 df = pd.read_excel(path)
 data = list(df.values)
 if len(list(data[0])) != 7:
  print(path)
  return

 for tem in data:
  tem_list =list(tem)
  s1.append(tem_list[0])
  s2.append(tem_list[1])
  s3.append(tem_list[2])
  s4.append(tem_list[3])
  s5.append(tem_list[4])
  s6.append(tem_list[5])
  s7.append(tem_list[6])
 for idx in range(6):
  s1.append('')
  s2.append('')
  s3.append('')
  s4.append('')
  s5.append('')
  s6.append('')
  s7.append('')
path = "1.县（市）社会经济主要指标/"
for tem in os.listdir(path):
 process_one(path + tem)


pd.DataFram({"1":s1,'2':s2,'3':s3,'4':s4,'5':s5,'6':s6,'7':s7}).to_excel("res.xlsx")

 

import msoffcrypto

s1,s2,s3,s4,s5,s6,s7 = [],[],[],[],[],[],[] 
def process_one(path):
 try:
    df = pd.read_excel(path, errors='ignore')
 except:
    print(path)
    return
 data = list(df.values)
 if len(list(data[0])) != 7:
  print(path)
  return

 for tem in data:
  tem_list =list(tem)
  s1.append(tem_list[0])
  s2.append(tem_list[1])
  s3.append(tem_list[2])
  s4.append(tem_list[3])
  s5.append(tem_list[4])
  s6.append(tem_list[5])
  s7.append(tem_list[6])
 for idx in range(6):
  s1.append('')
  s2.append('')
  s3.append('')
  s4.append('')
  s5.append('')
  s6.append('')
  s7.append('')
path = "20220724/2013/"
import os
import pandas as pd
for tem in os.listdir(path):
 
 file = msoffcrypto.OfficeFile(open(path + tem, 'rb'))
 file.load_key(password='VelvetSweatshop')
 path_ = (path + tem).replace("xls", "xlsx")
 os.popen("rm -rf " + path + tem).readlines()
 file.decrypt(open(path_, 'wb'))

 process_one(path_)


pd.DataFrame({"1":s1,'2':s2,'3':s3,'4':s4,'5':s5,'6':s6,'7':s7}).to_excel("2013.xlsx")

 

import msoffcrypto
import os
import pandas as pd

def process_one(path):
   df = pd.read_excel(path, errors='ignore')
   tem_list = list(df.values)
   row = len(tem_list)
   col = len(tem_list[0])
   i = 0
   while i < col:
      for j in range(row):
         s1.append(tem_list[j][i] if i < col else '')
         s2.append(tem_list[j][i+1] if i+ 1 < col else '')
         s3.append(tem_list[j][i+2] if i + 2 < col else '')
         s4.append(tem_list[j][i+3] if i + 3 < col else '')
         s5.append(tem_list[j][i+4] if i + 4 < col else '')
         s6.append(tem_list[j][i+5] if i + 5 < col else '')
         s7.append(tem_list[j][i+6] if i + 6 < col else '')
      i = i + 9
      if i >= col:
         break

      for _ in range(6):
         s1.append('')
         s2.append('')
         s3.append('')
         s4.append('')
         s5.append('')
         s6.append('')
         s7.append('')


for year in range(2008,2013):
   path = "20220724/" + str(year) + "/"
   s1,s2,s3,s4,s5,s6,s7 = [],[],[],[],[],[],[]
   for tem in os.listdir(path):

      file = msoffcrypto.OfficeFile(open(path + tem, 'rb'))
      file.load_key(password='VelvetSweatshop')
      path_ = (path + tem).replace("xls", "xlsx")
      os.popen("rm -rf " + path + tem).readlines()
      file.decrypt(open(path_, 'wb'))

      process_one(path_)
   pd.DataFrame({"1":s1,'2':s2,'3':s3,'4':s4,'5':s5,'6':s6,'7':s7}).to_excel(str(year) + ".xlsx")