14.10.2024 06:03
alexaae9 4 3200 Источник

Экспорт данных из базы данных в Excel - важнейшая задача для многих организаций, позволяющая пользователям легко анализировать, визуализировать и обмениваться информацией. Этот процесс обеспечивает беспрепятственный перенос данных, сохраняя целостность и структуру исходного набора данных и предоставляя удобный для пользователя формат.

В этой статье я расскажу о пяти уникальных подходах к экспорту данных из базы данных MySQL в файл Excel, используя различные библиотеки C# Excel. Я также приведу краткое сравнение этих библиотек, подчеркнув их особенности, производительность, цену и другие важные моменты.

  • Шаги по экспорту данных из базы данных в Excel с помощью библиотеки C#

  • Экспорт данных в Excel с помощью Microsoft.Office.Interop.Excel

  • Экспорт данных в Excel с помощью EPPlus

  • Экспорт данных в Excel с помощью NPOI

  • Экспорт данных в Excel с помощью ClosedXML

  • Экспорт данных в Excel с помощью Free Spire.XLS for .NET

  • Сравнение библиотек C# Excel

Шаги по экспорту данных из базы данных в Excel с помощью библиотеки C#

  1. Создайте проект на C# и импортируйте необходимые библиотеки.

  2. Установите соединение с базой данных.

  3. Выполните SQL-запрос для получения экспортируемых данных и сохраните их в DataTable или аналогичной структуре данных.

  4. С помощью библиотеки Excel создайте новую рабочую книгу и добавьте рабочий лист.

  5. Вставьте данные из DataTable в соответствующие ячейки рабочего листа.

  6. Сохраните рабочую книгу по указанному пути к файлу.

Экспорт данных в Excel с помощью Microsoft.Office Interop.Excel

Microsoft.Office.Interop.Excel - это проприетарная библиотека, предоставляемая компанией Microsoft в составе пакета Microsoft Office. Она позволяет разработчикам автоматизировать и взаимодействовать с Excel, но требует установки Microsoft Excel на машине, где выполняется код.

Сама библиотека Microsoft.Office.Interop.Excel бесплатна, так как поставляется вместе с Microsoft Office. Однако для его использования на машине должна быть установлена лицензионная копия Microsoft Office. Кроме того, убедитесь, что версия библиотеки Office Interop соответствует версии установленного Office, поскольку несоответствие может привести к ошибкам типа «System.IO.FileNotFoundException: Не удалось загрузить файл или сборку».

  1. Установите необходимые библиотеки:

Install-Package MySql.Data
Install-Package Microsoft.Office.Interop.Excel

  1. Напишите код: 

using System.Data;
using MySql.Data.MySqlClient;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExportDataToExcelUsingInterop
{
    class Program
    {
        static void Main(string[] args)
        {
            // Строка подключения к базе данных MySQL
            string connectionString = "Server=localhost;Database=exceldata;User ID=root;Password=admin;";

            // SQL-запрос для получения данных
            string query = "SELECT * FROM excel_table";

            // Создайте соединение с базой данных MySQL
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataTable dataTable = new DataTable();

                // Заполните DataTable данными из базы данных
                adapter.Fill(dataTable);

                // Создайте экземпляр Excel
                Excel.Application excelApp = new Excel.Application();
                excelApp.Visible = false;

                // Создайте новую книгу
                Excel.Workbook workbook = excelApp.Workbooks.Add();
                Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];

                // Добавьте заголовки столбцов в рабочий лист
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
                }

                // Добавьте данные в рабочий лист
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = dataTable.Rows[i][j];
                    }
                }

                // Сохраните файл Excel
                workbook.SaveAs("ExportedData.xlsx");
                workbook.Close();
                excelApp.Quit();

                // Освободите объекты COM
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }
        }
    }
}

Экспорт данных в Excel с помощью EPPlus

EPPlus - это библиотека Excel для C# .NET Core и Framework с открытым исходным кодом. Она основана на формате OpenXML и использует пространство имен OfficeOpenXml. Она предоставляет простой и удобный API для создания, чтения и работы с файлами Excel в формате .xlsx, не требуя установки Microsoft Excel.

Примечание: Начиная с версии 5, EPPlus перешел на модель двойного лицензирования. Она допускает бесплатное использование в проектах с открытым исходным кодом, но требует коммерческой лицензии для использования в коммерческой деятельности.

  1. Установите необходимые библиотеки:

Install-Package EPPlus
Install-Package MySql.Data

  1. Напишите код:

using System;
using System.Data;
using MySql.Data.MySqlClient;
using OfficeOpenXml;

namespace ExportDataToExcelUsingEPPlus
{
    class Program
    {
        static void Main(string[] args)
        {
            // Строка подключения к базе данных MySQL
            string connectionString = "Server=localhost;Database=exceldata;User ID=root;Password=admin;";

            // SQL-запрос для получения данных
            string query = "SELECT * FROM excel_table";

            // Создайте соединение с базой данных MySQL
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataTable dataTable = new DataTable();

                // Заполните DataTable результатами запроса
                adapter.Fill(dataTable);

                // Укажите тип лицензии как Некоммерческая
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

                // Создайте новый пакет Excel для работы с файлами Excel
                using (ExcelPackage excelPackage = new ExcelPackage())
                {
                    // Добавьте новый рабочий лист в пакет Excel
                    var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");

                    // Загрузите DataTable в рабочий лист Excel
                    worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);

                    // Сохраните файл Excel
                    excelPackage.SaveAs(new FileInfo("ExportedData.xlsx"));
                }
            }
        }
    }
}

Экспорт данных в Excel с помощью NPOI

NPOI - это библиотека .NET с открытым исходным кодом для чтения и записи документов Microsoft Office, включая файлы Excel, Word и PowerPoint. Библиотека поддерживает широкий спектр функций, включая импорт и экспорт данных, форматирование ячеек, формулы, диаграммы и поворотные таблицы, что делает ее подходящей как для простых, так и для сложных операций с Excel.

  1. Установите необходимые библиотеки:

Install-Package NPOI
Install-Package MySql.Data

  1. Напишите код:

using MySql.Data.MySqlClient;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;

namespace ExportDataToExcelUsingNPOI
{
    class Program
    {
        static void Main(string[] args)
        {
            // Строка подключения к базе данных MySQL
            string connectionString = "Server=localhost;Database=exceldata;User ID=root;Password=admin;";

            // SQL-запрос для получения данных
            string query = "SELECT * FROM excel_table";

            // Создайте соединение с базой данных MySQL
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open(); 
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataTable dataTable = new DataTable(); 

                // Заполните DataTable данными из базы данных
                adapter.Fill(dataTable);

                // Создайте новую книгу Excel
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Sheet1");

                // Создайте строку заголовка
                IRow headerRow = sheet.CreateRow(0);
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    // Установите имена столбцов
                    headerRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName); 
                }

                // Заполните лист данными из DataTable
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    // Создайте новую строку
                    IRow row = sheet.CreateRow(i + 1); 
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        // Установите значение ячейки
                        row.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString()); 
                    }
                }

                // Сохраните файл Excel
                using (var fileData = new FileStream("ExportedData.xlsx", FileMode.Create))
                {
                    // Запишите книгу в файл
                    workbook.Write(fileData); 
                }
            }
        }
    }
}

Экспорт данных в Excel с помощью ClosedXML

ClosedXML - это библиотека .NET, которая упрощает создание и работу с файлами Excel в формате OpenXML (.xlsx). Это библиотека с открытым исходным кодом и свободна для использования в соответствии с лицензией MIT. Это означает, что вы можете использовать ее как в личных, так и в коммерческих проектах без каких-либо лицензионных отчислений или ограничений.

  1. Установите необходимые библиотеки:

Install-Package ClosedXML
Install-Package MySql.Data

  1. Напишите код:

using System;
using System.Data;
using MySql.Data.MySqlClient;
using ClosedXML.Excel;

namespace ExportDataToExcelUsingClosedXML
{
    class Program
    {
        static void Main(string[] args)
        {
            // Строка подключения к базе данных MySQL
            string connectionString = "Server=localhost;Database=exceldata;User ID=root;Password=admin;";

            // SQL-запрос для получения данных
            string query = "SELECT * FROM excel_table";

            // Создайте соединение с базой данных MySQL
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataTable dataTable = new DataTable();

                // Заполните DataTable данными из базы данных
                adapter.Fill(dataTable);

                // Создайте новую книгу Excel
                using (var workbook = new XLWorkbook())
                {
                    // Добавьте новый рабочий лист в книгу
                    var worksheet = workbook.Worksheets.Add("Sheet1");

                    // Вставьте DataTable в рабочий лист, начиная с ячейки A1
                    worksheet.Cell(1, 1).InsertTable(dataTable, false);

                    // Сохраните файл Excel
                    workbook.SaveAs("ExportedData.xlsx");
                }
            }
        }
    }
}

Экспорт данных в Excel с помощью Free Spire.XLS for .NET

Free Spire.XLS for .NET - это универсальная библиотека, позволяющая разработчикам создавать, читать и изменять файлы Excel в форматах .xlsx и .xls. Благодаря простому API она поддерживает такие функции, как форматирование данных, вычисление формул и создание диаграмм.

Эта библиотека идеально подходит для автоматизации операций с Excel в приложениях .NET без необходимости использования Microsoft Office, предлагая бесплатную версию с необходимыми возможностями для различных задач разработки.

  1. Установите необходимые библиотеки:

Install-Package FreeSpire.XLS
Install-Package MySql.Data

  1. Напишите код:

using Spire.Xls;
using System.Data;
using MySql.Data.MySqlClient;

namespace ExportDataToExcelUsingSpireXLS
{
    class Program
    {
        static void Main(string[] args)
        {
            // Строка подключения к базе данных MySQL
            string connectionString = "Server=localhost;Database=exceldata;User ID=root;Password=admin;";

            // SQL-запрос для получения данных
            string query = "SELECT * FROM excel_table";

            // Создайте новую книгу Excel
            Workbook workbook = new Workbook();

            // Получите первый рабочий лист
            Worksheet worksheet = workbook.Worksheets[0];

            // Создайте соединение с базой данных MySQL
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataTable dataTable = new DataTable();

                // Заполните DataTable результатами запроса
                adapter.Fill(dataTable);

                // Вставьте DataTable в рабочий лист
                worksheet.InsertDataTable(dataTable, true, 1, 1);
            }

            // Сохраните книгу в файл
            workbook.SaveToFile("ImportedData.xlsx", ExcelVersion.Version2010);
        }
    }
}

Сравнение библиотек C# Excel

Эта статья посвящена базовой функциональности библиотеки C# Excel, а именно записи данных на рабочий лист. В ней не рассматриваются более продвинутые возможности, такие как применение условного форматирования, фильтрация данных или создание поворотных таблиц для анализа данных. Я широко использовал эти библиотеки и привожу следующие сравнения, основанные на моем опыте.

Вот сравнение Microsoft.Office.Interop.Excel, EPPlus, NPOI, ClosedXML и Free Spire.XLS для .NET по цене, производительности, простоте использования, возможностям и наилучшим вариантам применения:

Заключение

В этой статье блога рассмотрены пять различных методов экспорта данных из базы данных в Excel с помощью различных библиотек C# Excel. У каждой библиотеки есть свои сильные стороны и возможности. Вы можете выбрать ту, которая соответствует конкретным требованиям вашего проекта.

Комментарии (4)


  1. gyzl
    14.10.2024 10:12
    #27413770

    Подскажите, какая из них может писать генерируемый Excel файл в однонаправленный write-only поток, который, например, предоставляет ASP.NET в Request.Body? Ну то есть чтобы можно было начать отдавать файл в процессе генерации.


    1. vabka
      14.10.2024 10:12
      #27414446

      Из-за того как устроен сам формат xlsx - такие вряд-ли существуют.

      Возможно вам стоит посмотреть в сторону других форматов


  1. VasiliyLiGHT
    14.10.2024 10:12
    #27415454

    О, таких статей я прочитал достаточно, об этом пишут многие)) Но практически никто не заходит дальше. Например, как работать с OLE объектами? В случае с Interop.Excel всё просто (но для работы нужен офисный пакет на машине):

    Application excel = new() { Visible = false };
Workbook book = excel.Workbooks.Open(fileName);
Worksheet sheet = book.Worksheets[1];
OLEObjects objects = sheet.OLEObjects();
objects.Delete();
book.SaveAs(fileName2);
book.Close();
excel.Quit();
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(excel);

    В NPOI (она же Apache POI) вроде что-то есть, но документация какая-то дурацкая. В остальных библиотеках нагуглить не получилось.

    Пользуясь случаем, задам вопрос: как в NPOI корректно удалить "встроенные объекты"? Например, юзеры вставляют pdfки в файлы экселя, и перед копированием на сервер эти файлы нужно почистить. Набросал код:

    using FileStream file = new(fileName, FileMode.Open, FileAccess.Read);
POIFSFileSystem fs = new(file);
DirectoryNode root = fs.Root;
var mbds = root.Where(r => r.Name.StartsWith("MBD")).ToList();
List<DocumentEntry> docs = [];
foreach (var mbd in mbds.Cast<DirectoryEntry>())
{
	var entries = mbd.Entries;
	while (entries.MoveNext())
	{
		var entry = (DocumentEntry)entries.Current;
		docs.Add(entry);
	}
}
docs.ForEach(doc => doc.Delete());
mbds.ForEach(mbd => mbd.Delete());
using FileStream _out = new(fileName2, FileMode.Create, FileAccess.Write);
fs.WriteFileSystem(_out);
fs.Close();

    При вызове метода Delete() изменения в root происходят, но в конечном файле ничего не меняется. Как всё-таки нужно фиксировать эти изменения?

    Документация: https://poi.apache.org/components/poifs/how-to.html


  1. ALexKud
    14.10.2024 10:12
    #27416708

    Самый простой способ импорта из БД в Excell это в самом Excell использовать вкладку Данные, создать подключение к БД и его настроить, создав SQL запрос. Без всяких языков прораммирования, только через SQL. А в общем я использую Excell в своих системах через OLE Automation вместо генератора отчетов. Это в общем несложно (если знаешь как рулить командами Excell ) и дает большую свободу для работы с шаблонами отчетов, к тому же отчет получается редактируемым и пользователи могут сохранять его куда им надо.