Oracle向Excel电子表格报告避免科学记数法
我有一个输出到电子表格的 Oracle 报告。自动生成电子表格 获取大量数字并将其转换为科学记数法。
我试图通过以下方式避免这种情况:
1)在查询中添加一个空格,如下所示
select ' '||24534534534345353 "Really Long Number" from dual;
2)在查询中添加一个撇号('),如下所示
select ''''||24534534534345353 "Really Long Number" from dual;
上述两种方法来自 http://support.microsoft.com/kb/214233 不起作用。我仍然得到 科学计数法。
对于 1,Excel 会简单地忽略空格
对于 2,引号按字面意思并与数字一起打印。[奇怪的是,当我在生成报告后通过删除/添加数字来编辑单元格时,撇号(')消失。]
我还尝试过带有空格的 LPAD()/RPAD() 。还有其他解决方法吗?
I have an oracle report that outputs to an spreadsheet. The spreadsheet automatically
takes large numbers and converts them to scientific notation.
I've tried to avoid this by
1)Prepending a blank space like below in the query
select ' '||24534534534345353 "Really Long Number" from dual;
2)Prepending an apostrophe(') like below in the query
select ''''||24534534534345353 "Really Long Number" from dual;
The above two methods from http://support.microsoft.com/kb/214233 didn't work. I still get
the scientific notation.
For 1, the space is simply ignored by excel
For 2, the quote is taken literally and printed along with the number.[Strangely, When I edit the cell by removing/adding a digit after the report is generated, the apostrophe(') goes away.]
I've also tried LPAD()/RPAD() with blank space. Is there any other workaround for this?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我发现了以下使用不间断空格字符的解决方法:
它不是很简洁,但这是我能得到的最接近的。
I've found the following workaround with the non-breaking space character:
It isn't very neat but this is the closest that I could get.
您可以在 Excel 中将单元格格式设置为
0
吗?这将重新显示完整的数字:(抱歉古老的德国 Excel 版本...)
如果您想在 VBA 中执行此操作,尝试类似 的操作,
这将为当前选定的单元格执行此操作。
Can you just set the cell format to
0
in Excel? This will redisplay the full number:(sorry for the ancient German Excel version...)
If you want to do it in VBA, try something like
which will do it for the currently selected cell(s).