如何使用XlsxWriter根据另一个工作表中的相应单元格值格式化一个工作表中的所有单元格？

1 回答

• 3

  第一步是在Excel中计算条件格式，然后将其传输到XlsxWriter .

  像下面这样的东西应该工作：

  import pandas as pd
  
  # Create some Pandas dataframes from some data.
  df1 = pd.DataFrame([[ 1,   3,   1 ], [ 0,   4,   2 ]])
  df2 = pd.DataFrame([['a', 'd', 'b'], ['b', 'a', 'a']])
  
  # Create a Pandas Excel writer using XlsxWriter as the engine.
  writer = pd.ExcelWriter('pandas_xlsxwriter.xlsx', engine='xlsxwriter')
  
  # Write each dataframe to a different worksheet.
  df1.to_excel(writer, sheet_name='Sheet1', header=False, index=False)
  df2.to_excel(writer, sheet_name='Sheet2', header=False, index=False)
  
  # Get the xlsxwriter workbook and worksheet objects.
  workbook  = writer.book
  worksheet = writer.sheets['Sheet1']
  
  # Create a format for the conditional format.
  condition_format = workbook.add_format({'bg_color': '#C6EFCE',
                                          'font_color': '#006100'})
  
  # Write a conditional format over a range.
  worksheet.conditional_format('A1:C2', {'type': 'formula',
                                         'criteria': '=Sheet2!A1="a"', 
                                         'format': condition_format})
  
  # Close the Pandas Excel writer and output the Excel file.
  writer.save()
  

  输出：

  

  请注意，Excel需要在公式 =Sheet2!A1="a" 中的字符串周围使用双引号，并且通过使用relative cell reference（ A1 而不是绝对值 $A$1 ），公式将单独应用于范围中的每个单元格 .

  您可以将条件格式应用于基于数据帧的范围，如下所示：

  # Write a conditional format over a range.
  start_row = 0
  start_col = 0
  end_row = df1.shape[0] -1
  end_col = df1.shape[1] -1
  
  worksheet.conditional_format(start_row, start_col, end_row, end_col, 
                               {'type': 'formula',
                                'criteria': '=Sheet2!A1="a"', 
                                'format': condition_format})
  

  回复于 2024-04-26T04:16:02+08:00