数据库到 Excel 电子表格
将 SQL 数据库中的数据选择到 Excel 电子表格，包含标题和格式设置
从 SQL 命令
到格式化的 Excel
引言
本项目演示了如何根据 SQL SELECT 命令的结果创建格式化的 Excel 工作表。生成的 Excel 电子表格包含基于 SQL 结果返回的列的格式化、可排序和可筛选的标题。列可以在电子表格中格式化为文本或货币。用户可以在同一个 Excel 工作簿中创建多个工作表，其中包含来自不同 SQL 命令或数据库的结果。工作表的列标题取决于 SQL 语句的结果，更改 Select 语句，Excel 列也会随之改变。
我创建这个项目是为了能够快速以商业友好、专业的方式生成临时信息，而无需为每个请求编写代码。
要求
本项目是使用 Visual Studio 2010 和 Office Excel 2007 生成的。部分代码使用了 VS 2010 中添加的功能，部分 Excel 命令需要 2007 版 Office。这些概念可以与早期版本的 VS 和 Office 配合使用，只需对程序进行一些修改。此程序使用 SQL 特定的 ADO 命令，但我认为它也可以与通用的 ODBC 版本命令配合使用。
这个问题应该可以在任何安装了 Framework V4.0、Office 2007 并可以访问 SQL 数据库的机器上运行。
操作概述
- 用户输入连接字符串、SQL 命令和工作表名称。
- 代码将访问数据库并根据结果构建一个
DataTable。 - 代码然后从
DataTable列数据生成 Excel 标题。 - 代码将
DataTable中的DataRows添加到 Excel 工作簿。 - 用户可以根据需要保存或放弃电子表格。
程序
我使用 WPF 作为用户界面创建了这个项目。界面很简单：用户输入连接字符串、SQL 命令、工作表名称，然后按“导出到 Excel”按钮。
从 SQL 构建 DataTable
点击事件处理程序对输入的数据进行最少的验证，然后调用 PerformSqlGet() 通过 ADO.NET 从数据库返回 DataTable。
conn = new SqlConnection(_ConnectionString);
cmd = new SqlCommand(_Command);
此命令的类型（StoredProcedure 或 Text）基于命令的第一个单词。创建适配器来处理 Command，并使用适配器填充 DataTable。
cmd.Connection = conn;
cmd.CommandTimeout = _Timeout;
adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
加载 Excel
第一次按下此按钮时，Excel 会在后台加载。与 Excel 的通信是通过调用 COM 互操作来实现的，但现在编写这些代码似乎比以前版本的 Excel 和 Visual Studio 容易得多。我没有定位和下载 Excel 的 Primary Interop Assembly，而是直接为我的程序添加了一个 Excel.exe 的引用（在本例中是：c:\Program Files\Microsoft Office\Office12\EXCEL.EXE）。这为我生成了 interop assembly。在我的代码中，我添加了
using Microsoft.Office.Interop.Excel;
并声明了一些类变量
Microsoft.Office.Interop.Excel.Application _ExcelApp = null;
Microsoft.Office.Interop.Excel.Workbook _wb = null;
现在我已准备好加载 Excel。
在第一次按下“导出到 Excel”按钮时，我加载 Excel 应用程序，创建一个工作簿，并向工作簿添加一个工作表。
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_wb = _ExcelApp.Workbooks.Add();
Worksheet ws = AddWorksheetToWorkbook(_wb, txtWorksheetName.Text);
_ExcepApp 是后台运行的 Excel 应用程序实例的指针。Worksheet（也称为 Microsoft.Office.Interop.Excel.WorkSheet）是我将要向其中添加 DataTable 数据的 Excel 表格。
加载 Excel 工作表
完成后，我调用我的 AddToExcel() 方法来执行将标题和数据添加到工作表的任务。在 AddToExcel() 中，我使 Excel 不可见以获得轻微的性能提升。然后，我将 DataTable 中的列名添加到 Excel 的第一行。
for (int Idx = 0; Idx < dt.Columns.Count; Idx++)
{
ws.Range["A1"].Offset[0, Idx].Value = dt.Columns[Idx].ColumnName;
}
这样就创建了 Excel 标题。现在，我将 DataTable 中每一行的数据添加到电子表格中。
for (int Idx = 0; Idx < dt.Rows.Count; Idx++)
{ // <small>hey! I did not invent this line of code,
// I found it somewhere on CodeProject.</small>
// <small>It works to add the whole row at once, pretty cool huh?</small>
ws.Range["A2"].Offset[Idx].Resize[1, dt.Columns.Count].Value = dt.Rows[Idx].ItemArray;
}
现在标题和数据行已加载。有一个 Excel 2007 函数可以将工作表格式化为表格。要使用它，您需要定义一个 Excel 范围并调用该函数来创建表格。格式化表格的代码如下：
string EndColumn = GetColumnName(dt.Columns.Count);
Microsoft.Office.Interop.Excel.Range r = ws.Range["A1:" + EndColumn
+ (dt.Rows.Count + 1).ToString()];
r.Select();
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, r, null
, XlYesNoGuess.xlYes).Name = "Table1";
结果是一个格式精美的表格，带有彩色线条，并且可以按列数据进行排序或筛选。
收尾工作
列格式化
此版本的程序将允许将命名列格式化为文本或货币，而不是使用 Excel 的默认单元格格式。例如，此示例返回一个 PostalCode 列，其中包含数字和字母。默认的 Excel 显示效果不好
将 DataTable 列名（在此例中为 'PostalCode'）添加到“String Columns”字段会改变该列在 Excel 中的显示方式。
具有匹配名称的 Excel 列被重新格式化为“text”格式，从而获得更好的显示效果。
实现此目的所使用的技术是检查 DataTable 中的每一列，看其列名是否在用户界面中。如果匹配，则将“Text”格式应用于该列。奇怪的是，“Text”格式实际上是数字格式选项。
for (int Idx = 0; Idx < dt.Columns.Count; Idx++)
{
// if this column name is in the list of string request
if (ColumnsToMakeString != null)
{
if (ColumnsToMakeString.Contains(dt.Columns[Idx].ColumnName))
{
string ColumnName = GetColumnName(Idx + 1);
ColumnName += ":" + ColumnName;
ws.Range[ColumnName].NumberFormat = "@";
}
}
if (ColumnsToMakeCurrency != null)
{
// if this column name is in the list of currency request
if (ColumnsToMakeCurrency.Contains(dt.Columns[Idx].ColumnName))
{
string ColumnName = GetColumnName(Idx + 1);
ColumnName += ":" + ColumnName;
ws.Range[ColumnName].NumberFormat = "$#,##0.00";
}
}
}
数字到 Excel 列
上面使用的 GetColumnName() 方法是为了将数字转换为 Excel 的列名而开发的。例如，它将 1 转换为列名“A”，2 转换为“B”，26 转换为“AA”，27 转换为“BB”，依此类推。我通过简单地索引一对数组来完成此操作。
private string GetColumnName(int Column)
{
if (Column < 1)
{
return "A";
}
if (Column > 26 * 27)
{
return "ZZ";
}
char[] Alphabet = new char[] { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H'
, 'I', 'J', 'K' , 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S'
, 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
char[] Leader = new char[] { ' ', 'A', 'B', 'C', 'D', 'E', 'F', 'G'
, 'H', 'I', 'J', 'K' , 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
, 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
int IdxLeader = (Column - 1) / 26;
string results = Leader[IdxLeader].ToString()
+ Alphabet[(Column - IdxLeader * 26) - 1].ToString();
return results.Trim();
}
注意：DataTable 和 C# 的工作是相对于零的，而 Excel 的工作是相对于 1 的，因此调用 GetColumnName() 时使用了“Idx+1”。
Excel 列宽
我还担心数据字段过宽的问题。在我的一些生产表中，数据宽度可能高达 2000 个字符。我想限制 Excel 中列的宽度。另一方面，我也不希望宽列中有空白。代码处理每一列，首先设置“AutoFit”以使宽度适合所有数据，然后将任何列的最大宽度设置为 80 个点。结果是列足够宽以容纳数据，并在 80 个点处换行。我不确定这个度量单位是什么，但我知道它不是像素。
for (int Idx = 0; Idx < dt.Columns.Count; Idx++)
{
string ColumnName = GetColumnName(Idx + 1);
string RangeName = ColumnName + ":" + ColumnName; // results something like "B:B"
ws.Range[RangeName].EntireColumn.AutoFit();
if (ws.Range[RangeName].ColumnWidth > 80)
{
ws.Range[RangeName].ColumnWidth = 80;
}
}
多个工作表
我想为来自多个表或数据库的数据在同一个 Excel 工作簿中创建多个工作表。为了实现这一点，我只打开一次 Excel，并为每个 SQL 命令重复使用该 Excel。当我们在这些 SQL 命令之间关闭 Excel 时，这会带来问题。如果 Excel 关闭并且发出另一个 SQL 命令，那么 _ExcelApp 中指向已关闭的 Excel 的指针就会失效。为了解决这个问题，我在 AddWorksheetToWorkBook() 上捕获了一个异常。如果失败，则假定 Excel 已关闭并打开一个新的 Excel 实例。这不是一个很好的解决方案，但目前是我能做到的最好的。
实现这一目标的代码位于按钮的点击事件处理程序中。
if (_ExcelApp == null)
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_wb = _ExcelApp.Workbooks.Add();
}
// Add a worksheet
Worksheet ws;
try
{
ws = AddWorksheetToWorkbook(_wb, txtWorksheetName.Text);
}
catch (Exception ex)
{ // The main reason the AddWorksheet fails is because the user closed
// Excel between sheet creation
// so, create a new Excel and workbook
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_wb = _ExcelApp.Workbooks.Add();
ws = AddWorksheetToWorkbook(_wb, txtWorksheetName.Text);
}
结论
这段代码还不够完美。错误处理和恢复可以做得更好一些，并且目前它无法处理存储过程的参数。但它是一种准备 Excel 文档以支持临时报告和信息的快速方法。
历史
-
2011年1月6日：初始发布