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

Question

我有一位客户需要将 XLS 文件转换为 XLSB。有没有人以编程方式完成此操作（有或没有附加组件 - 没关系 - 只需要能够自动化它）？我正在寻找一种自动化的方法。

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

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 程序集：

参考资料：
工作簿.打开，workbook.SaveAs 、XlFileFormat.xlExcel12

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='[email protected]'

    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

Answer 3

此可以从命令行使用。