将Excel转换为景观方向的PDF

Question

我正在尝试将Excel表转换为可与以下代码一起使用的PDF：

    xlApp = client.Dispatch("Excel.Application")
    books = xlApp.Workbooks.Open('Table.xlsx')
    ws = books.Worksheets[0]
    ws.Visible = 1
    ws.ExportAsFixedFormat(0, 'Table.pdf')
    books.Save()
    books.Close()

唯一的问题是我需要桌子以景观为导向，而我对如何制作规范感到茫然。

我已经看到了具有ws.PageSetup.Orientation = xlLandscape等代码的解决方案或某些变化的解决方案，但我相信这仅适用于VBA/我无法找到Python的正确语法。

任何帮助或想法都将不胜感激。

Answer 1

一种更容易的方法是在打印之前更改方向：

def print_excel_worksheet_to_pdf(i_sz_excel_path, i_sz_ws_name, i_sz_pdf_path):
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False   #Keep the excel sheet closed
excel.DisplayAlerts = False  #"Do you want to over write it?" Will not Pop up
try:
    wb_source = excel.Workbooks.Open(i_sz_excel_path)
    ws_source = wb_source.Worksheets(i_sz_ws_name)
    ws_source.PageSetup.Orientation = 2 # change orientation to landscape
    ws_source.Select()
    wb_source.ActiveSheet.ExportAsFixedFormat(0, i_sz_pdf_path)
except Exception as e:
    print(e)
excel.Application.Quit()

只要您不保存更改，这不会影响原始文件，并消除了使用vbscript宏的临时文件

Answer 2

好的，所以我设法将一些代码弄解了一些我最初问的代码。我敢肯定，有一种更好的方法可以解决这个问题，但是对于任何遇到同一问题的人来说，这可能会有所帮助。

我最终将"保存为"现有的.xlsx文件作为新的宏启用Excel文件并输入在Python中创建的宏。VBA代码使将文件更改为景观模式成为可能，然后以PDF导出。宏通过后，我删除.xlsm文件。最后，如果Excel应用程序尚未关闭，则可以防止发生错误。

# Save as pdf
# Start excel application
xlApp = client.Dispatch("Excel.Application")
# Open the table previously created
books = xlApp.Workbooks.Open('Table.xlsx'))
# xlmodule allows for insertion of VBA code which we will use to change orientation and save as pdf
xlmodule = books.VBProject.VBComponents.Add(1)
# VBACode is the VBA code used, sub ... is the name of the macro
VBACode = '''Sub PDF_Creation()
        With Worksheets("Overlap Matrix").PageSetup
            .Orientation = xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        Worksheets("Overlap Matrix").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:="C:/.../PDF_test.pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
        End Sub'''
# Now we add the VBA code as a macro to the excel sheet
xlmodule.CodeModule.AddFromString(VBACode)
# To use the VBA code we must first save the file as xlsm -> FileFormat 52 denotes xlsm
books.SaveAs('Table', FileFormat=52)
# Close the book so that we can reopen it as a macro enabled file
books.Close()
# Open the newly created xlsm file
xlApp.Workbooks.Open(Filename='Table.xlsm', ReadOnly=1)
# Run the macro
xlApp.Application.Run('Table.xlsm!PDF_Creation')
# Quit the application
xlApp.Application.Quit()
# Delete the macro file, we still have the file without macros as a backup
# Use while loop to continue trying till we actually quit the excel application
file_deleted = False
while file_deleted is False:
    try:
        os.remove('Table.xlsm')
        file_deleted = True
    except WindowsError:
        sleep(0.5)