Delphi excel range
ASD-SOFT
Программирование. Теория и практика.
Обмен данными с MS Excel в Delphi при помощи OLE.
Обмен данными с MS Excel в Delphi при помощи OLE.
Здравствуйте уважаемые коллеги!
Все мы рано или поздно сталкиваемся с задачами обмена данных с приложениями пакета MS Office. Одно из них — это MS Excel. И именно о взаимодействии с данным продуктом MS Office пойдет речь в данной статье.
Один из способов взаимодействия Delphi c MS Excel — это подключиться к нему как к OLE объекту.
Итак.
Прежде всего для работы с MS Excel и OLE добавим в секцию Uses модули ComObj и ActiveX.
И первое что нам нужно проверить, а установлен ли MS Excel на компьютере пользователя в принципе.
Для этого воспользуемся функцией CLSIDFromProgID, которая ищет в реестре CLSID для переданного ProgID:
Справка из MSDN: Метод CLSIDFromProgID
Параметры:
pszProgID: POleStr — Строка с именем объекта
clsid: TCLSID — Указатель на структуру TGUID в которую передается найденный объект;
Возвращает:
HRESULT — Результат, который может принимать значения:
S_OK — объект найден;
CO_E_CLASSSTRING — Зарегистрированный CLSID для ProgID является недействительным;
REGDB_E_WRITEREGDB — Ошибка записи CLSID в реестр.
Из перечисленных результатов нам нужен S_OK.
Напишем функию для определения наличия Excel у пользователя:
Если Excel установлен, тогда выполним подключение к нему. Сделать это можно двумя способами: GetActiveOleObject — Получить ссылку на уже запущенный экземпляр Excel или CreateOleObject — Создать новый экземпляр Excel.
Если у нас стоит задача получать данные из запущенного Excel, тогда мы должны использовать только первый вариант, в остальных случаях пробуем подключиться и если не получается, то создаем.
Напишем 2 функции, для подключения XlsConnect и запуска нового XlsStart:
Добавим переменную FXlsApp с типом Variant, которая будет содержать в себе ссылку на объект Excel.
Теперь можно добавить кнопку, на клик которой подключимся к MS Excel используя написанные функции:
По умолчанию окно Excel запускается в фоновом режиме. Строка FXlsApp.Visible := True; делает фоновое окно Excel видимым.
Окно Excel запускается пустое и в него нужно добавить рабочую книгу. Делается это при помощи метода WorkBooks.Add, который добавляет новую книгу или открывает ранее сохраненную, если указать путь к файлу.
Добавим процедуру, которая будет это делать:
Книга добавлена, теперь попробуем записать что-нибудь в неё.
Где Row — индекс строки, и Col — индекс столбца, которые начинаются с единицы.
Где Range — массив ячеек, а А1 — привычные для Excel координаты ячейки.
В качестве координат может быть указан диапазон. Например, код
заполнит цифрой 5 все ячейки с А3 по А10, а код
выделит тот же диапазон светло-зеленым цветом.
В обратную сторону, то есть для получения данных из Excel, работает так же. Строка
Выведет сообщение с содержимым ячейки с координатами: Строка=5, Столбец=1.
После того, как мы произвели необходимые нам манипуляции с Excel, мы можем сохранить полученную книгу в файл следующей командой:
Где ActiveWorkbook — текущая книга.
И закрыть приложение Excel командой:
Как понимаете этим возможности управления Excel из Delphi не ограничиваются. И есть один достаточной простой способ узнать, как выполнить необходимо действие с Excel из Delphi.
Называется оно — Макросы.
Представим, что нам необходимо выполнить объединение нескольких ячеек в одну и мы не знаем как это сделать. Но хотим узнать. Для этого выполняем следующие шаги:
1. Запускаем Excel и создаем пустую книгу.
2. Запускаем команду «Записать макрос», по умолчанию название макроса будет «Макрос1». (В разных версиях Excel данная команда находится в разных пунктах меню).
3. Выделяем некоторый диапазон ячеек и нажимаем кнопку «Объединить и поместить в центре».
4. Останавливаем запись макроса.
5. Вызываем список макросов и выбираем там свой записанный макрос.
6. Нажимаем кнопку «Изменить»
Запускается редактор Microsoft Visual Basic for Application в котором видим код проделанных действий:
Давайте разберем по подробнее, что же такого он нам тут написал:
With Selection — Для выделенного диапазона ячеек настраиваем свойства:
HorizontalAlignment = xlCenter — Горизонтальная ориентация = по центру.
VerticalAlignment = xlBottom — Вертикальная ориентация — по нижнему краю.
WrapText = False — Перенос текста по словам — выключен.
Orientation = 0 — Ориентация 0 градусов.
AddIndent = False — Использование автоматического отступа вкл/выкл.
IndentLevel = 0 — Уровень отступа в 0.
ShrinkToFit = False — Сжимать текст по размерам столбца вкл/выкл.
ReadingOrder = xlContext — Порядок чтения по контексту.
MergeCells = False — Объединенные ячейки вкл/выкл
End With — Конец секции работы с выделенным диапазоном.
Selection.Merge — Объединить выделенный диапазон.
Теперь попробуем объединить ячейки из Delphi:
Выделяем нужный нам диапазон.
Объединяем ячейки задав свойство. Или при помощи метода:
Таким способом можно получать код практически для любых необходимых манипуляций.
А если какое-то свойство или метод вызывает вопросы, то можно воспользоваться справкой на MSDN.
Обратите внимание на особенность работы с массивами в VBA. Индексы в массивах в Delphi оборачиваются в квадратные скобки, в то время как в VBA они будут в круглых. И код в Delphi
в VBA будет выглядеть как
Ниже приведу небольшой FAQ по вопросу взаимодействия с Excel из Delphi
Как определить значения констант в Excel для использования в Delphi?
В редакторе VBA ставим точку остановки напротив интересующей константы. Нажимаем выполнить и когда выполнение остановиться, наводим на интересующую константу: 
Как отключить выводы сообщений в Excel?
Как получить список книг из Excel?
Как отключить отображение сетки?
Как вывести текущий лист на предпросмотр печати?
Как выделить жирным часть текста в ячейки?
Как выполнить автоподбор высоты строки для склеенной ячейки?
Как получить используемый диапазон ячеек?
Как получить букву столбца по индексу?
Delphi excel range
Здравствуйте мастера Delphi. Помогите пожалуйста. Вот код сохранения информации из ADOQuery в Excel:
procedure TForm1.Button1Click(Sender: TObject);
var
Excel, Workbook, Worksheet: OleVariant;
CurrentRowIndex, CurrentColIndex: integer;
begin
CurrentRowIndex := 1;
Excel := CreateOleObject(«Excel.Application»);
try
Workbook := Excel.Workbooks.Add;
Worksheet := Workbook.Worksheets.Add;
with ADOQuery do
try
Open;
while not Eof do
begin
for CurrentColIndex := 0 to FieldsCount — 1 do
Worksheet.Cells(CurrentRowIndex, CurrentColIndex + 1) := Fields[CurrentColIndex].AsString;
Next;
Inc(CurrentRowIndex);
end;
finally
begin
//здесь нужно написать код выделение ячейки и задания ей границ.
.
//например:
Worksheet.Range[«a1″,»a1»].Font.Color := clBlue;
//поменять цвет шрифта в ячейке, а мне нужно границы поставить на ячейку
end;
end;
finally
Excel.Visible := True;
Excel := Unassigned;
end;
end;
Это макрос в Excel: выделение ячейки и задания ей границ.
Sub Макрос1()
» Макрос1 Макрос
» Макрос записан 26.05.2005 (admin)
Range(«D8»).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End Sub
Помогите пожалуйста опытные программситы, буду очень вам благодарен. Спасибо
← →
yk © ( 2005-05-26 07:36 ) [1]
Во-первых, рекомендую сохранять выделение в переменную типа Variant:
var
Range, Cell1, Cell2: Variant;
.
.
Cell1 := WorkBook.ActiveSheet.Cells[beginRow-3,BeginCol-1];
Cell2 := WorkBook.ActiveSheet.Cells[beginRow-1,BeginCol];
Range := WorkBook.ActiveSheet.Range[Cell1, Cell2];
Range.Select;
Во-вторых ,
рекомендую найти статью «По волнам интеграции и почитать её»
В-третьих,
рамку клетки (или диапазона) делаешь так:
.
Range.Select;
Range.Borders.LineStyle:=xlContinuous;
Range.Borders.Weight := xlThick; //или xlMedium
.
В-четвёртых, больше эксперементируй с макросами в Excel — там куча инфы.
В-пятых, код был приведён для выделения всех сторон клеток.
Может кто-нибудь знает, как выделить, например только боковые границы? А то у меня не получилось 🙁
← →
YurikGL © ( 2005-05-26 07:38 ) [2]
> Range(«D8»).Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> With Selection.Borders(xlEdgeLeft)
> .LineStyle = xlContinuous
> .Weight = xlMedium
> .ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeTop)
> .LineStyle = xlContinuous
> .Weight = xlMedium
> .ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeBottom)
> .LineStyle = xlContinuous
> .Weight = xlMedium
> .ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeRight)
> .LineStyle = xlContinuous
> .Weight = xlMedium
> .ColorIndex = xlAutomatic
> End With
> End Sub
подозреваю, что будет что-то типа
Excel.activesheet.Range(«D8»).Select
Excel.selection.borders.item(xlEdgeRight).linestyle:=xlContinuous
Excel.selection.borders.item(xlEdgeRight).Weight = xlMedium
Excel.selection.borders.item(xlEdgeRight).ColorIndex = xlAutomatic
Дальше по образу и подобию
← →
YurikGL © ( 2005-05-26 07:40 ) [3]
> Может кто-нибудь знает, как выделить, например только боковые
> границы? А то у меня не получилось 🙁
Катя, а макрос писать не пробовала?
xlEdgeLeft — левая граница выбранной области,
xlEdgeBottom — нижняя граница выбранной области.
← →
yk © ( 2005-05-26 08:12 ) [4]
Не додумалась до
> item
Но это у мнея пока умозрительный интерес
А ты где? У меня назрело несколько вопросов и уточнений по базе.
Хоть в Аське появись, плиз
← →
Руслан ( 2005-05-26 11:01 ) [5]
Канал в Telegram
Вы здесь
Читаем файл Excel (xls) в Delphi
При написании бизнес приложений на Delphi, выполняющих какие либо расчеты, может возникнуть необходимость импорта (экспорта) данных из файлов других приложений. Одно из основных таких приложений – MS Excel. Как прочитать файлы xls в Delphi мы сейчас и рассмотрим!?
Создадим новый проект и разместим на форме три компонента:
StringGrid – находится на палитре компонентов, во вкладке Additional
Кнопку Button и для удобства выбора файлов, диалог открытия файлов (работа с диалогами описана здесь).
Для StringGrid, в свойствах(Properties) укажем следующее:
DefaultRowHeight – 17
FixedCols – 0
FixedRows – 0
В результате форма будет выглядеть примерно так:
На этом украшательства закончим и приступим к написанию кода.
Для работы с Excel файлами нам понадобится библиотека ComObj, входящая в стандартную поставку Delphi. Подключается она в разделе Uses. Добавьте в конце, после запятой «ComObj».
Теперь опишите процедуру Xls_Open после ключевого слова implementation:
procedure Xls_Open(XLSFile:string; Grid:TStringGrid);
const
xlCellTypeLastCell = $0000000B;
var
ExlApp, Sheet: OLEVariant;
i, j, r, c:integer;
begin
//создаем объект Excel
ExlApp := CreateOleObject(‘Excel.Application’);
//делаем окно Excel невидимым
ExlApp.Visible := false;
//открываем файл XLSFile
ExlApp.Workbooks.Open(XLSFile);
//создаем объект Sheet(страница) и указываем номер листа (1)
//в книге, с которого будем осуществлять чтение
Sheet := ExlApp.Workbooks[ExtractFileName(XLSFile)].WorkSheets[1];
//активируем последнюю ячейку на листе
Sheet.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate;
// Возвращает номер последней строки
r := ExlApp.ActiveCell.Row;
// Возвращает номер последнего столбца
c := ExlApp.ActiveCell.Column;
//устанавливаем кол-во столбцов и строк в StringGrid
Gr > Gr >
//считываем значение из каждой ячейки и копируем в нашу таблицу
for j:= 1 to r do
for i:= 1 to c do
Gr > //если необходимо прочитать формулы то
//Gr >
//закрываем приложение Excel
ExlApp.Quit;
//очищаем выделенную память
ExlApp := Unassigned;
Sheet := Unassigned;
end;
Наша процедура Xls_Open имеет два входных параметра:
- XLSFile – путь к файлу Excel (xls)
- Grid – таблица, в которую будем осуществлять вывод данных.
В комментариях к коду я постарался все расписать, объясню немного принцип работы процедуры.
Мы создаем в памяти объект ExlApp с приложением Excel.Application. Затем указываем что этот объект будет невидим для пользователя ExlApp.Visible := false (если false заменить на true, то при открытии файла мы увидим окно Excel с открываемым файлом). Далее открывается файл ExlApp.Workbooks.Open(XLSFile). Потом создаем еще один объект Sheet, который позволяет работать с листом книги Excel: Sheet := ExlApp.Workbooks[ExtractFileName(XLSFile)].WorkSheets[1]. Единица в конце указывает номер листа с которого будем читать данные (к листам можно обращаться по имени, для этого вместо 1 указываем «ИмяЛиста1»). Далее определяем кол-во строк и столбцов которые содержат данные, и используя циклы For считываем данные копируя их в StringGrid. Впринципе и все.
Для использования процедуры Xls_Open пропишем в событие Click кнопки Button1 следующее:
If OpenDialog1.Execute then Xls_Open (OpenDialog1.FileName, StringGrid1);
Основные недостатки этого способа: медленное чтение данных (хотя для небольших файлов вполне сгодится) и необходимость установленной версии Excel на компьютере пользователя.
Полный листинг программы:
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Grids, StdCtrls,ComObj;
type
TForm1 = class(TForm)
StringGrid1: TStringGrid;
Button1: TButton;
OpenDialog1: TOpenDialog;
procedure Button1Click(Sender: TObject);
private
public
end;
var
Form1: TForm1;
implementation
procedure Xls_Open(XLSFile:string; Grid:TStringGrid);
const
xlCellTypeLastCell = $0000000B;
var
ExlApp, Sheet: OLEVariant;
i, j, r, c:integer;
begin
//создаем объект Excel
ExlApp := CreateOleObject(‘Excel.Application’);
//делаем окно Excel невидимым
ExlApp.Visible := false;
//открываем файл XLSFile
ExlApp.Workbooks.Open(XLSFile);
//создаем объект Sheet(страница) и указываем номер листа (1)
//в книге, с которого будем осуществлять чтение
Sheet := ExlApp.Workbooks[ExtractFileName(XLSFile)].WorkSheets[1];
//активируем последнюю ячейку на листе
Sheet.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate;
// Возвращает номер последней строки
r := ExlApp.ActiveCell.Row;
// Возвращает номер последнего столбца
c := ExlApp.ActiveCell.Column;
//устанавливаем кол-во столбцов и строк в StringGrid
Gr > Gr >
//считываем значение из каждой ячейки и копируем в нашу таблицу
for j:= 1 to r do
for i:= 1 to c do
Gr > //если необходимо прочитать формулы то
//Gr >
//закрываем приложение Excel
ExlApp.Quit;
//очищаем выделенную память
ExlApp := Unassigned;
Sheet := Unassigned;
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
If OpenDialog1.Execute then Xls_Open (OpenDialog1.FileName, StringGrid1);
end;
end.
Обновленный пример. Добавлена функция отбора строк по критерию. Так же добавлен обработчик делающий отображение шрифта «шапки» в StringGrid жирным. Все дополнения постарался по максимуму раскомментить
Delphi excel range
Написанное приложение, прекрасно работающие с Excel’ем на собственном компьютере, часто, после переноса приложения на другой компьютер, оказывается неработоспособным! Отчего так происходит? В этой статья я собираюсь описать разницу в работе русской версии Excel’я из VBA и через COM интерфейс (библиотеку типов, TLB) из Delphi. Почему возникли расхождения? Ответа на эти вопросы у Microsoft я не нашел…
Примечание:
сравнивались только русская и английская (American English) версии Excel с номером версии 9.0 (MS Office 2000) и выше. Другие версии не рассматривались.
Описание типов объектов, применяемых в примерах:
Используемые в примерах » дополнительные » модули:
OleServer, Excel2000, Office2000 из стандартной поставки Delphi Enterprise версии 6 и выше.
У вас русская версия Excel?
Определить наличие русской версии Excel возможно так:
Английская версия Excel (English United States) вернет 1033 (или $0409), немецкая (German Standard) — $0407. Значения соответствуют LC >msoLanguageIDUI находится в модуле Office2000.pas и описана так:
Примечание:
в Office97 свойство LanguageSettings отсутствует
Далее мы рассмотрим приемы работы с » русским » Excel’ем.
Работа со свойством объекта Range NumberFormat
NumberFormat и NumberFormatLocal четко работают в VBA и полностью соответствуют своему содержанию в названиях, но только не при работе из Delphi. В Excel2000.pas (D7) они описаны как
Но, при попытке записи форматов из Delphi, выясняется, что NumberFormat и NumberFormatLocal ведут себя идентично, причем NumberFormat соответствует NumberFormatLocal (лучше было бы наоборот :). Т.е. в русской версии все форматы нужно писать «по-русски» (можно прямо в NumberFormat, в VBA — нельзя).
Формат даты
Код на VBA (эталон):
Формат чисел. Разделители. (DecimalSeparator, ThousendSeparator)
И это еще не все! Заходим в настройки Excel’я «Сервис/Параметры» переходим на закладку «Международные» и видим опять «Разделитель целой и дробной части», «Разделитель разрядов» и чекбокс «Использовать системные разделители». Т.е. использование системных разделителей не может гарантировать правильного применения при форматировании чисел в Excel’е. Решение: использовать свойство ExcelApplication.International (о нем дальше). Причем, даже при установленном свойстве ExcelApplication.UseSystemSeparators = False и отличных от системных ExcelApplication.DecimalSeparator и ExcelApplication.ThousandsSeparator, ExcelApplication.International отработает корректно.
Далее рассмотрим примеры работы (или не работы), приняв «стандартные» настройки для русских Windows:
Код на VBA (эталон):
Примечание:
в примерах значения записываются в Value2 для предотвращения форматирования «на лету» самим Excel’ем. Так число 123.45, записанное в Value будет автоматически отформатировано Excel’ем в формат валюты, а присвоение Value = Date будет автоматически переведено в формат даты. Запись в Value2 «воспринимает» значение как Double. Подробнее смотрите в справке VBA для Excel’я.
Решения (с использованием ExcelApplication.International):
Для получения формата даты можно написать функцию:
Для формата валюты:
Тот же принцип можно применить к времени и другим типам. Также смотрите другие индексы для свойства International (их там много) в справке VBA. Например, получить «основной» (general) формат можно так:
установить основной формат еще можно установить, записав в NumberFormat «пустую» строку, т.е. указать, что нет форматирования для чисел (даты):
Цвет в формате
К сожалению, не лучше обстоит дело и с цветом в форматах. Т.е. цвет в Delphi можно задавать только по-русски: Перечень цветов по-русски, которые можно задавать в формате: черный, красный, зеленый, синий, фиолетовый, желтый, белый. Список небогатый.
Формулы на листе
К счастью, работа со свойствами Formula и FormulaLocal в VBA и Delphi идентична и соответствуют своим названиям. Хочется отметить только один нюанс (это, кстати, действительно и для VBA) — при написании «русских» формул нужно учитывать системную переменную ListSeparator. Так, если на другом компьютере пользователь изменит его со стандартного для русской версии Windows символа «;» на «,» (например, как это делаю я :), то присвоение Range.FormulaLocal := ‘=округл(A1*B1; 2)’; вызовет ошибку! Поэтому, с учетом «разделителя элементов списка» нужно писать так:
Здесь приятней и проще пользоваться английскими формулами. Но, иногда, существует необходимость писать формулы из вариантного массива…
Примечание:
системные переменные ListSeparator, DateSeparator описаны в модуле System.
Запись формул из Variant-ного массива
Запись в свойство Formula, FormulaLocal, Value, Value2 из Variant-ного массива идентична в русском Excel’е и при работе из Delphi. Но, если мы хотим вставлять формулы прямо из массива, все они должны быть только русскими! Вот здесь то и всплывает необходимость определения наличия русской версии Excel’я (впрочем, это уже касалось задания цвета в свойстве NumberFormat).
Код на Delphi (тут мы применим знание написания русских формул, описанный выше, а именно ListSeparator):
Примечание:
из примера видно, что при записи из Variant-ного массива в Formula, FormulaLocal, Value, Value2 не имеет значения, какой стиль ссылок используется: A1 и R1C1 работают идентично. Но это не относится к свойствам FormulaR1C1 и FormulaR1C1Local, которые принимают формулы ТОЛЬКО в стиле R1C1.
Создание колонтитулов
Давайте запустим запись макроса создания колонтитула (меню в Excel «Сервис/Макрос/Начать запись…» ). Теперь откроем параметры страницы (меню «Файл/Параметры страницы…»). Создадим центральный нижний колонтитул «Лист &[Страница] из &[Страниц]» шрифтом «Arial», «полужирный» и размером 8pt. Слова «Лист» и «из» с начертанием «обычный». После «сокращения» макроса получим:
Т.е. при выводе на печать мы хотим, чтоб в нижний колонтитул по центру выводился текст, к примеру «Лист 1 из 5» .
Примечание:
если вы хотите увидеть работу вашего макроса в действии (чтоб работал PrintPreview), обязательно внесите на лист хоть какие-нибудь данные.
Вроде бы все ясно, осталось только переписать его под Delphi:
Проверяем в Excel’е «Предварительный просмотр» — оба, и не работает! А как же должно работать?
Припоминая русификацию еще Excel’я 4-й версии, напишем русские эквиваленты:
Сработало! Ну, и теперь добавим распознавание русской версии:
Вывод: при вставке кодов форматирования из Delphi в русский Excel должны вставляться только русские коды форматирования. А где их взять? Вот список кодов форматирования, полученных методом пробы:
|
И еще один опыт:
Работает! Т.е. начертания (Style у класса TFont в Delphi) шрифтов можно уверенно писать по-английски. Или заменить на коды форматирования:
Примечание:
для перевода строки в колонтитуле или ячейке используйте симол LF, ASCI код 10 (
#10 ):
Выводы
При работе с русским Excel’ем из Delphi необходимо соблюдать следующие правила:
- при задании форматов использовать только русские форматы чисел и даты;
- при цветном форматировании чисел указывать цвета только на русском языке;
- при записи формул из вариантного массива использовать только русские формулы;
- при создании колонтитулов использовать только русские коды форматирования;
- для совместимости с английской версий необходимо проверять LC >Мне не удалось найти документацию, касающуюся моментов описанных выше. Весь материал построен чисто на собственном опыте. И еще: не было возможности проверить на полностью английских версиях Windows и Office.
Все примеры тестировались на Delphi 6, Delphi 7, на русских версиях WindowsXP + OfficeXP, Windows98SE + Office2000 SR?1.
