Excel Sheet Table into Data Table issue

[fragrancehello]

hi

i have a excel file named as myexcel.xsls file and there are 10 sheets in that excel file named as summary,detail,kpi,orders etc etc.In excel sheet summary there are 5 tables in it named as Table1,Table2,Table3,Table4,Table5.Tables are created like I want to get Table1 records into the Data Table so for that i've written a following code

public DataTable GetExcelFileAsDataTable(string FileName)
{
DataTable dt = new DataTable();
OleDbConnection objConn = null;
try
{
objConn = new OleDbConnection(GetConnectionString(FileName));
OleDbCommand objCmd = new OleDbCommand("Select * From [Summary$]", objConn);
SPSecurity.RunWithElevatedPrivileges(delegate()
{

objConn.Open();
});

OleDbDataReader dr = objCmd.ExecuteReader();
if (dr != null)
{
dt.Load(dr);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
objConn.Close();
}

return dt;


}

But when i run that code it returns all the data from the summary sheet means all the rows from that summary sheet.

Can anyone tell me how i can only get single table which is Table1 or all the tables in the data set with each table with it's name

[djjeavons]

Hi

At the moment you are explicitly stating [Summary$] in your SELECT statement. If you want just Table1 then you can change this to [Table1$].

If you want all sheets from your Spread Sheet into a DataSet with a DataTable for each sheet then you could try the following code. This uses the GetSchema method of the connection object to find out what tables (in this case Sheets) are in the source database and then uses that information to grab all data from each sheet and add to a DataTable within a DataSet:

    string sourceConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp\source.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";
    OleDbConnection connection = new OleDbConnection(sourceConnectionString);

    //Open the connection to the spread sheet and read the Sheet Names (Tables) into a DataTable
    connection.Open();
    DataTable tables = connection.GetSchema("Tables", new String[] { null, null, null, "TABLE" });
    connection.Dispose();

    DataSet spreadSheetData = new DataSet();

    //Now that we have the tables, we can read all data from each and add to a DataSet.
    if (tables != null && tables.Rows.Count > 0)
    {
        foreach (DataRow row in tables.Rows)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", row["TABLE_NAME"].ToString()), sourceConnectionString);
            DataTable currentSheet = new DataTable(row["TABLE_NAME"].ToString());
            adapter.Fill(currentSheet);
            spreadSheetData.Tables.Add(currentSheet);
            adapter.Dispose();
        }
    }

HTH

[ddanbe]

I used to use another strategy for accessing Excel, see this class:

// ****************************************************
//
// Class to manipulate and work with Excel files in C#
// DM 2.2.2009
//
// ****************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace Peaker
{
    class ExcelC

    {
        const int FirstColWidth = 50;

        private Excel.Application  app = null;
        private Excel.Workbook workbook = null;
        private Excel.Worksheet worksheet = null;
        //private Excel.Range range = null;

        public ExcelC()
        {            
        }

        private int WSCount;

        public int WorkSheetCount
        {
            get { return WSCount = workbook.Worksheets.Count; }//if null?           
        }

        public void CreateADoc()
        {
            try
            {
                app = new Excel.Application();
                app.Visible = false;
                workbook = app.Workbooks.Add(1); 
                worksheet = (Excel.Worksheet)workbook.Sheets[1];
            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message);
            }
        }

        public void OpenADoc(string fileName)
        {
            try
            {
                app = new Excel.Application();
                workbook = app.Workbooks.Open
                    ( Filename : fileName, 
                    UpdateLinks : Type.Missing, 
                    ReadOnly : false, 
                    Format : 5, 
                    Password : Type.Missing,
                    WriteResPassword : Type.Missing,
                    IgnoreReadOnlyRecommended : true, 
                    Origin : Excel.XlPlatform.xlWindows,
                    Delimiter : "\t", 
                    Editable : true, 
                    Notify : false, 
                    Converter : Type.Missing,
                    AddToMru : true,
                    Local : false,
                    CorruptLoad : Type.Missing);
                worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);

            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message);
            }
        }

        public void Show()
        {
            app.Visible = true;
        }

        public void Hide()
        {
            app.Visible = false;
        }

        public void CloseADoc()
        {
            workbook.Close(true, Type.Missing, Type.Missing);           
        }

        public void Quit()
        {
            app.Quit();
            releaseObject(worksheet);
            releaseObject(workbook);
            releaseObject(app);
        }

        // Put data in an excel cell
        public void PutData(int row, int col, string data)
        {
            worksheet.Cells[row, col] = data;
        }

        // Get data as string out of an excel cell
        public string GetData(int row, int col)
        {
            string Val = string.Empty;
            if (worksheet.Cells[row, col].Value2 != null)
            {
                Val = worksheet.Cells[row, col].Value2.ToString();
            }
            return Val;
        }

        // Get data from a specific sheet
        public string GetData(int worksheetNr, int row, int col)
        {
            worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(worksheetNr);
            return GetData(row, col);
        }

        // Get data as double out of an excel cell
        public double GetDouble(int row, int col)
        {
            double d = -999.0;
            if (worksheet.Cells[row, col].Value2 != null)
            {
                d = worksheet.Cells[row, col].Value2;
            }
            return d;
        }

        // Write a series of strings to the active excel sheet
        public void PutHeader(int startrow, int startcol, List<string> H)
        {
            foreach (string item in H)
            {
                PutData(startrow, startcol, item);
                startcol++;
            }
        }

        public void WidenCols(int Ncols) //start with col nr 1
        {
            const int B_charASCIIcode = 66;

            worksheet.Range["A:A", Type.Missing].ColumnWidth = FirstColWidth;
            char ch = (char)(B_charASCIIcode + Ncols - 1);
            worksheet.Range["B:" + ch.ToString(), Type.Missing].ColumnWidth = 17;
        }

        public int GetLastFreeRowNr(int worksheetNr)
        {
            worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(worksheetNr);
            return worksheet.UsedRange.Rows.Count + 1; 
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 
    }
}

This was all the functionality I needed at the time, but it can easily be extended.
I used the info of this site
You can also find info about how to use OleDB here.