snipt

Ctrl+h for KB shortcuts

C#

Custom Ribbon button - Import Data from SQL to active sheet

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Xml;
using Microsoft.Office.Tools.Ribbon;
using Microsoft.Office.Interop.Excel;  
//last line very important,do not confused with Microsoft.Office.Tools.Excel;

private void buttonImportFromSql_Click(object sender, RibbonControlEventArgs e)
{
    var sheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet;
    PopulateData(sheet);
}

private void PopulateData(Worksheet sheet)
{
    try
    {
        string sheetName = sheet.Name;
        SqlConnection conn = new SqlConnection("Integrated Security=yes;Initial Catalog=RpoPrTracker;Data Source=(local)");
        conn.Open();
        SqlCommand command = new SqlCommand(string.Format("select * from {0}", sheetName), conn);
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataSet dataset = new DataSet();
        adapter.Fill(dataset);

        System.Data.DataTable table = dataset.Tables[0];

        int ColumnIndex = 0;
        sheet.Cells.ClearContents();
        foreach (System.Data.DataColumn col in table.Columns)
        {
            ColumnIndex++;
            sheet.Cells[1, ColumnIndex] = col.ColumnName;
        } 

        int rowIndex = 0;
        foreach (DataRow row in table.Rows)
        {
            rowIndex++;
            ColumnIndex = 0;
            foreach (DataColumn col in table.Columns)
            {
                ColumnIndex++;
                sheet.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
            }
        }
    }
    catch (XmlException exml)
    {
        // catch an xmlexception errors
        MessageBox.Show(exml.Message);
    }
}
https://snipt.net/embed/324f497fc7c1c7ac6f1935324d002846/
/raw/324f497fc7c1c7ac6f1935324d002846/
324f497fc7c1c7ac6f1935324d002846
csharp
C#
54
2019-06-26T07:44:19
True
False
False
/api/public/snipt/37995/
custom-ribbon-button-import-data-from-sql-to-active-sheet
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a> <a href="#L-19">19</a> <a href="#L-20">20</a> <a href="#L-21">21</a> <a href="#L-22">22</a> <a href="#L-23">23</a> <a href="#L-24">24</a> <a href="#L-25">25</a> <a href="#L-26">26</a> <a href="#L-27">27</a> <a href="#L-28">28</a> <a href="#L-29">29</a> <a href="#L-30">30</a> <a href="#L-31">31</a> <a href="#L-32">32</a> <a href="#L-33">33</a> <a href="#L-34">34</a> <a href="#L-35">35</a> <a href="#L-36">36</a> <a href="#L-37">37</a> <a href="#L-38">38</a> <a href="#L-39">39</a> <a href="#L-40">40</a> <a href="#L-41">41</a> <a href="#L-42">42</a> <a href="#L-43">43</a> <a href="#L-44">44</a> <a href="#L-45">45</a> <a href="#L-46">46</a> <a href="#L-47">47</a> <a href="#L-48">48</a> <a href="#L-49">49</a> <a href="#L-50">50</a> <a href="#L-51">51</a> <a href="#L-52">52</a> <a href="#L-53">53</a> <a href="#L-54">54</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">using</span> <span class="nn">System.Data</span><span class="p">;</span> </span><span id="L-2"><a name="L-2"></a><span class="k">using</span> <span class="nn">System.Data.SqlClient</span><span class="p">;</span> </span><span id="L-3"><a name="L-3"></a><span class="k">using</span> <span class="nn">System.Windows.Forms</span><span class="p">;</span> </span><span id="L-4"><a name="L-4"></a><span class="k">using</span> <span class="nn">System.Xml</span><span class="p">;</span> </span><span id="L-5"><a name="L-5"></a><span class="k">using</span> <span class="nn">Microsoft.Office.Tools.Ribbon</span><span class="p">;</span> </span><span id="L-6"><a name="L-6"></a><span class="k">using</span> <span class="nn">Microsoft.Office.Interop.Excel</span><span class="p">;</span> </span><span id="L-7"><a name="L-7"></a><span class="c1">//last line very important,do not confused with Microsoft.Office.Tools.Excel;</span> </span><span id="L-8"><a name="L-8"></a> </span><span id="L-9"><a name="L-9"></a><span class="k">private</span> <span class="k">void</span> <span class="nf">buttonImportFromSql_Click</span><span class="p">(</span><span class="kt">object</span> <span class="n">sender</span><span class="p">,</span> <span class="n">RibbonControlEventArgs</span> <span class="n">e</span><span class="p">)</span> </span><span id="L-10"><a name="L-10"></a><span class="p">{</span> </span><span id="L-11"><a name="L-11"></a> <span class="kt">var</span> <span class="n">sheet</span> <span class="p">=</span> <span class="p">(</span><span class="n">Microsoft</span><span class="p">.</span><span class="n">Office</span><span class="p">.</span><span class="n">Interop</span><span class="p">.</span><span class="n">Excel</span><span class="p">.</span><span class="n">Worksheet</span><span class="p">)</span><span class="n">Globals</span><span class="p">.</span><span class="n">ThisWorkbook</span><span class="p">.</span><span class="n">ActiveSheet</span><span class="p">;</span> </span><span id="L-12"><a name="L-12"></a> <span class="n">PopulateData</span><span class="p">(</span><span class="n">sheet</span><span class="p">);</span> </span><span id="L-13"><a name="L-13"></a><span class="p">}</span> </span><span id="L-14"><a name="L-14"></a> </span><span id="L-15"><a name="L-15"></a><span class="k">private</span> <span class="k">void</span> <span class="nf">PopulateData</span><span class="p">(</span><span class="n">Worksheet</span> <span class="n">sheet</span><span class="p">)</span> </span><span id="L-16"><a name="L-16"></a><span class="p">{</span> </span><span id="L-17"><a name="L-17"></a> <span class="k">try</span> </span><span id="L-18"><a name="L-18"></a> <span class="p">{</span> </span><span id="L-19"><a name="L-19"></a> <span class="kt">string</span> <span class="n">sheetName</span> <span class="p">=</span> <span class="n">sheet</span><span class="p">.</span><span class="n">Name</span><span class="p">;</span> </span><span id="L-20"><a name="L-20"></a> <span class="n">SqlConnection</span> <span class="n">conn</span> <span class="p">=</span> <span class="k">new</span> <span class="n">SqlConnection</span><span class="p">(</span><span class="s">&quot;Integrated Security=yes;Initial Catalog=RpoPrTracker;Data Source=(local)&quot;</span><span class="p">);</span> </span><span id="L-21"><a name="L-21"></a> <span class="n">conn</span><span class="p">.</span><span class="n">Open</span><span class="p">();</span> </span><span id="L-22"><a name="L-22"></a> <span class="n">SqlCommand</span> <span class="n">command</span> <span class="p">=</span> <span class="k">new</span> <span class="n">SqlCommand</span><span class="p">(</span><span class="kt">string</span><span class="p">.</span><span class="n">Format</span><span class="p">(</span><span class="s">&quot;select * from {0}&quot;</span><span class="p">,</span> <span class="n">sheetName</span><span class="p">),</span> <span class="n">conn</span><span class="p">);</span> </span><span id="L-23"><a name="L-23"></a> <span class="n">SqlDataAdapter</span> <span class="n">adapter</span> <span class="p">=</span> <span class="k">new</span> <span class="n">SqlDataAdapter</span><span class="p">(</span><span class="n">command</span><span class="p">);</span> </span><span id="L-24"><a name="L-24"></a> <span class="n">DataSet</span> <span class="n">dataset</span> <span class="p">=</span> <span class="k">new</span> <span class="n">DataSet</span><span class="p">();</span> </span><span id="L-25"><a name="L-25"></a> <span class="n">adapter</span><span class="p">.</span><span class="n">Fill</span><span class="p">(</span><span class="n">dataset</span><span class="p">);</span> </span><span id="L-26"><a name="L-26"></a> </span><span id="L-27"><a name="L-27"></a> <span class="n">System</span><span class="p">.</span><span class="n">Data</span><span class="p">.</span><span class="n">DataTable</span> <span class="n">table</span> <span class="p">=</span> <span class="n">dataset</span><span class="p">.</span><span class="n">Tables</span><span class="p">[</span><span class="m">0</span><span class="p">];</span> </span><span id="L-28"><a name="L-28"></a> </span><span id="L-29"><a name="L-29"></a> <span class="kt">int</span> <span class="n">ColumnIndex</span> <span class="p">=</span> <span class="m">0</span><span class="p">;</span> </span><span id="L-30"><a name="L-30"></a> <span class="n">sheet</span><span class="p">.</span><span class="n">Cells</span><span class="p">.</span><span class="n">ClearContents</span><span class="p">();</span> </span><span id="L-31"><a name="L-31"></a> <span class="k">foreach</span> <span class="p">(</span><span class="n">System</span><span class="p">.</span><span class="n">Data</span><span class="p">.</span><span class="n">DataColumn</span> <span class="n">col</span> <span class="k">in</span> <span class="n">table</span><span class="p">.</span><span class="n">Columns</span><span class="p">)</span> </span><span id="L-32"><a name="L-32"></a> <span class="p">{</span> </span><span id="L-33"><a name="L-33"></a> <span class="n">ColumnIndex</span><span class="p">++;</span> </span><span id="L-34"><a name="L-34"></a> <span class="n">sheet</span><span class="p">.</span><span class="n">Cells</span><span class="p">[</span><span class="m">1</span><span class="p">,</span> <span class="n">ColumnIndex</span><span class="p">]</span> <span class="p">=</span> <span class="n">col</span><span class="p">.</span><span class="n">ColumnName</span><span class="p">;</span> </span><span id="L-35"><a name="L-35"></a> <span class="p">}</span> </span><span id="L-36"><a name="L-36"></a> </span><span id="L-37"><a name="L-37"></a> <span class="kt">int</span> <span class="n">rowIndex</span> <span class="p">=</span> <span class="m">0</span><span class="p">;</span> </span><span id="L-38"><a name="L-38"></a> <span class="k">foreach</span> <span class="p">(</span><span class="n">DataRow</span> <span class="n">row</span> <span class="k">in</span> <span class="n">table</span><span class="p">.</span><span class="n">Rows</span><span class="p">)</span> </span><span id="L-39"><a name="L-39"></a> <span class="p">{</span> </span><span id="L-40"><a name="L-40"></a> <span class="n">rowIndex</span><span class="p">++;</span> </span><span id="L-41"><a name="L-41"></a> <span class="n">ColumnIndex</span> <span class="p">=</span> <span class="m">0</span><span class="p">;</span> </span><span id="L-42"><a name="L-42"></a> <span class="k">foreach</span> <span class="p">(</span><span class="n">DataColumn</span> <span class="n">col</span> <span class="k">in</span> <span class="n">table</span><span class="p">.</span><span class="n">Columns</span><span class="p">)</span> </span><span id="L-43"><a name="L-43"></a> <span class="p">{</span> </span><span id="L-44"><a name="L-44"></a> <span class="n">ColumnIndex</span><span class="p">++;</span> </span><span id="L-45"><a name="L-45"></a> <span class="n">sheet</span><span class="p">.</span><span class="n">Cells</span><span class="p">[</span><span class="n">rowIndex</span> <span class="p">+</span> <span class="m">1</span><span class="p">,</span> <span class="n">ColumnIndex</span><span class="p">]</span> <span class="p">=</span> <span class="n">row</span><span class="p">[</span><span class="n">col</span><span class="p">.</span><span class="n">ColumnName</span><span class="p">];</span> </span><span id="L-46"><a name="L-46"></a> <span class="p">}</span> </span><span id="L-47"><a name="L-47"></a> <span class="p">}</span> </span><span id="L-48"><a name="L-48"></a> <span class="p">}</span> </span><span id="L-49"><a name="L-49"></a> <span class="k">catch</span> <span class="p">(</span><span class="n">XmlException</span> <span class="n">exml</span><span class="p">)</span> </span><span id="L-50"><a name="L-50"></a> <span class="p">{</span> </span><span id="L-51"><a name="L-51"></a> <span class="c1">// catch an xmlexception errors</span> </span><span id="L-52"><a name="L-52"></a> <span class="n">MessageBox</span><span class="p">.</span><span class="n">Show</span><span class="p">(</span><span class="n">exml</span><span class="p">.</span><span class="n">Message</span><span class="p">);</span> </span><span id="L-53"><a name="L-53"></a> <span class="p">}</span> </span><span id="L-54"><a name="L-54"></a><span class="p">}</span> </span></pre></div> </td></tr></table>
excel, ribbon, vsto