Загрузка данных из Microsoft Excel в DataGridView: Справочник по C#

В данной инструкции рассмотрены 5ть примеров реализации получения данных из файла Microsoft Excel 2003-2007.

Пример №1 – получение данных с использованием библиотеки объектов «Microsoft Excel 14.0»;
Пример №2 – получение данных с использованием класса «OleDbConnection»;
Пример №3 – получение данных с использованием библиотеки «Excel Data Reader – Read Excel files in .Net»;
Пример №4 – получение данных из буфера обмена используется класс «Clipboard»;
Пример №5 – ручной ввод;

Создайте проект Windows Form в Microsoft Visual Studio и добавьте на форму три компонента:

textBox1 - в данном компоненте будет выводится путь, имя и расширение выбранного файла;

button1 – кнопка для запуска процесса получения данных из выбранного файла;

dataGridView1 – элемент управления необходимый для отображения полученных данных.

Выберете компонент «dataGridView1» и сделайте клик правой клавишей мыши по нему, из появившегося контекстного меню выберете пункт «Свойства».

Установите следующие параметры в свойствах компонента:

Dock – None – свойство задает границы элемента управления, прикрепленные к его родительскому элементу управления и определяет способ изменения его размеров относительно родительского элемента управления. http://msdn.microsoft.com
Anchor - Top, Bottom, Left, Right - свойство задает границы контейнера, с которым связан элемент управления, и определяет способ изменения его при изменении размеров его родительского элемента. http://msdn.microsoft.com

Пример №1
В данном примере рассмотрено использование функций приложения Microsoft Office Excel из пакета Microsoft Office, c использованием библиотеки объектов Microsoft Excel 14. Данная библиотека позволяет управляемому коду взаимодействовать с объектной моделью приложения Microsoft Office, основанной на модели COM. Сделайте двойной клик по компоненту «button1», вы перейдете в автоматически созданный метод «button1_Click», события компонента «Click».

Перейдите в «Обозреватель решений» и найдите группу «References» которая содержит все ссылки на внешние компоненты в проекте. Сделайте клик правой клавишей мыши по данной группе и выберете из появившегося контекстного меню, пункт «Добавить ссылку…».

У вас откроется окно «Менеджер ссылок – (имя вашего проекта)», в левой части данного окна вам будет предложено выбрать одну из категорий. Visual Studio предоставляет четыре группы для выбора.

Сборки — список всех компонентов платформы .NET Framework, ссылки на которые можно добавить.
Решение — список всех повторно используемых компонентов, созданных в локальных проектах.
COM — список всех COM-компонентов, ссылки на которые можно добавить.
Обзор — позволяет осуществлять поиск компонента в файловой системе.

Выберете группу «COM» и ее подгруппу «Библиотеки типов». В центральной части окна вам будет предложен список доступных библиотек для подключения к вашему проекту. Найдите в списке библиотеку «Microsoft Excel 14.0 Object Library» и поставьте галочку рядом с именем данной библиотеки. В нижней части окна нажмите кнопку «ОК».

После добавления библиотеки, у вас появится три новых пункта в обозревателе решений:

Microsoft.Office.Core;
Microsoft.Office.Interop.Excel;
VBIDE.

Для работы с добавленными ссылками необходимо добавить следующие пространства имен с использованием директивы «using»:

using System.Reflection; - указывается ссылка на использование типов в пространстве имен «System.Reflection», при этом уточнение использования типа в этом пространстве имен не требуется;
using ExcelObj = Microsoft.Office.Interop.Excel; - создается псевдоним пространства имен «Microsoft.Office.Interop.Excel».

В данном примере для открытия файла используется класс «OpenFileDialog», реализующий открытие окна для выбора файла по заданному фильтру «Excel 2003(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx». Данный фильтр так же реализует защиту от выбора файла не относящегося к Excel.

OpenFileDialog ofd = new OpenFileDialog();
//Задаем расширение имени файла по умолчанию.
ofd.DefaultExt = "*.xls;*.xlsx";
//Задаем строку фильтра имен файлов, которая определяет
//варианты, доступные в поле "Файлы типа" диалогового
//окна.
ofd.Filter = " Excel 2003(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx";
//Задаем заголовок диалогового окна.
ofd.Title = "Выберите документ для загрузки данных";

После выбора файла создается новый объект «Application» или приложение «Excel», которое может содержать одну или более книг, ссылки на которые содержит свойство «Workbooks». Книги - объекты «Workbook», могут содержать одну или более страниц, ссылки на которые содержит свойство «Worksheets». Страницы – «Worksheet», могут содержать объекты ячейки или группы ячеек, ссылки на которые становятся доступными через объект «Range». Полученные данные из файла будут заноситься в таблицу «dt», созданную с использованием класса «DataTable».

ExcelObj.Application app = new ExcelObj.Application();
ExcelObj.Workbook workbook;
ExcelObj.Worksheet NwSheet;
ExcelObj.Range ShtRange;
DataTable dt = new DataTable();

В коде присутствует проверка, что пользователь действительно выбрал файл, если данное условие выполнено, в текстовое поле с помощью свойства «FileName», класса «OpenFileDialog» помещается путь, имя и расширение выбранного файла в элемент управления «textBox1».

if (ofd.ShowDialog() == DialogResult.OK)
{
    textBox1.Text = ofd.FileName;

Для открытия существующего документа используется метод «Open» из набора «Excel.Workbooks», в качестве основного параметра указывается путь к файлу, остальные параметры остаются пустыми.

workbook = app.Workbooks.Open(ofd.FileName, Missing.Value,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Missing.Value);

Полный список параметров метода «Open» приведен в таблице ниже.

Имя параметра	Описание
FileNamee	Имя открываемого файла
UpdateLinks	Способ обновления ссылок в файле
ReadOnly	При значении true открытие только для чтения
Format	Определение формата символа разделителя
Password	Пароль доступа к файлу (до 15 символов)
WriteResPassword	Пароль на сохранение файла
IgnoreReadOnlyRecommended	При значении true отключается вывод запроса на работу без внесения изменений
Origin	Тип текстового файла
Delimiter	Разделитель при Format = 6
Editable	Используется только для надстроек Excel 4.0
Notify	При значении true имя файла добавляется в список нотификации файлов
Converter	Используется для передачи индекса конвертера файла используемого для открытия файла
AddToMRU	При true имя файла добавляется в список открытых файлов
Local	---
CorruptLoad	---

Для доступа к листу из книги «Workbook», используется метод «Sheets.get_Item» с указанием номера листа. Нумерация листов начинается с 1.

NwSheet = (ExcelObj.Worksheet)workbook.Sheets.get_Item(1);

Чтобы получить объект Microsoft.Office.Interop.Excel.Range, который представляет все ячейки, содержащие значение на данный момент, используется свойство станицы «Worksheet.UsedRange».

ShtRange = NwSheet.UsedRange;

После получения объекта «Range», с помощью цикла «For» загружается первая строка из таблицы и каждое значение устанавливается в качестве имени колонки таблицы.

for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
{
    dt.Columns.Add(
 new DataColumn((ShtRange.Cells[1, Cnum] as ExcelObj.Range).Value2.ToString()));
}

Далее таким же способом загружаются все оставшиеся строки с добавлением в таблицу.

for (int Rnum = 2; Rnum <= ShtRange.Rows.Count; Rnum++)
{
    DataRow dr = dt.NewRow();
    for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
    {
        if ((ShtRange.Cells[Rnum, Cnum] as ExcelObj.Range).Value2 != null)
        {
            dr[Cnum - 1] = 
   (ShtRange.Cells[Rnum, Cnum] as ExcelObj.Range).Value2.ToString();
        }
    }
    dt.Rows.Add(dr);
    dt.AcceptChanges();
}

По завершении загрузки данных с указанного листа, сформированная таблица «dt» подключается к элементу управления «dataGridView1». Так же открытый объект «Application» или приложение «Excel» закрывается.

dataGridView1.DataSource = dt;
app.Quit();

Полный листинг рассмотренного кода, приведен ниже, добавьте его в метод «button1_Click» компонента «button1».

OpenFileDialog ofd = new OpenFileDialog();
//Задаем расширение имени файла по умолчанию.
ofd.DefaultExt = "*.xls;*.xlsx";
//Задаем строку фильтра имен файлов, которая определяет
//варианты, доступные в поле "Файлы типа" диалогового
//окна.
ofd.Filter = "Excel Sheet(*.xlsx)|*.xlsx";
//Задаем заголовок диалогового окна.
ofd.Title = "Выберите документ для загрузки данных";
ExcelObj.Application app = new ExcelObj.Application();
ExcelObj.Workbook workbook;
ExcelObj.Worksheet NwSheet;
ExcelObj.Range ShtRange;
DataTable dt = new DataTable();
if (ofd.ShowDialog() == DialogResult.OK)
{
    textBox1.Text = ofd.FileName;

    workbook = app.Workbooks.Open(ofd.FileName, Missing.Value,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Missing.Value);
    
    //Устанавливаем номер листа из котрого будут извлекаться данные
    //Листы нумеруются от 1
    NwSheet = (ExcelObj.Worksheet)workbook.Sheets.get_Item(1);
    ShtRange = NwSheet.UsedRange;
    for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
    {
        dt.Columns.Add(
           new DataColumn((ShtRange.Cells[1, Cnum] as ExcelObj.Range).Value2.ToString()));
    }
    dt.AcceptChanges();

    string[] columnNames = new String[dt.Columns.Count];
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        columnNames[0] = dt.Columns[i].ColumnName;
    }                             
    
    for (int Rnum = 2; Rnum <= ShtRange.Rows.Count; Rnum++)
    {
        DataRow dr = dt.NewRow();
        for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
        {
            if ((ShtRange.Cells[Rnum, Cnum] as ExcelObj.Range).Value2 != null)
            {
                dr[Cnum - 1] = 
    (ShtRange.Cells[Rnum, Cnum] as ExcelObj.Range).Value2.ToString();
            }
        }
        dt.Rows.Add(dr);
        dt.AcceptChanges();
    }
    
    dataGridView1.DataSource = dt;
    app.Quit();
}
else
    Application.Exit();

Пример №2
Данный пример в качестве механизма получения данных использует класс «OleDbConnection», который предоставляет открытое подключение к источнику данных. При подключении необходимо указать строку специальную строку с несколькими параметрами:

Provider=Microsoft.ACE.OLEDB.12.0 – имя поставщика OLE DB;
Data Source=ofd.FileName – путь к источнику данных полученный при выборе файла в диалоговом окне;
Extended Properties='Excel 12.0 XML' – параметр расширенного подключения;
HDR = YES - указывает, что первая строка содержит имена столбцов, а не данные. Значение «NO» свидетельствует, что лист не содержит заголовков столбцов.

Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанный источник для определения типа данных каждого столбца. Если столбец содержит смешанные типы данных, особенно если числовые данные смешаны с текстовыми данными, драйвер принимает решение в пользу того типа данных, которого больше, и возвращает значения NULL в ячейки, содержащие данные другого типа. (В случае равенства преимущество получает числовой тип.) Большинство параметров форматирования ячеек в листе Excel не затрагивает это определение типа данных. Можно изменить поведение драйвера Excel, указав режим импорта. Чтобы указать режим импорта, необходимо добавить параметр IMEX=1 к значению расширенных свойств в строке соединения. В этом состоянии драйвер принудительно преобразовывает смешанные данные в текст. После составления строки подключения, устанавливается соединение с указанным файлом через класс «OleDbConnection»

System.Data.OleDb.OleDbConnection con =
    new System.Data.OleDb.OleDbConnection(constr);
con.Open();

При успешном подключении к источнику данных Excel с помощью «Microsoft ADO.NET», создаётся расположенный в памяти кэш данных, с использованием класса «DataSet». Далее извлекается список таблиц метаданных с помощью метода «GetOleDbSchemaTable».

DataSet ds = new DataSet();
DataTable schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
 new object[] { null, null, null, "TABLE" });

После получения таблицы с листами, получаем название первого листа, для создания sql запроса к файлу. Изменяя значение «Rows», вы изменяете, номер листа в книге, к которому будет выполнен запрос.

string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
string select = String.Format("SELECT * FROM [{0}]", sheet1);

Далее при помощи класса «OleDbDataAdapter» и его метода «Fill» данные загружаются в Dataset – «ds».

System.Data.OleDb.OleDbDataAdapter ad = 
    new System.Data.OleDb.OleDbDataAdapter(select, con);
ad.Fill(ds);

Полный листинг примера приведен ниже.

OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = "*.xls;*.xlsx";
ofd.Filter = "Excel 2003(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx";
ofd.Title = "Выберите документ для загрузки данных";

if (ofd.ShowDialog() == DialogResult.OK)
{
    textBox1.Text = ofd.FileName;

    String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                    ofd.FileName +
                    ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

    System.Data.OleDb.OleDbConnection con =
        new System.Data.OleDb.OleDbConnection(constr);
    con.Open();

    DataSet ds = new DataSet();
    DataTable schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
        new object[] { null, null, null, "TABLE" });

    string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
    string select = String.Format("SELECT * FROM [{0}]", sheet1);

    System.Data.OleDb.OleDbDataAdapter ad = 
        new System.Data.OleDb.OleDbDataAdapter(select, con);

    ad.Fill(ds);

    DataTable tb = ds.Tables[0];
    con.Close();
    dataGridView1.DataSource = tb;
    con.Close();
}
else
{
    MessageBox.Show("Вы не выбрали файл для открытия",
            "Загрузка данных...", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Пример №3

Для работы с документами также можно воспользоваться библиотеками сторонних производителей. Для примера рассмотрим библиотеку «Excel Data Reader – Read Excel files in .Net». Данный компонент вы можете скачать как с нашего сайта Rusfolder.net, так и с сайта производителя Codeplex.com.

Скачайте данный компонент и перейдите в обозреватель решений. Найдите группу «References», в которой содержатся все ссылки на внешние компоненты в проекте. Сделайте клик правой клавишей мыши по данной группе и выберете из появившегося контекстного меню, пункт «Добавить ссылку…».

Выберете группу «Обзор», данная группа позволяет вам осуществить поиск компонента в файловой системе. Нажмите на кнопку «Обзор…», расположенную в нижней части окна менеджера ссылок.

У вас откроется окно с заголовком «Выберете файлы, на которые нужно установить ссылки», перейдите в директорию со скачанными библиотеками. Данные библиотеки находятся в архиве «WinRar», распакуйте его. В папке с распакованным архивом вы увидите две директории:

Net20 - директория содержит библиотеки необходимые при работе с .Net Framework версии от 2.0 до 4.0;
Net45 – директория содержит библиотеки необходимые при работе с .Net Framework версии 4.5.

При создании данного проекта был выбран .Net Framework версии 3.5, поэтому переходим в директорию «Net20» и выбираем две библиотеки расположенные в ней, это «Excel.dll» и «ICSharpCode.SharpZipLib.dll». После выбора данных библиотек, нажмите на кнопку «Добавить» расположенную в нижней части данного окна.

После добавления библиотек, вы увидите их название, и путь к ним в центральной части окна менеджера ссылок. Так же напротив каждой из них будет установлена галочка, это означает, что в проект будут добавлены ссылки на обе библиотеки. В левой части окна будет выведено описание библиотек, это «Имя», «Автор» и «Версия файла». Нажмите на кнопку «ОК», расположенную в нижней части окна менеджера.

После добавления ссылок на библиотеки, у вас появится два новых пункта в обозревателе решений.

Перейдите в конструктор формы, нажав сочетание клавиш «Shift+F7». Добавьте на форму элемент управления «Button» из панели элементов и сделайте двойной клик левой клавишей мыши по нему. Вы перейдете в автоматически созданный метод «button3_Click», события компонента «Click». Добавьте приведенный ниже листинг в тело данного метода.

//http://exceldatareader.codeplex.com/

OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = "*.xls;*.xlsx";
ofd.Filter = "Excel 2003(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx";
ofd.Title = "Выберите документ для загрузки данных";

if (ofd.ShowDialog() == DialogResult.OK)
{
    textBox1.Text = ofd.FileName;

    System.IO.FileStream stream = 
        System.IO.File.Open(ofd.FileName, 
  System.IO.FileMode.Open, 
  System.IO.FileAccess.Read);

    Excel.IExcelDataReader IEDR;

    int fileformat = ofd.SafeFileName.IndexOf(".xlsx");

    if (fileformat > -1)
    {
        //2007 format *.xlsx
        IEDR = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream);
    }
    else
    {
        //97-2003 format *.xls
        IEDR = Excel.ExcelReaderFactory.CreateBinaryReader(stream);
    }

    //Если данное значение установлено в true
    //то первая строка используется в качестве 
    //заголовков для колонок
    IEDR.IsFirstRowAsColumnNames = true;

    DataSet ds = IEDR.AsDataSet();

    //Устанавливаем в качестве источника данных dataset 
    //с указанием номера таблицы. Номер таблицы указавает 
    //на соответствующий лист в файле нумерация листов 
    //начинается с нуля.
    dataGridView1.DataSource = ds.Tables[0];
    IEDR.Close();
}
else
{
    MessageBox.Show("Вы не выбрали файл для открытия",
     "Загрузка данных...", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Пример № 4

В данном примере рассмотрен листинг реализующий вставку данных скопированных в буфер обмена из файла Excel. Для получения данных из буфера обмена используется класс «Clipboard» с использованием его метода «GetDataObject», который извлекает данные находящиеся в данный момент, в системном буфере обмена. Во избежание ошибок с форматом получаемых данных, используется интерфейс «IDataObject», который предоставляет не зависящий от формата, механизм передачи данных.

IDataObject dataInClipboard = Clipboard.GetDataObject();

string stringInClipboard = 
    (string)dataInClipboard.GetData(DataFormats.Text);

Более подробно ознакомиться с классом «Clipboard», вы можете на сайте Microsoft MSDN.

Так как в буфере обмена, все данные содержаться в виде одной строки и их необходимо разбить на массив данных.

Данную задачу решает метод «String.Split». Метод возвращает строковый массив, содержащий подстроки разделенные элементами «\r» - возврат каретки и «\n» - перевод строки.

char[] rowSplitter = { '\r', '\n' };
string[] rowsInClipboard = 
    stringInClipboard.Split(rowSplitter, 
    StringSplitOptions.RemoveEmptyEntries);

Полученный массив строк содержит «\t» - знак горизонтальной табуляции, который разделят значения ячеек. Для добавления столбцов с заголовками (если такая строка скопирована в буфер обмена), необходимо взять из массива первый элемент и с использованием метода «String.Split» разбить на новый массив, указав в качестве элемента разделения знак горизонтальной табуляции - «\t».

Далее необходимо с помощью цикла «foreach» пройти по всему массиву и добавить новые колонки с указанием в качестве имени полученное значение.

string[] words = rowsInClipboard[0].Split('\t');
foreach (string word in words)
{
    dt.Columns.Add(word);
}
По такому же принципу происходит добавление строк в таблицу.
 
for (int i = 2; i <= rowsInClipboard.Length; i++)
{
    string[] rows = rowsInClipboard[i - 1].Split('\t');
    dt.Rows.Add(rows);
}

После получения всех данных из буфера обмена и заполнения таблицы «dt», она устанавливается в качестве источника данных элементу управления «dataGridView1». Перейдите в конструктор формы, нажав сочетание клавиш «Shift+F7». Добавьте на форму компонент «Button» из панели элементов и сделайте двойной клик левой клавишей мыши по нему. Вы перейдете в автоматически созданный метод «button4_Click», события компонента «Click». Добавьте приведенный ниже полный листинг в тело данного метода.

//Переменная указывающая, использовать ли
//первую строку, в качестве заголовков для 
//столбцов.
bool IsFirstRowAsColumnNames = true;

IDataObject dataInClipboard = Clipboard.GetDataObject();

string stringInClipboard = 
    (string)dataInClipboard.GetData(DataFormats.Text);

char[] rowSplitter = { '\r', '\n' };
string[] rowsInClipboard = 
    stringInClipboard.Split(rowSplitter, 
    StringSplitOptions.RemoveEmptyEntries);
DataTable dt = new DataTable();

if (IsFirstRowAsColumnNames)
{
    //В данном случае берется первая строка 
    //и разделятся на отдельные части с помощью 
    //класса Split. Данные помещаются в массив
    //после чего, циклом forech добавляются 
    //заголовки столбцов
    string[] words = rowsInClipboard[0].Split('\t');
    foreach (string word in words)
    {
        dt.Columns.Add(word);
    }

    for (int i = 2; i <= rowsInClipboard.Length; i++)
    {
        string[] rows = rowsInClipboard[i - 1].Split('\t');
        dt.Rows.Add(rows);
    }
}
else
{
    //Если в скопированных данных отсутствуют 
    //заголовки для столбцов, то 
    // автоматически пронумеровать их
    for (int colc = 1; colc <= rowsInClipboard[0].Split('\t').Length; colc++)
    {
        dt.Columns.Add("Столбец " + colc);
    }

    for (int i = 1; i <= rowsInClipboard.Length; i++)
    {
        string[] rows = rowsInClipboard[i - 1].Split('\t');
        dt.Rows.Add(rows);
    }
}
dataGridView1.DataSource = dt;

Пример №5

Добавление данных вручную.

В первых трех примерах присутствует строка закрытия подключения к выбранному файлу. Например:

IEDR.Close();
con.Close();
app.Quit();

Данные строки кода необходимы для завершения процесса «EXCEL.EXE», каждый раз запускающегося при подключении к выбранному файлу. При отсутствии данного кода, количество процессов с каждым открытым файлом будет возрастать, что может привести к сбоям в работе операционной системы.

Вы так же можете самостоятельно рассмотреть и другие компоненты по работе с файлами Microsoft Excel, например библиотеки от «GemBoxSoftware».

Ссылка для скачивания примера: Яндекс.Диск

Ссылка для скачивания библиотеки Excel Data Reader: Яндекс.Диск

Справочник по C#

Загрузка данных из Microsoft Excel в DataGridView

Комментариев нет:

Отправить комментарий

document.write(ssyby);

Загрузка данных из Microsoft Excel в DataGridView

Комментариев нет:

Отправить комментарий