DaniWeb Forum Index > Software Development > C#

C# RSS

Struggling on reading excel spreadsheet

Please support our C# advertiser: Intel Parallel Studio Home

•

Join Date: Apr 2009

Posts: 36

Reputation: Mongz is an unknown quantity at this point

Solved Threads: 0

Mongz

Offline

Light Poster

Struggling on reading excel spreadsheet

Sep 7th, 2009

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.

•

Join Date: Aug 2009

Posts: 8

Reputation: zydaru is an unknown quantity at this point

Solved Threads: 0

zydaru

Offline

Newbie Poster

Re: Struggling on reading excel spreadsheet

Sep 7th, 2009

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"

•

Join Date: Feb 2009

Posts: 3,215

Reputation: sknake has much to be proud of

Solved Threads: 573

sknake

Offline

.NET Enthusiast

Re: Struggling on reading excel spreadsheet

Sep 7th, 2009

Here is a class called "Excel Searcher":

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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");
    }
  }
}
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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
    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();
    }
    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();
    }

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Apr 2009

Posts: 36

Reputation: Mongz is an unknown quantity at this point

Solved Threads: 0

Mongz

Offline

Light Poster

Re: Struggling on reading excel spreadsheet

Sep 7th, 2009

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

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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();
        }
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

Last edited by John A; Sep 7th, 2009 at 1:29 pm. Reason: added code tags

•

Join Date: Feb 2009

Posts: 3,215

Reputation: sknake has much to be proud of

Solved Threads: 573

sknake

Offline

.NET Enthusiast

Re: Struggling on reading excel spreadsheet

Sep 7th, 2009

Please use code tags when posting code on daniweb:

[code=csharp]
...code here....
[/code]

Next look at the commend I added in your code:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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();
}
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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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!
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

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Apr 2009

Posts: 36

Reputation: Mongz is an unknown quantity at this point

Solved Threads: 0

Mongz

Offline

Light Poster

Re: Struggling on reading excel spreadsheet

Sep 7th, 2009

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

•

Join Date: Aug 2009

Posts: 8

Reputation: zydaru is an unknown quantity at this point

Solved Threads: 0

zydaru

Offline

Newbie Poster

Re: Struggling on reading excel spreadsheet

Sep 7th, 2009

[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 ?

•

Join Date: Feb 2009

Posts: 3,215

Reputation: sknake has much to be proud of

Solved Threads: 573

sknake

Offline

.NET Enthusiast

Re: Struggling on reading excel spreadsheet

Sep 7th, 2009

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:

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
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");
    }
  }
}
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:

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
    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());
      }
    }
    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());
      }
    }

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Apr 2009

Posts: 36

Reputation: Mongz is an unknown quantity at this point

Solved Threads: 0

Mongz

Offline

Light Poster

Re: Struggling on reading excel spreadsheet

Sep 7th, 2009

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

•

Join Date: Feb 2009

Posts: 3,215

Reputation: sknake has much to be proud of

Solved Threads: 573

sknake

Offline

.NET Enthusiast

Re: Struggling on reading excel spreadsheet

#10

Sep 7th, 2009

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.

Scott Knake
Custom Software Development
Apex Software, Inc.

Thread Tools	Search this Thread
Show Printable Version Email this Page

DaniWeb Message

Cancel Changes