Oracle向Excel电子表格报告避免科学记数法

发布于 2024-11-26 12:16:59 字数 604 浏览 9 评论 0原文

我有一个输出到电子表格的 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

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

评论(2

平安喜乐 2024-12-03 12:16:59

我发现了以下使用不间断空格字符的解决方法:

select 24534534534345353||chr(160) "Really Long Number" from dual;

它不是很简洁,但这是我能得到的最接近的。

I've found the following workaround with the non-breaking space character:

select 24534534534345353||chr(160) "Really Long Number" from dual;

It isn't very neat but this is the closest that I could get.

花开雨落又逢春i 2024-12-03 12:16:59

您可以在 Excel 中将单元格格式设置为 0 吗?这将重新显示完整的数字:

在此处输入图像描述
(抱歉古老的德国 Excel 版本...)

如果您想在 VBA 中执行此操作,尝试类似 的操作,

Sub ShowFullNumber()
    Selection.NumberFormat = "0"
End Sub

这将为当前选定的单元格执行此操作。

Can you just set the cell format to 0 in Excel? This will redisplay the full number:

enter image description here
(sorry for the ancient German Excel version...)

If you want to do it in VBA, try something like

Sub ShowFullNumber()
    Selection.NumberFormat = "0"
End Sub

which will do it for the currently selected cell(s).

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文