在Excel中想要写文件，大致要分为在：

原Excel文件基础上

  1. from openpyxl import load_workbook
  3. wb = load_workbook('files/p1.xlsx')
  4. sheet = wb.worksheets[0]
  6. # 找到单元格，并修改单元格的内容
  7. cell = sheet.cell(1, 1)
  8. cell.value = "新的开始"
  10. # 将excel文件保存到p2.xlsx文件中
  11. wb.save("files/p2.xlsx")

新创建Excel文件

  1. from openpyxl import workbook
  3. # 创建excel且默认会创建一个sheet（名称为Sheet）
  4. wb = workbook.Workbook()
  6. sheet = wb.worksheets[0] # 或 sheet = wb["Sheet"]
  8. # 找到单元格，并修改单元格的内容
  9. cell = sheet.cell(1, 1)
  10. cell.value = "新的开始"
  12. # 将excel文件保存到p2.xlsx文件中
  13. wb.save("files/p2.xlsx")

其它操作

  1. from openpyxl import workbook
  3. wb = workbook.Workbook() # Sheet
  5. # 1. 修改sheet名称
  6. """
  7. sheet = wb.worksheets[0]
  8. sheet.title = "数据集"
  9. wb.save("p2.xlsx")
  10. """
  12. # 2. 创建sheet并设置sheet颜色
  13. """
  14. sheet = wb.create_sheet("工作计划", 0)
  15. sheet.sheet_properties.tabColor = "1072BA"
  16. wb.save("p2.xlsx")
  17. """
  19. # 3. 默认打开的sheet
  20. """
  21. wb.active = 0
  22. wb.save("p2.xlsx")
  23. """
  25. # 4. 拷贝sheet
  26. """
  27. sheet = wb.create_sheet("工作计划")
  28. sheet.sheet_properties.tabColor = "1072BA"
  30. new_sheet = wb.copy_worksheet(wb["Sheet"])
  31. new_sheet.title = "新的计划"
  32. wb.save("p2.xlsx")
  33. """
  35. # 5.删除sheet
  36. """
  37. del wb["用户列表"]
  38. wb.save('files/p2.xlsx')
  39. """
  1. from openpyxl import load_workbook
  2. from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
  5. wb = load_workbook('files/p1.xlsx')
  7. sheet = wb.worksheets[1]
  9. # 1. 获取某个单元格，修改值
  10. """
  11. cell = sheet.cell(1, 1)
  12. cell.value = "开始"
  13. wb.save("p2.xlsx")
  14. """
  16. # 2.  获取某个单元格，修改值
  17. """
  18. sheet["B3"] = "Alex"
  19. wb.save("p2.xlsx")
  20. """
  22. # 3. 获取某些单元格，修改值
  23. """
  24. cell_list = sheet["B2":"C3"]
  25. for row in cell_list:
  26.     for cell in row:
  27.         cell.value = "新的值"
  28. wb.save("p2.xlsx")
  29. """
  31. # 4. 对齐方式
  32. """
  33. cell = sheet.cell(1, 1)
  35. # horizontal，水平方向对齐方式："general", "left", "center", "right", "fill", "justify", "centerContinuous", "distributed"
  36. # vertical，垂直方向对齐方式："top", "center", "bottom", "justify", "distributed"
  37. # text_rotation，旋转角度。
  38. # wrap_text，是否自动换行。
  39. cell.alignment = Alignment(horizontal='center', vertical='distributed', text_rotation=45, wrap_text=True)
  40. wb.save("p2.xlsx")
  41. """
  43. # 5. 边框
  44. # side的style有如下：dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin'
  45. """
  46. cell = sheet.cell(9, 2)
  47. cell.border = Border(
  48.     top=Side(style="thin", color="FFB6C1"), 
  49.     bottom=Side(style="dashed", color="FFB6C1"),
  50.     left=Side(style="dashed", color="FFB6C1"),
  51.     right=Side(style="dashed", color="9932CC"),
  52.     diagonal=Side(style="thin", color="483D8B"),  # 对角线
  53.     diagonalUp=True,  # 左下 ~ 右上
  54.     diagonalDown=True  # 左上 ~ 右下
  55. )
  56. wb.save("p2.xlsx")
  57. """
  59. # 6.字体
  60. """
  61. cell = sheet.cell(5, 1)
  62. cell.font = Font(name="微软雅黑", size=45, color="ff0000", underline="single")
  63. wb.save("p2.xlsx")
  64. """
  66. # 7.背景色
  67. """
  68. cell = sheet.cell(5, 3)
  69. cell.fill = PatternFill("solid", fgColor="99ccff")
  70. wb.save("p2.xlsx")
  71. """
  73. # 8.渐变背景色
  74. """
  75. cell = sheet.cell(5, 5)
  76. cell.fill = GradientFill("linear", stop=("FFFFFF", "99ccff", "000000"))
  77. wb.save("p2.xlsx")
  78. """
  80. # 9.宽高（索引从1开始）
  81. """
  82. sheet.row_dimensions[1].height = 50
  83. sheet.column_dimensions["E"].width = 100
  84. wb.save("p2.xlsx")
  85. """
  87. # 10.合并单元格
  88. """
  89. sheet.merge_cells("B2:D8")
  90. sheet.merge_cells(start_row=15, start_column=3, end_row=18, end_column=8)
  91. wb.save("p2.xlsx")
  92. """
  93. """
  94. sheet.unmerge_cells("B2:D8")
  95. wb.save("p2.xlsx")
  96. """
  98. # 11.写入公式
  99. """
  100. sheet = wb.worksheets[3]
  101. sheet["D1"] = "合计"
  102. sheet["D2"] = "=B2*C2"
  103. wb.save("p2.xlsx")
  104. """
  105. """
  106. sheet = wb.worksheets[3]
  107. sheet["D3"] = "=SUM(B3,C3)"
  108. wb.save("p2.xlsx")
  109. """
  111. # 12.删除
  112. """
  113. # idx，要删除的索引位置
  114. # amount，从索引位置开始要删除的个数（默认为1）
  115. sheet.delete_rows(idx=1, amount=20)
  116. sheet.delete_cols(idx=1, amount=3)
  117. wb.save("p2.xlsx")
  118. """
  120. # 13.插入
  121. """
  122. sheet.insert_rows(idx=5, amount=10)
  123. sheet.insert_cols(idx=3, amount=2)
  124. wb.save("p2.xlsx")
  125. """
  127. # 14.循环写内容
  128. """
  129. sheet = wb["Sheet"]
  130. cell_range = sheet['A1:C2']
  131. for row in cell_range:
  132.     for cell in row:
  133.         cell.value = "xx"
  135. for row in sheet.iter_rows(min_row=5, min_col=1, max_col=7, max_row=10):
  136.     for cell in row:
  137.         cell.value = "oo"
  138. wb.save("p2.xlsx")
  139. """
  141. # 15.移动
  142. """
  143. # 将H2:J10范围的数据，向右移动15个位置、向上移动1个位置
  144. sheet.move_range("H2:J10",rows=1, cols=15)
  145. wb.save("p2.xlsx")
  146. """
  147. """
  148. sheet = wb.worksheets[3]
  149. sheet["D1"] = "合计"
  150. sheet["D2"] = "=B2*C2"
  151. sheet["D3"] = "=SUM(B3,C3)"
  152. sheet.move_range("B1:D3",cols=10, translate=True) # 自动翻译公式
  153. wb.save("p2.xlsx")
  154. """
  156. # 16.打印区域
  157. """
  158. sheet.print_area = "A1:D200"
  159. wb.save("p2.xlsx")
  160. """
  162. # 17.打印时，每个页面的固定表头
  163. """
  164. sheet.print_title_cols = "A:D"
  165. sheet.print_title_rows = "1:3"
  166. wb.save("p2.xlsx")
  167. """