无法打开使用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屋!