hi all friend

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

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?

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);
			}

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?

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

hi

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

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
    }
}

Edited 5 Years Ago by Narue: n/a

Comments
:)

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

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.

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

Edited 5 Years Ago by jas2010: n/a

Your code have this error:

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

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 ?

This article has been dead for over six months. Start a new discussion instead.