How to read data from Excel in Asp.Net

Question

I am trying to read datas from an uploaded xls. I have used this code part:

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(fileName) + ";Extended Properties=Excel 8.0");
                            if (connection.State == ConnectionState.Closed)
                                    connection.Open();
                            string query = "select * from [Sheet1$]";
                            OleDbDataAdapter da = new OleDbDataAdapter(query, connection);
                            DataSet ds = new DataSet();
                            da.Fill(ds);

But I get this error: External table is not in the expected format.

I am pretty sure I am giving the correct path. I was working and it is not. if it works then it wont fill the datatable. It gives an error that says that Sheet1$ object can not be found. Any help?

Can you please check the excel sheet name – Joshi Sep 19 '14 at 11:38 — Joshi, Sep 19 '14 at 11:38

score 1 · Answer 1 · answered Jan 13 '12 at 13:40

are you sure the excel version is correct? you may also want to wrap the extended properties in quotes as well. I would also recommend cleaning up your resources so you don't create memory leaks.

var path = Server.MapPath(fileName);

//where 8.0 may be a different version: 9 - 12?
var connectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended ""Properties=Excel 8.0""", path);
using(var connection = new OleDbConnection(connectionString))
using(var command = connection.CreateCommand())
{
     connection.Open();
     command.CommandText = "select * from [Sheet1$]";
     var table = new DataTable();
     using(var reader = command.ExeucteReader())
     {
        table.Load(reader);
        return table;
     }
}

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

Have a look at this thread: Excel "External table is not in the expected format."

Maybe you should change provider as they suggest.

One other option is to use the OpenXML SDK to read the excel file. This could get you started: http://msdn.microsoft.com/en-us/library/ff921204.aspx

edited May 23 '17 at 12:29

Community

1
1

answered Jan 13 '12 at 13:41

granaker

1,318
8
13

I had looked at the first link and tried but it didnt work. I still get the error "External table is not in the expected format." – Ktt Jan 13 '12 at 14:03

score 0 · Accepted Answer · answered Jan 19 '12 at 13:44

string savePath = "/Assets/UploadedFiles/";
                            string fileName = "Activity.xls";
                            savePath += fileName; 
OleDbConnection conn= new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(savePath) + ";Extended Properties='Excel 12.0;HDR=YES'");
    if (conn.State == ConnectionState.Closed)
    conn.Open();
    string query = "select * from [Sheet1$]";
      OleDbDataAdapter da = new OleDbDataAdapter(query, conn);
                                DataSet ds = new DataSet();
                                da.Fill(ds, "Activities");
                                dt = ds.Tables[0];
                                conn.Close();

Gustavo F · Answer 4 · 2012-01-13T13:36:09.343

save the file in the hard disk, then add a reference to Microsoft Excel 12.0 Object Library and declare the using:

using Excel = Microsoft.Office.Interop.Excel;

then instantiate a class and load the file to read the cell values, example:

Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open("file.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

MessageBox.Show(xlWorkSheet.get_Range("A1","A1").Value2.ToString());

xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

EDIT 1: If you don't want to install Office in the server, you can use the excellibrary that works similar, simple as (from the author page) :

// open xls file
Workbook book = Workbook.Load(file);
Worksheet sheet = book.Worksheets[0];

 // traverse cells
 foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells)
 {
     dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
 }

 // traverse rows by Index
 for (int rowIndex = sheet.Cells.FirstRowIndex; 
        rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
 {
     Row row = sheet.Cells.GetRow(rowIndex);
     for (int colIndex = row.FirstColIndex; 
        colIndex <= row.LastColIndex; colIndex++)
     {
         Cell cell = row.GetCell(colIndex);
     }
 }

..that would require installing Excel on the web server which probably isn't a good idea — granaker, Jan 13 '12 at 13:31
is there a possible way to loop this? I will have many records in the excel — Ktt, Jan 13 '12 at 13:34
And I was trying to use the first code part you wrote and for example if I say C2,C3 it give system.object instead of the content of the cell — Ktt, Jan 13 '12 at 14:53

How to read data from Excel in Asp.Net

4 Answers4