1С 8.3 Excel
Данные > Примеры кода 1С > 1С 8.3 Excel
1С 8.3 и Excel совместимы с самых первых версий платформы: любую печатную форму можно сохранить в электронную таблицу (*.xls,*.xlsx) через Меню (Еще..) -> Сохранить. Некоторые документы в типовых конфигурациях имеют встроенную функцию выгрузки в электронную таблицу. Загрузить информацию в 1С из Excel штатными средствами до последнего времени было невозможно. Только в крайних релизах 1С 8.3 была реализована возможность загрузки информации в документы закупок и продаж из excel-файла (*.xls,*.xlsx) через буфер обмена (кнопка над табличной частью "Загрузить из внешнего файла"). Однако, типовые механизмы подходят только для узкого круга задач. Для решения более широкого спектра задач по экспорту/импорту данных в/из Excel средствами 1С 8.3 создаются команды, в которых используется палитра встроенного языка. Для примера, к скачиванию доступна обработка "Загрузка номенклатуры из Excel".
- Для работы 1С 8.3 с Excel используется COM объект типа "Excel.Application" (через OLE, нужен установленный MS Excel) или "ADODB.Connection" (ActiveX Data Object, нужен установленный драйвера ODBC) или "COMSafeArray" (через OLE, нужен установленный драйвер ADODB).
- При загрузке из Excel в управляемом приложении можно выполнять как &НаКлиенте так и на &НаСервере. Используется временный каталог стандартного пользователя USR1CV82.
- На дисках ИТС и на портале 1С, в меню "Технологическая поддержка" -> "Универсальные отчеты и обработки" -> есть типовая обработка "Загрузка данных из табличного документа".
- У табличного документа есть метод "Прочитать". Отличная альтернатива COM объекту - актуально для файлов больших объемов.
Оглавление:
Выбор Excel файла в 1С 8.3:
&НаКлиенте
Процедура ФайлНачалоВыбора(Элемент, ДанныеВыбора, СтандартнаяОбработка) //необходимо в событии "НачалоВыбора" поля ввода вызвать ДиалогВыбораФайла
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Заголовок = "Выберите файл Excel";
Диалог.ПредварительныйПросмотр = Ложь;
Диалог.Фильтр = "(*.xls,*.xlsx)|*.xls;*.xlsx;|Microsoft Excel 97/2000/XP/2003 (*.xls)|*.xls|Microsoft Excel 2007/2010 (*.xlsx)|*.xlsx";
Если ЗначениеЗаполнено(Объект.Файл) Тогда
Диалог.ПолноеИмяФайла= Объект.Файл;
КонецЕсли;
Если Диалог.Выбрать() Тогда
Объект.Файл = Диалог.ПолноеИмяФайла;
КонецЕсли;
КонецПроцедуры
Процедура ФайлНачалоВыбора(Элемент, ДанныеВыбора, СтандартнаяОбработка) //необходимо в событии "НачалоВыбора" поля ввода вызвать ДиалогВыбораФайла
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Заголовок = "Выберите файл Excel";
Диалог.ПредварительныйПросмотр = Ложь;
Диалог.Фильтр = "(*.xls,*.xlsx)|*.xls;*.xlsx;|Microsoft Excel 97/2000/XP/2003 (*.xls)|*.xls|Microsoft Excel 2007/2010 (*.xlsx)|*.xlsx";
Если ЗначениеЗаполнено(Объект.Файл) Тогда
Диалог.ПолноеИмяФайла= Объект.Файл;
КонецЕсли;
Если Диалог.Выбрать() Тогда
Объект.Файл = Диалог.ПолноеИмяФайла;
КонецЕсли;
КонецПроцедуры
Загрузка из Excel файла (через COMОбъект) в 1С 8.3:
&НаСервере
Процедура ЗагрузитьИзXLS()// в документ Поступление материалов
// Попытка открытия файла Excel
Попытка
Excel_App = Новый COMОбъект("Excel.Application");
Excel_App.WorkBooks.Open(Объект.Файл);
Исключение
Сообщить("Произошла ошибка при открытии файла "+СокрЛП(ОписаниеОшибки())+"! Операция прервана!");
Возврат;
КонецПопытки;
// Попытка выбрать первый лист (можно указать другой)
Попытка
Excel_App.Sheets(1).Select();
Исключение
// Если первый лист не найден - закрываем файл
Excel_App.ActiveWorkbook.Close();
Excel_App = 0;
Сообщить("Первый лист не найден!");
ОтменитьТранзакцию();
Возврат;
КонецПопытки;
// Вычисление количества строк и колонок в зависимости от версии Excel
version_Ex = Лев(Excel_App.Version, Найти(Excel_App.Version,".")-1);
Если version_Ex = "8" тогда
Колич_Строк = Excel_App.Cells.CurrentRegion.Rows.Count;
Колич_Колонок = Макс(Excel_App.Cells.CurrentRegion.Columns.Count, 13);
Иначе
Колич_Строк = Excel_App.Cells(1,1).SpecialCells(11).Row;
Колич_Колонок = Excel_App.Cells(1,1).SpecialCells(11).Column;
Конецесли;
// Имена колонок в файле должны совпадать с именами реквизитов табличной части, в которую загружаются данные
// Переменная ТЗ_Колонки содержит список номеров колонок, которые будут перегружаться
ТЗ_Колонки = Новый ТаблицаЗначений;
ТЗ_Колонки.Колонки.Добавить("НомерКолонки");
ТЗ_Колонки.Колонки.Добавить("НазваниеКолонки");
Для Каждая_Колонка = 1 по Колич_Колонок Цикл
ИмяКолонки = Excel_App.Cells(1, Каждая_Колонка).Text;
ИмяБезПробелов = СтрЗаменить(ИмяКолонки," ",""); // Удаление лишних пробелов из имен колонок
// Проверка наличия реквизитов табличной части "Материалы" в документе "ПоступлениеМатериалов"
Если НЕ ПроверкаРеквизитаТЧ(ИмяБезПробелов, Метаданные.Документы.ПоступлениеМатериалов, "Материалы") Тогда
Сообщить("Не найден реквизит с именем " + ИмяБезПробелов + "! Колонка не будет загружена!");
Иначе
Новая_Строка = ТЗ_Колонки.Добавить();
Новая_Строка.НомерКолонки = Каждая_Колонка;
Новая_Строка.НазваниеКолонки = ИмяБезПробелов;
КонецЕсли;
КонецЦикла;
// Если есть колонки для загрузки и есть колонка "Материалы" (обязательная к заполнению)
Если ТЗ_Колонки.Количество() <> 0 и
ТЗ_Колонки.НайтиСтроки(Новый Структура("НазваниеКолонки", "Материалы")).Количество() <> 0 Тогда
// Создание документа и заполнение реквизитов шапки
Тек_Документ = Документы.ПоступлениеМатериалов.СоздатьДокумент();
Тек_Документ.Комментарий = "Загружено из файла " + Объект.Файл;
Тек_Документ.Дата = ТекущаяДата();
Тек_Документ.Ответственный = ПараметрыСеанса.ТекущийПользователь;
Для Тек_Строка = 1 по Колич_Строк Цикл // Заполнение табличной части "Материалы"
Строка_Док = Тек_Документ.Материалы.Добавить();
Строка_Док.Валюта = Тек_Документ.ТипЦен.ВалютаЦены;
Для каждого Тек_ТЗ из ТЗ_Колонки Цикл
Тек_Значение = Excel_App.Cells(Тек_Строка, Тек_ТЗ.НомерКолонки).Text;
// Получение имени колонки
ИмяКолонкиДокумента = Excel_App.Cells(1, Тек_ТЗ.НомерКолонки).Text;
// Заполнение строки данными
Если ИмяКолонкиДокумента = "Материалы" Тогда
Строка_Док.Номенклатура = Справочники.Материалы.НайтиПоНаименованию(Тек_Значение, Истина);
ИначеЕсли ИмяКолонкиДокумента = "Цена" Тогда
Строка_Док.Цена = Тек_Значение;
ИначеЕсли ИмяКолонкиДокумента = "Ставка_НДС" Тогда
Строка_Док.Ставка_НДС = Тек_Значение;
КонецЕсли;
КонецЦикла;
КонецЦикла;
Тек_Документ.Записать(РежимЗаписиДокумента.Проведение); // Запись и проведение документа
Сообщить("Записан документ " + СокрЛП(Тек_Документ));
Иначе
Сообщить("В файле "+СокрЛП(Объект.Файл)+" не достаточно данных для заполнения документа!");
КонецЕсли;
Excel_App.DisplayAlerts = 0;
Excel_App.Quit();
Excel_App.DisplayAlerts = 1;
КонецПроцедуры
&НаСервере
Функция ПроверкаРеквизитаТЧ(ИмяРекв, МетаданныеДок, ИмяТЧ)
// Проверка наличия ТЧ
ТаблЧасть = МетаданныеДок.ТабличныеЧасти.Найти(ИмяТЧ);
Если ТаблЧасть = Неопределено Тогда // Нет такой таб. части в документе
Возврат Ложь; //реквизит не найден
Иначе
Возврат НЕ (ТаблЧасть.Реквизиты.Найти(ИмяРекв) = Неопределено);//реквизит найден
КонецЕсли;
КонецФункции
Процедура ЗагрузитьИзXLS()// в документ Поступление материалов
// Попытка открытия файла Excel
Попытка
Excel_App = Новый COMОбъект("Excel.Application");
Excel_App.WorkBooks.Open(Объект.Файл);
Исключение
Сообщить("Произошла ошибка при открытии файла "+СокрЛП(ОписаниеОшибки())+"! Операция прервана!");
Возврат;
КонецПопытки;
// Попытка выбрать первый лист (можно указать другой)
Попытка
Excel_App.Sheets(1).Select();
Исключение
// Если первый лист не найден - закрываем файл
Excel_App.ActiveWorkbook.Close();
Excel_App = 0;
Сообщить("Первый лист не найден!");
ОтменитьТранзакцию();
Возврат;
КонецПопытки;
// Вычисление количества строк и колонок в зависимости от версии Excel
version_Ex = Лев(Excel_App.Version, Найти(Excel_App.Version,".")-1);
Если version_Ex = "8" тогда
Колич_Строк = Excel_App.Cells.CurrentRegion.Rows.Count;
Колич_Колонок = Макс(Excel_App.Cells.CurrentRegion.Columns.Count, 13);
Иначе
Колич_Строк = Excel_App.Cells(1,1).SpecialCells(11).Row;
Колич_Колонок = Excel_App.Cells(1,1).SpecialCells(11).Column;
Конецесли;
// Имена колонок в файле должны совпадать с именами реквизитов табличной части, в которую загружаются данные
// Переменная ТЗ_Колонки содержит список номеров колонок, которые будут перегружаться
ТЗ_Колонки = Новый ТаблицаЗначений;
ТЗ_Колонки.Колонки.Добавить("НомерКолонки");
ТЗ_Колонки.Колонки.Добавить("НазваниеКолонки");
Для Каждая_Колонка = 1 по Колич_Колонок Цикл
ИмяКолонки = Excel_App.Cells(1, Каждая_Колонка).Text;
ИмяБезПробелов = СтрЗаменить(ИмяКолонки," ",""); // Удаление лишних пробелов из имен колонок
// Проверка наличия реквизитов табличной части "Материалы" в документе "ПоступлениеМатериалов"
Если НЕ ПроверкаРеквизитаТЧ(ИмяБезПробелов, Метаданные.Документы.ПоступлениеМатериалов, "Материалы") Тогда
Сообщить("Не найден реквизит с именем " + ИмяБезПробелов + "! Колонка не будет загружена!");
Иначе
Новая_Строка = ТЗ_Колонки.Добавить();
Новая_Строка.НомерКолонки = Каждая_Колонка;
Новая_Строка.НазваниеКолонки = ИмяБезПробелов;
КонецЕсли;
КонецЦикла;
// Если есть колонки для загрузки и есть колонка "Материалы" (обязательная к заполнению)
Если ТЗ_Колонки.Количество() <> 0 и
ТЗ_Колонки.НайтиСтроки(Новый Структура("НазваниеКолонки", "Материалы")).Количество() <> 0 Тогда
// Создание документа и заполнение реквизитов шапки
Тек_Документ = Документы.ПоступлениеМатериалов.СоздатьДокумент();
Тек_Документ.Комментарий = "Загружено из файла " + Объект.Файл;
Тек_Документ.Дата = ТекущаяДата();
Тек_Документ.Ответственный = ПараметрыСеанса.ТекущийПользователь;
Для Тек_Строка = 1 по Колич_Строк Цикл // Заполнение табличной части "Материалы"
Строка_Док = Тек_Документ.Материалы.Добавить();
Строка_Док.Валюта = Тек_Документ.ТипЦен.ВалютаЦены;
Для каждого Тек_ТЗ из ТЗ_Колонки Цикл
Тек_Значение = Excel_App.Cells(Тек_Строка, Тек_ТЗ.НомерКолонки).Text;
// Получение имени колонки
ИмяКолонкиДокумента = Excel_App.Cells(1, Тек_ТЗ.НомерКолонки).Text;
// Заполнение строки данными
Если ИмяКолонкиДокумента = "Материалы" Тогда
Строка_Док.Номенклатура = Справочники.Материалы.НайтиПоНаименованию(Тек_Значение, Истина);
ИначеЕсли ИмяКолонкиДокумента = "Цена" Тогда
Строка_Док.Цена = Тек_Значение;
ИначеЕсли ИмяКолонкиДокумента = "Ставка_НДС" Тогда
Строка_Док.Ставка_НДС = Тек_Значение;
КонецЕсли;
КонецЦикла;
КонецЦикла;
Тек_Документ.Записать(РежимЗаписиДокумента.Проведение); // Запись и проведение документа
Сообщить("Записан документ " + СокрЛП(Тек_Документ));
Иначе
Сообщить("В файле "+СокрЛП(Объект.Файл)+" не достаточно данных для заполнения документа!");
КонецЕсли;
Excel_App.DisplayAlerts = 0;
Excel_App.Quit();
Excel_App.DisplayAlerts = 1;
КонецПроцедуры
&НаСервере
Функция ПроверкаРеквизитаТЧ(ИмяРекв, МетаданныеДок, ИмяТЧ)
// Проверка наличия ТЧ
ТаблЧасть = МетаданныеДок.ТабличныеЧасти.Найти(ИмяТЧ);
Если ТаблЧасть = Неопределено Тогда // Нет такой таб. части в документе
Возврат Ложь; //реквизит не найден
Иначе
Возврат НЕ (ТаблЧасть.Реквизиты.Найти(ИмяРекв) = Неопределено);//реквизит найден
КонецЕсли;
КонецФункции
Выгрузка в Excel файл (через COMОбъект) в 1С 8.3:
&НаСервере
Процедура ВыгрузитьВXLS(Выб_Таблица)
Если Выб_Таблица.Количество() = 0 Тогда
Сообщить("Пустая таблица! Операция прервана!");
Возврат;
КонецЕсли;
Попытка
Excel_App = Новый COMОбъект("Excel.Application"); // Подключение к Excel
Исключение
Сообщить("Произошла ошибка при открытии файла "+СокрЛП(ОписаниеОшибки())+"! Операция прервана!");
Возврат;
КонецПопытки;
Попытка
Book_Excel = Excel_App.WorkBooks.Add();
Sheet_Excel = Book_Excel.WorkSheets(1);
Sheet_Excel.Name = "Test Sheet"; // Присваиваем имя первому листу
Excel_App.ActiveWindow.View = 2; // Режим страничного просмотра
Excel_App.ActiveWindow.Zoom = 100; // Масштаб
Sheet_Excel.PageSetup.Orientation = 2; // Альбомная ориентация
Sheet_Excel.Columns(1).ColumnWidth = 20; // Ширина первой колонки
Sheet_Excel.Columns(2).ColumnWidth = 40; // Ширина второй колонки
Sheet_Excel.Columns(10).ColumnWidth = 15;
Sheet_Excel.Columns(11).ColumnWidth = 15;
Sheet_Excel.Cells(1, 1).Value = "№ ПП"; //Создаем шапку 1
Sheet_Excel.Cells(1, 2).Value = "Текст";
Sheet_Excel.Cells(1, 3).Value = "Числа";
Sheet_Excel.Cells(1, 10).Value = "Дата";
Sheet_Excel.Cells(1, 11).Value = "Формула";
Sheet_Excel.Cells(2, 3).Value = "Число 1";//Создаем шапку 2
Sheet_Excel.Cells(2, 4).Value = "Число 2";
Sheet_Excel.Cells(2, 5).Value = "Число 3";
Sheet_Excel.Cells(2, 6).Value = "Число 4";
Sheet_Excel.Cells(2, 7).Value = "Число 5";
Sheet_Excel.Cells(2, 8).Value = "Число 6";
Sheet_Excel.Cells(2, 9).Value = "Число 7";
Sheet_Excel.Range("A1:A2").MergeCells = Истина; Sheet_Excel.Range("A1:A2").WrapText = Истина;
Sheet_Excel.Range("B1:B2").MergeCells = Истина; Sheet_Excel.Range("B1:B2").WrapText = Истина;
Sheet_Excel.Range("J1:J2").MergeCells = Истина; Sheet_Excel.Range("J1:J2").WrapText = Истина;
Sheet_Excel.Range("K1:K2").MergeCells = Истина; Sheet_Excel.Range("K1:K2").WrapText = Истина;
Sheet_Excel.Range("C1:I1").MergeCells = Истина; Sheet_Excel.Range("C1:I1").WrapText = Истина;
Sheet_Excel.Range("A1:K2").Borders.Linestyle = 1; //Линия границы
Sheet_Excel.Range("A1:K2").HorizontalAlignment = 3; //Выравнивание по горизонтали
Sheet_Excel.Range("A1:K2").VerticalAlignment = 2; //Выравнивание по вертикали
Sheet_Excel.Range("A1:K2").Font.Bold = 1; //Установим жирный шрифт в шапке
// Выгружаем данные в таблицу
Количество_Строк = 3; //Заполнение ТЧ начинаем с третьей строки
Для Каждого ТекСтрока Из Выб_Таблица Цикл
Sheet_Excel.Cells(Количество_Строк, 1).Value = ТекСтрока.НомерПоПорядку;
Sheet_Excel.Cells(Количество_Строк, 2).Value = ТекСтрока.Табл_Текст;
Sheet_Excel.Cells(Количество_Строк, 3).Value = ТекСтрока.Табл_Число1;
Sheet_Excel.Cells(Количество_Строк, 4).Value = ТекСтрока.Табл_Число2;
Sheet_Excel.Cells(Количество_Строк, 5).Value = ТекСтрока.Табл_Число3;
Sheet_Excel.Cells(Количество_Строк, 6).Value = ТекСтрока.Табл_Число4;
Sheet_Excel.Cells(Количество_Строк, 7).Value = ТекСтрока.Табл_Число5;
Sheet_Excel.Cells(Количество_Строк, 8).Value = ТекСтрока.Табл_Число6;
Sheet_Excel.Cells(Количество_Строк, 9).Value = ТекСтрока.Табл_Число7;
Sheet_Excel.Cells(Количество_Строк, 10).Value = ТекСтрока.Табл_Дата;
Sheet_Excel.Cells(Количество_Строк, 11).Formula = "=F" + Строка(Количество_Строк)
+ "+G" + Строка(Количество_Строк);
Количество_Строк = Количество_Строк + 1;
КонецЦикла;
Количество_Строк = Количество_Строк - 1; //Последняя строка для форматирования
Sheet_Excel.Range("A3:K" + Строка(Количество_Строк)).Borders.Linestyle = 1; //Линия границы
Sheet_Excel.Range("A3:K" + Строка(Количество_Строк)).VerticalAlignment = 2; //Выравнивание по вертикали
Для НомерСтроки = 3 По Количество_Строк Цикл // Установка числового формата
Для Столбец = 4 По 9 Цикл
Sheet_Excel.Cells(НомерСтроки, Столбец).NumberFormat = "0.00";
КонецЦикла;
Sheet_Excel.Cells(НомерСтроки, 11).NumberFormat = "0.00";
КонецЦикла;
Исключение
Excel_App.Quit();
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
Попытка
Book_Excel.SaveAs(Объект.Файл);
Сообщить("Файл " + Объект.Файл + " успешно сохранен");
Исключение
Сообщить(ОписаниеОшибки() + " Файл не сохранен!");
Возврат;
КонецПопытки;
Попытка
Excel_App.Quit();
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
КонецПроцедуры
Процедура ВыгрузитьВXLS(Выб_Таблица)
Если Выб_Таблица.Количество() = 0 Тогда
Сообщить("Пустая таблица! Операция прервана!");
Возврат;
КонецЕсли;
Попытка
Excel_App = Новый COMОбъект("Excel.Application"); // Подключение к Excel
Исключение
Сообщить("Произошла ошибка при открытии файла "+СокрЛП(ОписаниеОшибки())+"! Операция прервана!");
Возврат;
КонецПопытки;
Попытка
Book_Excel = Excel_App.WorkBooks.Add();
Sheet_Excel = Book_Excel.WorkSheets(1);
Sheet_Excel.Name = "Test Sheet"; // Присваиваем имя первому листу
Excel_App.ActiveWindow.View = 2; // Режим страничного просмотра
Excel_App.ActiveWindow.Zoom = 100; // Масштаб
Sheet_Excel.PageSetup.Orientation = 2; // Альбомная ориентация
Sheet_Excel.Columns(1).ColumnWidth = 20; // Ширина первой колонки
Sheet_Excel.Columns(2).ColumnWidth = 40; // Ширина второй колонки
Sheet_Excel.Columns(10).ColumnWidth = 15;
Sheet_Excel.Columns(11).ColumnWidth = 15;
Sheet_Excel.Cells(1, 1).Value = "№ ПП"; //Создаем шапку 1
Sheet_Excel.Cells(1, 2).Value = "Текст";
Sheet_Excel.Cells(1, 3).Value = "Числа";
Sheet_Excel.Cells(1, 10).Value = "Дата";
Sheet_Excel.Cells(1, 11).Value = "Формула";
Sheet_Excel.Cells(2, 3).Value = "Число 1";//Создаем шапку 2
Sheet_Excel.Cells(2, 4).Value = "Число 2";
Sheet_Excel.Cells(2, 5).Value = "Число 3";
Sheet_Excel.Cells(2, 6).Value = "Число 4";
Sheet_Excel.Cells(2, 7).Value = "Число 5";
Sheet_Excel.Cells(2, 8).Value = "Число 6";
Sheet_Excel.Cells(2, 9).Value = "Число 7";
Sheet_Excel.Range("A1:A2").MergeCells = Истина; Sheet_Excel.Range("A1:A2").WrapText = Истина;
Sheet_Excel.Range("B1:B2").MergeCells = Истина; Sheet_Excel.Range("B1:B2").WrapText = Истина;
Sheet_Excel.Range("J1:J2").MergeCells = Истина; Sheet_Excel.Range("J1:J2").WrapText = Истина;
Sheet_Excel.Range("K1:K2").MergeCells = Истина; Sheet_Excel.Range("K1:K2").WrapText = Истина;
Sheet_Excel.Range("C1:I1").MergeCells = Истина; Sheet_Excel.Range("C1:I1").WrapText = Истина;
Sheet_Excel.Range("A1:K2").Borders.Linestyle = 1; //Линия границы
Sheet_Excel.Range("A1:K2").HorizontalAlignment = 3; //Выравнивание по горизонтали
Sheet_Excel.Range("A1:K2").VerticalAlignment = 2; //Выравнивание по вертикали
Sheet_Excel.Range("A1:K2").Font.Bold = 1; //Установим жирный шрифт в шапке
// Выгружаем данные в таблицу
Количество_Строк = 3; //Заполнение ТЧ начинаем с третьей строки
Для Каждого ТекСтрока Из Выб_Таблица Цикл
Sheet_Excel.Cells(Количество_Строк, 1).Value = ТекСтрока.НомерПоПорядку;
Sheet_Excel.Cells(Количество_Строк, 2).Value = ТекСтрока.Табл_Текст;
Sheet_Excel.Cells(Количество_Строк, 3).Value = ТекСтрока.Табл_Число1;
Sheet_Excel.Cells(Количество_Строк, 4).Value = ТекСтрока.Табл_Число2;
Sheet_Excel.Cells(Количество_Строк, 5).Value = ТекСтрока.Табл_Число3;
Sheet_Excel.Cells(Количество_Строк, 6).Value = ТекСтрока.Табл_Число4;
Sheet_Excel.Cells(Количество_Строк, 7).Value = ТекСтрока.Табл_Число5;
Sheet_Excel.Cells(Количество_Строк, 8).Value = ТекСтрока.Табл_Число6;
Sheet_Excel.Cells(Количество_Строк, 9).Value = ТекСтрока.Табл_Число7;
Sheet_Excel.Cells(Количество_Строк, 10).Value = ТекСтрока.Табл_Дата;
Sheet_Excel.Cells(Количество_Строк, 11).Formula = "=F" + Строка(Количество_Строк)
+ "+G" + Строка(Количество_Строк);
Количество_Строк = Количество_Строк + 1;
КонецЦикла;
Количество_Строк = Количество_Строк - 1; //Последняя строка для форматирования
Sheet_Excel.Range("A3:K" + Строка(Количество_Строк)).Borders.Linestyle = 1; //Линия границы
Sheet_Excel.Range("A3:K" + Строка(Количество_Строк)).VerticalAlignment = 2; //Выравнивание по вертикали
Для НомерСтроки = 3 По Количество_Строк Цикл // Установка числового формата
Для Столбец = 4 По 9 Цикл
Sheet_Excel.Cells(НомерСтроки, Столбец).NumberFormat = "0.00";
КонецЦикла;
Sheet_Excel.Cells(НомерСтроки, 11).NumberFormat = "0.00";
КонецЦикла;
Исключение
Excel_App.Quit();
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
Попытка
Book_Excel.SaveAs(Объект.Файл);
Сообщить("Файл " + Объект.Файл + " успешно сохранен");
Исключение
Сообщить(ОписаниеОшибки() + " Файл не сохранен!");
Возврат;
КонецПопытки;
Попытка
Excel_App.Quit();
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
КонецПроцедуры
Справочно: Основные свойства и методы для работы с Excel через OLE в 1С 8.3:
Действия с приложением:
- Установка видимости окна приложения ...... Excel_App.Visible = Ложь;
- Установка режима вывода предупреждений (выводить/не выводить) ...... Excel_App.DisplayAlerts = Ложь;
- Закрытие приложения ...... Excel_App.Quit();
Действия с книгой:
- Создание новой книги ...... Book_Excel = Excel_App.WorkBooks.Add();
- Открытие существующей книги ...... Book_Excel = Excel_App.WorkBooks.Open(ИмяФайла);
- Сохранение книги ...... Book_Excel.SaveAs(ИмяФайла);
- Закрытие книги ...... Book_Excel .Close(0);
Действия с листом:
- Установка текущего листа ...... Sheet_Excel = Book_Excel.WorkSheets(НомерSheet_Excelа);
- Установка имени ...... Sheet_Excel.Name = Имя;
- Установка защиты ...... Sheet_Excel.Protect();
- Снятие защиты ...... Sheet_Excel.UnProtect();
- Установка ориентации страницы ...... Sheet_Excel.PageSetup.Orientation = 2; 1 - книжная, 2 - альбомная
- Установка левой границы ...... Sheet_Excel.PageSetup.LeftMargin = Excel_App.CentimetersToPoints(Сантиметры);
- Установка верхней границы ...... Sheet_Excel.PageSetup.TopMargin = Excel_App.CentimetersToPoints(Сантиметры);
- Установка правой границы ...... Sheet_Excel.PageSetup.RightMargin = Excel_App.CentimetersToPoints(Сантиметры);
- Установка нижней границы ...... Sheet_Excel.PageSetup.BottomMargin = Excel_App.CentimetersToPoints(Сантиметры);
Действия со строками, колонками, ячейками:
- Установка ширины колонки ...... Sheet_Excel.Columns(НомерКолонки).ColumnWidth = Ширина;
- Удаление строки ...... Sheet_Excel.Rows(НомерСтроки).Delete();
- Удаление колонки ...... Sheet_Excel.Columns(НомерКолонки).Delete();
- Удаление ячейки ...... Sheet_Excel.Cells(НомерСтроки, НомерКолонки).Delete();
- Установка значения ...... Sheet_Excel.Cells(НомерСтроки, НомерКолонки).Value = Значение;
- Объединение ячеек ...... Sheet_Excel.Range(Sheet_Excel.Cells(НомерСтроки, НомерКолонки),
- Установка шрифта ...... Sheet_Excel.Cells(НомерСтроки, НомерКолонки).Font.Name = ИмяШрифта;
- Установка размера шрифта ...... Sheet_Excel.Cells(НомерСтроки, НомерКолонки).Font.Size = РазмерШрифта;
- Установка жирного шрифта ...... Sheet_Excel.Cells(НомерСтроки, НомерКолонки).Font.Bold = 1; 1 - жирный шрифт, 0 - нормальный
- Установка курсива ...... Sheet_Excel.Cells(НомерСтроки, НомерКолонки).Font.Italic = 1; 1 - курсив, 0 - нормальный
- Установка подчеркнутого шрифта ...... Sheet_Excel.Cells(НомерСтроки, НомерКолонки).Font.Underline = 2; 2 - подчеркнутый, 1 - нет
Загрузка из Excel файла (через ADODB.Connection) в 1С 8.3:
&НаСервере
Процедура ЗагрузитьИзXLS_ADODB()
// Создание COM-объекта для соединения
ADODB_conn = Новый COMОбъект("ADODB.Connection");
// Установка строки соединения
ADODB_conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source="+ИмяФайла+";
|Extended Properties=""Excel 12.0 XML;HDR=YES"";";
ADODB_conn.Open(); // Открытие соединения
// Создание COM-объекта для получения выборки
ADODB_rec = Новый COMОбъект("ADODB.Recordset");
ТекстЗапроса = "SELECT * FROM [ЛистN1$]";
// Выборка (запрос)
ADODB_rec.Open(ТекстЗапроса, ADODB_conn);
// Обход результата выборки
Пока НЕ ADODB_rec.EOF() Цикл
ЗначениеКолонки1 = ADODB_rec.Fields.Item("КолонкаN1").Value; // Обращение по имени колонки
ЗначениеКолонки2 = ADODB_rec.Fields.Item(0).Value; // Обращение по индексу колонки
ADODB_rec.MoveNext();
КонецЦикла;
ADODB_rec.Close();
ADODB_rec = Неопределено;
ADODB_conn.Close();
ADODB_conn = Неопределено;
КонецПроцедуры
Процедура ЗагрузитьИзXLS_ADODB()
// Создание COM-объекта для соединения
ADODB_conn = Новый COMОбъект("ADODB.Connection");
// Установка строки соединения
ADODB_conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source="+ИмяФайла+";
|Extended Properties=""Excel 12.0 XML;HDR=YES"";";
ADODB_conn.Open(); // Открытие соединения
// Создание COM-объекта для получения выборки
ADODB_rec = Новый COMОбъект("ADODB.Recordset");
ТекстЗапроса = "SELECT * FROM [ЛистN1$]";
// Выборка (запрос)
ADODB_rec.Open(ТекстЗапроса, ADODB_conn);
// Обход результата выборки
Пока НЕ ADODB_rec.EOF() Цикл
ЗначениеКолонки1 = ADODB_rec.Fields.Item("КолонкаN1").Value; // Обращение по имени колонки
ЗначениеКолонки2 = ADODB_rec.Fields.Item(0).Value; // Обращение по индексу колонки
ADODB_rec.MoveNext();
КонецЦикла;
ADODB_rec.Close();
ADODB_rec = Неопределено;
ADODB_conn.Close();
ADODB_conn = Неопределено;
КонецПроцедуры
Выгрузка в Excel файла (через ADODB.Connection) в 1С 8.3:
&НаСервере
Процедура ВыгрузитьВXLS_ADODB()
// Создание COM-объекта для соединения
ADODB_conn = Новый COMОбъект("ADODB.Connection");
// Установка строки соединения
ADODB_conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source="+ИмяФайла+";
|Extended Properties=""Excel 12.0 XML;HDR=YES"";";
ADODB_conn.Open(); // Открытие соединения
// Создание COM-объекта для команды
ADODB_com = Новый COMОбъект("ADODB.Command");
ADODB_com.ActiveConnection = ADODB_conn;
// Присвоение текста команды для создания таблицы
ADODB_com.CommandText = "CREATE TABLE [ЛистN1] (КолонкаN1 char(255), КолонкаN2 date, КолонкаN3 int, КолонкаN4 float)";
ADODB_com.Execute(); // Выполнение команды
// Присвоение текста команды для добавления строки таблицы
ADODB_com.CommandText = "INSERT INTO [ЛистN1] (КолонкаN1, КолонкаN2, КолонкаN3, КолонкаN4) values (‘абвгдеё’, ‘8/11/2017’, ‘12345’, ‘12345,6789’)";
ADODB_com.Execute(); // Выполнение команды
// Удаление команды и закрытие соединения
ADODB_com = Неопределено;
ADODB_conn.Close();
ADODB_conn = Неопределено;
КонецПроцедуры
Процедура ВыгрузитьВXLS_ADODB()
// Создание COM-объекта для соединения
ADODB_conn = Новый COMОбъект("ADODB.Connection");
// Установка строки соединения
ADODB_conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source="+ИмяФайла+";
|Extended Properties=""Excel 12.0 XML;HDR=YES"";";
ADODB_conn.Open(); // Открытие соединения
// Создание COM-объекта для команды
ADODB_com = Новый COMОбъект("ADODB.Command");
ADODB_com.ActiveConnection = ADODB_conn;
// Присвоение текста команды для создания таблицы
ADODB_com.CommandText = "CREATE TABLE [ЛистN1] (КолонкаN1 char(255), КолонкаN2 date, КолонкаN3 int, КолонкаN4 float)";
ADODB_com.Execute(); // Выполнение команды
// Присвоение текста команды для добавления строки таблицы
ADODB_com.CommandText = "INSERT INTO [ЛистN1] (КолонкаN1, КолонкаN2, КолонкаN3, КолонкаN4) values (‘абвгдеё’, ‘8/11/2017’, ‘12345’, ‘12345,6789’)";
ADODB_com.Execute(); // Выполнение команды
// Удаление команды и закрытие соединения
ADODB_com = Неопределено;
ADODB_conn.Close();
ADODB_conn = Неопределено;
КонецПроцедуры
Создание нового листа Excel со структурой (через ADODB.Connection) в 1С 8.3:
&НаСервере
Процедура СозданиеНовогоЛистаВXLS_ADODB()
// Создание COM-объекта для работы с книгой
Book_Excel = Новый COMОбъект("ADOX.Catalog");
Book_Excel.ActiveConnection = Соединение;
// Создание COM-объекта для работы со структурой данных на листе
Excel_App = Новый COMОбъект("ADOX.Table");
Excel_App.Name = "ЛистN1";
Excel_App.Columns.Append("КолонкаN1", 202);
Excel_App.Columns.Append("КолонкаN2", 7);
Excel_App.Columns.Append("КолонкаN3", 5);
// Тип колонки (необязательный параметр):
// 5 - adDouble;
// 6 - adCurrency;
// 7 - adDate;
// 11 - adBoolean;
// 202 - adVarWChar;
// 203 - adLongVarWChar.
// Создание в книге листа с описанной структурой
Book_Excel.Tables.Append(Excel_App);
Excel_App = Неопределено;
Book_Excel = Неопределено;
КонецПроцедуры
Процедура СозданиеНовогоЛистаВXLS_ADODB()
// Создание COM-объекта для работы с книгой
Book_Excel = Новый COMОбъект("ADOX.Catalog");
Book_Excel.ActiveConnection = Соединение;
// Создание COM-объекта для работы со структурой данных на листе
Excel_App = Новый COMОбъект("ADOX.Table");
Excel_App.Name = "ЛистN1";
Excel_App.Columns.Append("КолонкаN1", 202);
Excel_App.Columns.Append("КолонкаN2", 7);
Excel_App.Columns.Append("КолонкаN3", 5);
// Тип колонки (необязательный параметр):
// 5 - adDouble;
// 6 - adCurrency;
// 7 - adDate;
// 11 - adBoolean;
// 202 - adVarWChar;
// 203 - adLongVarWChar.
// Создание в книге листа с описанной структурой
Book_Excel.Tables.Append(Excel_App);
Excel_App = Неопределено;
Book_Excel = Неопределено;
КонецПроцедуры
Справочно: Объекты ADO для работы с Excel в 1С 8.3:
- Connection
- Command
- Errors
- Fields
- Parameters
- Properties
- Recordset
- Record
- Stream
В строке соединения параметр HDR определяет как будет восприниматься первая строка на листе.
NO - первая строка воспринимается как данные. К значениям можно обращаться только по индексу колонки.
YES - первая строка воспринимается как названия колонок. К значениям можно обращаться по имени и по индексу колонки.
Выгрузка в Excel файл (через COMSafeArray) в 1С 8.3:
&НаСервере
Процедура ВыгрузитьВXLS_COMSafeArray(Выб_Таблица)
Если Выб_Таблица.Количество() = 0 Тогда
Сообщить("Пустая таблица! Операция прервана!");
Возврат;
КонецЕсли;
Если НЕ ЗначениеЗаполнено(Объект.Файл) Тогда
Сообщить("Выберите файл! Операция прервана!");
Возврат;
КонецЕсли;
Попытка
Excel_App = Новый COMОбъект("Excel.Application");
Excel_App.Interactive = Ложь;
Excel_App.DisplayAlerts = Ложь;
Исключение
Сообщить("Не удалось подключиться к Excel, возможно программа на компьютере не установлена! Операция прервана!");
Возврат;
КонецПопытки;
КолонкиТаблицы = Выб_Таблица.Колонки;
СтартМассив = Новый Массив;
Для каждого Колонка Из КолонкиТаблицы Цикл
СтартМассив.Добавить(Выб_Таблица.ВыгрузитьКолонку(Колонка.Имя));
КонецЦикла;
РазмерМассив = Новый Массив;
РазмерМассив.Добавить(КолонкиТаблицы.Количество());
РазмерМассив.Добавить(Выб_Таблица.Количество());
МассивArray = Новый COMSafeArray(СтартМассив, "VT_VARIANT", РазмерМассив);
НачальнаяСтрока = 2;
Book_Excel = Excel_App.Workbooks.Add();
Sheet_Excel = Book_Excel.Sheets(1);
Кол_Строк = Выб_Таблица.Количество()+1;
Кол_Колонок = КолонкиТаблицы.Количество();
Sheet_Excel.Range(Sheet_Excel.Cells(2,1), Sheet_Excel.Cells(Кол_Строк, Кол_Колонок)).Value = МассивArray;
a=0;
Для каждого Колонка Из КолонкиТаблицы Цикл
a=a+1;
Sheet_Excel.Cells(1,a).Value = Колонка.Имя;
Sheet_Excel.Columns(a).EntireColumn.AutoFit();
КонецЦикла;
Попытка
Book_Excel.SaveAs(Объект.Файл);
Исключение
Book_Excel.Close();
Book_Excel = Неопределено;
Сообщить("Ошибка при записи файла! Операция прервана!");
Возврат;
КонецПопытки;
Excel_App.Interactive = Истина;
Excel_App.DisplayAlerts = Истина;
Excel_App.Application.Quit();
Excel_App = Неопределено;
КонецПроцедуры
Процедура ВыгрузитьВXLS_COMSafeArray(Выб_Таблица)
Если Выб_Таблица.Количество() = 0 Тогда
Сообщить("Пустая таблица! Операция прервана!");
Возврат;
КонецЕсли;
Если НЕ ЗначениеЗаполнено(Объект.Файл) Тогда
Сообщить("Выберите файл! Операция прервана!");
Возврат;
КонецЕсли;
Попытка
Excel_App = Новый COMОбъект("Excel.Application");
Excel_App.Interactive = Ложь;
Excel_App.DisplayAlerts = Ложь;
Исключение
Сообщить("Не удалось подключиться к Excel, возможно программа на компьютере не установлена! Операция прервана!");
Возврат;
КонецПопытки;
КолонкиТаблицы = Выб_Таблица.Колонки;
СтартМассив = Новый Массив;
Для каждого Колонка Из КолонкиТаблицы Цикл
СтартМассив.Добавить(Выб_Таблица.ВыгрузитьКолонку(Колонка.Имя));
КонецЦикла;
РазмерМассив = Новый Массив;
РазмерМассив.Добавить(КолонкиТаблицы.Количество());
РазмерМассив.Добавить(Выб_Таблица.Количество());
МассивArray = Новый COMSafeArray(СтартМассив, "VT_VARIANT", РазмерМассив);
НачальнаяСтрока = 2;
Book_Excel = Excel_App.Workbooks.Add();
Sheet_Excel = Book_Excel.Sheets(1);
Кол_Строк = Выб_Таблица.Количество()+1;
Кол_Колонок = КолонкиТаблицы.Количество();
Sheet_Excel.Range(Sheet_Excel.Cells(2,1), Sheet_Excel.Cells(Кол_Строк, Кол_Колонок)).Value = МассивArray;
a=0;
Для каждого Колонка Из КолонкиТаблицы Цикл
a=a+1;
Sheet_Excel.Cells(1,a).Value = Колонка.Имя;
Sheet_Excel.Columns(a).EntireColumn.AutoFit();
КонецЦикла;
Попытка
Book_Excel.SaveAs(Объект.Файл);
Исключение
Book_Excel.Close();
Book_Excel = Неопределено;
Сообщить("Ошибка при записи файла! Операция прервана!");
Возврат;
КонецПопытки;
Excel_App.Interactive = Истина;
Excel_App.DisplayAlerts = Истина;
Excel_App.Application.Quit();
Excel_App = Неопределено;
КонецПроцедуры
Загрузка из Excel файла (напрямую через табличный документ) в 1С 8.3:
&НаСервере
Процедура ЗагрузитьИзXLSНапрямую()// Начиная с версии 1С 8.3.8 (только &НаСервере)
ТабличныйДокумент.Прочитать(Объект.Файл, СпособЧтенияЗначенийТабличногоДокумента.Значение); //Способ чтения: Значение, Текст
КонецПроцедуры
Процедура ЗагрузитьИзXLSНапрямую()// Начиная с версии 1С 8.3.8 (только &НаСервере)
ТабличныйДокумент.Прочитать(Объект.Файл, СпособЧтенияЗначенийТабличногоДокумента.Значение); //Способ чтения: Значение, Текст
КонецПроцедуры
Выгрузка в Excel файл (напрямую через табличный документ) в 1С 8.3:
&НаСервере
Процедура ВыгрузитьВXLSНапрямую()// Начиная с версии 1С 8.3.8 (можно и &НаКлиенте и на &НаСервере)
ТабличныйДокумент.Записать(Объект.Файл, ТипФайлаТабличногоДокумента.XLSX); //Тип файла: XLS95, XLS97, XLSX
КонецПроцедуры
Процедура ВыгрузитьВXLSНапрямую()// Начиная с версии 1С 8.3.8 (можно и &НаКлиенте и на &НаСервере)
ТабличныйДокумент.Записать(Объект.Файл, ТипФайлаТабличногоДокумента.XLSX); //Тип файла: XLS95, XLS97, XLSX
КонецПроцедуры
💡 Готовые модели кода (шаблоны) с "Excel" 1С:
1
комментарий
1сник
29 янв 2024
Здравствуйте!
Большие файлы как раз не получится через ТабДок читать, там 100 мб превращаются в 3 гб озу
Большие файлы как раз не получится через ТабДок читать, там 100 мб превращаются в 3 гб озу
____________________
Перепечатка текста и фотографий разрешена при наличии прямой ссылки на источник