将.xlsx工作表拆分成单独的工作簿？ [英] Splitting out .xlsx worksheets into seperate workbooks?

问题描述

我是新手，这是我的驾驶室。我正在尝试提出一个自动化流程，最终将带有多个选项卡的Excel电子表格作为单个表加载到MS SQL中。我们将其用于数据转换目的。



从我最初的阅读开始，我以为我可以使用PowerShell将电子表格拆分成单个文件，但我遇到了困难。我已经浏览了其他几个主题，但没有一个解决方案对我有用，我也无法弄明白。我尝试过使用.xlsx和.xls文件。 PowerShell 2.0。



我尝试过：



我是在我的C盘上创建了一个名为load的文件夹，这就是我放置文件的地方。 C：\ Looad \Load.xls和C：\Load \Load.ps1。



我正在使用下面的脚本粘贴，我正在尝试使用cmd执行此操作（我完全从其他帖子中借用了所有这些内容）我见过BTW）





PowerShell.exe -commandC：\ Loadad \ Load.ps1-filepath C：\ Load \ Load.xls-output_typecsv



我收到错误说

I'm new to this, and this is out of my wheelhouse. I'm trying to come up with an automated process to ultimately load an excel spreadsheet with multiple tabs into MS SQL as individual tables. We use this for data conversion purposes.

From the initial reading I've done, I was thinking I could use PowerShell to split out the spreadsheet into individual files, but I'm getting stuck. I've browsed a few other topics on this but none of the solutions are working for me, and I haven't been able to figure it out. I've tried using .xlsx and .xls files. PowerShell 2.0.

What I have tried:

I've created a folder on my C drive called load, and that is where I'm placing the files. C:\Load\Load.xls and C:\Load\Load.ps1.

I'm pasting in the script I'm using below, and I'm attempting execute this from cmd using (I've totally borrowed all this from other posts I've seen BTW)


PowerShell.exe -command "C:\Load\Load.ps1" -filepath "C:\Load\Load.xls" -output_type "csv"

I'm getting an error saying

Method Invocation failed because [System.__ComObject] doesn't contain a method named 'SaveAs'
At C:\Load\Load.ps1 :50 char:26
+ $WorkSheet.SaveAs<<<< ($ExtractedFileName + $FileFormat)
+ CategoryInfo : Invalid Operation: (SaveAs:String) [],RuntimeException
+FullyQualifiedErrorId : MethodNotFound

这是ps1文件中的脚本

this is the script in the ps1 file

[CmdletBinding()]
Param ( 
    [Parameter(Mandatory=$true,Position=0)] 
    [string]$filepath,
    [Parameter(Mandatory=$true,Position=1)] 
    [ValidateSet("csv","txt","xls","html")] 
    [string]$output_type 
)


#-----------------------------------------------------------------------------#


# Figures out and returns the 'XlFileFormat Enumeration' ID for the specified format.
# http://msdn.microsoft.com/en-us/library/office/bb241279%28v=office.12%29.aspx 
# NOTE: The code being used for 'xls' is actually a 'text' type, but it seemed
# to work the best for splitting the worksheets into separate Excel files.
function GetOutputFileFormatID 
{ 
Param([string]$fomat_name) 
    $Result = 0 
    switch($fomat_name) 
    { 
        "csv" {$Result = 6} 
        "txt" {$Result = 20} 
        "xls" {$Result = 21} 
        "html" {$Result = 44} 
        default {$Result = 51} 
    } 
    
    return $Result 
}

#-----------------------------------------------------------------------------# 
$Excel = New-Object -ComObject "Excel.Application" 
$Excel.Visible = $false #Runs Excel in the background. 
$Excel.DisplayAlerts = $false #Supress alert messages. 
$Workbook = $Excel.Workbooks.open($filepath) 
#Loop through the Workbook and extract each Worksheet
#     in the specified file type. if ($Workbook.Worksheets.Count -gt 0) { 
    write-Output "Now processing: $WorkbookName" 
    
    $FileFormat = GetOutputFileFormatID($output_type) 
    #Strip off the Excel extension. 
    $WorkbookName = $filepath -replace ".xlsx", "" #Post 2007 extension
    $WorkbookName = $WorkbookName -replace ".xls", "" #Pre 2007 extension 
    $Worksheet = $Workbook.Worksheets.item(1) 
    foreach($Worksheet in $Workbook.Worksheets) { 
        $ExtractedFileName = $Worksheet.Name + "." + $output_type 
        $Worksheet.SaveAs($ExtractedFileName + $FileFormat) 
        write-Output "Created file: $ExtractedFileName" 
    } 

#Clean up & close the main Excel objects. 
$Workbook.Close() 
$Excel.Quit()

有什么想法吗？

Any thoughts?

推荐答案

WorkSheet.SaveAs<<<< （

WorkSheet.SaveAs<<<< (

ExtractedFileName +

ExtractedFileName +

FileFormat）
+ CategoryInfo：无效操作：（SaveAs：String）[]，RuntimeException
+ FullyQualifiedErrorId：MethodNotFound

FileFormat) + CategoryInfo : Invalid Operation: (SaveAs:String) [],RuntimeException +FullyQualifiedErrorId : MethodNotFound

这是ps1文件中的脚本

this is the script in the ps1 file

[CmdletBinding()]
Param ( 
    [Parameter(Mandatory=

）

这篇关于将.xlsx工作表拆分成单独的工作簿？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！