Excel ЧаВо
         

Chart, ChartObject, Shape, Picture


Пожалуй, самым популярным использованием Excel является применение его для построения диаграмм (Chart). Это заметно по большому количеству примеров, статей и различных FAQ в Интернете по этой теме.

Chart Object

ChartObject Object Model

Shapes Collection Object Model



Chart Class



Как добавить 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



Как добавить картинке гиперссылку?


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

Hyperlink Property



Как добавить новый ряд (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



Как добавить в лист объект 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



Как изменить цвет серии в 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



Как изменить яркость или контрастность картинки?


(AShape.PictureFormat as PictureFormat).Brightness := 0.25; // 25% яркости

(AShape.PictureFormat as PictureFormat).Contrast := 0.75; // 75% контрастность

PictureFormat Property



Как изменить размер картинки и вписать ее в размер определенной области ячеек?


Если пытаться изменять размер картинки, то она, по умолчанию, будет менять размер пропорционально. Такую особенность задает свойство 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



Как изменять заголовки 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



Как получить доступ к ряду (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



Как сгруппировать несколько объектов 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



Как убрать легенду из Chart?


Легенду можно просто "спрятать", указав, что ее "нет". Также вы можете изменять любые атрибуты легенды (Font, Interior и другие), получив доступ к свойству Legend объекта Chart.

oChart.HasLegend[lcid] := False;

Legend Object

HasLegend



Как указать источник данных объекту 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



Как вставить в лист картинку?


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




Creating Charts in Excel 2003 Using Visual Basic for Applications Code

How To Create Excel Chart w/OLE Automation from Visual Basic

Microsoft Excel Charting FAQs

Excel Charts and Graphs Help from Mr Excel

Excel Chart Tips

How to automate Microsoft Excel from Microsoft Visual C# .NET

How To Automate Excel from an HTML Web Page Using JScript

VBA Samples for Working with Excel 2003 Worksheets

XL97: How to Create a GIF File from a Microsoft Excel Chart

COM Tips & Tricks



Как подключить книгу 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)



Как получить данные из 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 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



Как получить данные в 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



Как вставить данные из таблицы 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.




How To Use ADO with Excel Data from Visual Basic or VBA

Excel Source

Excel Destination

Working with ADO.NET Datasets in Microsoft Office

Microsoft Excel 2003 Language Reference

Understanding the Excel Object Model from a .NET Developer's Perspective

Chapter 6: ActiveX Data Objects

Automating Microsoft Office 97 and Microsoft Office 2000



К материалу прилагаются файлы:

Demo-проект на Delphi (63 K) обновление от 6/14/2006 9:06:00 AM

Demo-проект на C# (32 K) обновление от 6/14/2006 9:06:00 AM