python xlrd，pandas，openpyxl实现excel导出csv，支持xls，xlsm, xlsx

项目组之前的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])