Несколько способов форматирования и обработки данных в Excel документах с помощью IS-Builder.

45 25

Нередко приходится сталкиваться с ситуацией, когда нужно отформатировать xls-документ непосредственно в коде. В этой статье хотелось привести несколько примеров форматирования данных. Итак, начнем.

Обозначим переменные, которые будут использоваться в дальнейшем.

ExcelApp = CreateObject('Excel.Application')
Book = ExcelApp.WorkBooks.Add(1)
Sheet = Book.WorkSheets(1)  // первый лист книги
Data = Sheet.UsedRange
CellHL = Data.Cells(НомерСтроки1; НомерСтолбца1)      // Верхний левый угол
CellBR = Data.Cells(НомерСтроки2; НомерСтолбца2)      // Нижний правый угол
Cell = Data.Cells(НомерСтроки; НомерКолонки)          // ячейка

 

  1. Выделение всего текста в ячейке
  • Data.Range(CellHL; CellBR). Font.Bold = True          // жирным
  • Data.Range(CellHL; CellBR).Font.Italic = True           // курсивом
  • Data.Range(CellHL; CellBR). Font.Underline = True   //подчеркиванием
  1. Выделение определенного слова в тексте ячейки
Text = 'Выделяемое слово в ячейке'
LengText = Length(Text)
Cell = Data.Cells(НомерСтроки; НомерКолонки)
if Not Cell.HasFormula
  iPosition = CharPos(Text; CellHL.Value)
  Cell.Characters(iPosition; LengText).Font.Bold = True        // выделение жирным
  Cell.Characters(iPosition; LengText).Font.Underline = True   // подчеркивание
  Cell.Characters(iPosition; LengText).Font.Italic = True      // установка курсива
endif
  1. Объединение ячеек
Data.Range(CellHL; CellBR).Merge
  1. Установка размера шрифта
Data.Range(CellHL; CellBR).Font.Size = 12
  1. Установка цвета текста
Cell.Characters(iPosition; LengTextRed).Font.Color = -16776961   // установка красного цвета
  1. Горизонтальное выравнивание ячейки
Data.Range(CellHL; CellBR).HorizontalAlignment = -4130

Режим выравнивания

Константа в Excel

Значение в ISBL

По центру

xlHAlignCenter

-4108

По центру выделения

xlHAlignCenterAcrossSelection

7

Распределенное

xlHAlignDistributed

-4117

С заполнением

xlHAlignFill

5

По значению

xlHAlignGeneral

1

По ширине

xlHAlignJustify

-4130

По левому краю

xlHAlignLeft

-4131

По правому краю

xlHAlignRight

-4152

  1. Вертикальное выравнивание ячейки
Data.Range(CellHL; CellBR).VerticalAlignment = -4130

Режим выравнивания

Константа в Excel

Значение в ISBL

По нижнему краю

xlVAlignBottom

-4107

По центру

xlVAlignCenter

-4108

Распределенное

xlVAlignDistributed

-4117

По высоте

xlVAlignJustify

-4130

По верхнему краю

xlVAlignTop

-4160

  1. Отступ
Data.Item(НомерСтроки; НомерКолонки).IndentLevel = Значение отступа в символах
  1. Установка темы шрифта
Data.Range(CellHL; CellBR).Font.Name = 'Times New Roman'
  1. Установка ширины ячеек
Data.Range(CellHL; CellBR).ColumnWidth = Значение ширины в символах
  1. Установка высоты ячеек
Data.Range(CellHL; CellBR).RowHeight = Значение высоты в пунктах
  1. Установка границы ячейки и тип линии границы
SelectionTab = Data.Range(CellHL;CellBR)
SelectionTab.Borders(XlBordersIndex).LineStyle = 1 // непрерывная линия

Значения константы XlBordersIndex:

Расположение линии

Значение в Excel

Константа в ISBL

Линия по диагонали сверху – вниз

xlDiagonalDown

5

Линия по диагонали снизу – вверх

xlDiagonalUp

6

Линия, обрамляющая диапазон слева

xlEdgeLeft

7

Линия, обрамляющая диапазон сверху

xlEdgeTop

8

Линия, обрамляющая диапазон снизу

xlEdgeBottom

9

Линия, обрамляющая диапазон справа

xlEdgeRight

10

Все вертикальные линии внутри диапазона

xlInsideVertical

11

Все горизонтальные линии внутри диапазона

xlInsideHorizontal

12

Свойство LineStyle (тип линии) может принимать значения:

Тип линии

Значение в Excel

Константа в ISBL

Нет линии

xlLineStyleNone

0

(_________) Непрерывная

xlContinuous

1

(_._._._._) В виде тире и точек

xlDashDot

4

(_.._.._..) В виде тире и двойных точек

xlDashDotDot

5

(.........) В виде точек

xlDot

8

(=====) В виде двойной линии

xlDouble

9

(/././././) В виде наклонной пунктирной

xlSlantDashDot

13

  1. Установка толщины линии границы 
SelectionTab.Borders(XlBordersIndex).Weight = XlBorderWeight

Значения константы XlBorderWeight

Толщина линии      

Значение в Excel

Константа в ISBL

Сверхтонкая

xlHairline

1

Тонкая

xlThin

2

Средняя

xlMedium

3

Жирная

xlThick

4

  1. Установка цвета линии границы 
 SelectionTab.Borders(XlBordersIndex).ColorIndex = Index

где Index: 

  1. Установка отметки напротив пункта "переносить по словам" 
Data.Range(CellHL; CellBR).WrapText = True
  1. Отключение/включение режима показа предупреждений          
ExcelApp.DisplayAlerts = False // отключение
ExcelApp.DisplayAlerts = True  // включение
  1. Сохранение книги в pdf формат      
  Book.ExportAsFixedFormat(0; Filename;1; IncludeDocProperties; IgnorePrintAreas)

где

  • 0 - значение Microsoft.Office.Interop.Excel.XlFixedFormatType, указывающее, сохранять книгу в формате PDF;
  • Filename - полный путь к новому файлу формата PDF;
  • 1 - значение Microsoft.Office.Interop.Excel.XlFixedFormatQuality, определяющее качество экспортируемого файла;
  • IncludeDocProperties - значение true, чтобы включить свойства документа в новый экспортированный файл; в противном случае — значение false;
  • IgnorePrintAreas - значение true, чтобы пропускать области печати, установленные при экспорте; в противном случае — false.
  1. Построение сводной таблицы

        // Вставка сводной таблицы

 PivotTable = Book.PivotCaches.Create(1; SourceData).CreatePivotTable(TableDestination; TableName)

где

  • 1 - значение константы xlPivotTableSourceType;
  • SourceData – данные для нового сводной таблицы (диапазон ячеек);
  • TableDestination - диапазон на листе, где будет помещена итоговая Сводная таблица
  • TableName - имя новой Сводной таблицы
Sheet2 = Book.WorkSheets(2)
// Добавление поля в сводную таблицу
PivotField1 = Sheet2.PivotTables(TableName).PivotFields(NameColumn)
PivotField1.Orientation = 1 // задаем ориентацию – поле строки (поле столбца = 2; поле фильтра = 3)
PivotField1.Position = 1    // номер поля в списке полей, выбранной для ориентации (нумеруется с 1)

 где NameColumn – название колонки, которое совпадает с именем колонки в шапке таблицы с листа исходных данных.

PivotField2 = Sheet2.PivotTables(TableName).PivotFields(NameColumn) 
PivotField2.Orientation = 4 // задаем ориентацию – поле данных 
PivotField2.Function = 0 // функция суммы 

Значения констант параметра Функции:

0 – сумма; 1 – количество; 2 – среднее; 3 – максимум; 4 – минимум.

  1. Вставка гиперссылки на запись справочника в ячейку
RecordReference = References.ИмяСправочника.GetObjectByID(ИДЗаписи) // запись справочника
Sheet.Hyperlinks.Add(Sheet.Cells(НомерСтроки; НомерКолонки); RecordReference.Hyperlink(hltText))
CellHL = Data.Cells(НомерСтроки; НомерКолонки) 
Cell = Data.Range(CellHL;CellHL) 
Cell.Formula = Rez.Name

 

В дальнейшем планирую написать аналогичную статью для MS Word.

45
Авторизуйтесь, чтобы оценить материал.
4
Святослав Романов

Спасибо, очень полезная статейка!

Алена Перейма

Отличная статья. Добавлю еще несколько примеров, которые использовали сами.

Формат ячеек (обычно делаю для все колонки сразу):
 Sheet.Columns("A").NumberFormat = "@"                //текстовый формат 
 Sheet.Columns("B").NumberFormat = "# ##0,00"         //числовой формат
 Sheet.Columns("D").NumberFormat = "# ##0,00р."         //денежный формат
можно формат настраивать для отдельных ячеек
 Sheet.Range(CellHL; CellBR).NumberFormat = "@"               //текстовый формат 

Автофильтр:
Sheet.Range(CellHL; CellBR).AutoFilter       

Печать шапки таблицы (3 верхние строчки) на каждой странице:
Sheet.PageSetup.PrintTitleRows = "$3:$3"

Ориентация страницы:
Sheet.PageSetup.Orientation = 2     //альбомная 
Sheet.PageSetup.Orientation = 1     //книжная

Поля (установить по 1см):
Sheet.PageSetup.LeftMargin = ExcelApp.CentimetersToPoints(1)      //левое
Sheet.PageSetup.TopMargin  = ExcelApp.CentimetersToPoints(1)    //верхне
Sheet.PageSetup.RightMargin = ExcelApp.CentimetersToPoints(1)    //правое        
Sheet.PageSetup.BottomMargin  = ExcelApp.CentimetersToPoints(1)    //нижнее

Назвать лист:
Sheet.Name = "Какое-то название"

Добавить номерацию страниц в правом нижнем колонтитуле:
Sheet.PageSetup.FirstPageNumber = 1         //номер первой страници
Sheet.PageSetup.FooterMargin = 15           //отступ колонтитутла от нижнего края страницы  0,5см (отступ умножать на 30)
Sheet.PageSetup.RightFooter =  '&8 Страница &С&P из &К'       //на первой странице трехстраничного отчета в колонтитуле будет текст шрифтом размера 8 "Страница 1 из 3"

Елизавета Гончарова

Алена, спасибо за дополнение.

Елизавета Гончарова
Евгений Стоянов

Не нашел как залить ячейку. Если кому надо:

Data.Range(CellHL; CellBR).Interior.ColorIndex   = index (берется из 14 пункта)

Максим Иванов

Подскажите, пожалуйста, а можно как-нибудь установить цвет RGB (255, 255,255) ?

Елизавета Гончарова
Подскажите, пожалуйста, а можно как-нибудь установить цвет RGB (255, 255,255) ?

 Добрый день. Одним из вариантов установки цвета RGB является: вместо функции RGB() в Excel использовать формулу: 

256*256*R+256*G+B  

где

  • R - Число в диапазоне от 0 до 255 включительно, которое представляет красный компонент цвета
  • G - Число в диапазоне от 0 до 255 включительно, которое представляет зеленый компонент цвета
  • B - Число в диапазоне от 0 до 255 включительно, которое представляет синий компонент цвета.

То есть для заливки ячейки можно использовать следующий код:

CellHL = Data.Cells(1; 1)      // Верхний левый угол
CellBR = Data.Cells(5; 7)      // Нижний правый угол
  R = 127 // число, которое представляет красный компонент цвета
  G = 255 // число, которое представляет зеленый компонент цвета
  B = 0      // число, которое представляет синий компонент цвета
Data.Range(CellHL; CellBR).Interior.Color = 256*256*R+256*G+B 
Елизавета Гончарова
Добрый день. Одним из вариантов установки цвета RGB является: вместо функции RGB() в Excel использовать формулу: 256*256*R+256*G+B

Ошиблась в формуле, формула должна быть такой:

R + G * 256 + B * 256 * 256

 

Максим Иванов

Спасибо!

Отключение обновления экрана(ScreenUpdating) для ускорения работы макроса

  1. Sub excample()  
  2. Application.ScreenUpdating = False  
  3. 'Здесь находится Ваш код  
  4. Application.ScreenUpdating = True  
  5. End Sub 

Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.

Public Sub Prepare()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = False
    Application.DisplayAlerts = False
End Sub

Public Sub Ended()
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
    Application.DisplayStatusBar = True
    Application.DisplayAlerts = True
End Sub


По порядку:
1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
3. Не обрабатывать события.
4. Отображение границ страниц, тоже почему-то помогает.
5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).

Константин, спасибо за дополнения, интересная информация.

Анатолий Придыбайло

Дополню по заполнению формул в ячейки.
Столкнулся с тем что не работала формула суммирования значений полей, делал так:

Data.Range('D'& RowDown).Formula = '=СУММ(R[-10]C:R[-1]C)'

формула записывалась, но не вычислялась пока ее не передернешь. Почитал что это проблема связанна с использованием функций на русском языке и нужно использовать их английские аналоги. СУММ замени на SUM и все заработало как надо.

Анатолий Придыбайло

в дополнение к предыдущему комментарию:

для формул с русскими названиями нужно использовать не Formula, а FormulaLocal. Таким образом правильными вариантами написания формул будут:

Data.Range('D'& RowDown).FormulaLocal = '=СУММ(R[-10]C:R[-1]C)'
Data.Range('D'& RowDown).Formula = '=SUM(R[-10]C:R[-1]C)'
Татьяна Кравченко

Спасибо большое за статью, Елизавета.

А можно определить ячейку (строку и столбец) в таблице Excel по искомому слову? Например, если "нужное слово" содержится в какой то из ячеек таблицы Excel, то вывести ее адрес (строка; столбец).

Аяз Садыков

Дополню статью:
 "Применение условного форматирования"


FormatConditions.AddType , Operator , Formula1 , Formula2 )

Где Type, одно из значений этих констант:

Operator:

Formula1:
Указывается в зависимости от нужных вам условий (в примере Operator = 3 Formula ="", что значит применять условие для ячеек равным "", или еще один пример Operator = 6 Formula =50, условие будет применено к значениям равным <50)

Formula2:

Используется, если Operator = xlBetween или xlNotBetween

Пример:

  /// окрашиваем с помощью условий формативности
Selection = Data.Range('E'& early; 'BE'& early)
Selection.FormatConditions.Add(1;3;'=""')
Selection.FormatConditions(1).Interior.ColorIndex  = 37  // голубенький 

 

Аяз Садыков: обновлено 19.04.2018 в 14:22
Тарас Асачёв

Святые люди...

Вот и от меня кусочек:

WorkSheet.PageSetup.Orientation = 2 // Ориентация, 1-Вертикаль, 2-Горизонт

 

Тарас Асачёв

Пока моя память свежа... Тэги: [Сортировка, Excel, Sort, AutoFilter]

// одно из предложений с форума. Но оно не полное!!!
Excel = CreateObject("Excel.Application")
Excel.Visible = True
Book = Excel.Workbooks.Add()
WorkSheet = Book.WorkSheets(1)  
Range = WorkSheet.Range("A1:A6") // или иной ваш диапазон заголовков
Range.AutoFilter // Это важно и в решении этого нет
Range.Sort(WorkSheet.Range("A1")) // Вот это место, где никто не дописал, что тут надо расставлять параметры! Данная строка не работает, потому что в ней нет сымсла!
// Никто не написал, что пресловутое "SortFields" не нужно и не надо пытаться его гонять!
/* Суть: Синтаксис
Expression. Sort (сортировка ) (Key1, Order1, key2, Type, Order2, Key3, Order3, Header, ордеркустом, MatchCase, Orientation, сортмесод, _ DataOption1_, DataOption2, DataOption3) */
// И это значит, что хватит и тех скобок, что уже есть!
Range.Sort(WorkSheet.Range("A2"); 2) // и оно уже сортируется! потому что вы указали Key1 и Order1!!! 

Хоть бы самому не забыть и вдруг кому понадобится - искать долго не придется... 

Сергей Меньших

Оформление с помощью стилей

// Определение количества колонок
ColumnsCount = Sheet.UsedRange.Columns.Count // Количество колонок ColumnNames = ArrayOF('A'; 'B'; 'C'; 'D'; 'E'; 'F'; 'G'; 'H'; 'I'; 'J'; 'K'; 'L'; 'M'; 'N'; 'O'; 'P'; 'Q'; 'R'; 'S'; 'T'; 'U'; 'V'; 'W'; 'X'; 'Y'; 'Z') 
ColumnNameLast = ColumnNames[ColumnsCount - 1] // Имя последней колонки

// Оформление документа
  xlHAlignCenter = -4108
  xlHAlignLeft = -4131   
  xlHAlignRight = -4152
  xlVAlignBottom = -4107
  xlVAlignCenter = -4108
  xlVAlignTop = -4160  
  
  // Стили ячеек
  СтильШапкаТаблицы = Book.Styles.Add('СтильШапкаТаблицы')
  СтильШапкаТаблицы.Borders(1).LineStyle = 1
  СтильШапкаТаблицы.Borders(2).LineStyle = 1
  СтильШапкаТаблицы.Borders(3).LineStyle = 1
  СтильШапкаТаблицы.Borders(4).LineStyle = 1
  СтильШапкаТаблицы.WrapText = TRUE
  СтильШапкаТаблицы.HorizontalAlignment = xlHAlignCenter
  СтильШапкаТаблицы.VerticalAlignment = xlVAlignCenter
  СтильШапкаТаблицы.Font.Size = 8
  СтильШапкаТаблицы.Font.Bold = TRUE

  // Шапка таблицы
  ШапкаТаблицы = Range.Range(Format('A%0:s:%1:s%0:s'; ArrayOf(HeaderRowNumber; ColumnNameLast))) 
  ШапкаТаблицы.Style = 'СтильШапкаТаблицы'
  ШапкаТаблицы.Interior.Color = 15128749
  Sheet.Rows(HeaderRowNumber).RowHeight = 30
  ШапкаТаблицы.Columns.AutoFit
  Sheet.Columns(КолонкаВидДокумента).ColumnWidth = 50
  Sheet.Columns(КолонкаВАрхиве).ColumnWidth = 15
  Sheet.Columns(КолонкаНеВАрхиве).ColumnWidth = 15

Пример использования в отчёте по документам в файловом архиве

Прикреплен файл: ReportArchive.zip
 

Сергей Меньших

По сортировке я MSDN курил https://docs.microsoft.com/en-us/office/vba/api/excel.sortfields.add

   

    xlSortOnValues = 0
    xlAscending = 1
    xlSortNormal = 0
    xlYes = 1
    xlTopToBottom = 1
    xlPinYin = 1
    SheetSort = Sheet.Sort
    SheetSort.SortFields.Clear()
    SheetSort.SortFields.Add(Sheet.Columns(1); xlSortOnValues; xlAscending; xlSortNormal)
    SheetSort.Header = xlYes
    SheetSort.MatchCase = False
    SheetSort.Orientation = xlTopToBottom
    SheetSort.SortMethod = xlPinYin
    SheetSort.Apply    

 

Аяз Садыков

Значения ColorIndex

Цвет

 

Аяз Садыков: обновлено 29.10.2019 в 09:00
Mikhail Popkov

Для добавления листа обычно используется Excel.WorkSheets.Add - новый лист будет расположен слева от первого. Для добавления листа справа согласно: параметрам: Excel.WorkSheets.Add(Before,After,Count,Type) - в ISBL будет:

Excel.WorkSheets.Add(null; Excel.WorkSheets(Excel.WorkSheets.Count))

Валерий Самарин

    // Закрепить область (4 первые строки)
    Excel.ActiveWindow.SplitRow = 4
    Excel.ActiveWindow.FreezePanes = True

Татьяна Ларионова

Добрый день, подскажите как можно выделить например строку, программно нажать "Формат по образцу", затем выделить другую строку и применить формат? очень нужно, помогите пожалуйста!

Тарас Асачёв

Никто не написал, а я напишу! Переименование листов!

Excel = CreateObject("Excel.Application")                                     //
Excel.Visible = TRUE                                              
ExcelBook = Excel.Workbooks.Add         
ExcelBook.Sheets.Item(1).Name = 'Первый лист'                                    
WorkSheet = ExcelBook.Sheets.Item(1)             
ExcelBook.Sheets.Add.Name = 'Второй лист'                                        
ExcelBook.Sheets.Item('Второй лист')

 

Авторизуйтесь, чтобы написать комментарий