项目组之前的excel导出是通过宏命令完成的,但是这个没法做到批量操作,现在需要直接将某个目录下所有的xlsm文件导出为csv,每个sheet对应一个csv文件。
我的实现方案如下:
1.安装python3,勾选pip,省的自己再安装
2.pip安装openpyxl,xlrd,pandas
pip install openpyxl xlrd pandas
3.安装pyinstaller,将python脚本封装成exe
pip install pyinstaller,导出时只需要一个exe即可
python代码如下:
import sys
import os
import re
import pprint
import inspect
import csv
import openpyxl
import pandas as pd
import xlrd
def scan_files(directory,postfix):
files_list=[]
for root, sub_dirs, files in os.walk(directory):
for special_file in files:
if special_file.endswith(postfix):
files_list.append(os.path.join(root,special_file))
return files_list
def export_single_excel_xlrd(file_name, target_dir):
print(file_name)
execl_file = xlrd.open_workbook(file_name)
for sheet_name in execl_file.sheet_names():
if sheet_name.endswith(".csv"):
csv_file_name = target_dir + "\\" + sheet_name
else:
csv_file_name = target_dir + "\\" + sheet_name+".csv"
print(csv_file_name)
csv_file = csv.writer(open(csv_file_name,'w', encoding='utf-8', newline=''))
sheet_data = execl_file.sheet_by_name(sheet_name)
nrows = sheet_data.nrows
ncols = sheet_data.ncols
for row_idx in range(0,nrows):
row_data = sheet_data.row_values(row_idx)
csv_file.writerow(row_data)
def export_single_excel_pandas(file_name, target_dir):
print(file_name)
execl_file = pd.read_excel(io=file_name, sheet_name=None)
print(execl_file)
for sheet_name in execl_file:
if sheet_name.endswith(".csv"):
csv_file_name = target_dir + "\\" + sheet_name
else:
csv_file_name = target_dir + "\\" + sheet_name+".csv"
print(csv_file_name)
execl_file[sheet_name].to_csv(csv_file_name, index=False, float_format = '%g', encoding = 'utf-8');
def export_single_excel_openpyxl(file_name, target_dir):
print(file_name)
execl_file = openpyxl.load_workbook(file_name)
for sheet_name in execl_file.sheetnames:
csv_file_name = target_dir+"\\"+sheet_name+".csv"
print(csv_file_name)
#newline=''是必须的,这样可以避免多输出一行空行
csv_file = csv.writer(open(csv_file_name,'w', encoding='utf-8', newline=''))
sheet_data = execl_file[sheet_name]
for row in sheet_data.rows:
row_data=[]
all_none=True
for field in row:
row_data.append(field.value)
if field.value != None:
all_none = False
#忽略空行
if all_none != True:
csv_file.writerow(row_data)
all_excel_files = scan_files(sys.argv[1], "xlsm")
for execl_file in all_excel_files:
export_single_excel_xlrd(execl_file, sys.argv[2])