操作excel的方法，使用python将excel内容显示到网页中

#方法一

import openpyxl

wb = openpyxl.load_workbook('static/1.xlsx')

sheet = wb.get_sheet_by_name('Sheet1')

test_data=[]

for rowin sheet.rows:

    ls=[]

    for cin row:    

        ls.append(c.value)

    test_data.append(ls)

return render(request,'books/show_excel.html', {'test_data': test_data})

方法2：

from pandasimport read_csv,read_excel

data_set = read_excel('static/1.xlsx',header=None,skiprows=None,sheetname=0)

data = data_set.values[:, :]

test_data = []

for linein data:

ls = []

for jin line:

ls.append(j)

test_data.append(ls)

return render(request,'books/show_excel.html', {'test_data': test_data})

方法3：

import openpyxl

wb = openpyxl.load_workbook('static/1.xlsx')

sheet = wb.get_sheet_by_name('Sheet1')

text='''

.zhouri {

background: #688ac2;

}

'''

html=' 批量导入客户数据' +text+''

html =html+''方法三数据显示

test_data=[]

for rowin sheet.rows:

if row[1].value==u'星期日' or row[1].value==u'星期六':

html = html +''

    else:

html = html +''

    ls=[]

for celin row:

html = html +'{}'.format(cel.value)

# html = html + '{}'.format(cel.font.color.rgb,cel.value)

    html = html +''

html = html +'' +''

return HttpResponse(html)

方法四：

import os

from xlrdimport open_workbook, formatting

from datetimeimport datetime, date

from xlrdimport xldate_as_tuple

num =0

filename ='static/1.xlsx'

ext = filename[-4:]

# ext = filename.decode('utf-8')[-4:].encode('utf-8')

if ext =='.pdf':

return ''

filepath = os.path.join(settings.BASE_DIR, filename)

print(filepath)

wb = open_workbook(filepath)

sheet = wb.sheet_by_index(0)

text ='''

.zhouri {

background: #688ac2;

}

'''

html =' 批量导入客户数据' + text +''

html = html +''方法四测试数据显示

mergedcells = {}

mergedsapn = {}

mergedcellvalue = {}

for crangein sheet.merged_cells:

rlo, rhi, clo, chi = crange

print(rlo, rhi, clo, chi,'==========')# 2，4，0，1#2，4，1，2

    for rowxin range(rlo, rhi):# 开始的行，结束的行2，4

        for colxin range(clo, chi):# 开始的列，结束的列0，1#1，2

            mergedcells[(rowx, colx)] =False  # 2，0#取消单元格的合并

            value = (sheet.cell_value(rowx, colx))

if str(value).strip() !='':

mergedcellvalue[(rlo, clo)] = value# 2，0

    mergedcells[(rlo, clo)] =True

    mergedsapn[(rlo, clo)] = (rhi - rlo, chi - clo)

for rowin range(sheet.nrows):

flag=False

    if sheet.cell_value(row,1)==u'星期日' or sheet.cell_value(row,1)==u'星期六'or sheet.cell_value(row,1)=='':

html = html +''

        flag=True

    else:

html = html +''

    for colin range(sheet.ncols):

if (row, col)in mergedcells:

if mergedcells[(row, col)] ==True:

rspan, cspan = mergedsapn[(row, col)]

value =''

                if (row, col)in mergedcellvalue:

value = mergedcellvalue[(row, col)]

if type(value).__name__ =="float":

date_value = xldate_as_tuple(sheet.cell_value(row, col),0)

value = date(*date_value[:3]).strftime('%Y/%m/%d')

if flag:

html = html +'%s' % (rspan, cspan, value)

else:

html = html +'%s' % (rspan, cspan, value)

else:

value = sheet.cell_value(row, col)

if type(value).__name__ =="float":

date_value = xldate_as_tuple(sheet.cell_value(row, col),0)

value = date(*date_value[:3]).strftime('%Y/%m/%d')

# value = str(date[0])+'-'+str(date[1])+'-'+str(date[2])+'-'+str(date[3])

# print(value,'')

            if flag:

html = html +'' +str(value) +''

            else:

html = html +'' +str(value) +''

    html = html +''

html = html +'' +''

return HttpResponse(html)