Экспорт из DataGridView в Excel с помощью C# (Без сторонних библиотек)

Автор: | 09.04.2015
Нет комментариев

Здравствуйте, дорогие читатели блога 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()); }
            
        }
    }
}

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *