blocks|key|1147341|text|在Excel中手动重命名工作表时，您会遇到31个字符的限制，所以我建议这是一个硬限制。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1147342|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Renaming a worksheet manually in Excel, you hit a limit of 31 chars, so I'd suggest that that's a hard limit.

blocks|key|1147410|text|文件格式最多允许255个字符的工作表名称，但如果Excel+UI不希望超过31个字符，请不要尝试超过31个字符。App充满了奇怪的无文档记录的限制和怪癖，向它提供符合规范但不在测试人员测试范围内的文件通常会导致非常奇怪的行为。(个人最喜欢的示例:在具有Excel+97样式的字符串表格的文件中，对if()函数使用Excel+4.0字节码时，禁用了Excel+97中的粗体工具栏按钮。)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1147411|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.)

blocks|key|1147476|text|我的解决方案是使用一个简短的昵称(少于31个字符)，然后将整个名称写在单元格0中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1147477|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

My solution was to use a short nickname (less than 31 characters) and then write the entire name in cell 0.

blocks|key|1147513|text|我使用下面的vba代码，其中filename是包含我想要的文件名的字符串，函数RemoveSpecialCharactersAndTruncate的定义如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1147514|worksheet1.Name+=+RemoveSpecialCharactersAndTruncate(filename)

'Function+to+remove+special+characters+from+file+before+saving

Private+Function+RemoveSpecialCharactersAndTruncate$(ByVal+FormattedString$)
++++Dim+IllegalCharacterSet$
++++Dim+i+As+Integer
'Set+of+illegal+characters
++++IllegalCharacterSet$+=+"*."+&+Chr(34)+&+"//\[]:;%7C=,"
++++'Iterate+through+illegal+characters+and+replace+any+instances
++++For+i+=+1+To+Len(IllegalCharacterSet)+-+1
++++++++FormattedString$+=+Replace(FormattedString$,+Mid(IllegalCharacterSet,+i,+1),+"")
++++Next
++++'Return+the+value+capped+at+31+characters+(Excel+limit)
++++RemoveSpecialCharactersAndTruncate$+=+Left(FormattedString$,+_
+++++++++++++++++++++++++++Application.WorksheetFunction.Min(Len(FormattedString),+31))
End+Function|code-block|syntax|javascript|1147515|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I use the following vba code where filename is a string containing the filename I want, and Function RemoveSpecialCharactersAndTruncate is defined below:

<pre><code>worksheet1.Name = RemoveSpecialCharactersAndTruncate(filename)

'Function to remove special characters from file before saving

Private Function RemoveSpecialCharactersAndTruncate$(ByVal FormattedString$)
 Dim IllegalCharacterSet$
 Dim i As Integer
'Set of illegal characters
 IllegalCharacterSet$ = "*." &amp; Chr(34) &amp; "//\[]:;|=,"
 'Iterate through illegal characters and replace any instances
 For i = 1 To Len(IllegalCharacterSet) - 1
 FormattedString$ = Replace(FormattedString$, Mid(IllegalCharacterSet, i, 1), "")
 Next
 'Return the value capped at 31 characters (Excel limit)
 RemoveSpecialCharactersAndTruncate$ = Left(FormattedString$, _
 Application.WorksheetFunction.Min(Len(FormattedString), 31))
End Function
</code></pre>

blocks|key|933349|text|我刚刚在Windows+7上使用Excel+2013测试了几个路径。我发现总体路径名限制为213，基名长度为186。至少超出基名长度的错误对话框是清楚的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|933350|📷|atomic|offset|length|933351|尝试将不太长的基名移动到太长的路径名也是非常清楚的：|933352|933353|​|933354|不过，路径名错误具有欺骗性。完全没有帮助：|933355|933356|933357|这是一个懒惰的微软限制。这些随意的长度限制没有很好的理由，但最终，这是错误对话框中的一个真正的bug。|933358|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/1d25b95b46e138e7a3fe7c039cc5851e.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/d2ffdf1885535149d8aee62153e55e15.png|2|https://ask.qcloudimg.com/http-save/yehe-900000/c1da67ef0d29385f68edc2315c4c4395.png^0|0|0|1|0|0|0|0|1|1|0|0|0|0|1|2|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|15|8|@]|9|@$E|16|F|17|1|18]]|A|$]]|$1|G|3|H|5|6|7|19|8|@]|9|@]|A|$]]|$1|I|3|C|5|D|7|1A|8|@]|9|@$E|1B|F|1C|1|1D]]|A|$]]|$1|J|3|K|5|6|7|1E|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1F|8|@]|9|@]|A|$]]|$1|N|3|C|5|D|7|1G|8|@]|9|@$E|1H|F|1I|1|1J]]|A|$]]|$1|O|3|K|5|6|7|1K|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1L|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y|Z|-4]]|10|$5|U|V|W|A|$X|11|Z|-4]]|12|$5|U|V|W|A|$X|13|Z|-4]]]]

I just tested a couple paths using Excel 2013 on on Windows 7. I found the overall pathname limit to be 213 and the basename length to be 186. At least the error dialog for exceeding basename length is clear: <img src="https://i.stack.imgur.com/2NOeU.png" alt="basename error"> 

And trying to move a not-too-long basename to a too-long-pathname is also very clear:<a href="https://i.stack.imgur.com/b7CoW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/b7CoW.png" alt="enter image description here"></a>

The pathname error is deceptive, though. Quite unhelpful:<a href="https://i.stack.imgur.com/xMMvF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xMMvF.png" alt="enter image description here"></a>

This is a lazy Microsoft restriction. There's no good reason for these arbitrary length limits, but in the end, it’s a real bug in the error dialog.

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

<pre><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
</code></pre>

I get the following:

<pre><code>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 `&lt;main&gt;'
</code></pre>

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 <a href="http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx" rel="noreferrer">Excel 2007 specifications and limits</a> as mentioned in <a href="https://stackoverflow.com/questions/644429/what-is-excel-2007-workbook-name-size-limit-why/650478#650478">this related answer</a>, 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?

Is there a limit on an Excel worksheet's name length?

Windows

当我尝试使用拼音和win32ole使用以下代码设置较长的工作表名称时：require "win32ole"excel = WIN32OLE.new('Excel.Application')excel.Visible = 1puts excel.versionworkbook = excel.Workbooks.Addw...

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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