将SQL查询数据导出到Excel

Question

我有一个返回非常大的数据集的查询。我无法像平常那样将其复制并粘贴到 Excel 中。我一直在研究如何直接导出到 Excel 工作表。我正在运行 Microsoft Server 2003 的服务器上运行 SQL SERVER 2008。我正在尝试使用 Microsoft.Jet.OLEDB.4.0 数据提供程序和 Excel 2007。我拼凑了一小段代码，看起来像这样已经在例子中看到过。

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Working\Book1.xlsx;Extended Properties=EXCEL 12.0;HDR=YES')
SELECT productid, price FROM dbo.product

然而这不起作用，我收到一条错误消息说

“关键字“SELECT”附近的语法不正确”。

有谁对如何做到这一点或可能有更好的方法有任何想法吗？

Answer 1

我不知道这是否是您要查找的内容，但您可以将结果导出到 Excel，如下所示：

在结果窗格中，单击左上角的单元格以突出显示所有记录，然后右键单击顶部- 左侧单元格并单击“结果另存为”。导出选项之一是 CSV。

您也可以尝试一下：

INSERT INTO OPENROWSET 
   ('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\Test.xls;','SELECT productid, price FROM dbo.product')

最后，您可以考虑使用 SSIS（取代 DTS）进行数据导出。以下是教程的链接：

http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm

== 更新 #1 ==

要将结果保存为带有列标题的 CSV 文件，可以按照以下步骤操作：

1. 转到工具->选项
2. 查询结果->SQL Server->结果到网格
3. 选中“复制或保存结果时包括列标题”
4. 单击确定。
5. 请注意，新设置不会影响任何现有的“查询”选项卡 - 您需要打开新选项卡和/或重新启动 SSMS。

Answer 2

如果您只需要导出到 Excel，则可以使用导出数据向导。
右键单击数据库，任务->导出数据。

Answer 3

我遇到了类似的问题，但有一点不同 - 当结果集来自一个查询时，上面列出的解决方案有效，但在我的情况下，我有多个单独的选择查询，我需要将结果导出到 Excel。下面只是一个示例来说明，尽管我可以执行 name in 子句...

select a,b from Table_A where name = 'x'
select a,b from Table_A where name = 'y'
select a,b from Table_A where name = 'z'

向导允许我将一个查询的结果导出到 Excel，但在本例中并非不同查询的所有结果。

当我研究时，我发现我们可以禁用结果网格并启用结果文本。因此，按 Ctrl + T，然后执行所有语句。这应该在输出窗口中将结果显示为文本文件。您可以将文本处理为制表符分隔格式，以便导入到 Excel 中。

您还可以按 Ctrl + Shift + F 将结果导出到文件 - 它导出为 .rpt 文件，可以使用文本编辑器打开该文件并进行操作以进行 Excel 导入。

希望这对其他有类似问题的人有所帮助。

Answer 4

对于来这里寻找如何在 C# 中执行此操作的人，我已经尝试了以下方法，并在 dotnet core 2.0.3 和 entity Framework core 2.0 中取得了成功.3

首先创建您的模型类。

public class User
{  
    public string Name { get; set; }  
    public int Address { get; set; }  
    public int ZIP { get; set; }  
    public string Gender { get; set; }  
} 

然后安装 EPPlus Nuget 包。 （我使用的版本是 4.0.5，可能也适用于其他版本。）

Install-Package EPPlus -Version 4.0.5

创建 ExcelExportHelper 类，它将包含将数据集转换为 Excel 行的逻辑。该类与您的模型类或数据集没有依赖关系。

public class ExcelExportHelper
    {
        public static string ExcelContentType
        {
            get
            { return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; }
        }

        public static DataTable ListToDataTable<T>(List<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable dataTable = new DataTable();

            for (int i = 0; i < properties.Count; i++)
            {
                PropertyDescriptor property = properties[i];
                dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
            }

            object[] values = new object[properties.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = properties[i].GetValue(item);
                }

                dataTable.Rows.Add(values);
            }
            return dataTable;
        }

        public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
        {

            byte[] result = null;
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(String.Format("{0} Data", heading));
                int startRowFrom = String.IsNullOrEmpty(heading) ? 1 : 3;

                if (showSrNo)
                {
                    DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
                    dataColumn.SetOrdinal(0);
                    int index = 1;
                    foreach (DataRow item in dataTable.Rows)
                    {
                        item[0] = index;
                        index++;
                    }
                }


                // add the content into the Excel file  
                workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);

                // autofit width of cells with small content  
                int columnIndex = 1;
                foreach (DataColumn column in dataTable.Columns)
                {
                    int maxLength;
                    ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
                    try
                    {
                        maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
                    }
                    catch (Exception) //nishanc
                    {
                        maxLength = columnCells.Max(cell => (cell.Value +"").ToString().Length);
                    }

                    //workSheet.Column(columnIndex).AutoFit();
                    if (maxLength < 150)
                    {
                        //workSheet.Column(columnIndex).AutoFit();
                    }


                    columnIndex++;
                }

                // format header - bold, yellow on black  
                using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
                {
                    r.Style.Font.Color.SetColor(System.Drawing.Color.White);
                    r.Style.Font.Bold = true;
                    r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    r.Style.Fill.BackgroundColor.SetColor(Color.Brown);
                }

                // format cells - add borders  
                using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
                {
                    r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Right.Style = ExcelBorderStyle.Thin;

                    r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                    r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                    r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
                    r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                }

                // removed ignored columns  
                for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
                {
                    if (i == 0 && showSrNo)
                    {
                        continue;
                    }
                    if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName))
                    {
                        workSheet.DeleteColumn(i + 1);
                    }
                }

                if (!String.IsNullOrEmpty(heading))
                {
                    workSheet.Cells["A1"].Value = heading;
                   // workSheet.Cells["A1"].Style.Font.Size = 20;

                    workSheet.InsertColumn(1, 1);
                    workSheet.InsertRow(1, 1);
                    workSheet.Column(1).Width = 10;
                }

                result = package.GetAsByteArray();
            }

            return result;
        }

        public static byte[] ExportExcel<T>(List<T> data, string Heading = "", bool showSlno = false, params string[] ColumnsToTake)
        {
            return ExportExcel(ListToDataTable<T>(data), Heading, showSlno, ColumnsToTake);
        }
    }

现在在您想要生成 Excel 文件的位置添加此方法，可能是控制器中的方法。您也可以为存储过程传递参数。 请注意，该方法的返回类型是FileContentResult。无论您执行什么查询，重要的是您必须在列表中获得结果。

[HttpPost]
public async Task<FileContentResult> Create([Bind("Id,StartDate,EndDate")] GetReport getReport)
{
    DateTime startDate = getReport.StartDate;
    DateTime endDate = getReport.EndDate;

    // call the stored procedure and store dataset in a List.
    List<User> users = _context.Reports.FromSql("exec dbo.SP_GetEmpReport @start={0}, @end={1}", startDate, endDate).ToList();
    //set custome column names
    string[] columns = { "Name", "Address", "ZIP", "Gender"};
    byte[] filecontent = ExcelExportHelper.ExportExcel(users, "Users", true, columns);
    // set file name.
    return File(filecontent, ExcelExportHelper.ExcelContentType, "Report.xlsx"); 
}

更多详细信息可以在此处

Answer 5

我发现您正在尝试将 SQL 数据导出到 Excel 以避免将非常大的数据集复制粘贴到 Excel 中。

您可能有兴趣了解如何将 SQL 数据导出到 Excel 并自动更新导出（使用任何 SQL 数据库：MySQL、Microsoft SQL Server、PostgreSQL）。

要将数据从 SQL 导出到 Excel，您需要执行以下 2 个步骤：

• 步骤 1：将 Excel 连接到您的 SQL 数据库‍（Microsoft SQL Server、MySQL、PostgreSQL...）
• 步骤 2：将 SQL 数据导入 Excel

结果将是您想要将 SQL 数据库中的数据查询到 Excel 的表列表：



步骤 1：将 Excel 连接到外部数据源：您的 SQL 数据库

1. 安装 ODBC
2. 安装驱动程序
3. 避免常见错误
4. 创建 DSN

步骤 2：将 SQL 数据导入到Excel

1. 单击您想要的位置数据透视表
2. 单击“插入”
3. 单击“数据透视表”
4. 单击“使用外部数据源”，然后选择“连接”
5. 单击“系统 DSN”选项卡
6. 选择在 ODBC 管理器中创建的 DSN
7. 填写请求的用户名和密码
8. 避免常见错误
9. 访问 Microsoft 查询对话框
10. 单击箭头查看数据库中的表列表
11. 选择要从 SQL 数据库查询数据到 Excel 的表
12. 完成选择后单击“返回数据”

要自动更新导出，还有 2 个附加步骤：

1. 创建数据透视表具有外部 SQL 数据的表源
2. 使用 GETPIVOTDATA 函数在 Excel 中自动更新 SQL 数据

我创建了一个分步教程 关于整个过程，从连接 Excel 到 SQL，直到整个过程自动更新。您可能会找到详细说明和屏幕截图有用。