Шабля А. - Excel ЧаВо

Как загрузить новый экземпляр Excel или подключиться к запущенному экземпляру EXCEL.EXE? Как отсоединиться от Excel и закрыть его экземпляр?
Для определения, будет ли запущен новый экземпляр Excel.Application или присоединение к уже запущенному, используется свойство TExcelApplication.ConnectKind. По умолчанию это свойство имеет значение ckRunningOrNew (константы определены в unit OleServer). Однако рекомендуется, если нет на то особой надобности, всегда запускать новый экземпляр Excel.Application во избежание конфликтов с запущенным раннее экземпляром Excel.Application.
Свойство TExcelApplication.AutoQuit в конструкторе устанавливается по умолчанию в False (только в модуле ExcelXP в True) это значит, что если вы хотите при отсоединении завершить работу Excel (закрыть), то нужно вызвать метод TExcelApplication.Quit или установить свойство TExcelApplication.AutoQuit равным True.
Delphi:
var XL: TExcelApplication; begin // запускаем новый экземпляр Excel'я XL := TExcelApplication.Create(nil); try XL.ConnectKind := ckNewInstance; XL.Connect; // подключение XL.AutoQuit := False; // по умолчанию это свойство True только в unit ExcelXP XL.Visible[lcid] := True; // здесь работаем с Excel'ем finally // отсоединяемся XL.UserControl := True; // отдадим управление пользователю XL.Quit; // закрыть Excel XL.Disconnect; FreeAndNil(XL); end; C#:
private Excel.Application StartExcel(bool asNewInstance) { Excel.Application XL = null; if (!(asNewInstance)) { try { XL = System.Runtime.InteropServices.Marshal.GetActiveObject(Excel.Application) as Excel.Application; } catch { // XL = null; } } if (XL == null) XL = new Excel.Application(); if (XL.Workbooks.Count == 0) XL.Workbooks.Add(Type.Missing); XL.Visible = true; return XL; } private void FinishExcel(Excel.Application XL) { if (XL != null) { XL.ScreenUpdating = true; if (! XL.Interactive) XL.Interactive = true; XL.UserControl = true; if (XL.Workbooks.Count == 0) { XL.Quit(); } else { if (! XL.Visible) XL.Visible = true; XL.ActiveWorkbook.Saved = true; } // System.Runtime.InteropServices.Marshal.ReleaseComObject(XL); XL = null; GC.GetTotalMemory(true); // вызов сборщика мусора // Пока не закрыть приложение EXCEL.EXE будет висеть в процессах } } How to automate Microsoft Excel from Microsoft Visual C# .NET
How to use Visual C# to automate a running instance of an Office program
GC.GetTotalMemory Method
GC Class

Как запустить Excel из консольного приложения или в отдельном потоке (TThread)
Консольное приложение и дочерний поток (класс TThread) не предполагают работу с COM сервером, как это сделано в главном потоке для Application в VCL. Для того чтобы все работало, необходим вызов функции WinAPI CoInitialize.
{$APPTYPE CONSOLE} // для консольного приложения var NeedToUninitialize: Boolean; begin // NeedToUninitialize := Succeeded(CoInitialize(nil)); NeedToUninitialize := Succeeded(CoInitializeEx(nil, COINIT_MULTITHREADED)); try // здесь работаем с Excel'ем finallyif NeedToUninitialize then CoUninitialize; end; end. Отличие при работе в Thread весь код работы должен быть помещен в метод TThread.Execute.
Для C# ничего дополнительно вызывать не нужно все работает, что для WinForms, что для Console, что для объекта класса Thread().
CoInitializeEx
OleInitialize

Как получить и настроить папки Excel по умолчанию?
По умолчанию все открываемые и сохраняемые документы находятся в папке %USERPROFILE%Мои документы (Personal). Ссылка на эту папку содержится в свойстве TExcelApplication.DefaultFilePath (read/write).
Для чтения и записи в другие папки используйте полный путь к файлу книги.
Delphi:
(XL.ActiveSheet as _Worksheet).Range['A1', EmptyParam].Formula := Format('DefaultFilePath: %s', [XL.DefaultFilePath[lcid]]); DefaultFilePath Property
How to: Set the Default Save Path for Workbooks

Чем отличается TExcelApplication от ExcelApplication, TExcelWorkbook от ExcelWorkbook?
Все отличие TExcelApplication от ExcelApplication в том, что первый наследник TOleServer. Это расширяет возможности для выбора способа подключения/отключения COM-сервера Excel и упрощает работу с событиями Excel.Application.
Получить интерфейс ExcelApplication всегда можно из свойства TExcelApplication.DefaultInterface (DefaultInterface штатное свойство всех наследников класса ToleServer).

Как узнать локализацию Excel'я (русская версия или нет)?
Для Delphi это можно почитать здесь.
C#:
oSheet.get_Range(A1, Type.Missing).Value2 = XL.LanguageSettings.get_LanguageID( Microsoft.Office.Core.MsoAppLanguageID.msoLanguageIDUI); LanguageSettings Object

Как сделать, чтобы Excel работал быстрее?
Для ускорения работы с Excel'ем можно сделать следующие шаги:
Delphi:
// запретить перерисовку экрана XL.ScreenUpdating[lcid] := False; // отменить автоматическую калькуляцию формул XL.Calculation[lcid] := xlManual; // отменить проверку автоматическую ошибок в ячейках (для XP и выше) with XL.ErrorCheckingOptions dobegin BackgroundChecking := False; NumberAsText := False; InconsistentFormula := False; end; Не использовать метод Select, и, как следствие, свойство Selection (смотрите дальше).
Существенно повышается скорость, если вместо записи в каждую ячейку использовать запись из VarArray (смотрите дальше про объект Range). В Demo-проекте есть тест затраченного времени для различных методов записи.
Так как основное время работы с Excel'ем затрачивается на перерисовку (установка отступов для страницы, размеров строк и столбцов, атрибуты шрифтов и т.д.), то лучше всего использовать заранее подготовленный шаблон.

Как вывести приложение Excel на передний план?
Для выноса приложения Excel на передний план просто вызовите метод Visible объекта ExcelApplication.
Delphi:
XL.Visible[lcid] := True; // Этот способ только для Excel версии XP и выше SetForegroundWindow(XL.Hwnd); C#:
XL.Visible = true;
Как сделать так, чтоб работали английские формулы и форматы чисел в ячейках?
Решение для Delphi здесь Русский Excel и установка NumberFormat
К сожалению, при работе с русским Excel'ем из C# проблемы те же, но, к счастью, решаются проще через CultureInfo:
C#:
int savedCult = Thread.CurrentThread.CurrentCulture.LCID; try { // установим английскую культуру Thread.CurrentThread.CurrentCulture = new CultureInfo(0x0409, false); Thread.CurrentThread.CurrentUICulture = new CultureInfo(0x0409, false); // здесь работаем с Excel'ем, при чем работают английские формулы, DataFormat // и колонтитулы в PageSetup finally { // восстановим пользовательскую культуру для отображения всех данных в // привычных глазу форматах Thread.CurrentThread.CurrentCulture = new CultureInfo(savedCult, true); Thread.CurrentThread.CurrentUICulture = new CultureInfo(savedCult, true); } Русский Excel и установка NumberFormat
How to: Set the Culture and UI Culture for Windows Forms Globalization

Что такое Selection?
Как пишут в Best Practices for Setting Range Properties: Код, использующий Selection, сгенерирован записью макроса Excel, часто используется для обнаружения объекта или метода, который будет работать. Это хорошая идея, за исключением того, что записанный макрос не оптимизирован для пользователя. Обычно Excel при записи макроса использует Selection и изменяет выбор объекта при записи какой либо задачи.
Т.е. использование Selection не является обязательным и даже не рекомендуется для разработчика. Цитата оттуда же: На практике вызывайте метод Select объекта только тогда, когда твердо намерены изменить выбранный пользователем элемент. Вы можете никогда не использовать метод Select просто потому, что это вам удобно, как разработчику.
Если вы устанавливаете свойства объекта Range, у вас всегда есть альтернатива. Отказ от метода Select не только делает ваш код быстрее, но и порадует пользователей вашей программы (it makes your users happier).
Эта тема еще затронута здесь.
Selection Property

Почему не нужно использовать Excel.Application.Range, а следует ExcelWorksheet.Range?
Использование ExcelApplication.Range позволяет работать только с активным листом активной книги. Если вы открываете по ходу работы еще одну книгу или делаете активным другой лист в книге (например, добавляете новый лист), то данные будут вноситься именно в активный в данный момент лист.
Чтоб не попасть в неудобную ситуациюб всегда используйте объект Range объекта ExcelWorksheet. Это не только обезопасит ваш код от попадания куда Бог пошлет, но и позволит записывать данные сразу в несколько листов и даже книг без изменения ActiveSheet и ActiveCell.

Если приложение Excel работает и пользователь выполняет в Excel, одновременно, какие либо действия, то попытка подключится к Excel и вносить в него данные, вызывает ошибку. Как этого избежать?
При работе с запущенным приложением Excel, он может быть занят, если в это время пользователь редактирует значение в ячейке, или в нем открыто какое-либо модальное диалоговое окно (например, Открытие документа). Чтобы обойти эту ситуациюб всегда запускайте новую копию Excel.Application и устанавливайте свойство Interactive в False, что запретит пользователю что-либо делать в Excel'е или закрыть запущенный экземпляр Excel.Application:
Delphi:
XL := TExcelApplication.Create(nil); try XL.ConnectKind := ckNewInstance; XL.Connect; XL.Interactive[lcid] := False; // запрещаем работу пользователю с нашим экземпляром Excel'я XL.Visible[lcid] := True; // работать здесь finally // не забыть разрешить пользователю доступ к Excel'ю! XL.UserControl := True; XL.Interactive[lcid] := True; XL.Disconnect; FreeAndNil(XL); end; The action cannot be completed because Microsoft Office Excel is busy
Interactive Property

Экспорт в Excel длится довольно долго. Как можно уведомлять пользователя о ходе выполнения работы?
Чтобы пользователь не подумалб что во время экспорта данных в Excel ваша программа и Excel висит, лучше уведомлять его о ходе работы. Т.к. обновление экрана занимает довольно много времени и так довольно длительного процесса экспорта (хотя все же стоит подумать, как этот процесс оптимизировать и ускорить), то лучшим выходом из этой ситуации является показ этапов работы в свойстве ExcelApplication.StatusBar:
Delphi:
XL.DisplayStatusBar[lcid] := True; // покажем StatusBar, если его не было видно XL.StatusBar[lcid] := 'Читаем'; // XL.StatusBar[lcid] := 'Пишем'; // XL.StatusBar[lcid] := 'Считаем'; // XL.StatusBar[lcid] := False; // уберем наше последнее сообщение C#:
XL.DisplayStatusBar = true; // XL.StatusBar = Текст в статусбаре; // XL.StatusBar = false; // Готово DisplayStatusBar Property
StatusBar Property

Переношу записанный VBA макрос рисования границы в Excel, но Delphi не знает что такое xlContinuous. Где взять значение этой константы?
Это всего лишь значит, что вы пользуетесь в Delphi поздним связыванием. Добавьте в uses ExcelXP или Excel2000.
Для C# нужно полностью указывать namespace, тип и имя этой константы, например Excel.XlLineStyle.xlContinuous.
Microsoft.Office.Interop.Excel Namespace

Почему я не могу найти описание компонентов палитры Delphi Servers в Help'е? Где найти документацию по работе с Excel'ем?
Компоненты на палитре Servers это обертка (wrapper) популярных COM-серверов Microsoft. Для описания их объектной модели, свойств и методов используйте поставляемый с Microsoft Office VBA Help или ищите информацию на MSDN (также смотрите Полезные ссылки в конце).

Как добавить Chart в отдельный лист книги

Как добавить Chart в отдельный лист книги? Как внедрить Chart в лист книги?
Chart может существовать как отдельный лист книги, так и как внедренный в лист объект ChartObject. Принципиальная разница только в том, ChartObject - это уже объект Shape, содержащий в себе Chart.
Чтобы добавить Chart как отдельный лист книги, нужно воспользоваться методом Add коллекции Sheets.
var oChart: _Chart; oSheet: _Worksheet; ... oChart := XL.Sheets.Add(EmptyParam, oSheet, 1, xlChart, lcid) as _Chart; Внимание! Не пытайтесь добавить Chart, используя коллекцию Charts - вы получите OLE исключение, т.к.
Charts - это коллекция-подмножество Sheets, содержащее ссылки на листы Chart.
Чтоб добавить Chart, как внедренный в лист объект, можно воспользоваться методом Add коллекции листа ChartObjects (здесь существует возможность сразу задать координаты и размеры внедряемого Chart, как будто это объект Shape).
oChart := (oSheet.ChartObjects(EmptyParam, lcid) as ChartObjects).Add( oSheet.Range['B9', EmptyParam].Left, oSheet.Range['B9', EmptyParam].Top + 8, oSheet.Range['I9', EmptyParam].Left - oSheet.Range['B9', EmptyParam].Left, oSheet.Range['B30', EmptyParam].Top - oSheet.Range['B9', EmptyParam].Top).Chart as _Chart; Внимание! При преобразовании Chart в ChartObject (или наоборот) лист Chart (или объект ChartObject) исчезнет (будет утерян), поэтому необходимо всегда получать ссылку на новый объект Chart, возвращаемый функцией Locate.
Charts
Location Method
How to: Add Chart Controls to Worksheets

Как указать источник данных объекту Chart?
Для указания источника данных, можно воспользоваться методом SetSourceData объекта Chart или (мне кажется, что это даже удобнее) методом ChartWizard.
var oRng: ExcelRange; ... oRng := oSheet.Range['E2:H6', EmptyParam]; // Указываем источник данных методом SetSourceData with oChart do begin SetSourceData(oRng, xlColumns); // источник данных и PlotBy ChartType := xl3dColumn; // тип диаграммы end; // Указываем источник методом ChartWizard, в котором можно сразу // задать многие параметры диаграммы oChart.ChartWizard( oRng, // Source: OleVariant; xl3dColumn, // Gallery: OleVariant; EmptyParam, // Format: OleVariant; // данные серий - в колонках xlColumns, // PlotBy: OleVariant; EmptyParam, // CategoryLabels: OleVariant; EmptyParam, // SeriesLabels: OleVariant; True, // HasLegend: OleVariant; 'Sales', // Title: OleVariant; EmptyParam, // CategoryTitle: OleVariant; 'у.е.', // ValueTitle: OleVariant; EmptyParam, // ExtraTitle: OleVariant; lcid); SetSourceData Method
ChartWizard Method

Как получить доступ к ряду (series) Chart?
Например, в цикле присвоим всем сериям новое имя.
for i := 1 to (oChart.SeriesCollection(EmptyParam, lcid) as SeriesCollection).Count do (oChart.SeriesCollection(i, lcid) as Series).Name := Format('=Q%d', [i]); Обратиться к серии (ряду) можно не только по индексу, но и по имени, например
(oChart.SeriesCollection('Q1', lcid) as Series).Interior.Color := RGB(0, 0, 255); SeriesCollection Collection Object
Series Object

Как добавить новый ряд (Series) в Chart?
with (oChart.SeriesCollection(EmptyParam, lcid) as SeriesCollection).NewSeries do begin Values := oSheet.Range['H2', 'H6']; Name := '=Q4'; end; Также можно воспользоваться методом Extend, который расширяет область данных диаграммы.
NewSeries Method
Extend Method

Как изменить цвет серии в Chart?
with (oChart.SeriesCollection(1, lcid) as Series).Interior do begin Color := RGB(0, 0, 255); Pattern := xlSolid; end; Interior Property
Border Property
Fill Property

Как изменять заголовки Chart и его осей?
Например, так:
// Заголовок диаграммы HasTitle[lcid] := True; ChartTitle[lcid].Characters[EmptyParam, EmptyParam].Text := 'Sales'; // Ось Х - заголовка нет (Axes(xlCategory, xlPrimary, lcid) as Axis).HasTitle := False; // Оси Y - заголовок у.е. (Axes(xlValue, xlPrimary, lcid) as Axis).HasTitle := True; (Axes(xlValue, xlPrimary, lcid) as Axis).AxisTitle. Characters[EmptyParam, EmptyParam].Text := 'у.е.'; (Axes(xlValue, xlPrimary, lcid) as Axis).AxisTitle.Orientation := xlUpward; Axes Method

Как назначить подписи значениям оси Х (ось категорий) из области ячеек?
Для отображения подписей оси X предназначено свойство XValues объекта Series.
// значения по категориям (oChart.SeriesCollection(1, lcid) as Series).XValues := oSheet.Range['C2:C6', EmptyParam]; XValues Property

Как убрать легенду из Chart?
Легенду можно просто спрятать, указав, что ее нет. Также вы можете изменять любые атрибуты легенды (Font, Interior и другие), получив доступ к свойству Legend объекта Chart.
oChart.HasLegend[lcid] := False; Legend Object
HasLegend

Как добавить в лист объект Shape и записать в него текст?
Объект Shape - это контейнер для многих других объектов, таких как TextBox, PolyLine, ChartObject и др. Также и свойство Comment объекта Range, по возможностям и набору свойств, можно отнести к Shape.
Все объекты-наследники Shape содержатся в коллекции Shapes листа.
Внимание! Не забудьте явно указать namespace при присвоении типа объекта Shape, т.к. возможен конфликт с объектом Shape namespace Office:
var AShape: ExcelXP.Shape; // или AShape: Excel2000.Shape; Примеры:
// Rectangle AShape := ASheet.Shapes.AddShape( msoShapeRectangle, ASheet.Range['A1', EmptyParam].Width / 2, ASheet.Range['A1', EmptyParam].Height, ASheet.Range['D1', EmptyParam].Left - ASheet.Range['A1', EmptyParam].Width / 2, ASheet.Range['D9', EmptyParam].Top - ASheet.Range['A1', EmptyParam].Height ); AShape.TextFrame.Characters(EmptyParam, EmptyParam).Text := 'Rectangle'; // Oval AShape := ASheet.Shapes.AddShape( msoShapeOval, ASheet.Range['E1', EmptyParam].Left, ASheet.Range['F2', EmptyParam].Top, ASheet.Range['I1', EmptyParam].Left - ASheet.Range['E1', EmptyParam].Left, ASheet.Range['F12', EmptyParam].Top - ASheet.Range['F2', EmptyParam].Top ); AShape.TextFrame.Characters(EmptyParam, EmptyParam).Text := 'Oval'; AShape.TextFrame.HorizontalAlignment := xlCenter; AShape.TextFrame.VerticalAlignment := xlCenter; // TextBox AShape := ASheet.Shapes.AddShape( msoTextOrientationVerticalFarEast, ASheet.Range['B1', EmptyParam].Left, ASheet.Range['B11', EmptyParam].Top, ASheet.Range['E1', EmptyParam].Left - ASheet.Range['B1', EmptyParam].Left, ASheet.Range['B18', EmptyParam].Top - ASheet.Range['B10', EmptyParam].Top ); AShape.TextFrame.Characters(EmptyParam, EmptyParam).Text := 'Rhombus'; AShape.TextFrame.HorizontalAlignment := xlCenter; AShape.TextFrame.VerticalAlignment := xlCenter; // Hedgehog AShape := ASheet.Shapes.AddShape( msoShape32pointStar, ASheet.Range['D1', EmptyParam].Left, ASheet.Range['E19', EmptyParam].Top, ASheet.Range['G1', EmptyParam].Left - ASheet.Range['D1', EmptyParam].Left, ASheet.Range['E30', EmptyParam].Top - ASheet.Range['E19', EmptyParam].Top ); AShape.TextFrame.Characters(EmptyParam, EmptyParam).Text := 'Hedgehog'; AShape.TextFrame.HorizontalAlignment := xlCenter; AShape.TextFrame.VerticalAlignment := xlCenter; AShape.TextFrame.Orientation := msoTextOrientationUpward; // 2 Shapes Collection
Shapes
Shape Object
TextFrame Property
ShapeRange Collection

Как изменить цвет линии и фона Shape?
Delphi:
(AShape.Fill as FillFormat).ForeColor.SchemeColor := 41; (AShape.Line as LineFormat).ForeColor.SchemeColor := 62; C#:
AShape.Fill.ForeColor.SchemeColor = 43; AShape.Line.ForeColor.SchemeColor = 53; Fill Property
Line Property

Как сгруппировать несколько объектов Shape на листе?
// Сгруппируем все объекты Shapes на листе AShape := (ASheet.DrawingObjects(EmptyParam, lcid) as DrawingObjects).ShapeRange.Group; AShape.Name := 'MyGroup1'; // группировка через вариантный массив V := VarArrayCreate([1, 2], varVariant); // !!! // сгруппируем 2-й и 4-й Shape V[1] := 2; V[2] := 4; AShape := ASheet.Shapes.Range[V].Group; AShape.Name := 'MyGroup2'; // Если заранее известно, какие Shape будут сгруппированы, // то можно и так AShape := ASheet.Shapes.Range[VarArrayOf([2, 4])].Group; AShape.Name := 'MyGroup3'; // Теперь, на закуску, через Selection // Выберем 2-й, 3-й и 4-й Shape ASheet.Shapes.Item(2).Select(True); ASheet.Shapes.Item(3).Select(False); ASheet.Shapes.Item(4).Select(False); // сгруппируем выделенные Shape AShape := (XL.Selection[lcid] as DrawingObjects).ShapeRange.Group; AShape.Name := 'MyGroup4'; // Разгруппировать AShape.Ungroup; Заметьте, что после того, как объекты Shape были сгруппированы, их количество на листе (Shapes.Count) уменьшится. После группировки можно обратиться к отдельному Shape, включенному в группу, используя коллекцию GroupItems объекта Shape, полученного в результате группировки.
Group Method
Regroup Method
Ungroup Method
GroupItems Property

Как вставить в лист картинку?
var AShape: Excel_TLB.Shape; APic: Picture; // Excel_TLB Img: TImage; // ExtCtrls MyFormat: Word; // для копирования картинки в БО AData: THandle; // для копирования картинки в БО APalette: HPALETTE; // для копирования картинки в БО ... // Первый метод // Вставляем картинку из файла с нулевым размером в // позицию ячейки B2. Нулевой размер потому, что // мы не знаем заранее размера картинки with ASheet.Range['B2', EmptyParam] do AShape := ASheet.Shapes.AddPicture(ExpandFileName('common.gif'), msoFalse, msoTrue, Left, Top, 0, 0); // После вставки получаем ссылку на объект Shape, содержащий // нашу картинку.
Реанимируем оригинальный размер картинки AShape.ScaleHeight(1, msoTrue, EmptyParam); AShape.ScaleWidth(1, msoTrue, EmptyParam); AShape.Name := 'Picture1'; // Второй метод - вставляем в выделенную ячейку ASheet.Range['C7', EmptyParam].Select; APic := (ASheet.Pictures(EmptyParam, lcid) as Pictures).Insert(ExpandFileName('common.gif'), EmptyParam); APic.Name := 'Picture2'; // Третий метод - вставка через буфер обмена // Загрузим картинку с диска в TImage Img.Picture.LoadFromFile(ExpandFileName('common.bmp')); MyFormat := CF_PICTURE; AData := Img.Picture.Bitmap.Handle; APalette := Img.Picture.Bitmap.Palette; // и помещаем в буфер обмена Img.Picture.SaveToClipboardFormat(MyFormat, AData, APalette); Clipboard.SetAsHandle(MyFormat, AData); // из буфера обмена вставим в ячейку D12 ASheet.Paste(ASheet.Range['D12', EmptyParam], EmptyParam, lcid); Все добавленные картинки будут включены в коллекцию Shapes листа.
AddPicture Method
Worksheet.Pictures Method
Paste Method

Как изменить размер картинки и вписать ее в размер определенной области ячеек?
Если пытаться изменять размер картинки, то она, по умолчанию, будет менять размер пропорционально. Такую особенность задает свойство LockAspectRatio объекта Shape.
Если установить LockAspectRatio равным msoFalse, то картинка будет менять размеры непропорционально.
// Картинка будет вписана в область D12:J18 с сохранением пропорций AShape.Width := ASheet.Range['K12', EmptyParam].Left - ASheet.Range['D12', EmptyParam].Left; AShape.Height := ASheet.Range['D19', EmptyParam].Top - ASheet.Range['D12', EmptyParam].Top; // Картинка будет вписана во всю область D12:J18 без сохранения пропорций AShape.LockAspectRatio := msoFalse; AShape.Width := ASheet.Range['K12', EmptyParam].Left - ASheet.Range['D12', EmptyParam].Left; AShape.Height := ASheet.Range['D19', EmptyParam].Top - ASheet.Range['D12', EmptyParam].Top; LockAspectRatio Property

Как изменить яркость или контрастность картинки?
(AShape.PictureFormat as PictureFormat).Brightness := 0.25; // 25% яркости (AShape.PictureFormat as PictureFormat).Contrast := 0.75; // 75% контрастность PictureFormat Property

Как добавить картинке гиперссылку?
Смотрите Как добавить URL? Как сделать гиперссылку для рисунка?
Hyperlink Property

Как добавить новую книгу

Как добавить новую книгу?
Добавить новую пустую книгу:
Delphi:
XL.Workbooks.Add(EmptyParam, lcid); C#:
XL.Workbooks.Add(Type.Missing); В первом параметре метода Add можно указать стандартный тип шаблона Excel. Если же в нем указать имя (с полным путем) подготовленного файла (шаблоном может быть и обычный файл XLS, а не только файл XLT), то можно открыть книгу на диске как шаблон.
Delphi:
XL.Workbooks.Add('MyTemplate.xls', lcid); Откроет файл MyTemplate1.xls, т.е. точно как обычный шаблон Книга1.xls, но свой со своим форматированием, что позволит ускорить процесс экспорта данных в Excel, т.к. не придется форматировать ячейки и вызывать другие настройки листа.
Add Method (Workbooks Collection)
How to: Create New Workbooks

Как задать количество листов в новой книге?
Задать количество листов в новой книге можно перед добавлением новой книги:
Delphi:
XL.SheetsInNewWorkbook[lcid] := N; // N - новое количество листов (integer) XL.Workbooks.Add(EmptyParam, lcid); C#:
XL.SheetsInNewWorkbook = N; Где N = 1..255
SheetsInNewWorkbook Property

Как открыть книгу, имеющуюся на диске?
Если книга находится не в папке, указанной в Excel.Application.DefaultFilePath, то нужно указывать полный путь к открываемому файлу .xls, даже если файл находится в текущей папке вашего приложения, т.к. Excel ничего про него не знает.
Delphi:
WB.ConnectTo(XL.Workbooks.Open( 'МояКнига.xls', // Filename: WideString; 2, // UpdateLinks: OleVariant; 2 - never update False, // ReadOnly: OleVariant; EmptyParam, // Format: OleVariant; EmptyParam, // Password: OleVariant; EmptyParam, // WriteResPassword: OleVariant; EmptyParam, // IgnoreReadOnlyRecommended: OleVariant; EmptyParam, // Origin: OleVariant; EmptyParam, // Delimiter: OleVariant; EmptyParam, // Editable: OleVariant; EmptyParam, // Notify: OleVariant; EmptyParam, // Converter: OleVariant; False, // AddToMru: OleVariant; EmptyParam, // Local: OleVariant; EmptyParam, // CorruptLoad: OleVariant; lcid)); C#:
XL.Workbooks.Open( Книга1.xls, // FileName false, // UpdateLinks false, // ReadOnly Type.Missing, // Format Type.Missing, // Password Type.Missing, // WriteResPassword Type.Missing, // IgnoreReadOnlyRecommended Type.Missing, // Origin Type.Missing, // Delimiter true, // Editable Type.Missing, // Notify Type.Missing, // Converter false, // AddToMru Type.Missing, // Local Type.Missing // CorruptLoad ); Open Method
How to: Open Workbooks

Как сохранить книгу?
Save Method
SaveAs Method
How to: Save Workbooks

Как закрыть книгу без вопросов о сохранении? Как закрыть все книги?
Delphi:
// закрыть книгу без сохранения внесенных в нее изменений oWorkBook.Close(0); // xlDontSaveChanges // закрыть все книги без сохранения изменений XL.DisplayAlerts[lcid] := False; // отключаем предупреждения XL.Workbooks.Close(lcid); // закроем все книги Close Method
How to: Close Workbooks

Как узнать имена всех открытых книг?
Узнать имена всех книг экземпляра Excel.Application можно в цикле, например, так:
Delphi:
for i := 1 to XL.Workbooks.Count do XL.Assistant.DoAlert('Книга', XL.Workbooks[i].Name, msoAlertButtonOK, msoAlertIconInfo, msoAlertDefaultFirst, msoAlertCancelDefault, False); // а вот так имя книги с полным путем к ней (свойство FullName) for i := 1 to XL.Workbooks.Count do XL.Assistant.DoAlert('Книга', XL.Workbooks[i].FullName[lcid], msoAlertButtonOK, msoAlertIconInfo, msoAlertDefaultFirst, msoAlertCancelDefault, False); C#:
int R = 0; foreach (Excel.Workbook WB in XL.Workbooks) { oSheet.get_Range(A1, Type.Missing).get_Offset(R, 0).Formula = WB.FullName; R++; }
Как найти определенную открытую книгу?
Точно так же, как в предыдущем ответе по имени в свойстве Name. Если вы хотите сделать найденную книгу активной, то вызовите метод Activate
Name Property
Activate Method

Как переименовать книгу?
Переименовать книгу никак нельзя только сохранить под другим именем методом SaveAs (смотрите Как сохранить книгу).

Как спрятать книгу?
Delphi:
// спрячем активную книгу XL.ActiveWindow.Visible := False; Windows Collection Object
Window Object

Как открыть текстовый файл в Excel'е?
Практически, так же как и обычную книгу, только внимательно указав дополнительные параметры в методе OpenText.
OpenText Method
How to: Open Text Files as Workbooks

Как узнать имена всех листов в книге и их количество?
Узнать количество листов в книге можно в цикле по коллекции Workbook.Sheets. Количество листов свойство Sheets.Count.
Имя листа свойство Worksheet.Name.
Count Property
Name Property
How to: List All Worksheets in a Workbook

Как получить ссылку на активный лист в активной книге?
Обращаеясь к Excel.Application.ActiveSheet или WorkBook.ActiveSheet, вы получите ссылку на интерфейс IDispatch. Это происходит из-за того, что коллекция Excel.Application.Sheets может содержать объекты WorkSheet, Chart, Excel4MacroSheet (для поддержки Excel 4).
Delphi:
// получить ссылку на активный лист, ASheet: TExcelWorksheet ASheet.ConnectTo(XL.ActiveSheet as _Worksheet); // получить ссылку на второй лист активной книги ASheet.ConnectTo(XL.ActiveWorkbook.Sheet[2] as _Worksheet); C#:
Excel.Worksheet oSheet = (Excel.Worksheet) XL.ActiveSheet; // Excel.Worksheet oSheet = (Excel.Worksheet) XL.Sheets[1]; Определить тип листа можно, проверив свойство Worksheet.Type:
Delphi:
if ASheet.type_[lcid] = xlWorksheet then { это Worksheet }; C#:
if (oSheet.Type == Excel.XlSheetType.xlWorksheet) /* это Worksheet */ ; ActiveWorkbook Property
ActiveSheet Property
Type Property

Как добавить новый лист в книгу? Как удалить лист?
При добавлении можно указать тип нового листа (WorkSheet, Chart, Excel4MacroSheet) и текущее положение. Добавленный лист будет активизирован автоматически (на него будет указывать свойство ActiveSheet)
// Добавим один новый лист после текущего ASheet.ConnectTo(XL.ActiveWorkbook.Sheets.Add(EmptyParam, XL.ActiveSheet, 1, xlWorksheet, lcid)); // Удалить лист XL.DisplayAlerts[lcid] := False; // отключим предуреждения (XL.ActiveSheet as _Worksheet).Delete(lcid); // удаляем активный (можно любой) лист Add Method
Delete Method
How to: Add New Worksheets to Workbooks
How to: Delete Worksheets from Workbooks

Нужно ли делать лист активным, чтобы записать в него данные?
Не нужно переключение (активация) листов только замедлит экспорт данных. Получите ссылку на любой лист в книге (активной или нет) и работайте c ней, как с активной.
Активизировать лист нужно только в случае необходимости, например, при вставке из буфера обмена, предварительном просмотре и др.

Как задать имя листу в книге?
// ASheet: ExcelWorksheet; ASheet.ConnectTo(XL.ActiveSheet as _Worksheet); ASheet.Name := 'Новое имя'; Name Property

Как выделить один или несколько листов в книге?
Delphi:
// выделить один лист (XL.Sheets[1] as _Worksheet).Select(True, lcid); // выделим сразу несколько листов в цикле for i := 1 to 4 do begin (XL.Sheets[i] as _Worksheet).Select(False, lcid); Application.ProcessMessages; end; // листы можно выделить не в цикле, указав список выделяемых // листов (индексы или имена) как массив (XL.ActiveWorkbook.Sheets[VarArrayOf([1, 2, 3, 4])] as Sheets).Select(False, lcid); // Select - выделить, Activate - активировать без снятия выделения, // если активировать лист в выделенном диапозоне (XL.Sheets[4] as _Worksheet).Activate(lcid); // Если попробовать активировать 5 лист (не в выделенном диапозоне), // то выделенным окажется только один лист (XL.Sheets[5] as _Worksheet).Activate(lcid); Select Method
Activate Method
How to: Select a Worksheet

Как скопировать/переместить лист в одной книге? В другую книгу?
Delphi:
// скопировать лист в конец ASheet.Copy(EmptyParam, XL.ActiveWorkbook.Sheets[XL.ActiveWorkbook.Sheets.Count]); // переместим лист перед Лист4 ASheet.Move(XL.ActiveWorkbook.Sheets[4], EmptyParam); // переместить лист в другую книгу в конец. // Для копирования то же, только вместо Move вызвать метод Copy ASheet.Move(EmptyParam, XL.Workbooks[2].Sheets[XL.Workbooks[2].Sheets.Count]); Copy Method
Move Method
How to: Move Worksheets Within Workbooks

Как задать/убрать область печати? Как вызвать PrintPreview?
Как напечатать лист?
Delphi:
// Зададим область печати A1:D10 ASheet.PageSetup.PrintArea := '$A$1:$D$10'; // Удаляем область печати, присвоив пустую строку ASheet.PageSetup.PrintArea := ''; // Вызов PrintPreview ASheet.PrintPreview; // Печать на любой принтер (в примере на PDFCreator) // Если на активный принтер, то вместо имени принтера указать EmptyParam ASheet.DefaultInterface.PrintOut( EmptyParam, // From: OleVariant; EmptyParam, // To_: OleVariant; 1, //Copies: OleVariant; EmptyParam, // Preview: OleVariant; 'PDFCreator', // ActivePrinter: OleVariant; EmptyParam, // PrintToFile: OleVariant; True, //Collate: OleVariant; EmptyParam, //PrToFileName: OleVariant; lcid ); PrintArea Property
PrintOut Method
PrintPreview Method
How to: Print Worksheets

Как установить параметры печати: отступы на листе, ориентацию листа и др.?
Установка параметров печати довольно продолжительный процесс, поэтому советую настроить их в предварительно подготовленном шаблоне. Все параметры печати задаются в свойстве PageSetup объекта Worksheet.
Но учтите, что текст в свойствах Footer или Header для Left, Right, Center суммарно не должен превышать 255 символов.
Для задания отступов в сантиметрах используйте функцию Excel.Application.CentimetersToPoints
Delphi:
ASheet.Range['A1', EmptyParam].Formula := 'Любой текст, чтоб сработал PrintPreviw'; ASheet.PageSetup.LeftMargin := 0; ASheet.PageSetup.TopMargin := 0; ASheet.PageSetup.RightMargin := 0; ASheet.PageSetup.BottomMargin := 0; ASheet.PageSetup.HeaderMargin := 0; ASheet.PageSetup.FooterMargin := 0; ASheet.PageSetup.FitToPagesWide := 1; ASheet.PageSetup.FitToPagesTall := 1; ASheet.PrintPreview(True, lcid); Если вы выполните данный код, то заметите, как долго Excel настраивает все границы печати.
PageSetup Property
CentimetersToPoints Method

Как установить свои разрывы страниц (HPageBreaks) в области печати? Как узнать номер строки HpagePreak?
Для того, чтобы появились автоматические разрывы страниц, нужно перейти в режим разметки.
Delphi:
// переходим в режим разметки, чтобы заполнить коллекцию HPageBreaks XL.ActiveWindow.View := xlPageBreakPreview; // вставим разрыв перед 4-й строкой ASheet.HPageBreaks.Add(ASheet.Range['A4', EmptyParam]); // Как узнать номер строки, перед которой вставлен первый (индекс 1) HPageBreak FirstPageBreak := ASheet.HPageBreaks[1].Location.Row; Также вы можете переместить разрыв, присвоив новое значение свойству Location объекта HPageBreak
Delphi:
// переместим разрыв перед пятой строкой ASheet.HPageBreaks[1].Location := Asheet.Range ['A5', EmptyParam]; View Property
HPageBreaks Collection Object
VPageBreaks Collection Object
Add Method

Как сделать так, чтобы на каждой странице повторялись заголовки колонок таблицы?
Нужно задать сквозные строки заголовка таблицы.
Delphi:
// сквозная вторая строка ASheet.PageSetup.PrintTitleRows := '2:2'; PageSetup Property

Как спрятать рабочий лист?
Delphi:
ASheet.Visible[lcid] := xlSheetHidden; Visible Property
How to: Hide Worksheets

Как установить пароль на существующий лист/книгу?
Protect Method
Unprotect Method
How to: Protect Workbooks
How to: Protect Worksheets
How to: Remove Protection from Worksheets

Как создать макрос из Delphi? Как выполнить макрос, имеющийся в книге?
Вам не удастся создать макрос программно, т.к. по умолчанию в Excel VBA Project отключен доступ к VBA из программ. Как включить эту возможность, читайте PRB: Programmatic Access to Office XP VBA Project Is Denied
Пример создания макроса с параметром и вызов его из программы:
Delphi:
try VBComp := WB.VBProject.VBComponents.Add(1); // vbext_ct_StdModule VBComp.Name := 'TestModule'; // запишем туда наш макрос VBComp.CodeModule.AddFromString( 'Public Sub Test(ByVal ADate As Date)'#10 + #9'MsgBox Привет из VBA! Chr(13) Сегодня ADate, _'#10 + #9#9'vbInformation, Test макроса'#10 + 'End Sub' ); except ShowMessage('Нужно включить вручную в Excel''е Доверять доступ к Visual Basic Project'); end; // выполним макрос XL.Run('Test', Date()); C#:
Microsoft.Vbe.Interop.VBComponent VBComp = XL.ActiveWorkbook.VBProject.VBComponents.Add( Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule); VBComp.Name = TestModule; // запишем туда наш макрос VBComp.CodeModule.AddFromString( Public Sub Test(ByVal ADate As Date)\n + \tMsgBox \Привет из VBA!\ Chr(13) \Сегодня \ ADate, _\n + \t\tvbInformation, \Test макроса\\n + End Sub); // и выполним его передав текущую дату XL.Run(Test, DateTime.Today, 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, 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, Type.Missing ); Если все же вам очень нужно добавить макрос, независимо от настроек доступа к VBA Project, то можно воспользоваться листом макросов xlExcel4MacroSheet. Макроязык представляет собой команды.
Delphi:
ASheet := WB.Sheets.Add(EmptyParam, WB.Sheets[WB.Sheets.Count], 1, xlExcel4MacroSheet, lcid) as _Worksheet; ASheet.Range['A1', EmptyParam].Formula := '=alert(Привет из Excel4 MacroSheet!)'; ASheet.Range['A2', EmptyParam].Formula := '=return()'; ASheet.Range['A1', EmptyParam].Run( EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam ); Turn on or off Trust access to Visual Basic Project
How To Create and Call an Excel Macro Programmatically from VB
Programming To The Visual Basic Editor
Run Method

Будет ли работать макрос при выставленной Очень высокая безопасность макросов в Excel'е пользователя?
Записанные макросы в книге, открытой вашей программой всегда будут работать, независимо от выставленного в Excel'е Уровня безопасности для макросов.

Почему не работает макрос, записанный в книге?
Записанный в книге макрос может не работать по причине установленного антивируса. Например, установленный Kaspersky Office Guard, входящий в состав Антивирус Касперского, начисто отключает все вызовы VBA.

Как получить данные из ADODataSet

Как получить данные из ADODataSet?
Если у вас есть открытый RecordSet (свойство всех наследников TCustomADODataSet), то из него в любую ячейку листа можно получить данные. В примере данные из ADODataSet1 будут вставлены в область, начиная с ячейки A2.
Delphi:
ASheet := (XL.Sheets[1] as _Worksheet); ASheet.Range['A2', EmptyParam].CopyFromRecordset( ADODataSet1.Recordset, EmptyParam, EmptyParam ); C#:
ADODB._Recordset objRS = null; object objRecAff = null; ADODB.Connection objConn = new ADODB.Connection(); // Excel Excel.Worksheet oSheet = null; Excel.Range oRng = null; Excel.Application XL = new Excel.Application(); try { XL.Visible = true; objConn.Open(Provider=\Microsoft.Jet.OLEDB.4.0\;Data Source=\ + Environment.GetFolderPath(Environment.SpecialFolder.CommonProgramFiles) + \\Borland Shared\\Data\\dbdemos.mdb\;Persist Security Info=False;, , , 0); objRS = (ADODB._Recordset) objConn.Execute(employee, out objRecAff, (int) ADODB.CommandTypeEnum.adCmdTable); XL.Workbooks.Add(Type.Missing); oSheet = (Excel.Worksheet) XL.ActiveSheet; oRng = oSheet.get_Range(A1, Type.Missing); for (int i = 0; i objRS.Fields.Count; i++) { oRng.get_Offset(0, i).Value2 = objRS.Fields[i].Name; } oRng = oSheet.get_Range(A2, Type.Missing); oRng.CopyFromRecordset(objRS, Type.Missing, Type.Missing); oRng = oSheet.UsedRange.EntireColumn; oRng.AutoFit(); XL.ActiveWorkbook.Saved = true; } finally { objConn.Close(); objConn = null; objRS = null; objRecAff = null; oRng = null; oSheet = null; XL.UserControl = true; XL = null; } Т.к. в ADO.NET не существует такого объекта как RecordSet, то в C# приходится подключаться к старому знакомому ADODB (не забудьте добавить в References проекта Microsoft ActiveX Data Objects 2.8 Library на вкладке COM Imports).
How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET
CopyFromRecordset Method
How To Transfer Data from an ADO Recordset to Excel with Automation
Range.CopyFromRecordset Method

Как получить данные в Excel, используя QueryTable?
Delphi:
var AQueryTbl: ExcelQueryTable; ... XL := StartExcel; try ASheet := (XL.Sheets[1] as _Worksheet); R := ASheet.Range['A1', EmptyParam]; // начальная ячейка для данных AQueryTbl := ASheet.QueryTables.Add( 'OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;' + 'Data Source=' + ExpandFileName('DbDemos.xls') + ';' + 'Extended Properties=Excel 8.0;;', R, 'select * from [employee$]' ); AQueryTbl.RefreshStyle := xlInsertEntireRows; AQueryTbl.Refresh(False); C#:
Excel.Worksheet oSheet = null; Excel.Range oRng = null; Excel.QueryTable oQT = null; Excel.Application XL = new Excel.Application(); try { XL.Visible = true; XL.Workbooks.Add(Type.Missing); oSheet = (Excel.Worksheet) XL.ActiveSheet; oRng = oSheet.get_Range(A1, Type.Missing); oQT = (Excel.QueryTable) oSheet.QueryTables.Add( OLEDB;Provider=\Microsoft.Jet.OLEDB.4.0\;Data Source=\ + Environment.GetFolderPath(Environment.SpecialFolder.CommonProgramFiles) + \\Borland Shared\\Data\\dbdemos.mdb\;Persist Security Info=False;, oRng, select * from Employee ); oQT.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; oQT.Refresh(false); XL.ActiveWorkbook.Saved = true; } finally { oQT = null; oRng = null; oSheet = null; XL.UserControl = true; XL = null; } После получения данных, в книгу будет добавлена именованная область ячеек, содержащая импортированную таблицу.
QueryTables Property
Add Method
QueryTable Object

Как подключить книгу Excel как базу данных, используя поставщика данных Jet OLE DB Provider?
Для подключения книги Excel как базы данных нужно воспользоваться Microsoft Jet OLE DB провайдером и указать в свойстве соединения Extended Properties=Excel 8.0.
Delphi:
const ConStr = 'Provider=Microsoft.Jet.OLEDB.4.0;' + 'Data Source=%s;' + 'Extended Properties=Excel 8.0;HDR=Yes;;'; var Conn: TADOConnection; ... Conn.ConnectionString := Format(ConStr, [ExpandFileName('DbDemos.xls')]); Conn.Open; C#:
System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(); oConn.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Environment.CurrentDirectory + @\DbDemos.xls; + Extended Properties=\Excel 8.0;HDR=Yes;\;; oConn.Open; ADO Provider Properties and Settings
Connect to Excel with ADO
ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks
HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases
How To Transfer Data from ADO Data Source to Excel with ADO
OLE DB Tutorial (C# Programmer's Reference)

Как получить данные из таблицы (листа) Excel SQL запросом?
Данные из листа Excel, подключенного через ADO, можно показать в DBGrid, добавлять, править, удалять строки. Используем подключение из предыдущего примера.
Delphi:
ADODataSet1.Connection := Conn; ADODataSet1.CommandText := 'select * from [Лист1$]'#10 + 'where [HireDate] = #01/01/1994#'; ADODataSet1.Open; // Можно использовать именованную область ячеек, названную MyRange ADODataSet1.CommandText := 'select * from MyRange'; ADODataSet1.Open; // Возможно использовать указаную область ячеек ADODataSet1.ParamCheck := False; // ОБЯЗАТЕЛЬНО, иначе exception!!! ADODataSet1.CommandText := 'select * from [Лист1$A12:F42]'; C#:
System.Data.OleDb.OleDbCommand oCmd = new System.Data.OleDb.OleDbCommand(); System.Data.OleDb.OleDbDataAdapter oAdapt = new System.Data.OleDb.OleDbDataAdapter(); System.Data.DataSet oDS = new System.Data.DataSet(DbDemos); oCmd.Connection = oConn; // Выборка данных из одной таблицы (листа) с именем Employee oCmd.CommandText = select * from [Employee$]; // Выборка данных из именованной области ячеек c именем MyRange oCmd.CommandText = select * from MyRange; // Выборка из заданной области ячеек oCmd.CommandText = select * from [Employee$A10:F40]; // Выборка из 3 связанных таблиц (листов) книги oCmd.CommandText = SELECT O.OrderNo, O.SaleDate, O.PaymentMethod, O.ItemsTotal,\n + E.FirstName, E.LastName, C.Company\n + FROM [customer$] AS C\n + INNER JOIN ([employee$] AS E\n + INNER JOIN [orders$] AS O ON E.EmpNo = O.EmpNo)\n + ON C.CustNo = O.CustNo; // Получам данные в DataSet посредством OleDbDataAdapter'а oAdapt.SelectCommand = oCmd; oAdapt.Fill(oDS, Employee); // Покажем данные в DataGrid'е dataGrid1.DataSource = oDS.Tables[Employee]; Внимание! Если данные были получены в DataSet из именованной области ячеек, то, при попытке добавить новые строки, вы получите исключение Cannot expand named range.
Если данные были получены с указанием области ячеек, то новые записи будут добавляться после последней строки диапазона, но, если будет вызван метод Requey, эти новые строки не будут включены в DataSet.
Retrieve and Edit Excel Data with ADO
How to query and display excel data by using ASP.NET, ADO.NET, and Visual C# .NET
You receive error messages when you try to use ADO.NET OLEDbDataAdapter to modify an Excel workbook
How To Use ADO with Excel Data from Visual Basic or VBA

Как вставить данные из таблицы Access в таблицу Excel, используя ADO?
Заметьте, что при вставке данных таблица в Excel должна быть уже предварительно создана, т.е. должен быть лист с именем, например, Country и в первой строке листа заданы имена полей таблицы.
Delphi:
const InsCmd = 'insert into [Country$]'#10 + // Country - имя листа в книге 'select * from Country in %s'; // Country - имя таблицы в var Conn: TADOConnection; ... Conn.Execute(Format(InsCmd, ['dbdemos.mdb'])); INSERT INTO Statement
IN Clause

Какие существуют ограничения при использовании книги Excel как БД?

Размер листа (таблицы): 65 536 строк на 256 столбцов;
Содержимое ячейки (текст): 32 767 символов;
Листов в книге: ограничено доступной памятью;
Имен в книге: ограничено доступной памятью.

Хотя книга Excel и может выступать в качестве базы данных, не стоит думать, что это полноценная БД. Таблицы там голые - ни индексов, ни триггеров, ни хранимых процедур и других возможностей стандартных баз данных.
Также велика вероятность неправильного определения типа поля таблицы при открытии ее в DataSet при помощи ADO.
Огромная благодарность Елене Филипповой за помощь и поддержку при написании FAQ.

Как сделать автоперенос строк в ячейке

Font Property
Interior Property
How to: Change Formatting in a Row that Contains a Selected Cell

Как изменить атрибуты шрифта части текста в ячейке (цвет, размер, имя)?
Чтобы изменить часть текста ячейки можно воспользоваться свойством Characters объекта Range.
Delphi:
Msg: String; ... Msg := ' Человек собаке друг J'; // занесем тест в ячейку ASheet.Range['B3', EmptyParam].Formula := Msg; // займемся последним символом - изменим атрибуты шрифта ASheet.Range['B3', EmptyParam].Characters[Length(Msg), 1].Font.Name := 'Wingdings'; ASheet.Range['B3', EmptyParam].Characters[Length(Msg), 1].Font.Size := 24; ASheet.Range['B3', EmptyParam].Characters[Length(Msg), 1].Font.Color := clBlue; Characters Property

Как узнать позицию курсора в редактируемой по F2 ячейке или в строке формул и дописать в нее текст?
Никак! При входе в режим редактирования ячейки объект Excel.Application становится полностью недоступен через OLE.
XL97: Error Printing Microsoft Excel Section in a Binder

Как изменить выравнивание/угол наклона текста, отступы в ячейке?
Смотрите свойства HorizontalAlignment, VerticalAlignment, AddIndent и Orientation объекта Range
Delphi:
ASheet.Range['B2', EmptyParam].HorizontalAlignment := xlLeft; ASheet.Range['B2', EmptyParam].VerticalAlignment := xlCenter; ASheet.Range['B2', EmptyParam].Orientation := 45; // 45 градусов // подберем ширину столбца ASheet.Range['B:B', EmptyParam].Columns.AutoFit; // ASheet.Range['B2', EmptyParam].EntireColumn.AutoFit; HorizontalAlignment Property
VerticalAlignment Property
Orientation Property
AddIndent Property
IndentLevel Property

Как задать границы для области ячеек (Borders)?
Смотрите свойство Borders объекта Range.
Delphi:
// нарисуем рамку вокруг B2 ASheet.Range['B2', EmptyParam].BorderAround( xlContinuous, xlThick, xlColorIndexAutomatic, EmptyParam ); // нарисуем границу сверху ASheet.Range['A7:А7', EmptyParam].Borders[xlEdgeTop].LineStyle := xlContinuous; ASheet.Range['A7:E7', EmptyParam].Borders[xlEdgeTop].Weight := xlMedium; Перенос VBA-макросов в Delphi
Borders Property
BorderAround Method

Как скопировать форматы и формулы из строки в нижележащую область (AutoFill)?
Это как раз самый удобный метод копирования форматов и формул для расширения области данных при использовании шаблонов. Подразумевается, что между НАЧАЛО/КОНЕЦ находятся подготовленные ячейки шаблона (форматирование, именованная область DataRange для данных).
Delphi:
// НАЧАЛО ШАБЛОНА // шапка ASheet.Range['A1', EmptyParam].Formula := 'Шапка'; // таблица ASheet.Range['A2', EmptyParam].Formula := '#'; ASheet.Range['B2', EmptyParam].Formula := 'Имя'; ASheet.Range['C2', EmptyParam].Formula := 'Кол-во'; ASheet.Range['D2', EmptyParam].Formula := 'Цена'; ASheet.Range['E2', EmptyParam].Formula := 'Сумма'; ASheet.Range['A2:E2', EmptyParam].BorderAround( xlContinuous, xlHairline, xlColorIndexAutomatic, EmptyParam ); // сделаем вид, что у нас уже готова строка шаблона данных // и зададим форматы и формулы ASheet.Range['A3', EmptyParam].Font.Bold := True; ASheet.Range['C3', EmptyParam].Formula := '=round(rand()*10+1,0)'; ASheet.Range['D3', EmptyParam].Formula := '=round(rand()*100,2)'; // в случаях формул удобно использовать стил R1C1 ASheet.Range['E3', EmptyParam].FormulaR1C1 := '=round(RC[-1]*RC[-2],2)'; ASheet.Range['E3', EmptyParam].NumberFormat := '#,##0.00'; // пустая строка для того, чтоб сумма считалась автоматом ASheet.Range['A4', EmptyParam].EntireRow.Hidden := True; // добавим итоговую сумму (с пустой строкой) ASheet.Range['E5', EmptyParam].FormulaR1C1 := '=sum(R[-1]C:R[-2]C)'; ASheet.Range['E5', EmptyParam].Font.Bold := True; ASheet.Range['A5:E5', EmptyParam].Borders[xlEdgeTop].LineStyle := xlContinuous; ASheet.Range['A5:E5', EmptyParam].Borders[xlEdgeTop].Weight := xlMedium; // области данных присвоим имя (ASheet.Parent as ExcelWorkbook).Names.Add( 'DataRange', // Name, ASheet.Range['A3:E3', EmptyParam], // RefersTo: OleVariant; True, // Visible: OleVariant; EmptyParam, // MacroType: OleVariant; EmptyParam, // ShortcutKey: OleVariant; EmptyParam, // Category: OleVariant; EmptyParam, // NameLocal: OleVariant; EmptyParam, // RefersToLocal: OleVariant; EmptyParam, // CategoryLocal: OleVariant; EmptyParam, // RefersToR1C1: OleVariant; EmptyParam // RefersToR1C1Local: OleVariant ); ASheet.Range['DataRange', EmptyParam].Borders[xlEdgeBottom].LineStyle := xlContinuous; ASheet.Range['DataRange', EmptyParam].Borders[xlEdgeBottom].Weight := xlHairline; // КОНЕЦ ШАБЛОНА // Начало работы с шаблоном // Добавим 4 строки для занесения данных (итого уже 5 строк для данных) // Неудобство при использовании Cells в Range - обязательное // дублирование Cells во втором параметре ASheet.Range[ ASheet.Cells.Item[ASheet.Range['DataRange', EmptyParam].Row + 1, 1], ASheet.Cells.Item[ASheet.Range['DataRange', EmptyParam].Row + 4, 1] ].EntireRow.Insert(xlShiftDown, EmptyParam); // теперь заполним область форматированием, захватив (ОБЯЗАТЕЛЬНО) // и область-шаблон DataRange ASheet.Range['DataRange', EmptyParam].AutoFill( ASheet.Range[ // захватим область источника ASheet.Cells.Item[ASheet.Range['DataRange', EmptyParam].Row, 1], ASheet.Cells.Item[ASheet.Range['DataRange', EmptyParam].Row + 5, ASheet.Range['DataRange', EmptyParam].Columns.Count] ], xlFillCopy ); // заносим данные из массива (номер и имя) - 5 строк, 2 столбца arrData := VarArrayCreate([1, 5, 1, 2], varVariant); for i := 1 to 5 do begin arrData[i, 1] := i; arrData[i, 2] := Format('Имя %d', [i]); end; ASheet.Range[ ASheet.Cells.Item[ASheet.Range['DataRange', EmptyParam].Row, 1], ASheet.Cells.Item[ASheet.Range['DataRange', EmptyParam].Row + 5, 2] ].Formula := arrData; AutoFill Method
How to: Copy Data and Formatting across Worksheets

Как скопировать область ячеек с сохранением всех форматов? Как скопировать только значения ячейки?
Метод Copy позволяет не только копировать содержимое области ячеек в буфер обмена (при пустом параметре), но и задать конкретный адрес ячеек для копирования. Если вы хотите вставить из буфера только некоторые параметры скопированной в БО ячейки, то для вставки используйте метод PasteSpecial, указав необходимый XlPasteType (первый аргумент).
Delphi:
R := ASheet.Range['A1', EmptyParam]; // скопируем ячейку A1 в C3 - напрямую в ячейку R.Copy(ASheet.Range['C3', EmptyParam]); // текущий лист // в соседний лист R.Copy((XL.Sheets[2] as _Worksheet).Range['C3', EmptyParam]); // скопируем через буфер обмена R.Copy(EmptyParam); // поместим в БО // вставим в ячейку C3 в текущем листе ASheet.Paste(ASheet.Range['C5', EmptyParam], EmptyParam, lcid); // в соседний лист (XL.Sheets[2] as _Worksheet).Paste( (XL.Sheets[2] as _Worksheet).Range['C5', EmptyParam], EmptyParam, lcid); // вставляем только значение ячейки без форматирования ASheet.Range['C7', EmptyParam].PasteSpecial(xlPasteValues, xlPasteSpecialOperationNone, False, False); Copy Method
PasteSpecial Method
Paste Method
CutCopyMode Property

Как скопировать область, чтобы сохранились размеры строк/столбцов?
К сожалению, при копировании не сохраняются размеры строк и столбцов. Для сохранения размеров строк и столбцов можно использовать несколько способов:
Delphi:
// способ первый - использование метода PasteSpecial // скопируем область ячеек в буфер обмена R.Copy(EmptyParam); // поместим в БО // вставим в C3 - ширина колонки не изменилась ASheet.Paste(ASheet.Range['C5', EmptyParam], EmptyParam, lcid); // специальная свтавка с XlPasteType = xlPasteColumnWidths ASheet.Range['C5', EmptyParam].PasteSpecial(xlPasteColumnWidths, xlPasteSpecialOperationNone, False, False); // второй способ - обращение к коллекциям Rows и Columns // копируем весь/все столбец(ы) R.EntireColumn.Copy(EmptyParam); // поместим в БО // обязательно должна быть указана первая строка! ASheet.Paste(ASheet.Range['E1', EmptyParam], EmptyParam, lcid); // третий способ - копирование свойства ColumnWidth R.Copy(ASheet.Range['G4', EmptyParam]); // Просто копируем ширину столбца ASheet.Range['G4', EmptyParam].ColumnWidth := R.ColumnWidth; Copy Method
PasteSpecial Method

Как сделать автоперенос строк в ячейке?
Чтобы сделать перенос слов в ячейке, установите свойство WrapText объекта Range.
Delphi:
ASheet.Range['A1', EmptyParam].WrapText := True; ASheet.Range['A1', EmptyParam].EntireRow.AutoFit; WrapText Property

Как вставить несколько строк/столбцов? Как удалить несколько строк/столбцов? Как прятать/показывать строки и столбцы?
Как программно изменить высоту строки или ширину столбца?
Delphi:
// заполним ячейки данными для наглядности ASheet.Range['A1', EmptyParam].Formula := 1; R := ASheet.Range['A1:A25', EmptyParam]; R.DataSeries(xlColumns, xlLinear, xlDay, 1, EmptyParam, EmptyParam); R := ASheet.Range['A1:O1', EmptyParam]; R.DataSeries(xlRows, xlLinear, xlDay, 1, EmptyParam, EmptyParam); // не забывайте указывать EntireRow и EntireColumn! // добавим пять пустых строк после 20-й строки ASheet.Range['21:25', EmptyParam].EntireRow.Insert(xlShiftDown, EmptyParam); // удвоим ширину второго и третьего столбцов ASheet.Range['B:C', EmptyParam].EntireColumn.ColumnWidth := ASheet.Range['B:C', EmptyParam].EntireColumn.ColumnWidth * 2; // удвоим высоту второй и третьей строки ASheet.Range['2:3', EmptyParam].EntireRow.RowHeight := ASheet.Range['2:3', EmptyParam].EntireRow.RowHeight * 2; // удалим 4, 6 и 8 столбцы (два способа - кому что понравится) // ASheet.Range['D:D,F:F,H:H', EmptyParam].EntireColumn.Delete(xlShiftToLeft); ASheet.Range['D1,F1,H1', EmptyParam].EntireColumn.Delete(xlShiftToLeft); ColumnWidth
RowHeight Property
Insert Method
Delete Method

Как подогнать высоту или ширину ячеек для отображения всего текста?
Для отображения всего текста в ячейке или области ячеек используйте метод AutoFit объекта Range.
Delphi:
// для строки ASheet.Range['A1', EmptyParam].EntireRow.AutoFit; // для столбца ASheet.Range['A1', EmptyParam].EntireColumn.AutoFit; AutoFit Method
ShrinkToFit Property

Как сделать автоподбор высоты строк для объединенных ячеек?
Как известно, метод AutoFit для подбора высоты объединенных ячеек не срабатывает. Для этого был придуман простой метод (взят отсюда и просто адаптирован под Delphi).
Работает для объединенных ячеек в одной строке. Просто укажите одну из объединенных ячеек области (свойство WrapText должно быть включено).
Delphi:
procedure AutoFitMergedCellRowHeight(Rng: ExcelRange); var mergedCellRgWidth: Single; rngWidth, possNewRowHeight: Single; i: Integer; begin if Rng.MergeCells then begin // здесь использована самописная функция перевода стиля R1C1 в A1 if xlRCtoA1(Rng.Row, Rng.Column) = xlRCtoA1( Rng.Range['A1', EmptyParam].Row, Rng.Range['A1', EmptyParam].Column) then Rng := Rng.MergeArea; with Rng do begin if (Rows.Count = 1) and (WrapText) then begin (Rng.Parent as _Worksheet).Application.ScreenUpdating[lcid] := False; rngWidth := Cells.Item[1, 1].ColumnWidth; mergedCellRgWidth := 0; for i := 1 to Columns.Count do mergedCellRgWidth := Cells.Item[1, i].ColumnWidth + MergedCellRgWidth; MergeCells := False; Cells.Item[1, 1].ColumnWidth := MergedCellRgWidth; EntireRow.AutoFit; possNewRowHeight := RowHeight; Cells.Item[1, 1].ColumnWidth := rngWidth; MergeCells := True; RowHeight := possNewRowHeight; (Rng.Parent as _Worksheet).Application.ScreenUpdating[lcid] := True; end; // if end; // with end; // if end; // procedure // вызов AutoFitMergedCellRowHeight(ASheet.Range['F3', EmptyParam]); Конечно, функция должна быть вызвана для каждой строки, что, естественно, будет работать довольно долго. Поэтому старайтесь не использовать перенос текста в объединенных ячейках.

Как программно заморозить строки/столбцы?
Delphi:
// Отделить 3 строки XL.ActiveWindow.SplitRow := 3; // Отделить 1 колонку XL.ActiveWindow.SplitColumn := 1; // заморозим XL.ActiveWindow.FreezePanes := True; FreezePanes Property
SplitColumn Property
SplitRow Property
Split Property

Как добавить примечание к ячейке? Как удалить примечание?
Как изменить атрибуты шрифта примечания?
Комментарий - это своеобразный объект Shape, привязанный к определенному объекту Range.
Delphi:
// Добавление примечания // Способ первый ASheet.Range['A1', EmptyParam].AddComment('Note:'#10'Hello A1!'); // Способ второй ASheet.Range['A2', EmptyParam].NoteText('Note:'#10'Hello A2!', EmptyParam, EmptyParam); // Изменим атрибуты части текста примечания // обращаясь к свойствам Shape.TextFrame.Characters, // т.е. Comment - это некий объект Shape with ASheet.Range['A1', EmptyParam].Comment.Shape.TextFrame.Characters( // если не указать длину, то от заданной позиции и до конца текста 7, EmptyParam) do begin Font.Bold := False; Font.Color := clNavy; end; // добавим третью строку к коментарию в A2 ASheet.Range['A2', EmptyParam].NoteText( ASheet.Range['A2', EmptyParam].NoteText(EmptyParam, EmptyParam, EmptyParam) + #10'Третяя строка', EmptyParam, EmptyParam); // или так ASheet.Range['A2', EmptyParam].Comment.Text( ASheet.Range['A2', EmptyParam].Comment.Text(EmptyParam, EmptyParam, EmptyParam) + #10'Третяя строка', EmptyParam, EmptyParam); // можно показывать комментарий все время, как транспарант ASheet.Range['A2', EmptyParam].Comment.Visible := True; // False // теперь просто удалим комментарий ASheet.Range['A1', EmptyParam].Comment.Delete; // или так ASheet.Range['A1', EmptyParam].ClearNotes; Comment Property
AddComment Method
NoteText Method
ClearNotes Method
How to: Add, Delete, and Display Worksheet Comments

Как добавить URL? Как сделать гиперссылку для рисунка?
Delphi:
// добавим гиперссылки в A7 и A8 with ASheet do Hyperlinks.Add( Range['A7', EmptyParam], 'http://www.delphikingdom.com/asp/section.asp?id=16', EmptyParam, 'Все материалы раздела'#10'Hello, World!', 'Hello, World!'); with ASheet do Hyperlinks.Add( Range['A8', EmptyParam], 'http://www.delphikingdom.com/asp/nets.asp', EmptyParam, 'Верхний уровень Дерева тем'#10'тематического каталога', 'Тематический каталог'); // вставим рисунок в текущую ячейку и создадим гиперсылку Pic := (ASheet.Pictures(EmptyParam, lcid) as Pictures).Insert( MyPicsPath + '\common.gif', EmptyParam); ASheet.Hyperlinks.Add( Pic.ShapeRange.Item(1), 'http://www.delphikingdom.com/', EmptyParam, 'Королевство Delphi', EmptyParam); // редактирование with ASheet.Range['A8', EmptyParam].Hyperlinks.Item[1] do begin Address := 'http://www.delphikingdom.com/asp/answer.asp?IDAnswer=23150'; ScreenTip := 'Вопроc ¹ 23150'; TextToDisplay := 'Как в Excel редактировать гиперссылки, содержащиеся в ячейках?'; end; // удалим гиперссылку - останется только тект, указанный в TextToDisplay ASheet.Range['A8', EmptyParam].Hyperlinks.Item[1].Delete; Hyperlinks Property
Hyperlinks Collection
Hyperlink Object

Как отсортировать область ячеек?
Пример сортировки всех данных на листе по первому, второму и третьему столбцам.
Delphi:
ASheet.UsedRange[lcid].Sort( ASheet.Range['A1', EmptyParam], // Key1: OleVariant; xlAscending, // Order1: XlSortOrder; ASheet.Range['B1', EmptyParam], // Key2: OleVariant; EmptyParam, // xlSortValues xlAscending, // Order2: XlSortOrder; ASheet.Range['C1', EmptyParam], // Key3: OleVariant; xlAscending, // Order3: XlSortOrder; xlGuess, // Header: XlYesNoGuess; EmptyParam, // OrderCustom: OleVariant; False, // MatchCase: OleVariant; xlTopToBottom, // Orientation: XlSortOrientation; xlStroke // SortMethod: XlSortMethod ); Sort Method
How to: Sort Data in Worksheets Programmatically

Как сделать поиск значений в области ячеек или по всему листу?
Для поиска в области ячеек задайте диапазон ячеек при получении ссылки на объект Range. Если нужно искать по всему листу, то укажите UsedRange или просто одну ячейку, например A1.
Метод Find и FindNext возвращают объект Range, если значение найдено, и, если ничего не найдено, то nil (или null в C#).
Delphi:
var R: ExcelRange; ... S := '77'; R := ASheet.UsedRange[lcid].Find( S, // What: OleVariant; EmptyParam, // After: OleVariant; xlValues, // LookIn: OleVariant; xlPart, // LookAt: OleVariant; xlByRows, // SearchOrder: OleVariant; xlNext, // SearchDirection: XlSearchDirection; False, // MatchCase: OleVariant; False, //MatchByte: OleVariant // нужно установить в True, если EmptyParam // SearchFormat: OleVariant ); // поиск был завершен удачно, если определен объект R // поиск следующих ячеек с искомым текстом if Assigned(R) then begin Addr := R.Address[True, True, xlA1, EmptyParam, EmptyParam]; repeat // зальем красным цветом найденные ячейки R.Interior.Color := RGB(255, 0, 0); R.Font.Color := RGB(255, 255, 220); // найдем следующую R := ASheet.UsedRange[lcid].FindNext(R); if Assigned(R) then Addr2 := R.Address[True, True, xlA1, EmptyParam, EmptyParam]; // выход, если не найдено или адрес совпал (круг завершен) until not Assigned(R) or SameText(Addr, Addr2); end; Find Method
FindNext Method
CellFormat Object
How to: Search for Text in Worksheet Ranges

Как, имея ссылку на ячейку, узнать имя листа, которому она принадлежит? Узнать имя книги?
Получить ссылку на объект Worksheet, содержащий данную ячейку можно из свойства Parent.
var R: ExcelRange; ... // получим имя листа R.Formula := 'Имя листа: ' + (R.Parent as _Worksheet).Name; // получим имя книги R.Offset[1, 0].Formula := 'Имя книги: ' + ((R.Parent as _Worksheet).Parent as _Workbook).Name; // получим имя книги с полным путем к ней R.Offset[2, 0].Formula := 'Полное имя книги: ' + ((R.Parent as _Worksheet).Parent as _Workbook).FullName[lcid]; // из ячейки к объекту Excel.Application доступ только через Worksheet R.Offset[3, 0].Formula := (R.Parent as _Worksheet).Application.OperatingSystem[lcid]; Parent Property

Как записать значения сразу в несколько ячеек

Как записать значения сразу в несколько ячеек?
Для записи в несколько (область) ячеек используется объект Range (ExcelRange). Пример, как можно получить объект Range для области ячеек.
Delphi:
oRng: ExcelRange; ... ASheet := (XL.ActiveWorkbook.ActiveSheet as _Worksheet); oRng := ASheet.Range['A1:B5', EmptyParam]; oRng := ASheet.Range['A1', 'B5']; oRng := ASheet.Range[ASheet.Cells.Item[1, 1], ASheet.Cells.Item[5, 2]]; oRng.Formula := 123.45; // обращение к объекту Rows oRng := ASheet.Range['3:7', EmptyParam].Rows; oRng := ASheet.Range['A3:A7', EmptyParam].EntireRow; // обращение к объекту Columns oRng := ASheet.Range['B:E', EmptyParam].Columns; oRng := ASheet.Range['B1:E1', EmptyParam].EntireColumn; Заметьте, что при обращении к свойству Range и Cells объекта Range, адресация будет уже работать относительно области, указанной в объекте Range. Например, нижеприведенный код будет указывать не на ячейку A1, как сразу можно подумать, а на C2:
ASheet.Range['C2:F20', EmptyParam].Range['A1', EmptyParam]; А вот такой код вернет ячейку с адресом D3:
ASheet.Range['C2:F20', EmptyParam].Range['B2', EmptyParam]; EntireRow Property
EntireColumn Property
How to: Refer to Worksheet Ranges in Code

Как определить область выделенных ячеек и ее границы?
Чтобы получить область (или области) выделенных ячеек, нужно получить объект Range из свойства Selection объекта Excel.Application и обратиться к свойству Range.Areas.
Delphi:
try with XL.Selection[lcid] as ExcelRange do for i := 1 to Areas.Count do with Areas[i] do ShowMessageFmt('R%dC%d:R%dC%d', [Row, Column, Row + Rows.Count - 1, Column + Columns.Count - 1]); except // Selection - это не Range! end; Areas Property
Selection Property

Как записывать значения в ячейку (Value, Value2, Text, Formula)?
Начиная с версии Excel XP (10.0), свойство Value имеет параметр. Отличие Value2 от Value в том, что Value2 не поддерживает форматирования на лету для типов Currency, Double и Date. Свойство Text (только чтение для Range) возвращает текст в ячейке.
Свойство Formula выполняет те же функции, что и Value, с поддержкой форматирования на лету, а также позволяет записывать в ячейку формулы со ссылками в стиле A1 (в идеале английские, но что на практике, смотрите здесь). Для стиля R1C1 используется свойство FormulaR1C1.
Для записи локализованных (русских) форматов данных и формул используются свойства с окончанием Local, например FormulaLocal.
Delphi:
Range['A1', EmptyParam].Value2 := 'Любой текст'; Range['A2', EmptyParam].Value[xlRangeValueDefault] := 123.45; Range['A3', EmptyParam].Formula := Date; // Будьте внимательны - в английских формулах разделитель аргументов // только символ , (запятая), а не ;, как в русских формулах! Range['A4', EmptyParam].Formula := '=sum(A2:A3)'; C#:
// согласитесь, что немного неудобно писать так oSheet.get_Range(A6, Type.Missing).set_Value( Excel.XlRangeValueDataType.xlRangeValueDefault, 123.45); // или oSheet.get_Range(A7, Type.Missing).set_Value(Type.Missing, 123.45); // гораздо удобнее oSheet.get_Range(A6, Type.Missing).Value2 = 123.45; // или oSheet.get_Range(A6, Type.Missing).Formula = 123.45; Если вы попробуете записать макрос в Excel, то увидите, что запись значений ведется в свойство FormulaR1C1. С тем же успехом можно писать и в свойство Formula.
Внимание! При записи в свойство Formula, если это не формула, следите, чтобы текст не начинался с символов =, +, -, *, /. Или просто к тексту прибавляйте в начало знак апострофа (код символа 39):
Delphi:
Range['A1', EmptyParam].Formula := #39'Любой текст'; Range['A1', EmptyParam].Formula := #39 + MyStringVar; По волнам интеграции III
Value Property
Value2 Property
Formula Property
Text Property

Что работает быстрее, запись в Range или Cells?
Запись в Range работает быстрее, но не существенно (смотрите в Demo-проекте пример Как сделать, чтобы Excel работал быстрее?). Это связано с тем, что в Excel TLB свойство Cells.Item[R, C] имеет тип OleVariant и, как следствие, позднее связывание.
В C# между Range и Cells нет никакой разницы.
Для перевода из координат R1C1 в A1 удобно пользоваться самодельными функциями, например:
Delphi:
function xlRCtoA1(const ARow, ACol: Integer; RowAbsolute: Boolean = False; ColAbsolute: Boolean = False): String; const A1 = Ord('A') - 1; // номер A минус 1 (65 - 1 = 64) AZ = Ord('Z') - A1; // кол-во букв в англ. алфавите (90 - 64 = 26) var t, m: Integer; S: String[9]; // чтобы экономить память IV=256 последний столбец begin // номер колонки t := ACol div AZ; // целая часть m := (ACol mod AZ); // остаток? if m = 0 then Dec(t); if t 0 then S := Char(A1 + t) else S := ''; if m = 0 then t := AZ else t := m; S := S + Char(A1 + t); // весь адрес if ColAbsolute then S := '$' + S; if RowAbsolute then S := S + '$'; S := S + IntToStr(ARow); Result := S; end; Вот еще примеры

Что такое UsedRange? Как найти последнюю используемую ячейку? Что такое SpecialCells?
Что такое CurrentRegion?
UsedRange - прямоугольная область, включающая все заполненные ячейки и незаполненные, в промежутках между заполненными ячейками, на листе. Координаты области не обязательно начинаются в ячейке A1.
Также для определения координат различных ячеек можно использовать объект SpecialCells, например, с параметром xlCellTypeLastCell для нахождения последней (крайней справа снизу) используемой ячейки. CurrentRegion возвращает область вокруг ячейки, выделенную пустыми (незаполненными) ячейками.
End - находит последнюю ячейку в строке или столбце перед первой попавшейся пустой ячейкой, или первую заполненную, если вызывать метод End для пустой ячейки.
Delphi:
R: ExcelRange; ... // вся используемая область ячеек (прямоугольная) R := ASheet.UsedRange[lcid]; // получить последнюю (правую нижнюю) ячейку используемой области R := ASheet.Range['A1', EmptyParam].SpecialCells(xlCellTypeLastCell, EmptyParam); // получить все непустые ячейки вокруг ячейки A5 (удобно для обнаружения таблиц // на листе) R := ASheet.Range['A5', EmptyParam].CurrentRegion; // Найти последнюю непустую ячейку в столбце, если A9 - непустая ячейка. // Или первую непустую, если A9 - пустая ячейка. R := ASheet.Range['A9', EmptyParam].End_[xlDown]; UsedRange Property
SpecialCells Method
CurrentRegion Property
End Property

Как получить адрес ячейки?
Delphi:
R: ExcelRange; ... // абсолютные координаты в стиле A1 R := ASheet.Range['A1', EmptyParam]; R.Select; R.Formula := R.Address[True, True, xlA1, EmptyParam, EmptyParam]; // относительные координаты в стиле A1 R := ASheet.Range['A2', EmptyParam]; R.Formula := R.Address[False, False, xlA1, EmptyParam, EmptyParam]; // если указать RowAbsolute и ColumnAbsolute False, // то будет выдан адрес относительно активной ячейки R := ASheet.Range['A3', EmptyParam]; R.Formula := R.Address[False, False, xlR1C1, EmptyParam, EmptyParam]; // теперь получим абсолютный адрес R := ASheet.Range['A4', EmptyParam]; R.Formula := R.Address[True, True, xlR1C1, EmptyParam, EmptyParam]; // также координаты ячейки можно получить из свойств // Row и Column объекта Range R := ASheet.Range['A5', EmptyParam]; R.Formula := Format('Строка %d, Колонка %d', [R.Row, R.Column]); Address Property
Row Property
Column Property

Нужно ли выделять ячейку/область для того, чтобы вносить в нее данные?
Не нужно. Достаточно указать адрес области ячеек в объекте Range для выбранного объекта Worksheet (и/или Workbook).
Любой Select или Activate только замедлит работу вашей программы. Кроме того, метод Select возможно вызвать только на активном листе активной книги!
Не используйте Select и Activate без необходимости.
Best Practices for Setting Range Properties

В чем отличие Range.Activate от Range.Select?
И метод Activate и Select делают одно и то же - выделяют (активируют) ячейку. Разница лишь в том, что метод Activate позволяет выбрать только одну ячейку на листе или сделать активной любую ячейку в области, выделенной методом Select.
Метод Select позволяет выделять одну и более областей ячеек.
Select Method
Activate Method

Как установить свойству ячейки NumberFormat общий формат, текстовый формат, формат даты, числа, валюты?
Для правильной работы NumberFormat с английскими форматами не забудьте подключить модуль TrDispCall
Delphi:
// Установка текстового формата. // Записанное число как текст будет воспринят как текст, // если указать текстовый формат with ASheet.Range['A1', EmptyParam] do begin NumberFormat := '@'; Value[xlRangeValueDefault] := '1234567890123456'; end; // Записанное число как текст будет воспринят как число, // если указать общий формат with ASheet.Range['A1', EmptyParam] do begin NumberFormat := ''; Value2 := '1234567890123456'; end; // Записанное в ячейку число будет воспринято как число, но // с выравниванием влево как текст with ASheet.Range['A1', EmptyParam] do begin NumberFormat := '@'; Value2 := Now(); end; // Для установки общего формата достаточно записать // в свойство NumberFormat пустую строку with ASheet.Range['A1', EmptyParam] do begin NumberFormat := ''; end; // Для установки формата даты запишем в NumberFormat // формат короткой даты. with ASheet.Range['A1', EmptyParam] do begin NumberFormat := ShortDateFormat; // SysUtils end; // Установим формат целых чисел с разделителем тысяч with ASheet.Range['A1', EmptyParam] do begin NumberFormat := '#,##0'; end; // Установим формат float чисел с разделителем тысяч и // двумя знаками после запятой with ASheet.Range['A1', EmptyParam] do begin NumberFormat := '#,##0.00'; end; NumberFormat

Передаю в ячейку 385.00, а показывает 385. Почему?
Потому что в ячейке установлен общий формат (general), который отсекает незначащие цифры. В данном примере всегда будут указываться 2 цифры после запятой:
Delphi:
ASheet.Range['A1', EmptyParam].NumberFormat := '0.00'; ASheet.Range['A1', EmptyParam].Value2 := 385; // будет отображено 385.00
При записи в ячейку чисел как текста, целые числа автоматический преобразуются в численный формат, а вещественные нет. Вследствие чего возникает ошибка число сохранено как текст.
Лучше числа не записывать в ячейку как текст и не надеяться, что Excel всегда сможет на лету преобразовать текст верно. Вы никогда не можете быть уверенными, какие локальные установки формата чисел будут установлены на компьютере пользователя.
Всегда перед записью переводите записываемые числа из текста в число (Float или Integer) в своей программе.

Делаю экспорт в Excel, допустим, текст 000069987, а он выводит в ячейку 69987, т.е. удаляет лидирующие нули. Как мне сделать, чтобы в ячейку выводилось 000069987?
При записи текста, содержащего одни цифры, Excel пытается его преобразовать в число. Чтобы избажать такой помощи со стороны Excel'я, перед записью в ячейку установите в свойство NumberFormat текстовый формат или добавьте перед текстом символ апострофа ' (код символа 39).
Delphi:
S := '000069987'; // установим текстовый формат перед записью в ячейку with ASheet.Range['A1', EmptyParam] do begin NumberFormat := '@'; Formula := S; end; // или добавим перед текстом апостроф - результат тот же и даже быстрее работает // так как не приходится изменять свойство NumberFormat ASheet.Range['A2', EmptyParam].Formula := #39 + S;
Как очистить область ячеек? Как определить что ячейка Excel пустая?
Delphi:
ASheet.Range['A1', EmptyParam].Formula := 123.45; ASheet.Range['A2', EmptyParam].Formula := 0; // проверяем ASheet.Range['B1', EmptyParam].Formula := VarIsClear(ASheet.Range['A1', EmptyParam].Value2); ASheet.Range['B2', EmptyParam].Formula := VarIsClear(ASheet.Range['A2', EmptyParam].Value2); ASheet.Range['B3', EmptyParam].Formula := VarIsClear(ASheet.Range['A3', EmptyParam].Value2); // Записали в ячейку информацию и проверяем, что вернет нам функция VarIsClear // Запишем в A3 пустой текст, а в A1 очистим форматы ASheet.Range['A1', EmptyParam].ClearFormats; ASheet.Range['A3', EmptyParam].Formula := ''; // проверим ASheet.Range['B1', EmptyParam].Formula := VarIsEmpty(ASheet.Range['A1', EmptyParam].Value2); ASheet.Range['B2', EmptyParam].Formula := VarIsEmpty(ASheet.Range['A2', EmptyParam].Value2); ASheet.Range['B3', EmptyParam].Formula := VarIsEmpty(ASheet.Range['A3', EmptyParam].Value2); // После записи пустой строки ячейка так и осталась пустой // Очистим содержание всех ячеек и посмотрим, что получилось ASheet.UsedRange[lcid].ClearContents; ASheet.Range['B1', EmptyParam].Formula := VarIsEmpty(ASheet.Range['A1', EmptyParam].Value2); ASheet.Range['B2', EmptyParam].Formula := VarIsEmpty(ASheet.Range['A2', EmptyParam].Value2); ASheet.Range['B3', EmptyParam].Formula := VarIsEmpty(ASheet.Range['A3', EmptyParam].Value2); // Видно, что теперь все ячейки пустые (нет данных). Чтобы радикально очистить ячейки (данные, форматы, примечания и т.д.), можно вызвать метод Clear.
Clear Method
ClearContents Method
ClearFormats Method

Как задать имя области ячеек? Как обратиться к ячейке по имени?
Как определить, что такое имя существует на листе? Как задать имя области ячеек?
Delphi:
// создадим функцию для проверки наличия именованной области ячеек на листе function RangeNameExists(ASheet: ExcelWorksheet; const ARangeName: String): Boolean; var i: Integer; S: ExcelWorksheet; WB: ExcelWorkbook; begin Result := False; if not Assigned(ASheet) then Exit; WB := ASheet.Parent as ExcelWorkbook; for i := 1 to WB.Names.Count do begin if AnsiSameText(WB.Names.Item(i, EmptyParam, EmptyParam).Name_, ARangeName) then begin // имя нашли, а на нашем ли листе? S := WB.Names.Item(i, EmptyParam, EmptyParam).RefersToRange.Worksheet as ExcelWorksheet; Result := AnsiSameText(S.Name, ASheet.Name); if Result then Break; end; end; end; // присвоим имя MyNamedRange области B2:D7 (ASheet.Parent as ExcelWorkbook).Names.Add( 'MyNamedRange', // Name, ASheet.Range['B2:D7', EmptyParam], // RefersTo: OleVariant; стиль адресации A1 True, // Visible: OleVariant; EmptyParam, // MacroType: OleVariant; EmptyParam, // ShortcutKey: OleVariant; EmptyParam, // Category: OleVariant; EmptyParam, // NameLocal: OleVariant; EmptyParam, // RefersToLocal: OleVariant; EmptyParam, // CategoryLocal: OleVariant; EmptyParam, // RefersToR1C1: OleVariant; // адрес в стиле R1C1 EmptyParam // RefersToR1C1Local: OleVariant ); // : Name; // теперь попробуем определить наличие именованной области и, если есть такая, // обведем область рамкой if RangeNameExists(ASheet, 'MyNamedRange') then ASheet.Range['MyNamedRange', EmptyParam].BorderAround( xlContinuous, xlThick, xlColorIndexAutomatic, EmptyParam ); Если вы зададите области имя, уже существующее в листе, то старое имя будет утеряно, т.е. получится перезапись имени.
Присваивать имена области ячеек можно и неактивному листу. Задавать адрес ячейки можно и как текст (не обязательно ссылку на объект Range), а также можно в стиле R1C1, указав адрес области ячеек в параметре RefersToR1C1.
Names Collection Object
Add Method
How to: Create New Named Ranges in Worksheets

Как объединить ячейки? Как узнать, что ячейка входит в объединенную область и определить границы этой области?
Delphi:
// объединим область ячеек A1:C2 строки вместе ASheet.Range['A1:C2', EmptyParam].Merge(False); ASheet.Range['A1', EmptyParam].Select; // подправим вид курсора ASheet.Range['A1', EmptyParam].MergeArea.Formula := 'A1:C2 объединены'; // объединим область ячеек A3:C4 раздельно каждую строку ASheet.Range['A3:C4', EmptyParam].Merge(True); // определим, что ячейка C2 принадлежит объединенной области // при этом тестируем крайнюю ячейку области на вхождение if ASheet.Range['C2', EmptyParam].MergeCells then begin // если это так, то получим координаты области ASheet.Range['A3', EmptyParam].MergeArea.Formula := 'В стиле A1: ' + ASheet.Range['C2', EmptyParam].MergeArea.Address[True, True, xlA1, EmptyParam, EmptyParam]; ASheet.Range['A4', EmptyParam].MergeArea.Formula := Format('Начало в R%dC%d, конец в R%dC%d', [ ASheet.Range['C2', EmptyParam].MergeArea.Row, ASheet.Range['C2', EmptyParam].MergeArea.Column, ASheet.Range['C2', EmptyParam].MergeArea.Row + ASheet.Range['C2', EmptyParam].MergeArea.Rows.Count - 1, ASheet.Range['C2', EmptyParam].MergeArea.Column + ASheet.Range['C2', EmptyParam].MergeArea.Columns.Count - 1 ]); end; Смотрите дальше, как сделать автоподбор высоты строк для объединенных ячеек.
MergeCells Property
MergeArea Property
Merge Method
UnMerge Method

Как записывать данные из вариантного массива в Excel?
Запись данных из вариантного массива (VarArray) очень хорошо расписана в статьях По волнам интеграции III и Зарисовка на тему экспорта в Excel. Для разнообразия, приведу еще раз этот вариант быстрого экспорта в Excel.
Внимание! Если вы пытаетесь записать в область одну строку, то МАССИВ все равно ДОЛЖЕН БЫТЬ ДВУМЕРНЫМ!
Т.е. varData := VarArrayCreate([1, 1, 1, ColumnCount], varVariant); При записи массива вы должны указать в адресе области ячеек Range ВСЮ область для заполнения.
Delphi:
procedure CopyFromDataSet(ASheet: _WorkSheet; DataSet: TDataSet); var R, C: Integer; vData: Variant; begin DataSet.Last; // специально для IBX // строки на одну больше, чем в датасете - для шапки vData := VarArrayCreate([0, DataSet.RecordCount, 1, DataSet.FieldCount], varVariant); for C := 0 to DataSet.FieldCount - 1 do vData[0, C + 1] := DataSet.Fields[C].DisplayLabel; DataSet.First; R := 1; while not DataSet.Eof do begin for C := 0 to DataSet.FieldCount - 1 do case DataSet.Fields[C].DataType of ftString, ftFixedChar, ftWideString: vData[R, C + 1] := #39 + DataSet.Fields[C].Value; else vData[R, C + 1] := DataSet.Fields[C].Value; end; Inc(R); DataSet.Next; end; // укажем всю область, в которую будут записаны данные из массива with ASheet do with Range['A1', Cells.Item[DataSet.RecordCount + 1, DataSet.FieldCount]] do begin Formula := vData; Borders.Weight := xlHairline; EntireColumn.AutoFit; end; vData := Unassigned; // освободим память сами // настройка внешнего вида with ASheet do Range['A1', Cells.Item[1, DataSet.FieldCount]].Interior.ColorIndex := 15; ASheet.Application.ActiveWindow.SplitRow := 1; ASheet.Application.ActiveWindow.FreezePanes := True; ASheet.Application.ActiveWindow.DisplayGridlines := False; end; C#:
// данные будут взяты из таблицы EMPLOYEE из файла DBDEMOS.MDB string dbFile = Environment.GetFolderPath(Environment.SpecialFolder.CommonProgramFiles); dbFile += @\Borland Shared\Data\dbdemos.mdb; // заменим путь к файлу dbdemos dbFile = Regex.Replace(oleDbConnection1.ConnectionString, @^(?start.*?;Data Source=)(?db[^;]*)(?end;.*)$, ${start} + dbFile + ${end}); oleDbConnection1.ConnectionString = dbFile; try { oleDbDataAdapter1.Fill(dataSet1, employee); oleDbConnection1.Close(); // закроем соединение // DataTable tblEmployee = dataSet1.Tables[employee]; dataGrid1.DataSource = tblEmployee; // dataSet1.Tables[employee]; // создадим двумерный массив для экспорта object[,] arrEmployee = (object[,]) Array.CreateInstance(typeof(object), new int[2] {tblEmployee.Rows.Count + 1, tblEmployee.Columns.Count}, // длины массива new int[2] {0, 1}); // начальные индексы строк и столбцов // заголовки for (int i = 0; i tblEmployee.Columns.Count; i++) arrEmployee[0, i + 1] = tblEmployee.Columns[i].Caption; // данные for (int R = 0; R tblEmployee.Rows.Count; R++) for (int C = 0; C tblEmployee.Columns.Count; C++) { arrEmployee[R + 1, C + 1] = tblEmployee.Rows[R][C]; Application.DoEvents(); } Excel.Worksheet oSheet = null; Excel.Range oRng = null; Excel.Application XL = new Excel.Application(); try { XL.Visible = true; XL.Interactive = false; XL.Workbooks.Add(Type.Missing); oSheet = (Excel.Worksheet) XL.ActiveSheet; oRng = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[tblEmployee.Rows.Count + 1, tblEmployee.Columns.Count]); oRng.Formula = arrEmployee; // запись данных oRng.EntireColumn.AutoFit(); oRng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; oRng.Borders.Weight = Excel.XlBorderWeight.xlHairline; // шапка oRng = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[1, tblEmployee.Columns.Count]); oRng.Interior.ColorIndex = 15; // 25% серого oRng.Interior.Pattern = Excel.XlPattern.xlPatternSolid; XL.ActiveWindow.SplitRow = 1; XL.ActiveWindow.FreezePanes = true; XL.ActiveWindow.DisplayGridlines = false; XL.ActiveWorkbook.Saved = true; this.Activate(); } finally { oSheet = null; XL.Interactive = true; XL.UserControl = true; XL = null; } dataSet1.Tables.Remove(tblEmployee); } catch (Exception ex) { if (oleDbConnection1.State == ConnectionState.Open) oleDbConnection1.Close(); MessageBox.Show(ex.Message); }
Как прочесть данные из области ячеек в массив?
Точно также, как и при экспорте, только самому создавать массив не нужно - Excel все сделает за вас. В принципе, после получения данных в массив, Excel уже не нужен, и от него можно отсоединиться.
Delphi:
var myVarArray: OleVariant; // массив будет создан автоматически MyVarArray := ASheet.UsedRange[lcid].Value[xlRangeValueDefault]; // цикл по строкам for R := VarArrayLowBound(MyVarArray, 1) to VarArrayHighBound(MyVarArray, 1) do // цикл по столбцам for C := VarArrayLowBound(MyVarArray, 2) to VarArrayHighBound(MyVarArray, 2) do C#:
// объявим пустой двумерный массив и получим в него данные object[,] srcArr = (object[,]) ASheet.UsedRange.get_Value( Excel.XlRangeValueDataType.xlRangeValueDefault); // цикл по массиву for (int R = srcArr.GetLowerBound(0); R = srcArr.GetUpperBound(0); R++) for (int C = srcArr.GetLowerBound(1); C = srcArr.GetUpperBound(1); C++)
Почему при выгрузке данных в Excel не могу записать строк больше 65536?
Потому что это максимально возможное количество строк объекта Worksheet. Если вы записываете больше строк, чем 65536, то помещайте их на следующий лист книги - благо, что количество листов ограничено только оперативной памятью комьютера.
Excel specifications and limits

Как писать в ячейки нескольких листов сразу?
Чтобы занести данные в несколько листов сразу, вы можете объединить листы методом Worksheets.Select и воспользоваться методом FillAcrossSheets
Delphi:
var SelSheets: Sheets; // заносим данные в активный лист XL.Range['B2', EmptyParam].Formula := 123.45; XL.Range['B3', EmptyParam].Borders.LineStyle := xlUnderlineStyleDouble; // выберем 4 листа, в которые будут сдублированы данные SelSheets := XL.ActiveWorkbook.Sheets[VarArrayOf([1, 2, 3, 4])] as Sheets; // SelSheets.Select(False, lcid); // не обязательно // заполним выбранные листы данными из активного листа из любой области ячеек SelSheets.FillAcrossSheets((XL.ActiveSheet as _Worksheet).UsedRange[lcid], xlFillWithAll, lcid); Select Method
FillAcrossSheets Method

Как изменить цвет фона и шрифта ячейки?
Смотрите свойства Font и Interior объекта Range

Базы данных: Разработка - Управление - Excel