从URL逗号分隔的Excel QueryTables.Add [英] Excel QueryTables.Add from URL Comma Delimited

问题描述

我有一台服务器，它在http响应中返回大量逗号分隔的数据.我需要将此数据导入excel.

I have a server that returns large amounts of comma separated data in an http response. I need to import this data into excel.

我通过将内容传递到临时文件，然后以CSV格式读取临时文件来完成此工作，但是此过程似乎效率很低.查询表可以直接从http响应中读取，但会将每一行数据放入一个单元格中，而不是将每个逗号分隔成一个单元格.

I have this working by passing the contents to a temp file and then reading the temp file as a csv, but this process seems inefficient. The query tables can read directly from the http response, but it puts each line of data into a single cell, rather than separating into one cell per comma.

是否可以通过C#excel插件将http响应中的逗号分隔数据直接读入excel?

Is it possible to read comma separated data from an http response directly into excel from a C# excel add-in?

谢谢！

    public static void URLtoCSV(string URL, Excel.Worksheet destinationSheet, Excel.Range     destinationRange, int[] columnDataTypes, bool autoFitColumns)
    {
        destinationSheet.QueryTables.Add(
            "URL;" + URL,
        destinationRange, Type.Missing);
        destinationSheet.QueryTables[1].Name = URL;
        destinationSheet.QueryTables[1].FieldNames = true;
        destinationSheet.QueryTables[1].RowNumbers = false;
        destinationSheet.QueryTables[1].FillAdjacentFormulas = false;
        destinationSheet.QueryTables[1].PreserveFormatting = true;
        destinationSheet.QueryTables[1].RefreshOnFileOpen = false;
        destinationSheet.QueryTables[1].RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells;
        destinationSheet.QueryTables[1].SavePassword = false;
        destinationSheet.QueryTables[1].SaveData = true;
        destinationSheet.QueryTables[1].AdjustColumnWidth = true;
        destinationSheet.QueryTables[1].RefreshPeriod = 0;
        destinationSheet.QueryTables[1].Refresh(false);

        if (autoFitColumns == true)
            destinationSheet.QueryTables[1].Destination.EntireColumn.AutoFit();

    }

推荐答案

比您所引用的解决方案更简单的解决方案是使用"TEXT"类型而不是URL.TEXT支持所有CSV导入，包括从HTTP来源的导入.URL似乎被设计用来处理屏幕抓取，而不是其他任何事情.

The easier solution than the one you reference is to use the type of "TEXT" instead of URL. TEXT supports all CSV imports, including from HTTP sources. URL appears to be designed to handle screen scraping more than anything else.

例如在您的情况下:

destinationSheet.QueryTables.Add("URL;" + URL,

成为

destinationSheet.QueryTables.Add("TEXT;" + URL,

对于那些绊脚石问同样的问题，但在Excel中使用VB脚本的人，完整的解决方案如下:

And for those stumbling upon this post asking the same question but with VB scripting in Excel, the complete solution would look like:

' Load new data from web
With ActiveSheet.QueryTables.Add(Connection:="TEXT;http://yourdomain.com/csv.php", Destination:=Range("$A$1"))
.TextFileCommaDelimiter = True
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.Refresh BackgroundQuery:=False
End With

这篇关于从URL逗号分隔的Excel QueryTables.Add的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！