打开excel表格
注意: 只能打开已经存在的表壳,不能用该方法创建表格
import openpyxl import os PATH = os.path.dirname(__file__) excel_list = os.listdir(PATH) print(excel_list[1]) # excel_1.xlsx wk1 = openpyxl.load_workbook(excel_list[1]) print(wk1.sheetnames) # ['Sheet1', 'Sheet2', 'Sheet3'] wk2 = openpyxl.load_workbook(filename='小明.xlsx') # 相对路径 只需要excel名就可以 print(wk2.sheetnames) # ['Sheet1', 'Sheet2', 'Sheet3'] wk3 = openpyxl.load_workbook(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx') # 可以是绝对路径 print(wk3.sheetnames) # ['Sheet1', 'Sheet2', 'Sheet3']通过sheet 名字获取表格
sheet = wk3['Sheet1'] print(sheet) # <Worksheet "Sheet1">创建sheet
wk3.create_sheet('Sheet4') wk3.save(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx')修改sheet名称
sheet = wk3['Sheet4'] sheet.title = '表格4'获取表格尺寸大小
size = sheet.dimensions print(size) # A1:D2 可以看出有多少行 即2行获取表格内某个各自的数据
sheet = wk3.active # 打开活跃的/唯一的表格 (属于简写) cell = sheet['A1'] # 获取A1格子 print(cell.value) # 获取格子中的数据获取某个格子的行数.列数,坐标
row = cell.row col = cell.column coo = cell.coordinate print(row, col, coo) # 1 1 A1用行数,列数定位格子
cell = sheet.cell(row = 1, column= 3) print(cell.value) # c2查看格子
1. 查看 A1 到 A4 的格子 cells = sheet["A1":"A4"] print(cells) # 元组套元组, 每个元组就是一个格子, 可以for循环value # ((<Cell 'Sheet1'.A1>,), (<Cell 'Sheet1'.A2>,), (<Cell 'Sheet1'.A3>,), (<Cell 'Sheet1'.A4>,)) 2. 查看 A 列的所有数据 cells = sheet['A'] print(cells) # (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>) 3. 查看第 5 行 cells = sheet[5] print(cells) # (<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.D5>) 4. 查看第 5 行到第 6 行 cells = sheet[5:6] print(cells) # ((<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.D5>), (<Cell 'Sheet1'.A6>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.C6>, <Cell 'Sheet1'.D6>)) 5. 第一行第一列到第三行第四列 for row in sheet.iter_rows(min_row=1, max_row=3, min_col=1, max_col=4): print(row) (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>) (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>) (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>) 6. 查看整个表格的所有行 列同理 for row in sheet.rows: print(row)向某个表格内输入内容
wk3 = openpyxl.load_workbook(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx') sheet = wk3.active sheet['A3'] = '哈哈' wk3.save(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx')将列表中的数据追加到表格中
sheet = wk3.active data = [ ['id', 'resolutionStatus(知识点解决状态)', 'color(颜色)', '2018年1月', '小东'], [1.0, '暂不处理', '紫色'], [2.0, '未解决待处理', '红色'], [3.0, '已验证', '绿色'], [4.0, '临时解决方案', '橙色'] ] for row in data: sheet.append(row) wk3.save(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx')插入删除行或列
sheet.insert_cols(idx=2, amount=2) # 默认在第二列的左边插入空白列, amount用来指定插入几列 sheet.insert_rows(idx=2, amount=3) # 默认在第二行的上边插入空白列, amount用来指定插入几列 sheet.delete_cols(idx=2, amount=3) # 默认删掉第二列, amount用来指定删除几列 sheet.delete_rows(idx=2, amount=3) # 默认删掉第二行, amount用来指定删除几行 wk3.save(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx')移动表格到指定位置
将面积为 c1到D4 的表格 向下移动2行, 向左移动2列 sheet.move_range('c1 : D4', row=2, cols=-2) wk3.save(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx')创建新的excel
wk4 = openpyxl.Workbook() # 该方法用来创建新的excel sheet = wk4.active sheet.title = '表格1' wk4.save(filename='新的表格名字.xlsx')如下面的内容:
views.py import openpyxl from openpyxl.writer.excel import save_virtual_workbook class ExeclAPIView(APIView): def get(request,): # 获取到所有组 groups = group.objects.all() # 定义execl表格头 head = [_("Name"), _("Creator"), _("Create At")] data_list = [] for grp in groups: create_at = tsstr_sec(grp.timestamp) if grp.timestamp else '' row = [grp.group_name, grp.creator_name, create_at] # [组名, 创造者名,创建时间] data_list.append(row) # 操作execl wb = write_xls('groups', head, data_list) # 主要内容 if not wb: messages.error(request, _(u'Failed to export Excel')) return HttpResponseRedirect(next) response = HttpResponse(content_type='application/ms-excel') response['Content-Disposition'] = 'attachment; filename=groups.xlsx' #wb.save(response) response.content = save_virtual_workbook(wb) return response # 操作execl的方法 def write_xls(sheet_name, head, data_list): """write listed data into excel""" try: wb = openpyxl.Workbook() ws = wb.get_active_sheet() except Exception as e: logger.error(e) return None ws.title = sheet_name row_num = 0 # write table head for col_num in xrange(len(head)): c = ws.cell(row = row_num + 1, column = col_num + 1) c.value = head[col_num] # write table data for row in data_list: row_num += 1 for col_num in xrange(len(row)): c = ws.cell(row = row_num + 1, column = col_num + 1) c.value = row[col_num] return wb结果:
将图片插入execl
import openpyxl from openpyxl.drawing.image import Image from openpyxl.writer.excel import save_virtual_workbook def get_product_info(obj): product_list = [] for i in obj: product_info = {} product_info['ertificatePath'] = '%s%s'%(settings.MEDIA_URL, i.ertificatePath) product_list.append(product_info) return product_list class GetExeclAPIView(APIView): authentication_classes = () permission_classes = () throttle_classes = () def get(self, request): product_query = Product.objects.all().order_by('-id') product_list = get_product_info(product_query) # 定义execl表格头 head = ["证书图片"] data_list = [] imgsize = (720 / 10, 1280 / 10) # 设置一个图像缩小的比例 for product in product_list: img_path = product.get("ertificatePath") img = '' if img_path: img = Image(img_path.split('/',2)[-1]) img.width, img.height = imgsize row = [ img ] data_list.append(row) # 操作execl wb = write_xls('ertificate', head, data_list) # 主要内容 if not wb: res = {'code': 4, 'msg': '导出失败.'} return HttpResponse(json.dumps(res), content_type=settings.CONTENT_TYPE, status=status.HTTP_200_OK) response = HttpResponse(content_type='application/ms-excel') response['Content-Disposition'] = 'attachment; filename=Ertificate.xlsx' # wb.save(response) response.content = save_virtual_workbook(wb) return response # 操作execl的方法 def write_xls(sheet_name, head, data_list): """write listed data into excel""" try: wb = openpyxl.Workbook() ws = wb.get_active_sheet() except Exception as e: return None ws.title = sheet_name row_num = 0 # write table head for col_num in xrange(len(head)): c = ws.cell(row=row_num + 1, column=col_num + 1) c.value = head[col_num] # write table data for row in data_list: row_num += 1 for col_num in xrange(len(row)): c = ws.cell(row=row_num + 1, column=col_num + 1) if isinstance(row[col_num], Image): ws.add_image(row[col_num], 'H%s' % (row_num + 1)) # 插入图片 ws.row_dimensions[row_num + 1].height = 1280 / 10 * 0.8 ws.row_dimensions[row_num + 1].width = 720 / 10 * 0.8 else: c.value = row[col_num] return wb读取execl中的图片
from io import BytesIO from django import forms from PIL import Image as _Image from openpyxl import load_workbook class AddProductForm(forms.Form): """ Form for importing users from XLSX file. """ file = forms.FileField() class ProductAPIView(APIView): authentication_classes = () permission_classes = () throttle_classes = () form = AddProductForm(request.POST, request.FILES) if form.is_valid(): content = request.FILES['file'].read() # print(content) if str(request.FILES['file']).split('.')[-1].lower() not in settings.UPLOAD_TYPE: res = {'code': 4, 'msg': '请使用.xlsx的文件'} return HttpResponse(json.dumps(res), status=status.HTTP_200_OK) try: fs = BytesIO(content) wb = load_workbook(filename=fs) except Exception as e: res = {'code': 5, 'msg': '服务器内部错误'} return HttpResponse(json.dumps(res), status=status.HTTP_200_OK) rows = wb.worksheets[0].rows # 获取sheet1的所有行内容(每一行是一个obj) # remove first row(head field). rows.next() imges = wb.worksheets[0]._images # 获取execl中所有图片 # print imges # [<openpyxl.drawing.image.Image object at 0x7f1eee535650>] # print type(imges[0]) # <class 'openpyxl.drawing.image.Image'> image_info = {} for i in imges: # i.anchor._from.row # 图片所在行 # i.anchor._from.col # 图片所在列 # i.ref # 图片对象 image_info[i.anchor._from.row]= i.ref print image_info # {1: <_io.BytesIO object at 0x7f120a0c7ef0>, 3: <_io.BytesIO object at 0x7f120a0c7f50>} im = _Image.open(image_info.get(1)) # 获取图片 <class 'PIL.JpegImagePlugin.JpegImageFile'> im.save('/home/demo/Attach/Ertificate/1212.jpg') # 保存到指定路径,保存时需要给图片命名 # im.show() # 展示图片
