Недостатки 1с предприятие в помощь эксель. Что лучше – «1С» или «Excel»? Подключение внешнего загрузчика
Данный способ простой. Его суть заключается в том, что объект ТабличныйДокумент имеет методы:
- Записать(< ИмяФайла>, < ТипФайлаТаблицы >) для выгрузки данных в файл;
- Прочитать(< ИмяФайла>, < СпособЧтенияЗначений >) для загрузки данных из файла.
Внимание!
Метод Записать
()
доступен как на клиенте, так и на сервере. Метод Прочитать
()
доступен только на стороне сервера. Необходимо помнить об этом
при планировании клиент-серверного взаимодействия.
Рассмотрим пример сохранения табличного документа в файл. Необходимо любым способом создать и заполнить объект ТабличныйДокумент, а выгрузка в файл осуществляется всего лишь одной строкой:
ТабДок. Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента. XLSX );
Здесь ТабДок — сформированный табличный документ, ПутьКФайлу — имя файла для выгрузки, ТипФайлаТабличногоДокумента.XLSX — формат создаваемого файла. Поддерживаются следующие форматы Excel:
- XLS95 — формат Excel 95;
- XLS97 — формат Excel 97;
- XLSX — формат Excel 2007.
ТабДок = Новый ТабличныйДокумент;
ТабДок. Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение );
Здесь ПутьКФайлу — путь к загружаемому файлу Excel. СпособЧтенияЗначенийТабличногоДокумента.Значение определяет, каким образом нужно интерпретировать данные, считываемые из исходного документа. Доступны варианты:
- Значение;
- Текст.
Обмен через OLE
Обмен через технологию OLE automation, пожалуй, самый распространенный вариант программной работы с файлами Excel. Он позволяет использовать весь функционал, предоставляемый Excel, но отличается медленной скоростью работы по сравнению с другими способами. Для обмена через OLE требуется установка MS Excel:
- На компьютере конечного пользователя, если обмен происходит на стороне клиента;
- На компьютере сервера 1С:Предприятие, если обмен происходит на стороне сервера.
Пример выгрузки :
// Создание COM-объекта
Эксель = Новый COMОбъект(«Excel.Application»);
// Отключение вывода предупреждений и вопросов
Эксель. DisplayAlerts = Ложь;
// Создание новой книги
Книга = Эксель. WorkBooks. Add();
// Позиционирование на первом листе
Лист = Книга. Worksheets(1);// Запись значения в ячейку
Лист. Cells(НомерСтроки, НомерКолонки). Value = ЗначениеЯчейки;// Сохранение файла
Книга. SaveAs(ИмяФайла);
Эксель. Quit();
Эксель = 0;
Примеры чтения :
// —— ВАРИАНТ 1 ——
// Создание COM-объекта
Эксель = Новый COMОбъект(«Excel.Application» );
// Открытие книги
Книга = Эксель. Workbooks. Open(ПутьКФайлу );
Лист = Книга. Worksheets(1 );// Закрытие книги
Книга. Close(0 );// Закрытие Эксель и освобождение памяти
Эксель. Quit();
Эксель = 0 ;// —— ВАРИАНТ 2 ——
// Открытие книги
Книга = ПолучитьCOMОбъект(ПутьКФайлу );
// Позиционирование на нужном листе
Лист = Книга. Worksheets(1 );// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист. Cells(НомерСтроки, НомерКолонки). Value;// Закрытие книги
Книга. Application. Qui t();
Для обхода всех заполненных строк листа Excel можно использовать следующие приемы:
// —— ВАРИАНТ 1 ——
КоличествоСтрок = Лист. Cells(1 , 1 ). SpecialCells(11 ). Row;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
ЗначениеЯчейки = Лист. Cells(НомерСтроки, НомерКолонки). Value ;
КонецЦикла;// —— ВАРИАНТ 2 ——
НомерСтроки = 0 ;
Пока Истина Цикл
НомерСтроки = НомерСтроки + 1 ;
ЗначениеЯчейки = Лист. Cells(НомерСтроки, НомерКолонки). Value;
Если НЕ ЗначениеЗаполнено(ЗначениеЯчейки ) Тогда
Прервать;
КонецЕсли;
КонецЦикла;
Вместо последовательного обхода всех строк листа можно выгрузить все данные в массив и работать с ним. Такой подход будет быстрее при чтении большого объема данных:
ВсегоКолонок = Лист. Cells(1 , 1 ). SpecialCells(11 ). Column;
ВсегоСтрок = Лист. Cells(1 , 1 ). SpecialCells(11 ). Row;Область = Лист. Range(Лист. Cells(1 , 1 ), Лист. Cells(ВсегоСтрок, ВсегоКолонок));
Данные = Область. Value. Выгрузить ();
В таблице ниже приведены наиболее востребованные свойства и методы для работы с Excel через OLE:
Действие | Код | Комментарий |
Работа с приложением | ||
Установка видимости окна приложения | Эксель. Visible = Ложь; | |
Установка режима вывода предупреждений (выводить/не выводить) | Эксель. DisplayAlerts = Ложь; | |
Закрытие приложения | Эксель. Quit(); | |
Работа с книгой | ||
Создание новой книги | Книга = Эксель. WorkBooks. Add (); | |
Открытие существующей книги | Книга = Эксель. WorkBooks. Open(ИмяФайла ); | |
Сохранение книги | Книга. SaveAs(ИмяФайла ); | |
Закрытие книги | Книга. Close(0 ); | |
Работа с листом | ||
Установка текущего листа | Лист = Книга. WorkSheets(НомерЛиста ); | |
Установка имени | Лист. Name = Имя ; | |
Установка защиты | Лист. Protect (); | |
Снятие защиты | Лист. UnProtect (); | |
Установка ориентации страницы | Лист. PageSetup. Orientation = 2 ; | 1 — книжная, 2 — альбомная |
Установка левой границы | Лист. PageSetup. LeftMargin = Эксель. CentimetersToPoints(Сантиметры ); | |
Установка верхней границы | Лист. PageSetup. TopMargin = Эксель. CentimetersToPoints(Сантиметры ); | |
Установка правой границы | Лист. PageSetup. RightMargin = Эксель. CentimetersToPoints(Сантиметры ); | |
Установка нижней границы | Лист. PageSetup. BottomMargin = Эксель. CentimetersToPoints(Сантиметры ); | |
Работа со строками, колонками, ячейками | ||
Установка ширины колонки | Лист. Columns(НомерКолонки). ColumnWidth = Ширина ; | |
Удаление строки | Лист. Rows(НомерСтроки). Delete (); | |
Удаление колонки | Лист. Columns(НомерКолонки). Delete(); | |
Удаление ячейки | Лист. Cells(НомерСтроки, НомерКолонки). Delete (); | |
Установка значения | Лист. Cells(НомерСтроки, НомерКолонки). Value = Значение ; | |
Объединение ячеек | Лист. Range(Лист. Cells(НомерСтроки, НомерКолонки), Лист. Cells(НомерСтроки1, НомерКолонки1)). Merge (); | |
Установка шрифта | Лист. Cells(НомерСтроки, НомерКолонки). Font. Name = ИмяШрифта ; | |
Установка размера шрифта | Лист. Cells(НомерСтроки, НомерКолонки). Font. Size = РазмерШрифта ; | |
Установка жирного шрифта | Лист. Cells(НомерСтроки, НомерКолонки). Font. Bold = 1 ; | 1 — жирный шрифт, 0 — нормальный |
Установка курсива | Лист. Cells(НомерСтроки, НомерКолонки). Font. Italic = 1 ; | 1 — курсив, 0 — нормальный |
Установка подчеркнутого шрифта | Лист. Cells(НомерСтроки, НомерКолонки). Font. Underline = 2 ; | 2 — подчеркнутый, 1 — нет |
Для того, чтобы узнать какое свойство нужно менять или какой метод вызвать можно воспользоваться макросами Excel. Если записать макрос с требуемыми действиями, то после можно посмотреть программный код на VBA записанного макроса.
Использование 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 при обмене через ADO представляет собой базу данных, к которой можно обращаться при помощи SQL-запросов. Установка MS Excel не требуется, но обязательно наличие драйвера ODBC, при помощи которого будет осуществляться доступ. Используемый драйвер ODBC определяется при указании строки соединения к файлу. Обычно требуемый драйвер уже установлен на компьютере.
Обмен через ADO заметно быстрее обмена через OLE, но при выгрузке нет возможности использовать функционал Excel для оформления ячеек, разметки страниц, задания формул и т.д.
Пример выгрузки :
Соединение = Новый COMОбъект(«ADODB.Connection»);
Соединение. ConnectionString = «
|Data Source=»+ ИмяФайла + «;
;
Соединение. Open(); // Открытие соединения// Создание COM-объекта для команды
Команда = Новый COMОбъект(«ADODB.Command» );
Команда// Присвоение текста команды для создания таблицы
Команда. 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.
Пример чтения :
// Создание 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 не всегда целесообразно прибегать к программированию. Если в режиме Предприятия пользователь может отобразить требуемые для выгрузки данные, то, их возможно сохранить в Excel без программирования.
Для сохранения табличного документа (например, результата отчета) можно вызвать команду Сохранить или Сохранить как… главного меню.
В открывшемся окне требуется выбрать каталог, имя и формат сохраняемого файла.
Для сохранения данных динамических списков (например, списка номенклатуры) необходимо:
- Вывести данные в табличный документ при помощи команды Еще ⇒ Вывести список… ;
- Сохранить табличный документ в требуемый формат.
Многим офисным работникам не редко приходится пользоваться программами 1C и Excel. Им приходится экспортировать данные из одного приложения в другое. Сегодня мы рассмотрим, как выгрузить из 1С в Excel.
Выгружаем данные из 1С в Эксель
Выгрузка данных из 1С в Microsoft Excel – процесс, состоящий из несложных действий. Для этого достаточно встроенных в данные приложения средств. Есть несколько способов экспорта данных.
Выгружаем большое количество данных
В открытом документе следует кликнуть правой кнопкой мыши и выбрать «Вывести список». После этого все элементы будут отображены как обычный текст. Его можно скопировать и вставить в документ MS Excel, но лучше открыть меню «Файл» и перейти в раздел «Сохранить как».
Остается лишь выбрать формат файл и сохранить его.
Выгружаем печатные формы документов
Как вставить список в книгу Excel
Чтобы перенести список следует воспользоваться следующим способом:
- Нужный список открываем в программе 1С. Затем необходимо правой кнопкой мыши кликнуть на «Все действия», а затем «Вывести список».
- Появится оболочка вывода списка. В поле «Выводит в» должен быть установлен вариант «Табличный документ».
- Чуть ниже можно выбрать колонки, которые следует перенести.
Совет! Не следует ничего менять в этом окне, когда вы собираетесь полностью перенести документ.
- После всего этого пользователь увидит на экране список в виде таблицы. Его нужно весь выделить и скопировать.
- Затем создается новый документ в Excel и данные вставляются в него при помощи «CTRL+V».
Создаем новую книгу Excel со списком
Можно долго не возиться и сразу вывести список из 1С в Эксель:
1. Делаем все то же, что и в предыдущем методе пока не сформируется список. Затем нужно перейти в меню, открыть раздел «Файл» и выбрать «Сохранить как».
Заключение
Теперь вы знаете, несколько способов, как выгрузить таблицу из 1С в Эксель. Процедура довольно легкая, и с ней справится абсолютно любой пользователь. Главное – это хорошо изучить наши инструкции, выбрать подходящий способ и четко следовать указаниям. Чтобы было проще, смотрите на вставленные скрины.
Довольно часто, при общении с потенциальными клиентами, я слышу о том, что им вполне хватает функционала таблицы от офисного пакета, и они не видят смысла в программах «1С». Все потому что некоторые по-прежнему ассоциируют данное решение лишь с программами для бухгалтера, что является неправдой.
«Excel» – универсальный и массовый продукт, с этим не поспоришь. Практически каждый может воспользоваться данной программой, в отличие от разработок «1С», которые направлены на узких специалистов. Используя «зеленые» таблицы, можно произвольно реализовывать необходимый функционал, который будет удобным: тут и финансовая модель организации со всеми взаиморасчетами, управленческий учет, даже регламентированный, большой выбор визуализации данных, возможность собственноручно расширять возможности программы, используя различные надстройки – все в Ваших руках, все с нуля...
Хорошо, если Вы только начинаете свой бизнес, постепенно учитывая необходимые данные в таблице по взаимоотношениям с клиентами, поставщиками, контролирующими органами, выстраивая свою произвольную структуру, но как быть дальше, когда база данных станет объемной, а штат сотрудников будет исчисляться десятками? Все так же считать по собственным параметрам? Я за то, чтобы не путать удобство с привычкой, которая зачастую мешает систематизации бизнеса, а следственно - его оптимизации.
Для наглядности давайте представим какие могу возникнуть потребности в автоматизации учета, например, у торговли:
1. Анализ продаж в рамках определенного периода.
Руководитель отдела продаж может анализировать данные по продажам. Ему ведь важно понимать выполняется ли план, насколько эффективны его стратегии, чтобы в случае неэффективности вовремявнести корректировку в работу.
2. Складской учет.
Нужно ли объяснять, что для продаж также важно понимание наличия товара на складе, какой товар может быть зарезервирован, а какого нет в наличии и что нужно заказать? Думаю, тут и так все понятно.
3. Ведение базы контрагентов.
Даже если собственник продает лишь то, что можно купить здесь и сейчас, что характерно для сектора B2C, то с базой поставщиков у него немного другие отношения – договора, первичка... Представьте, насколько это удобно, когда все, что связано с поставщиком автоматически сразу отображается в единой базе? Менеджер, отвечающий за склад, сразу видит наличие товара на складе, другой сотрудник, ответственный за документы, мониторит информацию по их наличию, исполняются ли все обязательства по договорным отношениям, кто и сколько кому должен. А в случае расхождений можно сверить данные за определенный период, сформировав результат менее чем за минуту.
4. Прибыль организации.
Сформировать отчет по прибыли не составит большого труда, поскольку все данные по расходам и доходам уже и так присутствуют в базе. При том условии, если информация вносится своевременно, по крайней мере, нужной датой. Тут основополагающий фактор – личная ответственность пользователя.
Если у Вас не так много клиентов и поставщиков, Вам действительно подойдет «Excel», так как времени предостаточно для конструирования собственных схем в таблице и их заполнений, с последующим распределением документов по различным папкам на компьютере: договора, поставщики, клиенты, клиенты по соседству, клиенты в разработке, клиенты на удаление – бесконечный и увлекательный процесс создания папок в папке. А если информации становится много, удобно ли в этом случае вести учет своей базы? Конечно, всегда есть исключения, порой встречаются организации с большими оборотами, у которых данные по финансам ведутся только в «Excel». Сколько же при этом времени у них уходит на обмен и объединение данными между отделами? Отвечу: много.
Я не представляю, как в большом количестве документов «Excel», организованных произвольной системой, оперативно найти нужный. К примеру, при предоставлении документов на требования от налоговой. Как бухгалтер будет сдавать отчетность в контролирующие органы, когда информация вся разрознена? Бухгалтерия, конечно, может быть на аутсорсинге и некоторых собственников бизнеса, к сожалению, может не волновать какие у данного специалиста могут быть процессы лишь бы балансы сводил. Но даже если так, то как тогда при этом организован процесс обмена документами? Насколько быстро сотрудник на стороне разберется в импровизированной информационной структуре?
Про «1С» такого сказать не могу, поскольку там вся информация структурирована и взаимосвязана:
-
Единая база как для удаленных, так и для штатных сотрудников, в которой можно работать со всеми стандартизированными и произвольными документами;
Формирование отчетности в контролирующие органы происходит на основании данных, которые уже отражены в программе, нет необходимости открывать множество файлов, сводить несколько значений в одну таблицу получая итоговые результаты.
В защиту «Excel»
Наверное, Вам показалось, что я против построения данных в «зеленых» таблицах. Отнюдь. На самом деле я сам частенько веду некоторые дела в «Excel»: если мне нужны произвольные вычисления, если мне необходимо сегментировать данные по клиентам, проводя аналитику по тем параметрам, которых нет в «1С» - да, такое бывает – я иду в «Excel». Проще говоря, в таблицах я занимаюсь вычислениями, нестандартным анализом, но систематизирую все потом все равно в «1С».
Да и потом, для просмотра выгруженных файлов из «1С» все равно понадобиться «Excel», так как данные в «1С» хранятся в электронных таблицах. Вам кажется, что это замкнутый круг? Я скажу по-другому: «1С» и «Excel» идут рядом как два напарника, но у каждого из них свое предназначение, они хорошо дополняют друг друга, но не взаимозаменяют.
Если Вам нужно систематизировать ваши дела с учетом законодательной системы нашего государства, с учетом специфики и направленности бизнеса, тем более, когда дело касается больших объемов информации, то Вам нужен «1С». Если Вам нужны произвольные расчеты, построение стратегии с нуля, визуализация нестандартных данных по аналитике, то к Вашим услугам «Excel». Но гораздо удобней работать с данными решениями одновременно.
Что в итоге – «1С» или «Excel»?
На самом старте, если вопрос именно в финансах на автоматизацию, хотя базовые программы «1С» стоят не таких уж больших денег, я бы воспользовался «Excel». Огромный плюс, в том, что данные из «зеленых» таблиц без потерь можно подгрузить в «1С». Но по мере масштабирования, я бы советовал обратить внимание на программы по автоматизации бизнес-процессов. Их много, и не обязательно это может быть «1С»...
Переход на «1С» можно осуществить тогда, когда Вы понимаете, что необходимо оптимизировать свои ресурсы, в том числе автоматизировать вопросы по взаимоотношению с клиентами и поставщиками, с надзорными органами. В общем, наладить свои рабочие процессы, чтобы повысить эффективность бизнеса, когда обработка информации будет занимать большое количество времени, а файлы «Excel» уже не будут справляться с объемами вводимых данных.
Однако не каждая «1С» может Вам подойти, нужно учитывать различные факторы: специфику и сферу бизнеса, масштабы, потребность в ежедневных задачах, рутину которую необходимо свести к минимуму. Все индивидуально. Истина, как я уже говорил, где-то между «1С» и «Excel» – она в дополнении друг друга.
На этом, пожалуй, все. Но если возникнут вопросы, то обращайтесь, постараемся Вам помочь. Успешных бизнес-процессов, коллеги!