为白痴自动更正 Excel 电子表格

发布于 2024-07-30 08:34:57 字数 669 浏览 1 评论 0原文

我有一个客户无法正确格式化电子表格。我已经十次要求他们遵循合理的指导方针（如用正确的数据类型格式化列），但由于他们缺乏智慧或专业精神或两者兼而有之，他们就是不能/不会这样做。

我为电子表格设置了 ODBC 连接，并提取除包含产品计数或产品价格的列之外的数据。除非我在 Excel 中手动将每一列的类型设置为“数字”，否则这些值在显示结果的 ASP 页面中显示为空。

是否可以编写一条 SQL 语句来改变传入的数据类型？我不在乎是否将每一列转换为字符串，只要 ADODB.Recordset 将显示所有列的值即可。

代码：

<%
Dim MM_SQLSource

MM_SQLSource = "SELECT * FROM [Inventory$]"
Set rsGetExcelInfo = Server.CreateObject("ADODB.Recordset")
rsGetExcelInfo.ActiveConnection = MM_Excel_Connect
rsGetExcelInfo.Source = MM_SQLSource
rsGetExcelInfo.CursorType = 0
rsGetExcelInfo.CursorLocation = 2
rsGetExcelInfo.LockType = 1

rsGetExcelInfo.Open()
%>

原文

I have a client who is incapable of formatting spreadsheets properly. I've asked them 10 times over to follow a sensible guideline (As in FORMAT THE COLUMNS WITH THE CORRECT DATATYPE) but in their lack of intelligence or professionalism or both, they just can't /won't do it.

I have an ODBC connection set up for the spreadsheets and pulls the data except for the columns that contain product counts or product prices. Unless I manually set the type for each of those columns to "Number" in Excel, the values appear null in the ASP page that displays the results.

Is it possible to write a SQL statement that will alter the datatype as it comes in? I don't care it if converts every column to a string as long as the ADODB.Recordset will display the values of all of the columns.

Code:

<%
Dim MM_SQLSource

MM_SQLSource = "SELECT * FROM [Inventory$]"
Set rsGetExcelInfo = Server.CreateObject("ADODB.Recordset")
rsGetExcelInfo.ActiveConnection = MM_Excel_Connect
rsGetExcelInfo.Source = MM_SQLSource
rsGetExcelInfo.CursorType = 0
rsGetExcelInfo.CursorLocation = 2
rsGetExcelInfo.LockType = 1

rsGetExcelInfo.Open()
%>

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

轮廓§ 2024-08-06 08:34:58

这可能不完全是你客户的错。 Excel 有时对于如何设置列格式有自己的想法。例如，如果您有一列包含邮政编码，其中一些带有 Plus 4，另一些则没有，那么该列的格式几乎是一团糟。

至于你原来的问题，根据这个网站，CONVERT是一个有效的 SQL 标量函数，所以也许类似的东西

SELECT CONVERT(BadField, SQL_CHAR) AS FixedField FROM [Inventory$]

可以工作？

我的第一反应是建议使用 COM 从电子表格中读取数据。我很确定您能够读取每个单元格的格式并进行相应的处理，但我总是发现通过 COM 使用 Excel 很困难，而且速度不是很快（我只用 C++ 完成过）。

It may not be entirely your client's fault. Excel sometimes has its own ideas about how a column should be formatted. For example, if you have a column containing zip codes, some with the Plus 4, others without, it is pretty much a crap shoot as to how that column will be formatted.

As for your original question, according to this site, CONVERT is a valid SQL scalar function, so maybe something like

SELECT CONVERT(BadField, SQL_CHAR) AS FixedField FROM [Inventory$]

might work?

My first inclination was to suggest using COM to read the data from the spreadsheet. I'm pretty sure you would be able to read each cell's format and deal with it accordingly, but I always found Excel via COM to be difficult and not terribly fast (I've only done it from C++).

回复收藏 0 原文