xls3-1-字典python在保留原excel格式的前提下插入/修改数据
d:anaconda3python3python.exe -m pip install --upgrade pip
d:anaconda3python3python.exe -m pip install --upgrade pip
pip install xlrd
pip install xlwt
pip install xlutils
pip install properties
pip install xlwt
doskey /history
d:anaconda3python3python.exe -m pip install --upgrade pip
d:anaconda3python3python.exe -m pip install --upgrade pip
import xlrd,xlwt
from xlutils.copy import copy
import xlrd #导入模块
from xlutils.copy import copy #导入copy模块
#rb = xlrd.open_workbook('lll.xlsx') #打开weng.xls文件
file_path=r'D:
ote202003春雷log4mail801my200330aaa.xlsx'
file2path=r'D:
ote202003春雷log4mail801my200330aa1.xlsx'
read_file = xlrd.open_workbook(file_path,formatting_info=True)
rb = xlrd.open_workbook(file_path,formatting_info=True)
wb = copy(rb) #利用xlutils.copy下的copy函数复制
wb.save(file2path) #保存文件
import xlrd #导入模块
from xlutils.copy import copy #导入copy模块
rb = xlrd.open_workbook('lll.xlsx') #打开weng.xls文件
wb = copy(rb) #利用xlutils.copy下的copy函数复制
ws = wb.get_sheet(0) #获取表单0
ws1 = wb.get_sheet(1)
wb.save('sss.xls') #保存文件
ws.write(0, 0, 'changed!') #改变(0,0)的值
ws.write(8,0,label = '好的') #增加(8,0)的值
如何使用python在保留原excel格式的前提下插入/修改数据
一、需求分析:
统计的报表中需要每日查询当天数据并追加到原有的excel后面。
因为原始excel格式已经设定好,如果使用xlwt,仅仅指定设定我们要插入的单元格的格式,原始数据的格式会被初始化。
所以如果能够不改变原格式的前提下完成插入数据,将会省去较多的时间去调整excel格式。
该随笔实现的功能就是在不改变excel模板的格式下进行写入数据。
二、环境
python3、windows
三、实现代码
如果想要实现该功能需要用到python的两个库:xlrd和xlutils。
xlrd打开excel,但是打开的excel并不能直接写入数据,需要用xlutils主要是复制一份出来,实现后续的写入功能。
# -*- coding: utf-8 -*-
"""
Created on Fri Dec 29 10:29:35 2017
@author: Administrator
"""
import pandas as pd
import pymysql
import datetime
#使用前提导入以下两个库
import xlrd
import xlutils.copy
#用于SQL中限定时间
yestoday = datetime.date.today() - datetime.timedelta(days = 1)
yestoday = yestoday.strftime("%Y-%m-%d")
time_limit = str(yestoday) + ' 23:59:59'
#指定原始excel路径
filepath = 'C:\Users\Administrator\Desktop\python脚本\origin_excel.xls'
#使用pandas库传入该excel的数值仅仅是为了后续判断插入数据时应插入行是哪行
original_data = pd.read_excel(filepath,encoding='utf-8')
#rb打开该excel,formatting_info=True表示打开excel时并保存原有的格式
rb = xlrd.open_workbook(filepath,formatting_info=True)
#创建一个可写入的副本
wb = xlutils.copy.copy(rb)
#本文重点,该函数中定义:对于没有任何修改的单元格,保持原有格式。
def setOutCell(outSheet, col, row, value):
""" Change cell value without changing formatting. """
def _getOutCell(outSheet, colIndex, rowIndex):
""" HACK: Extract the internal xlwt cell representation. """
row = outSheet._Worksheet__rows.get(rowIndex)
if not row: return None
cell = row._Row__cells.get(colIndex)
return cell
# HACK to retain cell style.
previousCell = _getOutCell(outSheet, col, row)
# END HACK, PART I
outSheet.write(row, col, value)
# HACK, PART II
if previousCell:
newCell = _getOutCell(outSheet, col, row)
if newCell:
newCell.xf_idx = previousCell.xf_idx
#定义一个执行SQL的函数,因为数据需要连接数据库查询
def execude_sql(sql):
results = []
db = pymysql.connect('xx','xx','xx','xx',charset='utf8')
cur = db.cursor()
cur.execute(sql)
result = cur.fetchone()
results.append(result[0])
return results
sql1_1 = """SELECT COUNT(id) FROM cl_user cu WHERE cu.regist_time <= '"""+ time_limit +"""' AND cu.id IN (SELECT id FROM v_13bmd_regist)"""
sql1_2 = """xxx"""
sql1_3 = """xxx"""
#判断需要写入的行是哪一行
for row in range(0,len(original_data)):
if original_data.iloc[row,0] == yestoday:
print('当前需要修改的行为:' + row)print('正在查询:注册人数……')
regist_white = execude_sql(sql1_1)
regist_nature = execude_sql(sql1_2)
regist_all = execude_sql(sql1_3)
#写入excel数据
outSheet = wb.get_sheet(0)
setOutCell(outSheet,1,row,regist_white[0])
setOutCell(outSheet,2,row,regist_nature[0])
setOutCell(outSheet,3,row,regist_all[0])#保存excel
wb.save('output.xls')
print('finish')
四、参考来源
葡萄皮的数据空间:http://biotopiblog.sinaapp.com
https://stackoverflow.com/questions/3723793/preserving-styles-using-pythons-xlrd-xlwt-and-xlutils-copy
在Python操作Excel 的模块有 xlrd、xlwt、xlutils等。
xlrd:读取Excel文件数据
xlwt:写入Excel 数据,缺点是Excel格式无法复用,为了方便用户,写入的话,比较推荐xlutils模块,它可以可复制原excel格式
#coding=utf-8
import xlrd,xlwt
from xlutils.copy import copy
#读取文件
read_file = xlrd.open_workbook(file_path,formatting_info=True)
#参数注释:
#file_path:文件路径,包含文件的全名称
#formatting_info=True:保留Excel的原格式
#将文件复制到内存
write_data = copy(read_file)
#读取复制后文件的sheet1
write_save = write_data.get_sheet(0)
#写入数据
write_save.write(x,y,value)
#参数注释:
#x,y:写入目标格的位置坐标
#value:写入数据
#保存写入数据后的文件到原文件路径
write_data.save(self.file_path)
这里要注意的是: xlrd模块0.8版本后不支持以xlsx为后缀名文件,所以使用这个方法的时候,excel要用xls格式的,不然会导致保存的文件无法打开
python xlwt写excel格式控制 颜色、模式、编码、背景色
关于写excel的格式控制,比如颜色等等
1 import xlwt 2 from datetime import datetime 3 4 font0 = xlwt.Font() 5 font0.name = 'Times New Roman' 6 font0.colour_index = 2 7 font0.bold = True 8 9 style0 = xlwt.XFStyle() 10 style0.font = font0 11 12 style1 = xlwt.XFStyle() 13 style1.num_format_str = 'D-MMM-YY' 14 15 wb = xlwt.Workbook() 16 ws = wb.add_sheet('A Test Sheet') 17 18 ws.write(0, 0, 'Test', style0) 19 ws.write(1, 0, datetime.now(), style1) 20 ws.write(2, 0, 1) 21 ws.write(2, 1, 1) 22 ws.write(2, 2, xlwt.Formula("A3+B3")) 23 24 wb.save('example.xls')
Examples Generating Excel Documents Using Python’s xlwt
Here are some simple examples using Python’s xlwt library to dynamically generate Excel documents.
Please note a useful alternative may be ezodf, which allows you to generate ODS (Open Document Spreadsheet) files for LibreOffice / OpenOffice. You can check them out at:http://packages.python.org/ezodf/index.html
The Simplest Exampleimport xlwt
workbook = xlwt.Workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('My Worksheet')
worksheet.write(0, 0, label = 'Row 0, Column 0 Value')
workbook.save('Excel_Workbook.xls')
Formatting the Contents of a Cellimport xlwt
workbook = xlwt.Workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('My Worksheet')
font = xlwt.Font() # Create the Font
font.name = 'Times New Roman'
font.bold = True
font.underline = True
font.italic = True
style = xlwt.XFStyle() # Create the Style
style.font = font # Apply the Font to the Style
worksheet.write(0, 0, label = 'Unformatted value')
worksheet.write(1, 0, label = 'Formatted value', style) # Apply the Style to the Cell
workbook.save('Excel_Workbook.xls')
Attributes of the Font Objectfont.bold = True # May be: True, False
font.italic = True # May be: True, False
font.struck_out = True # May be: True, False
font.underline = xlwt.Font.UNDERLINE_SINGLE # May be: UNDERLINE_NONE, UNDERLINE_SINGLE, UNDERLINE_SINGLE_ACC, UNDERLINE_DOUBLE, UNDERLINE_DOUBLE_ACC
font.escapement = xlwt.Font.ESCAPEMENT_SUPERSCRIPT # May be: ESCAPEMENT_NONE, ESCAPEMENT_SUPERSCRIPT, ESCAPEMENT_SUBSCRIPT
font.family = xlwt.Font.FAMILY_ROMAN # May be: FAMILY_NONE, FAMILY_ROMAN, FAMILY_SWISS, FAMILY_MODERN, FAMILY_SCRIPT, FAMILY_DECORATIVE
font.charset = xlwt.Font.CHARSET_ANSI_LATIN # May be: CHARSET_ANSI_LATIN, CHARSET_SYS_DEFAULT, CHARSET_SYMBOL, CHARSET_APPLE_ROMAN, CHARSET_ANSI_JAP_SHIFT_JIS, CHARSET_ANSI_KOR_HANGUL, CHARSET_ANSI_KOR_JOHAB, CHARSET_ANSI_CHINESE_GBK, CHARSET_ANSI_CHINESE_BIG5, CHARSET_ANSI_GREEK, CHARSET_ANSI_TURKISH, CHARSET_ANSI_VIETNAMESE, CHARSET_ANSI_HEBREW, CHARSET_ANSI_ARABIC, CHARSET_ANSI_BALTIC, CHARSET_ANSI_CYRILLIC, CHARSET_ANSI_THAI, CHARSET_ANSI_LATIN_II, CHARSET_OEM_LATIN_I
font.colour_index = ?
font.get_biff_record = ?
font.height = 0x00C8 # C8 in Hex (in decimal) = 10 points in height.
font.name = ?
font.outline = ?
font.shadow = ?
Setting the Width of a Cellimport xltw
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, 'My Cell Contents')
worksheet.col(0).width = 3333 # 3333 = 1" (one inch).
workbook.save('Excel_Workbook.xls')
Entering a Date into a Cellimport xlwt
import datetime
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
style = xlwt.XFStyle()
style.num_format_str = 'M/D/YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
worksheet.write(0, 0, datetime.datetime.now(), style)
workbook.save('Excel_Workbook.xls')
Adding a Formula to a Cellimport xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, 5) # Outputs 5
worksheet.write(0, 1, 2) # Outputs 2
worksheet.write(1, 0, xlwt.Formula('A1*B1')) # Should output "10" (A1[5] * A2[2])
worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) # Should output "7" (A1[5] + A2[2])
workbook.save('Excel_Workbook.xls')
Adding a Hyperlink to a Cellimport xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.google.com";"Google")')) # Outputs the text "Google" linking to http://www.google.com
workbook.save('Excel_Workbook.xls')
Merging Columns and Rowsimport xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write_merge(0, 0, 0, 3, 'First Merge') # Merges row 0's columns 0 through 3.
font = xlwt.Font() # Create Font
font.bold = True # Set font to Bold
style = xlwt.XFStyle() # Create Style
style.font = font # Add Bold Font to Style
worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # Merges row 1 through 2's columns 0 through 3.
workbook.save('Excel_Workbook.xls')
Setting the Alignment for the Contents of a Cellimport xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() # Create Style
style.alignment = alignment # Add Alignment to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')
Adding Borders to a Cell# Please note: While I was able to find these constants within the source code, on my system (using LibreOffice,) I was only presented with a solid line, varying from thin to thick; no dotted or dashed lines.
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.DASHED # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
borders.right = xlwt.Borders.DASHED
borders.top = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DASHED
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style = xlwt.XFStyle() # Create Style
style.borders = borders # Add Borders to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')
Setting the Background Color of a Cellimport xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
style = xlwt.XFStyle() # Create the Pattern
style.pattern = pattern # Add Pattern to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')
TODO: Things Left to Document- Panes -- separate views which are always in view
- Border Colors (documented above, but not taking effect as it should)
- Border Widths (document above, but not working as expected)
- Protection
- Row Styles
- Zoom / Manification
- WS Props?
Source Code for reference available at: https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/
http://www.youlikeprogramming.com/2011/04/examples-generating-excel-documents-using-pythons-xlwt/
Java实现 LeetCode 23 合并K个排序链表
Java实现 LeetCode 23 合并K个排序链表
Java实现 LeetCode 23 合并K个排序链表
Java实现 LeetCode 22 括号生成
Java实现 LeetCode 22 括号生成
Java实现 LeetCode 22 括号生成
Java实现 LeetCode 21 合并两个有序链表
Java实现 LeetCode 21 合并两个有序链表
Java实现 LeetCode 21 合并两个有序链表