Excel工作表的名称长度有限制吗？

本文介绍了Excel工作表的名称长度有限制吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

需要win32ole excel = WIN32OLE.new（'Excel.Application'） excel.Visible = 1 放置excel.version 工作簿= excel.Workbooks.Add 工作表1 =工作簿.Worksheets.Add 工作表1.Name =假假性甲状旁腺功能亢进#Length 30，罚款工作表2 =工作簿。工作表。添加工作表2.Name =Supercalifragilisticexpialidocious#Length 34，不好

我得到以下内容：

$ {$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $为工作表或图表键入无效的名称。确保： 您键入的名称不超过31个字符。 该名称不包含以下任何字符：：\ /？ * [或] 您没有将该名称留空。 HRESULT错误代码：0x80020009 异常发生。 from - ：9：in`< main>'

版本12.0表示我正在运行Excel 2007，但它抱怨工作表名称太长。我看了一下 Excel 2007规范和限制，如此处所述相关答案，我找不到任何这样的限制。 （尝试手动重命名工作表，建议可能会有这样的限制，但是）

是否有限制，是一个硬限制还是一个可以更改的限制更改Excel的配置？

解决方案

文件格式将允许最多255个字符的工作表名称，但如果Excel UI没有不要超过31个字符，不要超过31.应用程序充满了奇怪的无证限制和怪癖，并且提供了在规范内但不在测试人员测试的范围内的文件通常会导致非常奇怪的行为。 （个人最喜爱的例子：在Excel 97样式的字符串表的文件中，对if（）函数使用Excel 4.0字节码，在Excel 97中禁用了粗体的工具栏按钮。）

When I try to set a longish worksheet name using ruby and win32ole with the following code:

require "win32ole" excel = WIN32OLE.new('Excel.Application') excel.Visible = 1 puts excel.version workbook = excel.Workbooks.Add worksheet1 = workbook.Worksheets.Add worksheet1.Name = "Pseudopseudohypoparathyroidism" #Length 30, fine worksheet2 = workbook.Worksheets.Add worksheet2.Name = "Supercalifragilisticexpialidocious" #Length 34, not fine

I get the following:

12.0 -:9:in `method_missing': (in setting property `Name': ) (WIN32OLERuntimeError) OLE error code:800A03EC in Microsoft Office Excel You typed an invalid name for a sheet or chart. Make sure that: The name that you type does not exceed 31 characters. The name does not contain any of the following characters: : \ / ? * [ or ] You did not leave the name blank. HRESULT error code:0x80020009 Exception occurred. from -:9:in `<main>'

The version 12.0 indicates that I'm running Excel 2007, but it's complaining that the worksheet name is too long. I had a look at Excel 2007 specifications and limits as mentioned in this related answer, and I couldn't find it mentioning any such limit. (Trying to rename a worksheet manually suggests there may be such a limit, however)

Is there a limit, and is it a hard limit or one that can be changed by changing the configuration of Excel?

解决方案

The file format would permit up to 255-character worksheet names, but if the Excel UI doesn't want you exceeding 31 characters, don't try to go beyond 31. App's full of weird undocumented limits and quirks, and feeding it files that are within spec but not within the range of things the testers would have tested usually causes REALLY strange behavior. (Personal favorite example: using the Excel 4.0 bytecode for an if() function, in a file with an Excel 97-style stringtable, disabled the toolbar button for bold in Excel 97.)