将数据从 Oracle SQL Developer 导出到 Excel .xlsx
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44724970/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export data from Oracle SQL Developer to Excel .xlsx
提问by IAmRapson
I have a small project where data from Oracle SQL Developer needs to be exported to Excel (using commands rather than tools in SLQ Developer), then create a graph. Using "spool" I can export to csv fine (but cant make a graph in csv) but when I try to export to xlsx it corrupts the whole excel sheet saying
我有一个小项目，需要将 Oracle SQL Developer 中的数据导出到 Excel（使用命令而不是 SLQ Developer 中的工具），然后创建一个图表。使用“假脱机”我可以导出到 csv 很好（但不能在 csv 中制作图形）但是当我尝试导出到 xlsx 时它会破坏整个 excel 表说
"Excel cannot open the file "ExcelFile.xlsx" because the file format or file extention
is not valid. Verify that the file has not been corrupted and that the
file extension mathces the format of the file."
Here is the code I used in SQL Developer.
这是我在 SQL Developer 中使用的代码。
spool FileLocation\ExcelFile.xlsm
SELECT * FROM Table;
spool off;
Is there any way I can stop the data from becoming corrupted or is there another way to export data to a .xlsx file?
有什么方法可以阻止数据损坏，还是有其他方法可以将数据导出到 .xlsx 文件？
回答by thatjeffsmith
Nooooooo.
呜呜呜。
set sqlformat csv
spool c:\file.sql
select * from table;
spool off;
then open the file in excel.
然后用excel打开文件。
OR
或者
Run your query interactively.
以交互方式运行您的查询。
Right click on the grid, Export > XLSX. Open the file.
右键单击网格，导出 > XLSX。打开文件。
Spool only writes the query output to the file, it doesn't look at the file extension and figure out HOW to write the output at that point.
Spool 只将查询输出写入文件，它不会查看文件扩展名并找出此时如何写入输出。
So you either have to code it yourself via the query, or use one of the format outputs we support
因此，您要么必须通过查询自己编码，要么使用我们支持的格式输出之一
SET SQLFORMAT
CSV
JSON
DELIMITED
XML
HTML
INSERT
LOADER
Use 'help set sqlformat' for help.
使用“help set sqlformat”获取帮助。
回答by kpazik
Hi sql developer from what I know for exporting is using sqlplus(code is same) so perhabs there are other ways but this one should be good enough
嗨，我知道导出的 sql 开发人员正在使用 sqlplus（代码相同），所以也许还有其他方法，但这应该足够了
I would try changing first line to look like this:
我会尝试将第一行更改为如下所示：
spool ExcelFile.xls
Probably you also need to turn on
可能你还需要开启
SET MARKUP HTML ON
http://www.orahow.com/2015/09/spool-sqlplus-output-to-excel-format.html
http://www.orahow.com/2015/09/spool-sqlplus-output-to-excel-format.html
Anyway there is workaround - you can just generate .CSV file and then open it in excel and save as .xlsx file
无论如何有解决方法 - 您可以生成 .CSV 文件，然后在 excel 中打开它并另存为 .xlsx 文件
