Библиотеки для экспорта данных в Excel

В этой статье мы рассмотрим способы для выгрузки данных в xls/xlsx файл. Для примера был создан небольшой тестовый проект для демонстрации возможностей библиотек. Допустим у нас есть класс студент

public class Student
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public bool Gender { get; set; }
        public string phone { get; set; }
        public int Eng { get; set; }
        public int Math { get; set; }
    }

public class Student

{

public int ID { get; set; }

public string Name { get; set; }

public int Age { get; set; }

public bool Gender { get; set; }

public string phone { get; set; }

public int Eng { get; set; }

public int Math { get; set; }

}

Создадим функцию которая будет создавать фейковые данные студентов

        /// <summary>
        /// Generate Random Student Data
        /// </summary>
        /// <param name="count">count</param>
        /// <returns>List<Student></returns>
        private List<Student> GenerateRandomData(int count)
        {
            Random rnd = new Random();
            List<Student> students = new List<Student>();
            for (int i = 0; i < count; i++)
            {
                students.Add(new Student()
                {
                    Name = "name" + i.ToString(),
                    Age = rnd.Next(10, 60),
                    Gender = rnd.NextDouble() >= 0.5,
                    ID = i,
                    phone = "8987654321",
                    Eng = rnd.Next(0, 100),
                    Math = rnd.Next(0, 100)
                });
            }
            return students;
        }

/// <summary>

/// Generate Random Student Data

/// </summary>

/// <param name="count">count</param>

/// <returns>List<Student></returns>

private List<Student> GenerateRandomData(int count)

{

Random rnd = new Random();

List<Student> students = new List<Student>();

for (int i = 0; i < count; i++)

{

students.Add(new Student()

{

Name = "name" + i.ToString(),

Age = rnd.Next(10, 60),

Gender = rnd.NextDouble() >= 0.5,

ID = i,

phone = "8987654321",

Eng = rnd.Next(0, 100),

Math = rnd.Next(0, 100)

});

}

return students;

}

Теперь рассмотрим способы выгрузить эти данные в excel.

Содержание

NPOI
Общие характеристики NPOI:
Как использовать
EPPlus
Общие характеристики
Как использовать
ClosedXML
Как использовать
ClosedXML.Report
‎Функции‎
Как использовать
OleDB
Провайдеры данных
Extended Properties
Как использовать

NPOI

Этот проект является .NET версией проекта POI Java. С помощью NPOI вы можете легко читать/записывать файлы Office 2003/2007.

Общие характеристики NPOI:

a. Это абсолютно бесплатно для использования

b. Охват большинства функций Excel (стиль ячеек, формат данных, формула и т. д.)

c. Поддерживаемые форматы: xls, xlsx, docx.

d. Разработан для ориентации на интерфейс (взгляните на NPOI. Пространство имен SS)

e. Поддержка не только экспорта, но и импорта

f. Реальные успешные кейсы по всему миру

г. огромное количество базовых примеров

h. Работает как на Windows, так и на Linux

Подробно узнать о проекте можно по ссылке

Ниже указан способ выполнить экспорт с помощью NPOI

Как использовать

Установим библиотеку через NuGet

Подключим в код

using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;

 List<Student> students = GenerateRandomData(100);
            
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Primer_NPOI");

//create the header row
IRow headerRow = sheet.CreateRow(0);
int count = 0;
foreach (var item in typeof(Student).GetProperties())
{
   headerRow.CreateCell(count++).SetCellValue(item.Name);
}
            
//set the value 
for (int i = 1; i < students.Count() + 1; i++)
{
   IRow row = sheet.CreateRow(i);
   count = 0;
   row.CreateCell(count++).SetCellValue(students[i - 1].ID);
   row.CreateCell(count++).SetCellValue(students[i - 1].Name);
   row.CreateCell(count++).SetCellValue(students[i - 1].Age);
   row.CreateCell(count++).SetCellValue(students[i - 1].Gender ? "М" : "Ж");
   row.CreateCell(count++).SetCellValue(students[i - 1].phone);
   row.CreateCell(count++).SetCellValue(students[i - 1].Eng);
   row.CreateCell(count).SetCellValue(students[i - 1].Math);
}

try
{
    //save file
    SaveFileDialog saveDialog = new SaveFileDialog()
    {
       DefaultExt = ".xlsx",
       FileName = "Primer_NPOI",
       Filter = "Excel |*.xlsx"
    };
    if (saveDialog.ShowDialog() == true)
    {
       using (FileStream MS = new FileStream(saveDialog.FileName,FileMode.Create))
       {
          workbook.Write(MS);
       }
      }
    }
catch (Exception ex)
{
   StatusText.Text = ex.Message;
}

List<Student> students = GenerateRandomData(100);

XSSFWorkbook workbook = new XSSFWorkbook();

ISheet sheet = workbook.CreateSheet("Primer_NPOI");

//create the header row

IRow headerRow = sheet.CreateRow(0);

int count = 0;

foreach (var item in typeof(Student).GetProperties())

{

headerRow.CreateCell(count++).SetCellValue(item.Name);

}

//set the value

for (int i = 1; i < students.Count() + 1; i++)

{

IRow row = sheet.CreateRow(i);

count = 0;

row.CreateCell(count++).SetCellValue(students[i - 1].ID);

row.CreateCell(count++).SetCellValue(students[i - 1].Name);

row.CreateCell(count++).SetCellValue(students[i - 1].Age);

row.CreateCell(count++).SetCellValue(students[i - 1].Gender ? "М" : "Ж");

row.CreateCell(count++).SetCellValue(students[i - 1].phone);

row.CreateCell(count++).SetCellValue(students[i - 1].Eng);

row.CreateCell(count).SetCellValue(students[i - 1].Math);

}

try

{

//save file

SaveFileDialog saveDialog = new SaveFileDialog()

{

DefaultExt = ".xlsx",

FileName = "Primer_NPOI",

Filter = "Excel |*.xlsx"

};

if (saveDialog.ShowDialog() == true)

{

using (FileStream MS = new FileStream(saveDialog.FileName,FileMode.Create))

{

workbook.Write(MS);

}

catch (Exception ex)

{

StatusText.Text = ex.Message;

}

EPPlus

EPPlus использует OpenOffice для чтения и запись в формате (xlsx) Excel

Официальный веб-сайт:http://epplus.codeplex.com/

Общие характеристики

Создание, чтение и изменение книг Office Open XML (xslx и xlsm)

Поддерживает как .NET Framework, так и .NET Core. Нет различий в наборе функций между фреймворками.

Предоставляет простой в использовании, строго типизированный программный интерфейс, который тесно связан с хорошо известным API Excel VBA.

Только управляемый код, без зависимостей от Microsoft Excel

Закаленная в боях кодовая база после многих лет работы в качестве самой скачиваемой в мире библиотеки .NET для управления электронными таблицами. Качество, обеспечиваемое тысячами юнит-тестов.

Исходный код EPPlus в открытом доступе на Github , распространяется под некоммерческой лицензией Polyform

Как использовать

Установка

List<Student> students = GenerateRandomData(100);

using (ExcelPackage p = new ExcelPackage())
{
ExcelWorksheet sheet = p.Workbook.Worksheets.Add("EPPlus_Test");

//create the header row
int count = 1;
foreach (var item in typeof(Student).GetProperties())
{
    sheet.Cells[1, count].Style.Fill.PatternType = ExcelFillStyle.Solid;
    sheet.Cells[1, count].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
    sheet.Cells[1, count++].Value = item.Name;
}

//set the value 
for (int i = 2; i < students.Count() + 2; i++)
{
    count = 1;
    sheet.Cells[i, count++].Value = students[i - 2].ID;
    sheet.Cells[i, count++].Value = students[i - 2].Name;
    sheet.Cells[i, count++].Value = students[i - 2].Age;
    sheet.Cells[i, count++].Value = students[i - 2].Gender ? "М" : "Ж";
    sheet.Cells[i, count++].Value = students[i - 2].phone;
    sheet.Cells[i, count++].Value = students[i - 2].Eng;
    sheet.Cells[i, count].Value = students[i - 2].Math;
    sheet.Cells[i, count].Style.Fill.PatternType = ExcelFillStyle.Solid;
    sheet.Cells[i, count].Style.Fill.BackgroundColor.SetColor(students[i - 2].Math < 60 ? Color.Red : Color.Transparent);
}
sheet.Column(4).AutoFit();

//condition format
var ruleIcon = sheet.ConditionalFormatting.AddThreeIconSet(new ExcelAddress(2, 5, 10, 5), eExcelconditionalFormatting3IconsSetType.Signs);
var colorformat = sheet.ConditionalFormatting.AddDatabar(new ExcelAddress(2, 6, 10, 6), Color.Blue);

//add chart 
ExcelWorksheet sheet2 = p.Workbook.Worksheets.Add("chart");
ExcelChart chart = sheet2.Drawings.AddChart("NewChart", eChartType.Area3D);
chart.Title.Text = "Title";
chart.Series.Add(sheet.Cells[2, 6, 6, 6], sheet.Cells[2, 1, 6, 1]);
chart.SetPosition(4, 1, 1, 0);
chart.SetSize(800, 400);
chart.View3D.DepthPercent = 60;
chart.View3D.Perspective = 15;
chart.View3D.RotX = 20;
chart.View3D.RotY = 15;

try
{
    //save file
    SaveFileDialog saveDialog = new SaveFileDialog()
    {
        DefaultExt = ".xlsx",
        FileName = "EPPlus_Test",
        Filter = "Excel |*.xlsx"
    };
    if (saveDialog.ShowDialog() == true)
    {
        File.WriteAllBytes(saveDialog.FileName, p.GetAsByteArray());
        //p.SaveAs(new FileInfo(saveDialog.FileName));
    }
}
catch (Exception ex)
{
    StatusText.Text = ex.Message;
}
}
}

List<Student> students = GenerateRandomData(100);

using (ExcelPackage p = new ExcelPackage())

{

ExcelWorksheet sheet = p.Workbook.Worksheets.Add("EPPlus_Test");

//create the header row

int count = 1;

foreach (var item in typeof(Student).GetProperties())

{

sheet.Cells[1, count].Style.Fill.PatternType = ExcelFillStyle.Solid;

sheet.Cells[1, count].Style.Fill.BackgroundColor.SetColor(Color.Yellow);

sheet.Cells[1, count++].Value = item.Name;

}

//set the value

for (int i = 2; i < students.Count() + 2; i++)

{

count = 1;

sheet.Cells[i, count++].Value = students[i - 2].ID;

sheet.Cells[i, count++].Value = students[i - 2].Name;

sheet.Cells[i, count++].Value = students[i - 2].Age;

sheet.Cells[i, count++].Value = students[i - 2].Gender ? "М" : "Ж";

sheet.Cells[i, count++].Value = students[i - 2].phone;

sheet.Cells[i, count++].Value = students[i - 2].Eng;

sheet.Cells[i, count].Value = students[i - 2].Math;

sheet.Cells[i, count].Style.Fill.PatternType = ExcelFillStyle.Solid;

sheet.Cells[i, count].Style.Fill.BackgroundColor.SetColor(students[i - 2].Math < 60 ? Color.Red : Color.Transparent);

}

sheet.Column(4).AutoFit();

//condition format

var ruleIcon = sheet.ConditionalFormatting.AddThreeIconSet(new ExcelAddress(2, 5, 10, 5), eExcelconditionalFormatting3IconsSetType.Signs);

var colorformat = sheet.ConditionalFormatting.AddDatabar(new ExcelAddress(2, 6, 10, 6), Color.Blue);

//add chart

ExcelWorksheet sheet2 = p.Workbook.Worksheets.Add("chart");

ExcelChart chart = sheet2.Drawings.AddChart("NewChart", eChartType.Area3D);

chart.Title.Text = "Title";

chart.Series.Add(sheet.Cells[2, 6, 6, 6], sheet.Cells[2, 1, 6, 1]);

chart.SetPosition(4, 1, 1, 0);

chart.SetSize(800, 400);

chart.View3D.DepthPercent = 60;

chart.View3D.Perspective = 15;

chart.View3D.RotX = 20;

chart.View3D.RotY = 15;

try

{

//save file

SaveFileDialog saveDialog = new SaveFileDialog()

{

DefaultExt = ".xlsx",

FileName = "EPPlus_Test",

Filter = "Excel |*.xlsx"

};

if (saveDialog.ShowDialog() == true)

{

File.WriteAllBytes(saveDialog.FileName, p.GetAsByteArray());

//p.SaveAs(new FileInfo(saveDialog.FileName));

}

catch (Exception ex)

{

StatusText.Text = ex.Message;

}

ClosedXML

‎ClosedXML — это библиотека .NET для чтения, обработки и записи файлов Excel 2007+ (.xlsx, XLSM). Он направлен на предоставление интуитивно понятного и удобного интерфейса для работы с базовым ‎‎OpenXML‎‎ API.‎

Ссылка на проект

‎Для получения дополнительной информации см. вики‎

Как использовать

List<Student> students = GenerateRandomData(100);
            
XLWorkbook workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("ClosedXML_Test");
int count = 1;
foreach (var item in typeof(Student).GetProperties())
{
    sheet.Cell(1, count).Style.Fill.PatternType = XLFillPatternValues.Solid;
    sheet.Cell(1, count).Style.Fill.BackgroundColor = XLColor.Yellow;
    sheet.Cell(1, count++).Value = item.Name;
}

for (int i = 2; i < students.Count() + 2; i++)
{
    count = 1;
    sheet.Cell(i, count++).Value = students[i - 2].ID;
    sheet.Cell(i, count++).Value = students[i - 2].Name;
    sheet.Cell(i, count++).Value = students[i - 2].Age;
    sheet.Cell(i, count++).Value = students[i - 2].Gender ? "М" : "Ж";
    sheet.Cell(i, count++).Value = students[i - 2].phone;
    sheet.Cell(i, count++).Value = students[i - 2].Eng;
    sheet.Cell(i, count).Value = students[i - 2].Math;
    sheet.Cell(i, count).Style.Fill.PatternType = XLFillPatternValues.None;
    sheet.Cell(i, count).Style.Fill.BackgroundColor = students[i - 2].Math < 60 ? XLColor.Red : XLColor.Transparent;
}
sheet.Column(4).AdjustToContents();

//condition format
sheet.Range(2, 6, 10, 6).AddConditionalFormat().DataBar(XLColor.Orange).LowestValue().HighestValue();
sheet.Range(2, 3, 10, 3).AddConditionalFormat().IconSet(XLIconSetStyle.ThreeTrafficLights2)
    .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 45, XLCFContentType.Number)
    .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 20, XLCFContentType.Number)
    .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 10, XLCFContentType.Number);

try
{
    //save file
    SaveFileDialog saveDialog = new SaveFileDialog()
    {
        DefaultExt = ".xlsx",
        FileName = "ClosedXML",
        Filter = "Excel |*.xlsx"
    };
    if (saveDialog.ShowDialog() == true)
    {
        workbook.SaveAs(saveDialog.FileName);
    }
}
catch (Exception ex)
{
    StatusText.Text = ex.Message;
}

List<Student> students = GenerateRandomData(100);

XLWorkbook workbook = new XLWorkbook();

var sheet = workbook.Worksheets.Add("ClosedXML_Test");

int count = 1;

foreach (var item in typeof(Student).GetProperties())

{

sheet.Cell(1, count).Style.Fill.PatternType = XLFillPatternValues.Solid;

sheet.Cell(1, count).Style.Fill.BackgroundColor = XLColor.Yellow;

sheet.Cell(1, count++).Value = item.Name;

}

for (int i = 2; i < students.Count() + 2; i++)

{

count = 1;

sheet.Cell(i, count++).Value = students[i - 2].ID;

sheet.Cell(i, count++).Value = students[i - 2].Name;

sheet.Cell(i, count++).Value = students[i - 2].Age;

sheet.Cell(i, count++).Value = students[i - 2].Gender ? "М" : "Ж";

sheet.Cell(i, count++).Value = students[i - 2].phone;

sheet.Cell(i, count++).Value = students[i - 2].Eng;

sheet.Cell(i, count).Value = students[i - 2].Math;

sheet.Cell(i, count).Style.Fill.PatternType = XLFillPatternValues.None;

sheet.Cell(i, count).Style.Fill.BackgroundColor = students[i - 2].Math < 60 ? XLColor.Red : XLColor.Transparent;

}

sheet.Column(4).AdjustToContents();

//condition format

sheet.Range(2, 6, 10, 6).AddConditionalFormat().DataBar(XLColor.Orange).LowestValue().HighestValue();

sheet.Range(2, 3, 10, 3).AddConditionalFormat().IconSet(XLIconSetStyle.ThreeTrafficLights2)

.AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 45, XLCFContentType.Number)

.AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 20, XLCFContentType.Number)

.AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 10, XLCFContentType.Number);

try

{

//save file

SaveFileDialog saveDialog = new SaveFileDialog()

{

DefaultExt = ".xlsx",

FileName = "ClosedXML",

Filter = "Excel |*.xlsx"

};

if (saveDialog.ShowDialog() == true)

{

workbook.SaveAs(saveDialog.FileName);

}

catch (Exception ex)

{

StatusText.Text = ex.Message;

}

ClosedXML.Report

‎ClosedXML.Report — это инструмент для создания отчетов и анализа данных в приложениях .NET с помощью Microsoft Excel. Это . NET-библиотека для создания отчетов Microsoft Excel без необходимости установки Excel на компьютере, на котором выполняется код. С помощью ClosedXML.Report можно легко экспортировать любые данные из классов .NET в Excel с помощью XLSX-шаблона.‎

‎Функции‎

‎Копирование форматирования ячеек‎

‎Условное форматирование

‎Вертикальные и горизонтальные таблицы и поддиапазоны‎

‎Возможность реализации формул Excel‎

‎Использование динамически вычисляемых формул с синтаксисом C # и Linq‎

‎Операции с табличными данными: сортировка, группировка, суммарные функции.‎

‎Сводные таблицы‎

‎Поддиапазоны‎

Как использовать

ClosedXML/ClosedXML.Report: ClosedXML.Report is a tool for report generation with which you can easily export any data from your .NET classes to Excel using a XLSX-template. (github.com)

OleDB

Провайдеры данных

Для работы с Excel 2003 (.Xls) можно использовать провайдер Microsoft Jet OLE DB 4.0.

SELECT * FROM OPENROWSET(
	'Microsoft.Jet.OLEDB.4.0', 
	'Excel 12.0;Database=d:\tmp\TimeSheets.xlsx;HDR=YES;IMEX=1', 
	'SELECT * FROM [Sheet1$]');

SELECT * FROM OPENROWSET(

'Microsoft.Jet.OLEDB.4.0',

'Excel 12.0;Database=d:\tmp\TimeSheets.xlsx;HDR=YES;IMEX=1',

'SELECT * FROM [Sheet1$]');

Для работы с Excel 2007 (.Xlsx) — Microsoft ACE OLEDB 12.0.

SELECT * FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=d:\tmp\TimeSheets.xlsx;HDR=YES;IMEX=1',
    'SELECT * FROM [Sheet1$]');

SELECT * FROM OPENROWSET (

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=d:\tmp\TimeSheets.xlsx;HDR=YES;IMEX=1',

'SELECT * FROM [Sheet1$]');

В Windows 10 открыть настройки источников данных ODBC можно написав «Источники данных ODBC» или через Панель управления \ Администрирование.

Extended Properties

HDR=YES|NO. HDR=YES означает, что первую строку листа, следует рассматривать как заголовки колонок. Т.о. значение из первой строки можно использовать как имена полей в sql запросах (любых: select, insert, update, delete).
IMEX=1|3. 1 — открыть соединение для чтения. 3 — для записи.

Как использовать

List<Student> students = GenerateRandomData(100);

try
{
    SaveFileDialog saveDialog = new SaveFileDialog()
    {
        DefaultExt = ".xlsx",
        FileName = "Sample",
        Filter = "Excel |*.xlsx"
    };
    if (saveDialog.ShowDialog() == true)
    {
        string Connstring = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={saveDialog.FileName};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
        using (OleDbConnection Conn = new OleDbConnection(Connstring))
        {
            await Conn.OpenAsync();

            //create sheet
            string sql = "CREATE TABLE Test (ID int,Name VarChar,Age int,Gender VarChar,phone VarChar,Eng int,Math int)";
            using (OleDbCommand cmd = new OleDbCommand(sql, Conn))
            {
                await cmd.ExecuteNonQueryAsync();

                //insert data
                cmd.CommandText = "Insert into Test values (@ID,@Name,@Age,@Gender,@phone,@Eng,@Math)";
                for (int i = 0; i < students.Count; i++)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@ID", OleDbType.Integer).Value = students[i].ID;
                    cmd.Parameters.Add("@Name", OleDbType.VarChar).Value = students[i].Name;
                    cmd.Parameters.Add("@Age", OleDbType.Integer).Value = students[i].Age;
                    cmd.Parameters.Add("@Gender", OleDbType.VarChar).Value = students[i].Gender ? "М" : "Ж";
                    cmd.Parameters.Add("@phone", OleDbType.VarChar).Value = students[i].phone;
                    cmd.Parameters.Add("@Eng", OleDbType.Integer).Value = students[i].Eng;
                    cmd.Parameters.Add("@Math", OleDbType.Integer).Value = students[i].Math;
                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }
    }
}
catch (Exception ex)
{
    StatusText.Text = ex.Message;
}

List<Student> students = GenerateRandomData(100);

try

{

SaveFileDialog saveDialog = new SaveFileDialog()

{

DefaultExt = ".xlsx",

FileName = "Sample",

Filter = "Excel |*.xlsx"

};

if (saveDialog.ShowDialog() == true)

{

string Connstring = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={saveDialog.FileName};Extended Properties='Excel 12.0 Xml;HDR=YES;'";

using (OleDbConnection Conn = new OleDbConnection(Connstring))

{

await Conn.OpenAsync();

//create sheet

string sql = "CREATE TABLE Test (ID int,Name VarChar,Age int,Gender VarChar,phone VarChar,Eng int,Math int)";

using (OleDbCommand cmd = new OleDbCommand(sql, Conn))

{

await cmd.ExecuteNonQueryAsync();

//insert data

cmd.CommandText = "Insert into Test values (@ID,@Name,@Age,@Gender,@phone,@Eng,@Math)";

for (int i = 0; i < students.Count; i++)

{

cmd.Parameters.Clear();

cmd.Parameters.Add("@ID", OleDbType.Integer).Value = students[i].ID;

cmd.Parameters.Add("@Name", OleDbType.VarChar).Value = students[i].Name;

cmd.Parameters.Add("@Age", OleDbType.Integer).Value = students[i].Age;

cmd.Parameters.Add("@Gender", OleDbType.VarChar).Value = students[i].Gender ? "М" : "Ж";

cmd.Parameters.Add("@phone", OleDbType.VarChar).Value = students[i].phone;

cmd.Parameters.Add("@Eng", OleDbType.Integer).Value = students[i].Eng;

cmd.Parameters.Add("@Math", OleDbType.Integer).Value = students[i].Math;

await cmd.ExecuteNonQueryAsync();

}

catch (Exception ex)

{

StatusText.Text = ex.Message;

}

Скачать проект