i have imported my excel into dataset ds, RSS

• • Reply
  

  sssstud

  Member

  40 Points

  53 Posts

  i have imported my excel into dataset ds,

  Feb 29, 2012 08:04 AM|LINK

   now i want to create datatable from datset ds or these value can iserted into any table ... please help me guys..  i want to create a tabel from returned dataset ds ;

      string Patfilename = System.Web.HttpContext.Current.Server.MapPath("Book1.xls");
  
      protected void Page_Load(object sender, EventArgs e)
      {
          GetExcel(Patfilename);
      }
     
  
      public DataSet GetExcel(string fileName)
      {
         
          Application oXL;
          Workbook oWB;
          Worksheet oSheet;
          Range oRng;
          try
          {
              //  creat a Application object
              oXL = new ApplicationClass();
              //   get   WorkBook  object
              oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value);
  
              //   get   WorkSheet object  
              oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
              System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
              DataSet ds = new DataSet();
              ds.Tables.Add(dt);
              DataRow dr;
  
              StringBuilder sb = new StringBuilder();
              int jValue = oSheet.UsedRange.Cells.Columns.Count;
              int iValue = oSheet.UsedRange.Cells.Rows.Count;
              //  get data columns
              for (int j = 1; j <= jValue; j++)
              {
                  dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
              }
  
              //string colString = sb.ToString().Trim();
              //string[] colArray = colString.Split(':');
  
              //  get data in cell
              for (int i = 1; i <= iValue; i++)
              {
                  dr = ds.Tables["dtExcel"].NewRow();
                  for (int j = 1; j <= jValue; j++)
                  {
                      oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
                      string strValue = oRng.Text.ToString();
                      dr["column" + j] = strValue;
                  }
                  ds.Tables["dtExcel"].Rows.Add(dr);
  
              }
  
             
           return ds;
              
          }
          catch (Exception ex)
          {
              Label1.Text = "Error: ";
              Label1.Text += ex.Message.ToString();
              return null;
          }
         
      }

  Thanks ,
  
  
  • Edited by sssstud on Feb 29, 2012 08:05 AM
  • Edited by sssstud on Feb 29, 2012 08:06 AM
  • Edited by sssstud on Feb 29, 2012 08:06 AM
  • Edited by sssstud on Feb 29, 2012 08:06 AM
• • Reply
  

  ramanselva

  Contributor

  2064 Points

  324 Posts

  Re: i have imported my excel into dataset ds,

  Feb 29, 2012 08:13 AM|LINK

  Use SqlBulk Copy to write datatable into SqlServer database..

  //First create a connection string to destination database
  string connectionString;
  connectionString = YourConnectionStringand
  Initial Catalog=TestSMODatabase";
  
  //Open a connection with destination database;
  using (SqlConnection connection = 
         new SqlConnection(connectionString))
  {
     connection.Open();
  
     //Open bulkcopy connection.
     using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
     {
     //Set destination table name
  //to table previously created.
  bulkcopy.DestinationTableName = "dbo.TestTable";
  
  try
  {
     bulkcopy.WriteToServer(ds.Tables["dtExcel"]);
  }
  catch (Exception ex)
  {
     Console.WriteLine(ex.Message);
  }
  
  connection.Close();
     }
  }
  

  Please remember to click “Mark as Answer” on the post that helps you.
  This can be beneficial to other community members reading the thread.
  
  Regards,
  Rama Selvam M.
  • Marked as answer by sssstud on Feb 29, 2012 10:10 AM
• • Reply
  

  sssstud

  Member

  40 Points

  53 Posts

  Re: i have imported my excel into dataset ds,

  Feb 29, 2012 10:16 AM|LINK

  itsss works for me thanks very much ... but there is problem in my code ... when i m generating columns name from Excel ,its just bulkcopying on table which all cloumns are varchar .

   //  get data columns
              for (int j = 1; j <= jValue; j++)
              {
                  dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
              }

  By this its just taking only columns which type is string . how can i do this weather its varchar or int .

  Bytheway weather u replied it or not ... still god bless ya ..bcoz u solved my last problem .

  Thanks ,
  
  
• • Reply
  

  ramanselva

  Contributor

  2064 Points

  324 Posts

  Re: i have imported my excel into dataset ds,

  Feb 29, 2012 11:10 AM|LINK

  Hi,
        First you defined the table with schema

  then start to insert table 
           
                   DataTable table = new DataTable();
  	table.Columns.Add("Dosage", typeof(int));
  	table.Columns.Add("Drug", typeof(string));
  	table.Columns.Add("Patient", typeof(string));
  	table.Columns.Add("Date", typeof(DateTime));
  
  

  Please remember to click “Mark as Answer” on the post that helps you.
  This can be beneficial to other community members reading the thread.
  
  Regards,
  Rama Selvam M.