Работа с сервером Excel

При создании нового документа в Excel (рабочая книга) из совокупности библиотек выбираются объекты, которые составляют каркас документа. Эти объекты доступны программисту.

Объект ExcelApplication задает приложение Excel, его свойства, методы и события характеризуют приложение в целом. Одно из свойств (Workbooks) возвращает все открытые в приложении рабочие книги.

Объект ExcelWorkbook задает рабочую книгу и имеет в свою очередь свойство Worksheets – все рабочие листы книги.

Объект Worksheet – это конкретный лист рабочей книги.

1.Установка связи с объектом

В библиотеке VCL имеются специальные компоненты, представляющие объекты Excel в приложении Builder C++. При проектировании приложения необходимо разместить на форме пиктограммы желаемых компонент, а для установки связи с объектами использовать метод Connect(). Вот несколько примеров такой связи:

• Установить связь с Excel

ExcelApplication1->Connect();

• Установить связь с активной рабочей книгой

ExcelWorkbook1-> ConnectTo (ExcelApplication1->ActiveWorkbook);

• Установить связь с активным листом

ExcelWorksheet1 -> ConnectTo ( ExcelWorkbook1->ActiveSheet);

• Получить лист, следующий за ExcelWorksheet1, и установить связь с ним

ExcelWorksheet2 -> ConnectTo (ExcelWorksheet1 -> get_Next());

Окно Excel станет видимым (или невидимым, если параметр равен false) только после вызова специального метода:

#define lcid LOCALE_SYSTEM_DEFAULT

ExcelApplication1->set_Visible(lcid,true);

Для вызова некоторых методов и свойств используется специальная константа LOCALE_SYSTEM_DEFAULT (lcid), ее значение равно 0.

Количество рабочих книг, открытых в приложении, и число рабочих листов в книге можно определить так:

int n= ExcelApplication1-> Workbooks-> Count;

Label1->Caption=(AnsiString) n;

n= ExcelWorkbook1-> Worksheets->Count;

Label2->Caption=(AnsiString) n;

Для открытия файла используется метод Open:

WideString filename="d:/work.xls";

ExcelApplication1->Workbooks->Open(filename);

Для сохранения рабочей книги используется метод Save:

ExcelWorkbook1->Save(lcid);

Можно добавить новую рабочую книгу и установить с ней связь.

ExcelApplication1->Workbooks -> Add();

ExcelWorkbook3-> ConnectTo ( ExcelApplication1->ActiveWorkbook);

Рабочая книга может иметь автора. Следующие операторы устанавливают и получают имя автора программно:

ExcelWorkbook1->set_Author(0,TVariant("Павлов Иван Петрович"));

Label1->Caption=ExcelWorkbook1->get_Author(lcid);

Сделать рабочий лист активным:

ExcelWorksheet2 ->Activate (lcid);

Можно установить новое имя листа:

ExcelWorksheet1->set_Name((TVariant)"MyList");

Label6->Caption=(AnsiString)ExcelWorksheet1->Name;

2.Работа с ячейками таблицы

Запись значения в активную ячейку

ExcelApplication1->ActiveCell->set_Value((TVariant)"a");

Можно выделить указанную ячейку (например “D1”) и сделать ее активной:

ExcelWorksheet2 -> get_Range ((TVariant)"D1",(TVariant)"D1") -> Select();

ExcelApplication1->get_ActiveCell() -> set_Value((TVariant)"888");

Массив ячеек

В Excel предусмотрено обращение к ячейкам таблицы как к элементам двумерного массива Cells(,). В Builder C++ сначала нужно получить доступ к этому массиву, вызвав метод get_Cells(), а затем метод set_Item(, , ) запишет заданное значение в элемент с указанными индексами. Все параметры перед передачей серверу должны быть упакованы в особый формат TVariant.

Например,

• - число 222 записывается в ячейку с номерами (1,1):

ExcelWorksheet2 -> get_Cells()-> set_Item((TVariant)1,(TVariant)1,(TVariant)"222");

• - формула записывается в ячейку (2,2):

ExcelWorksheet2 -> get_Cells()-> set_Item((TVariant)2,(TVariant)2,(TVariant)"=A1+D1");

• для записи формул можно использовать функции:

ExcelWorksheet2 -> get_Cells()-> set_Item((TVariant)5,(TVariant)1,(TVariant)"=СУММА(A1:A3");

Запись в цикле:

int i, j;

TVariant k;

for (i=0; i<3; i++)

for (j=0; j<4; j++)

{ k=(TVariant)(i+j);

ExcelWorksheet2 -> get_Cells()-> set_Item((TVariant)(i+1),(TVariant)(j+1),k);

}

Извлечение из ячейки:

k= (TVariant)ExcelWorksheet2 -> get_Cells()-> get_Item((TVariant)1,(TVariant)4);

ExcelWorksheet2 -> get_Range((TVariant)"A7",(TVariant)"A7")-> Select();

ExcelApplication1->get_ActiveCell()->set_Value(k);

Объект Range рабочего листа ExcelWorksheet предоставляет удобный доступ к таблице и может представлять:

• ячейку таблицы

Например, в ячейку B1 занести число 4:

ExcelWorksheet1->get_Range((TVariant)"B1",

(TVariant)"B1")->set_Value((TVariant)4);

• диапазон ячеек

Запись формулы в ячейки “с3-с6”:

ExcelWorksheet2->get_Range((TVariant)"с3",(TVariant)"с6") -> set_Value((TVariant)"=a3+b3");

Когда формула присваивается диапазону ячеек, то переменные в формуле имеют относительные имена, поэтому в данном примере в ячейку c4 будет вписана формула “=a4 +b4” и т.д.

• прямоугольную область,

Название города помещается в 12 ячеек

ExcelWorksheet1->get_Range((TVariant)"A2",(TVariant)"D4")->

set_Value((TVariant)"Москва ");

Получить значение из ячейки можно следующим образом:

TVariant y;

y=ExcelWorksheet2->get_Range((TVariant)"b5",(TVariant)"b5")->get_Value();

Очистить содержимое диапазона ячеек:

ExcelWorksheet2 -> get_Range((TVariant)"D1",(TVariant)"E3")-> Clear();

Копировать диапазон ячеек через буфер обмена:

ExcelWorksheet2 -> get_Range((TVariant)"A1",(TVariant)"B3")->Copy();

ExcelWorksheet2 -> get_Range((TVariant)"A7",(TVariant)"B9")->Select();

ExcelWorksheet2 -> Paste();

Смещение Offset

При создании объектов Range нельзя пользоваться смещением. Тем не менее, можно использовать смещение, чтобы переходить от одного объекта Range к другому, например от одной ячейки к другой, отстоящей от первой на определенном расстоянии. Достигается это благодаря методу Offset объекта Range. Метод имеет два параметра: смещение по строкам и столбцам, и возвращает новый объект Range, отстоящий от прежнего на заданное расстояние.

Например, следующий оператор задает запись в ячейку, отстоящую от b5 на (–1, 3), то есть в ячейку

ExcelWorksheet2->get_Range((TVariant)"b5",(TVariant)"b5")->

get_Offset ((TVariant)-1,(TVariant)3)->set_Value((TVariant)"=b5+d4");

В следующем примере числами заполняется прямоугольная область (5 строк, 4 столбца; базовая ячейка “a3”):

int i, j;

for (i=0; i<5; i++)

for (j=0; j<4; j++)

ExcelWorksheet1 -> get_Range((TVariant)"a3",(TVariant)"a3")->

get_Offset((TVariant)i,(TVariant)j)->set_Value((TVariant)(i+j));

3.Оформление ячеек таблицы

Выделить курсивом или полужирным шрифтом:

ExcelWorksheet2 -> get_Range((TVariant)"A1",(TVariant)"E5")->get_Font()-> set_Italic((TVariant)true);

ExcelApplication1->get_ActiveCell()->get_Font()-> set_Bold((TVariant)true);

Выравнивание (xlCenter – по центру, xlRight – по правому краю, xlLeft – по левому краю) и подчеркивание

ExcelWorksheet2 -> get_Range((TVariant)"D1",(TVariant)"E3")-> set_HorizontalAlignment((TVariant)xlCenter); xlRight, xlLeft

ExcelWorksheet2 -> get_Range((TVariant)"D1",(TVariant)"E3")->get_Font()-> set_Underline((TVariant)true);

Изменение цвета букв:

• Синий

ExcelWorksheet2 -> get_Range((TVariant)"D1",(TVariant)"E3")->get_Font()-> set_Color((TVariant)0x00FF0000);

• Светло-салатовый

ExcelWorksheet2 -> get_Range((TVariant)"D1",(TVariant)"E3")->get_Font()-> set_Color((TVariant)RGB(0,255,255));

При работе с цветом можно также использовать зарезервированные константы (clRed – красный, clWhite – белый и т.д.).

ExcelWorksheet2 -> get_Range((TVariant)"A1",(TVariant)"B7")->get_Font()-> set_Color((TVariant)clWhite);

Заливка цветом:

ExcelWorksheet2 -> get_Range((TVariant)"A1",(TVariant)"B7")->Interior-> set_Color((TVariant)RGB(0,128,200));

ExcelWorksheet2 -> get_Range((TVariant)"A1",(TVariant)"B7")->Interior-> set_Pattern((TVariant)xlHorizontal);

XlHorizontal определяет горизонтальную штриховку, можно использовать также вертикальную xlVertical и сплошную заливку xlSolid.

Задание

Разработать приложение, для обработки данных электронной таблицы. Приложение открывает (или создает новую) рабочую книгу «Детские товары.xls» и привязать ее к компоненту Workbook1, убедиться, что рабочая книга содержит три рабочих листа (или создать их):

• лист 1 (Worksheet1) «Счет»;

• лист 2 (Worksheet2) «Цены»;

• лист 1 (Worksheet3) «Товары».

Сформировать информацию на рабочих листах:

• Worksheet3: заголовок «Детские игрушки», таблица должна содержать следующие поля: номер по порядку, код (четырехзначный уникальный, например – 0376 или 9564), название, количество экземпляров в наличии;

• Worksheet2: заголовок «Цены», таблица должна содержать следующие поля: номер по порядку (записи в таблице могут идти в другом порядке), код (коды берутся из Worksheet3), цена;

• Worksheet1: заголовок «Счет», таблица должна содержать следующие поля: номер порядку (записи могут идти в другом порядке, чем на других листах), код (коды берутся из Worksheet3), названия (названия берутся из Worksheet3), цена (цены берутся из Worksheet2), количество экземпляров (заказ товара), стоимость товара (количество*цена). Последняя строка на листе содержит общую стоимость заказанного товара (сумма стоимостей отдельных товаров).

Главное окно приложения может выглядеть как на рисунке (заголовок окна «Заказ детских игрушек»).

Порядок создания приложения

После оформления главного окна приложения написать код обработчиков:

• кнопка «Открыть» устанавливает связь с Excel и либо открывает файл «Детские товары», либо добавляет новую рабочую книгу;

• кнопка «Закрыть» сохраняет рабочую книгу, разрывает связь с Excel и заканчивает работу всего приложения;

• кнопка «Лист3» выводит на рабочий лист 3 заголовок «Детские игрушки», заголовки столбцов таблицы и заполняет ее данными о имеющихся в наличии товарах по желанию программиста (5-6 записей);

• кнопка «Лист2» выводит на рабочий лист 2 заголовок «Цены», заголовки столбцов таблицы и заполняет ее данными о имеющихся в наличии товарах (данные берутся с рабочего листа 3);

• кнопка «Лист1» выводит на рабочий лист 1 заголовок «Счет», заголовки столбцов таблицы и заполняет ее данными о имеющихся в наличии товарах и ценах (данные берутся с рабочих листов 2 и 3);

• кнопка «Ввод цен» запрашивает у пользователя цены на имеющиеся товары и заносит их в таблицу на листе 2 (для ввода может использоваться, например, диалоговое окно);

• кнопка «Заказ» запрашивает у пользователя требуемое количество каждого товара и заносит эти данные в таблицу на листе 1 (для ввода может использоваться, например, диалоговое окно);

• кнопка «Счет» выполняет сложение стоимостей партий всех товаров и выдает конечный результат (общая стоимость товара).

Приложение должно обеспечить строгий порядок доступа пользователя к имеющимся функциям, то есть порядок доступности кнопок:

• кнопка «Открыть»;

• кнопка «Лист3»

• кнопка «Лист2»

• кнопка «Ввод цен»

• кнопка «Лист1»

• кнопка «Заказ»

• кнопка «Счет»

• кнопка «Закрыть».