Работа с Excel

Программирование - Практика программирования

Собрал различные полезности для работы с Excel из 1С. Иногда приходится форматировать документ Excel программно из 1С. Так вот, чтобы не искать постоянно на просторах интернета как сделать левое выравнивание в ячейке Excel из 1С и т.п. решил опубликовать это...

1. Подключение к Excel

Попытка
    Excel = Новый COMОбъект("Excel.Application"); 
Исключение
    Сообщить(ОписаниеОшибки() + "Возможно программа Exсel не установлена на данном компьютере!");
    Возврат Ложь;
КонецПопытки;

Книга = Excel.WorkBooks.Add();  //Создание книги
Книга = Excel.WorkBooks.Open(ПутьКФайлу); //Открытие существующей книги
Лист = WorkBook.WorkSheets(НомерЛиста); //Выбор рабочего листа по номеру
Лист = WorkBook.WorkSheets(ИмяЛиста);   //Выбор рабочего листа по имени

//Сохранение книги
Попытка
	Книга.SaveAs(ПутьКФайлу);
	Книга.Close();
	Excel.Quit();	
Исключение
	Книга.Close();
	Excel.Quit();
КонецПопытки;

 

2. Запись значения в ячейку

ТекущийЛист.Cells(i, j).Value = "2";

 

3. Шрифт и размер шрифта 

Excel.Columns("A:H").Select();
Лист.Cells.Font.Size = 12;                        
Лист.Cells.Font.Name = "Calibri";

 

4. Установка фильтра

Лист.Cells.AutoFilter();

 

5. Горизонтальное выравнивание ячейки

ТекущийЛист.Cells(i, j).HorizontalAlignment = -4130;

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

Константа в Excel

Значение в ISBL

По центру

xlHAlignCenter

-4108

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

xlHAlignCenterAcrossSelection

7

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

xlHAlignDistributed

-4117

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

xlHAlignFill

5

По значению

xlHAlignGeneral

1

По ширине

xlHAlignJustify

-4130

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

xlHAlignLeft

-4131

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

xlHAlignRight

-4152

 

 

6. Вертикальное выравнивание ячейки

ТекущийЛист.Cells(i, j).VerticalAlignment = -4130;

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

Константа в Excel

Значение в ISBL

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

xlVAlignBottom

-4107

По центру

xlVAlignCenter

-4108

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

xlVAlignDistributed

-4117

По высоте

xlVAlignJustify

-4130

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

xlVAlignTop

-4160


7. Установка свойства ячейки "переносить по словам"

ТекущийЛист.Cells(i, j).WrapText = True;

 

8. Отключение/включение режима показа предупреждений

ExcelApp.DisplayAlerts = False; // отключение
ExcelApp.DisplayAlerts = True;  // включение

 

9. Формулы

ТекущийЛист.Cells(i, j).Formula = "SUM(A1:A10)";

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

ТекущийЛист.Cells(i, j).FormulaLocal = "Сумм(A1:A10)";

 

10. Таблица цветов с индексами

Таблица цветов

ТекущийЛист.Cells(i, j).Font.ColorIndex = 8;

 

11. Установка границы ячейки и тип линии границы

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

Тип линии

Значение в Excel

Значение в ISBL

Нет линии

xlLineStyleNone

0

Непрерывная

xlContinuous

1

В виде тире и точек

xlDashDot

4

В виде тире и двойных точек

xlDashDotDot

5

В виде точек

xlDot

8

В виде двойной линии

xlDouble

9

В виде наклонной пунктирной

xlSlantDashDot

13

 

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

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

Значение в Excel

Значение в ISBL

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

xlDiagonalDown

5

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

xlDiagonalUp

6

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

xlEdgeLeft

7

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

xlEdgeTop

8

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

xlEdgeBottom

9

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

xlEdgeRight

10

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

xlInsideVertical

11

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

xlInsideHorizontal

12

 

См. также

Комментарии
1. script Мальчинко (script) 201 24.10.17 00:57 Сейчас в теме
Допишите еще один универсальный способ. Через макросы. Включаем запись макроса. Меняем что нужно в таблице. Завершаем макрос. Смотрим код в VB редакторе.
fokses; Михаська; Lem0n; bajiepka; frkbvfnjh; Sitial; Alias; maxdmt; chebser; olbu; zqzq; Xershi; +12 Ответить
5. Гайк Аракелян (arakelyan) 191 24.10.17 08:01 Сейчас в теме
7. Андрей Андреев (user662672_explorer2000) 62 24.10.17 08:04 Сейчас в теме
(1) Тоже всегда считал этот метод 100% работающим, но когда возникла необходимость программно заменить картинку на листе, то запись макроса вернула пустую процедуру (( пришлось много и долго читать интернет
8. Гайк Аракелян (arakelyan) 191 24.10.17 08:11 Сейчас в теме
(7) Программно заменить картинку можно напрямую из 1С, однако появляется проблема с её позиционированием. Влить картинку ровно в ячейку Excel только средствами 1С у меня не вышло, пришлось добавлять макрос в VBA. Если тема интересна, могу добавить в статью.
9. Андрей Андреев (user662672_explorer2000) 62 24.10.17 08:39 Сейчас в теме
(8) Задача у меня такого плана: на листе много картинок высокого качества, из-за чего файл вести очень много. Нужно картинки заменить на картинки с качеством пониже. Как понизить качество я представляю, как картинку забрать с листа тоже, но как ее заменить пока не очень понимаю, еще ищу, благо терпит. Буду благодарен если подскажете
19. splxgf (splxgf) 24.10.17 22:58 Сейчас в теме
(9)
(8) Задача у меня такого плана: на листе много картинок высокого качества, из-за чего файл вести очень много. Нужно картинки заменить на картинки с качеством пониже. Как понизить качество я представляю, как картинку забрать с листа тоже, но как ее заменить пока не очень понимаю, еще ищу, благо терпит. Буду благодарен если подскажете

В экселе есть встроенная функция понижения качества всех картинок или выделенной.
При очень большом желании это можно сделать средствами VBA.
https://infostart.ru/public/19977/
20. Андрей Андреев (user662672_explorer2000) 62 25.10.17 06:39 Сейчас в теме
(19) Экселевская функция, к сожалению, не дает нужного результата. Пробовал вручную, файл практически не уменьшается в размерах.
28. splxgf (splxgf) 26.10.17 15:00 Сейчас в теме
(20) Если делали вручную, задавали качество для экрана и применяли ко всем картинкам, то уменьшение размера должно было произойти.
Если нет, то маловероятно что достигнете лучших результатов дополнительными скриптами.
Кстати с заменой картинки проблем нет, делал такой код на экселе, но он сильно зависит от версии.
Находите картинку, забираете ее, запоминаете характеристики (положение, размер), и удаляеете...
Затем вставляете уже сжатую через ActiveSheet.Shapes.AddPicture
23. Денис Мельников (Mi11er) 16 25.10.17 13:23 Сейчас в теме
Вставлю свою пять копеек, так как сам намучался с excel

Объединение яйчеек

Лист.Range("E1:F1").MergeCells = Истина; 


Может уже было, но все же
Цвет фона
Лист.Cells(2,3).Interior.ColorIndex = 42;


ГиперСсылки
Лист.Cells(СтрокаЛиста, 2).Hyperlinks.Add(Лист.Cells(СтрокаЛиста, 2), Выборка.МДО_URLТовара,,"Ссылка на товар с сайта www.___.ru",Выборка.Артикул );


Закрепление областей
Эксель.Application.ActiveWindow.SplitRow = 4;
Эксель.Application.ActiveWindow.FreezePanes = 1;


Ширина колонки
Лист.Columns(7).ColumnWidth = "10";
Михаська; Il; IgorS; +3 Ответить
15. Александр Полетаев (Alias) 115 24.10.17 14:51 Сейчас в теме
Из опыта собственной необходимости:
- к пункту 9: формулы можно задавать также через FormulaR1C1 и FormulaR1C1Local, это бывает много удобнее тем что не нужно постоянно менять буквы в формуле -- она одна и та же для всех ячеек, например расчёт НДС по сумме из предыдущей колонки "=ROUND(RC[-1]*1.18, 2)" для всех строк колонки.
- к пункту 10: заливка фона это Ячейка.Interior.ColorIndex
- Ячейка.Locked = False; это отключить защиту ячейки, после чего Лист.Protect("p@ssw0rd") -- защитить лист от изменений.

Всецело присоединяюсь также к комментарию (1).

P.S. И да, спасибо, отличная шпаргалка :)
18. Евгений Марков (Sitial) 24.10.17 18:29 Сейчас в теме
Вставлю свои пять копеек)

Исходные данные:
Excel = Новый COMОбъект("Excel.Application");
ИмяФайлаПолное = ПолучитьИмяВременногоФайла("xlsx");
Книга = Excel.WorkBooks.Open(ИмяФайлаПолное);
Лист1 = Книга.WorkSheets(1);


Странно, что никто не написал про то, чтобы приложение было вообще видно, а не работало как-то там в фоне:
Excel.Application.Visible = Истина;


Вызов функции на листе (на примере функции "ПОИСКПОЗ()", в англ.как "Match"):
// ИмяДляПоиска - то, что ищем
// Лист1.Columns(2) - диапазон поиска
// 0 - первое точное совпадение (может также принимать значения "1" или "-1"
НомерСтроки = Excel.WorksheetFunction.Match(ИмяДляПоиска,Лист1.Columns(2),0);


Поиграем немного с форматом сразу для диапазона ячеек:
Диапазон = Лист1.Range(Лист1.Cells(Строка1,Колонка1), Лист1.Cells(Строка2, Колонка2));
	Диапазон.Font.Italic = 1; // наклонный шрифт
	Диапазон.Font.Bold = 1; // жирный шрифт
	Если Формат = "Текст" Тогда
		Диапазон.NumberFormat = "@"; // текстовый формат
	ИначеЕсли Формат = "Число" Тогда
		Диапазон.NumberFormat = "# ##0,00"; // числовой формат с разделителями групп разрядов
		Диапазон.NumberFormat = "# ##0,00;-# ##0,00;""-"""; // тоже самое, только вместо нуля выводится прочерк + при автосуммировании нет ошибки
	ИначеЕсли Формат = "ЧислоЦелое" Тогда
		Диапазон.NumberFormat = "# ##0;-# ##0;""-"""; // округление до целого
	ИначеЕсли Формат = "ВыделитьЦветом" Тогда
		R = 255;
		G = 255;
		B = 153;
		Диапазон.Interior.Color = ((B*256) + G) * 256 + R; // цвет ячейки
	ИначеЕсли Формат = "ОбщийТекст" Тогда
		Диапазон.Value = ОбщийТекст; // во всех ячейках диапазона устанавливаем данный текст
	КонецЕсли;
Показать


Как получить номер столбца в формате Excel (работает и для колонок вида AAA...ZZZ)
&НаКлиенте
Функция НомерСтолбцаВФорматеExcel(Знач НомерКолонки)
	Алфавит = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
	ИмяКолонки = "";
	
	Пока НомерКолонки <> 0 Цикл
		Остаток = (НомерКолонки - 1)%26;
		
		Буква = Сред(Алфавит, Остаток + 1, 1);
		ИмяКолонки = Буква + ИмяКолонки;
		
		НомерКолонки = Цел((НомерКолонки - Остаток)/26);
	КонецЦикла;
	
	Возврат ИмяКолонки;
КонецФункции
Показать


Установка формулы автосуммирования, используя функцию выше:
Диапазон = "" +НомерСтолбцаВФорматеExcel(Колонка1) + (НачальнаяСтрока) + ":" +
					НомерСтолбцаВФорматеExcel(Колонка2) + (КонечнаяСтрока); // Колонка2 = Колонка1 для случая, если это один столбец
		
		Лист1.Cells(Строка, Колонка).FormulaLocal = "=СУММ(" +Диапазон+ ")";
rpgshnik; Il; inf012; +3 Ответить
21. Андрей Лукин (frkbvfnjh) 250 25.10.17 07:30 Сейчас в теме
(1) Зачем раскрываешь секреты Масонов?! :)
2. Denis A (Denis Nsk) 24.10.17 05:46 Сейчас в теме
Спасибо за Ваши труды!
Лично для меня, полезная шпаргалка.
excepter; chebser; arakelyan; +3 Ответить
3. Александр Лыткин (TrinitronOTV) 24.10.17 05:49 Сейчас в теме
В пункте 11 почему-то нет примера кода на языке 1С
6. Гайк Аракелян (arakelyan) 191 24.10.17 08:02 Сейчас в теме
(3)Он, был....но таинственным образом куда-то пропал. Добавлю сегодня.
4. Алексей (ADirks) 179 24.10.17 06:38 Сейчас в теме
ещё в шпору

последняя строка
xlLastCell = 11;
LastRow = Sh.Cells(1,1).SpecialCells(xlLastCell).Row;

чтобы не умничал, пытаясь превращать строки в числа и даты (особенно бесит)
Sh.Cells(Row, i).NumberFormat = "@";
Sh.Cells(Row, i).Value = Value;

иногда и это не помогает, тогда так
Sh.Cells(Row, i).Value = "'" + Value;
arakelyan; +1 Ответить
10. Кирилл kirillkr (kirillkr) 27 24.10.17 08:59 Сейчас в теме
Не хватает информации, в каком формате сохранить книгу, которую как раз искал несколько дней назад.
Если Число(Excel.Version) < 12 Тогда
	// Используется Excel 97-2003
	пРасширение = "xls";
	пФорматФайла = -4143;
Иначе
	Если впИспользуемМакросы Тогда
		пРасширение = "xlsm";
		пФорматФайла = 52;
	Иначе
		пРасширение = "xlsx";
		пФорматФайла = 51;
	КонецЕсли;
КонецЕсли;
Показать
arakelyan; +1 Ответить
11. Олег Николаев (o.nikolaev) 199 24.10.17 09:03 Сейчас в теме
Нет:
- добавления картинок, позиционирования на нужной картинке и установке ее свойств;
- запись, чтение очень больших объемов данных в (из) файла Excel;
- работы с диаграммами.
arakelyan; +1 Ответить
12. gorakh I (gorakh) 17 24.10.17 09:17 Сейчас в теме
ДокументExcel = ПолучитьCOMОбъект(ИмяФайла);
//Листы :
				    СписокЛистов = Новый СписокЗначений;					
				Для каждого Лист Из ДокументExcel.Sheets Цикл
				        СписокЛистов.Добавить(Лист.Index,Лист.Name);
				КонецЦикла;	

ЛистExcel= ДокументExcel.Sheets(НомерЛистаКниги);
ПоследняяСтрока = ЛистExcel.UsedRange.Rows.Count;
ЛеваяКолонка = ЛистExcel.UsedRange.Column;
ПоследняяКолонка =  ЛистExcel.UsedRange.Columns.Count;
НомерКолонкиСТекстом=ЛистExcel.UsedRange.Find("text").Column;
//Или
НомерКолонкиСТекстом=ЛистExcel.Cells.Find("text").Column;
//Подчищаем за собой память
ДокументExcel.Application.Quit();
Показать
13. Сергей Зеркин (sergalei) 24.10.17 10:36 Сейчас в теме
Дамп листа в массив.
Значительно ускоряет обработку больших таблиц.

Функция ПолучитьМассивИзЛистаЭксель(Путь, НомерЛиста)
	
	Попытка
		Эксель = Новый ComОбъект("Excel.Application");
		Эксель.DisplayAlerts = 0;
		Книга = Эксель.Workbooks.Open(Путь);
	Исключение		
		Возврат Неопределено;
	КонецПопытки;
		
	Результат = Эксель.Sheets(НомерЛиста).UsedRange.Value.Выгрузить();
	Эксель.Quit();
	
	Возврат Результат;
	
КонецФункции
Показать
lishniy; klinval; Михаська; &rew; AtPups000; kare; the1; maxdmt; arakelyan; +9 Ответить
14. maxdmt (maxdmt) 15 24.10.17 11:16 Сейчас в теме
(13), подробнее
Функция ПрочитатьЛистExcel(ТЗ = Неопределено, ЛистЭксель = Неопределено, НомерПервойСтроки = 1, НомерПервойКолонки = 1, ВсегоСтрок = 0, ВсегоКолонок = 0) Экспорт
	
	Если ЛистЭксель = Неопределено Тогда
		ЛистЭксель = ПолучитьCOMОбъект(,"Excel.Application");
	КонецЕсли;
	Если ВсегоСтрок = 0 Тогда
		ВсегоСтрок = ЛистЭксель.Cells.SpecialCells(11).Row;
	КонецЕсли;
	Если ВсегоКолонок = 0 Тогда
		ВсегоКолонок = ЛистЭксель.Cells.SpecialCells(11).Column;
	КонецЕсли;
	Если ТЗ = Неопределено Тогда
		ТЗ =  Новый ТаблицаЗначений;
		Для Счетчик = 1 По ВсегоКолонок Цикл
			ТЗ.Колонки.Добавить("Колонка"+Счетчик, Новый ОписаниеТипов("Строка"));
		КонецЦикла;
	КонецЕсли;
	Состояние("Заполнение пустой таблицы...");
	Для Счетчик = НомерПервойСтроки По ВсегоСтрок Цикл
		НоваяСтрока = ТЗ.Добавить();
	КонецЦикла;
	
	Состояние("Выгрузка в массив...");
	Область = ЛистЭксель.Range(ЛистЭксель.Cells(НомерПервойСтроки,НомерПервойКолонки), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
	Данные = Область.Value.Выгрузить();
	
	Для Счетчик = 0 По ВсегоКолонок-1 Цикл
		Состояние("Загрузка колонки: " + (Счетчик + 1) + " из " + ВсегоКолонок);
		ТЗ.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);
	КонецЦикла;
	//ЛистЭксель = Неопределено;
	Возврат ТЗ;
КонецФункции
Показать
25. Андрей Суханцов (&rew) 7 26.10.17 05:43 Сейчас в теме
(13) Да и в принципе зачем держать Ексель открытым занимая без дела ресурсы.
16. Дмитрий Королев (ArchLord42) 58 24.10.17 15:20 Сейчас в теме
Группировка строк, аналогичная табличному документу

Лист.Range("A1:A3").Rows.Group()


Собственно чтобы сгрупиировать колонки нужно обратиться не к Rows, а к Cols

так же можно настроить способ вывода значка с "+", либо сверху, либо снизу

Лист.Outline.SummaryRow = 0;


0 - вверху
1 - внизу (по умолчанию)

и конечно же имеется аналог метода "ПоказатьУровеньГруппировокСтрок/ПоказатьУровеньГруппировокК­олонок"

Лист.Outline.ShowLevels(УровеньГруппировки)
17. Владимир (vladismi) 157 24.10.17 15:37 Сейчас в теме
Хорошая шпаргалка.
Плюс!
22. Максим *** (premier) 140 25.10.17 08:21 Сейчас в теме
(0)
чтобы не искать постоянно на просторах интернета
достаточно в редакторе VBA открыть Object Browser (F2). Он содержит описание объектов MS Office, значения констант VBA, а также встроенную справочную систему. Видимо не зря шутка появилась, что программер нажимает F1, когда других возможностей что-то узнать о программе уже не осталось )))
26. Алексей (ADirks) 179 26.10.17 09:34 Сейчас в теме
(22) так то да, но быстрее в шпаргалку посмотреть. Задачи то обычно весьма однотипные.
24. Олег Валуйский (waol) 227 25.10.17 18:32 Сейчас в теме
как говорится - на полку в качестве справочного пособия
27. Николай Коноводов (niki295) 5 26.10.17 10:20 Сейчас в теме
29. Дмитрий Котов (rpgshnik) 127 02.11.17 05:20 Сейчас в теме
Статья хорошая, в закладки.
Предлагаю автору, обновить статью добавив выдержки из комментарий, очень полезно и информативно будет.
arakelyan; +1 Ответить
30. Гайк Аракелян (arakelyan) 191 07.11.17 11:21 Сейчас в теме
(29)Добавлю обязательно, занят немного на работе. Я ещё не все свои мысли изложить успел)
rpgshnik; +1 Ответить
Оставьте свое сообщение