以编程方式将XLS转换为XLSB？

Question

我有一个客户需要将XLS文件转换为XLSB。是否有人可以以编程方式完成此操作（使用或不使用插件都可以——只需能够自动化即可）？我正在寻找一种自动化此操作的方法。

顺便提一下，客户问到这个问题是因为他们正在使用Sharepoint，似乎它有一种比XLS更快更容易分析XLSB文件的方法？我正在努力提高我的Sharepoint知识，但同时我也在尝试找到这个XLSB问题的答案。

- John Cruz

---

如果您能使用早期绑定 Office Interop，那么这相当容易。请指定其他限制。您能保存并在本地磁盘操作文件吗？ - Petr Abdulin

@Petr - 是的，我可以保存并在本地磁盘上操作文件。 - John Cruz

您可以使用MS Office VBA进行转换。 - dns

Answer 1

那么，还有一个简短的格式版本：

using Microsoft.Office.Interop.Excel;

// init excel
Application excelApplication = new Application();

// ...

// open book in any format
Workbook workbook = excelApplication.Workbooks.Open("1.xls", XlUpdateLinks.xlUpdateLinksNever, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

// save in XlFileFormat.xlExcel12 format which is XLSB
workbook.SaveAs("1.xlsb", XlFileFormat.xlExcel12, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

// close workbook
workbook.Close(false, Type.Missing, Type.Missing);

// ...

// shutdown excel
excelApplication.Quit();

您需要安装带有.NET编程支持的Excel（默认情况下在安装程序中禁用！），并从项目引用Excel的MS Office PIA程序集：

参考资料：Workbooks.Open，workbook.SaveAs，XlFileFormat.xlExcel12

- Petr Abdulin

Answer 2

我编写了这个PowerShell代码，用于递归地转换许多文件夹中的*.xls文件。 此脚本提示选择一个文件夹，将所有文件转换并删除原始文件（移动到回收站），在PowerShell控制台中显示每个文件名。

<#
    .SYNOPSIS
    Covert all *.xls files recursivly in a provided path
    .DESCRIPTION
    XLS files within a provided path are recursively enumerated and convert to XLSB files (with macro).
    The original XLS files are deleted if newfile has created (in trash), a new XLSb file replace the old file.
    #>
    $autor='alban Lopez'
    $version=0.85
    $email='[e-mail]'

    function ConvertTo-XLSB {
    <#
    .SYNOPSIS
    XLS files within a provided path are recursively enumerated and convert to XLSB files.
    .DESCRIPTION
    XLS files within a provided path are recursively enumerated and convert to XLSB files.
    The original XLS files remain intact, a new XLSB file will be created.
    .PARAMETER Path
    This parameter takes the input of the path where the XLS files are located.
    .PARAMETER Visible
    Using the parameter will show you how Excel does the work. Not using the parameter will enable Excel 
    to accomplish its tasks in the background.
    Note: Bu not using this parameter you will be able to convert some XLS files which have corruptions 
    in them, when using the parameter and therefor the Excel GUI will give you an error.
    .PARAMETER ToFolder
    This parameter enables you to provide a location where the file is saved. When this parameter is 
    not used, the file will be saved as an XLS file in the same location as where the 
    original XLS file is located.
    .EXAMPLE
    ConvertTo-XLSB -Path 'D:\Data\2012'
    .EXAMPLE
    ConvertTo-XLSB -Path 'D:\Data\2012' -Visible
    .EXAMPLE
    ConvertTo-XLSB -Path 'D:\Data\2012' -ToFolder 'D:\Data\2012XLSB'
    .EXAMPLE
    ConvertTo-XLSB -Path 'D:\Data\2012' -Visible -ToFolder 'D:\Data\2012XLSB'
#>
    [cmdletbinding()]

        param (
            [parameter(mandatory=$true)][string]$Path,
            [parameter(mandatory=$false)][switch]$Visible,
            [parameter(mandatory=$false)][string]$ToFolder
        )
        begin {
            $Excel = New-Object -ComObject excel.application
            $Excel.DisplayAlerts = $false
            # $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault # xlsx
            $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlExcel12 # 50 = xlsb
            $shell = new-object -comobject "Shell.Application"

            $count = 0
            $count_OK = 0
            $count_Nok = 0

            if ($Visible -eq $true) {
                $Excel.visible = $true
            } else {
                $Excel.visible = $false
            }
            $filetype = "*xls"
        } process {
            if (Test-Path -Path $Path) {
                Get-ChildItem -Path $Path -Include '*.xls' -recurse | ForEach-Object {
                    if ($ToFolder -ne '') {
                        $FilePath = Join-Path $ToFolder $_.BaseName
                    } else {
                        $FilePath = ($_.fullname).substring(0, ($_.FullName).lastindexOf("."))
                    }
                    $FilePath += ".xlsb"
                    $WorkBook = $Excel.workbooks.open($_.fullname)
                    $WorkBook.saveas($FilePath, $xlFixedFormat)
                    $WorkBook.close()
                    $OldFolder = $Path.substring(0, $Path.lastIndexOf("\")) + "\old"
                    if (test-path $FilePath){
                        $count_OK++
                        Write-Host -nonewline "$count_OK > "
                        Write-Host $_.fullname -ForegroundColor Cyan
                        $item = $shell.Namespace(0).ParseName("$($_.fullname)")
                        $item.InvokeVerb("delete")
                    } else {
                        $count_Nok++
                        Write-Host -nonewline "$count_Nok > "
                        Write-Host $_.fullname -ForegroundColor red
                    }
                    $count++
                }
            } else {
                return 'No path provided or access has been denied.'
            }
        } end {
            Write-Host '========================================================' -ForegroundColor yellow
            Write-Host -nonewline "Total : $count";
            Write-Host -nonewline " / Erreurs : $count_Nok / " -ForegroundColor red;
            Write-Host "convertis : $count_ok" -ForegroundColor green;
            Write-Host '========================================================' -ForegroundColor yellow

            $Excel.Quit()
            $Excel = $null
            [gc]::collect()
            [gc]::WaitForPendingFinalizers()
        }
    }


#=============================================================================
# Displays a select file dialog box, returning the path to a CSV file.
#=============================================================================
function Read-FolderBrowserDialog([string]$Message, [string]$InitialDirectory)
{
    $app = New-Object -ComObject Shell.Application
    $folder = $app.BrowseForFolder(0, $Message, 0, $InitialDirectory)
    if ($folder) { return $folder.Self.Path } else { return $false }
}

''
'Choisir le dossier source >'
$source = Read-FolderBrowserDialog -Message "Dossier source"

while ($source)
{
    "Convertion de tous les fichiers du dossier : $source"
    $ConvTime = Measure-Command {ConvertTo-XLSB -Path $source}
    Write-Host "$($ConvTime.Hours):$($ConvTime.Minutes):$($ConvTime.Seconds)";
    ''
    "End"
    ''
    'Choisir le dossier source >'
    $source = Read-FolderBrowserDialog -message "Dossier source" -InitialDirectory $source
    #$dest = Select-FolderDialog -message "Dossier Destination (sera conservé)" -RootFolder $source
}
start-sleep -s 30

- Alban

---

脚本中可能存在流程问题，因为当我尝试在没有选择文件夹对话框的情况下运行它（我将这些行注释掉），它就无法执行转换。我是从Windows的“cmd”中运行它的...我正在使用EXAMPLEs部分中显示的确切命令... 可能是我提供给powershell.exe的"-ExecutionPolicy Bypass"参数在某种程度上产生了干扰吗？ - GWD

Answer 3

这可以从命令行中使用。

- Dave Ziegler

---

它似乎只支持从.xlsb格式转换（而不是转换到该格式），这让我感到惊讶，因为它似乎使用Excel COM互操作性来处理.xlsb格式，这应该使保存和加载同样容易。 - Aaron Thoma