c# - 如何使用 OpenXML 在 Excel 工作表中设置复选框值

Question

我在工作表中有一个带有 ActiveX 控件(复选框、单选按钮等)的 Excel 模板。

我需要以编程方式获取复选框引用并将值更改为已选中或未选中。

经过短暂的互联网搜索，我找不到任何合适的线索。谁能帮我解决这个问题？

我正在使用 visual studio 2019、OpenXML 2.5 和 C# 以及 .Net Framework 4.7。

Answer 1

这..结果是相当复杂..但是一个很好的挑战:)

这是可能的，但很丑。第一:学分。那两个问题here和 here让我开始，剩下的就是通过反复试验。 (注:所有代码样例依次跟进，完整的样例放在最后，因为比较长)

第一步:找到复选框所有 ActiveX 控件都存储为一个控件，我们需要找到有问题的控件。

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, true))
        {
            foreach (var control in doc.WorkbookPart.WorksheetParts.First().Worksheet.Descendants<Control>())
            {
                Console.WriteLine();
                Console.WriteLine("Control {0}:", control.Name);
                Console.WriteLine("Id: {0}", control.Id);

然后您可以通过过滤名称找到您要查找的特定控件。

奖励:识别类型根据其中一个链接问题，我们可以按如下方式识别控件类型:

                var part = doc.WorkbookPart.WorksheetParts.First().GetPartById(control.Id) as EmbeddedControlPersistencePart;
                var xreader = OpenXmlReader.Create(part.GetStream());
                xreader.Read();

                var xml = xreader.LoadCurrentElement();
                var classID = xml.GetAttribute("classid", xml.NamespaceUri).Value;

                if (classID == "{8BD21D40-EC42-11CE-9E0D-00AA006002F3}") // it is a checkbox

第二步:获取这些属性所有 ActiveX 控件都将它们的属性存储为二进制文件。所以你需要解析和编辑这个二进制文件来改变刻度线。所以让我们先找到二进制数据

var binary = part.EmbeddedControlPersistenceBinaryDataParts.First();

简单。现在我们需要获取二进制流并通过将其拉入内存流使其可编辑

using (var source = binary.GetStream())
{
    using (var reader = new BinaryReader(source))
    {
         using (var stream = new MemoryStream(reader.ReadBytes((int)source.Length)))
         {

整洁。现在一些愚蠢的二进制解析。我通过使用 HxD 和一个一个地操作属性的反复试验找到了二进制数据的布局。我在这个问题的末尾添加了我的分析。由于 xlsx 文件只是 zip 文件，您可以自己在路径“xl\activeX”中相对轻松地找到二进制文件

首先，让我们跳到保存复选标记的所需位置

                                // Skip random junk
                                stream.Seek(20, SeekOrigin.Begin);

                                // read additional data flags & how many additional bytes are added
                                var flags = (byte) stream.ReadByte();
                                var skip = 0;

                                if ((flags & 0x01) != 0) // Attribute Block
                                    skip++;
                                if ((flags & 0x02) != 0) // BackColor Block
                                    skip++;
                                if ((flags & 0x04) != 0) // ForeColor Block
                                    skip++;

                                // skip some bytes
                                stream.Seek(2, SeekOrigin.Current);
                                flags = (byte) stream.ReadByte();

                                if ((flags & 0x01) != 0) // Special Effect Block
                                    skip++;
                                if ((flags & 0x04) != 0) // PicturePosition Block
                                    skip++;

                                // skip some flags
                                stream.Seek(4, SeekOrigin.Current);

                                // skip attribute blocks & some data blocks
                                stream.Seek(skip * 4 + 6 * 4, SeekOrigin.Current);

然后解析事件复选标记，将其反转并保存回流中

                                var check = (byte) stream.ReadByte();

                                // check = 0x30: False
                                // check = 0x31: True
                                // check = 0x32: Undefined
                                bool? boolCheck = (check == 0x31);
                                if (check == 0x32) boolCheck = null;

                                // invert checkmark
                                boolCheck = !boolCheck;

                                if (boolCheck == null)
                                    check = 0x32;
                                if (boolCheck == true)
                                    check = 0x31;
                                if (boolCheck == false)
                                    check = 0x30;

                                stream.Seek(-1, SeekOrigin.Current);
                                stream.WriteByte(check);

现在写回新流并保存所有内容。完成。

                                // ignore all the other stuff

                                stream.Seek(0, SeekOrigin.Begin);
                                binary.FeedData(stream);
                                doc.Save();
                            }
                        }
                    }

                    break;
                }
            }
        }

        Console.Read();

注意:中断在那里是因为复选框被找到了两次，我懒得弄清楚原因。

结论这就是您如何使用 OpenXML 在电子表格中反转 activeX CheckBox 的复选标记。

附录 1这是我想出的完整代码。示例 xlsm 中只有一个 activeX 复选框:

    static void Main(string[] args)
    {
        string fileName = @"C:\Users\***\Desktop\Test.xlsm";
        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, true))
        {
            foreach (var control in doc.WorkbookPart.WorksheetParts.First().Worksheet.Descendants<Control>())
            {
                Console.WriteLine();
                Console.WriteLine("Control {0}:", control.Name);
                Console.WriteLine("Id: {0}", control.Id);

                var part = doc.WorkbookPart.WorksheetParts.First().GetPartById(control.Id) as EmbeddedControlPersistencePart;
                var xreader = OpenXmlReader.Create(part.GetStream());
                xreader.Read();

                var xml = xreader.LoadCurrentElement();
                var classID = xml.GetAttribute("classid", xml.NamespaceUri).Value;

                if (classID == "{8BD21D40-EC42-11CE-9E0D-00AA006002F3}") // it is a checkbox
                {
                    var binary = part.EmbeddedControlPersistenceBinaryDataParts.First();
                    using (var source = binary.GetStream())
                    {
                        using (var reader = new BinaryReader(source))
                        {
                            using (var stream = new MemoryStream(reader.ReadBytes((int)source.Length)))
                            {
                                // Skip random junk
                                stream.Seek(20, SeekOrigin.Begin);

                                // read additional data flags & how many additional bytes are added
                                var flags = (byte) stream.ReadByte();
                                var skip = 0;

                                if ((flags & 0x01) != 0) // Attribute Block
                                    skip++;
                                if ((flags & 0x02) != 0) // BackColor Block
                                    skip++;
                                if ((flags & 0x04) != 0) // ForeColor Block
                                    skip++;

                                // skip some bytes
                                stream.Seek(2, SeekOrigin.Current);
                                flags = (byte) stream.ReadByte();

                                if ((flags & 0x01) != 0) // Special Effect Block
                                    skip++;
                                if ((flags & 0x04) != 0) // PicturePosition Block
                                    skip++;

                                // skip some flags
                                stream.Seek(4, SeekOrigin.Current);

                                // skip attribute blocks & some data blocks
                                stream.Seek(skip * 4 + 6 * 4, SeekOrigin.Current);

                                var check = (byte) stream.ReadByte();

                                // check = 0x30: False
                                // check = 0x31: True
                                // check = 0x32: Undefined
                                bool? boolCheck = (check == 0x31);
                                if (check == 0x32) boolCheck = null;

                                // invert checkmark
                                boolCheck = !boolCheck;

                                if (boolCheck == null)
                                    check = 0x32;
                                if (boolCheck == true)
                                    check = 0x31;
                                if (boolCheck == false)
                                    check = 0x30;

                                stream.Seek(-1, SeekOrigin.Current);
                                stream.WriteByte(check);

                                // ignore all the other stuff

                                stream.Seek(0, SeekOrigin.Begin);
                                binary.FeedData(stream);
                                doc.Save();
                            }
                        }
                    }

                    break;
                }
            }
        }

        Console.Read();
    }

附录 2对于任何感兴趣的人，这里是迄今为止对二进制格式的分析。如果设置了一些标志 (IIF)，并且所有字符串都被填充为 4 字节的倍数，一些 block 将只被写入:

ClassID (first half is reverse byte order in a block)
40 1D D2 8B-42 EC-CE 11-9E 0D-00 AA 00 60 02 F3

Random Junk
00 02 38 00

Attribute Flags
        TrippleState: C0=F, E0=T
40  01  C0  80  01  00  00  00

 Cursor Flag
 |     ForeColor Flag
 |     |BackColor Flag
 |     ||Attribute Block
 |??? ?|||      
[0100 0000] 01 ...

          Accelerator Flag
          |   SpecialEffect Flag
          |   | Picture Position Flag
        ??|? ?|?|
... C0 [1000 0000] ...

Attributes (IIF Attribute Flag)
1B  08  80  2C

      BackStyle
      | Enable
      | |   Alignment
      | |    |       WordWrap
      | |    |       |            AutoSize
 ???? |?|? ??|? ???? |??? ???? ???| ????
[0001 1011 0000 1000 1000 0000 0010 1100]

Background Color (IIF BackColor Flag)
04  00  00  80  (Reverse order)

Foreground Color (IIF ForeColor Flag)
04  00  00  80  (Reverse order)

Random Junk
    Mouse Cursor
40  00  00  00

Length: Value
01  00  00  80

Length: Caption
09  00  00  80

Picture Position (IIF Picture Position Flag)
00  00  02  00 = 0, Left Top
03  00  05  00 = 1, Left Center
06  00  08  00 = 2, Left Bot
02  00  00  00 = 3, Right Top
....

Special Effect (IIF Special Effect Flag)
00  00  00  00

Accelerator Key (IIF Accelerator Flag)
61  00  00  00

Length: Group
06  00  00  80

Width (unknown Units)
B7 09 00 00

Height (unknown Units)
E5 02 00 00

Value (Padded to multiple of 4 Bytes)
30 = False
31 = True
32 = Undefined
30  xx  xx  xx

Caption (Padded to multiple of 4 Bytes)

Group Name (Padded to multiple of 4 Bytes)

Random Junk
00 02 18 00 35 00 00 00

Length: Font
07  00  00  80

Random Junk (Variable length? not deciphered)
E1 00 00 00 00 02 00 00

Font Name (Padded to multiple of 4 Bytes)

我认为它相当完整，我涵盖了 ActiveX 公开的大部分属性，跳过了位图和图标。但也许还有更多；最后我无法破译最后一 block 垃圾；它的长度可变，但我并没有真正在意，因为它在复选标记之后。