[Python知识库]读写excel文件

qichu_qimo.py

# coding=utf-8
'''
pip install openpyxl -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com
'''
import openpyxl
import os
import shutil

gConfigFile = '要显示的标题配置文件.txt'


def getCell(sh, row, column):
    cell = sh.cell(row=row, column=column)
    return cell


def dealWithOne(fPath):
    kvs = {}
    wb = openpyxl.load_workbook(fPath)
    sh = wb.get_sheet_by_name("pgMain_1")
    print("fPath {} {} {}".format(fPath, sh.max_row, sh.max_column))

    key3 = None
    key2 = None
    if sh.max_row >= 6 and sh.max_column > 1:
        kvs['公司'] = getCell(sh, 2, 1).value
        kvs['PayPal账户'] = getCell(sh, 3, 1).value
        kvs['起止日期'] = getCell(sh, 5, 1).value
        kvs['货币类型'] = getCell(sh, 6, 1).value

    if sh.max_column == 3:
        for i in range(7, sh.max_row + 1):
            col1 = getCell(sh, i, 1)
            col2 = getCell(sh, i, 2)
            col3 = getCell(sh, i, 3)

            if col2.value == '期初' and col3.value == '期末':
                key2 = '期初'
                key3 = '期末'
            elif col2.value == '扣款' and col3.value == '存款':
                key2 = '扣款'
                key3 = '存款'
            elif key2 and key3:
                # print("i {}, col1 {}  {} {}, col2 {} {} {}, col3 {} {} {}".format(
                #     i, col1.data_type, col1.value, col1,
                #     col2.data_type, col2.value, col2,
                #     col3.data_type, col3.value, col3))
                if col1.value:
                    k2 = '-'.join((key2, col1.value))
                    k3 = '-'.join((key3, col1.value))
                    value2 = ''
                    value3 = ''
                    if col2.value:
                        value2 = col2.value
                    if col3.value:
                        value3 = col3.value

                    kvs[k2] = value2
                    kvs[k3] = value3
    return kvs


def dealAll():
    DST_DIR = '存放合并后的期初期末表'
    TABLE_FILE = "合并后.xlsx"
    root = os.getcwd()
    dstDirPath = os.path.sep.join((root, DST_DIR))
    if os.path.exists(dstDirPath):
        shutil.rmtree(dstDirPath)

    os.makedirs(dstDirPath)
    allActiveArr = []
    for parent, ds, fs in os.walk(root):
        for f in fs:
            if (f.lower().startswith('fsr') and f.lower().endswith('.xlsx')):
                srcPath = os.path.sep.join((parent, f))
                kvs = dealWithOne(srcPath)
                allActiveArr.append(kvs)

    maxKeyItem = {}
    for kvs in allActiveArr:
        if len(kvs.keys()) > len(maxKeyItem.keys()):
            maxKeyItem = kvs

    print("max len {}".format(len(maxKeyItem.keys())))
    dstTablePath = os.path.sep.join((dstDirPath, TABLE_FILE))
    wb = openpyxl.Workbook()
    sh = wb.create_sheet('merge', 0)
    keys = getConfigs()
    if len(keys) == 0:
        for k in maxKeyItem.keys():
            keys.append(k)

    global gConfigFile
    
    with open(gConfigFile, mode='w', encoding='utf-8') as f:
        f.write('\n'.join(keys))

    for i in range(0, len(keys)):
        sh.cell(row=1, column=(i+1), value=keys[i])

    for line in allActiveArr:
        oneItem = []
        for k in keys:
            value = line[k]
            oneItem.append(value)
        sh.append(oneItem)
    wb.save(dstTablePath)


def getConfigs():
    titles = []
    global gConfigFile
    if os.path.exists(gConfigFile):
        with open(gConfigFile, mode='r', encoding='utf-8') as f:
            while True:
                line = f.readline()
                if line:
                    ti = line.strip()
                    if len(ti) > 0:
                        titles.append(ti)
                else:
                    break
    return titles


if __name__ == '__main__':
    dealAll()
    print("main end")

csv2xlsx.py

# coding=utf-8
import pandas as pd
import os
'''
csv转xlsx格式
pip install pandas -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com
'''


def dealWith():
    DST_FOLDER = 'CSV转XSX后'
    root = os.getcwd()
    DST_PARENT_PATH = os.path.sep.join((root, DST_FOLDER))

    if not os.path.exists(DST_PARENT_PATH):
        os.makedirs(DST_PARENT_PATH)

    SUFFIX = '.csv'
    for parent, ds, fs in os.walk(root):
        if not parent == DST_PARENT_PATH:
            for f in fs:
                if f.lower().endswith(SUFFIX):
                    oldPath = os.path.sep.join((parent, f))
                    index = len(f) - len(SUFFIX)
                    newName = f[0:index] + ".xlsx"
                    newPath = os.path.sep.join((DST_PARENT_PATH, newName))
                    oldFile = pd.read_csv(oldPath, encoding='utf-8')
                    oldFile.to_excel(
                        newPath.lower(), sheet_name='Download', index=False)
                    # oldFile.to_excel(newPath.lower(), engine='xlsxwriter')


if __name__ == '__main__':
    dealWith()
    print("main end")

rename_copy_to_same_folder.py

# coding=utf-8
import os
from shutil import copyfile


def dealWith():
    DST_PREFIX = '改名后'
    root = os.getcwd()
    dstDir = os.path.sep.join((root, DST_PREFIX))
    suffixArray = ['.xlsx', '.csv']

    if not os.path.exists(dstDir):
        os.makedirs(dstDir)

    for parent, ds, fs in os.walk(root):
        if not parent == dstDir:
            for f in fs:
                dirName = os.path.basename(parent)
                for suffix in suffixArray:
                    if f.lower().endswith(suffix):
                        oldPath = os.path.sep.join((parent, f))
                        dstPath = os.path.sep.join((dstDir, dirName + "-" + f))
                        try:
                            if oldPath.startswith(DST_PREFIX):
                                print("error {}".format(oldPath))
                            copyfile(oldPath, dstPath)
                        except IOError as e:
                            print("copyoldPath = {},  erro = {}".format(oldPath, e))
                        finally:
                            pass


if __name__ == '__main__':
    dealWith()
    print("main finish.")

后来又打包成exe文件

安装pyinstaller

pip install pyinstaller -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com

然后生成打包exe

pyinstaller qichu_qimo.py

在dist文件夹下出现qichu_qimo