openpyxl教程--人世间的快乐就是每天赚点积分好下载喜欢的东西

Openpyxl教程（2020年9月2日）

开始了。

第一步：安装环境

python环境（anaconda继承环境更傻瓜式一点）

安装openpyxl

conda install openpyxl

第二步：了解excel的基本环境

行（row）列（column）格子（cell）工作表（sheet）

第三步：

基本操作

进入 jupyter环境

juoyter notebook

快速补齐快捷键–Tab

打开Excel表格并获取表格名称

from openpyxl import load_workbook workbook = load_workbook(filename = "练习2.xlsx") workbook.sheetnames

通过sheets名获得内容

sheet1 = workbook["Sheet1"] print(sheet1)

）获取表格的尺寸大小 （几行列数据 几行列数据 )

sheet1.dimensions

获取表格内某个格子的数据

方法1：

sheet2 = workbook.active print(sheet2) cell1 = sheet2["A2"] print(cell1.value)

方法2：

cell1 = sheet.cell(row = 1,column = 1)

获取某个变量的值，行，列，sheet，坐标

print(cell1.value, cell1.row, cell1.column, cell1.coordinate) print(cell2.value, cell2.row, cell2.column, cell2.coordinate)

获取多个格子的数据

# 获取A1:C2区域的值 cell = sheet["A1:H12"] print(cell) for i in cell: for j in i: print(j.value) sheet["A"] --- 获取A列的数据 sheet["A:C"] --- 获取A,B,C三列的数据 sheet[5] --- 只获取第5行的数据

按行读取和按列读取

# 按行获取值 for i in sheet2.iter_rows(min_row=2, max_row=5, min_col=1, max_col=2): for j in i: print(j.value) # 按列获取值 for i in sheet2.iter_cols(min_row=2, max_row=5, min_col=1, max_col=2): for j in i: print(j.value) #查询所有的表格 for i in sheet2.rows(): print(i.value)

向EXCEL写入数据

功能1：修改表格中的内容

#方法1 sheet1["A1"] = "hello world" #方法2 cellA1 = sheet1["A1"] cellA1.value = "hello world" """ 注意：我们将“A1”单元格的数据改为了“哈喽”， 并另存为了“哈喽.xlsx”文件。 如果我们保存的时候，不修改表名，相当于直接修改源文件； """

功能2：.append()向表格中插入行数据 (很有用 )

注意回顾一下 tuple（元组，不可更改的集合），list（有序，可重复集合），set（无序，不可重复集合），dist（字典，键值对）

#新建tuple---（）小括号 tuple1 = （”a“，”b“，”c“） tuple2 = tuple(list1) tuple3 = tuple("hello world") #新建list----[]中括号 list1 = [”a“，”b“，”c“] #新建set,必须由set函数生成或者{}----{}大括号 set1 = set([”a“，”b“，”c“]) set2 = {”a“，”b“，”c“} #新建字典dist #创建一个空字典 empty_dict = dict() print(empty_dict) #用**kwargs可变参数传入关键字创建字典 a = dict(one=1,two=2,three=3) print(a) #传入可迭代对象 b = dict(zip(['one','two','three'],[1,2,3])) print(list(zip(['one','two','three'],[1,2,3]))) print(b) #传入可迭代对象 c = dict([('one', 1), ('two', 2), ('three', 3)]) print(c) c1 = dict([('one', 1), ('two', 2), ('three', 3),('three', 4),('three', 5)]) print(c1)#如果键有重复，其值为最后重复项的值。 #传入映射对象，字典创建字典 d = dict({'one': 1, 'two': 2, 'three': 3})

功能3：使用excel函数公式（***很重要***）

for i in range(2,16): sheet["D{}".format(i)] = '=IF(RIGHT(C{},2)="cm",C{},SUBSTITUTE(C{},"m","")*100&"cm")'.format(i,i,i)

EXCEL里常用的公式

frozenset({'ISLOGICAL', 'YIELDDISC', 'DAYS360', 'CUBEKPIMEMBER', 'IMSUB', 'MATCH', 'CUBEMEMBER', 'IMSUM', 'ODDFYIELD', 'CUBEMEMBERPROPERTY', 'TRUNC', 'IMEXP', 'DSTDEV', 'CUBEVALUE', 'COS', 'COMBIN', 'SECOND', 'SQRTPI', 'COSH', 'ISNONTEXT', 'MODE', 'QUARTILE', 'DPRODUCT', 'DATEVALUE', 'NORMSDIST', 'DMIN', 'HARMEAN', 'ISERROR', 'LOGINV', 'AVERAGEIFS', 'GAMMALN', 'BETADIST', 'DGET', 'TODAY', 'NEGBINOMDIST', 'ABS', 'AND', 'LN', 'SIGN', 'MAX', 'SUMX2MY2', 'SEARCHB', 'HYPGEOMDIST', 'RIGHTB', 'ODDLPRICE', 'PERCENTRANK', 'DAY', 'RAND', 'DOLLAR', 'BETAINV', 'PHONETIC', 'ODDLYIELD', 'BINOMDIST', 'EDATE', 'TEXT', 'GCD', 'EXP', 'COUPDAYS', 'TBILLYIELD', 'CRITBINOM', 'YIELDMAT', 'DAVERAGE', 'IRR', 'PV', 'SIN', 'RANK', 'NETWORKDAYS', 'ATAN2', 'MROUND', 'GROWTH', 'INFO', 'DSTDEVP', 'COUNTIF', 'LOGNORMDIST', 'FV', 'CUBERANKEDMEMBER', 'PROB', 'IMREAL', 'HEX2BIN', 'SUMXMY2', 'ISNUMBER', 'IMABS', 'GETPIVOTDATA', 'BIN2DEC', 'NOMINAL', 'COUNTBLANK', 'ROMAN', 'MIRR', 'AREAS', 'GESTEP', 'SUMX2PY2', 'DMAX', 'JIS', 'PRICEMAT', 'TTEST', 'DELTA', 'SUBSTITUTE', 'COLUMN', 'EFFECT', 'REPLACE', 'QUOTIENT', 'STDEVP', 'ATANH', 'TIMEVALUE', 'RADIANS', 'BESSELK', 'COUNT', 'PMT', 'IFERROR', 'MDETERM', 'GAMMAINV', 'CELL', 'LEFT', 'NETWORKDAYS.INTL', 'CUBESET', 'MULTINOMIAL', 'DB', 'VARPA', 'IF', 'FINDB', 'CONVERT', 'DURATION', 'MINUTE', 'SYD', 'COUPPCD', 'MIN', 'VAR', 'TRIM', 'DEC2OCT', 'OFFSET', 'ERF', 'RANDBETWEEN', 'MINA', 'FLOOR', 'HLOOKUP', 'AVERAGE', 'VDB', 'IMPOWER', 'DDB', 'RIGHT', 'LOGEST', 'PROPER', 'SINH', 'CHOOSE', 'MDURATION', 'INTRATE', 'PPMT', 'LCM', 'INT', 'NORMDIST', 'LOWER', 'MINVERSE', 'N', 'SMALL', 'REPLACEB', 'CONFIDENCE', 'IMCOS', 'STDEV STDEVA', 'ECMA.CEILING', 'ASINH', 'FISHERINV', 'OCT2DEC', 'INDEX', 'SUMIF', 'AVERAGEIF', 'AMORLINC', 'CORREL', 'ERFC', 'FISHER', 'OCT2HEX', 'TINV', 'PERCENTILE', 'BIN2OCT', 'ISREF', 'WEEKDAY', 'POWER', 'XIRR', 'TAN', 'COUPDAYSNC', 'RECEIVED', 'ACCRINT', 'T', 'KURT', 'MEDIAN', 'IMLOG10', 'POISSON', 'LEFTB', 'LOOKUP', 'COLUMNS', 'INTERCEPT', 'ERROR.TYPE', 'EVEN', 'IMCONJUGATE', 'CEILING', 'VLOOKUP', 'TBILLPRICE', 'IMAGINARY', 'PRICE', 'FREQUENCY', 'CHAR', 'NPV', 'VARP', 'IMARGUMENT', 'COMPLEX', 'SUMIFS', 'ROW', 'HEX2OCT', 'WORKDAY ', 'PRICEDISC', 'CHIINV', 'DATE', 'NOT', 'PI', 'DOLLARDE', 'RATE', 'AMORDEGRC', 'SLOPE', 'ACOSH', 'FIND', 'BESSELY', 'WEIBULL', 'NORMINV', 'ODD', 'IMSQRT', 'TANH', 'SERIESSUM', 'ATAN', 'COUNTA', 'FORECAST', 'FIXED', 'TIME', 'XNPV', 'NOW', 'FDIST', 'VALUE', 'BIN2HEX', 'PRODUCT', 'IMSIN', 'CHITEST', 'STANDARDIZE', 'SKEW', 'SUMSQ', 'COUPNCD', 'MMULT', 'LENB', 'COUNTIFS', 'DEVSQ', 'DSUM', 'EOMONTH', 'ISPMT', 'BESSELJ', 'TRIMMEAN', 'DATEDIF', 'PERMUT', 'ODDFPRICE', 'LOG10', 'VARA', 'EXACT', 'INDIRECT', 'LEN', 'SUMPRODUCT', 'SUBTOTAL', 'DOLLARFR', 'ISEVEN', 'DEC2BIN', 'ISO.CEILING', 'WORKDAY.INTL', 'DISC', 'LARGE', 'RTD', 'FALSE', 'FINV', 'YEAR', 'IMPRODUCT', 'IPMT', 'REPT', 'SUM', 'DEGREES', 'MIDB', 'UPPER', 'MOD', 'ROUNDDOWN', 'CODE', 'DEC2HEX', 'MAXA', 'CUBESETCOUNT', 'COVAR', 'FACTDOUBLE', 'HEX2DEC', 'GEOMEAN', 'ISTEXT', 'ACCRINTM', 'RSQ', 'ISNA', 'AVEDEV', 'LOG', 'TDIST', 'WEEKNUM', 'CLEAN', 'IMLN', 'CHIDIST', 'FTEST', 'PEARSON', 'SQRT', 'DCOUNTA', 'NPER', 'AVERAGEA', 'YEARFRAC', 'OCT2BIN', 'MONTH', 'ZTEST', 'STDEVPA STEYX', 'FACT', 'ASIN', 'IMLOG2', 'SLN', 'BAHTTEXT', 'ROWS', 'SEARCH', 'YIELD', 'COUPDAYBS', 'ASC', 'ACOS', 'FVSCHEDULE', 'ISBLANK', 'TBILLEQ', 'TRUE ADDRESS', 'MID', 'HYPERLINK', 'CUMPRINC', 'DVARP', 'DCOUNT', 'NA', 'TRANSPOSE', 'IMDIV', 'ISERR', 'OR', 'ISODD', 'CUMIPMT', 'EXPONDIST', 'TREND', 'GAMMADIST', 'LINEST', 'HOUR', 'ROUND', 'TYPE', 'BESSELI', 'ROUNDUP', 'CONCATENATE', 'COUPNUM', 'DVAR', 'NORMSINV'})

插入行和插入列

sheet2.insert_col(idx = "数字编号",amount="要插入的列数") sheet2.insert_rows(idx= "数字编号"，amount="要插入的行数")

删除行和列

* .delete_rows(idx=数字编号, amount=要删除的行数) * .delete_cols(idx=数字编号, amount=要删除的列数)

移动格子

#move_range() sheet1.move_range("C2:D4",rows=2,cols=-1)

创建新的sheet表格

workbook.create_sheet("新建一个sheet")

删除某个sheet表

workbook.remove(sheet1)

把sheet复制到另一张excel里去

workbook.copy_worksheet(sheet1)

修改sheet名

sheet1.title = "新的的sheet名"

创建新的 excel 表格文件 表

from openpyxl import Workbook workbook = Workbook() sheet = workbook.active sheet.title = "表格1" workbook.save(filename = "新建的excel表格")

.freeze_panes：冻结窗口

这个也不常用，只是看到了，顺便提一下

下面就比较经典了

给表格添加筛选器

workbook = load_workbook(filename = "花园.xlsx") sheet = workbook.active print(sheet) sheet.auto_filter.ref = sheet["A1"] workbook.save(filename = "花园.xlsx")

批量修改字体样式

cell = sheet["A1"] font = Font(name="微软雅黑",size=20,bold=True,italic=True,color="FF0000") cell.font = font workbook.save(filename = "花园.xlsx")

获取表格中格子的字体样式

font = cell.font

设置对齐样式

Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)水平对齐：‘distributed’，‘justify’，‘center’，‘leftfill’， ‘centerContinuous’，‘right，‘general’；垂直对齐：‘bottom’，‘distributed’，‘justify’，‘center’，‘top’； cell.alignment = alignment

设置边框样式

Side(style=边线样式，color=边线颜色)Border(left=左边线样式，right=右边线样式，top=上边线样式，bottom=下边线样式)style参数的种类： 'double, ‘mediumDashDotDot’, ‘slantDashDot’, ‘dashDotDot’,‘dotted’,‘hair’, 'mediumDashed, ‘dashed’, ‘dashDot’, ‘thin’, ‘mediumDashDot’,‘medium’, ‘thick’ side1 = Side(style="thin",color="FF0000") side2 = Side(style="thick",color="FFFF0000") border = Border(left=side1,right=side1,top=side2,bottom=side2) cell.border = border

设置填充样式

PatternFill（fill_type）

GradeientFill()

设置行高和列宽

sheet = workbook.active # 设置第1行的高度 sheet.row_dimensions[1].height = 50 # 设置B列的宽度 sheet.column_dimensions["B"].width = 20

合并单元格

sheet.merge_cells("C1:D2") sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=3) * .unmerge_cells(待合并的格子编号) * .unmerge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)