Здравствуйте, дорогие читатели блога lemiro.ru. Сегодня хочу поделиться, как выгрузить данные из DataGridView в EXCEL с помощью языка программирования C#. В предыдущей статье я уже рассматривал один способ экспорта, но там требовалась дополнительная сторонняя библиотека ExcelLibrary. В данном же способе, есть один минус: при большом количестве записей файл MICROSOFT EXCEL создается долго. Примерно на 500 строк — 14 секунд. Скорее всего это из-за того, что каждая ячейка с данными считывется и записывается в файл последовательно.
Для начала создадим новый класс и подключим ссылку на Microsoft.Office.Interop.Excel;. Так же добавим его в код:
using Excel = Microsoft.Office.Interop.Excel;
После этого создадим новые приватные переменные:
private Excel.Application _excel;
private Excel.Worksheet _sheet;
Опишем метод, который будет создавать COM-объект EXCEL:
public void CreateExcelFile()
{
//создаем COM-объект Excel
_excel = new Excel.Application();
//количество листов в книге
_excel.SheetsInNewWorkbook = 1;
//добавляем книгу
_excel.Workbooks.Add(Type.Missing);
//получам ссылку на первую открытую книгу
Excel.Workbook workbook = _excel.Workbooks[1];
//получаем ссылку на первый лист
_sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1);
}
Далее опишем вспомогательный метод, который будет переводить числовое значение ячейки в представление вида «A1»:
private string ExcelIntToCharCollums(int num)
{
switch (num)
{
case 1: return "A";
case 2: return "B";
case 3: return "C";
case 4: return "D";
case 5: return "E";
case 6: return "F";
case 7: return "G";
case 8: return "H";
case 9: return "I";
case 10: return "J";
case 11: return "K";
case 12: return "L";
case 13: return "M";
case 14: return "N";
case 15: return "O";
case 16: return "P";
case 17: return "Q";
case 18: return "R";
case 19: return "S";
case 20: return "T";
case 21: return "U";
case 22: return "V";
case 23: return "W";
}
return null;
}
Теперь, собственно и сам метод, который будет заполнять наш COM-объект (excel) данными из DataGridView:
public void DataGridViewToExcel(ref DataGridView dgw)
{
var dgw1 = new DataGridView();
dgw1 = dgw;
var cntColl = dgw1.ColumnCount;
var cntrow = dgw1.RowCount;
try
{
if (dgw1.RowCount != 0)
{
//Заполнение заголовков столбцов
for (int coll = 1; coll <= cntColl; coll++)
{
_sheet.Cells[1, coll] = dgw1.Columns[coll - 1].HeaderCell.Value;
_sheet.Range[ExcelIntToCharCollums(coll).ToString() + "1"].ColumnWidth = 15; //Ширина столбца
_sheet.Range[ExcelIntToCharCollums(coll).ToString() + "1"].WrapText = true; //Перенос текста
//делаем их жирными
_sheet.Range[ExcelIntToCharCollums(coll).ToString() + "1"].Font.Bold = true; //Жирный шрифт
_sheet.Range[ExcelIntToCharCollums(coll).ToString() + "1"].Font.Size = 14; // Размер шрифта
}
//Заполнение ячеек данными
for (int row = 0; row <= cntrow - 1; row++)
{
for (int coll = 1; coll <= cntColl; coll++)
{
_sheet.Cells[row + 2, coll] = dgw1.Rows[row].Cells[coll - 1].Value;
_sheet.Range[ExcelIntToCharCollums(coll).ToString() + row + 1.ToString()].WrapText = true; //Перенос текста
}
}
_excel.Visible = true;
// Уничтожение объекта Excel.
Marshal.ReleaseComObject(_excel);
// Вызываем сборщик мусора для немедленной очистки памяти
GC.GetTotalMemory(true);
}
}
catch (Exception ee)
{ MessageBox.Show(ee.ToString()); }
}
Хотелось бы отдельно рассмотреть код сборщика мусора. Без этих двух строк При закрытии программы иногда оставались зависшие процессы EXCEL, которые приходилось
завершать вручную через Диспетчер Задач.
// Уничтожение объекта Excel.
Marshal.ReleaseComObject(_excel);
// Вызываем сборщик мусора для немедленной очистки памяти
GC.GetTotalMemory(true);
Дополнение: Как выяснилось опытным путем, Microsoft Excel не умеет переносить текст в объединенных ячейках, для решения этой проблемы была написана функция,
которая копировала текст из объединенной ячейки в любую обычную, выставляла бы свойство "перенос текста", после чего копировала высоту ячейки, очищала ее и присваивала новую высоту объединенной.
public void AutoFit_MergeCell(string FirstCell, string SecondCell)
{
//FirstCell - объединенная ячейка, в которой хотим выставить высоту
//SecondCell - обыкновенная ячейка, с такой же шириной как и у FirstCell
//авто выставление высоты объединенных ячеек
if (_sheet.get_Range(FirstCell, FirstCell).Value2 != null) // скопировать текст из объединенной ячейки в любую другую ячейку
{
_sheet.get_Range(SecondCell, SecondCell).Value2 =
_sheet.get_Range(FirstCell, FirstCell).Value2.ToString();
}
_sheet.Range[SecondCell].WrapText = true; //Включаем перенос текса в ячейки
//_sheet.get_Range(SecondCell, SecondCell).EntireRow.AutoFit(); //применить автовысоту
double CellHeight = (double)_sheet.get_Range(SecondCell, SecondCell).RowHeight;//узнаем высоту ячейки
_sheet.get_Range(SecondCell, SecondCell).Value2 = ""; //очищаем ячейку в которую копировали строку
_sheet.get_Range(FirstCell, FirstCell).RowHeight = CellHeight; //и задаем нужную высоту для объединенной ячейки
}
Сам класс полностью:
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace _ExcelReport
{
class ExcelReport
{
private Excel.Application _excel;
private Excel.Worksheet _sheet;
private int _colCell; //Количество стобцов
private List _ColumnsName; //Название столбцов
public Process idprc;
///
/// Создание объекта Excel
///
public void CreateExcelFile()
{
_excel = new Excel.Application(); //создаем COM-объект Excel
_excel.SheetsInNewWorkbook = 1;//количество листов в книге
_excel.Workbooks.Add(Type.Missing); //добавляем книгу
Excel.Workbook workbook = _excel.Workbooks[1]; //получам ссылку на первую открытую книгу
_sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1);//получаем ссылку на первый лист
}
///
/// Метод переноса строк в объединенных ячейках
///
/// /// Объединенная ячейка с данными
/// Любая свободная ячека
public void AutoFit_MergeCell(string FirstCell, string SecondCell)
{
//FirstCell - объединенная ячейка, в которой хотим выставить высоту
//SecondCell - обыкновенная ячейка, с такой же шириной как и у FirstCell
//авто выставление высоты объединенных ячеек
if (_sheet.get_Range(FirstCell, FirstCell).Value2 != null) // скопировать текст из объединенной ячейки в любую другую ячейку
{
_sheet.get_Range(SecondCell, SecondCell).Value2 =
_sheet.get_Range(FirstCell, FirstCell).Value2.ToString();
}
_sheet.Range[SecondCell].WrapText = true; //Включаем перенос текса в ячейки
//_sheet.get_Range(SecondCell, SecondCell).EntireRow.AutoFit(); //применить автовысоту
double CellHeight = (double)_sheet.get_Range(SecondCell, SecondCell).RowHeight;//узнаем высоту ячейки
_sheet.get_Range(SecondCell, SecondCell).Value2 = ""; //очищаем ячейку в которую копировали строку
_sheet.get_Range(FirstCell, FirstCell).RowHeight = CellHeight; //и задаем нужную высоту для объединенной ячейки
}
///
/// Список название колонок
///
public void SetCollName(List str)
{
_ColumnsName = str;
}
///
/// Количество колонок
///
public void SetCountCol(int cnt)
{
_colCell = cnt;
}
///
/// Метод перевода числа в символ для Excel
///
private string ExcelIntToCharCollums(int num)
{
switch (num)
{
case 1: return "A";
case 2: return "B";
case 3: return "C";
case 4: return "D";
case 5: return "E";
case 6: return "F";
case 7: return "G";
case 8: return "H";
case 9: return "I";
case 10: return "J";
case 11: return "K";
case 12: return "L";
case 13: return "M";
case 14: return "N";
case 15: return "O";
case 16: return "P";
case 17: return "Q";
case 18: return "R";
case 19: return "S";
case 20: return "T";
case 21: return "U";
case 22: return "V";
case 23: return "W";
}
return null;
}
///
/// Метод заполнение файла Excel данными
///
public void DataGridViewToExcel(ref DataGridView dgw)
{
var dgw1 = new DataGridView();
dgw1 = dgw;
var cntColl = dgw1.ColumnCount;
var cntrow = dgw1.RowCount;
try
{
if (dgw1.RowCount != 0)
{
//Заполнение заголовков столбцов
for (int coll = 1; coll <= cntColl; coll++)
{
_sheet.Cells[1, coll] = dgw1.Columns[coll - 1].HeaderCell.Value;
_sheet.Range[ExcelIntToCharCollums(coll).ToString() + "1"].ColumnWidth = 15; //Ширина столбца
_sheet.Range[ExcelIntToCharCollums(coll).ToString() + "1"].WrapText = true; //Перенос текста
//делаем их жирными
_sheet.Range[ExcelIntToCharCollums(coll).ToString() + "1"].Font.Bold = true; //Жирный шрифт
_sheet.Range[ExcelIntToCharCollums(coll).ToString() + "1"].Font.Size = 14; // Размер шрифта
}
//Заполнение ячеек данными
for (int row = 0; row <= cntrow - 1; row++)
{
for (int coll = 1; coll <= cntColl; coll++)
{
_sheet.Cells[row + 2, coll] = dgw1.Rows[row].Cells[coll - 1].Value;
_sheet.Range[ExcelIntToCharCollums(coll).ToString() + row + 1.ToString()].WrapText = true; //Перенос текста
}
}
_excel.Visible = true;
// Уничтожение объекта Excel.
Marshal.ReleaseComObject(_excel);
// Вызываем сборщик мусора для немедленной очистки памяти
GC.GetTotalMemory(true);
}
}
catch (Exception ee)
{ MessageBox.Show(ee.ToString()); }
}
}
}