locked
C# code snippet RRS feed

  • Question

  • Below is the function which parses the data(StringID,StringName,data, comment) from excel & fills the list

    public interface DataCol
    
    
    
     {
    
    
    
      int ID;
    
    
    
      string Name;
    
    
    
      string data;
    
    
    
      string Comment;
    
    
    
     }
    
    
    
     public List<DataCol> ParseFile(string fileName)
    
    
    
     {
    
    
    
      List<DataCol> s = new List<DataCol>();
    
    
    
      string columnName = string.Empty;
    
    
    
    
    
    
    
    
    
    
    
      OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Excel 12.0;HDR=YES;");
    
    
    
      oleConn.Open();
    
    
    
      DataTable dt = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    
    
    
      string[] excelSheets = new string[dt.Rows.Count];
    
    
    
      int i = 0;
    
    
    
      foreach (DataRow row in dt.Rows)
    
    
    
      {
    
    
    
      excelSheets[i] = row["TABLE_NAME"].ToString();
    
    
    
      i++;
    
    
    
      }
    
    
    
      Type colType;
    
    
    
    
    
    
    
      for (int ec = 0; ec < excelSheets.Count(); ec++)
    
    
    
      {
    
    
    
      string SelectText = "select * from [" + excelSheets[ec] + "]";
    
    
    
      OleDbDataAdapter adp = new OleDbDataAdapter(SelectText, oleConn);
    
    
    
      DataSet ds2 = new DataSet();
    
    
    
      adp.Fill(ds2);
    
    
    
      if (0 != ds2.Tables.Count)
    
    
    
      {
    	

    DataRowCollection

     

    drc = ds2.Tables[0].Rows;

    foreach (DataRow dr in drc) { s.Add(new DataCol(Convert.ToInt32(dr.Field<Double>("StringID")), dr.Field<string>("StringName"), dr.Field<string>("data"), dr.Field<string>("Comment"))); } } } return s; }

    How can i optimize it(ParseFile function) still further??? 

    • Edited by Sruthi M Wednesday, April 28, 2010 6:10 AM
    Wednesday, April 28, 2010 5:59 AM

Answers

  • Thank you. I got it now.

    In my opinion you can make it more readable and reliable as below.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.OleDb;
    using System.Data;
    
    namespace ConsoleApplication1
    {
      class Program
      {
        static void Main(string[] args)
        {
        }
        public List<DataCol> ParseFile(string fileName)
        {
          List<DataCol> s = new List<DataCol>();
          string columnName = string.Empty;
          using (OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Excel 12.0;HDR=YES;"))
          {
            oleConn.Open();
            DataTable excelSheets = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            foreach (DataRow excelSheet in excelSheets.Rows)
            {
              using (OleDbDataAdapter adp = new OleDbDataAdapter(string.Format("select * from [{0}]", excelSheet["TABLE_NAME"]), oleConn))
              {
                DataTable dt = new DataTable();
                adp.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                  s.Add(new DataCol(Convert.ToInt32(dr.Field<Double>("StringID")), dr.Field<string>("StringName"), dr.Field<string>("data"), dr.Field<string>("Comment")));
                }
    
              }
            }
          }
          return s;
        }
      }
      public class DataCol
      {
        int ID;
        string Name;
        string data;
        string Comment;
        public DataCol(int ID, string Name, string data, string Comment)
        {
          this.ID = ID;
          this.Name = Name;
          this.data = data;
          this.Comment = Comment;
        }
      }
    }
    

    Sincerely,

    Yasser.


    Don't be stickler and wine with William Shakespeare after the solution :^)
    "And this our life, exempt from public haunt, finds tongues in trees, books in the running brooks, sermons in stones, and good in everything." William Shakespeare
    • Marked as answer by Sruthi M Wednesday, April 28, 2010 6:53 AM
    Wednesday, April 28, 2010 6:26 AM

All replies

  • Sorry, what is the drc? I can't see it's declaration!
    Don't be stickler and wine with William Shakespeare after the solution :^)
    "And this our life, exempt from public haunt, finds tongues in trees, books in the running brooks, sermons in stones, and good in everything." William Shakespeare
    Wednesday, April 28, 2010 6:07 AM
  • DataRowCollection

     

    drc = ds2.Tables[0].Rows;

    updated this in the above snippet

    Wednesday, April 28, 2010 6:10 AM
  • Thank you. I got it now.

    In my opinion you can make it more readable and reliable as below.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.OleDb;
    using System.Data;
    
    namespace ConsoleApplication1
    {
      class Program
      {
        static void Main(string[] args)
        {
        }
        public List<DataCol> ParseFile(string fileName)
        {
          List<DataCol> s = new List<DataCol>();
          string columnName = string.Empty;
          using (OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Excel 12.0;HDR=YES;"))
          {
            oleConn.Open();
            DataTable excelSheets = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            foreach (DataRow excelSheet in excelSheets.Rows)
            {
              using (OleDbDataAdapter adp = new OleDbDataAdapter(string.Format("select * from [{0}]", excelSheet["TABLE_NAME"]), oleConn))
              {
                DataTable dt = new DataTable();
                adp.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                  s.Add(new DataCol(Convert.ToInt32(dr.Field<Double>("StringID")), dr.Field<string>("StringName"), dr.Field<string>("data"), dr.Field<string>("Comment")));
                }
    
              }
            }
          }
          return s;
        }
      }
      public class DataCol
      {
        int ID;
        string Name;
        string data;
        string Comment;
        public DataCol(int ID, string Name, string data, string Comment)
        {
          this.ID = ID;
          this.Name = Name;
          this.data = data;
          this.Comment = Comment;
        }
      }
    }
    

    Sincerely,

    Yasser.


    Don't be stickler and wine with William Shakespeare after the solution :^)
    "And this our life, exempt from public haunt, finds tongues in trees, books in the running brooks, sermons in stones, and good in everything." William Shakespeare
    • Marked as answer by Sruthi M Wednesday, April 28, 2010 6:53 AM
    Wednesday, April 28, 2010 6:26 AM