C# , Read from Excel

[jas2010]

hi all friend

I wanna Read some data from my Excel , but I can't work excel 2007, do you know what's problem.

[Narue]

The file format changed between Excel 2003 and Excel 2007. Your solution for the older format won't work on the newer format. How are you reading the files?

[jas2010]

my code like this:

try
			{
				SqlConnection conn = new SqlConnection("Integrated Security=yes;Initial Catalog=Emp;Data Source=(local)");
				conn.Open();
				
				SqlCommand command = new SqlCommand("select * from emp", conn);
				
				SqlDataAdapter adapter = new SqlDataAdapter(command);
				DataSet dataset = new DataSet();

				adapter.Fill(dataset);
				

				Excel.ApplicationClass excel = new ApplicationClass();

				excel.Application.Workbooks.Add(true);

				System.Data.DataTable table = dataset.Tables[0];
				int ColumnIndex=0;
				foreach( System.Data.DataColumn col in table.Columns) 
				{
					ColumnIndex++; 
					excel.Cells[1,ColumnIndex]=col.ColumnName; } int rowIndex=0;
				foreach(DataRow row in table.Rows) 
				{
					rowIndex++; 
					ColumnIndex=0; foreach(DataColumn col in table.Columns) 
								   {
									  ColumnIndex++; 
									  excel.Cells[rowIndex+1,ColumnIndex]=row[col.ColumnName]; 
								   } 
				}

				excel.Visible = true;

				Worksheet worksheet = (Worksheet)excel.ActiveSheet;

				worksheet.Activate();
				

			}
			catch (XmlException exml)
			{
				// catch an xmlexception errors
				MessageBox.Show( exml.Message);
			}

[Narue]

I'd guess that the Office interop is for 2003. Are you sure you have the latest interop? Just to verify, your code works with spreadsheets saved in an Office 2000/2003 format, yes?

[jas2010]

I'd guess that the Office interop is for 2003. Are you sure you have the latest interop? Just to verify, your code works with spreadsheets saved in an Office 2000/2003 format, yes?

yes of course

[jas2010]

not anyone help me

[jas2010]

hi

I used windows 7 and Office 2007 , do you have any Example for me about connection C# with Excel 2007 in windows 7?

[Narue]

I've always done it with ADO.NET:

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;

namespace JSW.Utilities {
    public class ExcelSpreadsheet {
        #region Data Fields
        // Not an auto-property so we can initialize within the getter
        private List<string> _workSheets = null;
        #endregion

        #region Properties
        /// <summary>
        /// Gets or sets the path of the Excel file
        /// </summary>
        private string FileName { get; set; }

        /// <summary>
        /// Gets or sets the Excel connection string for ADO.NET
        /// </summary>
        private string ConnectionString { get; set; }

        /// <summary>
        /// The contents of the worksheets in the Excel file
        /// </summary>
        /// <remarks>
        /// Worksheets are only loaded when requested and kept in memory afterward
        /// </remarks>
        private DataSet SpreadSheet { get; set; }

        /// <summary>
        /// Gets a list of worksheet names for the Excel file
        /// </summary>
        public List<string> WorkSheets {
            get {
                if (_workSheets != null)
                    return _workSheets;

                _workSheets = new List<string>();

                using (var connection = new OleDbConnection(ConnectionString)) {
                    connection.Open();

                    DataTable schema = connection.GetOleDbSchemaTable(
                        OleDbSchemaGuid.Tables,
                        new object[] { null, null, null, "TABLE" });

                    foreach (DataRow row in schema.Rows)
                        _workSheets.Add(row["TABLE_NAME"].ToString());

                    connection.Close();
                }

                return _workSheets;
            }
        }
        #endregion

        #region Indexers
        /// <summary>
        /// Gets a worksheet from the Excel file by name
        /// </summary>
        /// <param name="sheetName">Name of the desired worksheet</param>
        /// <returns>The worksheet as a data table</returns>
        public DataTable this[string sheetName] {
            get {
                // Don't reload the worksheet if it's already in memory
                if (SpreadSheet.Tables.Contains(sheetName))
                    return SpreadSheet.Tables[sheetName];

                var table = new DataTable(sheetName);
                string query = "select * from [" + sheetName + "]";

                using (var connection = new OleDbConnection(ConnectionString)) {
                    connection.Open();

                    using (var command = new OleDbCommand(query, connection)) {
                        using (var adapter = new OleDbDataAdapter(command)) {
                            adapter.Fill(table);
                            SpreadSheet.Tables.Add(table);
                        }
                    }

                    connection.Close();
                }

                return table;
            }
        }
        #endregion

        #region Constructors
        public ExcelSpreadsheet(string filename, bool hasHeader) {
            if (filename.EndsWith("xlsx")) {
                ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                    + filename + ";Extended Properties=\"Excel 12.0;HDR=" + (hasHeader ? "Yes" : "No")
                    + ";IMEX=1\";";
            } else {
                // Assume the old ".xls" format
                ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                    + filename + ";Extended Properties=\"Excel 8.0;HDR=" + (hasHeader ? "Yes" : "No")
                    + ";IMEX=1\";";
            }

            FileName = filename;
            SpreadSheet = new DataSet();
        }
        #endregion
    }
}

[jas2010]

I've always done it with ADO.NET:

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;

namespace JSW.Utilities {
    public class ExcelSpreadsheet {
        #region Data Fields
        // Not an auto-property so we can initialize within the getter
        private List<string> _workSheets = null;
        #endregion

        #region Properties
        /// <summary>
        /// Gets or sets the path of the Excel file
        /// </summary>
        private string FileName { get; set; }

        /// <summary>
        /// Gets or sets the Excel connection string for ADO.NET
        /// </summary>
        private string ConnectionString { get; set; }

        /// <summary>
        /// The contents of the worksheets in the Excel file
        /// </summary>
        /// <remarks>
        /// Worksheets are only loaded when requested and kept in memory afterward
        /// </remarks>
        private DataSet SpreadSheet { get; set; }

        /// <summary>
        /// Gets a list of worksheet names for the Excel file
        /// </summary>
        public List<string> WorkSheets {
            get {
                if (_workSheets != null)
                    return _workSheets;

                _workSheets = new List<string>();

                using (var connection = new OleDbConnection(ConnectionString)) {
                    connection.Open();

                    DataTable schema = connection.GetOleDbSchemaTable(
                        OleDbSchemaGuid.Tables,
                        new object[] { null, null, null, "TABLE" });

                    foreach (DataRow row in schema.Rows)
                        _workSheets.Add(row["TABLE_NAME"].ToString());

                    connection.Close();
                }

                return _workSheets;
            }
        }
        #endregion

        #region Indexers
        /// <summary>
        /// Gets a worksheet from the Excel file by name
        /// </summary>
        /// <param name="sheetName">Name of the desired worksheet</param>
        /// <returns>The worksheet as a data table</returns>
        public DataTable this[string sheetName] {
            get {
                // Don't reload the worksheet if it's already in memory
                if (SpreadSheet.Tables.Contains(sheetName))
                    return SpreadSheet.Tables[sheetName];

                var table = new DataTable(sheetName);
                string query = "select * from [" + sheetName + "]";

                using (var connection = new OleDbConnection(ConnectionString)) {
                    connection.Open();

                    using (var command = new OleDbCommand(query, connection)) {
                        using (var adapter = new OleDbDataAdapter(command)) {
                            adapter.Fill(table);
                            SpreadSheet.Tables.Add(table);
                        }
                    }

                    connection.Close();
                }

                return table;
            }
        }
        #endregion

        #region Constructors
        public ExcelSpreadsheet(string filename, bool hasHeader) {
            if (filename.EndsWith("xlsx")) {
                ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                    + filename + ";Extended Properties=\"Excel 12.0;HDR=" + (hasHeader ? "Yes" : "No")
                    + ";IMEX=1\";";
            } else {
                // Assume the old ".xls" format
                ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                    + filename + ";Extended Properties=\"Excel 8.0;HDR=" + (hasHeader ? "Yes" : "No")
                    + ";IMEX=1\";";
            }

            FileName = filename;
            SpreadSheet = new DataSet();
        }
        #endregion
    }
}

hi dear

i used from your code but i received following Error:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

please help me

[Narue]

i used from your code but i received following Error:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Yep, I got that error too when adding support for xlsx files. I fixed it by installing this.

[jas2010]

hi again

I went to up link and download AccessDatabaseEngine.exe then Setup it and Change Connection string to this:

OleDbConnection con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\\Excel\\Friends.xls;Extended Properties=Excel 12.0;HDR=YES;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file";
            OleDbCommand com = new OleDbCommand(
               "select name,family from [Sheet1$]",con);
            DataSet ds = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter(com);
            adapter.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];

but I received last error too

plz help me

[Narue]

Try using my class and see if you get the same errors.

[jas2010]

Your code have this error:

'JSW.Utilities.ExcelSpreadsheet.FileName.get' must declare a body because it is NotFiniteNumberException maked abstract or exterm

[Narue]

You're using a compiler older than Visual C# 2008, aren't you?

[jas2010]

ok , I used from Visual C# 2005 but now using Visual C# 2010 and not has any error , now how can i use from your code for Read Excel file , how i call your class ?