Excel电子表格中的VARCHAR大小限制 [英] VARCHAR size limit in Excel spreadsheets

问题描述

我正在尝试使用以下连接字符串将SQL Server 2008数据库中的一些数据读取到具有C＃的Excel 2007电子表格中：

I'm trying to read some data from an SQL Server 2008 database into an Excel 2007 spreadsheet with C#, using this connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=foo.xlsx;Extended Properties='Excel 12.0 XML;HDR=YES'

数据库中的一列是VARCHAR（1000）。当我尝试在电子表格中重新创建架构时，Excel的VARCHAR似乎最多支持255。本页建议单元格可以包含的字符总数约为32K，因此原则上应该可以在其中获得更长的字符串。

One of the columns in the database is a VARCHAR(1000). When I try recreating the schema in the spreadsheet, it seems like Excel's VARCHAR only supports up to 255. This page suggests that the "Total number of characters that a cell can contain" is around 32K, so in principle, it should be possible to get a longer string in.

有没有一种简单的方法可以解决255个字符的限制？

Is there a simple way to work around the 255 char limit?

推荐答案

尽管XLOPER12现在将支持最多32,767个Unicode字符长的字符串，但是xlfEvaluate（和其他）excel C-Api函数在Excel 2010中继续限制为255个字符长。如果传递了XLOPER12，它将返回xltypeErr。字符串长度大于255的字符串。

Although XLOPER12 will now support a string up to 32,767 Unicode characters long, xlfEvaluate (and other) excel C-Api function continues to be limited to 255 characters long in Excel 2010. It will return xltypeErr if it is passed an XLOPER12 with a string longer than 255.

用户在Excel中看到的所有字符串在许多版本中现在都作为Unicode字符串存储在内部。 Unicode工作表字符串的长度最多为32,767（215-1）个字符，并且可以包含任何有效的Unicode字符。

All strings the user sees in Excel have for many versions now been stored internally as Unicode strings. Unicode worksheet strings can be up to 32,767 (215 - 1) characters in length and can contain any valid Unicode character.

首次引入C API时，工作表字符串是字节字符串的长度限制为255个字符，C API反映了这些限制。使用Excel 2007，C API已更新为可处理Excel长Unicode字符串。这意味着以正确方式注册的DLL函数可以接受Unicode参数并返回Unicode字符串。

When the C API was first introduced, worksheet strings were byte strings limited in length to 255 characters, and the C API reflected these limitations. With Excel 2007, the C API is updated to handle Excel long Unicode strings. This means that DLL functions registered in the right way can accept Unicode arguments and return Unicode strings.

注意：
C API仍完全支持字节字符串为了向后兼容，但是它们仍然具有相同的255个字符的限制。除了截断字符串或​​将字符串分成多个单元格外，没有其他简单的解决方案。

Note: Byte strings are still fully supported in the C API for backward compatibility, however they still have the same 255-character limit. No easy solution other than to truncate the string, or divide the string into multiple cells.

这篇关于Excel电子表格中的VARCHAR大小限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！