python处理excel表格

python中读取excel的包：xlrd

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from __future__ import unicode_literals
import sys
import re

reload(sys)
sys.setdefaultencoding('utf8')

import xlrd
import json

file = "dbsetting.xlsx"
data = xlrd.open_workbook(file)  # 打开文件
# print(data)
table = data.sheets()[0]  # 选择第一个sheets
# print(table)
nrows = table.nrows   # 【一共多少行】
# print(nrows)  # 299
"""
处理后的数据格式
需要处理的数据列
"""
returnData = {}

for row in range(1, nrows):
    content = table.row_values(row)  # 第几行的数据，一般第一行都是标题，从第二行开始是数据体
    # print content,type(content)  # 输出的是列表，之后可以操作列表
    # 可将处理后的数据放进字典，json序列化，写入文件

python中写入excel的包：xlwt

import xlwt

def to_excel():
    # 创建一个workbook 设置编码
    workbook = xlwt.Workbook(encoding='utf-8')
    # 创建一个worksheet
    worksheet = workbook.add_sheet('My Worksheet')
    # 标头
    keys = ['a', 'b', 'c', 'd', 'e']
    for h in range(len(keys)):
        worksheet.write(0, h, keys[h])

    # 写入数据
    data = [[1, 2, 3, 4, 5], [6, 7, 8, 9, 10], [11, 12, 13, 14, 15], [16, 17, 18, 19, 20]]
    i = 1
    for line in data:
        j = 0
        for x in line:
            worksheet.write(i, j, x)
            j += 1
        i += 1

    # 保存
    workbook.save('text' + '.xlsx')

to_excel()

原excel基础上添加列，可使用包：openpyxl（pip install openpyxl）

import openpyxl

def add_pv():
    """
    添加pv列
    :return:
    """
    b_pv_list = []
    with open('./b/ok.log', 'r', encoding='utf-8')as f:
        for line in f:
            b_pv_list.append(line.split('\t')[2].strip())

    wb = openpyxl.load_workbook('./b标注结果.xlsx')
    ws = wb.worksheets[0]
    for index, row in enumerate(ws.rows):
        if index == 0:
            row[2].value = 'pv'
        else:
            row[2].value = b_pv_list[index - 1]
    wb.save('add_pv_b.xlsx')

add_pv()

原excel基础上追加内容，可使用包：xlutils（pip install xlutils）

from xlutils.copy import copy

def readline():
    wb = xlrd.open_workbook(r'add_pv_b.xlsx')  
    sheet1 = wb.sheet_by_index(0)  # 获取第一张表
    nrows = sheet1.nrows  # 获取总行数
    ncols = sheet1.ncols
    return nrows

def append_data():
    """
    将没有结果的数据追加至最后
    :return:
    """
    b_no_ok_list = []
    with open('./b/no_ok.log', 'r', encoding='utf-8')as f:
        for line in f:
            b_no_ok_list.append(line.split('\t'))

    data = xlrd.open_workbook(r'add_pv_b.xlsx')
    ws = copy(data)  # 复制之前表里存在的数据
    table = ws.get_sheet(0)
    nownrows = readline()

    for info in b_no_ok_list:
        query = info[0]
        type = info[1]
        pv = info[2].strip()
        table.write(nownrows, 0, label=query)  # 最后一行追加数据
        table.write(nownrows, 1, label=type)
        table.write(nownrows, 2, label=int(pv))
        table.write(nownrows, 3, label='')
        table.write(nownrows, 4, label='')
        table.write(nownrows, 5, label='')
        table.write(nownrows, 6, label='FALSE')
        table.write(nownrows, 7, label=0)
        nownrows += 1

    ws.save('add_no_recall_b.xlsx')  # 保存的有旧数据和新数据

append_data()

如果遇到日期格式，可以这样处理

import xlrd

book =xlrd.open_workbook("demo.xlsx")
sheet = book.sheet_by_index(0)
nrows = sheet.nrows
for i in range (1,nrows):
    startDate = sheet.cell_value(i, 10)
    startDate = xlrd.xldate.xldate_as_datetime(startDate, 0)
    startDate = startDate.strftime('%Y-%m-%d %H:%M:%S') #即可转换为日期格式

最后编辑于：2020.09.30 11:37:30