GridView/Listview 绑定、模板和自动生成字段中的数据类型
背景：我正在从数据库填充大量 asp.net c# GridView 和 ListView，随后用户可以将它们导出到 Excel。我想导出为原生 Excel（而不是 html）。我无法使用办公自动化，我正在使用 JET，效果很好。我无法控制用户的机器。
问题：在进行导出时，您必须告诉 Jet 每个字段的类型，在我的例子中是“文本”(varchar) 或“数字”(双精度)。不同之处在于，如果导出数字列，用户可以在 Excel 中对数据求和，而导出的字符串带有前导撇号，因此在算术中用处不大。
目前，我解析 Grid/ListView 的第一个数据行，检查每个值是数字还是文本，并相应地为列分配类型。这是有效的，除非我在第一列中有一些看起来是数字但实际上是文本字符串的东西。我不想解析每一行以确保我具有正确的数据类型，因为其中一些导出非常大。
当我从数据库加载 Grid/ListView 时，数据库当然知道每个字段是什么类型。所以我的问题是...如何提取 Grid/ListView 项目后面的数据库项目的类型？我可以将其显式编码为项目的属性，但这会重复我已经拥有的信息，只要我能够获取它即可。
我知道如果我有一个 DataTable，那么我可以从中获取基础类型，但大多数情况下我没有方便的表，只有 Grid/ListView。
-- （编辑） 请注意，如果您尝试将空字符串插入可为空的数字列，Jet 将抛出异常。执行此操作的方法是从插入语句中省略该列名，或输出零。
Background: I'm populating lots of asp.net c# GridViews and ListViews from a database and subsequently users may export them to Excel. I want export as native Excel (not html). I can't use office automation, and I'm using JET which works fine. I have no control over users' machines.
Question: When doing the export, you have to tell Jet what type each field is, in my case "text" (varchar) or "numeric" (double). The difference is that if you export a numeric column, the users can sum the data in Excel, where as strings are exported with a leading apostrophe and so are not much use in arithmetic.
Currently I parse the first data row of the Grid/ListView, check if each value is numeric or text, and assign a type to the column accordingly. That works, except for when I have something in the first column which looks numeric but in fact is a text string. I don't want to parse every row in order to be sure I have the correct data type as some of these exports are quite large.
When I load the Grid/ListView from the database, the database certainly knows what type each field is. So my question is... how do I extract the type of the database item behind a a Grid/ListView item? I could explicitly code it as an attribute on the item, but that's duplicating information I already have, if only I can get to it.
I know that where I have a DataTable then I can get the underlying type from that, but mostly I don't have tables handy, just the Grid/ListView.
--
(edit)
Note that Jet will throw if you try to insert an empty string into a nullable numeric column. The way to do this is to omit that column name from the insert statement, or output a zero.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为您想要做的是在绑定后找出
GridView或ListView的底层DataSource？如果是这样，这是不可能的，因为一旦绑定，控件就不会真正跟踪
DataSource。它们主要用于填充其列、模板等，然后仅维护这些项目。我可以提供的唯一建议是将
DataSource存储在另一个位置，以便您稍后可以访问它，然后使用它来发现用于每列的基础数据类型。如果需要，您可以将其存储在Session或ViewState中，但这会增加开销。另一种选择是在绑定之前确定数据类型，然后将它们存储在具有某种类型格式的隐藏字段中，您稍后可以使用它来复制正在执行的操作。例如（不好的例子，但给了你一个想法）在隐藏字段中存储以下数据：
不太漂亮，但说明了我正在考虑的基本想法。
I think what your trying to do is figure out the underlying
DataSourceof theGridVieworListViewafter it has been bound?If so that is no possible since the controls don't really keep track of the
DataSourceonce the has been bound. They are mainly used to fill it's columns, templates, etc and then just those items are maintained.The only recommendation I can offer is to store the
DataSourcein another location so that you can access it later and then use it to discover the underlying datatypes used for each column. You could store it in theSessionorViewStateif you want but this will add overhead. The other option would be to figure out the datatypes before binding and then storing them in a hidden field with some type of format you can use later to replicate what was being done.Eg (bad example but gives you an idea) Store in a hidden field the following data:
Not pretty but illustrates the basic idea I am thinking of.
据我所知，答案是“你不能”。我的数据源仅返回字符串，因此您无法有效地解析任何自动创建的 DataTable 的列类型。
我所做的是...
我可以通过查看网格/列表的第一行来“嗅探”大多数列类型。仅当第一列数据为空（这不会告诉您任何信息），或者第一列暗示数字但列数据是字母数字等时，此操作才会失败。
因此，对于那些我知道存在问题或测试显示存在问题的列，我使用自定义属性来注释那些特定的 Grid 或 ListView 项目，该属性强制将项目导出为文本或数字。我默认为文本，所以在实践中我大多只需要注释那些我特别想要呈现为数字的条目。例外的是一些以前导零开头的 ID 号，我想以这种方式呈现，因此明确地作为文本。
如果我有一个数据表（例如图表），并且我无法执行嗅探技巧（见上文）或注释技巧（同上），那么我将列类型的映射传递给我的导出代码。
总之，在某些情况下，我被迫在 Grid/ListView 模板字段或字段映射中重复列类型定义。无法赢得所有人。它确实有效。
As far as I can tell the answer is "you can't". My data sources return only strings, so you can't usefully parse any auto-created DataTable for column types.
What I did was...
Most column types I can "sniff" from looking at the 1st row of the grid/list. This fails only if 1st column data is null (which tells you nothing), or if the 1st column implies numeric but the column data is for example alpha-numeric.
So for those columns where I know there's a problem, or where testing shows there to be so, I annotate those specific Grid or ListView items with a custom attribute which forces the items to be exported as text or numeric. I default to text, so in practice I mostly just have to annotate those entries I specifically want to render as numeric. Exceptions are some ID numbers which start with leading zeros, which I want to render that way hence go explicitly as text.
Where I have a DataTable (eg for charts), and I can't do the sniffing trick (see above), or the annotation trick (ditto), then I pass a map of the column types to my export code.
In summary, in some cases I am forced to repeat the column type definition either in the Grid/ListView template fields, or in the field-map. Can't win them all. It does work.