none
Databound Fomulas in a List Object RRS feed

  • Question

  • I have formulas for Excel in a DataTable and when I bind the DataTable to the list object they are treated as text. Is there anyway of getting these formulas to calculate after it is bound?

    For example I have a DataTable with the formula =1+2 in the first row. When I bind the data to the list object I thought it would calculate out to 3, but instead it just shows up as '=1+2

    Below is some sample code I wrote up real quick in to demonstrate what I mean. I just made a ribbon added a button to it and added the code to the click handler of the button.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using Tools = Microsoft.Office.Tools.Excel;
    using System.Data;
    
    namespace Formula
    {
        public partial class Ribbon1
        {
            private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
            {
            }
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("Formula");
                dt.Rows.Add(dt.NewRow()["Formula"] = "=1+2");
                Tools.Worksheet ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveSheet);
                Tools.ListObject list = ws.Controls.AddListObject(ws.Range["A1"], "FormulaTest");
                list.DataSource = dt;
                list.AutoSetDataBoundColumnHeaders = true;
            }
        }
    }

    Friday, February 17, 2012 6:57 PM

All replies

  • Hi Gravedigger7789,

    Thanks for posting in the MSDN Forum.

    I tried following snippet on my side. It works fine for me (Visual Studio 2010, Office 2010)

    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.Data;
    
    namespace ExcelWorkbook7
    {
        public partial class Ribbon1
        {
            private DataTable dt = null;
    
            private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
            {
                dt = new DataTable();
                DataColumn dc = new DataColumn("Fomula");
                dt.Columns.Add(dc);
                dc.DataType = Type.GetType("System.String");
                DataRow dr = dt.Rows.Add();
                dr[0] = "=1+1";
            }
    
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                //string s = "=1+1";
                string s = dt.Rows[0]["Fomula"].ToString();
                Excel.Application xlApp = Globals.ThisWorkbook.Application;
                Excel.Workbook xlWorkbook = xlApp.ActiveWorkbook;
                Excel.Worksheet xlWorksheet = xlWorkbook.ActiveSheet;
                Excel.Range xlRange = xlWorksheet.Cells[1, 1];
                xlRange.Value2 = s;
            }
        }
    }

    I hope it can help you.

    Have a good dya,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 22, 2012 3:30 AM
    Moderator
  • That works well if the DataTable only has a few values in it. My DataTable is going to be upwards of 1000 rows and 50ish columns. Looping through all of that and writing each value to the sheet individually will take a long time. I need to find a quick way to bind the DataTable to Excel without the formulas being placed as Text.
    Wednesday, February 22, 2012 2:26 PM
  • I have the same problem.  Were you ever able to resolve this issue?
    Friday, April 20, 2012 7:45 PM
  • to get a calculated column in a databound listobject, I put a placeholder field in the underlying query to give a null column e.g.

    Select ShoeSize

    , HatSize

    , null PlaceHolder

    , name

    from.....

    The after the listobject is populated:

    Listobject.ListColumns["PlaceHolder"].DataBodyRange.Formula = "=[@HatSize] + [@ShoeSize]";

    brian

    Thursday, June 28, 2012 2:54 AM
  • I'm Using EntityFrameWork

    1. Binding.

    2. Add Calculate Columns.

    ##listobject Name = list1


    http://vsto.tistory.com

    Thursday, June 28, 2012 4:29 AM