Python使用openpyxl生成xlsx文件无法保存到指定目录问题求助

核心修改逻辑 #

只需要调整文件生成路径的拼接逻辑，新增目录自动创建逻辑避免路径不存在报错即可，原有业务逻辑无需改动。

具体修改点 #

• 给类新增可配置的保存目录参数，支持自定义存储路径
• 程序启动时自动创建目标存储目录（如果目录不存在）
• 将原来仅包含文件名的self.filename替换为「指定目录+文件名」的绝对/相对路径

修改后完整代码 #

import datetime
import os
from shutil import copyfile
import xml.etree.ElementTree as ET
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

class MeasurementsData:
    # 新增save_dir参数，传入你需要保存的目录路径，默认存放在当前目录下的output文件夹
    def __init__(self, save_dir="./output"):
        self.tag = ""
        self.date = ""
        self.filename = ""
        self.application = ""
        self.element = ""
        self.concentration = ""
        self.unit = ""
        self.concentration_results = []
        self.save_dir = save_dir
        # 自动创建目标目录，不存在就新建，避免报错
        os.makedirs(self.save_dir, exist_ok=True)

    def parse_empirical_assay(self, result):
        application_methods=result.find('EmpiricalAssayResult').find('ScreeningInfo').findall('ScreeningMethod')
        for method in application_methods:
            if self.application != "":
                self.application += " - "
            self.application += method.attrib['name']

        concentration_results = result.find('EmpiricalAssayResult').find('ConcentrationResults').findall('ConcentrationResult')
        for concentration_result in concentration_results:
            self.concentration_results.append({
                'element': concentration_result.attrib['name'],
                'concentration': concentration_result.attrib['concentration'],
                'unit': concentration_result.attrib['unit']
                })

    def parse_fundamental_parameters(self, result):
        application_methods=result.find('FundamentalParametersResult').find('ScreeningInfo').findall('ScreeningMethod')
        for method in application_methods:
            if self.application != "":
                self.application += " - "
            self.application += method.attrib['name']

        concentration_results = result.find('FundamentalParametersResult').find('ConcentrationResults').findall('ConcentrationResult')
        for concentration_result in concentration_results:
            self.concentration_results.append({
                'element': concentration_result.attrib['name'],
                'concentration': concentration_result.attrib['concentration'],
                'unit': concentration_result.attrib['unit']
                })

    def parse(self, data):
        #print(data)
        xml_data = ET.fromstring(data)
        self.tag = xml_data.attrib['name']
        if self.tag == None or self.tag == "":
            self.tag = "noname"
        # 修改：拼接指定目录和文件名，生成完整存储路径
        self.filename = os.path.join(self.save_dir, self.tag + ".xlsx")
        self.date = xml_data.attrib['measurementTime']
        self.date = self.date.split('.')[0]
        self.date=datetime.datetime.strptime(self.date, '%Y-%m-%dT%H:%M:%S')
        #print(self.tag)
        
        result = xml_data.find('Result')
        if result[0].tag == "EmpiricalAssayResult":
            if not os.path.isfile(self.filename):
                copyfile("files/ReportDiMisura1.xlsx", self.filename)
            self.parse_empirical_assay(result)
        elif result[0].tag == "FundamentalParametersResult":
            if not os.path.isfile(self.filename):
                copyfile("files/ReportDiMisura9Colonne2.xlsx", self.filename)
            self.parse_fundamental_parameters(result)
        
        wb = load_workbook(filename = self.filename)
        if wb == None:
            print('File xlsm non trovato!')
            return

        ws = wb['Report']
        if wb == None:
            print('Foglio Report non presente!')
            return

        font = Font(name='Calibri',
            size=11,
            bold=False,
            italic=False,
            vertAlign=None,
            underline='none',
            strike=False,
            color='FF000000')

        alignment = Alignment(horizontal='center',
            vertical='center',
            text_rotation=0,
            wrap_text=False,
            shrink_to_fit=False,
            indent=0)

        cell = ws.cell(row=10, column=2)
        cell.value = self.application
        cell.font = font
        cell.alignment = alignment

        headers_row=23
        row = headers_row+1
        count=1
        cell = ws.cell(row=row, column=1)
        while cell.value != None:
            row += 1
            count += 1
            cell = ws.cell(row=row, column=1)

        if cell.value == None:
            cell.value = self.date #.strftime("%d/%m/%Y %H:%M")
            cell.font = font
            cell.alignment = alignment

            cell = ws.cell(row=row, column=2)
            cell.value = count
            cell.font = font
            cell.alignment = alignment

            column=3
            for concentration_result in self.concentration_results:
                cell = ws.cell(row=headers_row, column=column)
                cell.value = concentration_result['element'] + " " + concentration_result['unit']
                cell.font = font
                cell.alignment = alignment

                cell = ws.cell(row=row, column=column)
                cell.value = float(concentration_result['concentration'])
                cell.font = font
                cell.alignment = alignment
                column+=1

        wb.save(filename = self.filename)

使用说明 #

如果需要修改存储路径，只需要在初始化MeasurementsData类的时候传入路径即可，示例：

• Windows系统示例：md = MeasurementsData(save_dir=r"C:\Users\admin\Desktop\测量报表")
• Linux/Mac系统示例：md = MeasurementsData(save_dir="/home/admin/measurement_reports")
• 也可以直接修改__init__方法里的save_dir默认值，改成固定路径不用每次传参。

内容的提问来源于stack exchange，提问作者Combustible_lemons