Python 读取Excel某一列｜转存json

import xlrd

worksheet = xlrd.open_workbook(u'测试.xlsx')
sheet_names= worksheet.sheet_names()
print(sheet_names)
for sheet_name in sheet_names:
    sheet = worksheet.sheet_by_name(sheet_name)
    rows = sheet.nrows # 获取行数
    cols = sheet.ncols # 获取列数，尽管没用到
    all_content = []
    for i in range(rows) :
        cell = sheet.cell_value(i, 2) # 取第二列数据
        try:
            cell = int(cell) # 转换为整数
            all_content.append(cell)
        except ValueError as e:
            pass
    # cols = sheet.col_values(2) # 获取第二列内容 但是整数会处理成float
    print(all_content)

升级版本，将表格以json 输出到文件

import xlrd
import json
from datetime import datetime
from xlrd import xldate_as_tuple

worksheet = xlrd.open_workbook(u'text.xlsx')
sheet_names= worksheet.sheet_names()
print(sheet_names)
for sheet_name in sheet_names:
    sheet = worksheet.sheet_by_name(sheet_name)
    rows = sheet.nrows # 获取行数
    cols = sheet.ncols # 获取列数，
    all_content = {}
    for i in range(rows) :
        crmid = ''
        for j in range(cols):
            cell = sheet.cell_value(i, j) #
            ctype = sheet.cell(i, j).ctype
            if j==0 :
                try:
                    id = int(cell)  # 以第一列做键值
                except ValueError as e:
                    id = cell
                all_content[id] = []
            
            if ctype ==3: # 若是日期格式转换为固定格式
                date = datetime(*xldate_as_tuple(cell, 0))
                cell = date.strftime('%Y-%n-%d')
            else:
                pass
            
            all_content[id].append(cell)
    res = json.dumps(all_content)
    with open('test.txt', 'a') as f:
        f.write(res) # 为啥输出到文件呢，因为数据太多的时候print打印不全
    print (res)

ps版本：按行或列获取全部：

import xlrd

worksheet = xlrd.open_workbook(u'test.xlsx')
sheet_names= worksheet.sheet_names()
print(sheet_names)
for sheet_name in sheet_names:
    sheet = worksheet.sheet_by_name(sheet_name)
    rows = sheet.nrows # 获取行数
    cols = sheet.ncols # 获取列数

    # 输出每一行数据
    all_content = []
    row_content = []
    for i in range(rows) :
        row_content = []
        for j in range(cols) :
            cell = sheet.cell_value(i, j) # 取第i行第j列数据 
            row_content.append(cell)
        all_content.append(row_content)
    print(all_content)

    # 输出每一列数据
    all_content = []
    col_content = []
    for i in range(cols) :
        col_content = []
        for j in range(rows) :
            cell = sheet.cell_value(j, i) # 取第i列第j行数据
            col_content.append(cell)
        all_content.append(col_content)
    print(all_content)