SQL脚本在Excel输出文件中显示前导0
我有 sql 脚本“example.sql”： SPOOL &1 Select '<.TR>'||'<.TDalign="left">'||column_name||'<./ TD>'||'<.TR>'从表1； spool off..将其内容转储到cshell脚本“getdata.csh”这是我从sql脚本获取数据到csh脚本的方式sqlplus $ORA_UID/$ORA_PSWD @${SQL}example.sql $ {DATA}${ext} 一旦我从中提取数据，我就会通过组合 3 个文件头来创建一个 Excel 文件。 html ; <.head> <.标题) 标题 <./标题> <./头> <.body> <.表> <.tr> <.th>列名<./th> <.tr>包含查询结果和预告片的 ext 文件 <./tr> <./表> <./body> <./html> 我将此文件保存为 .xls 并通过电子邮件作为附件发送。现在我的问题是 Column_name 的数据以 0 开头，但是当我打开 excel 文件时，前导 0 消失了，但我想保留那个0..那么我可以添加什么来确保电子邮件附加的excel文件在另一端打开时会有前导0..请提供任何帮助都会很好
I have sql script "example.sql": SPOOL &1 Select '<.TR>'||'<.TD align="left">'||column_name||'<./TD>'||'<.TR>' from table1; spool off..which dumps it contents to cshell script "getdata.csh" this is how i get data from sql script to csh script sqlplus $ORA_UID/$ORA_PSWD @${SQL}example.sql ${DATA}${ext} once i extract data from it i create a excel file by combining 3 files header. html <html>ext file that has query results and trailer.html
<.head>
<.title)
Title
<./title>
<./head>
<.body>
<.table >
<.tr>
<.th>Column Name<./th>
<.tr><./tr> and i save this file as .xls and send it through email as attachment.. Now my problem is Column_name has data that starts with 0 but when i open excel file leading 0 are gone but i wanna keep that 0.. so what can i add to make sure that email attached excel file will have leading 0 when that is opened on the other side.. plz any help would be good
<./table>
<./body>
<./html>
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
使用 oracle：
假设你的属性名为“number”
Using oracle:
Say your attribute is called 'number'
使用 Excel 对象模型或宏进入 Excel 文件抓取列并更改格式。
在你的情况下：
Use the excel object model, or a macro to go into the excel file grab the column and change the formatting.
In your case:
如果您要即时生成 Excel 文件，则可以在这些数字前面加上撇号，即 '
这会导致 Excel 将数字视为字符串。唯一的缺点是，如果工作表中有任何使用这些数字的方程，可能会产生一些副作用。
If you're generating the excel file on the fly, you could prepend those numbers with an apostrophe, ie '
This causes Excel to treat the number like a string. The only downside is it might cause some side effects if the sheet has any equations that use those numbers.
我过去处理过这个问题，这个问题严格来说是Excel格式的“功能”。不幸的是，我没有资源来完全测试答案，但您可以尝试以下两件事。
awk '{$1= "="" $1 """;打印 $0}' inFile > outFile
缺点是您现在告诉 Excel 将这些值视为字符串。如果您对这些值进行任何奇特的计算，您可能会遇到不同的问题。
如果您向他们寻求帮助，您可能还想告诉人们您正在使用的 Excel 版本。
IHTH
I have dealt with this issue in the past, and the problem is strictly a "feature" of Excel formatting. Unfortunately, I don't have the resources to completely test an answer, but here are two things you can try.
awk '{$1= "="" $1 """; print $0}' inFile > outFile
The downside is that you're now telling Excel to treat these values as strings. If you're doing any fancy calculations on these values you may have different problems.
You might also want to tell people what version of Excel you are using, if you go to them for help.
IHTH