Здравствуйте, дорогие читатели блога lemiro.ru. Сегодня хочу поделиться, как выгрузить данные из DataGridView в EXCEL с помощью языка программирования C#. В предыдущей статье я уже рассматривал один способ экспорта, но там требовалась дополнительная сторонняя библиотека ExcelLibrary. В данном же способе, есть один минус: при большом количестве записей файл MICROSOFT EXCEL создается долго. Примерно на 500 строк — 14 секунд. Скорее всего это из-за того, что каждая ячейка с данными считывется и записывается в файл последовательно.
Для начала создадим новый класс и подключим ссылку на Microsoft.Office.Interop.Excel;. Так же добавим его в код:
|
1 |
using Excel = Microsoft.Office.Interop.Excel; |
После этого создадим новые приватные переменные:
|
1 2 |
private Excel.Application _excel; private Excel.Worksheet _sheet; |
Опишем метод, который будет создавать COM-объект EXCEL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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»:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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, которые приходилось
завершать вручную через Диспетчер Задач.
|
1 2 3 4 |
// Уничтожение объекта Excel. Marshal.ReleaseComObject(_excel); // Вызываем сборщик мусора для немедленной очистки памяти GC.GetTotalMemory(true); |
Дополнение: Как выяснилось опытным путем, Microsoft Excel не умеет переносить текст в объединенных ячейках, для решения этой проблемы была написана функция,
которая копировала текст из объединенной ячейки в любую обычную, выставляла бы свойство «перенос текста», после чего копировала высоту ячейки, очищала ее и присваивала новую высоту объединенной.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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; //и задаем нужную высоту для объединенной ячейки } |
Сам класс полностью:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 |
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<string> _ColumnsName; //Название столбцов public Process idprc; /// <summary> /// Создание объекта Excel /// </summary> 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);//получаем ссылку на первый лист } /// <summary> /// Метод переноса строк в объединенных ячейках /// </summary> /// /// <param name="FirstCell">Объединенная ячейка с данными</param> /// <param name="SecondCell">Любая свободная ячека</param> 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; //и задаем нужную высоту для объединенной ячейки } /// <summary> /// Список название колонок /// </summary> public void SetCollName(List<string> str) { _ColumnsName = str; } /// <summary> /// Количество колонок /// </summary> public void SetCountCol(int cnt) { _colCell = cnt; } /// <summary> /// Метод перевода числа в символ для Excel /// </summary> 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; } /// <summary> /// Метод заполнение файла Excel данными /// </summary> 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()); } } } } |