我正在尝试为现有Excel文件的所有工作表添加打印头标题。
我写的代码如下图所示，但它创建新的工作表和所有数据将丢失。
我如何添加一个页眉/页脚为一个现有的Excel文件的电子邮件页面，而不改变数据？

import xlsxwriter
import pandas as pd
import glob
import os
def addExcelFile(locationPath, textAdd):
    
    for file in glob.glob(locationPath + '\\**\\*.xlsx', recursive=True):        
        errorFile = os.path.basename(file).split('/')[-1]
        print(errorFile[0:2])
        if (errorFile[0:2] != '~$'):
            print(file)
            xl = pd.ExcelFile(file, engine='openpyxl')
            # Open a Pandas Excel writer using XlsxWriter as the engine.
            writer = pd.ExcelWriter(file, engine='xlsxwriter')
            # Get the xlsxwriter workbook and worksheet objects.
            workbook  = writer.book
            listSheet = xl.sheet_names
            print(len(listSheet))
            # see all sheet names
            if(textAdd == 'LGE Confidential'):
                for i in range (0,len(listSheet)):
                    nameSheet = listSheet[i]
                    writer.sheets={nameSheet:workbook.add_worksheet()}
                    worksheet = writer.sheets[nameSheet]
                    # Set color for text :https://learn.microsoft.com/en-us/dotnet/api/system.windows.media.colors?view=windowsdesktop-7.0 
                    # replace char FF -> K in start color code 
                    worksheet.set_header('&C&"Tahoma,Bold"&14&Kff0000' + textAdd)
            elif(textAdd == 'LGE Internal Use Only'):
                for i in range (0,len(listSheet)):
                    nameSheet = listSheet[i]
                    writer.sheets={nameSheet:workbook.add_worksheet()}
                    worksheet = writer.sheets[nameSheet]
                    # Set color for text :https://learn.microsoft.com/en-us/dotnet/api/system.windows.media.colors?view=windowsdesktop-7.0 
                    # replace char FF -> K in start color code 
                    worksheet.set_header('&C&"Tahoma,Bold"&14&Ka9a9a9' + textAdd)
            # Close the Pandas Excel writer and output the Excel file.
            writer.save()
            xl.close()
        else:
            print('File error: ', file, '. It will not be add header!')

字符串
添加一个页眉/页脚为一个现有的excel文件的电子邮件页面，不改变数据。

import openpyxl
import glob
import os
def addExcelFile(textAdd, workbook):
### Set the colour for the header, only two variations based on the text. Grey can be the
### default colour which is set and only changed if the text is 'LGE Confidential'
head_color = 'a9a9a9' # Default to Grey
if textAdd == 'LGE Confidential':
head_color = 'ff0000' # Change to RED if the text is 'LGE Confidential'
### Create the Header from the default style, colour and text
headertext = '&C&"Tahoma,Bold"&14&K' + head_color + textAdd
### Add the header as needed
for sheet in workbook.worksheets:
sheet.firstHeader.center.text = headertext
sheet.oddHeader.center.text = headertext
sheet.evenHeader.center.text = headertext
locationPath = '<path>'
### Header text (textAdd). Hard set here for code runability
ta = 'LGE Confidential'
# ta = 'LGE Internal Use Only'
for file in glob.glob(locationPath + '\\**\\*.xlsx', recursive=True):
errorFile = os.path.basename(file).split('/')[-1]
print(errorFile[0:2])
if errorFile[0:2] != '~$':
print(file)
### Open the workbook with Openpyxl
wb = openpyxl.load_workbook(file)
### Call function to add Header to workbook sheets
addExcelFile(ta, wb)
### Save updated workbook
wb.save(file)

1条答案

按热度按时间

oknwwptz1#

你似乎做的比你所需要的结果要多。
你只需要在工作簿中的工作表中添加标题。没有必要使用Pandas和Xlwriters，只需使用Openpyxl并更新每个工作表。
你也有重复的代码，尽量避免重复的代码，如果同一个代码段可以用于多个应用程序。部分if(textAdd == 'LGE Confidential'):等重复约4行不必要的。
使用Openpyxl，你可以将页眉（和页脚）设置为左，中，右三个部分之一，并允许对第一页和奇数/偶数页进行不同的设置。在示例中，我使用了“中心”部分，并包括所有页面，这样你就可以改变为适合的页面。
据我所知，下面的代码示例应该能满足你的需要。我已经硬设置了'textAdd'字符串，所以代码是可运行的，在每个测试之间进行更改。

import openpyxl
import glob
import os
def addExcelFile(textAdd, workbook):
        ### Set the colour for the header, only two variations based on the text. Grey can be the
        ### default colour which is set and only changed if the text is 'LGE Confidential'
        head_color = 'a9a9a9'  # Default to Grey
        if textAdd == 'LGE Confidential':
            head_color = 'ff0000'  # Change to RED if the text is 'LGE Confidential'
        ### Create the Header from the default style, colour and text
        headertext = '&C&"Tahoma,Bold"&14&K' + head_color + textAdd
        ### Add the header as needed
        for sheet in workbook.worksheets:
            sheet.firstHeader.center.text = headertext
            sheet.oddHeader.center.text = headertext
            sheet.evenHeader.center.text = headertext
locationPath = '<path>'
### Header text (textAdd). Hard set here for code runability
ta = 'LGE Confidential'
# ta = 'LGE Internal Use Only'
for file in glob.glob(locationPath + '\\**\\*.xlsx', recursive=True):
    errorFile = os.path.basename(file).split('/')[-1]
    print(errorFile[0:2])
    if errorFile[0:2] != '~$':
        print(file)
    ### Open the workbook with Openpyxl
    wb = openpyxl.load_workbook(file)
    ### Call function to add Header to workbook sheets
    addExcelFile(ta, wb)
    ### Save updated workbook 
    wb.save(file)

字符串

展开查看全部

赞(0）回复(0）举报 2023-11-15

Python;在不更改数据的情况下为现有Excel文件上的电子邮件页面添加页眉/页脚

1条答案

相关问题

热门标签

最新问答