使用 VBA Excel 的 SQL 数据透视函数文本文件数据库

我们在工作中无法访问 SQL Server，因此我必须在 Excel VBA 中设计一个应用程序并使用文本文件 (CSV) 来存储数据。

我查询数据、加入 CSV 没有问题，但我想使用 SQL Pivot/Unpivot 语句将其中一列转置为行。我不确定该功能是否存在，因为我不断收到 FROM 子句中存在语法错误的错误。

    Public Function getData() As ADODB.Recordset

    Dim path As String, conn As ADODB.Connection, rs As ADODB.Recordset
    path = ThisWorkbook.path & "\"

    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & path & ";" & _
               "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")

    rs.ActiveConnection = conn
    rs.Source = "SELECT * " & _
                "FROM " & _
                    "(SELECT emp_id, client, allocation " & _
                    "FROM ALLOCATIONdb.csv) AS s " & _
                "PIVOT (SUM(allocation) FOR client IN (client1, client2)) AS pvt"

    Set getData = rs

End Function

我的数据目前看起来像图片的顶部，我希望它看起来像底部。

这对于数据透视表来说很简单，但我希望用户能够输入数据。

此外，是否有办法使其动态化，因为可能的客户端数量未知，因此行数需要扩展。

提前致谢

这可能不是最好的解决方案，但它确实对我有用。 我将数据转换为列表，并将其传递给名为“ToDataTable”的函数，以首先转换为数据表。

public  DataTable ToDataTable<T>(List<T> items)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                dataTable.Columns.Add(prop.Name , prop.PropertyType);
            }
            foreach (T item in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    object val;
                    val = Props[i].GetValue(item, null);
                    values[i] = val;
                }
                dataTable.Rows.Add(values);
            }
            return dataTable;
        }

然后使用 GetInversedDataTable() 函数将其转换为您想要的格式。

public static DataTable GetInversedDataTable(DataTable table, string columnX, 
     string columnY, string columnZ, string nullValue, bool sumValues)
{

    DataTable returnTable = new DataTable();

    if (columnX == "")
        columnX = table.Columns[0].ColumnName;

    returnTable.Columns.Add(columnY);
    List<string> columnXValues = new List<string>();

    foreach (DataRow dr in table.Rows)
    {

        string columnXTemp = dr[columnX].ToString();
        if (!columnXValues.Contains(columnXTemp))
        {

            returnTable.Columns.Add(columnXTemp);
        }
    }

    //Verify if Y and Z Axis columns re provided
    if (columnY != "" && columnZ != "")
    {

        List<string> columnYValues = new List<string>();

        foreach (DataRow dr in table.Rows)
        {
            if (!columnYValues.Contains(dr[columnY].ToString()))
                columnYValues.Add(dr[columnY].ToString());
        }

        //Loop all Column Y Distinct Value
        foreach (string columnYValue in columnYValues)
        {

            DataRow drReturn = returnTable.NewRow();
            drReturn[0] = columnYValue;
            DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");

            foreach (DataRow dr in rows)
            {
                string rowColumnTitle = dr[columnX].ToString();

                foreach (DataColumn dc in returnTable.Columns)
                {
                    if (dc.ColumnName == rowColumnTitle)
                    {

                        if (sumValues)
                        {
                            try
                            {
                                drReturn[rowColumnTitle] = 
                                     Convert.ToDecimal(drReturn[rowColumnTitle]) + 
                                     Convert.ToDecimal(dr[columnZ]);
                            }
                            catch
                            {
                                drReturn[rowColumnTitle] = dr[columnZ];
                            }
                        }
                        else
                        {
                            drReturn[rowColumnTitle] = dr[columnZ];
                        }
                    }
                }
            }
            returnTable.Rows.Add(drReturn);
        }
    }
    else
    {
        throw new Exception("The columns to perform inversion are not provided");
    }
    if (nullValue != "")
    {
        foreach (DataRow dr in returnTable.Rows)
        {
            foreach (DataColumn dc in returnTable.Columns)
            {
                if (dr[dc.ColumnName].ToString() == "")
                    dr[dc.ColumnName] = nullValue;
            }
        }
    }

    return returnTable;
}

像这样使用它：

DataTable dtReturn = GetInversedDataTable(dt, "client", "emp_id", 
                                          "allocation", "0", true);

提供了三列并返回一个新的 DataTable。

下面的示例将使用源表和下面的参数来构建数据透视表。 就你而言： X 轴列：“客户端” Y 轴列：“emp_id” Z轴栏：“分配” 空值：“0”； 值总和：true

请注意，这是我用于解决我的问题的代码，并且我的代码是 C# 语言