none
VSTO C# RRS feed

  • Question

  • Hey I am using Excel-Addins.There are two buttons in ribbon 1 is show,which displays the table from database.2 one is update.

    I want database table to be updated when i update in excelsheet.

    When I update the contents of table in excel sheet and press update button it has to get updated in excelsheet as well as in database table.

    Plz  send the code.

    Thursday, May 26, 2011 12:14 PM

All replies

  • Hope this helps. I can study this part due to your question, thanks.

    and I think below source in this post is better than previous source I wrote on.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Windows.Forms;
    
    namespace ExcelAddIn1
    {
      public partial class Ribbon1 
      {
        Excel.Worksheet wrk;
        Microsoft.Office.Tools.Excel.Worksheet test;
        Microsoft.Office.Tools.Excel.ListObject li;
        northwindDataContext nor = new northwindDataContext();
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {
          wrk = Globals.ThisAddIn.Application.ActiveSheet;
          test = Globals.Factory.GetVstoObject(wrk);
          
          
        }
    
        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
          li = test.Controls.AddListObject(wrk.Range["a1", "a2"], "test");
          var query = from c in nor.Categories
                select c;
    
          li.DataSource = query.ToList();      
        }
    
        private void button2_Click(object sender, RibbonControlEventArgs e)
        {
          li.DefaultDataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged;
          
          nor.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);
        }
      }
    }
    

     

    • Edited by VSTO_Beginner Friday, May 27, 2011 1:44 PM Source Update
    Thursday, May 26, 2011 2:04 PM
  • No these links are not usefull
    Friday, May 27, 2011 6:26 AM
  • Sorry.Refer to below Source.

    it is similar way with Ribbon.

    1. Linq to sql

    2. Display Table  or Use Listobject(better than this source)

        private void button1_Click(object sender, EventArgs e)
        {
          var query = from c in db.발주
                join o in db.업체 on c.ID equals o.발주id into z
                from o in z.DefaultIfEmpty()
                where c.공종ID == Convert.ToInt32(공id.Text.ToString())
                select new
                {
                  id = c.ID,
                  품명 = c.품명,
                  규격 = c.규격,
                  수량 = c.수량,
                  재료비 = o.재료비,
                  노무비 = o.노무비,
                  경비 = o.경비
                };
    
          int i = 5;
          //list1.AutoSetDataBoundColumnHeaders = true;
          //list1.DataSource = query.ToList();
    
          foreach (var c in query)
          {
            this.Range["a" + i, missing].Value2 = c.id;
            this.Range["a" + i, missing].get_Offset(0, 1).Value2 = c.품명;
            this.Range["a" + i, missing].get_Offset(0, 2).Value2 = c.규격;
            this.Range["a" + i, missing].get_Offset(0, 3).Value2 = c.수량;
            this.Range["a" + i, missing].get_Offset(0, 4).Value2 = c.재료비;
            this.Range["a" + i, missing].get_Offset(0, 5).Value2 = c.노무비;
            this.Range["a" + i, missing].get_Offset(0, 6).Value2 = c.경비;
    
            i++;
          }
        }
    

    3. Update  : I think below source is not good. I'm contemplating how to update as well.

    I think if you use Listobject, that will be easier to update.

        private void button2_Click(object sender, EventArgs e)
        {
          int i = 5;
          while (this.Range["a" + i, missing].Value2 != null)
          {
            bool cnt = db.업체.Any(s => s.발주id == Convert.ToInt32(this.Range["a" + i, missing].Value2.ToString()));
    
            if (cnt == true)
            {
              //Update
              var query = db.업체.Single(s => s.발주id == Convert.ToInt32(this.Range["a" + i, missing].Value2.ToString()));
              query.업체id = Convert.ToInt32(cat.Text);
              query.발주id = Convert.ToInt32(this.Range["a" + i, missing].Text);
              query.공종ID = Convert.ToInt32(공id.Text);
              query.업체명 = 업체명.Text.ToString();
              query.재료비 = Convert.ToDouble(this.Range["a" + i, missing].get_Offset(0, 4).Text);
              query.노무비 = Convert.ToDouble(this.Range["a" + i, missing].get_Offset(0, 5).Text);
              query.경비 = this.Range["a" + i, missing].get_Offset(0, 6).Value2 == null ?
                0 : Convert.ToDouble(this.Range["a" + i, missing].get_Offset(0, 6).Text);
              //query.경비 = this.Range["a" + i, missing].get_Offset(0, 6).Text.ToString().Length > 0 ? Convert.ToDouble(this.Range["a" + i, missing].get_Offset(0, 6).Text) : 0; //Convert.ToDouble((this.Range["a" + i, missing].get_Offset(0, 6).Value2 = 0).ToString());
            }
    
            else
            {
              //Add
              업체 dd = new 업체();
              dd.업체id = Convert.ToInt32(cat.Text);
              dd.발주id = Convert.ToInt32(this.Range["a" + i, missing].Text);
              dd.공종ID = Convert.ToInt32(공id.Text);
              dd.업체명 = 업체명.Text.ToString();
              dd.재료비 = Convert.ToDouble(this.Range["a" + i, missing].get_Offset(0, 4).Text);
              dd.노무비 = Convert.ToDouble(this.Range["a" + i, missing].get_Offset(0, 5).Text);
              dd.경비 = this.Range["a" + i, missing].get_Offset(0, 6).Text == null ? 0 : Convert.ToDouble(this.Range["a" + i, missing].get_Offset(0, 6).Text);
              db.업체.InsertOnSubmit(dd);
            }
            i++;
          }
          
          db.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);
    
        }
    

     


    http://vsto.tistory.com
    Friday, May 27, 2011 7:05 AM
  • Hey VSTO_Beginner it is not working
    Monday, May 30, 2011 7:00 AM
  • Didn't you want to see or update the table?

    1. Add Linq to Sql( I used Northwind)

    2. Database -> ExcelSheet Show Button

        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
          li = test.Controls.AddListObject(wrk.Range["a1", "a2"], "test");
          var query = from c in nor.Categories
                select c;

          li.DataSource = query.ToList();     
        }

    3. ExcelSheet ->Database  Update Button

        private void button2_Click(object sender, RibbonControlEventArgs e)
        {
          li.DefaultDataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged;
         
          nor.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);
        }

    If you'd like to refresh Excel sheet when database update, reading SQL Server Notification Services or WCF helps you.

    http://msdn.microsoft.com/en-us/library/aa226909(v=sql.80).aspx

     


    http://vsto.tistory.com 

    Monday, May 30, 2011 7:24 AM
  • Hey I am using Excel-Addins.There are two buttons in ribbon 1 is show,which displays the table from database.2 one is update.

    I want database table to be updated when i update in excelsheet.

    When I update the contents of table in excel sheet and press update button it has to get updated in excelsheet as well as in database table.

    Plz  send the code.


    Hi Rishiketh

    See if the information in this MSDN documentation helps you get going:

    http://msdn.microsoft.com/en-us/library/bybtbt99.aspx


    Cindy Meister, VSTO/Word MVP
    Monday, May 30, 2011 7:44 AM
    Moderator
  • Hey VSTO_Beginner

    1 step Add linq to sql how u did that

    and what about the Datacontext how did u create that and used in Submitchanges method

    Monday, May 30, 2011 12:06 PM
  • I'm sorry that I can't explain in detail because I'm not good at writing in English.

    If you click below 2 links, you can see how I did it.

     http://msdn.microsoft.com/en-us/library/bb384429.aspx

    about Linq to Sql : http://msdn.microsoft.com/en-us/library/bb386976.aspx

    Simple Video : http://msdn.microsoft.com/en-us/office/cc507092


    http://vsto.tistory.com
    Monday, May 30, 2011 2:04 PM