Работа с excel из 1С

1. Создание объекта приложение Excel
2. Программное открытие файла Excel, либо его создание при отсутствии
3. Обращение к листу открытого файла Excel
4. Обойти все листы и скрыть их по условию
5. Добавить лист в самый конец(по умолчанию он добавляется в самое начало)
6. Очистить лист
7. Получить указанную область листа.
8. Скопировать область из одной книги в другу.
9. Функция по переводу Адреса стиля ссылок Excel из A1 в R1C1 на языке 1С.

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

В этой статье приведу несколько листингов которые облегчат понимание этой связки и помогут в написании своего кода. Последующие листинги опираются на предыдущие, поэтому если в листинге, например, используется переменная «ПриложениеЭксель», но не инициализируется, значит это было в одном из предыдущих листингов.

1. Создание объекта приложение Excel

Попытка
    ПриложениеЭксель = Новый COMОбъект("Excel.Application")
Исключение
    Предупреждение("ОШИБКА создания приложения Excel, возможно программа не установлена!");
КонецПопытки;

2. Программное открытие файла Excel, либо его создание при отсутсвии

ПутьКФайлу = “C:\obmen\zagruzka.xls”;
ФайлЭксель = Новый Файл(ПутьКФайлу);
Если ФайлЭксель.Существует() Тогда
    Результат = ПриложениеЭксель.WorkBooks.Open(ПутьКФайлу);
Иначе
    РабочаяКнига=ПриложениеЭксель.Workbooks.Add();
    РабочаяКнига=ПриложениеЭксель.ActiveWorkbook;
    ПриложениеЭксель.ActiveWorkbook.SaveAs(ПутьКФайлу,-4143);
    Результат = ПриложениеЭксель.WorkBooks.Open(ПутьКФайлу);
КонецЕсли;

3. Обращение к листу открытого файла Excel

ЛистЭксель = ПриложениеЭксель.WorkBooks.Open(ПутьКФайлу).WorkSheets.Item(ИмяЛиста);

4. Обойти все листы и скрыть их по условию

КоличествоЛистов = ПриложениеЭксель.ActiveWorkbook.Worksheets.Count;
КоллекцияЛистов = ПриложениеЭксель.ActiveWorkbook.Worksheets;

Для ТекНомерЛиста = 1 По КоличествоЛистов  Цикл
    ТекЛист = КоллекцияЛистов(ТекНомерЛиста);
    Если ТекЛист.Name = «Лист1» Тогда	
        ТекЛист.Visible = False;
    КонецЕсли;
КонецЦикла;

5. Добавить лист в самый конец(по умолчанию он добавляется в самое начало)

НомерПоследнегоЛиста = ФайлЭксель.WorkSheets.Count;
ДобавленныйЛист = ФайлЭксель.WorkSheets.Add(,ФайлЭксель.WorkSheets(НомерПоследнегоЛиста));
ДобавленныйЛист.Name = ИмяЛиста;

6. Очистить лист

ДобавленныйЛист.Cells.Clear();

7. Получить указанную область листа. Здесь используются переменные НомСтрВерх, НомСтрНиз, НомКолВерх, НомКолНиз – они обозначают координаты самой левой верхней ячейки и самой правой нижней ячейки областей

ОбластьОграниченнаяСтроками = ЭксельФайл.WorkSheets.Item(ИмяЛиста).Rows(""+НомСтрВерх+":"+НомСтрНиз);

ЯчейкаВерхняяЛевая = ЭксельФайл.WorkSheets.Item(ИмяЛиста).Cells(НомСтрВерх, НомКолВерх);
ЯчейкаНижняяПравая = ЭксельФайл.WorkSheets.Item(ИмяЛиста).Cells(НомСтрНиз, НомКолНиз);
ОбластьОграниченнаяЯчейками = ЭксельФайл.WorkSheets.Item(ИмяЛиста).Range(ЯчейкаВерхняяПравая,ЯчейкаНижняяЛевая);

8. Скопировать область из одной книги в другу. О константах Excel(используемых числах) читайте в разделе «В заключении» данной статьи.

НоваяКнига = ПриложениеЭксель.WorkBooks.Open(ПутьКНовомуФайлу); 
ЛистПриемник = НоваяКнига.WorkSheets.Item(ИмяЛистаПриемника);
ОбластьОграниченнаяЯчейками.Copy(); //записали в буфер
ЛистПриемник.Range(ЛистПриемник.Cells(ТекСтрока, ТекСтолбец), ЛистПриемник.Cells(ТекСтрока+КоличСтрок-1, ТекСтолбец+КоличСтолбцов-1)).PasteSpecial();
ЛистПриемник.Range(ЛистПриемник.Cells(ТекСтрока, ТекСтолбец), ЛистПриемник.Cells(ТекСтрока+КоличСтрок-1, ТекСтолбец+КоличСтолбцов-1)).PasteSpecial(8);// цифра 8 означает сохранить ширину столбцов как в источнике.

9. Функция по переводу Адреса стиля ссылок Excel из A1 в R1C1 на языке 1С.

Чтобы конвертировать стиль ссылок Excel с буквенными колонками в числовые можно из кода ниже сделать функцию с параметром АдресA1

ДлинаАдресаА1 = СтрДлина(АдресА1);
Для с=1 По ДлинаАдресаА1 Цикл
	  ТекСимвол = Сред(АдресА1,с,1);
	  Если Найти("0123456789", ТекСимвол) > 0 Тогда //начался номер строки
		  
		  БуквИндексКолонки = Лев(АдресА1, с-1);
		  
		  ДлинаИндекса = СтрДлина(БуквИндексКолонки);
		  НомерКолонкиЧислом = 0;
		  Для с = 1 По ДлинаИндекса Цикл
			  ТекСимв = Сред(БуквИндексКолонки, с, 1);
			  ИндексБуквы = Найти(ВРег("abcdefghijklmnopqrstuvwxyz"), ВРег(ТекСимв));
			  НомерКолонкиЧислом = НомерКолонкиЧислом * 26 + ИндексБуквы;
		  КонецЦикла;
		  НомерСтроки = Прав(АдресА1, ДлинаАдресаА1 - с + 1);
		  НомерСтрокиЧислом = Число(НомерСтроки);
		  Прервать;
	  КонецЕсли;
КонецЦикла;
АдресR1C1  = "R" + НомерСтрокиЧислом + "C" + НомерКолонкиЧислом;

В заключении

В некоторых примерах параметрами в методы Excel передаются числа. Это происходит из-за того, что 1С не знает о константах Excel, например «xlCellTypeLastCell». Но к счастью эти константы имеют числовые значения. Чтобы узнать их откройте справку excel. Если справка не установлена, то можно посмотреть значение в режиме отладки Excel.