Hi Friends,
I am exporting data into Excel (2003 format).
But when I try to read the same Excel, I get this error
"External table is not in the expected format"
I have MS Office 2007 installed on my PC.
When I open that Excel and then try to read it, it works fine, whereas it is expected that it should give me error msg that the File is already being used.
(I think so. Please clear me if I am wrong)
Here is what my code says:
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 = "";
On Button_Click Even, I have..
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();
}
And these are my methods
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();
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)
{
XITingExceptionProcessor.ProcessException(this, ex);
}
finally
{
cn.Close();
}
}
Please help me out friends.
Thanks,
Lok..