excel相关操作（写入）

写Excel

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

• 原Excel文件基础上写内容。

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

• 在原excel文件基础上追加内容

  from openpyxl import load_workbook
  
  wb = load_workbook('files/p1.xlsx')
  sheet = wb.worksheets[0]
  
  sheet.append([1,2,3])	# 按照行增加，一行有几列就增加几个
  wb.save('files/p1.xlsx')

• 新创建Excel文件写内容

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

• 在了解了如何读取Excel和创建Excel之后，后续对于Excel中的sheet和cell操作基本上都相同

• from openpyxl import workbook
  
  wb = workbook.Workbook() # Sheet
  
  # 1. 修改sheet名称
  """
  sheet = wb.worksheets[0]
  sheet.title = "数据集"
  wb.save("p2.xlsx")
  """
  
  # 2. 创建sheet并设置sheet颜色
  """
  sheet = wb.create_sheet("工作计划", 0)
  sheet.sheet_properties.tabColor = "1072BA"
  wb.save("p2.xlsx")
  """
  
  # 3. 默认打开的sheet
  
  wb.active = 0
  wb.save("p2.xlsx")
  
  # 4. 拷贝sheet
  
  sheet = wb.create_sheet("工作计划")
  sheet.sheet_properties.tabColor = "1072BA"
  
  new_sheet = wb.copy_worksheet(wb["Sheet"])
  new_sheet.title = "新的计划"
  wb.save("p2.xlsx")
  
  # 5.删除sheet
  
  del wb["用户列表"]
  wb.save('files/p2.xlsx')
  from openpyxl import load_workbook
  from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
  
  
  wb = load_workbook('files/p1.xlsx')
  
  sheet = wb.worksheets[1]
  
  # 1. 获取某个单元格，修改值
  """
  cell = sheet.cell(1, 1)
  cell.value = "开始"
  wb.save("p2.xlsx")
  """
  
  # 2.  获取某个单元格，修改值
  """
  sheet["B3"] = "Alex"
  wb.save("p2.xlsx")
  """
  
  # 3. 获取某些单元格，修改值
  """
  cell_list = sheet["B2":"C3"]
  for row in cell_list:
      for cell in row:
          cell.value = "新的值"
  wb.save("p2.xlsx")
  """
  
  # 4. 对齐方式
  """
  cell = sheet.cell(1, 1)
  
  # horizontal，水平方向对齐方式："general", "left", "center", "right", "fill", "justify", "centerContinuous", "distributed"
  # vertical，垂直方向对齐方式："top", "center", "bottom", "justify", "distributed"
  # text_rotation，旋转角度。
  # wrap_text，是否自动换行。
  cell.alignment = Alignment(horizontal='center', vertical='distributed', text_rotation=45, wrap_text=True)
  wb.save("p2.xlsx")
  """
  
  # 5. 边框
  # side的style有如下：dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin'
  """
  cell = sheet.cell(9, 2)
  cell.border = Border(
      top=Side(style="thin", color="FFB6C1"), 
      bottom=Side(style="dashed", color="FFB6C1"),
      left=Side(style="dashed", color="FFB6C1"),
      right=Side(style="dashed", color="9932CC"),
      diagonal=Side(style="thin", color="483D8B"),  # 对角线
      diagonalUp=True,  # 左下 ~ 右上
      diagonalDown=True  # 左上 ~ 右下
  )
  wb.save("p2.xlsx")
  """
  
  # 6.字体
  """
  cell = sheet.cell(5, 1)
  cell.font = Font(name="微软雅黑", size=45, color="ff0000", underline="single")
  wb.save("p2.xlsx")
  """
  
  # 7.背景色
  """
  cell = sheet.cell(5, 3)
  cell.fill = PatternFill("solid", fgColor="99ccff")
  wb.save("p2.xlsx")
  """
  
  # 8.渐变背景色
  """
  cell = sheet.cell(5, 5)
  cell.fill = GradientFill("linear", stop=("FFFFFF", "99ccff", "000000"))
  wb.save("p2.xlsx")
  """
  
  # 9.宽高（索引从1开始）
  """
  sheet.row_dimensions[1].height = 50
  sheet.column_dimensions["E"].width = 100
  wb.save("p2.xlsx")
  """
  
  # 10.合并单元格
  """
  sheet.merge_cells("B2:D8")
  sheet.merge_cells(start_row=15, start_column=3, end_row=18, end_column=8)
  wb.save("p2.xlsx")
  """
  """
  sheet.unmerge_cells("B2:D8")
  wb.save("p2.xlsx")
  """
  
  # 11.写入公式
  """
  sheet = wb.worksheets[3]
  sheet["D1"] = "合计"
  sheet["D2"] = "=B2*C2"
  wb.save("p2.xlsx")
  """
  """
  sheet = wb.worksheets[3]
  sheet["D3"] = "=SUM(B3,C3)"
  wb.save("p2.xlsx")
  """
  
  # 12.删除
  """
  # idx，要删除的索引位置
  # amount，从索引位置开始要删除的个数（默认为1）
  sheet.delete_rows(idx=1, amount=20)
  sheet.delete_cols(idx=1, amount=3)
  wb.save("p2.xlsx")
  """
  
  # 13.插入
  """
  sheet.insert_rows(idx=5, amount=10)
  sheet.insert_cols(idx=3, amount=2)
  wb.save("p2.xlsx")
  """
  
  # 14.循环写内容
  """
  sheet = wb["Sheet"]
  cell_range = sheet['A1:C2']
  for row in cell_range:
      for cell in row:
          cell.value = "xx"
  
  for row in sheet.iter_rows(min_row=5, min_col=1, max_col=7, max_row=10):
      for cell in row:
          cell.value = "oo"
  wb.save("p2.xlsx")
  """
  
  # 15.移动
  """
  # 将H2:J10范围的数据，向右移动15个位置、向上移动1个位置
  sheet.move_range("H2:J10",rows=1, cols=15)
  wb.save("p2.xlsx")
  """
  """
  sheet = wb.worksheets[3]
  sheet["D1"] = "合计"
  sheet["D2"] = "=B2*C2"
  sheet["D3"] = "=SUM(B3,C3)"
  sheet.move_range("B1:D3",cols=10, translate=True) # 自动翻译公式
  wb.save("p2.xlsx")
  """
  
  # 16.打印区域
  """
  sheet.print_area = "A1:D200"
  wb.save("p2.xlsx")
  """
  
  # 17.打印时，每个页面的固定表头
  """
  sheet.print_title_cols = "A:D"
  sheet.print_title_rows = "1:3"
  wb.save("p2.xlsx")
  """