Объектная модель Microsoft Excel
1. Сущность и основные элементы объектной модели Excel
Объектная модель MS Excel по общим принципам идентична объектной модели MS Word. Эта модель также имеет иерархическую структуру, в корне которой находится объект Application (Excel.Application), через который обеспечивается доступ к любой коллекции или внутреннему объекту приложения MS Excel или к компонентам открытых рабочих книг. Общая структура объектной модели MS Excel представлена на рисунке 1.
Рисунок 1 – Структура объектной модели MS Excel
Как видно из рисунка, основу объектной модели составляет объект Application. Он включает в себя объекты и коллекции (наборы), соответствующие пользовательскомй интерфейсу Excel. Это может быть, например, Selection — текущий выделенный объект, рабочие книги, элементы управления, диалоговые окна и другие компоненты приложения.
Объект Selection может представлять из себя различные элементы, в зависимости от текущего выделения. Это логично, так как пользователь может выделять отдельные ячейки, строки, столбцы, лист полностью, диаграмму и т.п. И в зависимости от пользовательского выделения структура объекта и его свойства так же будут меняться. Именно поэтому на данной иерархии объект Selection вынесен в отдельный элемент.
Остальные компоненты объектной модели Excel группируются в соответствующие наборы объектов, доступ к которым может осуществляться по индексу. Так, элементами коллекции рабочих книг являются экземпляры класса «рабочая книга». Рабочая книга, в свою очередь, содержит в себе коллекцию листов. Элементами коллекции могут быть как рабочие листы, содержащие ячейки с данными, так и листы с диаграммами. Данные два типа листов представлены отдельными различными объектами, унаследованными от общего объекта листа.
Основным объектом рабочего листа, с которым работает как пользователь Excel, так и разработчик при использовании объектной модели Word, является ячейка. Объект ячейки также является составным, обладает огромным количеством разнообразных свойств (текст, границы, стиль, шрифт, заливка и т. д.) и может содержать в себе другие объекты.
Чтобы настраивать и форматировать ячейку, нужно сперва тем или иным образом получить к ней доступ. Это можно сделать, например, с помощью объекта Range, представляющего собой некую область ячеек. Кроме того, ячейки могут объединяться в строки и столбцы. Для всех этих объектов существуют свои наборы и коллекции, к элементам которых можно получать доступ по индексу.
Таким образом, к основным объектам Excel относятся:
- Application;
- Workbook;
- Worksheet;
- Range.
Для корректной работы с объектной моделью Excel нужно подключить соответствующую ссылку в проекте Visual Studio, а также требуемое пространство имен, например, так.
using Excel = Microsoft.Office.Interop.Excel;
Набор Workbooks
Набор Workbooks позволяет работать с открытыми рабочими книгами, создавать рабочие книги, импортировать в них данные. Далее перечислены некоторые основные операции с набором Workbooks.
Создание рабочей книги. Для этого служит метод Add. В качестве параметра ему можно передать имя шаблона рабочей книги.
Excel.Workbook wb = ThisApplication.Workbooks.Add (Type.Missing);
Закрытие всех открытых рабочих книг. Метод Close можно применять как к отдельному файлу, так и ко всем открытым рабочим книгам.
ThisApplication.Workbooks.Close();
Открытие существующей рабочей книги. Для этого вызывается метод Open набора Workbooks. В простейшей своей форме метод Open используется так, как показано ниже. У метода Open большое количество необязательных параметров, определяющих его поведение при определенных обстоятельствах, но обычно эти параметры не нужны.
Excel.Workbook wb = ThisApplication.Workbooks.Open( @"C:\YourPath\Yourworkbook.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Работа с листами
Свойство Sheets класса Workbook возвращает объект Sheets. Этот объект содержит набор объектов Sheet, каждый из которых относится либо к Worksheet (лист с ячейками данных), либо к Chart (лист с диаграммой). В классе Sheets есть множество полезных компонентов. Рассмотрим некоторые из них.
Свойство Visible позволяет показать или скрыть существующий лист без его удаления и повторного создания. Оно принимает значения перечислимого типа XlSheetVisibility (xlSheetHidden, xlSheetVeryHidden, xlSheetVisible). При значении xlSheetHidden пользователи могут увидеть скрытый лист средствами UI, а при значении XlSheetVeryHidden для отображения скрытой таблицы нужно выполнить код:
((Excel.Worksheet) ThisWorkbook.Sheets[1]).Visible = Excel.XlSheetVisibility.xlSheetVisible;
Метод Add позволяет добавить новый лист в набор листов рабочей книги и принимает четыре необязательных параметра, задающих местонахождение листа, количество добавляемых листов и тип листа (рабочий лист, диаграмма и т. д.):
Excel.Sheet sh = ThisWorkbook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Метод Copy создает копию листа и вставляет лист в заданное место. Новый лист можно вставлять до или после существующего листа. По умолчанию Excel создает новую рабочую книгу, содержащую новый лист. В следующем фрагменте кода копируется первый лист рабочей книги, и копия помещается после третьего листа:
((Excel.Worksheet) ThisWorkbook.Sheets[1]).Copy (Type.Missing, ThisWorkbook.Sheets[3]);
Метод Delete удаляет заданный лист:
((Excel.Worksheet) ThisWorkbook.Sheets[1]).Delete();
Метод Move во многом аналогичен методу Copy за исключением того, что в нем не создается новый экземпляр листа, а переносится существующий. Можно указать лист, перед которым помещается переносимый лист, или лист, после которого он помещается (но не тот и другой сразу). А по умолчанию Excel, как и в случае Copy, создает новую рабочую книгу, содержащую этот лист. В следующем фрагменте первый рабочий лист делается последним.
Excel.Sheets shts = ThisWorkbook.Sheets; ((Excel.Worksheet)shts[1]).Move(Type.Missing, shts[shts.Count]);
Метод PrintOut позволяет распечатать выбранный объект (метод применим к нескольким разным объектам). Имеется возможность задать ряд дополнительных параметров: диапазон печатаемых страниц, число копий, нужен ли предварительный просмотр перед печатью, имя используемого принтера, нужна ли печать в файл, требуется ли разбор по копиям и т.п. В следующем примере печатается заданный лист, причем на принтер выводится лишь первая страница в двух экземплярах, перед печатью выполняется предварительный просмотр и используется принтер по умолчанию.
((Excel.Worksheet)ThisApplication.Sheets[1]).
PrintOut(1, 1, 2, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Метод PrintPreview позволяет выполнить предварительный просмотр перед печатью, при необходимости можно запретить изменения в разметке страницы.
((Excel.Worksheet)ThisApplication.Sheets[1]).PrintPreview (false);
Метод Select выделяет заданный объект, перемещая выделение, сделанное пользователем. (Чтобы передать фокус объекту, не меняя пользовательское выделение, можно применять метод Activate.) При необходимости можно передать ссылку на объект, замещаемый текущим выделением. В следующем фрагменте выделяется первый рабочий лист.
((Excel.Worksheet)ThisApplication.Sheets[1]).Select (Type.Missing);
Методы класса Workbook
Класс Workbook содержит огромное количество методов. Рассмотрим некоторые из, которые используются чаще всего.
Метод Activate активизирует рабочую книгу и выбирает первый лист рабочей книги.
ThisApplication.Workbooks[1].Activate();
Метод Close закрывает заданную рабочую книгу; при необходимости можно указать, требуется ли сохранение изменений. Если рабочую книгу еще ни разу не сохраняли, можно задать имя файла. Кроме того, если рабочая книга передается другим пользователям, можно указать, следует ли отправлять рабочую книгу очередному пользователю. В следующем фрагменте кода рабочая книга закрывается без сохранения изменений.
ThisApplication.Workbooks[1].Close(false, Type.Missing, Type.Missing);
Метод Save, как легко догадаться, сохраняет рабочую книгу. Если рабочую книгу еще не сохраняли, следует вместо этого метода вызывать SaveAs, чтобы указать путь (иначе Excel сохранит ее в текущей папке под именем, присвоенным при создании):
// Сохраняем все открытые рабочие книги
foreach (Excel.Workbook wb in ThisApplication.Workbooks)
{
wb.Save();
}
Метод SaveAs значительно сложнее метода Save. Он позволяет сохранить заданную рабочую книгу и при необходимости указать ее имя, формат файла, пароль, режим доступа и т. д. Все параметры метода можно посмотреть в справочной системе.
2. Объект Range. Работа с диапазонами
Объект Range применяется в большинстве приложений Excel. Чтобы выполнить операцию с какой-либо областью Excel, ее сначала нужно представить в виде объекта Range и далее работать уже с этим объектам, обращаясь к его свойствам и методам. Объект Range может представлять из себя ячейку, строку, столбец, выборку ячеек, содержащую один или несколько блоков ячеек (которые не обязательно должны быть смежными) и даже группу ячеек из разных листов.
Программное обращение к диапазонам
Класс Range настолько гибок, что при программной работе с диапазонами возможны множество вариантов. Иногда объект Range представляет собой отдельный объект, а иногда он выступает в роли набора объектов. Хотя объект Range часто ссылается на одиночный объект, у него есть члены Item и Count, поэтому зачастую бывает сложно разобраться, как им правильно пользоваться.
Рассмотрим далее несколько различных методов получения диапазонов. В каждом из последующих примеров предполагается, что предварительно описаны следующие переменные:
Excel.Worksheet ws = (Excel.Worksheet)ThisWorkbook.
Worksheets[1]; Excel.Range rng, rng1, rng2;
Итак, для того чтобы получить ссылку на диапазон, следует выполнить одно из следующих действий:
1. Обратиться к свойству ActiveCell объекта Application:
rng = ThisApplication.ActiveCell;
2. Задействовать свойство Range объекта, указав диапазон в строковом формате. Поскольку параметризованные неиндексированные свойства в C# не поддерживаются, это приходится делать вызовом метода get_Range:
rng = ws.get_Range("A1", Type.Missing); rng = ws.get_Range("A1:B12", Type.Missing);
3. Воспользоваться свойством Cells рабочего листа, указав значения строки и столбца:
rng = (Excel.Range)ws.Cells[1, 1];
4. Указать «углы» диапазона. Можно также напрямую ссылаться на свойства Cells, Rows или Columns диапазона:
rng = ws.get_Range("A1", "C5");
rng = ws.get_Range("A1", "C5").Cells; rng = ws.get_Range("A1", "C5").Rows; rng = ws.get_Range("A1", "C5").Columns;
5. Обратиться к именованному диапазону. Поскольку в C# метод get_Range принимает два параметра, а для задания диапазона по имени достаточно одного, в качестве второго параметра передавается Type.Missing:
rng = ThisApplication.Range("SomeRangeName", Type.Missing);
6. Обратиться к заданной строке, столбцу или диапазону строк и столбцов. Следует обратить внимание, что свойства Rows и Columns возвращают значения Object, поэтому, если параметр Option Strict установлен в On, необходимо преобразование типов:
rng = (Excel.Range)ws.Rows[1, Type.Missing]; rng = (Excel.Range)ws.Rows["1:3", Type.Missing]; rng = (Excel.Range)ws.Columns[3, Type.Missing];
В случае свойства Columns функция IntelliSense среды VisualStudio дает неверную подсказку: показывает, что нужно задать значение строки, затем — столбца. А на самом деле значения для свойства Columns идут в обратном порядке. В свойствах Rows и Columns второй параметр не используется.
7. Через свойство Selection объекта Application получить диапазон, соответствующий выделенным ячейке или ячейкам). Свойство Address – еще одно параметризованное свойство, которое в C# не обрабатывается напрямую. Чтобы получить адрес, соответствующий объекту Range, нужно вызвать метод get_Address. Все параметры свойства Address необязательны, а методу get_Address требуется передавать пять параметров; скорее всего, интересен будет лишь третий параметр, задающий формат адреса.:
label1.Text = ((Excel.Range)ThisApplication.Selection). get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));
8. Создать диапазон, содержащий объединение двух других диапазонов (указав два диапазона в кавычках через запятую):
rng = ThisApplication.get_Range("A1:D4, F2:G5", Type.Missing);
9. Создать диапазон, являющийся пересечением двух других диапазонов (указав эти два диапазона в кавычках без разделителя):
rng = ThisApplication.get_Range("A1:D16 B2:F14", Type.Missing);
10. Воспользоваться свойством Offset диапазона, чтобы получить диапазон, заданный координатами относительно исходного диапазона; в следующем примере содержимое добавляется в область под ячейкой, находящейся в первой строке и первом столбце. Свойство Offset также параметризованное, поэтому в коде на C# нельзя прочитать его значение напрямую. Необходимо вызывать соответствующий метод get_Offset:
rng = (Excel.Range) ws.Cells[1, 1]; for (int i = 1; i <= 5; i++)
{
rng.get_Offset(i, 0).Value2 = i.ToString();
}
11 . Через свойство CurrentRegion получить диапазон, который представляет текущую область (ограниченную ближайшими пустыми строкой и столбцом).
Вывод данных в ячейки
Основным действием, которое чаще всего выполняется с ячейками либо с их диапазонами, является получение либо запись данных в ячейки. Чтобы получить доступ к содержимому ячейки, используется свойство Value2.
Range rng = ThisApplication.ActiveCell; label1.Text = rng.Value2.ToString();
Автоматическое заполнение диапазонов
Метод AutoFill класса Range позволяет автоматически заполнить диапазон значениями. Чаще всего метод AutoFill используется для занесения в диапазон последовательно возрастающих или убывающих значений. Поведение при автозаполнении задается необязательной константой из перечислимого XlAutoFillType (xlFillDays, xlFillFormats, xlFillSeries, xlFillWeekdays, xlGrowthTrend, xlFillCopy, xlFillDefault, xlFillMonths, xlFillValues, xlFillYears или xlLinearTrend). Если тип заполнения не указан, Excel использует тип заполнения по умолчанию (xlFillDefault) и заполняет заданный диапазон так, как считает нужным.
Сортировка данных в диапазоне
Метод Sort позволяет программно сортировать данные так же, как и в пользовательском интерфейсе Excel. Следует задать диапазон, в котором выполняется сортировка, и при необходимости до трех строк или столбцов, по которым ведется сортировка.
3. Общие приемы форматирования ячеек
Чтобы нарисовать границу вокруг диапазона ячеек, можно использовать метод BorderAround:
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
Для заливки ячеек следует обрабатывать свойство Interior объекта Range и его подсвойства, например, ColorIndex:
range.Interior.ColorIndex = 34;
Если необходимо нарисовать только одну границу ячейки либо диапазона (например, нижнюю), надо настраивать свойство Borders, представляющее собой набор границ диапазона. Для каждого элемента этой коллекции (т.е. для каждой границы) можно указать стиль и цвет линии, а также другие необходимые параметры.
range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
Для объединения ячеек служит метод Merge.
range.Merge(Type.Missing);
Для настройки шрифта в ячейках можно настраивать свойство Font.
range.Font.Size = 14; range.Font.Bold = true;
4. Практическое задание. Использование объектной модели Excel для экспорта/импорта данных в бизнес-приложениях
Задание. Добавим в приложение «Телефонный справочник» возможность экспорта данных в Microsoft Excel (например, список всех телефонов из справочника, сгруппированный по фамилиям).
Также, предположим, что существует некоторая старая база данных, хранящаяся в файле формата .xlsx. Поэтому реализуем в приложении «Телефонный справочник» импорт данных из Microsoft Excel (например, списка людей в таблицу Contacts или списка абонентов в таблицу Phones с проверкой на наличие в базе данных импортируемых записей).
4.1. Экспорт списка абонентов из базы данных в Microsoft Excel
Чтобы использовать объектную модель Excel, в приложение нужно добавить ссылку на COM-библиотеку Microsoft Word 14.0 Object Library (рисунок 1).
Рисунок 1 – Добавление ссылки на объектную модель Excel
В качестве шаблона, на основе которого будет формироваться список, будет использоваться файл «spisok.xlsx», внешний вид которого приведен на рисунке 2. Создайте этот файл и поместите его в папку reports в каталоге, где находится .exe-файл приложения.
Рисунок 2 – Внешний вид файла шаблона
Для работы с объектной моделью Excel следует подключить пространство имен Microsoft.Office.Interop.Excel.
Для работы с Excel нам понадобятся объекты, представляющие приложение, окно, рабочую книгу, рабочий лист Excel, а также диапазон ячеек. Объявите их в классе формы следующим образом:
//объект приложения Microsoft.Office.Interop.Excel.Application ExcelApp;
//объект окна Excel Microsoft.Office.Interop.Excel.Window ExcelWindow;
//объект рабочей книги Microsoft.Office.Interop.Excel.Workbook WorkBook;
//набор листов Excel Microsoft.Office.Interop.Excel.Sheets ExcelSheets;
//объект рабочего листа Microsoft.Office.Interop.Excel.Worksheet WorkSheet;
//диапазон ячеек Microsoft.Office.Interop.Excel.Range range;
Для создания нового отчета в Excel на основе заданного шаблона опишем метод OpenExcelDocument. Имя файла шаблона передается функции в качестве параметра. Текст метода:
private void OpenExcelDocument(string FileName)
{
//создать новый объект приложения Excel
ExcelApp = new Microsoft.Office.Interop.Excel.Application();
//задать файл шаблона
object template = System.Windows.Forms.Application.
StartupPath + @"\reports\" + FileName;
//применить шаблон ExcelApp.Workbooks.Add(template);
//получить первую рабочую книгу файла WorkBook = ExcelApp.Workbooks[1];
//получить список листов рабочей книги ExcelSheets = WorkBook.Worksheets;
//выбрать первый лист
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) ExcelSheets.get_Item(1);
}
Следующие два метода предназначены занесения данных в ячейки Excel. В качестве параметров им передаются имя ячейки и значение в формате строки, которое нужно записать в ячейку. Функция PutCellBorder дополнительно рисует тонкую рамку вокруг выбранной ячейки.
private void PutCell(string cell, string val)
{
//получить диапазон, соответствующий выбранной ячейке range = WorkSheet.get_Range(cell, Type.Missing);
//занести в ячейку значение range.Value2 = val;
}
private void PutCellBorder(string cell, string val)
{
//вызвать функцию занесения в ячейку значения PutCell(cell, val);
//нарисовать границу вокруг ячейки range.BorderAround
(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex. xlColorIndexAutomatic, Type.Missing);
}
Для запуска процесса формирования списка поместите на главную форму программы кнопку «Список в Excel». Текст ее обработчика с комментариями приведен ниже:
private void ExcelButton_Click(object sender, EventArgs e)
{
//создать документ на основе шаблона OpenExcelDocument("spisok.xlsx");
//занести текущую дату в ячейку D1 PutCell("D1", DateTime.Now.ToShortDateString());
//i - порядковый номер записи int i = 1;
//просмотреть все строки таблицы Phones foreach (DataRow row in dataSet1.Phones)
{
//занести в столбец А порядковый номер записи PutCellBorder("A" + (i + 5).ToString(),
i.ToString());
//получить строку из родительской таблицы Contacts DataRow contact = row.GetParentRow(dataSet1.Relations
["FK_Contacts_Phones"]);
//занести в столбец B фамилию абонента PutCellBorder("B" + (i + 5).ToString(),
contact["Fam"].ToString());
//занести в столбец C имя абонента
PutCellBorder("C" + (i + 5).ToString(), contact["Name"].ToString());
//занести в столбец D номер телефона PutCellBorder("D" + (i + 5).ToString(),
row["Phone"].ToString()); i++;
}
//сделать приложение Excel видимым ExcelApp.Visible = true;
}
Внешний вид сформированного программой списка будет выглядеть примерно так (рисунок 3):
Рисунок 3 – Внешний вид сформированного в Excel отчета
В данном отчете просто отображаются все номера телефонов из справочника. Модифицируем процесс его формирования так, чтобы имелась возможность их группировки по фамилиям людей (рисунок 4).
Рисунок 4 – Отчет с группировкой данных
Для реализации подобных действий обработчик нажатия кнопки нужно модифицировать следующим образом:
private void ExcelButton_Click(object sender, EventArgs e)
{
//создать документ на основе шаблона OpenExcelDocument("spisok.xlsx");
//занести текущую дату в ячейку D1 PutCell("D1", DateTime.Now.ToShortDateString());
int i = 6;
//просмотреть все строки таблицы Contacts foreach (DataRow row in dataSet1.Contacts)
{
//занести в столбец А название группы - фамилию PutCell("A" + i.ToString(), row["Fam"].ToString());
//выделить ячейки с А по D
range = WorkSheet.get_Range("A" + i.ToString(), "D" + i.ToString());
//и объединить их
range.Merge(Type.Missing);
//установить жирный шрифт range.Font.Bold = true;
//и курсив range.Font.Italic = true;
//задать цвет заливки range.Interior.ColorIndex = 34;
//нарисовать границу вокруг ячейки range.BorderAround
(Microsoft.Office.Interop.Excel.XlLineStyle. xlContinuous, Microsoft.Office.Interop.Excel. XlBorderWeight.xlThin, Microsoft.Office.Interop. Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
//установить выравнивание по центру range.HorizontalAlignment = Microsoft.Office.Interop.
Excel.XlHAlign.xlHAlignCenter;
//получить список телефонов по заданной фамилии DataRow[] phones =
row.GetChildRows(dataSet1.Relations ["FK_Contacts_Phones"]);
i++;
//num - порядковый номер телефона заданного абонента int num = 0;
//просмотреть все телефоны заданного абонента foreach (DataRow phone in phones)
{
num++;
//занести в столбец А порядковый номер записи PutCellBorder("A" + i.ToString(),
num.ToString());
//занести в столбец B фамилию абонента PutCellBorder("B" + i.ToString(),
row["Fam"].ToString());
//занести в столбец C имя абонента PutCellBorder("C" + i.ToString(),
row["Name"].ToString());
//занести в столбец D номер телефона PutCellBorder("D" + i.ToString(),
phone["Phone"].ToString());
i++;
}
//вывести количество записей в текущей группе PutCell("A" + i.ToString(), "Итого: " +
num.ToString());
//и отформатировать соответствующую ячейку
range = WorkSheet.get_Range("A" + i.ToString(), "D" + i.ToString());
range.Merge(Type.Missing); range.Font.Italic = true; range.Interior.ColorIndex = 40; range.BorderAround
(Microsoft.Office.Interop.Excel.XlLineStyle. xlContinuous, Microsoft.Office.Interop.Excel. XlBorderWeight.xlThin, Microsoft.Office.Interop.
Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing); i++;
}
//сделать приложение Excel видимым ExcelApp.Visible = true;
}
4.2. Импорт списка абонентов (в том числе с номерами телефонов) из Microsoft Excel в базу данных
Итак, рассмотрим импорт данных о людях из Microsoft Excel в таблицу базы данных Contacts с заданием проверки на возможное дублирование записей.
Подготовим файл «Контакты.xlsx» со списком абонентов (рисунок 5). Для проверки на возможное дублирование данных в импортируемом файле и таблице БД Contacts запишем в файл помимо новых людей, также строки, совпадающие с существующими уже БД записями. Например, Иванов Иван. Также в самом файле продублируем некоторых абонентов, например, Зеленская Дарья.
Рисунок 5 – Исходный файл Excel для импорта данных
Объекты, представляющие приложение, окно, рабочую книгу, рабочий лист Excel, а также диапазон ячеек были уже объявлены ранее в главном классе формы.
Для запуска процесса импорта файла поместите на главную форму программы кнопку «Импорт», а также текстовое поле для вывода полного имени импортируемого файла. Текст ее обработчика с комментариями приведен ниже:
private void button1_Click(object sender, EventArgs e)
{
//создать диалоговое окно для выбора файла для импорта OpenFileDialog ofd = new OpenFileDialog();
//задать расширение имени файла по умолчанию ofd.DefaultExt = "*.xls;*.xlsx";
//задать строку фильтра имен файлов, которая
// определяет варианты, доступные в поле
//"Файлы типа" диалогового окна
ofd.Filter = "Excel Sheet(*.xlsx)|*.xlsx";
//задать заголовок диалогового окна.
ofd.Title = "Выберите документ для загрузки данных";
//открыть приложение Эксель (создать новый объект
//приложения Эксель) ExcelApp = new
Microsoft.Office.Interop.Excel.Application();
//если выбран файл и нажата ОК, то экспортируем файл
//в БД
if (ofd.ShowDialog() == DialogResult.OK)
{
//вывести полное имя файла для импорта в текстовое поле textBox1.Text = ofd.FileName;
try
{
//если выбран файл для импорта из Excel, if (ofd.FileName != "")
{
//то загрузить выбранный файл WorkBook =
ExcelApp.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 WorkSheet =
(Worksheet)WorkBook.Sheets.get_Item(1);
//использовать свойство Worksheet.UsedRange
//для определения количества ячеек,
//содержащих текст на листе 1 range = WorkSheet.UsedRange;
//определить количество строк и столбцов с
//данными на листе 1
int rowCount = range.Rows.Count; int colCount = range.Columns.Count;
//переменная для определения совпадения
//импортируемой строки и записи в таблице БД
//0 - нет совпадений, 1 - совпадение найдено int flag = 0;
//переменная для подсчета импортированных в
//таблицу БД строк int countPlus = 0;
//перебрать заполненные строки с листа 1
//i - порядковый номер строки
for (int i = 3; i <= rowCount; i++)
{
//установить первоначально, что не совпадает
//импортируемая строка с существующей
//записью в таблице Contacts flag = 0;
//получить значение из выбранной в столбце B
//ячейки
string exfam = WorkSheet.get_Range("B" + i.ToString(), Type.Missing).Value2.ToString();
//получить значение из выбранной в столбце с ячейки
string exname = WorkSheet.get_Range("C" + i.ToString(), Type.Missing).Value2.ToString();
//просмотреть все записи таблицы Contacts
//для проверки на наличие в таблице
//импортируемой строки foreach(DataRow row in
dataSet11.Contacts)
{
//получить значение из столбца "Fam" string dsfam = row["Fam"].ToString();
//получить значение из столбца "Name" string dsname =
row["Name"].ToString();
//если значения в столбце В и "Fam"
//и столбце С и "Name" совпадают,
if (String.Compare(exfam, dsfam) ==
0 && String.Compare(exname, dsname) ==
0)
{
flag = 1; //то установить, что
//найдено совпадение break; //прекратить перебор людей
//в таблице Contacts
}
}//foreach end
//если найдено совпадение импортируемой
//строки и существующей записи в Contacts,
// то перейти к импорту следующей строки
if (flag == 1) continue;
//создать новую запись в таблице Contacts DataRow r = dataSet11.Contacts.NewRow();
//заполнить значением столбец "Fam" r["Fam"] = exfam;
//заполнить значением столбец "Name" r["Name"] = exname;
//вывести сообщение о добавлении записи MessageBox.Show("Добавлен контакт " +
exfam + " " + exname);
//увеличить счетчик импортированных в БД
//записей контактов countPlus++;
//добавить запись в таблицу Contacts dataSet11.Contacts.Rows.Add(r);
}//for end
//если ни одна строка не была импортирована, то
//вывести сообщение
if (countPlus == 0) MessageBox.Show("Нет добавленных записей в таблицу Контакты");
//сохранить изменения и обновить содержимое
//формы UpdateForm();
//сделать приложение Excel видимым ExcelApp.Visible = true;
}//if end
}//try end
catch
{
MessageBox.Show("Выберите файл");
}
}
}
Фрагмент главной формы после выполнения импорта представлен на рисунке 6. Как видно, данные из файла
«Контакты.xlsx» импортировались в таблицу БД «Телефонный справочник», при этом совпадающие строки в файле с существующими записями в таблице «Контакты» не были добавлены в таблицу. В случае добавления новой записи на экран выводилось сообщение со сведениями о новом абоненте.
Рисунок 6 – Таблица «Контакты» после выполнения импорта
Усложним задачу импорта данных в базу. Предположим, что существует список абонентов (файл «Абоненты.xlsx»), который нужно импортировать в БД (рисункок 7). Данный список содержит данные, которые относятся к двум связанным таблицам. Поскольку каждый абонент может иметь более одного телефонного номера, то необходима проверка не только на совпадение импортируемых записей с таблицей «Phones», но и на наличие уже зарегистрированного контакта. Причем сначала нужно заполнить главную таблицу «Contacts» записями из столбцов В и С, а затем уже по связи заполнить внешний ключ в таблице «Phones». Аналогично, как и в предыдущем примере следует задать проверку на дублирование записей в таблицах.
Рисунок 7 – Список абонентов для импорта в две таблицы БД
Изменнный текст обработчика кнопки «Импорт» с учетом изложенных замечаний с комментариями приведен ниже:
private void button1_Click(object sender, EventArgs e)
{
//создать диалоговое окно для выбора файла для импорта OpenFileDialog ofd = new OpenFileDialog();
//задать расширение имени файла по умолчанию ofd.DefaultExt = "*.xls;*.xlsx";
//задать строку фильтра имен файлов, которая определяет
//варианты, доступные в поле "Файлы типа" диалогового окна ofd.Filter = "Excel Sheet(*.xlsx)|*.xlsx";
//задать заголовок диалогового окна.
ofd.Title = "Выберите документ для загрузки данных";
//открыть приложение Эксель (создать новый объектприложения Эксель)
ExcelApp = new
Microsoft.Office.Interop.Excel.Application();
//если выбран файл и нажата ОК, то экспортируем файл в БД if (ofd.ShowDialog() == DialogResult.OK)
{
//вывести полное имя файла для импорта в текстовое поле textBox1.Text = ofd.FileName;
try
{
//если выбран файл для импорта из Excel, if (ofd.FileName != "")
{
//то загрузить выбранный файл WorkBook =ExcelApp.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 WorkSheet =(Worksheet)WorkBook.Sheets.get_Item(1);
//использовать свойство Worksheet.UsedRange для
//определения количества ячеек, содержащих текст на листе 1 range = WorkSheet.UsedRange;
//переменная для определения совпадения
//импортируемой строки
//и записи в таблице БД
int flag = 0; //0 - нет совпадений, 1 –
//совпадение найдено
//переменная для счета импортированных в таблицу
//БД строк
int countPlus = 0;
//перебрать заполненные строки с листа 1
//i - порядковый номер строки
for (int i = 3; i <= rowCount; i++)
{
//установить первоначально, что не совпадает
//импортируемая строка
//с существующей записью в таблице Phones flag = 0;
//получить значение из выбранной в столбце B ячейки string exfam = WorkSheet.get_Range("B" +i.ToString(), Type.Missing).Value2.ToString();
//получить значение из выбранной в столбце С ячейки string exname = WorkSheet.get_Range("C" +i.ToString(),Type.Missing).Value2.ToString();
//получить значение из выбранной в столбце D ячейки string extel = WorkSheet.get_Range("D" + i.ToString(), Type.Missing).Value2.ToString();
//переменная для хранения значения
//первичного ключа текущей записи в таблице
//Contactsstring dscontid = "";
//просмотреть все записи таблицы Phones
//для проверки на наличие в таблице
//импортируемой строки foreach (DataRow rowf indataSet11.Phones)
{
//получить значение из столбца "Phone" string dsphone =rowf["Phone"].ToString();
//получаем родительскую строку для
//доступа к полям "Фамилия" и "Имя" DataRow parRow =rowf.GetParentRow(dataSet11.Relations["FK_Contacts_Phones"]);
//получить значение из столбца "Fam" string dsfam =parRow["Fam"].ToString();
//получить значение из столбца "Name" string dsname =parRow["Name"].ToString();
//если значение импортируемой строки из
//эксель совпадает с записью в таблице
//Phones,
if (String.Compare(exfam, dsfam) ==0 && String.Compare(exname,dsname) == 0 && String.Compare(extel, dsphone) == 0)
{
flag = 1;//то установить, что
//найдено совпадение, break;//и прекратить перебор
//записей в таблице Phones
}
}//foreach end Phones
//если найдено совпадение импортируемой
//строки и существующей записи в Phones, то
//перейти к импорту следующей строки if (flag == 1) continue;
//поскольку один человек может иметь
//несколько телефонов, то
//просмотреть все записи таблицы Contacts
//для проверки: есть ли запись об указанном
//в импортируемой строке контакте? foreach (DataRow row indataSet11.Contacts)
{
//получить значение из столбца "Fam" string dsfamc = row["Fam"].ToString();
//получить значение из столбца "Name" string dsnamec =row["Name"].ToString();
//если значения в столбце В и "Fam" и
//столбце С и "Name" совпадают,
if (String.Compare(exfam, dsfamc) == 0 && String.Compare(exname, dsnamec) == 0)
{
flag = 2;//то установить, что
//найдено совпадение - контакт уже зарегистрирован,
//и получить значение поля Id
//указанного зарегистрированного контакта dscontid = row["Id"].ToString(); break;//прекратить перебор людей в
//таблице Contacts
}
}//foreach end Contacts
//если импортируемый абонент еще не
//зарегистрирован в таблице Contacts if (flag == 0)
{
//, то создать новую запись таблицы
//Contacts DataRow r =dataSet11.Contacts.NewRow();
//заполнить значением столбец "Fam" r["Fam"] = exfam;
//заполнить значением столбец "Name" r["Name"] = exname;
//вывести сообщение о добавлении записи MessageBox.Show("Добавлен контакт "+ exfam + " " + exname);
//добавить запись в таблицу Contacts dataSet11.Contacts.Rows.Add(r);
//и получить значение поля Id нового
//добавленного контакта dscontid = r["Id"].ToString();
}
//создать новую запись таблицы Phones DataRow ro = dataSet11.Phones.NewRow();
//заполнить значением столбец "ContactId" ro["ContactId"] = dscontid;
//заполнить значением столбец "Phone" ro["Phone"] = extel;
//вывести сообщение о добавлении записи MessageBox.Show("Добавлена запись вPhones: " + exfam + " " + exname + " " + extel);
//увеличить счетчик импортированных в
//таблицу Phones сведений об абонентах и их
// телефонах countPlus++;
//добавить запись в таблицу Phones dataSet11.Phones.Rows.Add(ro);
}//for end
//если ни одна строка не была импортирована в
//Phones, то вывести сообщение
if (countPlus == 0) MessageBox.Show("Нет добавленных записей в таблицу Phones");
//сохранить изменения и обновить содержимое
// формы UpdateForm();
//сделать приложение Excel видимым ExcelApp.Visible = true;
}//if end
}//try end catch
{
MessageBox.Show("Выберите файл");
}
}
}
