无法打开使用oLedb导出的XLSX文件 [英] Could not open XLSX file exported using oLedb

问题描述

我使用OLEDB连接将样本数据表从数据库保存到Excel。



如果我使用[.XLS]扩展名保存excel文件，我可以打开文件并查看存储的数据表。



但如果我创建Excel文件[.XLSX]格式，移动数据后，

如果我尝试打开它我收到错误，如

文件格式或文件扩展名不正确。检查文件是否有损坏或文件扩展名与文件格式不匹配我无法打开Excel文件。



我正在使用Windows 7并尝试安装Acess数据库引擎但仍然获得同样的错误。



下面是我试图用.XLSX格式创建简单excel文件的src代码。

（文件选择的路径是C：\\Users \ Vijay \Desktop \ Recipe.XLSX）

Hi,

I am saving sample data table from database to Excel using OLEDB connection.

If I save the excel file with [.XLS] extension, I can open the file and see the stored data table.

But if I create the Excel file with [.XLSX] format, after moving datatable,
if I try to open it I am getting error like
"File format or file extension is incorrect. Check whether file has corrupted or file extension doesnot match with file format" and I cannot open the Excel file.

I am using Windows 7 and tried installing "Acess Database engine" but still getting the same error.

Below is the src code in which I am trying to create simple excel file with .XLSX format.
(File path selected is C:\\Users\Vijay\Desktop\Recipe.XLSX)

Dim saveDialog As New System.Windows.Forms.SaveFileDialog
        saveDialog.Title = "ExportFile"
        saveDialog.Filter = "MS Office Excel2007|*.xlsx"
        'saveDialog.DefaultExt = "xlsx"
        'saveDialog.SupportMultiDottedExtensions = True
        saveDialog.FileName = "Recipe"

        If saveDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then

            ExpFileName = saveDialog.FileName
        End If

        Dim ConString As String = ""

    ConString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel 12.0;HDR=YES"";Data Source={0}", ExpFileName)
        Dim conObj As New System.Data.OleDb.OleDbConnection()
        conObj.ConnectionString = ConString
        conObj.Open()


        Dim createTable As String = "CREATE TABLE sheet1(VJ1 char(255),VJ2 char(255))"

        Dim cmd As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand(createTable, conObj)

        cmd.ExecuteNonQuery()

在此先感谢，



Vijay

Thanks in Advance,

Vijay

推荐答案

在您的代码中'Extended Properties =Excel 12.0;您的连接字符串的HDR = YES'。应添加'Xml'以使'Extended Properties =Excel 12.0 Xml; HDR = YES'。

。所以你必须修改你的连接字符串：

In your code ' Extended Properties=""Excel 12.0;HDR=YES ' of your connection string .a 'Xml' should be added to make ' Extended Properties=""Excel 12.0 Xml;HDR=YES '.
.So you have to modify your connection string as :

ConString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel 12.0 Xml;HDR=Yes"";Data Source=" & ExpFileName)

它似乎用于指定或验证数据在excel表.ie我不确定为什么这么重要但

此连接字符串适用于您的场景。

It seems to be used to specify or validate data in excel sheet .i.e. i am not sure why this is so important but
This connection string works as of your scenario.

您的代码生成旧Excel格式。当您只是将旧格式的扩展名更改为.xlsx时，Excel会抛出该异常。当您以较新的格式保存文件时，将扩展名更改为.xls，Excel将警告您该文件可能已损坏，但您仍然可以将其打开。所以：只需确保扩展名与格式匹配。

Your code generates the "old" Excel format. When you just change the extension of the old format to ".xlsx", Excel throws that exception. When you save a file in the newer format nad change the extension to ".xls", Excel will warn you that the file could be corrupted, but you can still open it. So: just make sure the extension matches the format.

这篇关于无法打开使用oLedb导出的XLSX文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！