python写一些脚本

import json
import pandas as pd
import re
import requests
from PIL import Image
from io import BytesIO
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as OpenpyxlImage
import os


def read_json_and_save_to_excel():
# 假设你的文件路径是当前目录下的data.json
file_path = 'data.json'
# 定义输出的Excel文件名
output_excel_file = 'image_urls.xlsx'

# 打开文件并读取其内容
with open(file_path, 'r', encoding='utf-8') as file:
# 解析JSON数据
data = json.load(file)

# 获取实际的数据列表
real_data = data['data']

# 创建一个空列表来存储imageUrl
image_urls = []

# 遍历列表并收集所有的imageUrl
for item in real_data:
# 检查是否存在imageUrl键
if 'imageUrl' in item:
image_urls.append(item['imageUrl'])
elif 'liveData' in item and 'imageUrl' in item['liveData']:
image_urls.append(item['liveData']['imageUrl'])
else:
image_urls.append(None) # 如果没有imageUrl，则添加None

# 将imageUrl列表转换为pandas DataFrame
df = pd.DataFrame(image_urls, columns=['originImageUrl'])

# 将DataFrame保存到Excel文件
df.to_excel(output_excel_file, index=False)

print(f"Image URLs have been saved to {output_excel_file}")


def read_webp_and_save_to_excel():
# 假设你的文件路径是当前目录下的data.txt
file_path = 'webp.txt'

# 准备正则表达式，用于匹配URL
url_pattern = re.compile(r'https?://[^\s]+')

# 读取文本文件并提取URL到列表中
url_list = []
# 打开文件并逐行读取内容
with open(file_path, 'r', encoding='utf-8') as file:
# 逐行读取文件
for line in file:
# 使用正则表达式查找URL
match = url_pattern.search(line)
if match:
# 提取URL
url_list.append(match.group())

# 读取Excel文件
excel_path = 'image_urls.xlsx'
df = pd.read_excel(excel_path)

# 创建一个新列webpImageUrl，初始值为None
df['webpImageUrl'] = None

# 遍历originImageUrl列，匹配并填充webpImageUrl列
for index, row in df.iterrows():
origin_url = row['originImageUrl']
for url in url_list:
if url.startswith(origin_url):
df.at[index, 'webpImageUrl'] = url
break # 找到匹配项后跳出循环

# 将更新后的DataFrame保存到新的Excel文件中
df.to_excel('webp_image_urls.xlsx', index=False) # 替换为你想要保存的文件名


def read_heif_and_save_to_excel():
# 假设你的文件路径是当前目录下的data.txt
file_path = 'heif.txt'

# 准备正则表达式，用于匹配URL
url_pattern = re.compile(r'https?://[^\s]+')

# 读取文本文件并提取URL到列表中
url_list = []
# 打开文件并逐行读取内容
with open(file_path, 'r', encoding='utf-8') as file:
# 逐行读取文件
for line in file:
# 使用正则表达式查找URL
match = url_pattern.search(line)
if match:
# 提取URL
url_list.append(match.group())

# 读取Excel文件
excel_path = 'webp_image_urls.xlsx'
df = pd.read_excel(excel_path)

# 创建一个新列webpImageUrl，初始值为None
df['heifImageUrl'] = None

# 遍历originImageUrl列，匹配并填充heifImageUrl列
for index, row in df.iterrows():
origin_url = row['originImageUrl']
for url in url_list:
if url.startswith(origin_url):
df.at[index, 'heifImageUrl'] = url
break # 找到匹配项后跳出循环

# 将更新后的DataFrame保存到新的Excel文件中
df.to_excel('heif_image_urls.xlsx', index=False) # 替换为你想要保存的文件名


# 定义一个函数来获取URL对应的文件大小
def get_file_size(url):
try:
response = requests.head(url, allow_redirects=True, timeout=10)
size = response.headers.get('content-length', 0) # 获取文件大小
return round(int(size) / 1024.0, 2)
except Exception as e:
print(f"Error getting file size for {url}: {e}")
return 0


def save_image_file_size_to_excel():
# 读取Excel文件
excel_path = 'android_final_image_info_with_thumbnails.xlsx' # 替换为你的Excel文件路径
df = pd.read_excel(excel_path)

if df['originImageSize'].dtype != 'float':
df['originImageSize'] = df['originImageSize'].astype(float)
if df['webpImageSize'].dtype != 'float':
df['webpImageSize'] = df['webpImageSize'].astype(float)
if df['heifImageSize'].dtype != 'float':
df['heifImageSize'] = df['heifImageSize'].astype(float)
# # 创建三个个新列来存储文件大小
# df['originImageSize'] = None
# df['webpImageSize'] = None
# df['heifImageSize'] = None

# 遍历DataFrame，获取每个URL对应的文件大小
for index, row in df.iterrows():
if pd.notnull(row['originImageUrl']):
df.at[index, 'originImageSize'] = get_file_size(row['originImageUrl'])
if pd.notnull(row['webpImageUrl']):
df.at[index, 'webpImageSize'] = get_file_size(row['webpImageUrl'])
if pd.notnull(row['heifImageUrl']):
df.at[index, 'heifImageSize'] = get_file_size(row['heifImageUrl'])

# 将更新后的DataFrame保存到新的Excel文件中
df.to_excel('android_final_image_info_with_thumbnails-1.xlsx', index=False) # 替换为你想要保存的文件名


# 定义下载图片并创建缩略图的函数
def download_and_resize_image(url, size=(128, 128)):
try:
response = requests.get(url)
response.raise_for_status() # 确保请求成功
img = Image.open(BytesIO(response.content))
img.thumbnail(size) # 创建缩略图

# 如果图像是CMYK模式，则转换为RGB
if img.mode == 'CMYK':
img = img.convert('RGB')

return img
except Exception as e:
print(f"Error downloading or resizing image {url}: {e}")
return None


def save_thumbnails_to_excel():
# 读取Excel文件
excel_path = 'android_final_image_info_with_thumbnails-1.xlsx' # 替换为你的Excel文件路径
wb = load_workbook(excel_path)
ws = wb.active

# 定义图片URL列和对应的Excel列
image_columns = {
'A': 'originImageUrl'
}

# 缩略图将存储在原始图片URL列的下一列
thumbnail_columns = {
'G': 'originImageThumbnail'
}

# 遍历每个图片URL列
for original_col, thumbnail_col in zip(image_columns.keys(), thumbnail_columns.keys()):
# 从第二行开始处理（假设第一行是标题行）
for index, cell in enumerate(ws[original_col][1:], start=2): # 从第二行开始
print(f'开始处理第{index}条')
image_url = cell.value
if image_url:
img = download_and_resize_image(image_url)
if img:
# 将PIL图像保存到字节流中
img_byte_array = BytesIO()
img.save(img_byte_array, format='PNG')
img_byte_array.seek(0)

# 创建openpyxl的Image对象
openpyxl_img = OpenpyxlImage(img_byte_array)

# 将图片插入到指定单元格
cell_ref = f'{thumbnail_col}{index}'
ws.add_image(openpyxl_img, cell_ref)

# 保存更新后的Excel文件
wb.save('android_final_image_info_with_thumbnails-2.xlsx')


def process_images():
# 读取json文件
with open('200image.json', 'r') as f:
data = json.load(f)
 
image_data = data['data']

# 读取excel文件
wb = load_workbook('rec_image.xlsx')
sheet = wb.active

# 创建一个字典，key为A列的url，value为B列的url
url_dict = {sheet.cell(row=i, column=1).value.split('@')[0]: sheet.cell(row=i, column=6).value for i in range(1, sheet.max_row+1)}

# 删除不存在imageUrl的项
# data['data'] = [item for item in data['data'] if 'imageUrl' in item]

# 遍历json数组
i = 0
while i < len(image_data):
if 'feedbacks' in image_data[i]:
del image_data[i]['feedbacks']
if 'mge' in image_data[i]:
del image_data[i]['mge']
if 'mgeExtras' in image_data[i]:
del image_data[i]['mgeExtras']
if i % 2 == 0:
if 'imageUrl' in image_data[i]:
if image_data[i]['imageUrl'] is not None:
tempUrl1 = image_data[i]['imageUrl'].split('@')[0]
if tempUrl1 in url_dict:
tempUrl2 = url_dict[tempUrl1]
if i+1 < len(image_data):
image_data[i+1]['imageUrl'] = tempUrl2
i += 1
else:
print(i , "imageUrl is 不存在")
if i+1 < len(image_data):
del image_data[i+1]
del image_data[i]
 
else:
print(i , "imageUrl is None")
i += 1
else:
if i+1 < len(image_data):
del image_data[i+1]
del image_data[i]
else:
i += 1
 
# 保留100条数据
data['data'] = data['data'][200:]

# 生成新的json文件
with open('resF2.json', 'w') as f:
json.dump(data, f, ensure_ascii=False, indent=4)


def read_excel_and_save_to_json():
# 读取Excel文件，不使用第一行作为列名
df = pd.read_excel('rec_image.xlsx', header=None)
print(df.columns)

# 提取第1、2、4、6列的数据
df = df[[0, 1, 3, 5]]

# 将DataFrame转换为字典列表
data_list = df.to_dict('records')

# 将字典列表保存到JSON文件
with open('output.json', 'w', encoding='utf-8') as f:
json.dump(data_list, f, ensure_ascii=False, indent=4)


if __name__ == '__main__':
read_excel_and_save_to_json()