[Python]Mac实现处理读写xlsx xls excel文件格式(含中文处理方法)

最近有个需求要处理excel 格式的数据，数据量比较大。用传统的语言似乎不太好处理，于是改用python实现，这里记录一下实现过程。

首先，科普一下xlsx xls的excel文件区别是什么。

• xls是03版Office Microsoft Office Excel 工作表的格式，用03版Office，新建Excel默认保存的Excel文件格式的后缀是.xls；

• xlsx是07版Office Microsoft Office Excel 工作表的格式，用07版Office，新建Excel默认保存的的Excel文件格式后缀是.xlsx。

使用xls的唯一理由只能是照顾老版本软件的兼容性需要

xls的坏处有：

• 如果你的数据超出256列和65536行，使用xls会导致数据被截断
• 使用xls格式后，无法使用一系列Excel新功能
• 使用xls格式会导致文件体积暴增
• 使用xls格式无法挽救出错文件
• xls格式没有xlsx格式快

所以大家以后还是用xlsx格式吧。

好了，言归正传，开始写一下实现部分，处理的部分用到了下面几个库

1. sudo easy_install pip
2. pip install openpyxl #读取xlsx格式excel文件
3. pip install xlrd #读取xls 格式excel文件
4. pip install uniout #可以显示中文
5. pip install numpy #数据保存

openpyxl模块

安装如上，处理excel的模块库有几种，我只记录跟使用了这一个，好用就行。

记录相关用法：

from openpyxl import load_workbook,Workbook

# 设置文件 mingc
addr = "yourexcel.xlsx"
# 打开文件
wb = load_workbook(addr)

#获取当前活跃的sheet
# sheet=wb.get_active_sheet()

#一般不清楚活跃的sheet是什么，所以这里可以指定用第一个sheet
sheetnames = wb.get_sheet_names() 
#获取第一个sheet
sheet = wb.get_sheet_by_name(sheetnames[0])


#获取列
print(sheet["B"])
#打印B列第2行的值（index从0算起）
print (sheet["B"][1].value)

#获取sheet 列和行数
print(sheet.max_row)
print(sheet.max_column)

#数据保存生成xlsx格式excel文件
workbook = Workbook()
sheet_output = workbook.active
sheet_output.title = "sheet_export"
sheet_output.append(["第一列内容","第二列内容","第三列内容","第四列内容"])
...#导出append你处理好的列数据
workbook.save('my_file_export.xlsx')

#其他处理
....

xlrd模块

xlrd用来读取xls excel文件，相关用法如下：

import xlrd
workbook = xlrd.open_workbook('yourexcel.xls') # 打开xls文件
worksheets = workbook.sheets()[0] # 打开第一张表
nrows = worksheets.nrows # 获取表的行数

#行操作：
sheet1.row_values(0)  # 获取第一行所有内容，合并单元格，首行显示值，其它为空。
sheet1.row(0)         　　# 获取单元格值类型和内容
sheet1.row_types(0)   # 获取单元格数据类型

#表操作
sheet1.row_values(0, 6, 10)   # 取第1行，第6~10列（不含第10表）
sheet1.col_values(0, 0, 5)    # 取第1列，第0~5行（不含第5行） 这个比较好用，取第几列，行范围内容数据
sheet1.row_slice(2, 0, 2)     # 获取单元格值类型和内容
sheet1.row_types(1, 0, 2)   # 获取单元格数据类型


#其他处理
....

中文处理

先说一下我的需求实现，我需要将一份xlsx数据全部吃进来，然后与另一份xls格式的文件比对(包含空的情况)，把筛选数据另存起来导出，功能目的很明确。

在实现的过程中发现，读取到的不是unicode字符就是十六进制的东西，结合之前的理解。大家可以参考一下之前记录的一篇关于python 编码解码的理解[Python]输出中文的方法，搞懂编码encode和解码decode.

处理实现如下：

#遍历第一列内容，包含中文格式，这里有2个处理。

#第一个，指定python版本编码方式：
import sys
if sys.version_info[0] == 3:
    from importlib import reload
    reload(sys)
if sys.version_info[0] == 2:
    reload(sys)
    sys.setdefaultencoding('utf-8')
#第二个，对内部的utf-8编码的数据先解码再编码即可拿到中文数据
for i in xrange(1,sheet.max_row):
            tempData = {}
            if len(str(sheet["A"][i].value)) == 0 or sheet["A"][i].value == None: #为空处理
                tempData["第一列"] = "nil"
            else:
                tempData["第一列"] = sheet["A"][i].value.decode('utf-8').encode('utf-8')
....

numpy模块

这个模块主要用到了一个功能是数据保存，数据保存方式很多，这里我也只用到了numpy。
这里有一个地方要注意，加载npy数据的时候要注意参数allow_pickle=True，numpy有一个版本变动，后面不支持必须要加这个arg

import numpy as np

# Save ur dic data.
np.save('my_file_source.npy', dicData) 

#npy格式文件内容读取，格式为dic
dic_exportData = np.load('my_file_source.npy',allow_pickle=True).item()

补充

这里，我的需求数据里有发现数据包含重复的情况，因此需要额外处理一下。

方法是将那一列数据先存到一个list里，通过该方法将重复数量大于2的print并以追加模式保存进txt文档里，供使用者查看。

def get_sort_list(templist):
    setlist = set(templist)
    dictlist = {}
    for item in setlist:
        if templist.count(item) >=2:
            dictlist.update({item:templist.count(item)})
    print("dup key list count : ",len(dictlist))
    print("dup key list : ",dictlist)
    if len(dictlist) !=0:
        file = open('dup_key.txt', 'a+') 
        for k,v in dictlist.items():
            file.write(str(k)+' '+str(v)+'\n') 
        file.close()