如何用C#（优先使用OpenXml）在.xlsx文件中绘制线条？

我需要用C#程序在.xlsx文件中绘制一条从起始点延伸到左下方的线条，例如从E5（列5/行5）到C10（列3/行10）。

我编写了代码，但打开文件时总是提示内容有问题，询问是否恢复。选择“否”文件无变化，选择“是”后线条变成从(3/5)到(5/10)，完全不符合预期。我使用的是OpenXml v2.19 NuGet包（公司要求必须用这个版本），代码如下：

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using D = DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
using DS = DocumentFormat.OpenXml.Drawing.Spreadsheet;
using System;

public class ExcelLinie
{
    static void Main()
    {
        string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
        string filePath = System.IO.Path.Combine(desktopPath, "Example.xlsx");

        using (SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            // Create Workbook-Part
            WorkbookPart workbookPart = document.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();

            // Add Worksheet
            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets in workbook
            Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());

            // New Sheet
            Sheet sheet = new Sheet()
            {
                Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "MySheet"
            };
            sheets.Append(sheet);

            // Add Drawing
            DrawingsPart drawingsPart;

            if (worksheetPart.DrawingsPart == null)
            {
                drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
                worksheetPart.Worksheet.AppendChild(new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) });
            }
            else
            {
                drawingsPart = worksheetPart.DrawingsPart;
            }

            // Creates XML structure
            var worksheetDrawing = new WorksheetDrawing();

            // Creates Line
            var twoCellAnchor = new TwoCellAnchor(
                new DS.FromMarker( // Start point
                    new ColumnId("5"),
                    new ColumnOffset("0"),
                    new RowId("5"),
                    new RowOffset("0")
                ),
                new DS.ToMarker( // End point
                    new ColumnId("3"),
                    new ColumnOffset("0"),
                    new RowId("10"),
                    new RowOffset("0")
                ),
                new ConnectionShape(
                    new NonVisualConnectionShapeProperties(
                        new NonVisualDrawingProperties() { Id = (UInt32Value)3U, Name = "Straight Connector 2" },
                        new NonVisualConnectorShapeDrawingProperties()
                    ), // Shape
                    new ShapeProperties(
                        new D.Transform2D(
                            new D.Offset() { X = 0, Y = 0 },
                            new D.Extents() { Cx = 0, Cy = 0 }
                        ),
                        new D.PresetGeometry(
                            new D.AdjustValueList()
                        )
                        { Preset = D.ShapeTypeValues.Line },
                        new D.Outline(
                            new D.SolidFill(
                                new D.RgbColorModelHex() { Val = "326417" } // Color Hexcode
                            )
                        )
                        {
                            Width = 12700 * 2 // Thickness 1 Point = 12700 EMUs
                        }
                    )
                ),
                new ClientData()
            );
            worksheetDrawing.Append(twoCellAnchor);

            drawingsPart.WorksheetDrawing = worksheetDrawing;
            drawingsPart.WorksheetDrawing.Save();

            // Save Workbook
            workbookPart.Workbook.Save();
        }

        Console.WriteLine($"File saved at {filePath}");
    }
}

问题原因 #

1. TwoCellAnchor锚点顺序违规：OpenXml要求TwoCellAnchor的FromMarker必须是锚定区域的左上角单元格（列号≤To的列号，行号≤To的行号）。你的代码中FromMarker是E5(5,5)，ToMarker是C10(3,10)，列号5>3，不符合规范，导致Excel识别为无效XML，修复时自动交换两个锚点，颠倒了线条的起点和终点。
2. 错误使用ConnectionShape：ConnectionShape用于连接两个形状，绘制自由线条应该使用Shape元素，并设置预设形状为Line。

修复后的代码 #

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using D = DocumentFormat.OpenXml.Drawing;
using DS = DocumentFormat.OpenXml.Drawing.Spreadsheet;
using System;

public class ExcelLinie
{
    static void Main()
    {
        string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
        string filePath = System.IO.Path.Combine(desktopPath, "Example.xlsx");

        using (SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            // 创建工作簿部分
            WorkbookPart workbookPart = document.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();

            // 添加工作表
            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // 在工作簿中添加工作表引用
            Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
            Sheet sheet = new Sheet()
            {
                Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "MySheet"
            };
            sheets.Append(sheet);

            // 添加绘图部分
            DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
            worksheetPart.Worksheet.AppendChild(new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) });

            // 创建工作表绘图结构
            var worksheetDrawing = new WorksheetDrawing();

            // 绘制线条：从E5(5,5)到C10(3,10)
            // TwoCellAnchor的From必须是左上锚点(3,5)，To是右下锚点(5,10)，符合OpenXml规范
            var twoCellAnchor = new TwoCellAnchor(
                new DS.FromMarker(
                    new ColumnId("3"),
                    new ColumnOffset("0"),
                    new RowId("5"),
                    new RowOffset("0")
                ),
                new DS.ToMarker(
                    new ColumnId("5"),
                    new ColumnOffset("0"),
                    new RowId("10"),
                    new RowOffset("0")
                ),
                new Shape(
                    new NonVisualShapeProperties(
                        new NonVisualDrawingProperties() { Id = (UInt32Value)3U, Name = "Line 2" },
                        new NonVisualShapeDrawingProperties(new ShapeLocks() { NoGrouping = true })
                    ),
                    new ShapeProperties(
                        // 变换：设置线条的起点为E5的位置，终点为C10的位置
                        new D.Transform2D(
                            // 起点：E5的左上角偏移（相对于FromMarker的C5）
                            new D.Offset() { X = GetColumnOffset(5) - GetColumnOffset(3), Y = 0 },
                            // 线条的宽高：从E5到C10的水平/垂直偏移（水平为负，因为向左）
                            new D.Extents() { Cx = GetColumnOffset(3) - GetColumnOffset(5), Cy = GetRowOffset(10) - GetRowOffset(5) }
                        ),
                        new D.PresetGeometry(new D.AdjustValueList()) { Preset = D.ShapeTypeValues.Line },
                        new D.Outline(
                            new D.SolidFill(new D.RgbColorModelHex() { Val = "326417" })
                        ) { Width = 12700 * 2 }
                    )
                ),
                new ClientData()
            );
            worksheetDrawing.Append(twoCellAnchor);

            drawingsPart.WorksheetDrawing = worksheetDrawing;
            workbookPart.Workbook.Save();
        }

        Console.WriteLine($"文件已保存至：{filePath}");
    }

    // 计算指定列的起始偏移量（EMU单位，默认列宽8.43字符，1字符=14400 EMU）
    private static long GetColumnOffset(int columnIndex)
    {
        // 默认列宽：8.43 * 14400 = 121392 EMU
        const long defaultColumnWidthEmu = 121392;
        return (columnIndex - 1) * defaultColumnWidthEmu;
    }

    // 计算指定行的起始偏移量（EMU单位，默认行高15磅，1磅=12700 EMU）
    private static long GetRowOffset(int rowIndex)
    {
        // 默认行高：15 * 12700 = 190500 EMU
        const long defaultRowHeightEmu = 190500;
        return (rowIndex - 1) * defaultRowHeightEmu;
    }
}

关键修改点 #

1. 修正TwoCellAnchor锚点顺序：将FromMarker设为C5(3,5)，ToMarker设为E10(5,10)，确保左上到右下的顺序，避免Excel修复时交换锚点。
2. 替换ConnectionShape为Shape：使用Shape元素绘制自由线条，符合OpenXml对普通线条的定义。
3. 计算线条位置：通过GetColumnOffset和GetRowOffset方法计算单元格的EMU偏移量，准确定位线条的起点（E5）和终点（C10）。

这样修改后，Excel打开文件不会再提示损坏，线条会正确从E5延伸到C10的左下方位置。

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