External table is not in the expected format.

See more:

C#2.0

C#

ASP.NET

Hi Friends,
I am generating an Excel file (Excel 2003 i.e. ".xls") using some data tables.
File generates successfully.
Now I am trying to open the same file using upload control and OLEDB connection, but I am getting this strange error.
"External table is not in the expected format."
Now, if I open the file and try to run my code, it works fine....
whereas it is expected that it should give me an error saying "File is already being used." (Please correct me here if I am wrong.)

I googled a lot about this but got no solution. All I got is,
use Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";
instead of "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

I use these two providers depending upon type of the file (i.e .xls / .xlsx)

I ahve (Do spell check before you post.) MS Office 2007 installed on my machine.

Can you please help me friends??

Thanks,
Lok..


Please refer this code..

public enum ExcelVersion
{
	Excel,
        Excel2007
};
private string ProviderExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
private string ExtendedPropertiesExcel = "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
private string ProviderExcel2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";
private string ExtendedPropertiesExcel2007 = "Extended Properties=\"Excel 12.0;HDR=YES\"";
private string mConnString = "";
protected void btnPost_Click(object sender, EventArgs e)
{
	if (rdpUpload.UploadedFiles.Count == 0)
        	throw new Exception("Please select file to upload");
	
	string sExtension = "";
        string FileName = "";
        ApplicationDirectoryTypeInfoList oApplicationDirectoryTypeInfoList = ApplicationDirectoryTypeInfoList.GetAllApplicationDirectories();  // my classes to read Directory..
        ApplicationDirectoryTypeInfo oApplicationDirectoryTypeInfo = oApplicationDirectoryTypeInfoList.GetItem(35);
        string FilePath = oApplicationDirectoryTypeInfo.ApplicationDirectoryFQN;
        FilePath = FilePath.Substring(FilePath.IndexOf("/") + 1);
        FilePath = AppDomain.CurrentDomain.BaseDirectory + FilePath;
        foreach (UploadedFile file in rdpUpload.UploadedFiles)
        {
		FileName = file.GetName();
                sExtension = file.GetName();
                sExtension = sExtension.Substring(sExtension.LastIndexOf(".") + 1);
                if (sExtension != "xls")
                    throw new System.Exception("Select .xls/.xlsx file ");
                 ExcelImport(ExcelVersion.Excel, FilePath + "//" + FileName);
                 GetPaymentVouchers();
        }
}
public void ExcelImport(ExcelVersion ver, string FilePath)
{
	switch (ver)
        {
		
                case ExcelVersion.Excel:
                    mConnString = ProviderExcel + FilePath + ";" + ExtendedPropertiesExcel;
                    break;
                case ExcelVersion.Excel2007:
                    mConnString = ProviderExcel2007 + FilePath + ";" + ExtendedPropertiesExcel2007;
                    break;
        }
}
public void GetPaymentVouchers()
{
	OleDbConnection cn = new OleDbConnection(mConnString);
        try
        {
		cn.Open();           //   I get Error here while opening Connection...
                DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                string SheetName = schemaTable.Rows[0][2].ToString();
                StringBuilder SelectStatement = new StringBuilder();
                SelectStatement.Append("SELECT * FROM [").Append(SheetName).Append("]");
                OleDbDataAdapter da = new OleDbDataAdapter(SelectStatement.ToString(), cn);
                DataSet ds = new DataSet();
                da.Fill(ds);
	}
	catch(Exception ex)
	{}
}