searching excel colomns and sheets

[darkocean]

I have a excel file and I want to find what I need the ID which is first colomn. The excel file is 4 sheets and fisrt colomn is ID on them. Problem is this :
I know only and only ID but I dont know this ID which sheet and which row so I must be search it all 4 sheets and rows then I will found it because I need to delete this row.
For example : My Id :123 it is 8th row and 3th sheet so i must be find 3th sheet and find 8th row and will delete...
Thank you for your attend...

[sknake]

Do you want to do this with Excel automation (Interop) or with OleDb?

[darkocean]

OleDb :) dear Sknake

[sknake]

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Windows.Forms;

namespace daniweb
{
  public partial class frmOleDdXlsSearch : Form
  {
    public frmOleDdXlsSearch()
    {
      InitializeComponent();
    }






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

    private void button1_Click(object sender, EventArgs e)
    {
      const string provider = @"System.Data.OleDb";
      //File to search
      const string fName = @"C:\dotnetwin\xlApp\WindowsFormsApplication1\teknoser.xls";
      //Text to search
      string findString = "102500033";

      string connStr = BuildExcelConnectionString(fName, true);
      DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
      using (DbConnection conn = factory.CreateConnection())
      {
        conn.ConnectionString = connStr;
        conn.Open();
        List<string> tables = 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
            tables.Add(tblName);
          }
        }
        DataTable dtColumns = new DataTable();
        tables.Sort();
        foreach (string table in tables)
        {
          string query = string.Format("Select * From [{0}]", table);
          using (DbCommand cmd = factory.CreateCommand())
          {
            cmd.CommandText = query;
            cmd.Connection = conn;

            using (DataTable dtSheet = new DataTable())
            {
              try
              {
                using (DbDataReader dr = cmd.ExecuteReader())
                {
                  dtSheet.Load(dr);
                }
              }
              catch
              {
                //Sometimes they aren't really tables
                continue;
              }
              for (int iRow = 0; iRow < dtSheet.Rows.Count; iRow++)
              {
                for (int iCol = 0; iCol < dtSheet.Columns.Count; iCol++)
                {
                  if (string.Compare(Convert.ToString(dtSheet.Rows[iRow][iCol]), findString, true) == 0)
                  {
                    Console.WriteLine(string.Format("String found in table {0} and column {1} at row {2}",
                      table,
                      dtSheet.Columns[iCol].ColumnName, 
                      iRow));
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Results in

String found in table LEFKOSA$ and column Bildirim at row 0
String found in table TÜMÜ$ and column Bildirim at row 2

[darkocean]

Hello,

Thank you so much, Your help too much support me nad these codes are working correctly. If i try it alone in new windows form application, it is ok but whenever I add these codes in my applicaiton in 477th line, i get an error which is connat access the teknoser.xls file...I attached my applicaiton's code and plese help me...

[sknake]

I can't compile that code, i'm missing a definition for Sistem sistemim = new Sistem();

I do see the problem though:

using (DbConnection conn = factory.CreateConnection())
      {
        conn.ConnectionString = connStr;
        conn.Open();

The conn.Open() actually opens the excel file and locks it. The OleDb provider has the excel file open until the end of that using block on line 523. You need to move the Excel Automation code:

Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(xlsPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

That needs to be after the OleDb connection has been closed and disposed.

[sknake]

..and what does Teknoser mean? Its a funny sounding word to me :)

[darkocean]

Thank you man I will try it, now I setup the windows 7 :)
Teknoser is a company which is our partner in Turkey and We are in Cyprus. We are deal with software development in POS nad windows, mobile applicaitons. I want to know more about you...Thank you so much your helping... ;)

[sknake]

You're welcome and please don't forget to mark this thread as solved if the solution works for you!

Good luck :)

[darkocean]

Hello Sknake,

I could not solve this problem. Sistem class has much of codes but it is irrelevant with TeknoserKapama class. I dont know where I put csharp Syntax (Toggle Plain Text)

using (DbConnection conn = factory.CreateConnection())
      {
      conn.ConnectionString = connStr;
      conn.Open();

Please re-place these code and send me...The codes are very much and my mind is mixed :)

[darkocean]

Hello,

I could not solve this problem, Sistem class has much of codes but it is irrelevant with TeknoserKapama and Excel works. I dont know where I put these codes :

using (DbConnection conn = factory.CreateConnection())
      {
      conn.ConnectionString = connStr;
      conn.Open();

The codes are very much and my mind is mixed :) Please you verify /solve this problem and send this cs file...

By for now

[sknake]

Split it out in to another class:

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

Results in:

Sheet: 'MAGOSA$', Row: 3, Column: 0
Sheet: 'TÜMÜ$', Row: 5, Column: 0

You should just pass the array of ExcelSearchResult[] off to your logic that deletes the rows.

[sknake]

This is also a lesson in NOT sticking all your code in one huge method :P

You want to do this:

private void button3_Click(object sender, EventArgs e)
    {
      ExcelSearchResult[] result;
      using (ExcelSeacher finder = new ExcelSeacher(@"C:\dotnetwin\xlApp\WindowsFormsApplication1\teknoser.xls", true))
      {
        result = finder.FindString("102500035");
      }
      DeleteRows(result);
    }
    private void DeleteRows(ExcelSearchResult[] rows)
    {
      //Excel.Application = new
      //open excel
      foreach (ExcelSearchResult row in rows)
      {
        //excel.activeworkbook = row.Sheet
        //excel.Select(row.Row, row.Col)
        //excel.Delete()
      }
      //excel.close()
    }

[darkocean]

I tried something and delete it but There is some problems. I attached my files. Always excel is working background means windows task manager also sometimes ran.Select(); gives the error. I did not solve this problem :( I want to escape this application anymore :)
also sometimes whenever application runs, it will ask read only :S
thank you for your answers...
Also another logical problem is I want to search 4 sheets whichs are LEFKOŞA,GİRNE,MAGOSA,GÜZELYURT but I dont want to search in TÜMÜ sheet but I use now 2 excel files teknoser and teknoser2 so I believe in solve this problem...

[darkocean]

This is my files...I forget to attachment sorry

[sknake]

I'm going on vacation so I don't know if I can get to it today but i'll try. If I don't, here is what you need to do:

1) Use the ExcelSearcher I posted to get the row results.
* ExcelSearcher has to be disposed after step 1. This can be done with using() or calling .Dispose()
2) Open the Excel file like you're currently doing
3) Select the rows you want and delete them
4) Call a .SaveAs() in excel to a temporary file. You cannot do .SaveAs() and overwrite the file. It will prompt you.
* To see how to disable the prompt please see thread:
http://www.daniweb.com/forums/thread208167.html
5) Close the workbook, Excel application, Close, Dipose, and Null out all of your excel references
* Also shown how to do that in above thread
6) Call a File.Delete(ExistingFile) and File.Move() and overwrite your old file
* Also shown how to do that in above thread

[darkocean]

Have a nice holiday... dont solve this problem, still i am working on this...

[darkocean]

Thank you man I solved this ;)