Struggling on reading excel spreadsheet

[Mongz]

Hi Guys, i created an C# application, i need to read an excel spreadsheet called "Graduates.xlsx", i have to search for a specific row on a column called "ID number", once i get that column i must retrieve a record of that row on column "First name". Its been weeks trying this for some weeks.
Any help will be appreciated.

[zydaru]

why don't you get your complete excel sheet in a dataset and from there it should be easy...
i've done that...but I haven't tried it for ".xlsx", just for ".xls"

[sknake]

Here is a class called "Excel Searcher":

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;

namespace daniweb
{
  public struct ExcelSearchResult
  {
    public int Row { get; set; }
    public int Col { get; set; }
    public string SheetName { get; set; }
    public ExcelSearchResult(int Row, int Col, string SheetName)
      : this()
    {
      this.Row = Row;
      this.Col = Col;
      this.SheetName = SheetName;
    }
    public override string ToString()
    {
      return string.Format("Sheet: '{0}', Row: {1:F0}, Column: {2:F0}",
        this.SheetName,
        this.Row,
        this.Col);
    }
  }

  public class ExcelSeacher : IDisposable
  {
    private const string provider = @"System.Data.OleDb";
    private string _fileName;
    private string _connectionString;
    private bool _firstRowContainsColHeader;
    private DbProviderFactory factory;
    private DbConnection conn;

    public string FileName
    {
      get { return _fileName; }
    }
    public string ConnectionString
    {
      get { return _connectionString; }
    }
    public bool FirstRowContainsColHeader
    {
      get { return _firstRowContainsColHeader; }
    }
    //
    #region ctors
    private ExcelSeacher()
    {
      factory = DbProviderFactories.GetFactory(provider);
      conn = factory.CreateConnection();
    }
    public ExcelSeacher(string FileName)
      : this(FileName, true)
    {
    }
    public ExcelSeacher(string FileName, bool FirstRowContainsColumnHeader)
      :this()
    {
      if (!File.Exists(FileName))
        throw new FileNotFoundException("File not found", FileName);

      this._fileName = FileName;
      this._connectionString = BuildExcelConnectionString(FileName, FirstRowContainsColumnHeader);
      this._firstRowContainsColHeader = FirstRowContainsColumnHeader;
      conn.ConnectionString = this._connectionString;
      conn.Open();
    }
    #endregion
    private string[] GetTableList()
    {
      List<string> result = new List<string>();
      using (DataTable dtSchema = conn.GetSchema("Tables"))
      {
        for (int i1 = 0; i1 < dtSchema.Rows.Count; i1++)
        {
          string tblName = Convert.ToString(dtSchema.Rows[i1]["TABLE_NAME"]);
          string tblType = Convert.ToString(dtSchema.Rows[i1]["TABLE_TYPE"]);
          if ((string.Compare(tblType, "system", true) == 0) || (string.Compare(tblType, "access table", true) == 0))
            continue; //not a user table
          result.Add(tblName);
        }
      }
      return result.ToArray();
    }
  

    public ExcelSearchResult[] FindString(string TextToFind)
    {
      List<ExcelSearchResult> result = new List<ExcelSearchResult>();

      string[] tableList = GetTableList();
      foreach (string tbl in tableList)
      {
        DataTable dt = GetTable(tbl);
        if (dt == null)
          continue;
        using (dt)
        {
          for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
          {
            for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
            {
              if (string.Compare(Convert.ToString(dt.Rows[iRow][iCol]), TextToFind, true) == 0)
              {
                /*
                 * Excel starts counting from 1, not zero. So we need to increate it by 1.
                 * Additionaly we need to increase it by another 1 if the first row contains
                 * column headers because in that case, the first row doesn't show up in
                 * dt.Rows
                 * 
                 */
                if (this.FirstRowContainsColHeader)
                  result.Add(new ExcelSearchResult(iRow+2, iCol, tbl));
                else
                  result.Add(new ExcelSearchResult(iRow+1, iCol, tbl));
              }
            }
          }
        }
      }

      return result.ToArray();
    }

    private DbCommand GetCommand()
    {
      DbCommand cmd = factory.CreateCommand();
      cmd.Connection = conn;
      return cmd;
    }

    private DataTable GetTable(string TableName)
    {
      try
      {
        string query = string.Format("Select * From [{0}]", TableName);
        using (DbCommand cmd = GetCommand())
        {
          cmd.CommandText = query;
          using (DbDataReader dr = cmd.ExecuteReader())
          {
            DataTable result = new DataTable();
            result.Load(dr);
            return result;
          }
        }
      }
      catch
      {
        return default(DataTable);
      }
    }

    #region IDisposable Members
    public void Dispose()
    {
      if (conn != null)
      {
        conn.Dispose();
        conn = null;
      }
    }
    #endregion

    private static string BuildExcelConnectionString(string Filename, bool FirstRowContainsHeaders)
    {
      return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=\"Excel 8.0;HDR={1};\"",
                          Filename.Replace("'", "''"),
                          FirstRowContainsHeaders ? "Yes" : "No");
    }
  }
}

Calling it:

private void button2_Click(object sender, EventArgs e)
    {
      ExcelSearchResult[] result;
      using (ExcelSeacher finder = new ExcelSeacher(@"C:\dotnetwin\xlApp\WindowsFormsApplication1\teknoser.xls", true))
      {
        result = finder.FindString("102500035");
      }
      foreach (ExcelSearchResult r in result)
        Console.WriteLine(r.ToString());
      System.Diagnostics.Debugger.Break();
    }

[Mongz]

On this method i recieve an exception : "Invalid operation. The connection is closed."
#endregion

private string[] GetTableList()    
 {      
            List<string> result = new List<string>();
            using (DataTable dtSchema = conn.GetSchema("Tables"))
            {        
                for (int i1 = 0; i1 < dtSchema.Rows.Count; i1++)
            {          
                    string tblName = Convert.ToString(dtSchema.Rows[i1]["TABLE_NAME"]);
                    string tblType = Convert.ToString(dtSchema.Rows[i1]["TABLE_TYPE"]); 
                    if ((string.Compare(tblType, "system", true) == 0) ||      (string.Compare(tblType, "access table", true) == 0))
                   continue; //not a user table          result.Add(tblName);
              }      
          }  
      return result.ToArray();
}

And i changed path of the file to the following

 protected void Button1_Click(object sender, EventArgs e)
        {
            Business.ExcelSearchResult[] result;
            using (Business.ExcelSeacher finder = new Business.ExcelSeacher(@"C:\Data\Graduates.xls", true))
            {
                result = finder.FindString("8009105987083"); 
            }
            foreach (Business.ExcelSearchResult r in result) 
                MessageBox.Show(r.ToString());
            System.Diagnostics.Debugger.Break();
        }

please give some help, forgive me im still poor in programming

[sknake]

Please use code tags when posting code on daniweb:

Next look at the commend I added in your code:

protected void Button1_Click(object sender, EventArgs e)
{
Business.ExcelSearchResult[] result;
using (Business.ExcelSeacher finder = new Business.ExcelSeacher(@"C:\Data\Graduates.xls", true))
{
result = finder.FindString("8009105987083"); 
} //The excel sheet is closed here!
foreach (Business.ExcelSearchResult r in result) 
MessageBox.Show(r.ToString());
System.Diagnostics.Debugger.Break();
}

Finally, this code should work:

protected void Button1_Click(object sender, EventArgs e)
{
Business.ExcelSearchResult[] result;
using (Business.ExcelSeacher finder = new Business.ExcelSeacher(@"C:\Data\Graduates.xls", true))
{
  result = finder.FindString("8009105987083"); 
  foreach (Business.ExcelSearchResult r in result) 
  {
    MessageBox.Show(r.ToString());
    System.Diagnostics.Debugger.Break();
  }
}
} //The excel sheet is closed here!

You need to access the excel sheets before you close the connection, which is done at the commented line because IDisposable.Dispose() is called on the ExcelSearcher

[Mongz]

Can you please review the code becouse its not working for me.

[

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Windows.Forms;
namespace Business
{
public struct ExcelSearchResult
{
public int Row { get; set; }
public int Col { get; set; }
public string SheetName { get; set; }
public ExcelSearchResult(int Row, int Col, string SheetName): this()
{
this.Row = Row;
this.Col = Col;
this.SheetName = SheetName;
}
public override string ToString()
{
return string.Format("Sheet: '{0}', Row: {1:F0}, Column: {2:F0}", this.SheetName, this.Row, this.Col);
}
}
public class ExcelSeacher : IDisposable
{
private const string provider = @"System.Data.OleDb";
private string _fileName;
private string _connectionString;
private bool _firstRowContainsColHeader;
private DbProviderFactory factory;
private DbConnection conn;
public string FileName
{
get { return _fileName; }
}
public string ConnectionString
{
get { return _connectionString; }
}
public bool FirstRowContainsColHeader
{
get { return _firstRowContainsColHeader; }
}
//
#region ctors
private ExcelSeacher()
{
factory = DbProviderFactories.GetFactory(provider);
conn = factory.CreateConnection();
}
public ExcelSeacher(string FileName): this(FileName, true)
{
}
public ExcelSeacher(string FileName, bool FirstRowContainsColumnHeader) :this()
{
try
{
if (!File.Exists(FileName))
throw new FileNotFoundException("File not found", FileName);
this._fileName = FileName;
this._connectionString = BuildExcelConnectionString(FileName, FirstRowContainsColumnHeader);
this._firstRowContainsColHeader = FirstRowContainsColumnHeader;
conn.ConnectionString = this._connectionString; conn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
#endregion
private string[] GetTableList()
{
List<string> result = new List<string>();
try
{
using (DataTable dtSchema = conn.GetSchema("Tables"))
{
for (int i1 = 0; i1 < dtSchema.Rows.Count; i1++)
{
string tblName = Convert.ToString(dtSchema.Rows[i1]["TABLE_NAME"]);
string tblType = Convert.ToString(dtSchema.Rows[i1]["TABLE_TYPE"]);
if ((string.Compare(tblType, "system", true) == 0) || (string.Compare(tblType, "access table", true) == 0))
continue; //not a user table          result.Add(tblName);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return result.ToArray();
}
public ExcelSearchResult[] FindString(string TextToFind)
{
List<ExcelSearchResult> result = new List<ExcelSearchResult>();
string[] tableList = GetTableList();
foreach (string tbl in tableList)
{
DataTable dt = GetTable(tbl);
if (dt == null)
continue;
using (dt)
{
for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
{
for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
{
if (string.Compare(Convert.ToString(dt.Rows[iRow][iCol]), TextToFind, true) == 0)
{
/*
* * Excel starts counting from 1, not zero. So we need to increate it by 1.
* * Additionaly we need to increase it by another 1 if the first row contains
* * column headers because in that case, the first row doesn't show up in
* * dt.Rows
* *
* */
if (this.FirstRowContainsColHeader)
result.Add(new ExcelSearchResult(iRow+2, iCol, tbl));
else
result.Add(new ExcelSearchResult(iRow+1, iCol, tbl));
}
}
}
}
}
return result.ToArray();
}
private DbCommand GetCommand()
{
DbCommand cmd = factory.CreateCommand();
cmd.Connection = conn;
return cmd;
}
private DataTable GetTable(string TableName)
{
try
{
string query = string.Format("Select * From [{0}]", TableName);
using (DbCommand cmd = GetCommand())
{
cmd.CommandText = query;
using (DbDataReader dr = cmd.ExecuteReader())
{
DataTable result = new DataTable();
result.Load(dr);
return result;
}
}
}
catch
{
return default(DataTable);
}
}
#region IDisposable Members
public void Dispose()
{
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
#endregion
private static string BuildExcelConnectionString(string Filename, bool FirstRowContainsHeaders)
{
return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=\"Excel 8.0;HDR={1};\"",Filename.Replace("'", "''"),FirstRowContainsHeaders ? "Yes" : "No");
}
}
}

Then this is behind the button. The file is on c drive inside folder called Data. I recieve an exception " File not found" and "connectionstring not open.

protected void Button1_Click(object sender, EventArgs e)
{
Business.ExcelSearchResult[] result;
using (Business.ExcelSeacher finder = new Business.ExcelSeacher(@"C:\Data\Graduates.xls", true))
{
result = finder.FindString("8009105987083");
foreach (Business.ExcelSearchResult r in result)
{
MessageBox.Show(r.ToString());
System.Diagnostics.Debugger.Break();
}
}
}

Your kindness is really appreciated

[zydaru]

private DataTable getDataFromXLS(string strFilePath)
{
string strConnectionString = "";
                    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                                     "Data Source=" + strFilePath + "; Jet OLEDB:Engine Type=5;" +
                                                     "Extended Properties=Excel 8.0;";
                    OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
                    cnCSV.Open();
                    OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [Sheet1$]", cnCSV);
                    OleDbDataAdapter daCSV = new OleDbDataAdapter(); daCSV.SelectCommand = cmdSelect;
                    DataTable dtCSV = new DataTable();
                    daCSV.Fill(dtCSV);
                    cnCSV.Close();
                    daCSV = null;
                    return dtCSV;
}[/CODE=csharp]

and from a DataTable you can do whatever you want ?[CODE=csharp]private DataTable getDataFromXLS(string strFilePath)
{
string strConnectionString = "";
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strFilePath + "; Jet OLEDB:Engine Type=5;" +
"Extended Properties=Excel 8.0;";
OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
cnCSV.Open();
OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [Sheet1$]", cnCSV);
OleDbDataAdapter daCSV = new OleDbDataAdapter(); daCSV.SelectCommand = cmdSelect;
DataTable dtCSV = new DataTable();
daCSV.Fill(dtCSV);
cnCSV.Close();
daCSV = null;
return dtCSV;
}[/CODE=csharp]

and from a DataTable you can do whatever you want ?

[sknake]

Ah I lied -- and gave you the incorrect call. The problem was that I gave you a connection string for ".xls" -- and not ".xlsx". Also the code I gave you only did _exact_ matches. I have changed it to work with .xls and .xlsx, as well as partial matches. Please see this code:

Excel search:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;

namespace daniweb
{
  public struct ExcelSearchResult
  {
    public int Row { get; set; }
    public int Col { get; set; }
    public string SheetName { get; set; }
    public ExcelSearchResult(int Row, int Col, string SheetName)
      : this()
    {
      this.Row = Row;
      this.Col = Col;
      this.SheetName = SheetName;
    }
    public override string ToString()
    {
      return string.Format("Sheet: '{0}', Row: {1:F0}, Column: {2:F0}",
        this.SheetName,
        this.Row,
        this.Col);
    }
  }

  public class ExcelSeacher : IDisposable
  {
    private const string provider = @"System.Data.OleDb";
    private string _fileName;
    private string _connectionString;
    private bool _firstRowContainsColHeader;
    private DbProviderFactory factory;
    private DbConnection conn;

    public string FileName
    {
      get { return _fileName; }
    }
    public string ConnectionString
    {
      get { return _connectionString; }
    }
    public bool FirstRowContainsColHeader
    {
      get { return _firstRowContainsColHeader; }
    }
    //
    #region ctors
    private ExcelSeacher()
    {
      factory = DbProviderFactories.GetFactory(provider);
      conn = factory.CreateConnection();
    }
    public ExcelSeacher(string FileName)
      : this(FileName, true)
    {
    }
    public ExcelSeacher(string FileName, bool FirstRowContainsColumnHeader)
      :this()
    {
      if (!File.Exists(FileName))
        throw new FileNotFoundException("File not found", FileName);

      string fileExt = Path.GetExtension(FileName);


      this._fileName = FileName;

      if (string.Compare(fileExt, ".xls", true) == 0)
        this._connectionString = BuildExcelConnectionString(FileName, FirstRowContainsColumnHeader);
      else if (string.Compare(fileExt, ".xlsx", true) == 0)
        this._connectionString = BuildExcel2007ConnectionString(FileName, FirstRowContainsColumnHeader);
      else
        throw new InvalidOperationException("Unknown file extension");
      
      this._firstRowContainsColHeader = FirstRowContainsColumnHeader;
      conn.ConnectionString = this._connectionString;
      conn.Open();
    }
    #endregion
    private string[] GetTableList()
    {
      List<string> result = new List<string>();
      using (DataTable dtSchema = conn.GetSchema("Tables"))
      {
        for (int i1 = 0; i1 < dtSchema.Rows.Count; i1++)
        {
          string tblName = Convert.ToString(dtSchema.Rows[i1]["TABLE_NAME"]);
          string tblType = Convert.ToString(dtSchema.Rows[i1]["TABLE_TYPE"]);
          if ((string.Compare(tblType, "system", true) == 0) || (string.Compare(tblType, "access table", true) == 0))
            continue; //not a user table
          result.Add(tblName);
        }
      }
      return result.ToArray();
    }
  
    /// <summary>
    /// 
    /// </summary>
    /// <param name="TextToFind">Search term</param>
    /// <param name="ExactMatch">Match all of the cell contents</param>
    /// <param name="IgnoreCase">Whether or not the case should be ignored</param>
    /// <returns></returns>
    public ExcelSearchResult[] FindString(string TextToFind, bool ExactMatch, bool IgnoreCase)
    {
      List<ExcelSearchResult> result = new List<ExcelSearchResult>();

      string[] tableList = GetTableList();
      foreach (string tbl in tableList)
      {
        DataTable dt = GetTable(tbl);
        if (dt == null)
          continue;
        using (dt)
        {
          for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
          {
            for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
            {
              string cellData = Convert.ToString(dt.Rows[iRow][iCol]);
              if (cellData == null)
                cellData = string.Empty;

              bool matched;

              if (ExactMatch)
                matched = (string.Compare(cellData, TextToFind, IgnoreCase) == 0);
              else
                matched = (cellData.IndexOf(TextToFind, (IgnoreCase ? StringComparison.CurrentCultureIgnoreCase : StringComparison.CurrentCulture)) >= 0);

              if (matched)
              {
                /*
                 * Excel starts counting from 1, not zero. So we need to increate it by 1.
                 * Additionaly we need to increase it by another 1 if the first row contains
                 * column headers because in that case, the first row doesn't show up in
                 * dt.Rows
                 * 
                 */
                if (this.FirstRowContainsColHeader)
                  result.Add(new ExcelSearchResult(iRow+2, iCol, tbl));
                else
                  result.Add(new ExcelSearchResult(iRow+1, iCol, tbl));
              }
            }
          }
        }
      }

      return result.ToArray();
    }

    private DbCommand GetCommand()
    {
      DbCommand cmd = factory.CreateCommand();
      cmd.Connection = conn;
      return cmd;
    }

    private DataTable GetTable(string TableName)
    {
      try
      {
        string query = string.Format("Select * From [{0}]", TableName);
        using (DbCommand cmd = GetCommand())
        {
          cmd.CommandText = query;
          using (DbDataReader dr = cmd.ExecuteReader())
          {
            DataTable result = new DataTable();
            result.Load(dr);
            return result;
          }
        }
      }
      catch
      {
        return default(DataTable);
      }
    }

    #region IDisposable Members
    public void Dispose()
    {
      if (conn != null)
      {
        conn.Dispose();
        conn = null;
      }
    }
    #endregion

    private static string BuildExcelConnectionString(string Filename, bool FirstRowContainsHeaders)
    {
      return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=\"Excel 8.0;HDR={1};\"",
                          Filename.Replace("'", "''"),
                          FirstRowContainsHeaders ? "Yes" : "No");
    }
    public static string BuildExcel2007ConnectionString(string Filename, bool FirstRowContainsHeaders)
    {
      return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR={1}\";",
                            Filename.Replace("'", "''"),
                            FirstRowContainsHeaders ? "Yes" : "No");
    }
  }
}

Calling it:

private void button4_Click(object sender, EventArgs e)
    {
      ExcelSearchResult[] result;
      using (ExcelSeacher finder = new ExcelSeacher(@"C:\data\Spreadsheet.xls", true))
      {
        result = finder.FindString("o", false, true);
      }
      foreach (ExcelSearchResult r in result)
      {
        Console.WriteLine(r.ToString());
      }
    }

[Mongz]

Now the code can read but bit complicated to make sum changes becouse it returns : "Sheet: 'Sheet1$',Row:3, Column:0", "Sheet: 'Sheet1$',Row:3, Column:0" and "Sheet: 'Sheet1$',Row:3, Column:0" then it terminates. Cant read the cell i want

[sknake]

Yes that code shows you how to search every cell.. change the excel searcher to grab the other column and stick the information you want in the result[] array it returns.