Offline
Unsolved 
IDisposable.Dispose() is called on the ExcelSearcher Offline 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");
}
}
}
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();
}
}
}
Offline | DaniWeb Message | |
| Cancel Changes | |
