Створення файлу excel із 1с 8.3. Що краще – «1С» чи «Excel»? У вас є питання, чи потрібна допомога консультанта
Вже давно найпопулярнішою програмою серед бухгалтерів, планувальників, економістів та управлінців став додаток 1C. Воно має не тільки різноманітну кількість конфігурацій для різних видівдіяльності, а й локалізації під стандарти обліку у кількох країнах світу. Дедалі більше підприємств переходить на ведення обліку саме у цій програмі. Але процедура перенесення даних вручну з інших програм обліку в 1C - досить довге і нудне заняття, що забирає багато часу. Якщо підприємстві вівся облік з допомогою Excel, процес переносу можна істотно автоматизувати і прискорити.
Виробляти перенесення даних з Excel в 1С потрібно як на початковому періоді роботи з цією програмою. Іноді настає необхідність подібного, коли під час діяльності потрібно занести якісь списки, що зберігаються у книзі табличного процесора. Наприклад, якщо потрібно перенести прайс-листи або замовлення онлайн-магазину. У випадку, коли списки невеликі, їх можна вбити і вручну, але що робити, якщо вони містять сотні найменувань? Для того, щоб прискорити процедуру, можна вдатися до деяких додаткових можливостей.
Для автоматичного завантаженняпідійдуть практично всі типи документів:
- Список номенклатури;
- Список контрагентів;
- Список прайсів;
- Перелік замовлень;
- Інформація про купівлю або продаж і т.д.
Відразу слід зазначити, що в 1С немає вбудованих інструментів, які б дозволяли переносити дані з Ексель. Для цих цілей потрібно підключити зовнішній завантажувач, який є файлом у форматі epf.
Підготовка данних
Нам потрібно буде підготувати дані у самій таблиці Excel.
Крім цих універсальних дій щодо підготовки даних у книзі Ексель, потрібно буде ще привести документ у відповідність до вимог конкретного завантажувача, який ми будемо використовувати, але про це ми поговоримо трохи нижче.
Підключення зовнішнього завантажувача
Підключити зовнішній завантажувач із розширенням epfдо додатку 1С можна як до підготовки файлу Ексель, так і після. Головне, щоб до початку процесу виконання завантаження обидва ці підготовчих моментубули вирішені.
Існує кілька зовнішніх завантажувачів таблиць Ексель для 1С, які створені різними розробниками. Розглядатимемо ми приклад із використанням інструменту для обробки інформації «Завантаження даних із табличного документа» для версії 1С 8.3.
Однією з основних баз даних, із якими працює 1C, є список номенклатури товарів та послуг. Тому для опису процедури завантаження з Excel зупинимося на прикладі перенесення цього типу даних.
- Повертаємось до вікна обробки. Так як ми завантажуватимемо номенклатуру товарів, то в параметрі перемикач повинен стояти в позиції «Довідник». Втім, його так встановлено за умовчанням. Перемикати його слід лише тоді, коли ви зібралися переносити інший тип даних: табличну частину або регістр відомостей. Далі у полі «Вигляд довідника»клацаємо по кнопці, на якій зображено крапку. Відкривається список, що випадає. У ньому нам слід вибрати пункт "Номенклатура".
- Після цього обробник автоматично розставляє поля, які програма використовує у цьому виді довідника. Потрібно відразу зазначити, що не обов'язково заповнювати всі поля.
- Тепер знову відкриваємо документ Excel, що переноситься. Якщо найменування його колонок відрізняється від найменування полів довідника 1С, які містять відповідні, потрібно перейменувати ці стовпці в Екселі так, щоб назви повністю збігалися. Якщо таблиці є стовпці, котрим у довіднику немає аналогів, їх слід видалити. У нашому випадку такими стовпцями є «Кількість»і «Ціна». Також слід додати, що порядок розміщення стовпців у документі повинен суворо збігатися з тим, що представлений в обробці. Якщо для деяких стовпців, які відображаються в завантажувачі, у вас немає даних, то ці колонки можна залишати порожніми, але нумерація стовпців, де є дані, повинна збігатися. Для зручності та швидкості редагування можна застосовувати спеціальну можливість Ексель для швидкого переміщення колонок місцями.
Після того, як ці дії зроблені, тиснемо на значок «Зберегти», який представлений у вигляді піктограми, що зображує дискету у лівому верхньому куті вікна. Потім закриваємо файл, натиснувши стандартну кнопку закриття.
- Повертаємось у вікно обробки 1С. Тиснемо на кнопку «Відкрити»яка зображена у вигляді папки жовтого кольору.
- Відкриється вікно відкриття файлу. Переходимо до директорії, де розташований документ Ексель, який нам потрібен. Перемикач відображення файлів за замовчуванням встановлений для розширення mxl. Щоб показати потрібний нам файл, його потрібно переставити в позицію «Аркуш Excel». Після цього виділяємо документ, що переноситься, і тиснемо на кнопку «Відкрити».
- Після цього вміст відкривається в обробнику. Щоб перевірити коректність заповнення даними, тиснемо на кнопку «Контроль заповнення».
- Як бачимо, інструмент контролю заповнення повідомляє нам, що помилок не знайдено.
- Тепер переміщуємось у вкладку "Налаштування". У «Поле пошуку»ставимо галочку в тому рядку, який у всіх найменувань, що заносяться до довідника номенклатури, буде унікальним. Найчастіше для цього використовують поля «Артикул»або «Найменування». Це потрібно робити для того, щоб при додаванні нових позицій до списку дані не подвійні.
- Після того, як усі дані внесені та налаштування виконані, можна переходити до безпосереднього завантаження інформації до довідника. Для цього клацаємо по напису "Завантажити дані".
- Виконується процес завантаження. Після її завершення можна перейти до довідника номенклатури та переконатися, що всі потрібні дані туди додані.
Ми простежили процедуру додавання даних до довідника номенклатури у програмі 1C 8.3. Для інших довідників та документів завантаження буде проводитися за тим самим принципом, але з деякими нюансами, з якими користувач зможе розібратися самостійно. Також потрібно зауважити, що у різних сторонніх завантажувачів процедура може відрізнятися, але загальний підхід залишається у всіх однаковий: спочатку обробник завантажує з файлу інформацію у вікно, де проводиться її редагування, а потім вона додається безпосередньо в базу даних 1С.
У 1С існує два способи роботи з файлами MS Excel, через COM-об'єкт і вбудованими засобами 1С за допомогою об'єкта табличний документ. Розберемо обидва ці способи докладніше.
1. Робота через COM-об'єкт.
Для цього способу необхідний встановлений MS Excel, притому якщо ви працюєте з файлом на сервері, то відповідно MS Excel має бути встановлений на сервері, якщо на клієнті - на клієнтській стороні так само необхідний MS Excel.
Приклад (наприкінці статті можна подивитися у текстовому вигляді):
Слід зазначити, що це об'єкти, методи та властивості, що надаються COM-объектом "Excel.Application" - це об'єкти, методи та властивості VBA, мови програмування MS Office.
Примітка:
Буває потрібно працювати з існуючим шаблоном. Тоді перед нами виникає необхідність десь зберігати цей шаблон, щоб до нього мали доступ усі необхідні користувачі. Однак є простіше рішення, зберігаємо шаблон як двійковий макет.
2. Робота через Табличний Документ 1С.
Табличний документ 1С підтримує формат MS Excel для запису з платформи 8, а ось для відкриття тільки з платформи 8.3.6. При відкритті всі листи завантажуються в один табличний документ. Починаючи з версії 8.3.10, при завантаженні різні аркуші завантажуються, як різні області.
Приклад запису дуже простий і вимагає особливої уваги:
Однак тут у нас постає проблема. При записі з 1С книзі Excelза промовчанням вимкнено відображення імен аркушів.
Цю проблему можна вирішити 2 способами, 1 - у самій книзі в налаштуваннях увімкнути відображення аркушів (не всі користувачі будуть згодні це робити), 2 - зробити це через COM-об'єкт (нам знову потрібний встановлений MS Excel).
Після цього невеликого доповнення у файлі MS Excel будуть видні ярлики аркушів.
Читання ж MS Excel не така проста задача, оскільки метод прочитати доступний тільки на сервері або товстому клієнті. Для цього нам потрібно передати файл MS Excel на сервер.
&На Клієнті
Процедура Створення документа()
Виняток
КінецьСпроби;
Книга = Ексель.WorkBooks.Add(); //Створюємо нову книгу MS Excel
Аркуш = Книга.WorkSheets.Add(); //Додаємо лист
Лист.Name = "Приклад із 1С"; //Задаємо ім'я аркуша
Лист.Cells(1,1).Value = "Давайте створимо формулу";!}
Аркуш.Cells (2,1).Value = 1;
Аркуш.Cells (2,2).Value = 2;
Аркуш.Cells (2,3).Formula = "=A2+B2";
Аркуш.Cells (3,1).Value = ПоточнаДата();
SaveAs("C:\1\Test.xlsx");
Книжка.Close();
КінецьПроцедури
&На сервері
Функція ОтриматиМакетСервер()
Повернення РеквізитФормиЗначення("Об'єкт").ОтриматиМакет("ШаблонНаВивантаження"); //Таким чином ми отримуємо макет зовнішньої обробки;
КінецьФункції
&На Клієнті
Процедура Робота()
Ексель = Новий COMОб'єкт ("Excel.Application");
Виняток
Повідомити("Невдала спроба підключення компоненти Excel. Можливо, програма Excelне встановлена на цьому комп'ютері!");
КінецьСпроби;
Макет = ОтриматиМакетСервер();
Макет.Записати(Ім'яТимчасовогоФайлу);
У вас є питання, чи потрібна допомога консультанта?
Книга = Ексель.WorkBooks.Open (Ім'я Тимчасового Файлу);
Аркуш Шаблона = Книга. WorkSheets (1);
Аркуш Шаблона.Cells (6,1).Value = "Дата:";!}
Аркуш Шаблона.Cells (6,2).Value = ПоточнаДата();
Аркуш Шаблона.NumberFormat = "dd/mm/yy;@"; // Дамо формат дати, цей формат отримано записом макросу в MS Excel
Аркуш Шаблона.Columns("B:B").EntireColumn.AutoFit; // Розтягнемо колонку, щоб дата точно містилася
SaveAs(Ім'яФайлу);
Книжка.Close();
КінецьПроцедури
&На Клієнті
КінецьПроцедури
&На Клієнті
Процедура ЗаписатиТабличнийДокумент()
Табличний Документ = Новий Табличний Документ ();
ТабличнийДокумент.Область("R1C1").Текст = "Приклад запису в MS Excel з 1С";
ТабличнийДокумент.Записати("C:\1\Test2.xls",ТипФайлаТабличногоДокумента.XLSX);
Excel = Новий COMОб'єкт ("Excel.Application");
Excel.WorkBooks.Open("C:\1\Test2.xls");
Excel.Visible = 0;
Excel.ActiveWindow.DisplayWorkbookTabs = 1;
Excel.ActiveWindow.TabRatio = 0.6;
Excel.ActiveWorkbook.Save();
Excel.Application.Quit()
КінецьПроцедури
ДвійковіДані = Новий ДвійковіДані("C:\1\test2.xlsx");
Адреса=ПоміститиВчаснеСховище(ДвійковіДані,ЦяФорма.УнікальнийІдентифікатор) ;
ТабличнийДокумент = ЗавантажитиНа Сервері (Адреса);
Табличний Документ. Показати ();
КінецьПроцедури
&На сервері
Функція ЗавантажитиНа Сервері(Адреса)
Ім'яТимчасовогоФайлу = ОтриматиІм'яТимчасовогоФайлу("xlsx");
ДаніФайла = ОтриматиЗ ТимчасовогоСховища(Адреса);
ДаніФайла.Записати(Ім'яТимчасовогоФайлу);
Табличний Документ = Новий Табличний Документ ();
Повернення ТабличнийДокумент;
- При виконанні обміном інформацією між контрагентами (прайс-листи, акти звіряння тощо);
- Для полегшення роботи операторів у випадках, коли основний облік ведеться до 1С, а деяка його частина виконується у таблицях Excel;
- При первинному наповненні бази даних.
Для виконання цієї операції можна використовувати як типовий функціонал, доступний за наявності передплати інформаційно-технологічного супроводу (ІТС), так і самостійно написані обробки, реалізовані через різні варіанти підключення. У нашій статті ми намагатимемося максимально повно розібрати всі можливі випадки та відповісти на більшість існуючих питань щодо завантаження даних з Excel у 1С.
Універсальний механізм
На дисках ІТС, а також на порталі 1С, зайшовши в меню "Технологічна підтримка" -> "Універсальні звіти та обробки", у папці "Завантаження даних з табличного документа" знаходиться відповідна обробка.
При її запуску відкривається форма (Рис.1):
Як видно з форми, вона дозволяє виконувати перенесення інформації у такі об'єкти конфігурації:
- Довідник;
- Табличну частинудокумента чи довідника;
- Регістр відомостей.
Залежно від того, в яке положення встановлено перемикач, змінюється поле вибору об'єкта.
Таблична форма обробки заповнюється при відкритті файлу, що завантажується.
Типова обробка підтримує вивантаження з:
- Розробленого фахівцями фірми 1С, файлів форматуmxl;
- Листаxls, збереженого у форматіExcel 97-2003;
- Текстовий файлtxt;
- Таблиціdbf.
Excel файли великого обсягу можуть завантажуватися досить довго, так що, якщо передбачається, що завантаження даних може відбуватися в кілька етапів або Ви точно знаєте, що Вам доведеться кілька разів відкривати існуючий файл, для економії нервів і часу, інформацію з нього краще зберегти в mxl формат. Зробити це можна безпосередньо із форми обробки.
Важливо розуміти, що якщо у вихідному файлі існують угруповання рядків, підзаголовки та примітки, їх треба буде видалити вручну.
Тепер перейдемо до закладки «Налаштування» (Рис.2):
Рис.2
Досить часто еселівські таблиці містять шапку зі всілякими реквізитами та даними (найменування друкованої форми, реквізити контрагента, дату та номер вхідного документа, імена колонок і так далі), щоб виключити їх обробку програмою на формі, у реквізиті «Перший рядок табличного документа» треба вказати перший рядок з інформацією, що переноситься
Після вибору об'єкта метаданих, на який буде здійснюватися запис інформації, таблична частина закладки «Налаштування» буде автоматично заповнена іменами реквізитів, описом їх типів та ін. важливою інформацією. Розгляду колонок табличної частини закладки «Налаштування» слід присвятити окремий розділ.
Колонки «Налаштування»
Позначка – встановленням чи зняттям прапорця у рядку визначається, чи заповнюватиметься відповідний реквізит.
Подання реквізиту – тут прописується синонім (найменування) реквізиту метаданих, як його в конфігураторі.
Поле пошуку – якщо встановити галочку в цьому полі, обробка здійснюватиме пошук елементів за відповідним реквізитом і, у разі успіху, змінювати наявні дані, додаткова функція цього поля – захист від появи дублікатів.
Опис типів – відображає тип даних, який має той чи інший реквізит метаданих.
Режим завантаження – пропонує три варіанти на вибір (Рис.3):
Рис.3
- Шукати – здійснюватиметься пошук відповідного елемента, у разі його відсутності може бути створено новий;
- Встановлювати - підпорядкованим чином встановлюється певне значення;
- Обчислювати – у цьому випадку в полі створюваного елемента буде встановлений результат обчислення виразу, вказаного в колонці «Умова зв'язку/Вираз значення».
У разі, активація поля Вираз викликає відкриття форми (Рис.4).
Рис.4
Номер колонки - поле, яке використовується для вказівки, з якої колонки еселівської таблиці необхідно брати дані для заповнення.
Значення за замовчуванням - досить часто виникає ситуація, коли в файлі, що завантажується, містяться не всі необхідні для запису елемента дані, в цьому випадку для заповнення буде проставлена інформація, що міститься в даному полі.
Умова зв'язку/Вираз значення – частково ми вже торкнулися цього поля, коли розглядали поля, додатково в ньому можна вказати умову, відповідно до якої будуть синхронізовані вихідні дані.
Ось, в принципі, і вся інформація, яка доступна на закладці «Налаштування».
Щоб щоразу не вбивати багато часу на завантаження, прописування відповідностей та виразів, розробники передбачили можливість збереження варіанта налаштування у файл з розширенням mxlz.
Перевірити коректність даних можна натисканням на кнопку «Контроль заповнення» (Рис.1). Після цього можна запускати процес завантаження. Про успішне виконання процедури або нестандартні ситуації Ви будете оповіщені окремо.
Для завантаження даних з Екселя в інформаційних базах"Управління та торгівля" існує ще один механізм. Він менш універсальний, ніж наведений вище спосіб, проте не вимагає підписки ІТС і міститься у типовій поставці.
Цю обробку можна знайти на закладці «Закупівлі», в меню «Сервіс», вона має назву «Завантаження цін постачальника з файлів» (Мал.5)
Рис.5
Форма обробки містить:
- поле вибору дати, яка вказуватиме, на який час актуальна дана ціна;
- Поле вибору контрагента, який надіслав свій прайс-лист;
- Кнопку, що дозволяє вибрати тип цін, що встановлюються;
- Табличну частину, яку можна заповнити даними, що завантажуються.
Цю форму можна побачити на Рис.6
Рис.6
Пояснення у верхній частині форми пояснює, як користуватись першою закладкою форми.
Після вибору контрагента (залежно від того, чи виступає він постачальником, комісіонером чи продавцем) у таблиці стануть доступні додаткові колонки для кожного типу цін.
При роботі через веб-інтерфейс, в деяких браузерах може знадобитися встановлення браузерного доповнення (Мал.7). Нам потрібно натиснути кнопку «Почати встановлення» та перезапустити наше підключення.
Рис.7
Після цього ми, використовуючи буфер обміну, зможемо переносити інформацію з однієї таблиці до іншої. Коли необхідні колонки («Артикул», «Найменування», «Ціна») будуть заповнені, ми натискаємо кнопку «Далі» і переходимо на другу сторінку (Рис.8)
Рис.8
Програма автоматично виконає пошук відповідності всередині бази даних і, якщо таких не знайдеться, запропонує варіанти усунення помилки. Зовнішній виглядтабличній частині можна контролювати за допомогою перемикача. Крім того, користувач може самостійно зіставити елементи файлу, що завантажується наявним в базі даних.
- Реєструвати все поспіль;
- Реєструвати тільки ті, що змінилися порівняно з наявними в базі.
У текстовому полі можна внести коментар, записаний у документі (Рис.9):
Рис.9
Після виконання обробки:
- У довіднику "Номенклатура постачальника" буде створено відповідний елемент (якщо його не було);
- Йому буде зіставлено елемент довідника «Номенклатура»;
- Буде створено та проведено документ «Встановлення цін номенклатури» із зазначенням: постачальника, типу цін та дати фіксації даних.
Аналогічно працює обробка «Завантаження товарів із зовнішніх файлів».
Варіанти обробки перенесення своїми руками
Головна проблема у висмикуванні даних з екселівського файлу полягає в тому, що 1С не має вбудованого однозначного механізму його відкриття. Існує кілька варіантів підключення Excel до 1С:
- Через Microsoft ADODB – достатньо швидкий спосіб, Який, як правило, застосовний і для файлового і для клієнт-серверного варіанту зберігання бази;
- Через використання Microsoft Office - метод, який іноді збоїть при роботі з SQL базами, як правило, працює дещо повільніше першого способу, до того ж потрібно встановлений Office;
- Через Libre Office - на відміну від попереднього методу безкоштовний, крім xls і xlsx форматів підтримує ще й власні таблиці, але вимагає встановленого пакета LibreOffice і деякої підготовки файлу, що завантажується (перший рядок таблиці повинен містити імена колонок).
Розглянемо докладніше різні способита варіанти.
Через ADODB.Connection
Взагалі, ADO розшифровується як ActiveX Data Object і служить для програмного доступу до різних баз даних. Найбільша проблема при створенні будь-якого підключення до стороннього файлу (у тому числі й до еселівського) – грамотно скласти рядок підключення.
Для файлів Excel тут можливі три варіанти:
Параметри рядка підключення:
- Provider – тут визначається драйвер, що використовується;
- Data Source – визначає ім'я файлу, який ми відкриватимемо;
- Extended Properties – тут можна вказати: чи потрібний рядок-заголовок для таблиці (HDR = YES говорить про те, що дані будуть зчитуватися з першого рядка, HDR = NO – що з другого), чи відкривається файл тільки для читання (ReadOnly) та деякі Інші додаткові параметри.
Створивши рядок підключення, ми можемо з'єднатися із файлом, що завантажується (Рис.13)
Рис.13
Тепер ми можемо за допомогою простого запиту (Рис.14) запустити вибірку інформації із файлу, що завантажується.
В даному випадку параметр «Аркуш» визначає, з яким листом з екселівської книги нам доведеться працювати.
Набір записів, що зберігаються на аркуші, можна прочитати за допомогою об'єкта Recordset. При цьому перший запис листа можна отримати параметром BOF (початок файлу), а останній EOF (кінець файлу).
Через Excel додаток
Головна відмінність від попереднього способу – крім драйверів роботи з базами даних, на комп'ютері, де виконується з'єднання, має бути встановлений Excel. Тільки в цьому випадку ми можемо ініціалізувати програму для читання даних з таблиці (Рис 16).
Цей COM об'єкт має кілька дочірніх параметрів, але головним нам, за поточних умов завдання, є параметр WorkBooks (Рис.17).
Після ініціалізації книги необхідно визначити аркуш, з якого зчитуватимуться дані (Рис.18).
Після цього можна перебирати всередині циклів рядки і колонки таблиці файлу, що відкривається.
Декілька слів про можливі помилки
Левова частка помилок при підключенні до файлу виникає через те, що файл вже зайнятий іншою програмою. Добре, якщо в панелі завдань видно, що Excel запущено на комп'ютері, але якщо Ви або інший користувач відкрили його із зовнішньої обробки, візуально це можна визначити лише через «Диспетчер завдань», так що не забувайте перед закінченням перенесення закрити підключення:
У разі роботи через ADO (Мал.19);
Рис.19
- У разі роботи з додатком (Мал. 20).
Мал. 20.
Підключення та процедуру завершення роботи з даними найкраще організовувати всередині конструкції Спроба-Виключення-КінецьСпроби, викликаючи у винятковій ситуації опис помилки. Хоча це іноді й уповільнює роботу, але значно спрощує визначення причини виникнення помилки і, зрештою, способів її усунення.
