python读写excel

	XlsxWriter	xlrd	xlwt	openpyxl
介绍	可以创建XLSX文件	用来读取xls文件，是python-excel的三大模块	用来写xls文件，是python-excal的三大模块	可以读写XLSX、XLSM文件
读	×	√	×	√
写	√	×	√	√
.xls	×	√	√	×
.xlsx	√	×	×	√
大文件	√	×	×	√

Excel 2003 即XLS文件有大小限制即65536行256列，所以不支持大文件，而Excel 2007以上即XLSX文件的限制则为1048576行16384列

下面使用xlrd和xlutils的copy来写excel文件的例子，最后save只能新建一个文件，不能在原文件上写，所以还是有不足的地方

from xlrd import open_workbook
from xlutils.copy import copy


def mapFind(map, key):
    try:
        return map.get(key) is not None;
    except Exception as e:
        return False;

if __name__ == '__main__':
    #从三个文件中读出手机号，然后记录到map中
    phoneMap = {};
    files = ["C:/Users/Desktop/2020-05-21/前5000.csv","C:/Users/Desktop/2020-05-21/中5000.csv","C:/Users/Desktop/2020-05-21/后所有.csv","C:/Users/Desktop/2020-05-21/匹配.csv"]
    title = "";
    for file in files:
        with open(file, 'rb') as f:
            i = 0;
            while True:
                l = f.readline();
                if l:
                    try:
                        line = l.decode("gbk", 'ignore');
                    except Exception as e:
                        try:
                            line = l.decode("utf-8");
                        except Exception as e:
                            print("行数:" + str(i));
                            print(l);
                            print(e);
                            print("-------------------------------")
                    line = line.strip('\r\n')
                    line = line.replace('"','');
                    if i == 0:
                        title = line;
                    else:
                        infos = line.split(",");
                        phoneMap[infos[4]] = infos;
                    i = i + 1;
                else:
                    break;
    print(len(phoneMap))

    rb = open_workbook('D:\\202003.xlsx')

    table = rb.sheet_by_index(0)
    wb = copy(rb)

    ws = wb.get_sheet(0)
    nrows = table.nrows
    ncols = table.ncols

    print(nrows,ncols)

    for i in range(nrows):
        if i==0:
            titles = title.split(",");
            index = 0;
            for title in titles:
                ws.write(i, 17 + index, title)
                index=index+1
        else:
            phone = str(int(table.cell(i,8).value))
            if mapFind(phoneMap,phone):
                infos = phoneMap[phone];
                #将匹配的内容写入文件
                index = 0;
                for info in infos:
                    ws.write(i,17+index,info)
                    index=index+1
    wb.save("D:\\new.xlsx")

用XlsxWriter的例子：

    ACCZ = []

    ACAZ = []

    title = "";
    serviceMap = {};
    for serviceCode in ACCZ:
        serviceMap[serviceCode] = serviceCode
    for serviceCode in ACAZ:
        serviceMap[serviceCode] = serviceCode

    folderPath = "C:/Users//Desktop/3月地市数据/";
    files = os.listdir("C:/Users//Desktop/3月地市数据/")
    newFolderPath = "C:/Users//Desktop/3月数据梳理/";
    for file in files:
        if file.startswith("~$"):
            continue
        print(file)
        rb = open_workbook(folderPath + file)

        table = rb.sheet_by_index(1)

        nrows = table.nrows
        ncols = table.ncols

        print(nrows, ncols)

        f = xlsxwriter.Workbook(newFolderPath + file)
        worksheet = f.add_worksheet('操作日志')

        newRowIndex = 1
        for i in range(nrows):
            if i == 0:
                for colsIndex in range(ncols):
                    value = table.cell(0, colsIndex).value
                    print(value)
                    worksheet.write(0, colsIndex, value)
            else:
                serviceCode = table.cell(i, 9).value
                if mapFind(serviceMap, serviceCode):
                    for colsIndex in range(ncols):
                        value = table.cell(i, colsIndex).value
                        print(value)
                        worksheet.write(newRowIndex, colsIndex, value)
                    newRowIndex=newRowIndex+1
        f.close();