Как сделать автоперенос строк в ячейке
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
