使用Python操作excel文件

依赖环境

读取excel表里的数据，需要依赖的包是xlrd，首先需要安装xlrd包

pip3 install xlrd

简单的常用命令

#coding=utf-8
import xlrd
'''
文件路径比较重要，要以这种方式去写文件路径不用
'''
file_path = r'd:/功率因数.xlsx'
#读取的文件路径
file_path = file_path.decode('utf-8')
#文件中的中文转码
data = xlrd.open_workbook(file_path)
#获取数据
table = data.sheet_by_name('历史数据')
#获取sheet
nrows = table.nrows
#获取总行数
ncols = table.ncols
#获取总列数

table.row_values(i)
#获取一行的数值
table.col_values(i)
#获取一列的数值

#获取一个单元格的数值
cell_value = table.cell(a,b).value

 本地excel文件测试版本

#-*- coding=utf-8 -*-
import xlrd
def open_excel(file= 'file.xls'):
    try:
        data = xlrd.open_workbook(file)
        return data
    except Exception as e:
        print(str(e))

def excel_table_byname(file= u'D:\\test\\学信网zh.xlsx',colnameindex=0,by_name=u'Sheet1'):#修改自己路径
     data = open_excel(file)
     table = data.sheet_by_name(by_name) #获得表格
     nrows = table.nrows  # 拿到总共行数
     colnames = table.row_values(colnameindex)  # 某一行数据 ['姓名', '用户名', '联系方式', '密码']
     list = []
     for rownum in range(1, nrows): #也就是从Excel第二行开始，第一行表头不算
         row = table.row_values(rownum)
         if row:
             app = {}
             for i in range(len(colnames)):
                 app[colnames[i]] = row[i] #表头与数据对应
             list.append(app)
     return list

def main():
    tables = excel_table_byname()
    for row in tables:
       print(row)
if __name__ =="__main__":
    main()

读取excel文件录入数据库

代码示例

# coding: utf-8
from __future__ import unicode_literals
from questionnaire.models import Paper, QuestionnaireTag, QuestionnaireAnswer, ChoiceQuestion, QuestionnaireQuota, \
    SubjectQuestion
import xlrd
import os
from education import settings


def choicequestion_init():
    # 遍历文件夹
    file_package = os.listdir(os.path.join(os.path.join(settings.BASE_DIR, 'education', 'init', 'choicequestion')))
    for file in file_package:
        if not os.path.isdir(file):  # 判断是否是文件夹，不是文件夹才打开
            workbook = xlrd.open_workbook(os.path.join(
                os.path.join(settings.BASE_DIR, 'education', 'init', 'choicequestion') + "/" + file))  # 打开文件
            sheet_names = workbook.sheet_names()
            # 循环Excel文件的所有行
            for sheet in sheet_names:
                sheet = workbook.sheet_by_name(sheet)
                if sheet.name:
                    tag_obj = QuestionnaireTag.objects.create(name=sheet.name)
                # 创建试卷
                paper_obj = Paper()
                paper_obj.name = tag_obj.name
                paper_obj.questionnairetag = tag_obj
                paper_obj.save()

                last_obj = None
                for index, row in enumerate(sheet.get_rows()):
                    if index == 0:
                        continue
                    if row[0].value:
                        if not QuestionnaireQuota.objects.filter(name=row[0].value):
                            parent_obj = QuestionnaireQuota.objects.create(name=row[0].value)
                        if not QuestionnaireQuota.objects.filter(name=row[1].value):
                            children_obj = QuestionnaireQuota.objects.create(name=row[1].value, parent=parent_obj)
                            last_obj = parent_obj
                    else:
                        if not QuestionnaireQuota.objects.filter(name=row[0].value):
                            children_obj = QuestionnaireQuota.objects.create(name=row[1].value, parent=last_obj)

                    type_name = ""
                    if row[3].value == "单选":
                        type_name = "single"
                    elif row[3].value == "多选":
                        type_name = "multiple"

                    is_textfield = False
                    if row[4].value == "否":
                        is_textfield = False
                    elif row[4].value == "是":
                        is_textfield = True

                    ans = {}
                    num = 65
                    for i in range(5, len(row)):
                        ans["option_%s" % (chr(num))] = row[i].value
                        num += 1

                    question_obj = ChoiceQuestion.objects.create(stem=row[2].value, type=type_name,
                                                                 is_textfield=is_textfield, **ans)

                    question_obj.questionnairequotas.add(children_obj)
                    question_obj.questionnairetags.add(tag_obj)
                    question_obj.save()
                    paper_obj.choicequestions.add(question_obj)
                    paper_obj.save()