Работа с файлами Excel в 1С 8.3
MS Excel давно стал стандартом для работы с электронными таблицами. Поэтому весьма частая задача, с которой сталкивается 1С разработчик – это чтение файлов Excel и загрузка их в 1С. В статье рассматриваются различные способы выгрузки и загрузки из 1С в файлы Excel.
Существует несколько способов программной работы с файлами Excel из 1С. Каждый из них имеет свои преимущества и недостатки:
- Обмен через OLE
- Обмен через табличный документ
- Использование COMSafeArray
- Обмен через ADO
- Выгрузка без программирования
Обмен через OLE
Наверно самый распространённый вариант – обмен через технологию OLE automation. Он позволяет использовать весь функционал, предоставляемый Excel, но отличается медленной скоростью работы по сравнению с другими способами.
Обмен через OLE требует наличие установленного MS Excel на компьютере, на котором будет производиться обмен данными:
- На компьютере конечного пользователя, если обмен происходит на стороне клиента;
- На компьютере сервера 1С:Предприятие, если обмен происходит на стороне сервера.
Чтение из Excel через OLE:
// —— ВАРИАНТ 1 ——
// Создание COM-объекта
Эксель = Новый COMОбъект(«Excel.Application»);
// Открытие книги
Книга = Эксель.Workbooks.Open(ПутьКФайлу);
// Позиционирование на нужном листе
Лист = Книга.Worksheets(1);
// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист.Cells(НомерСтроки, НомерКолонки).Value;
// Закрытие книги
Книга.Close(0);
// Закрытие Эксель и освобождение памяти
Эксель.Quit();
Эксель = 0;
// —— ВАРИАНТ 2 ——
// Открытие книги
Книга = ПолучитьCOMОбъект(ПутьКФайлу);
// Позиционирование на нужном листе
Лист = Книга.Worksheets(1);
// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист.Cells(НомерСтроки, НомерКолонки).Value;
// Закрытие книги
Книга.Application.Quit();
Для обхода всех заполненных строк листа Excel можно использовать следующие приемы:
// —— ВАРИАНТ 1 ——
КоличествоСтрок = Лист.Cells(1, 1).SpecialCells(11).Row;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
ЗначениеЯчейки = Лист.Cells(НомерСтроки, НомерКолонки).Value;
КонецЦикла;// —— ВАРИАНТ 2 ——
НомерСтроки = 0;
Пока Истина Цикл
НомерСтроки = НомерСтроки + 1;
ЗначениеЯчейки = Лист.Cells(НомерСтроки, НомерКолонки).Value;
Если НЕ ЗначениеЗаполнено(ЗначениеЯчейки) Тогда
Прервать;
КонецЕсли;
КонецЦикла;
Очень часто приходится работать с очень большими файлами Excel, и обработка путем перебора строк занимает огромное количество времени. В таких случаях удобно в одно действие загрузить всю таблицу в массив и потом работать уже непосредственно с массивом:
ВсегоКолонок = Лист.Cells(1, 1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1, 1).SpecialCells(11).Row;
Область = Лист.Range(Лист.Cells(1, 1), Лист.Cells(ВсегоСтрок, ВсегоКолонок));
Данные = Область.Value.Выгрузить();
Выгрузка в Excel через OLE:
// Создание COM-объекта
Эксель = Новый COMОбъект(«Excel.Application»);
// Отключение вывода предупреждений и вопросов
Эксель.DisplayAlerts = Ложь;
// Создание новой книги
Книга = Эксель.WorkBooks.Add();
// Позиционирование на первом листе
Лист = Книга.Worksheets(1);
// Запись значения в ячейку
Лист.Cells(НомерСтроки, НомерКолонки).Value = ЗначениеЯчейки;
// Сохранение файла
Книга.SaveAs(ИмяФайла);
// Закрытие Эксель и освобождение памяти
Эксель.Quit();
Эксель = 0;
Полезные функции при работе с Excel через OLE:
| Устанавливает видимость Excel при работе | //0 - Excel не виден, 1 - виден. |
| Добавление новой книги в файл Excel | Книга = Соединение.WorkBooks.Add(); |
| Сохранение книги Excel | Книга.SaveAs(ИмяФайла); |
| Добавление нового листа к книге | Лист = Книга.WorkSheets.Add(); |
| Переименование листа | Лист.Name = ИмяЛиста; |
| Изменение маштаба листа | //"Масштаб" (от 10 до 400). |
| Изменение ориентации листа | //1 - книжная, 2 - альбомная. |
| Отступы листа | //Левый отступ |
| Обращение к ячейке чтение/запись | //Прочитать значение ячейки |
| Обращение к области ячеек | //В качестве параметров передаем ячейки по диогонали |
|
Часто при чтении или записи значений в Excel, ставятся |
ЗначениеЯчейки = Лист.Cells(1, 3).Value; |
| Объединение ячеек | Лист.Range(Лист.Cells(1, 1), Лист.Cells(10, 1)).Merge(); |
| Работа с именованными ячейками в Excel | Обл = Лист.Range("Имя_Ячейки_В_Excel").Select(); |
| Удаление ячейки | Лист.Cells(1, 3).Delete(); |
| Удаление области ячеек | Лист.Range(Лист.Cells(1, 1), Лист.Cells(10, 1)).Delete(); |
| Обращение к строке | //Сч = Номер строки |
| Изменение ширины колонки | Лист.Columns(НомерКолонки).ColumnWidth = Ширина; |
| Обращение к колонке | //Сч = Номер Колонки |
| Удаление Строки | Лист.Rows(Сч).Delete(); |
| Фон ячейки/области/строки/ | //Фон Ячейки |
| Функция переводит цвет из формата RGB в формат Excel | Функция ПолучитьЦветExcelRGB(R, G, B) |
| Управление шрифтом в ячейке/строке/области | //Изменение шрифта |
| Разрешает переносить по словам в ячейке | //1-Переносить |
| Управление рамкой ячейки | //1 - Тонкая сплошная линия |
| Устанавливаем формат ячейки | //"@" - текстовый |
| Формула в ячейке | Лист.Cells(Сч, Сч2).FormulaLocal = "=ОКРУГЛ(135,46456;0)"; |
| Функция для получения ширины колонки Excel | &НаКлиенте |
| Разрешить перенос слов в ячейке | Лист.Cells(1, 1).WrapText = 1; |
| Группировки данных на листе | //Развернуть все группы (строки и колонки): |
Обмен через табличный документ
Простой способ, суть которого в том, что объект ТабличныйДокумент имеет методы:
Записать(<ИмяФайла>, <ТипФайлаТаблицы>)для выгрузки данных в файл.Прочитать(<ИмяФайла>, <СпособЧтенияЗначений>)для загрузки данных из файла.
Внимание!
Метод Записать() доступен как на клиенте, так и на сервере. Метод Прочитать() доступен только на стороне сервера. Необходимо помнить об этом при планировании клиент-серверного взаимодействия.
Загрузка из файла в табличный документ:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);Здесь ПутьКФайлу – путь к загружаемому файлу Excel. СпособЧтенияЗначенийТабличногоДокумента.Значение определяет, каким образом нужно интерпретировать данные, считываемые из исходного документа. Доступны варианты:
- Значение.
- Текст.
Выгрузка в файл также очень проста и осуществляется всего лишь одной строкой:
ТабДок.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX);Здесь ТабДок – сформированный табличный документ, ПутьКФайлу – имя файла для выгрузки, ТипФайлаТабличногоДокумента.XLSX – формат создаваемого файла. Поддерживаются следующие форматы Excel:
- XLS95 — формат Excel 95.
- XLS97 — формат Excel 97.
- XLSX — формат Excel 2007.
Использование COMSafeArray
При выгрузке больших объемов данных из 1С в Excel для ускорения можно использовать объект COMSafeArray. COMSafeArray – объектная оболочка над многомерным массивом SafeArray из COM, позволяет создавать и использовать SafeArray для обмена данными между COM-объектами. Проще говоря, это массив значений, который можно использовать для обмена между приложениями по технологии OLE.
// Создание COMSafeArray
МассивКом = Новый COMSafeArray(«VT_Variant», ВсегоКолонок, ВсегоСтрок);
// Заполнение COMSafeArray
Для Стр = 0 По ВсегоСтрок — 1 Цикл
Для Кол = 0 По ВсегоКолонок — 1 Цикл
МассивКом.SetValue(Кол, Стр, Значение);
КонецЦикла;
КонецЦикла;
// Присвоение области листа Excel значений из COMSafeArray
Лист.Range(Лист.Cells(1, 1), Лист.Cells(ВсегоСтрок, ВсегоКолонок)).Value = МассивКом;
Обмен через ADO
Данный метод позволяет работать с Excel через ODBC и имеет ряд преимуществ:
- Не требует установки самой Excel, необходима лишь установить ODBC. Но, как правило, он уже установлен. Эта особенность позволяет работать на стороне сервера без дополнительных установок Excel.
- Позволяет работать с таблицей Excel как с базой данных и строить к ней запросы на T-SQL. Таким образом мы можем делать отборы еще на этапе чтения данных и другие преимущества что дает Т-SQL.
Обмен через ADO заметно быстрее обмена через OLE, но при выгрузке нет возможности использовать функционал Excel для оформления ячеек, разметки страниц, задания формул и т.д.
Выгрузка через ADO:
// Создание COM-объекта для соединения
Соединение = Новый COMОбъект(«ADODB.Connection»);
// Установка строки соединения
Соединение.ConnectionString = «
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=» + ИмяФайла + «;
|Extended Properties=»»Excel 12.0 XML;HDR=YES»»;»;
Соединение.Open(); // Открытие соединения
// Создание COM-объекта для команды
Команда = Новый COMОбъект(«ADODB.Command»);
Команда.ActiveConnection = Соединение;
// Присвоение текста команды для создания таблицы
Команда.CommandText = «CREATE TABLE [Лист1] (Колонка1 char(255), Колонка2 date, Колонка3 int, Колонка4 float)»;
Команда.Execute(); // Выполнение команды
// Присвоение текста команды для добавления строки таблицы
Команда.CommandText = «INSERT INTO [Лист1] (Колонка1, Колонка2, Колонка3, Колонка4) values (‘абвгдеё’, ‘8/11/2017’, ‘12345’, ‘12345,6789’)»;
Команда.Execute(); // Выполнение команды
// Удаление команды и закрытие соединения
Команда = Неопределено;
Соединение.Close();
Соединение = Неопределено;Для создания нового листа и формирования его структуры можно воспользоваться объектами ADOX.Catalog и ADOX.Table:
// Создание COM-объекта для работы с книгой
Книга = Новый COMОбъект(«ADOX.Catalog»);
Книга.ActiveConnection = Соединение;
// Создание COM-объекта для работы со структурой данных на листе
Таблица = Новый COMОбъект(«ADOX.Table»);
Таблица.Name = «Лист1»;
Таблица.Columns.Append(«Колонка1», 202);
Таблица.Columns.Append(«Колонка2», 7);
Таблица.Columns.Append(«Колонка3», 5);
Таблица.Columns.Append(«Колонка4», 5);
// Создание в книге листа с описанной структурой
Книга.Tables.Append(Таблица);
Таблица = Неопределено;
Книга = Неопределено;
В приведенном примере в методе
Таблица.Columns.Append(«Колонка1», 202);во втором параметре указывается тип колонки. Это не обязательный параметр, но вот некоторые значения типа колонки:
- 5 — adDouble;
- 6 — adCurrency;
- 7 — adDate;
- 11 — adBoolean;
- 202 — adVarWChar;
- 203 — adLongVarWChar.
Чтение через ADO:
// Создание COM-объекта для соединения
Соединение = Новый COMОбъект(«ADODB.Connection»);
// Установка строки соединения
Соединение.ConnectionString = «
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=» + ИмяФайла + «;
|Extended Properties=»»Excel 12.0 XML;HDR=YES»»;»;
Соединение.Open(); // Открытие соединения
// Создание COM-объекта для получения выборки
Выборка = Новый COMОбъект(«ADODB.Recordset»);
ТекстЗапроса = «SELECT * FROM [Лист1$]»;
// Выполнение запроса
Выборка.Open(ТекстЗапроса, Соединение);
// Обход результата выборки
Пока НЕ Выборка.EOF() Цикл
ЗначениеКолонки1 = Выборка.Fields.Item(«Колонка1»).Value; // Обращение по имени колонки
ЗначениеКолонки2 = Выборка.Fields.Item(0).Value; // Обращение по индексу колонки
Выборка.MoveNext();
КонецЦикла;
Выборка.Close();
Выборка = Неопределено;
Соединение.Close();
Соединение = Неопределено;В строке соединения параметр HDR определяет как будет восприниматься первая строка на листе:
- YES – первая строка воспринимается как названия колонок. К значениям можно обращаться по имени и по индексу колонки.
- NO – первая строка воспринимается как данные. К значениям можно обращаться только по индексу колонки.
В приведенных примерах рассмотрено лишь несколько объектов ADO. Объектная модель ADO состоит из следующих объектов:
- Connection;
- Command;
- Recordset;
- Record;
- Fields;
- Stream;
- Errors;
- Parameters;
- Properties.
Выгрузка без программирования
Если в режиме 1С Предприятия пользователь может отобразить требуемые для выгрузки данные, то, их возможно сохранить в Excel без программирования.
Для сохранения табличного документа (например, результата отчета) можно вызвать команду Сохранить или Сохранить как в главном меню.
Для сохранения данных динамических списков (например, списка номенклатуры) необходимо:
- Вывести данные в табличный документ при помощи команды Еще ⇒ Вывести список.
- Сохранить табличный документ в требуемый формат.
