如何在ASP.NET MVC中实现员工层级DataTable导出至Visio(.vsd)？

Got it, let's break down how to solve this problem. Generating a .vsd (or the more modern .vsdx) file that includes both your employee hierarchy data and a visual org chart from an ASP.NET MVC app is totally feasible—you just need to work with either Microsoft's free Open XML SDK or a third-party library, since .vsd is a proprietary format. Here's what you need to know:

方案1：使用微软Open XML SDK（免费，推荐） #

First, a quick note: .vsd is an older binary format, while .vsdx is the modern Open XML-based format supported by Visio 2013+. The Open XML SDK doesn't support .vsd natively, so I'll focus on .vsdx (which is more reliable for server-side generation). If you absolutely need .vsd, skip to the third-party library section below.

Step 1: Install Required NuGet Packages #

Install-Package DocumentFormat.OpenXml
Install-Package DocumentFormat.OpenXml.Visio

Step 2: Convert Your DataTable to a Hierarchical Structure #

First, we'll turn your flat DataTable into a nested hierarchy to make drawing the org chart easier:

public class EmployeeNode
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public int? ParentId { get; set; }
    public List<EmployeeNode> Children { get; set; } = new List<EmployeeNode>();
}

private List<EmployeeNode> ConvertDataTableToHierarchy(DataTable employeeTable)
{
    var allNodes = employeeTable.AsEnumerable()
        .Select(row => new EmployeeNode
        {
            Id = row.Field<int>("Id"),
            Name = row.Field<string>("Name"),
            Position = row.Field<string>("Position"),
            ParentId = row.IsNull("ParentId") ? null : (int?)row.Field<int>("ParentId")
        })
        .ToList();

    // Map parent-child relationships
    var nodeLookup = allNodes.ToDictionary(n => n.Id);
    foreach (var node in allNodes)
    {
        if (node.ParentId.HasValue && nodeLookup.ContainsKey(node.ParentId.Value))
        {
            nodeLookup[node.ParentId.Value].Children.Add(node);
        }
    }

    // Return root-level employees (no parent)
    return allNodes.Where(n => !n.ParentId.HasValue).ToList();
}

Step 3: Generate the Visio File with Chart and Data Table #

Here's a method to create the Visio document, draw the org chart, and add the employee data table:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Visio;
using System.IO;

public byte[] GenerateOrgChartVisio(List<EmployeeNode> hierarchy, DataTable employeeData)
{
    using (var memStream = new MemoryStream())
    {
        using (var visioDoc = VisioDocument.Create(memStream))
        {
            var pagePart = visioDoc.MainDocumentPart.Pages.FirstOrDefault() 
                ?? visioDoc.MainDocumentPart.AddNewPart<PagePart>();
            pagePart.Page = new Page();

            // Draw the hierarchical org chart
            DrawHierarchyNodes(pagePart, hierarchy, 100, 100, 220);

            // Add the employee data table below the chart
            AddEmployeeDataTable(pagePart, employeeData, 100, 650);
        }
        return memStream.ToArray();
    }
}

// Recursively draw employee nodes and connectors
private void DrawHierarchyNodes(PagePart pagePart, List<EmployeeNode> nodes, double x, double y, double verticalGap)
{
    int shapeId = pagePart.Page.Elements<Shape>().Max(s => int.TryParse(s.Id, out int id) ? id : 0) + 1;

    foreach (var node in nodes)
    {
        // Create employee shape
        var employeeShape = new Shape
        {
            Id = shapeId.ToString(),
            Text = new Text(new Paragraph(new Run(new Text($"{node.Name}\n{node.Position}"))))
        };
        employeeShape.Append(new ShapeLayout
        {
            PinX = new DoubleValue(x),
            PinY = new DoubleValue(y),
            Width = new DoubleValue(160),
            Height = new DoubleValue(70)
        });
        pagePart.Page.Append(employeeShape);

        // Draw child nodes and connectors
        if (node.Children.Any())
        {
            double childX = x - (node.Children.Count * 90) + 90;
            double childY = y + verticalGap;
            foreach (var child in node.Children)
            {
                shapeId++;
                var childShape = new Shape
                {
                    Id = shapeId.ToString(),
                    Text = new Text(new Paragraph(new Run(new Text($"{child.Name}\n{child.Position}"))))
                };
                childShape.Append(new ShapeLayout
                {
                    PinX = new DoubleValue(childX),
                    PinY = new DoubleValue(childY),
                    Width = new DoubleValue(160),
                    Height = new DoubleValue(70)
                });
                pagePart.Page.Append(childShape);

                // Add connector between parent and child
                shapeId++;
                var connector = new Shape
                {
                    Id = shapeId.ToString(),
                    Type = ShapeValues.Connector
                };
                connector.Append(new ShapeLayout
                {
                    BeginX = new DoubleValue(x + 80),
                    BeginY = new DoubleValue(y + 70),
                    EndX = new DoubleValue(childX + 80),
                    EndY = new DoubleValue(childY)
                });
                pagePart.Page.Append(connector);

                childX += 180;
            }
        }
        shapeId++;
        x += 200;
    }
}

// Add a data table with all employee details
private void AddEmployeeDataTable(PagePart pagePart, DataTable table, double x, double y)
{
    int shapeId = pagePart.Page.Elements<Shape>().Max(s => int.TryParse(s.Id, out int id) ? id : 0) + 1;
    const int rowHeight = 35;
    const int colWidth = 130;

    // Draw table headers
    for (int i = 0; i < table.Columns.Count; i++)
    {
        var headerShape = new Shape
        {
            Id = shapeId.ToString(),
            Text = new Text(new Paragraph(new Run(new Text(table.Columns[i].ColumnName))))
        };
        headerShape.Append(new ShapeLayout
        {
            PinX = new DoubleValue(x + (i * colWidth)),
            PinY = new DoubleValue(y),
            Width = new DoubleValue(colWidth),
            Height = new DoubleValue(rowHeight)
        });
        pagePart.Page.Append(headerShape);
        shapeId++;
    }

    // Draw table rows
    y += rowHeight;
    foreach (DataRow row in table.Rows)
    {
        for (int i = 0; i < table.Columns.Count; i++)
        {
            var cellShape = new Shape
            {
                Id = shapeId.ToString(),
                Text = new Text(new Paragraph(new Run(new Text(row[i].ToString()))))
            };
            cellShape.Append(new ShapeLayout
            {
                PinX = new DoubleValue(x + (i * colWidth)),
                PinY = new DoubleValue(y),
                Width = new DoubleValue(colWidth),
                Height = new DoubleValue(rowHeight)
            });
            pagePart.Page.Append(cellShape);
            shapeId++;
        }
        y += rowHeight;
    }
}

Step 4: Add an MVC Action to Trigger Download #

Add this to your controller to let users download the generated file:

public ActionResult DownloadOrgChart()
{
    // Replace with your actual DataTable retrieval logic
    DataTable employeeTable = GetSampleEmployeeData();
    List<EmployeeNode> hierarchy = ConvertDataTableToHierarchy(employeeTable);

    byte[] visioFileBytes = GenerateOrgChartVisio(hierarchy, employeeTable);

    // Use "application/vnd.ms-visio" for .vsd (if using third-party library)
    return File(visioFileBytes, "application/vnd.ms-visio.drawing.12", "EmployeeOrgChart.vsdx");
}

// Sample data method (replace with your DB call)
private DataTable GetSampleEmployeeData()
{
    var table = new DataTable();
    table.Columns.Add("Id", typeof(int));
    table.Columns.Add("Name", typeof(string));
    table.Columns.Add("Position", typeof(string));
    table.Columns.Add("ParentId", typeof(int));

    table.Rows.Add(1, "John Doe", "CEO", DBNull.Value);
    table.Rows.Add(2, "Jane Smith", "CTO", 1);
    table.Rows.Add(3, "Bob Brown", "Engineering Manager", 2);
    table.Rows.Add(4, "Alice Lee", "Marketing Director", 1);

    return table;
}

方案2：使用第三方库（如Aspose.Diagram） #

If you want to avoid the low-level Open XML code, third-party libraries like Aspose.Diagram simplify generating .vsd/.vsdx files with built-in org chart and table support. Note that this is a commercial library, but it handles layout and formatting automatically.

Sample Code with Aspose.Diagram #

using Aspose.Diagram;
using System.IO;

public byte[] GenerateOrgChartWithAspose(List<EmployeeNode> hierarchy, DataTable employeeData)
{
    var diagram = new Diagram();
    var page = diagram.Pages[0];

    // Aspose has built-in org chart tools (simplified example)
    // For full org chart layout, refer to Aspose's documentation

    // Add employee data table
    var table = page.Shapes.AddTable(100, 650, employeeData.Columns.Count * 130, (employeeData.Rows.Count + 1) * 35);
    
    // Populate headers
    for (int i = 0; i < employeeData.Columns.Count; i++)
    {
        table.Cells[i, 0].Text.Value.Add(new Txt(employeeData.Columns[i].ColumnName));
    }
    
    // Populate rows
    for (int rowIdx = 0; rowIdx < employeeData.Rows.Count; rowIdx++)
    {
        for (int colIdx = 0; colIdx < employeeData.Columns.Count; colIdx++)
        {
            table.Cells[colIdx, rowIdx + 1].Text.Value.Add(new Txt(employeeData.Rows[rowIdx][colIdx].ToString()));
        }
    }

    using (var memStream = new MemoryStream())
    {
        diagram.Save(memStream, SaveFileFormat.VSD); // Generates .vsd directly
        return memStream.ToArray();
    }
}

Key Notes #

• .vsd vs .vsdx: Stick with .vsdx if possible—it's open, easier to manipulate, and compatible with modern Visio versions. .vsd requires either a third-party library or Visio Interop (which is not recommended for server environments, as it requires installing Visio on the server).
• Layout Customization: The Open XML example uses a basic layout. For more polished auto-layout, implement a tree layout algorithm or use a library's built-in layout tools.
• Server Environment: Avoid Visio Interop in production—it's unstable and requires elevated permissions. Open XML or third-party libraries are far more reliable.

内容的提问来源于stack exchange，提问作者Beatrix