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.
Mongz -3 Light Poster
zydaru 0 Newbie Poster
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 1,622 Senior Poster Featured Poster
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 -3 Light Poster
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 1,622 Senior Poster Featured Poster
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 -3 Light Poster
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 0 Newbie Poster
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 1,622 Senior Poster Featured Poster
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 -3 Light Poster
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 1,622 Senior Poster Featured Poster
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.
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.