Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#2.0 ASP.NET
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..
Posted 15-Nov-11 22:43pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
LokeshZende at 16-Nov-11 6:07am
   
Thanks for your reply Deepak..
 
But while googling, I had already tried your solution..It doesn't work..:(
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
I am facing same type of problem solution.
 
Just go to Open Excel file
 
1) Click on File Menu.
 
2) Click on Save As .
 
3) Show a Box.
 
4) Change File Name & Save As Type to Excel 97-2003 Workbook (*.xls) & click save then it work.
 
Also this type of problem occurs in .csv apply same procedure & solve it.
  Permalink  
v2
Comments
Lokesh Zende at 29-Nov-12 6:07am
   
Hello Sanjay,
Yes, it definitely works. But I dont want to save the excel in other formats. Rather, how can I ask the user to do such things. hahaha..Hope u can understand.
Thanks for reply.
basunld at 4-May-13 6:45am
   
Nice its worked!...Thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

sometimes the file might be locked by a different user or process, so you just need to copy it and load the copied file as it says here
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 mhegazy94 460
1 Sergey Alexandrovich Kryukov 405
2 Kornfeld Eliyahu Peter 285
3 Gihan Liyanage 167
4 Sibeesh KV 150
0 Sergey Alexandrovich Kryukov 6,805
1 OriginalGriff 6,696
2 CPallini 5,350
3 George Jonsson 3,609
4 Gihan Liyanage 2,792


Advertise | Privacy | Mobile
Web02 | 2.8.140922.1 | Last Updated 16 Feb 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100