使用c#将Power Queries添加到Excel [英] Adding Power Queries to Excel using c#

问题描述

我想将以下代码从VBA复制到c#VSTO:

I want to replicate the following code from VBA into c# VSTO:

M_Script = LoadTextFile(qname, wk)
qSource = workbook_path & "\" & qname & ".csv"
Dim qry As WorkbookQuery
Set qry = wk.Queries.Item(qname)
qry.Formula = M_Script
Set qry = wk.Queries.Add(qname, M_Script, qSource)

wk.Connections.Add2 "Query - " & query.Name, _
        "Connection to the '" & query.Name & "' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
        , """" & query.Name & """", 6, True, False

有一个简单的等价物吗?我找不到wk.查询.我不希望将数据加载到工作表中，而只创建与查询的连接.

Is there an easy equivalent? I could not find the wk.Queries. I don't want the data to be loaded into the worksheet but create only a connection to the query.

提前谢谢！

推荐答案

我最终使用以下方法.尽管它不是真正的C#，但我希望我能在某个时候找到更好的解决方案.VSTO文档确实是不合标准的.

I ended up using the following approach. Though it's not really c# and I hope I will find a better solution at some point. VSTO Documentation is really sub-standard.

public void addQuery(string m_script_path, string query_name, Excel.Workbook wk)
{
            VBComponent newStandardModule;
            if(wk.VBProject.VBComponents.Count==0){
                newStandardModule = wk.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
            }
            else
            {
                newStandardModule = wk.VBProject.VBComponents.Item(1);
            }

            var codeModule = newStandardModule.CodeModule;

            // add vba code to module
            var lineNum = codeModule.CountOfLines + 1;
            var macroName = "addQuery";
            var codeText = "Public Sub " + macroName + "()" + "\r\n";
            codeText += "M_Script = CreateObject(\"Scripting.FileSystemObject\").OpenTextFile(\""+m_script_path+"\", 1).ReadAll" + "\r\n";
            codeText += "ActiveWorkbook.Queries.Add Name:=\""+ query_name+"\", Formula:=M_Script\r\n";
            codeText += "ActiveWorkbook.Connections.Add2 _\r\n";
            codeText += "\"Query - test\", _\r\n";
            codeText += "\"Connection to the '" + query_name + "' query in the workbook.\", _\r\n";
            codeText += "\"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" + query_name + ";Extended Properties=\" _\r\n";
            codeText += ", \"\"\"" + query_name + "\"\"\", 6, True, False\r\n";

            codeText += "End Sub";

            codeModule.InsertLines(lineNum, codeText);

            var macro = string.Format("{0}.{1}", newStandardModule.Name, macroName);

            wk.Application.Run(macro);

            codeModule.DeleteLines(lineNum, 9);
        }

这篇关于使用c#将Power Queries添加到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！