概述

工作中有时候需要将数据库导出数据到Excel中，供业务人员使用，本文将介绍如何借助Dbvisualizer实现大数据导出到Excel一种思路以及详细操作

工具

DbVisualizer Pro 10.0.20

创建查询窗口查询结果

新建查询窗口，并执行SQL查询获取结果，如下图

使用导出向导

点击查询结果上方导出功能，弹出向导页面，Output Format选择CSV,这里不选XLS涉及到Excel一个限制：

Excel2003(.xls)最大支持65536行， Excel2007(.xlsx)最大支持1048576行；

如果导出的数据大于65536行又小于1048576行，可以采用先导出CSV再通过Excel另存为.xlsx;在导出CSV格式数据的时候需要注意分隔符的选择，Excel转成CSV文件，在Windows下默认的列表分隔符是逗号,,所以查询结果中所有,需要先使用SQL函数替换，避免Excel转成CSV文件时候拆分错误

点击Next检查导出字段

点击Next预览前100条结果

点击Next选择保存路径

点击Settings->Copy Settings to Clipboard复制向导配置到剪切板

创建查询窗口并贴入向导配置

创建查询窗口并贴入向导配置，查询窗口中Max Rows大小决定导出数据总行数，-1代表全部导出

配置说明以及修改

Filename 文件导出路径，根据实际情况修改填写
在配置末新增执行SQL，替换下配置中select 1 from dual;成需要导出的查询SQL，最后在查询窗口运行脚本即可导出CSV文件

@export on;
@export set AppendFile="false"
BinaryFileDir="C:\Users\30456"
BinaryFormat="Don't Export"
BooleanFalseFormat="false"
BooleanTrueFormat="true"
CLOBFileDir="C:\Users\30456"
CLOBFormat="Don't Export"
ClipboardFlavor="Unicode String"
ClipboardName=""
CsvColumnDelimiter=","
CsvColumnHeaderIsColumnAlias="true"
CsvIncludeColumnHeader="true"
CsvIncludeColumnHeaderPerResult="false"
CsvIncludeSQLCommand="Don't Include"
CsvRemoveNewlines="false"
CsvRowCommentIdentifier=""
CsvRowDelimiter="\n"
CsvSplitFileSize="-1"
DateFormat="yyyy-MM-dd"
DecimalNumberFormat="Unformatted"
DecimalNumberSeparator="."
Destination="File"
Encoding="GBK"
ExcelColumnHeaderIsColumnAlias="true"
ExcelFileFormat="OOXML, Excel 2007 (xlsx)"
ExcelIncludeColumnHeader="true"
ExcelIncludeSQLCommand="false"
ExcelIntroText=""
ExcelSheetName=""
ExcelTextDateTime="true"
ExcelTextOnly="false"
ExcelTitle=""
Filename="C:\Users\30456\Desktop\test.csv"
Format="CSV"
HtmlColumnHeaderIsColumnAlias="true"
HtmlConvertChars="true"
HtmlFooter="[ Generated by: <a href=""http://www.dbvis.com"">DbVisualizer Pro 10.0.20</a> ]"
HtmlIncludeSQLCommand="false"
HtmlIntroText=""
HtmlPerTableHeader="<tr>
  <td class=""label"">Date:</td>
  <td class=""value""><b>DbV:dbvis-timestamp:VbD</b></td>
</tr>
<tr>
  <td class=""label"">Columns:</td>
  <td class=""value""><b>DbV:dbvis-column-count:VbD</b></td>
</tr>
<tr>
  <td class=""label"">Table:</td>
  <td class=""value""><b>DbV:dbvis-object:VbD</b></td>
</tr>
"
HtmlPerTableHeaderGridExport="<tr>
  <td class=""label"">Date:</td>
  <td class=""value""><b>DbV:dbvis-timestamp:VbD</b></td>
</tr>
<tr>
  <td class=""label"">Rows:</td>
  <td class=""value""><b>DbV:dbvis-row-count:VbD</b></td>
</tr>
<tr>
  <td class=""label"">Columns:</td>
  <td class=""value""><b>DbV:dbvis-column-count:VbD</b></td>
</tr>
"
HtmlTitle=""
ImageHeight=""
ImageWidth=""
JSONColumnHeaderIsColumnAlias="true"
JSONSplitFileSize="-1"
JSONStyle="Array"
MaxRows="-1"
NumberFormat="Unformatted"
NumberGroupingSeparator=","
Orientation="Portrait"
QuoteDuplicateEmbedded="true"
QuoteTextData="None"
ShowNullAs="(null)"
SqlAfterExportStmts=""
SqlBeforeExportStmts=""
SqlBeginIdentifier="`"
SqlBlockBeginDelim="--/"
SqlBlockEndDelim="/"
SqlDelimitedIdentifiers="false"
SqlEditor="New Editor"
SqlEditorPosition="Last"
SqlEndIdentifier="`"
SqlGroupBy="Object"
SqlIncludeAutoGeneratedValues="true"
SqlIncludeCreateDDL="false"
SqlIncludeSQLCommand="Don't Include"
SqlQualifier=""
SqlQualifyColumnName="true"
SqlQualifyObjectName="false"
SqlRowCommentIdentifier="--"
SqlSeparator=";"
SqlSplitFileSize="-1"
TableName="we_chat_tag"
TimeFormat="HH:mm:ss"
TimeStampFormat="yyyy-MM-dd HH:mm:ss"
TxtColumnHeaderIsColumnAlias="true"
TxtIncludeColumnHeader="true"
TxtIncludeSQLCommand="Don't Include"
TxtRemoveNewLines="false"
TxtRowDelimiter="\n"
TxtSpacesBetweenColumns="1"
XmlColumnHeaderIsColumnAlias="true"
XmlIncludeSQLCommand="false"
XmlIntroText=""
XmlStyle="DbVisualizer";
--下面部分属于额外新增配置，替换SQL查询脚本即可导出想要结果
--    执行的SQL脚本  --  START

select  1 from dual;

--    执行的SQL脚本  --  END
@export off;