Модификация приложения Windows Forms : прочие возможности Microsoft Excel
Богатства возможностей по управлению запущенным приложением потрясающе. Практически, всё, что можно сделать в автономно запущенном приложении доступно и из приложения на C#. Программист может достаточно легко выполнить то или иное действие из приложения, если создаст макрос для этого действия, а, затем, «переведёт» код VBA в коды C#.
Для нашего приложения, третья группа элементов продемонстрирует широкие возможности по работе в Excel и документами.
Объекты, которыми оперирует COM-сервер Excel, несколько десятков. Будем рассматривать лишь основные, которые непосредственно требуются для обмена информацией приложения и сервера. Все объекты имеют иерархическую структуру. Сам сервер — объект Application или приложение Excel, может содержать одну или более книг, ссылки на которые содержит свойство Workbooks. Книги — объекты Workbook, могут содержать одну или более страниц, ссылки на которые содержит свойство Worksheets или (и) диаграмм — свойство Charts. Страницы — Worksheet, содержать объекты ячейки или группы ячеек, ссылки на которые становятся доступными через объект Range. Ниже в иерархии: строки, столбцы... Аналогично и для объекта Chart серии линий, легенды...
Обратим внимание на то, что, интерфейс C# вместо понятия ячейки использует объекты Range (выбранная ячейка или группа ячеек). Отметим также группу объектов ActiveCell, ActiveChart и ActiveSheet, относящихся к активному окну (расположенному поверх других). Они, по набору свойств и методов, полностью аналогичны объектам Range, Chart и Sheet и, в ряде случаев, просто облегчают получение ссылки.
|
|
|
Немного обособленно от этой иерархической структуры объектов находится свойство Windows объекта Excel.Application, предназначенное для управления окнами сервера Excel. Свойство Windows содержит набор объектов Window, которые имеют, в свою очередь, набор свойств и методов для управления размерами, видом, масштабом и упорядочиванием открытых окон, отображением заголовков, цветами и прочее. Эти же возможности доступны и для свойств и методов объекта Excel.Application - ActiveWindow (ссылка на активное окно).
Все эти объекты принято определять глобально для того, чтобы обеспечить доступ к ним из любой функции проекта.
Вторым в иерархии объектов Excel.Application является объект Workbook. Информация об объектах Workbook хранится в виде ссылок на открытые рабочие книги в свойстве Workbooks. Книга в приложение может быть добавлена только через добавление ссылки в совокупность Workbooks, а ссылка на открытую книгу может быть получена различным образом (по имени, номеру, как ссылка на активную книгу).
Начнём с создания рабочих книг:
Создание двух рабочих книг из 3-х и 5-ти листов (на примере нашего приложения):
|
|
|
Excel.Application ObjExcel;
public LWP13Main()
{
InitializeComponent();
ObjExcel = new Excel.Application();
ObjExcel.Visible = true;
ObjExcel.SheetsInNewWorkbook = 3;
ObjExcel.Workbooks.Add(Type.Missing);
ObjExcel.SheetsInNewWorkbook = 5;
ObjExcel.Workbooks.Add(Type.Missing);
}
Свойство SheetsInNewWorkbook возвращает или устанавливает количество листов, автоматически помещаемых Excel в новые рабочие книги.
В качестве параметра методу Add можно передать имя шаблона рабочей книги, однако, в этом случае мы привязываемся к пути, по которому инсталлированы приложения Microsoft Office. В примере использован другой способ: Type — класс декларации типов, Type.Missing — отсутствие значения. Некоторые методы Excel принимают необязательные параметры, которые не поддерживаются в C#. Для решения этой проблемы в коде на C# требуется передавать поле Type.Missing вместо каждого необязательного параметра, который является ссылочным типом (reference type). Кроме того, (этого нет в документации) при задании в методе ADD чисел от 1 до 7 будет создана книга с одним листом (1, 6), диаграмма (2), макрос (3, 4) и книга с четырьмя листами (5).
Из других свойств отметим свойство TemplatesPath. С его помощью, зная имя файла шаблона, можно напрямую задавать имя шаблона (правда, в этом нет необходимости, если мы не хотим использовать, например, свой собственный шаблон). Свойство StartupPath возвращает путь к папке, которая содержит надстройки, выполняемые при запуске Excel и, хотя свойство для отображения информации нам ничего не дает, все же порой бывает необходимо найти имя файла настроек и удалить его для того, чтобы приложение работало только с собственными настройками.
|
|
|
Книги могут быть не только добавлены, но и закрыты. Следующие вызовы закрывают все или конкретную рабочую книгу:
ObjExcel.Workbooks.Close();
ObjExcel.Windows[1].Close(false, Type.Missing, Type.Missing);
Подробнее:
ObjExcel.Windows[1].Close(
SaveChanges, // Если в книге нет никаких изменений в документе, то параметр игнорируется.
// Иначе, если есть изменения, но есть ссылки на закрываемую книгу
// в других открытых окнах - этот параметр также игнорируется.
// При отсутствии ссылок и наличии изменений - этот параметр
// определяет, должны ли быть сохранены изменения.
// При true и определенном параметре Filename - изменения
// сохраняются, иначе запрашиваетcя имя файла. При false сохранения
|
|
|
// нет. Если Type.Missing - вызывается диалоговое окно Save As
Filename, // Имя файла
RouteWorkbook // Если файл не должен быть перенаправлен другому получателю
// этот параметр игнорируется. Иначе при true файл направляется
// следующему получателю. При false пересылки нет
);
Теперь о сохранении документов:
Документы Excel можно сохранить программно и обычным для Excel способом. В любом случае перед выходом из Excel необходимо вызвать метод Quit. Если свойство Excel.Application.DisplayAlerts имеет значение true, Excel предложит сохранить несохранённые данные, если после старта в документ были внесены какие либо изменения. Excel автоматически не возвращает это свойство в значение по умолчанию, поэтому его рекомендуется возвращать в исходное состояние.
Excel.Workbook ObjWorkBook;
Excel.Workbooks ObjWorkBooks;
public LWP13Main()
{
...
// Запрашивать сохранение
ObjExcel.DisplayAlerts = true;
// Получаем набор ссылок на объекты Workbook (на созданные книги)
ObjWorkBooks = ObjExcel.Workbooks;
// Получаем ссылку на книгу 1 - нумерация от 1
ObjWorkBook = ObjWorkBooks[1];
// Ссылку можно получить и так, но тогда надо знать имена книг,
// причём, после сохранения - знать расширение файла
// ObjWorkBook = ObjWorkBooks["Book 1"];
// Запроса на сохранение для книги не должно быть
ObjWorkBook.Saved = true;
// Используем свойство Count, число Workbook в Workbooks
if (ObjWorkBooks.Count > 1)
{
ObjWorkBook = ObjWorkBooks[2];
// Запрос на сохранение книги 2 должен быть
ObjWorkBook.Saved = false;
}
}
Теперь, если выйти на конкретную книгу, как показано в примере, приведенном выше, и присвоить свойству Saved объекта Workbook значение true, Excel согласно документации не должен предлагать сохранение независимо от того, были или нет изменения в данной книге.
Для получения формата открываемого документа и задания формата сохраняемого служит свойство Excel.Application.DefaultSaveFormat. Свойство имеет много значений типа XlFileFormat (какие могут быть легко, посмотреть в диалоговом окне «Сохранение документа» в поле «Тип файла», открыв Excel и выбрав пункт меню «Файл» -> «Сохранить как»).
Например:
ObjExcel.DefaultSaveFormat = Excel.XlFileFormat.xlHtml;
В окне диалога сохранения файла будет установлен тип файла «Веб-страница».
Для сохранения документов можно использовать методы Excel.Workbook.Save и SaveAs. Метод Save сохраняет рабочую книгу в папке по умолчанию (выбирается в настройках Excel: «Файл» ->» Параметры «-> «Сохранение» -> «Расположение файлов по умолчанию») с именами, присваиваемыми документу по умолчанию («Книга1.xls», «Книга2.xls» ...) или в директорию и с именем под которым документ уже был сохранён.
Пример сохранения «по умолчанию»:
// Устанавливаем формат
ObjExcel.DefaultSaveFormat = Excel.XlFileFormat.xlExcel9795;
// Будем спрашивать разрешение на запись поверх существующего документа
ObjExcel.DisplayAlerts = true;
ObjWorkBook = ObjWorkBooks[1];
// Сохраняем книгу 1
ObjWorkBook.Save();
ObjWorkBook = ObjWorkBooks[2];
// Сохраняем книгу 2
ObjWorkBook.Save();
При значении свойства DisplayAlerts = true Excel будет спрашивать: записать ли сохраняемый документ поверх существующего, при значении false: не будет спрашивать.
Метод SaveAs позволяет сохранить документ с указанием имени, формата файла, пароля, режим доступа и прочее. Данный метод, как и метод Save, присваивает свойству Saved значение true.
ObjWorkBook.SaveAs(
Filename, // Имя сохраняемого файла
FileFormat, // Формат сохраняемого файла
Password, // Пароль доступа к файлу до 15 символов
WriteResPassword, // Пароль на доступ на запись
ReadOnlyRecommended, // При true режим только для чтения
CreateBackup, // Создать резервную копию файла при true
AccessMode, // Режим доступа к рабочей книге
ConflictResolution, // Способ разрешения конфликтов
AddToMru, // При true сохраненный документ добавляется
// в список ранее открытых файлов
TextCodePage, // Кодовая страница
TextVisualLayout, // Направление размещения текста
Local // Идентификатор Excel.Application
);
Для доступа к книге используются значение AccessMode xlShared — общая рабочая книга, xlExclusive — монопольный доступ или xlNoChange — запрет изменения режима доступа.
Параметр ConflictResolution — способ разрешения конфликтов при одновременном внесении несколькими пользователями изменений в один документ - может иметь значения: xlUserResolution — отображение диалогового окна разрешения конфликтов (параметр по умолчанию), xlLocalSessionChanges — принятие изменений, внесенных пользователем или xlOtherSessionChanges — принятие изменений, внесенных другими пользователями. О применении SaveAs будет написано в рабочем коде приложения ниже.
Также, для сохранения документа может быть использован метод SaveCopyAs, который сохраняет копию рабочей книги в файле.
Метод Save Copy As не производит преобразование документа и, поэтому, например вместо Веб-страницы Book1.html сохранит копию *.xls x (Excel 2007 и выше) документа (изменит только расширение).
И наконец, об открытии документов:
Для открытия существующего документа основным методом является метод Open набора Excel.Workbooks. Для открытия текстовых файлов как рабочих книг, баз данных, файлов в формате *. xml, используются методы OpenText, OpenDatabase или OpenXml.
ObjExcel.Workbooks.Open(@"C:\Документ.html",
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);
ObjExcel.Workbooks.Open(@"C:\Документ.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);
Параметров как видим очень много. Метод Open и его свойства таковы:
ObjWorkBooks.Open(
FileName, // Имя открываемого файла
UpdateLinks, // Способ обновления ссылок в файле
ReadOnly, // При значении true открытие только для чтения
Format, // Определение формата символа разделителя
Password, // Пароль доступа к файлу до 15 символов
WriteResPassword, // Пароль на сохранение файла
IgnoreReadOnlyRecommended, // При значении true отключается вывод
// запроса на работу без внесения изменений
Origin, // Тип текстового файла
Delimiter, // Разделитель при Format = 6
Editable, // Используется только для надстроек Excel 4.0
Notify, // При значении true имя файла добавляется в
// список нотификации файлов
Converter, // Используется для передачи индекса конвертера файла
// используемого для открытия файла
AddToMRU, // При true имя файла добавляется в список
// открытых файлов
Local, // Языковые параметры загрузки (true или false)
CorruptLoad // Режим загрузки файла (всего 3 возможных)
);
UpdateLinks — позволяет задать способ обновления ссылок в файле. Если данный параметр не задан, то выдается запрос на указание метода обновления. Значения: 0 — не обновлять ссылки; 1 — обновлять внешние ссылки; 2 — обновлять только удаленные ссылки; 3 — обновлять все ссылки.
Format — при работе с текстовыми файлами определяет символ разделителя для полей, заносимых в различные ячейки документа. Значения параметра: 1 — символ табуляции; 2 — запятая; 3 — пробел; 4 - точка с запятой; 5 — нет разделителя; 6 — другой символ, определённый в параметре Delimiter.
Теперь перейдём к нашему приложению. Для начала подготовим третий блок элементов управления на форме.
Рис. 5. 1. Модифицированная форма приложения и расстановка третьей группы элементов
Здесь 7 кнопок и один GroupBox.
GroupBox:
| (Name): | GB_Other |
| Text: | Прочие возможности работы с Excel |
Button:
| (Name): | B_Create |
| Text: | Сформировать готовый документ с графиком |
| Size: | 312; 23 |
Button:
| (Name): | B_Other |
| Text: | Другие листы |
| Size: | 100; 23 |
Button:
| (Name): | B_Action |
| Text: | Выполнить различные действия |
| Size: | 206; 23 |
Button:
| (Name): | B_Unfreeze |
| Text: | Разморозить |
| Size: | 100; 23 |
Button:
| (Name): | B_Merge |
| Text: | Объединение |
| Size: | 100; 23 |
Button:
| (Name): | B_Exit |
| Text: | Выгрузить все открытые приложением экземпляры Excel |
| Size: | 312; 23 |
Button:
| (Name): | B_AutoFill |
| Text: | Автозаполнение |
| Size: | 100; 23 |
Начнём с самой первой кнопки (B _ Create). Нажатие кнопки вызывает окно Excel, далее создаётся книга с тремя листами, в ней формируются данные о продажах для некоей фирмы, после чего приложение через Excel спрашивает, для скольких кварталов за год вывести данные о продажах. После выбора числа кварталов (от четырёх до одного), Excel строит график (на основе случайных данных для каждого сотрудника фирмы), перемещает этот график на основной (первый) лист и сохраняет документ под двумя паролями в директории с приложением (Новый документ. xslx). Два пароля: первый пароль на доступ к документу («123»), второй на изменение данных («321»).
Для работы кода нам потребуется следующее. Найдём в LWP 13 Main . cs строчки:
public partial class LWP13Main : Form
{
Excel.Application ObjExcel1;
Excel.Workbook ObjWorkBook1;
Excel.Worksheet ObjWorkSheet1;
Добавим после (с заделом на будущую функциональность):
//
Excel.Application ObjExcel3;
//
Excel.Application ObjExcel4;
Excel.Workbook ObjWorkBook4;
Excel.Workbooks ObjWorkBooks4;
Excel.Worksheet ObjWorkSheet4;
Excel.Sheets ObjSheets4;
Excel.Range ObjRange4;
//
Excel.Application ObjExcel5;
Excel.Workbook ObjWorkBook5;
Excel.Workbooks ObjWorkBooks5;
Excel.Worksheet ObjWorkSheet5;
Excel.Sheets ObjSheets5;
Excel.Range ObjRange5;
//
Excel.Application ObjExcel6;
Excel.Workbook ObjWorkBook6;
Excel.Workbooks ObjWorkBooks6;
Excel.Worksheet ObjWorkSheet6;
Excel.Sheets ObjSheets6;
Excel.Range ObjRange6;
//
Excel.Application ObjExcel7;
Excel.Workbook ObjWorkBook7;
Excel.Workbooks ObjWorkBooks7;
Excel.Worksheet ObjWorkSheet7;
Excel.Sheets ObjSheets7;
Excel.Range ObjRange7;
Код достаточно хорошо прокомментирован, событие Click нажатия кнопки B _ Create таково:
private void B_Create_Click(object sender, EventArgs e)
{
B_Create.Enabled = false;
B_Other.Enabled = true;
B_Merge.Enabled = true;
Excel._Workbook ObjWorkBook3;
Excel._Worksheet ObjWorkSheet3;
Excel.Range ObjRange3;
try
{
ObjExcel3 = new Excel.Application();
ObjExcel3.Visible = true;
// Задаём число листов в новом документе (Книге)
ObjExcel3.SheetsInNewWorkbook = 3;
ObjWorkBook3 = (Excel._Workbook)(ObjExcel3.Workbooks.Add(Type.Missing));
ObjWorkSheet3 = (Excel._Worksheet)ObjWorkBook3.ActiveSheet;
// Заполняем заголовочные ячейки
ObjWorkSheet3.Cells[1, 1] = "Имя";
ObjWorkSheet3.Cells[1, 2] = "Фамилия";
ObjWorkSheet3.Cells[1, 3] = "Полное имя";
ObjWorkSheet3.Cells[1, 4] = "Продажи";
// Форматируем A1:D1 как "жирный", вертикальное положение: по центру
ObjWorkSheet3.get_Range("A1", "D1").Font.Bold = true;
ObjWorkSheet3.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// Создаём массив для заполнения ячеек данными (имена и фамилии)
string[,] saNames = new string[5, 2];
saNames[0, 0] = "Иван";
saNames[0, 1] = "Иванов";
saNames[1, 0] = "Антон";
saNames[1, 1] = "Антонов";
saNames[2, 0] = "Пётр";
saNames[2, 1] = "Петров";
saNames[3, 0] = "Андрей";
saNames[3, 1] = "Андреев";
saNames[4, 0] = "Кейв";
saNames[4, 1] = "Джонсон";
// Заполняем A2:B6 из массива ("Имя" и "Фамилия").
ObjWorkSheet3.get_Range("A2", "B6").Value2 = saNames;
// Заполняем C2:C6 по формуле (=A2 & " " & B2).
ObjRange3 = ObjWorkSheet3.get_Range("C2", "C6");
ObjRange3.Formula = "=A2 & \" \" & B2";
// Заполняем D2:D6 по формлуе (=RAND()*100000) и применяем формат
ObjRange3 = ObjWorkSheet3.get_Range("D2", "D6");
ObjRange3.Formula = "=RAND()*1000";
ObjRange3.NumberFormat = "0.00р";
// Автозаполнение A:D.
ObjRange3 = ObjWorkSheet3.get_Range("A1", "D1");
ObjRange3.EntireColumn.AutoFit();
// Манипулируем с переменным числом столбцов для квартальных
// данных продаж (вызываем метод DisplayQuarterlySales).
// Для построение графика будет использован ChartWizard
DisplayQuarterlySales(ObjWorkSheet3);
// Делаем Excel видимым и передаём управления пользователю
ObjExcel3.Visible = true;
ObjExcel3.UserControl = true;
}
catch (Exception ex)
{
String errorMessage;
errorMessage = "Ошибка: ";
errorMessage = String.Concat(errorMessage, ex.Message);
errorMessage = String.Concat(errorMessage, "\nЛиния: ");
errorMessage = String.Concat(errorMessage, ex.Source);
MessageBox.Show(errorMessage, "Автоматизация Microsoft Office Excel (C#) :: Ошибка построения диаграммы");
}
ObjExcel3.Quit();
}
private void DisplayQuarterlySales(Excel._Worksheet ObjWorkSheet3)
{
Excel._Workbook ObjWorkBook3;
Excel.Series ObjSeries3;
Excel.Range ObjResizeRange3;
Excel._Chart ObjChart3;
String sMsg;
int iNumQtrs = 4;
// Определяем, сколько кварталов частей для отображения данных
for (iNumQtrs = 4; iNumQtrs >= 2; iNumQtrs--)
{
sMsg = "Ввод данных с продаж за ";
sMsg = String.Concat(sMsg, iNumQtrs);
sMsg = String.Concat(sMsg, " квартала(-л)?");
DialogResult iRet = MessageBox.Show(sMsg, "Сколько кварталов учитывать?", MessageBoxButtons.YesNo);
if (iRet == DialogResult.Yes) break;
}
sMsg = "Вывод данных за ";
sMsg = String.Concat(sMsg, iNumQtrs);
sMsg = String.Concat(sMsg, " квартала(-л)");
MessageBox.Show(sMsg, "Данные о продажах за кварталы");
// Начиная с E1, заполнить заголовки (и далее по количество столбцов выбранных столбцов)
ObjResizeRange3 = ObjWorkSheet3.get_Range("E1", "E1").get_Resize(Missing.Value, iNumQtrs);
ObjResizeRange3.Formula = "=\"К\" & COLUMN()-4 & CHAR(10) & \"(квартал)\"";
// Меняем ориентацию текста (38) и параметр поворота текста (true)
ObjResizeRange3.Orientation = 38;
ObjResizeRange3.WrapText = true;
// Заполняем заголовки (верхнюю ячейку столбца) начиная с E1 жёлтым цветом
ObjResizeRange3.Interior.ColorIndex = 36;
// Заполняем E2:E6 формулами (случайное число до 100) и меняем формат отображения (рубли)
ObjResizeRange3 = ObjWorkSheet3.get_Range("E2", "E6").get_Resize(Missing.Value, iNumQtrs);
ObjResizeRange3.Formula = "=RAND()*100";
ObjResizeRange3.NumberFormat = "0.00р";
// Применяем к E1:E6 чёрные границы ячеек
ObjResizeRange3 = ObjWorkSheet3.get_Range("E1", "E6").get_Resize(Missing.Value, iNumQtrs);
ObjResizeRange3.Borders.Weight = Excel.XlBorderWeight.xlThin;
// Для всех ячеек ниже E1 формируем сумму, под числом рисуем двойную черту
ObjResizeRange3 = ObjWorkSheet3.get_Range("E8", "E8").get_Resize(Missing.Value, iNumQtrs);
ObjResizeRange3.Formula = "=SUM(E2:E6)";
ObjResizeRange3.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlDouble;
ObjResizeRange3.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThick;
// Создаём график для выбранных данных
ObjWorkBook3 = (Excel._Workbook)ObjWorkSheet3.Parent;
ObjChart3 = (Excel._Chart)ObjWorkBook3.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// Используем "Мастер диаграмм" чтобы создать новый график на основе выбранных данных
ObjResizeRange3 = ObjWorkSheet3.get_Range("E2:E6", Missing.Value).get_Resize(Missing.Value, iNumQtrs);
// Делаем график объёмной
ObjChart3.ChartWizard(Missing.Value, Excel.XlChartType.xl3DColumn, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// Меняем подписи срава (по вертикали) на "Полное имя"
ObjSeries3 = (Excel.Series)ObjChart3.SeriesCollection(1);
// В цикле пробегаем по каждому элементу подписи по вертикали справа
for (int iRet = 1; iRet <= 5; iRet++)
{
ObjSeries3 = (Excel.Series)ObjChart3.SeriesCollection(iRet);
ObjSeries3.Name = ObjWorkSheet3.get_Range("C" + (iRet + 1), "C" + (iRet + 1)).Value2;
}
ObjChart3.Location(Excel.XlChartLocation.xlLocationAsObject, ObjWorkSheet3.Name);
// Перемещаем график так, чтобы он не закрывал покрыть данные
ObjResizeRange3 = (Excel.Range)ObjWorkSheet3.Rows.get_Item(10, Missing.Value);
ObjWorkSheet3.Shapes.Item("Chart 1").Top = (float)(double)ObjResizeRange3.Top;
ObjResizeRange3 = (Excel.Range)ObjWorkSheet3.Columns.get_Item(2, Missing.Value);
ObjWorkSheet3.Shapes.Item("Chart 1").Left = (float)(double)ObjResizeRange3.Left;
// Сохраняем документ и устанавливаем статус документа "сохранён"
ObjWorkBook3.Saved = true;
// Не будем спрашивать разрешение на запись поверх существующего документа
ObjExcel3.DisplayAlerts = false;
// Сохраняем документ в папке с приложением
ObjWorkBook3.SaveAs(
Environment.CurrentDirectory + "\\Новый документ", // Имя файла (object FileFormat)
ObjExcel3.DefaultSaveFormat, // Формат файла
"123", // Пароль (object Password)
"321", // Повтор пароля (object WriteResPassword)
Type.Missing, // (object ReadOnlyRecommended)
Type.Missing, // (object object CreateBackup)
Excel.XlSaveAsAccessMode.xlNoChange,// (object XlSaveAsAccessMode AccessMode)
Type.Missing, // (object ConflictResolution)
Type.Missing, // (object AddToMru)
Type.Missing, // (object TextCodepage)
Type.Missing, // (object TextVisualLayout)
Type.Missing); // (object Local)
System.Threading.Thread.Sleep(1000);
}
После нажатия кнопки и совершения действия в Excel, код принудительно закрывает Excel:
ObjExcel3.Quit();
Это сделано с целью недопущения «размножения» процессов Excel в памяти ПК. Так как наше приложение в процессе работы будет создавать несколько самостоятельных объектов-приложений Excel (для каждого действия своя копия). Если закрыть окно Excel вручную, связь с объектом из приложения для окна будет утеряна, а процесс останется в памяти даже после закрытия формы нашего приложения. Если такое всё-таки случилось, можно воспользоваться следующим кодом (снять комментарии с кода):
private void LWP13Main_FormClosing(object sender, FormClosingEventArgs e)
{
// Выгружает все (!) процессы EXCEL.EXE из памяти
//foreach (System.Diagnostics.Process currentProcess in System.Diagnostics.Process.GetProcessesByName("EXCEL.EXE"))currentProcess.Kill();
}
Это событие «закрытия формы» возникающее во время завершения работы формы, до полного высвобождения ресурсов. Код события выгружает все процессы EXCEL . EXE из памяти. Вернее «убивает».
Событие Click кнопки B _ Exit:
private void B_Exit_Click(object sender, EventArgs e)
{
// Выходим из всех открытых приложением окон Excel
try
{
B_Open.Enabled = true;
ObjExcel1.Quit();
}
catch { }
try
{
B_Create.Enabled = true;
B_Other.Enabled = false;
B_Merge.Enabled = false;
ObjExcel3.Quit();
}
catch { }
try
{
ObjExcel4.Quit();
}
catch { }
try
{
ObjExcel5.Quit();
}
catch { }
try
{
B_Unfreeze.Enabled = false;
ObjExcel6.Quit();
}
catch { }
try
{
B_AutoFill.Enabled = true;
ObjExcel7.Quit();
}
catch { }
}
Кнопка B _ Other делают следующее. Открывает ранее сохранённый документ («Новый документ»), вносит в ячейку А7 первого листа значение «10,5», в ячейку А1 второго листа слово «Лист 2» (форматированное), и заполняет третий лист. Затем сохраняет всё и закрывает документ и Excel. Код события Click:
private void B_Other_Click(object sender, EventArgs e)
{
B_Other.Enabled = false;
int n, m;
ObjExcel4 = new Excel.Application();
ObjExcel4.Visible = true;
// Получаем набор ссылок на объекты Workbook
ObjWorkBooks4 = ObjExcel4.Workbooks;
// Открываем книгу и получаем на нее ссылку
ObjWorkBook4 = ObjExcel4.Workbooks.Open(Environment.CurrentDirectory + "\\Новый документ", Type.Missing, Type.Missing, Type.Missing, "123", "321", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Если бы открыли несколько книг, то получили ссылку так
// ObjWorkBook4 = ObjWorkBooks4[1];
// Получаем массив ссылок на листы выбранной книги
ObjSheets4 = ObjWorkBook4.Worksheets;
// Получаем ссылку на лист 1
ObjWorkSheet4 = (Excel.Worksheet)ObjSheets4.get_Item(1);
// Выбираем ячейку для вывода A7
ObjRange4 = ObjWorkSheet4.get_Range("A7", "A7");
// Выводим число
ObjRange4.Value2 = 10.5;
// Выбираем лист 2
ObjWorkSheet4 = (Excel.Worksheet)ObjSheets4.get_Item(2);
// При выборе одной ячейки можно не указывать вторую границу
ObjRange4 = ObjWorkSheet4.get_Range("A1", Type.Missing);
// Выводим значение текстовую строку
ObjRange4.Value2 = "Лист 2";
ObjRange4.Font.Size = 20;
ObjRange4.Font.Italic = true;
ObjRange4.Font.Bold = true;
// Выбираем лист 3
ObjWorkSheet4 = (Excel.Worksheet)ObjSheets4.get_Item(3);
// Делаем третий лист активным
ObjWorkSheet4.Activate();
// Вывод в ячейки используя номер строки и столбца Cells[строка, столбец]
for(m = 1; m < 20; m++)
{
for(n = 1; n < 15; n++)
{
ObjRange4 = (Excel.Range)ObjWorkSheet4.Cells[m, n];
// Выводим координаты ячеек
ObjRange4.Value2 = m.ToString() + " " + n.ToString();
}
}
// Сохраняем результат
ObjWorkBooks4 = ObjExcel4.Workbooks;
ObjWorkBook4 = ObjWorkBooks4[1];
ObjWorkBook4.Save();
System.Threading.Thread.Sleep(1000);
ObjExcel4.Quit();
}
Кнопка B _ Merge также работает с тем же самым документом. Нажатие кнопки: загрузка документа, объединение ячеек второго листа, заполнение тёмно-красным цветом и изменение границы объединённой ячейки (красный цвет, толщина), затем сохранение и выход из Excel.
private void B_Merge_Click(object sender, EventArgs e)
{
B_Merge.Enabled = false;
ObjExcel7 = new Excel.Application();
ObjExcel7.Visible = true;
// Получаем набор ссылок на объекты Workbook
ObjWorkBooks7 = ObjExcel7.Workbooks;
// Открываем книгу и получаем на нее ссылку
ObjWorkBook7 = ObjExcel7.Workbooks.Open(Environment.CurrentDirectory + "\\Новый документ", Type.Missing, Type.Missing, Type.Missing, "123", "321", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Если бы открыли несколько книг, то получили ссылку так
// ObjWorkBook7 = ObjWorkBooks7[1];
// Получаем массив ссылок на листы выбранной книги
ObjSheets7 = ObjWorkBook7.Worksheets;
// Получаем ссылку на лист 2
ObjWorkSheet7 = (Excel.Worksheet)ObjSheets7.get_Item(2);
// Делаем первый лист активным (другим способом)
//ObjWorkSheet7.Activate();
iMySheetActivate(2);
ObjRange7 = ObjWorkSheet7.get_Range("B2", "D7");
// Объединяем ячейки
ObjRange7.Merge(Type.Missing);
// Устанавливаем цвет обводки
ObjRange7.Borders.ColorIndex = 3;
// Устанавливаем стиль и толщину линии
ObjRange7.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
ObjRange7.Borders.Weight = Excel.XlBorderWeight.xlThick;
// Сделаем заливку
ObjRange7.Interior.ColorIndex = 30;
ObjRange7.Interior.PatternColorIndex = Excel.Constants.xlAutomatic;
// Сохраняем результат
ObjWorkBooks7 = ObjExcel7.Workbooks;
ObjWorkBook7 = ObjWorkBooks7[1];
ObjWorkBook7.Save();
System.Threading.Thread.Sleep(1000);
ObjExcel7.Quit();
}
private int iMySheetActivate(int viNumSheet)
{
try
{
Excel.Worksheet sheet = (Excel.Worksheet)ObjWorkBook7.Sheets[viNumSheet];
Excel.DocEvents_Event sheetEvents = (Excel.DocEvents_Event)sheet;
Excel._Worksheet _sheet = (Excel._Worksheet)sheet;
sheetEvents.Activate += new Excel.DocEvents_ActivateEventHandler(sheetEvents_Activate);
_sheet.Activate();
}
catch (Exception)
{
return 1;
}
return 0;
}
public void sheetEvents_Activate()
{
// Пусто!
}
Зачем использовать метод iMySheetActive вместо:
//ObjWorkSheet7.Activate();
Сняв комментарии со строчки получим предупреждение:
«Неоднозначность между методом
"Microsoft.Office.Interop.Excel._Worksheet.Activate()" и
"Microsoft.Office.Interop.Excel.DocEvents_Event.Activate", который методом не является. Используйте группу методов.»
Эта двусмысленность в использовании одноимённых свойства и метода объявленных в интерфейсе _Worksheet и интерфейсе DocEvents. Оба эти интерфейса наследует класс Worksheet. И, хотя использование метода Activate не приводит к двусмысленности в выполнении кода, для тех, кто привык писать «чистый код» этот «глюк» лучше устранить. Устранение можно выполнить через события Excel.
Работа кнопки B _ AutoFill: загружается заранее подготовленный документ («Пример автозапонения») и на основе данных из документа производит автоматическое заполнение ячеек, затем сохраняет как новый документ: Автозаполнение. xlsx и закрывает Excel. Код события Click кнопки B_AutoFill:
private void B_AutoFill_Click(object sender, EventArgs e)
{
B_AutoFill.Enabled = false;
ObjExcel5 = new Excel.Application();
ObjExcel5.Visible = true;
ObjExcel5.UserControl = true;
ObjWorkBooks5 = ObjExcel5.Workbooks;
ObjWorkBook5 = ObjExcel5.Workbooks.Open(Environment.CurrentDirectory + "\\Пример автозаполнения", Type.Missing, Type.Missing, Type.Missing, "123", "321", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ObjSheets5 = ObjWorkBook5.Worksheets;
ObjWorkSheet5 = (Excel.Worksheet)ObjSheets5.get_Item(1);
// Делаем первый лист активным
ObjWorkSheet5.Activate();
ObjRange5 = ObjWorkSheet5.get_Range("B1", "B1");
ObjRange5.Value2 = "1";
ObjRange5 = ObjWorkSheet5.get_Range("B2", "B2");
ObjRange5.Value2 = "3";
Excel.Range ObjRange51 = ObjExcel5.get_Range("B1:B2", Type.Missing);
Excel.Range ObjRange52 = ObjExcel5.get_Range("B1:B15", Type.Missing);
ObjRange51.AutoFill(ObjRange52, Excel.XlAutoFillType.xlFillSeries);
ObjRange51 = ObjExcel5.get_Range("C1", Type.Missing);
ObjRange52 = ObjExcel5.get_Range("C1:C15", Type.Missing);
ObjRange51.AutoFill(ObjRange52, Excel.XlAutoFillType.xlFillYears);
ObjRange51 = ObjExcel5.get_Range("D1", Type.Missing);
ObjRange52 = ObjExcel5.get_Range("D1:D15", Type.Missing);
ObjRange51.AutoFill(ObjRange52, Excel.XlAutoFillType.xlFillMonths);
ObjRange51 = ObjExcel5.get_Range("E1", Type.Missing);
ObjRange52 = ObjExcel5.get_Range("E1:E15", Type.Missing);
ObjRange51.AutoFill(ObjRange52, Excel.XlAutoFillType.xlFillDefault);
ObjRange51 = ObjExcel5.get_Range("F1", Type.Missing);
ObjRange52 = ObjExcel5.get_Range("F1:F15", Type.Missing);
ObjRange51.AutoFill(ObjRange52, Excel.XlAutoFillType.xlFillWeekdays);
ObjRange51 = ObjExcel5.get_Range("G1", Type.Missing);
ObjRange52 = ObjExcel5.get_Range("G1:G15", Type.Missing);
ObjRange51.AutoFill(ObjRange52, Excel.XlAutoFillType.xlFillDays);
ObjRange51 = ObjExcel5.get_Range("H1", Type.Missing);
ObjRange52 = ObjExcel5.get_Range("H1:H15", Type.Missing);
ObjRange51.AutoFill(ObjRange52, Excel.XlAutoFillType.xlFillDays);
ObjRange51 = ObjExcel5.get_Range("I1:I2", Type.Missing);
ObjRange52 = ObjExcel5.get_Range("I1:I15", Type.Missing);
ObjRange51.AutoFill(ObjRange52, Excel.XlAutoFillType.xlFillSeries);
// Сохраняем результат
ObjWorkBooks5 = ObjExcel5.Workbooks;
ObjWorkBook5 = ObjWorkBooks5[1];
ObjWorkBook5.Saved = true;
// Не будем спрашивать разрешение на запись поверх существующего документа
ObjExcel5.DisplayAlerts = false;
ObjWorkBook5.SaveAs(
Environment.CurrentDirectory + "\\Автозаполнение", ObjExcel5.DefaultSaveFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
System.Threading.Thread.Sleep(1000);
ObjExcel5.Quit();
}
Исходный документ выглядит так:
Рис. 5. 2. Исходный документ «Пример автозаполнения» (Файл типа Лист Microsoft Excel, разрешение *. xlsx)
Ячейка C1 исходного документа имеет тип «Дата». Остальные: «Общий». Сам файл в архиве также можно загрузить по ссылке в конце этого материала (сслыка доступна в программном продукте).
Наконец две оставшиеся кнопки. Первая (B _ Action) выполняет достаточно много разнообразных действий с выводом результата либо через MessageBox либо непосредственно в приложении. Например, изменяет размер окна до 50%, выключает строку формул, отключает возможность редактирования ячеек напрямую, выводит список всех недавно открытых документов и прочее. Также после нажатия, окно Excel будет заморожено (нельзя закрыть и совершить любые действия с окном и содержимым). Для «размораживания» введена кнопка B _ Unfreeze. Весь код прокомментирован. Событие Click для кнопки B _ Action:
private void B_Action_Click(object sender, EventArgs e)
{
B_Other.Enabled = false;
B_Unfreeze.Enabled = true;
// Создаём книги для примеров. Для некоторых примеров нам понадобятся
// несколько книг, для других будет достаточно одной. Для приводимых ниже
// примеров может понадобиться не только объекты книг, но и объекты
// листов и ячеек
ObjExcel6 = new Excel.Application();
ObjExcel6.Visible = true;
ObjExcel6.UserControl = true;
ObjExcel6.SheetsInNewWorkbook = 2;
ObjExcel6.Workbooks.Add(Type.Missing);
ObjExcel6.SheetsInNewWorkbook = 3;
ObjExcel6.Workbooks.Add(Type.Missing);
ObjWorkBooks6 = ObjExcel6.Workbooks;
// Получаем ссылку на объект: Книга1 - номерация от 1
ObjWorkBook6 = ObjWorkBooks6[1];
// Можно, используя свойства WindowState задать размер приложения.
// Возможные значения: xlNormal, xlMinimized, xlMaximized
ObjExcel6.WindowState = Excel.XlWindowState.xlNormal;
// Можно задать полноэкранный режим отображения при значении свойства
// DisplayFullScreen равным true и вернуться к обычному режиму (false)
ObjExcel6.DisplayFullScreen = false;
// Можно задать размеры при DisplayFullScreen = false; и XlWindowState.xlNormal;,
// используя соответствующие значения свойств Width, Height, Left и Top
ObjExcel6.Width = 500;
ObjExcel6.Height = 400;
ObjExcel6.Left = 10;
ObjExcel6.Top = 10;
// Можно убрать любую из панелей инструментов при значении свойства Visible равным
// false для соответствующего CommandBars, который может иметь значения:
// Standard, Formatting, Visual Basic, Web, WordArt, Clipboard, External Data,
// Exit Design Mode, Stop Recording, Chart, Picture, Reviewing, Drawing, PivotTable
// Forms, Control Toolbox и другие
ObjExcel6.CommandBars["Standard"].Visible = true;
// Можно получить или изменить форму курсора - свойство Cursor.
// Возможные значения: xlDefault, xlIBeam, xlNorthwestArrow, xlWait.
// Посмотреть имя курсора:
Text = ObjExcel6.Cursor.ToString();
MessageBox.Show("Текущий курсор: " + Text, "Автоматизация Microsoft Office Excel (C#) :: Курсор мыши");
// Можно изменить курсор
ObjExcel6.Cursor = Excel.XlMousePointer.xlWait;
// Можно изменить масштаб отображения документа (свойство Zoom):
ObjExcel6.ActiveWindow.Zoom = 50;
// Можно изменить шрифт по умолчанию и его размер. После перезапуска
// Excel все выведенное будет отображено данным шрифтом:
ObjExcel6.StandardFont = "Arial";
ObjExcel6.StandardFontSize = 10;
// Можно не отображать строку редактирования содержимого ячейки
// (свойство DisplayFormulaBar)
ObjExcel6.DisplayFormulaBar = false;
// Можно запретить редактирование ячеек в самих ячейках (свойство EditDirectlyInCell),
// разрешив редактирование только в строке формул
ObjExcel6.EditDirectlyInCell = false;
// Можно вообще запретить доступ к документу. Если свойство Interactiv
// не вернуть в true, то нельзя будет даже закрыть Excel
ObjExcel6.Interactive = false;
// Можно программно запретить обновление экрана после каждого изменения
// и, после выполнения большого объема выводимой информации, разрешить.
// Результат: увеличение скорости вывода
ObjExcel6.ScreenUpdating = false; // Запретить
// ... здесь большой объём выводимой информации
ObjExcel6.ScreenUpdating = true; // Разрешить
// Можно принудительно выполнить пересчёты формул, используя метод Calculate,
// в диапазоне ячеек, в книге или во всех открытых рабочих книгах
ObjSheets6 = ObjWorkBook6.Worksheets;
ObjWorkSheet6 = (Excel.Worksheet)ObjSheets6.get_Item(1);
// Для диапазона:
// ObjRange6 = ObjWorkSheet6.get_Range("A1", "С10").Calculate();
// Для книги:
// ObjWorkBook6.Calculate();
// Для всех книг:
// ObjExcel6.Calculate();
// Можно проверить правильность написания текста. Например,
// следующие строки дадут результат "написан некорректно".
// В методе CheckSpelling можно задать словарь (второй параметр)
// и задать игнорировать ли регистр (третий параметр) при проверки
ObjRange6 = ObjWorkSheet6.get_Range("A1", Type.Missing);
ObjRange6.Value2 = "Текьст";
Text = (ObjExcel6.CheckSpelling(ObjRange6.Value2.ToString(), Type.Missing, true) ? "написан корректно" : "написан некорректно");
MessageBox.Show("Текст в ячейке: \"" + ObjRange6.Value2 + "\", " + Text, "Автоматизация Microsoft Office Excel (C#) :: Проверка орфографии");
// Можно отменить последнее из выполненных действий (метод Undo), выполненное
// в самом приложении (не влияет на операции, выполненные из приложения)
// ObjExcel6.Undo();
// Можно получить и изменить путь сохранения и открытия файлов по умолчанию
Text = ObjExcel6.DefaultFilePath; // Выведет Ваш путь
MessageBox.Show("Текущий путь: " + Text, "Автоматизация Microsoft Office Excel (C#) :: Текущий путь");
ObjExcel6.DefaultFilePath = @"C:\";
Text = ObjExcel6.DefaultFilePath; // Выведет C:\
// Можно создать копию документа, используя метод Workbook.NewWindow()
// Например для документа "a" будут созданы окна "a:1" и "a:2":
Excel.Window ObjWindow6 = ObjWorkBook6.NewWindow();
// Можно создать копию документа и по другому - через свойства Application.Workbooks.
// Если окон много, то для проверки наличия окна целесообразно
// использовать свойство Count
if (ObjExcel6.Windows.Count > 1)
{
ObjWindow6 = ObjExcel6.Windows[1];
ObjWindow6.Application.Workbooks[1].NewWindow();
}
// Можно изменить расположение окон используя метод Arange. Порядок расположения
// определяет первый параметр метода: xlArrangeStyleCascade, xlArrangeStyleHorizontal,
// xlArrangeStyleTiled, xlArrangeStyleVertical. Второй параметр при true означает, что
// требуется упорядочить только видимые окна активной книги, при false - все.
// Третий и четвертый параметр - синхронизация разверток горизонтальной и вертикальной
ObjExcel6.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleVertical, true, true, true);
// Можно убрать заголовки строк и столбцов, используя свойство DisplayHeadings
ObjWindow6.DisplayHeadings = false;
// Или так
ObjExcel6.ActiveWindow.DisplayHeadings = false;
// Можно при значении свойства DisplayFormulas равным true показываеть в
// ячейках формулы (там где они есть), а при false - значения
ObjWindow6.DisplayFormulas = false;
// Можно, используя свойство DisplayWorkbookTabs при true показываеть помимо
// Scrollbars позиции табуляции для выбора листов книг и кнопки навигации по
// листам, или, убрать их, при значении свойства равным false
ObjWindow6.DisplayWorkbookTabs = true;
// Можно разделить лист путем отделения как, отдельной части, несколько
// cтолбцов или строк, используя свойства SplitColumn или SplitRow
ObjWindow6.SplitColumn = 5;
ObjWindow6.SplitRow = 5;
// Можно разделить окно вертикально или горизонтально используя свойства SplitVertical
// или SplitHorizontal (практически аналог предыдущего пункта)
ObjWindow6.SplitVertical = 10;
ObjWindow6.SplitHorizontal = 10;
// Можно изменить цвет сетки для листов.
// 1. Используя свойство GridlineColor
ObjWindow6.GridlineColor = ColorTranslator.ToOle(Color.Blue);
// 2. Используя свойство GridlineColorIndex
ObjWindow6.GridlineColorIndex = (Excel.XlColorIndex)3;
// Можно вообще убрать сетку, используя свойство DisplayGridlines
ObjWindow6.DisplayGridlines = false;
// Можно получить список всех недавно открывавшихся файлов.
// Для этого используется свойство Eccel.Application.RecentFiles
for (int j = 0; j < ObjExcel6.RecentFiles.Count; j++)
{
ObjRange6 = (Excel.Range)ObjWorkSheet6.Cells[j + 1, 1];
ObjRange6.Value2 = ObjExcel6.RecentFiles[j + 1].Name;
}
// Можно перейти на последнюю заполненную ячейку Excel
ObjExcel6.ActiveCell.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Select();
}
Событие Click для кнопки B_Unfreeze:
private void B_Unfreeze_Click(object sender, EventArgs e)
{
ObjExcel6.Interactive = true;
}
На этом всё. Приложение готово.
Завершающая часть
Компилируем приложение (Release) и запускаем. Проверяем работоспособность первых двух групп элементов и переходим к третьей:
Нажимаем на кнопку Сформировать готовый документ с графиком:
Рис. 6. 1. Модифицированное приложение Windows Forms : результат создания новой книги для графика (указываем число кварталов, нажатие «Нет» уменьшает число)
Рис. 6. 2. Модифицированное приложение Windows Forms : результат создания новой книги для графика (данные за четыре квартала и график на основе данных)
Жмём Другие листы. Результат:
Рис. 6. 3. Модифицированное приложение Windows Forms : результат создания изменения книги с графиком (заполненный Лист3)
Жмём Объединение:
Рис. 6. 4. Модифицированное приложение Windows Forms : результат создания изменения книги с графиком (заполненный Лист2)
Жмём Автозаполнение:
Рис. 6. 5. Модифицированное приложение Windows Forms : результат создания книги с автоматическим заполнением ячеек (заполненный Лист1)
Жмём Выполнить различные действия и затем Разморозить, чтобы разблокировать Excel:
Рис. 6. 6. Модифицированное приложение Windows Forms : результат работы кнопки «Выполнить различные действия» и «Разморозить»
Дата добавления: 2019-09-13; просмотров: 232; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!