使用正则表达式解析Excel范围定义 [英] Using regex to parse Excel range definitions

问题描述

最终的目标是拥有一个审核工具，该工具将通过Open XML读取Excel文件的Defined Names部分，并解释范围名称的定义.有些范围包含单个单元格，有些则包含矩形范围，有些则非常复杂.这是具有各种定义的文件的已定义名称部分:

The ultimate goal is to have an auditing tool that will read the Defined Names portion of an Excel file through Open XML, and interpret the definition of the range names. Some ranges contain a single cell, some a rectangular range, and some are rather complex. Here''s the defined names portion of a file with a variety of definitions:

<x:definedNames xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:definedName name="bigRange">'Sht2 Test'!$C$3:$E$8</x:definedName>
  <x:definedName name="cellabove">Sheet1!A1048576</x:definedName>
  <x:definedName name="multirange">'Sht2 Test'!$B$16:$C$18,'Sht2 Test'!$E$14:$F$16,'Sht2 Test'!$H$12:$J$15</x:definedName>
  <x:definedName name="TestRange">2*TestRange1</x:definedName>
  <x:definedName name="TestRange1">Sheet1!$C$3</x:definedName>
  <x:definedName name="ThreeD">Sheet1:Sheet3!$A$16</x:definedName>
</x:definedNames>

使用Expresso，我已经能够构造以下模式:

Using Expresso I''ve been able to construct the following pattern:

(?<sheet>\''?.*\''?|.*)?\!\$?(?<col>[a-zA-Z]+)\$?(?<rownum>\d+)|\$?(?<col>[a-zA-Z]+)\$?(?<rownum>\d+) 

它将捕获工作表名称，列字母和行号，并适用于以下简单变体:

It will capture the sheet name, the column letter, and the row number, and it works on these simple variations:

C66<br />
$AB$15<br />
''Sht2 Test''!$C$3<br />
Sheet2!$CC$44<br />

我无法弄清楚的是，哪种模式可以在Sheet1上工作！$ A $ 1:$ C $ 4给我两个单独的捕获，一个捕获到单元格A1，一个捕获到单元格C4 ，并且两者都具有相同的工作表名称

是否有不涉及巨大，混乱模式的解决方案?如果是这样，该模式可能是什么?

谢谢，
Duke

What I''m unable to figure out, and what would be nice to have, is a pattern that would work on Sheet1!$A$1:$C$4 to give me 2 separate captures, one for cell A1 and one for cell C4, and both with same sheet name

Is there a solution that doesn''t involve a huge, messy pattern? If so, what might that pattern be?

Thanks,
Duke

推荐答案

C

3:

E

这篇关于使用正则表达式解析Excel范围定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！