Сегодня практически все компании и предприятия, торгующие оптом и в розницу, занимающиеся предоставлением услуг и т. д., используют в своей работе программные продукты для учета всех хозяйственных операций, финансово-экономической деятельности и бухгалтерского учета. Чаще всего для этих целей применяется ставший уже «классикой» набор программ от фирмы 1С.
Иногда случается так, что нужно загрузить или выгрузить отчетность и другие документы из табличного процессора MS Excel в 1С и обратно. В этом материале будет подробно рассказано: что собой представляет выгрузка в Excel в 1с и как в 1с выгрузить в Excel.
Особенности выгрузки и загрузки данных из 1С в Excel
Сравнительно недавно в программе «1С: Бухгалтерия» появилась возможность манипулировать данными 1С, загрузка из Excel которых ранее производилась лишь внешними способами. Это позволило производить загрузку и выгрузку документов без изменений конфигурации и использования сторонних обработок. Все процедуры по обороту файлов стали настолько просты, что опытные специалисты сразу же полюбили этот функционал, а новые освоили его очень быстро.
Сама по себе выгрузка в MS Excel может потребоваться в некоторых ситуациях, например, при копировании готовой таблицы в абсолютно пустую базу данных. Также это применяется при необходимости работать с базами без использования средств 1С. С выходом новой «1С: Бухгалтерия 8.3 ред. 3.0» отпала нужда применять сторонние средства для выгрузки, так как платформа получила все необходимые для этих целей механизмы. Несмотря на это, старые способы остались, и о них будет рассказано далее.
Особенность загрузок и выгрузок из 1С состоит в том, что программа работает со следующими типами файлов сохранений для документов и счетов:
- .mxl — специальный файл сохранений, разработанный для 1С программ. Используется ими для сохранения таблиц. Его можно сразу подгружать в другую базу данных 1С на другом компьютере;
- .xls — файл таблиц данных MS Excel, ставший стандартным для этой программы и других табличных редакторов;
- .pdf — файл-документ, который разработан компанией Adobe для своего редактора Adobe Reader;
- .html — документ в виде веб-страницы, которая может быть просмотрена в любом браузере или размещена на сайте;
- .txt — стандартный для многих систем текстовый файл.
У каждого из этих форматов есть свои особенности, влияющие на сохранение и отображение сведений и данных из регистров. Наиболее близким к стандартному файлу сохранений .mxl от 1С становится файл Excel .xls, так как только при выгрузке в этих двух форматах возможно отображение в таблице исходной иерархии, наличие возможности сворачивать и разворачивать группы. Остальные форматы предоставляют информацию простыми списками.
Важно! Microsoft Excel выбран не случайно. Когда-то он заменил собой бумажные списки и таблицы, ручное ведение бухгалтерии в офисах. Позже он отошел на второй план в этой области после появления 1С. Несмотря на это, Excel был и остается одним из наиболее популярных табличных процессоров с огромным функционалом и собственным языком создания макросов VBA от Microsoft. Программа и ныне часто используется для импортирования и экспортирования данных из различных систем учета.
Наиболее простым способом выгрузить все таблицы из программ 1С в формат «Экселя» является простое сохранение в .xls. Для начала нужно открыть нужную базу в Бухгалтерии, а затем выбрать «Операции» — «Справочники» — «Номенклатура», но иногда нужно сохранить отдельную часть документа. При большом объеме таблиц каждая группа выгружается в отдельный файл для повышения удобства работы.
Дальнейшие действия таковы:
- Нажать ПКМ на любой строчке и выбрать «Вывести список», чтобы открыть меню установок;
- Пометить галочками поля, которые нужно выгрузить в Excel;
- В строке «Выводить в…» выбрать текстовый или табличный документ и нажать Энтер;
- Будет открыта таблица, на которую нужно нажать в произвольном месте ЛКМ, чтобы выделить ее полностью;
- Перейти в Файл — Сохранить как;
- Указать нужный формат и дать название будущему документу.
Загрузка же таблиц из Excel в Бухгалтерию производится таким же способом, но есть один нюанс: она должна производиться только профессионалом, который сможет исправить все ошибки, которые возникают при импорте больших объемов данных.
Важно! Перед началом любой загрузки и выгрузки следует во избежание недоразумений и ошибок совершать резервное и архивное копирование базы данных и документов. Может произойти так, что ошибка не позволит вернуть целостность файлу.
Теперь понятно, что такое работа с Excel, как в 1С сохранить данные в Excel, как произвести в 1С чтение и импорт из Excel. Осталось лишь ознакомиться с доступными для этих действий способами.
Основные способы выгрузки и загрузки данных из 1С в Excel
К основным способам выгрузки и загрузки табличных документов между 1С и Excel относят обмены через обычные документы, обмены через OLE, использование COMSafeArray и обмен через ADO. Все они требуют навыков программирования и делают всю работу в автоматическом режиме, но также есть способ и без программирования.
Через Табличный документ
Простой способ, заключающийся в том, что объект ТабличныйДокумент обладает следующими методами:
- Записать(<ИмяФайла>, <ТипФайлаТаблицы>) для экспорта данных;
- Прочитать(<ИмяФайла>, <СпособЧтенияЗначений>) для импорта данных.
Важно! Если метод записи доступен как на клиенте, так и на сервере, то метод прочитать работает только со стороны сервера. Это необходимо считывать при проработке взаимодействия клиента и сервера.
Для сохранения табличного документа в файл нужно любым способом создать ТабличныйДокумент. Выгрузка в файл при этом будет производиться командой: ТабДок.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX);
Далее пояснения: ТаблДок — это созданный документ, а ПутьКФайлу — адрес или имя файла для выгрузки. Тип.. — является типом создаваемого файла, который в данному случае имеет расширение .XLSX. 1C поддерживает следующие типы Excel-файлов: .xls95, .xls97, .xlsx.
Загрузка данных также производится очень просто. Для этого создается новый документ, в который записывается содержание существующего:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
ПутьКФайлу в данном случае указывает имя и путь к загружаемому файлу Excel. Параметр «СпособЧтения…» определяет, как именно нужно интерпретировать полученную информацию. Для этого доступно два значения: Значение и Текст.
Через технологию OLE
Этот вариант, пожалуй, один из самых распространенных при работе с файлами Excel. Он использует весь доступный функционал табличного процессора, но достаточно медленный, если сравнивать его с другими способами. Чтобы обмениваться через OLE, нужно соблюсти ряд условий: установить Excel на ПК пользователя, если обмен планируется с клиентской стороны или установить программу на сервере при использовании серверного взаимодействия.
Ниже будет дан пример выгрузки документа. В нем создается управляемый объект COM и выключается вывод вопросов и уведомлений. Далее создается новая книга и позиционирование переносится на первый лист. Происходит запись значений в ячейку, сохранение файла под данным именем и закрытие Excel.
Листинг:
Эксель = Новый COMОбъект(“Excel.Application”);
Эксель.DisplayAlerts = Ложь;
Книга = Эксель.WorkBooks.Add();
Лист = Книга.Worksheets(1);
Лист.Cells(НомерСтроки, НомерКолонки).Value = ЗначениеЯчейки;
Книга.SaveAs(ИмяФайла);
Эксель.Quit();
Эксель = 0;
Чтение происходит аналогично: создается COM-объект и открывается книга. Далее выполняется позиционирование на нужном листе, производится чтение значения из ячейки или цикл обхода определенного числа ячеек и книга закрывается, как и Excel.
Листинг:
Эксель = Новый COMОбъект(“Excel.Application”);
Книга = Эксель.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Worksheets(1);
ЗначениеЯчейки = Лист.Cells(НомерСтроки, НомерКолонки).Value;
Книга.Close(0);
Эксель.Quit();
Эксель = 0;
Часто для обхода ячеек применяют цикл, проходящий все заданные массивы в необходимом порядке. Это работает быстро и эффективно при чтении именно больших объемов данных. Пример цикла прохода:
ВсегоКолонок = Лист.Cells(1, 1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1, 1).SpecialCells(11).Row;
Область = Лист.Range(Лист.Cells(1, 1), Лист.Cells(ВсегоСтрок, ВсегоКолонок));
Данные = Область.Value.Выгрузить();
Важно! Для более гибкой работы с OLE рекомендуется найти таблицу с наиболее востребованными свойствами и методами для Excel. Она содержит основные характеристики и названия функции для работы с приложением, листами, колонками и столбцами, ячейками.
Через COMSafeArray
Если производится выгрузка больших объемов данных, то иногда бывает эффективно использовать объекты COMSafeArray, которые, согласно определению, являются объектной оболочной многомерного массива SafeArray. Объект позволяет создавать массив этого типа и использовать его для загрузки и выгрузки данных и их обмена между объектами-COM. То есть, это тот тип объекта, который был использован в передаче данных по технологии OLE.
Пример:
МассивКом = Новый COMSafeArray(“VT_Variant«, ВсегоКолонок, ВсегоСтрок);
Для Стр = 0 По ВсегоСтрок — 1 Цикл
Для Кол = 0 По ВсегоКолонок — 1 Цикл
МассивКом.SetValue(Кол, Стр, Значение);
КонецЦикла;
КонецЦикла;
Лист.Range(Лист.Cells(1, 1), Лист.Cells(ВсегоСтрок, ВсегоКолонок)).Value = МассивКом;
В примере происходит создание объекта массива COMSafeArray и его заполнение. После чего, ячейкам Excel-листа присваиваются значения из него. Данные должны выгрузиться в текущий лист.
Через ADO
При таком обмене, файл-таблица Excel выступает в роли базы данных, к которой обращаются с помощью запросов, написанных на SQL.В этом способе даже не обязательна установка самого Excel, но обязательно наличие драйверов ODBC. Именно с его помощью и происходит доступ. Он определяется при указании пути к файлу для соединения с ним. Преимущество ADO в том, что оно заметно быстрее позволяет прочитать файл 1С в Excel, чем OLE. Однако оно не позволяет использовать функции «Экселя», чтобы оформить ячейки, страницы и формулы.
Возможный вариант выгрузки:
Соединение = Новый COMОбъект(“ADODB.Connection”);
Соединение.ConnectionString = ”
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=”+ИмяФайла+”;
|Extended Properties=””Excel 12.0 XML;HDR=YES””;”;
Соединение.Open();
Команда = Новый 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();
Соединение = Неопределено;
В примере происходит создание объекта для открытия соединения и установка той самой строки-пути соединения. Далее оно открывается и создается объект типа COM для команды. Происходит присвоение текста команды на создание таблицы и ее выполнение, присвоение команды для добавления записей в таблицу и ее выполнение. После всего этого команда удаляется и соединение закрывается.
Выгружать можно практически аналогичным образом. На самом деле в ADO есть большое количество объектов для работы. Errors, Streams, Fields — одни из них.
Без помощи программирования
Исключительный способ выгрузки без использования программирования и технологий был описан выше. Он основан на простом сохранении всей таблицы номенклатуры, баланса или их части с помощью стандартной команды «Save» или «Сохранить», способной сохранять данные в нескольких форматах, в том числе и в формате .xls.
Если необходимость сохранить динамическую таблицу, например, номенклатуру, то следует вывести данные из нее при помощи команды «Еще» — «Вывести список…» и сохранить личные карточки, информацию о контрагентах, накладные, отчеты или кассовые документы в нужном формате.
Возможные проблемы при выгрузке и загрузке данных из 1С в Excel
Иногда бывает так, что простая кнопка «Сохранить» не активна и не получается сохранить документ. В этом случае нужно активизировать саму форму, кликнув по ней в любом месте. Теперь кнопка станет активной. Другие ошибки, особенно при использовании программирования различных методов, чаще всего связаны с:
- неправильным указанием пути к файлу;
- попытками создать файл ненадлежащего имени;
- Опечатки в коде скриптов.
Таким образом, стало понятно, как из 1С выгрузить документы в Excel и как произвести импорт из Excel.В экспорте и импорте табличных файлов из Excel в 1C и наоборот нет ничего очень сложного. Существует множество методов это сделать и каждый из них больше подходит для своих ситуаций.
https://www.youtube.com/watch?v=O6agy1bsVfA