Немного слов
Время показало, что приложения для работы с электронными таблицами, в том числе и Microsoft Excel, имеют потрясающие возможности для сбора, систематизации и анализа различных данных. Трудно представить бизнес, где бы не использовали этот инструмент. Вне зависимости от размера компании в ней обязательно используют электронные таблицы в том или ином виде.
Все те, кто работают с платформой 1С и решениями на ее основе не по наслышке знают, что Excel является чуть ли не главным инструментом бухгалтеров, аналитиков, финансистов и многих других специалистов.
Коллеги, Вы только вспомните сколько было сделано выгрузок из 1С в Excel! Сколько человеко-часов на это было потрачено!
Нет, нет! Excel ни в коем случае не является заменой учетных систем 1С, ведь назначение у них совершенно разное. Уберите оружие! Он скорее инструмент локального пользования для более гибкой обработки данных, ведь в 1С это не всегда сделать также просто, как в Excel.
Но если все так хорошо, то почему статья будет про Access? Неужели им кто-то еще пользуется? И на самом деле пользуется!
Excel vs. Access
Эпичной битвы не будет! Мы просто сравним два отличных инструмента по 9 критериям и опишем ситуации, когда Access может быть более подходящим вариантом.
| Критерий |
Excel |
Access |
| Назначение |
Создание графиков, диаграмм и различных моделей данных. |
Приложение базы данных для сбора и сортировки данных. |
| Использование |
Построение финансовых, статистических и других моделей, а также возможность проверки данных при вводе. |
Сбор, сортировка и изменение информации в базах данных. |
| Размер хранилища |
Имеет ограничения по количеству строк (1 048 576) и столбцов (16 384), а также другие ограничения. Подробнее смотреть здесь. |
Ограничения на макс. размер базы (2 ГБ) и другие. Подробнее смотреть здесь. |
| Возможность доработки |
Очень просто. Это может сделать любой пользователь Excel, конечно, если дело не касается макросов или других специфичных вещей. |
Сложно, поскольку требуются знания по построению баз данных и других особенностей. |
| Применимость |
Больше всего подходит для анализа данных (финансовых, статистических или любых других). |
Больше подходит для хранения данных. Ориентировано больше на малый бизнес. |
| Изучение |
Прост в изучении. |
Тяжелее в изучении по сравнению с Excel. |
| Реляционный или линейный |
Не реляционное хранилище данных. |
Для хранения используется реляционная модель. |
| Знание программирования |
В большинстве случаев знания программирования не нужны. |
Знания программирования обязательны для работы с базами данных Access. |
И так, Access меньше ограничен в объемах выгрузки данных и позволяет строить реляционную модель хранения информации. Кроме того, можно делать произвольные SQL-запросы к таблицам для обработки данных. Во всем остальном Excel удобнее и эффективнее. Это если смотреть общую информацию, поэтому поклонников Access прошу не идти на несанкционированный митинг в комментариях! Тем более никто не мешает подключиться к базе Access из Excel и работать в последнем.
Таким образом, когда нужно выгрузить информацию из базы 1С в большом объеме и при этом у конечного потребителя нет навороченной СУБД, или просто стандарт работы через Access, или же есть реализованная на нем бизнес-логика, то почему бы не использовать его? Также иногда нужно обойти ограничение Excel в 1 миллион строк и выгрузить данные одним набором. В последнем случае база данных Access также может быть хорошим выбором.
Конечно, вместо Excel или Access есть масса других вариантов, но в зависимости от требований они не всегда могут подходить:
- CSV-файлы - их трудно анализировать, да и подходит это больше для последующей загрузки в другой источник данных.
- База SQL Server / PostgreSQL - отличный вариант, вот только не все конечные пользователи могут позволить себе настройку этого ПО.
- Разбить выгрузку Excel на несколько файлов? Отлично! Попробуйте собрать отчетность из 15 файлов Excel по 1 миллиону записей, тогда и поговорим!
- И др.
Далее поверхностно пробежимся по вопросу лицензирования и наконец-то перейдем к разработке.
Немного про лицензии
Этот вопрос очень важный, поскольку можно здорово напортачить, от чего в будущем могут быть проблемы из-за нарушения лицензионного соглашения Microsoft на их ПО. Дело тут вот в чем.
Выгрузка или другие действия в 1С должны выполняться на стороне сервера 1С, т.к. только там мы можем работать с запросами, таблицами значений или с системой компоновки данных. Конечно, есть еще толстый клиент и файловые базы, но сейчас они не представляют особого интереса, ведь тонкий клиент более предпочтительный вариант для прикладных решений. А для файловых баз проблема лицензирования офиса вообще не стоит, т.к. обычно все кто с этой базой работают уже имеют установленный офисный пакет. Получается, что для того, чтобы сервер 1С мог работать с базами данных Access нужно выполнить одно из следующих действий на сервере 1С:
Самым простым вариантом выглядит установка полного пакета приложения MS Access на сервере, но это сильно нарушает лицензионное соглашение. В этом случае потребуется покупка лицензий на Office не только для сервера, но и клиентские лицензии для всех пользователей, которые работают с этим сервером. В нашем случае для всех, кто работает в 1С на этом сервере. А если пользователей 1000? И их становится все больше? Докупать лицензии? Это будет не дешево. В некоторых случаях лицензия может вообще запрещать такое развертывание, но на подробностях уже не будем останавливаться.
Второй вариант - это установка только ODBC-драйвера в пакете Microsoft Access Database Engine 2016 Redistributable. Да, это будет работать! Access устанавливать не нужно, а мы сможем работать с базой данных Access через ADO-соединение. Но и тут не все просто! Если перейти по ссылке и посмотреть детальную информацию, то там черным по белому написано, что этот пакет не может использоваться для:
- Для замены Ace (нас это сейчас не интересует).
- Для замены Jet OLEDB (из 1С тоже можно работать через Jet с базами Access, но это уже другая история).
- Как замена основных систем управления базами данных, электронными таблицами или документами.
- И самый главный пункт - использоваться как системная служба или программа серверной части приложения.
Фактически, этот пакет предназначен для клиентских компьютеров, которые подключаются к различным источникам данных
Третий вариант - установка Microsoft Access 2016 Runtime. Согласно информации на странице, для установки и распространения не требуется покупка дополнительных лицензий, т.к. этот пакет содержит лишь среду выполнения, которая используется для запуска уже готовых решений. Средства разработки в ней отсутствуют. При этом в состав пакета также входит установщик ODBC-драйвера, который нам и нужен.
На практике видел использование второго и третьего варианта, причем аудит от Microsoft не выявлял в этом случае никаких нарушений. Возможно, установка Microsoft Access Database Engine 2016 Redistributable формально и может являться нарушением соглашения, но по факту его никто не проверяет.
Все вышесказанное относится и к другим продуктам MS Office, в том числе и Excel, Word и т.д. Интересная информация по лицензированию есть здесь, можете прочитать там про "Access Runtime".
Нашли ошибку или не согласны что лицензирование работает именно так? Пишите в комментариях!
Простые примеры
В основном мы сосредоточимся на задачах выгрузки данных в Access. Операции загрузки также возможна, но она всегда достаточно простая и очень сильно завязана на условиях задачи (что и куда загрузить, как преобразовывать данные из базы и т.д.). Приведу лишь небольшой пример загрузки данных.
ФайлБазы = "D:\Каталог баз\ПримерБазы.accdb";
// Инициализация подключения к базе
СтрокаПодключения = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + ФайлБазы;
Connection = Новый COMОбъект("ADODB.Connection");
Connection.Open(СтрокаПодключения);
// Формируем команды чтения данных из таблицы "Выгрузка_результата_компоновки"
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;
Command.CommandText = "Select * FROM Выгрузка_результата_компоновки";
Command.CommandType = 1;
RecordSet = Command.Execute();
// Считываем все поля и выводим пользователю
ЗначенияСтрокой ="";
Пока RecordSet.EOF() = 0 Цикл
Для НомерПоля = 0 по Recordset.Fields.Count - 1 цикл
ЗначенияСтрокой = ЗначенияСтрокой + " " + Recordset.Fields(НомерПоля).Value;
КонецЦикла;
Сообщить(ЗначенияСтрокой);
ЗначенияСтрокой = "";
RecordSet.MoveNext();
КонецЦикла;
// Освобождаем ресурсы
RecordSet.Close();
Connection.Close();
Все просто - создаем соединение с базой данных Access через ADO, создаем команду чтений данных в виде набора и обрабатываем его. После освобождаем ресурсы.
С выгрузкой данных все куда интересней, потому что проблемы сложнее:
- ODBC-драйвер не позволяет создать пустую базу. Тут либо иметь уже готовый файл с нужными таблицами или придумывать альтернативный вариант (что и было сделано, но об этом позже).
- При выгрузке всегда одного набора данных задача упрощается, но что если нужно выгружать каждый раз разные наборы. Например, нужно выгружать отчет на СКД и учитывать, что настройки (выводимые поля) пользователь может изменять. То есть нам нужно научиться создавать таблицы в Access, сопоставляя типы 1С с доступными типами MS Access.
- Нужно учесть ограничения базы Access на различные типы, а также на сам ODBC-драйвер. Например, что выгрузка в Access не поддерживает даты меньше 1753 года, если используется ODBC-драйвер.
- Также необходимо учитывать максимальный размер базы данных в 2 ГБ. Тут в качестве решения может быть настройка максимального количества выгружаемых записей для одной базы. В случае необходимости выгрузка будет выполняться в несколько порций.
То есть на практике нужно быть готовым к выгрузке произвольных наборов данных. которые в 1С могут быть представлены таблицей значений, запросов или результатом компоновки данных отчета. Если бы этих проблем не стояло, то алгоритм выгрузки был бы примерно такой.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| &ТекущаяДата КАК Дата,
| ""Привет из Access"" КАК Строка";
Запрос.УстановитьПараметр("ТекущаяДата", ТекущаяДата());
ТаблицаИсточник = Запрос.Выполнить().Выгрузить();
// Подготовленная база Access для выгрузки
ПутьКБазе = "C:\Access\ПростаяВыгрузка.accdb";
СтрокаПодключения = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + ПутьКБазе;
СоединениеКБазе = Новый COMОбъект("ADODB.Connection");
СоединениеКБазе.Open(СтрокаПодключения);
ИмяТаблицы = "ПростаяВыгрузка";
Запись = Новый COMОбъект("ADODB.RecordSet");
ТекстЗапроса = "SELECT * FROM " + ИмяТаблицы;
Запись.Open(
// Текст запроса
ТекстЗапроса,
// Соединение с базой
СоединениеКБазе,
// Указывает тип курсора, используемого в записей объекта.
// CursorType (https://docs.microsoft.com/ru-ru/sql/ado/reference/ado-api/cursortypeenum?view=sql-server-2017)
// 1 = adOpenKeyset. Использует курсор набора ключей.
1,
// Тип блокировки
// LockTypeEnum (https://docs.microsoft.com/ru-ru/sql/ado/reference/ado-api/open-method-ado-recordset?view=sql-server-2017)
// 3 = adLockOptimistic (Указывает, оптимистической блокировки, записей.)
3
);
// Добавляем записи в таблицу базы Access
// В исходном файле первая колонка содержит дату,
// а во второй сохраняем строку.
Для Каждого СтрокаТаблицы Из ТаблицаИсточник Цикл
Запись.AddNew();
Запись.Fields(0).Value = СтрокаТаблицы.Дата;
Запись.Fields(1).Value = СтрокаТаблицы.Строка;
Запись.UpDate();
КонецЦикла;
СоединениеКБазе.Close();
СоединениеКБазе = Неопределено;
Но вернемся все же к реальным требованиям. Для упрощения разработки и сопровождения сделаем прокси-функцию, которая позволит выгружать в Access таблицы значений, запросы и результаты СКД.
Прокси, прокси, прокси
Начнем с описания что в итоге нужно получить. Интерфейс выгрузки будет представлен тремя функциями:
- ВыгрузитьТаблицуЗначений(КаталогВыгрузки, ИсточникДанных)
- ВыгрузитьЗапрос(КаталогВыгрузки, ИсточникДанных)
- ВыгрузитьРезультатКомпоновки(КаталогВыгрузки, СхемаКомпоновкиДанных, Настройки)
Примеры работы с ними ниже под спойлером.
Примеры выгрузки в Access
Все функции находятся в общем серверном модуле "РаботаСAccessСервер" и возвращают структуру результата выгрузки с количеством выгруженных записей.
Выгрузка таблицы значений
&НаСервере
Функция ВыгрузкаВТаблицуЗначенийНаСервере()
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Значения_1.ПолеЧисло КАК ПолеЧисло,
| Значения_1.ПолеСтрока КАК ПолеСтрока,
| ИСТИНА КАК Булево,
| ДАТАВРЕМЯ(2018, 12, 31) КАК Дата
|ПОМЕСТИТЬ Значения_1
|ИЗ
| (ВЫБРАТЬ
| 1 КАК ПолеЧисло,
| ""Тест 1"" КАК ПолеСтрока
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
| 2,
| ""Тест 2""
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
| 3,
| ""Тест 3"") КАК Значения_1
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Значения_2.ПолеЧисло КАК ПолеЧисло,
| Значения_2.ПолеСтрока КАК ПолеСтрока,
| ЛОЖЬ КАК Булево,
| ДАТАВРЕМЯ(1, 1, 1) КАК Дата
|ПОМЕСТИТЬ Значения_2
|ИЗ
| (ВЫБРАТЬ
| 1 КАК ПолеЧисло,
| ""Тест 1"" КАК ПолеСтрока
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
| 2,
| ""Тест 2""
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
| 3,
| ""Тест 3"") КАК Значения_2
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Значения_1.ПолеСтрока КАК FieldTwo,
| Значения_2.ПолеСтрока КАК FieldFour,
| Значения_1.Булево КАК BoolT,
| Значения_2.Булево КАК BoolF,
| Значения_1.Дата КАК DateNormal,
| Значения_2.Дата КАК DateEmpty,
| ДАТАВРЕМЯ(1753, 1, 1) КАК DateTooSmall,
| ДАТАВРЕМЯ(100, 1, 1) КАК DateError,
| ДАТАВРЕМЯ(3999, 12, 31) КАК DateTooBig,
| ВЫРАЗИТЬ(1 КАК ЧИСЛО(5, 0)) КАК SimpleNumber,
| 1923.55 + 65.04 КАК ComplexNumber,
| ""You can actually use the Decimal data type in CREATE TABLE queries. However, this requires your statement to be executed either using ADO, or on a database that's been set to use ANSI-92 compatible syntax. To set your database to ANSI-92 compatible syntax: Go to File -> Options. Open the tab Object Designers. Go to Query Designer, and under SQL Server Compatible Syntax (ANSI 92), check This Database. Now you can just execute the query. Note that this affects all queries in the database, and affects queries in various ways. To execute a query using ADO:"" КАК LongString
|ИЗ
| Значения_1 КАК Значения_1,
| Значения_2 КАК Значения_2";
ТаблицаВыгрузки = Запрос.Выполнить().Выгрузить();
КаталогВыгрузки = КаталогВременныхФайлов() + "ТестоваяВыгрузка";
СоздатьКаталог(КаталогВыгрузки);
РезультатВыгрузки =
РаботаСAccessСервер.ВыгрузитьТаблицуЗначений(
КаталогВыгрузки,
ТаблицаВыгрузки,
,
РаботаСAccessКлиентСервер.ПолучитьОграниченияВыгрузкиБазы(,3));
ИмяТаблицы = "ВыгрузкаТаблицыЗначений";
Коллекциябаз = НайтиФайлы(КаталогВыгрузки, "*.accdb");
ПутьКАрхиву = КаталогВыгрузки + "\" + Формат(ТекущаяДата(), "ДФ=yyyyMMdd") + "_ВыгрузкаДанных_" + ИмяТаблицы + ".zip";
Архив = Новый ЗаписьZipФайла(ПутьКАрхиву,, "Выгрузка данных в Access """ + ИмяТаблицы + """");
Для Каждого ФайлБазы Из Коллекциябаз Цикл
Архив.Добавить(ФайлБазы.ПолноеИмя, РежимСохраненияПутейZIP.НеСохранятьПути);
КонецЦикла;
Архив.Записать();
Возврат Новый ДвоичныеДанные(ПутьКАрхиву);
КонецФункции
2. Пример для запроса.
&НаСервере
Функция ВыгрузитьЗапросНаСервере()
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Значения_1.ПолеЧисло КАК ПолеЧисло,
| Значения_1.ПолеСтрока КАК ПолеСтрока,
| ИСТИНА КАК Булево,
| ДАТАВРЕМЯ(2018, 12, 31) КАК Дата
|ПОМЕСТИТЬ Значения_1
|ИЗ
| (ВЫБРАТЬ
| 1 КАК ПолеЧисло,
| ""Тест 1"" КАК ПолеСтрока
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
| 2,
| ""Тест 2""
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
| 3,
| ""Тест 3"") КАК Значения_1
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Значения_2.ПолеЧисло КАК ПолеЧисло,
| Значения_2.ПолеСтрока КАК ПолеСтрока,
| ЛОЖЬ КАК Булево,
| ДАТАВРЕМЯ(1, 1, 1) КАК Дата
|ПОМЕСТИТЬ Значения_2
|ИЗ
| (ВЫБРАТЬ
| 1 КАК ПолеЧисло,
| ""Тест 1"" КАК ПолеСтрока
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
| 2,
| ""Тест 2""
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
| 3,
| ""Тест 3"") КАК Значения_2
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Значения_1.ПолеСтрока КАК FieldTwo,
| Значения_2.ПолеСтрока КАК FieldFour,
| Значения_1.Булево КАК BoolT,
| Значения_2.Булево КАК BoolF,
| Значения_1.Дата КАК DateNormal,
| Значения_2.Дата КАК DateEmpty,
| ДАТАВРЕМЯ(1753, 1, 1) КАК DateTooSmall,
| ДАТАВРЕМЯ(100, 1, 1) КАК DateError,
| ДАТАВРЕМЯ(3999, 12, 31) КАК DateTooBig,
| ВЫРАЗИТЬ(1 КАК ЧИСЛО(5, 0)) КАК SimpleNumber,
| 1923.55 + 65.04 КАК ComplexNumber,
| ""You can actually use the Decimal data type in CREATE TABLE queries. However, this requires your statement to be executed either using ADO, or on a database that's been set to use ANSI-92 compatible syntax. To set your database to ANSI-92 compatible syntax: Go to File -> Options. Open the tab Object Designers. Go to Query Designer, and under SQL Server Compatible Syntax (ANSI 92), check This Database. Now you can just execute the query. Note that this affects all queries in the database, and affects queries in various ways. To execute a query using ADO:"" КАК LongString
|ИЗ
| Значения_1 КАК Значения_1,
| Значения_2 КАК Значения_2";
КаталогВыгрузки = КаталогВременныхФайлов() + "ТестоваяВыгрузка";
СоздатьКаталог(КаталогВыгрузки);
РезультатВыгрузки =
РаботаСAccessСервер.ВыгрузитьЗапрос(
КаталогВыгрузки,
Запрос,
,
РаботаСAccessКлиентСервер.ПолучитьОграниченияВыгрузкиБазы(,3));
ИмяТаблицы = "ВыгрузкаТаблицыЗначений";
Коллекциябаз = НайтиФайлы(КаталогВыгрузки, "*.accdb");
ПутьКАрхиву = КаталогВыгрузки + "\" + Формат(ТекущаяДата(), "ДФ=yyyyMMdd") + "_ВыгрузкаДанных_" + ИмяТаблицы + ".zip";
Архив = Новый ЗаписьZipФайла(ПутьКАрхиву,, "Выгрузка данных в Access """ + ИмяТаблицы + """");
Для Каждого ФайлБазы Из Коллекциябаз Цикл
Архив.Добавить(ФайлБазы.ПолноеИмя, РежимСохраненияПутейZIP.НеСохранятьПути);
КонецЦикла;
Архив.Записать();
Возврат Новый ДвоичныеДанные(ПутьКАрхиву);
КонецФункции
3. Пример для результата компоновки данных.
Выгрузить результат компоновки данных
&НаСервере
Функция ВыгрузитьВAccessНаСервере()
ОтчетНаСервере = РеквизитФормыВЗначение("Отчет");
КаталогВыгрузки = КаталогВременныхФайлов() + "ВыгрузкаИзСКДвAccess";
СоздатьКаталог(КаталогВыгрузки);
РезультатВыгрузки = РаботаСAccessСервер.ВыгрузитьРезультатКомпоновки(
КаталогВыгрузки,
ОтчетНаСервере.ПолучитьМакет("ОсновнаяСхемаКомпоновкиДанных"),
Отчет.КомпоновщикНастроек.ПолучитьНастройки(),
СтрЗаменить(ЭтаФорма.ПредставлениеТекущегоВарианта, " ", "_"),
РаботаСAccessКлиентСервер.ПолучитьОграниченияВыгрузкиБазы(,2,)
);
ИмяТаблицы = "ВыгрузкаОтчетаСКД";
Коллекциябаз = НайтиФайлы(КаталогВыгрузки, "*.accdb");
ПутьКАрхиву = КаталогВыгрузки + "\" + Формат(ТекущаяДата(), "ДФ=yyyyMMdd") + "_ВыгрузкаДанных_" + ИмяТаблицы + ".zip";
Архив = Новый ЗаписьZipФайла(ПутьКАрхиву,, "Выгрузка данных в Access """ + ИмяТаблицы + """");
Для Каждого ФайлБазы Из Коллекциябаз Цикл
Архив.Добавить(ФайлБазы.ПолноеИмя, РежимСохраненияПутейZIP.НеСохранятьПути);
КонецЦикла;
Архив.Записать();
Возврат Новый ДвоичныеДанные(ПутьКАрхиву);
КонецФункции
Все примеры возвращают двоичные данные архива, в котором находятся все сформированные файлы базы данных Access.
Функция "РаботаСAccessКлиентСервер.ПолучитьОграниченияВыгрузкиБазы" возвращает структуру параметров выгрузки.
Функция ПолучитьОграниченияВыгрузкиБазы(
МаксимальныйРазмерМБ = 1536,
МаксимальноеКоличествоЗаписей = 5000000,
ПорцияВыгрузкиДляПроверкиРазмераБазы = 10000) Экспорт
СтруктураОграничений = Новый Структура;
СтруктураОграничений.Вставить("МаксимальныйРазмерМБ", МаксимальныйРазмерМБ);
СтруктураОграничений.Вставить("МаксимальноеКоличествоЗаписей", МаксимальноеКоличествоЗаписей);
СтруктураОграничений.Вставить("ПорцияВыгрузкиДляПроверкиРазмераБазы", ПорцияВыгрузкиДляПроверкиРазмераБазы);
Возврат СтруктураОграничений;
КонецФункции
При выгрузке таблицы значений и запроса мы указали, что максимальное количество записей в одной базе равно 3. А для выгрузки результата компоновки данных макс. количество записей в базе указали 2.
Но как были решены все те проблемы, о которых говорилось выше? Ответим по порядку!
Создать базу данных можно двумя способами:
- Если на клиенте есть установленный MS Access, то базу можно создать с его помощью, в т.ч. и инициализировать нужные таблицы. Так мы получим шаблон базы данных нужной структуры, а позже его уже можно передать на сервер и работать с ним с помощью ODBC.
- Другой вариант - можно создать пустой файл базы данных Access без таблиц и сохранить его, например, в общий макет. На сервере для создания базы данных используем данные макета, а создание таблиц выполняем с помощью ODBC.
Инициализация базы данных Access
Примерно так выглядит инициализация базы данных с помощью клиентского приложения MS Access.
СтрокаПодключения = РаботаСAccessКлиентСервер.ПолучитьСтрокуПодключенияJetOLEDB(ПутьКБазе);
Catalog = Новый COMОбъект("ADOX.Catalog");
Catalog.ActiveConnection = СтрокаПодключения;
Table = Новый COMОбъект("ADOX.Table");
Table.Name = ОписаниеТаблицы.ИмяТаблицы;
Для Каждого ОписаниеПоля Из ОписаниеТаблицы.ОписаниеПолей Цикл
Column = Новый COMОбъект("ADOX.Column");
Column.Name = ОписаниеПоля.Имя;
Column.Type = ОписаниеПоля.Тип;
Если ОписаниеПоля.ТипЗначения = Тип("Число") Тогда
Если ЗначениеЗаполнено(ОписаниеПоля.Длина) Тогда
Column.NumericScale = ОписаниеПоля.Длина;
КонецЕсли;
Если ЗначениеЗаполнено(ОписаниеПоля.ДлинаДробнойЧасти) Тогда
Column.Precision = ОписаниеПоля.ДлинаДробнойЧасти;
КонецЕсли;
Column.Attributes = 2; // Доступно значение NULL
ИначеЕсли ОписаниеПоля.ТипЗначения = Тип("Строка") Тогда
Если ЗначениеЗаполнено(ОписаниеПоля.Длина) Тогда
Column.DefinedSize = ОписаниеПоля.Длина;
КонецЕсли;
Column.Attributes = 2; // Доступно значение NULL
ИначеЕсли ОписаниеПоля.ТипЗначения = Тип("Дата") Тогда
Column.Attributes = 2; // Доступно значение NULL
КонецЕсли;
Table.Columns.Append(Column);
КонецЦикла;
Catalog.Tables.Append(Table);
В случае, если таблицу нужно создать средствами ADO через ODBC-драйвер, то инициализация таблицы выглядит уже немного иначе.
ЗакрытьСоединение = Ложь;
Если Соединение = Неопределено Тогда
Соединение = РаботаСAccessКлиентСервер.ПолучитьСоединениеADO(ПутьКБазе);
ЗакрытьСоединение = Истина;
КонецЕсли;
Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
ТекстЗапроса =
"CREATE TABLE " + ОписаниеТаблицы.ИмяТаблицы + "
|(";
НомерПоля = 1;
ВсегоПолей = ОписаниеТаблицы.ОписаниеПолей.Количество();
Для Каждого ПолеТаблицы Из ОписаниеТаблицы.ОписаниеПолей Цикл
ЭтоПоследнееПоле = (НомерПоля = ВсегоПолей);
ТекстЗапроса = ТекстЗапроса + "
| " + ПолеТаблицы.Имя + " " + ПолеТаблицы.ИмяТипаЗначения + "" + ?(ЭтоПоследнееПоле, "", ",");
НомерПоля = НомерПоля + 1;
КонецЦикла;
ТекстЗапроса = ТекстЗапроса + "
|)";
Команда.CommandText = ТекстЗапроса;
Попытка
Команда.Execute();
Исключение
СообщениеОбОшибке =
"Произошла ошибка при создании таблицы.
|
|Подробности:
|" + ОписаниеОшибки();
РаботаСAccessВызовСервера.ЗаписатьОшибкуВЛог(СообщениеОбОшибке);
ВызватьИсключение СообщениеОбОшибке;
КонецПопытки;
Если ЗакрытьСоединение Тогда
РаботаСAccessКлиентСервер.ЗакрытьСоединениеADO(Соединение);
КонецЕсли;
Остается вопрос по поводу описания полей таблицы (типы, ограничения и т.д.), но об этом ниже.
Но что на счет описания типов полей таблицы и как его получить для типов платформы 1С? В этом случае нужно обрабатывать несколько ситуаций:
- Тип значения поля составной.
- Тип значения "Строка" как с указанной длиной, так и неограниченная.
- Числовой тип разной длины и разрядности.
- Тип булево.
- Тип дата.
- Ссылочные типы.
- Остальные типы, включая служебные.
Общее соответствие типов можно сделать такое.
| Тип 1С |
Тип Access |
| Составной тип (может включать примитивные типы и ссылочные) |
CHAR(255), в случае превышения длины строки значение будет обрезаться. |
| Строка |
CHAR(<Размер>). Размер определяется длиной строки в описании типа (макс. длина 255). Если это значение - представление какой-либо ссылки, но нужно получить длину поля представления (код, наименование) и присвоить его длине строки в Access. |
| Число |
Если у числа явно указано, что оно может быть только целым, то используем "INTEGER". В остальных случаях "DOUBLE" с различной длиной и разрядностью дробной части. |
| Булево |
BIT |
| Дата |
DATETIME |
| Ссылочные и другие типы |
CHAR(<Размер>). Длину указываем как 150 символов по умолчанию. |
Получение описания полей Access для таблицы значений 1С
В качестве таблицы данных лучше всего передавать пустую таблицу значений, в которой инициализированы колонки с необходимыми типами данных. Например, если нужно определить структуру описания полей для таблицы значений, то вместо передачи всей таблицы использовать такой способ.
// ИсточникДанных - таблица значений для выгрузки.
ИсточникОписанияСтруктурыТаблицы = ИсточникДанных.СкопироватьКолонки();
ОписаниеСтруктурыТаблицы = РаботаСAccessВызовСервера.СоставлениеСтруктурыОписанияПолей(ИсточникОписанияСтруктурыТаблицы, ИмяТаблицы);
Сама функция получения описания структуры полей таблицы в Access может быть такой.
Функция СоставлениеСтруктурыОписанияПолей(Знач ТаблицаДанных, Знач ИмяТаблицы) Экспорт
СтруктураОписания = Новый Структура("ИмяТаблицы,ОписаниеПолей");
СтруктураОписания.ИмяТаблицы = ИмяТаблицы;
СтруктураОписания.ОписаниеПолей = Новый Массив;
ОбщийРазмерЗаписиБайт = 0;
ИндексКолонки = 0;
Для Каждого КолонкаТЗ Из ТаблицаДанных.Колонки Цикл
СтруктураСвойстПоля = Новый Структура("Имя,Тип,Длина, ДлинаДробнойЧасти, Синоним, ТипЗначения, ИндексКолонки, ИмяТипаЗначения, ДлинаБайт");
ТипЗначенияКолонки = КолонкаТЗ.ТипЗначения;
ДлинаСтроки = ТипЗначенияКолонки.КвалификаторыСтроки.Длина;
Разрядность = ТипЗначенияКолонки.КвалификаторыЧисла.Разрядность;
РазрядностьДробнойЧасти = ТипЗначенияКолонки.КвалификаторыЧисла.РазрядностьДробнойЧасти;
ДлинаСтроки = КолонкаТЗ.ТипЗначения.КвалификаторыСтроки.Длина;
СтруктураСвойстПоля.ДлинаБайт = 0;
Если ТипЗначенияКолонки.Типы().Количество() > 2 Тогда
СтруктураСвойстПоля.Имя = КолонкаТЗ.Имя;
СтруктураСвойстПоля.Тип = "202";//adVarWChar, type 202 [строка в Юникоде длиной в 255 символов (DT_WSTR)]
СтруктураСвойстПоля.Длина = 255;
СтруктураСвойстПоля.Синоним = КолонкаТЗ.Заголовок;
СтруктураСвойстПоля.ИмяТипаЗначения = "CHAR(" + XMLСтрока(СтруктураСвойстПоля.Длина) + ")";
СтруктураСвойстПоля.ТипЗначения = Тип("Строка");
СтруктураСвойстПоля.ДлинаБайт = 255 + 10;
ИначеЕсли ТипЗначенияКолонки.СодержитТип(Тип("Строка")) Тогда
СтруктураСвойстПоля.ТипЗначения = Тип("Строка");
СтруктураСвойстПоля.Имя = КолонкаТЗ.Имя;
СтруктураСвойстПоля.Синоним = КолонкаТЗ.Заголовок;
ДополнительныйТип = Неопределено;
ТипыПоля = ТипЗначенияКолонки.Типы();
Если ТипыПоля.Количество() > 1 Тогда
Для Каждого ТипПоля Из ТипыПоля Цикл
Если ТипПоля <> Тип("Строка") Тогда
ДополнительныйТип = ТипПоля;
КонецЕсли;
КонецЦикла;
КонецЕсли;
Если ДлинаСтроки = 0 И НЕ ДополнительныйТип = Неопределено Тогда
ДлинаСтроки = РаботаСAccessВызовСервера.ПолучитьДлинуПредставленияСсылочнгоТипа(ДополнительныйТип);
КонецЕсли;
Если ДлинаСтроки = 0 ИЛИ ДлинаСтроки >= 250 Тогда
СтруктураСвойстПоля.Тип = "202";
СтруктураСвойстПоля.Длина = 250;
СтруктураСвойстПоля.ДлинаБайт = СтруктураСвойстПоля.Длина + 10;
СтруктураСвойстПоля.ИмяТипаЗначения = "CHAR(" + XMLСтрока(СтруктураСвойстПоля.Длина) + ")";
Иначе
СтруктураСвойстПоля.Тип = "202";
СтруктураСвойстПоля.Длина = ДлинаСтроки;
СтруктураСвойстПоля.ИмяТипаЗначения = "CHAR(" + XMLСтрока(СтруктураСвойстПоля.Длина) + ")";
СтруктураСвойстПоля.ДлинаБайт = СтруктураСвойстПоля.Длина + 10;
КонецЕсли;
ИначеЕсли ТипЗначенияКолонки.СодержитТип(Тип("Число")) Тогда
СтруктураСвойстПоля.Имя = КолонкаТЗ.Имя;
СтруктураСвойстПоля.Синоним = КолонкаТЗ.Заголовок;
СтруктураСвойстПоля.ТипЗначения = Тип("Число");
Если Разрядность = 0 ИЛИ РазрядностьДробнойЧасти = 0 Тогда
СтруктураСвойстПоля.Тип = "5";
СтруктураСвойстПоля.Длина = 15;
СтруктураСвойстПоля.ДлинаДробнойЧасти = 2;
СтруктураСвойстПоля.ДлинаБайт = 8;
СтруктураСвойстПоля.ИмяТипаЗначения = "DOUBLE";
ИначеЕсли Разрядность = 0 И РазрядностьДробнойЧасти > 0 Тогда
СтруктураСвойстПоля.Тип = "5";
СтруктураСвойстПоля.Длина = 1;
СтруктураСвойстПоля.ДлинаДробнойЧасти = РазрядностьДробнойЧасти;
СтруктураСвойстПоля.ДлинаБайт = 8;
СтруктураСвойстПоля.ИмяТипаЗначения = "DOUBLE";
ИначеЕсли РазрядностьДробнойЧасти > 0 И Разрядность > 0 Тогда
СтруктураСвойстПоля.Тип = "5";
СтруктураСвойстПоля.Длина = Разрядность;
СтруктураСвойстПоля.ДлинаДробнойЧасти = РазрядностьДробнойЧасти;
СтруктураСвойстПоля.ДлинаБайт = 8;
СтруктураСвойстПоля.ИмяТипаЗначения = "DOUBLE";
Иначе
СтруктураСвойстПоля.Тип = "3";
СтруктураСвойстПоля.Длина = Разрядность;
СтруктураСвойстПоля.ДлинаДробнойЧасти = 0;
СтруктураСвойстПоля.ДлинаБайт = 2;
СтруктураСвойстПоля.ИмяТипаЗначения = "INTEGER";
КонецЕсли;
ИначеЕсли ТипЗначенияКолонки.СодержитТип(Тип("Булево")) Тогда
СтруктураСвойстПоля.ТипЗначения = Тип("Булево");
СтруктураСвойстПоля.Имя = КолонкаТЗ.Имя;
СтруктураСвойстПоля.Тип = "11";
СтруктураСвойстПоля.Длина = Неопределено;
СтруктураСвойстПоля.Синоним = КолонкаТЗ.Заголовок;
СтруктураСвойстПоля.ДлинаБайт = 2;
СтруктураСвойстПоля.ИмяТипаЗначения = "BIT";
ИначеЕсли ТипЗначенияКолонки.СодержитТип(Тип("Дата")) Тогда
СтруктураСвойстПоля.ТипЗначения = Тип("Дата");
СтруктураСвойстПоля.Имя = КолонкаТЗ.Имя;
СтруктураСвойстПоля.Тип = "7";
СтруктураСвойстПоля.Длина = Неопределено;
СтруктураСвойстПоля.Синоним = КолонкаТЗ.Заголовок;
СтруктураСвойстПоля.ДлинаБайт = 8;
СтруктураСвойстПоля.ИмяТипаЗначения = "DATETIME";
Иначе
СтруктураСвойстПоля.ТипЗначения = Тип("Строка");
СтруктураСвойстПоля.Имя = КолонкаТЗ.Имя;
СтруктураСвойстПоля.Тип = "202";
СтруктураСвойстПоля.Длина = 150;
СтруктураСвойстПоля.Синоним = КолонкаТЗ.Заголовок;
СтруктураСвойстПоля.ДлинаБайт = СтруктураСвойстПоля.Длина + 10;
СтруктураСвойстПоля.ИмяТипаЗначения = "CHAR(" + XMLСтрока(СтруктураСвойстПоля.Длина) + ")";
КонецЕсли;
СтруктураСвойстПоля.ИндексКолонки = ИндексКолонки;
СтруктураОписания.ОписаниеПолей.Добавить(СтруктураСвойстПоля);
ОбщийРазмерЗаписиБайт = ОбщийРазмерЗаписиБайт + СтруктураСвойстПоля.ДлинаБайт;
ИндексКолонки = ИндексКолонки + 1;
КонецЦикла;
Возврат СтруктураОписания;
КонецФункции
Полученное описание структуры полей может быть не универсальным, но большинство потребностей при выгрузке в Access закрывает.
По поводу проверки на ограничения типов, например для дат, можно использовать такие функции.
Проверка даты на корректность
Проверяем, можно ли выгрузить значение даты через ADO.
Функция ЭтоКорректнаяДата(ЗначениеДаты) Экспорт
Если ЗначениеЗаполнено(ЗначениеДаты)
И ЗначениеДаты < Дата(1753,1,1) Тогда
Возврат Ложь;
ИначеЕсли НЕ ТипЗнч(ЗначениеДаты) = Тип("Дата") Тогда
Возврат Ложь;
Иначе
Возврат Истина;
КонецЕсли;
КонецФункции
Если значение даты не может быть выгружено, то можно присваивать значение NULL.
Ограничение базы данных Access на 2 Гб можно обойти, разбивая ее на несколько файлов. Тут либо разбивать по размеру базы, каждый раз проверяя текущий размер файла базы, либо задав ограничение на количество записей.
Полная реализация выгрузки с примерами доступна в репозитории на GitHub - "1C-Plus-MSOffice", где постепенно будут добавляться различные примеры, алгоритмы и инструкции по работе с продуктами MS Office из 1С. Планируется добавить такой функционал и инструкции:
- Выгрузка в Excel и Word на сервере без использования MS Office.
- Описание штатных возможностей платформы для выгрузки в Excel.
- Оптимизация выгрузки больших массивов данных в Excel (минимальное использование памяти и др.).
- Описание формата файлов документов Office.
- Использование Outlook и шифрования в нем.
- И др.
Содержимое репозитория не является готовым решением, а лишь помогает, объясняет и ускоряет разработку. Весь материал лишь признан помогать, а не выполнять работу за Вас (разработчиков).
Замечания, предложения, issues и pull-request'ы приветствуются!
После всего
Мы рассмотрели использование Access для выгрузки данных из баз 1С, некоторые нюансы лицензирования и небольшие примеры выгрузки и загрузки данных. Также продемонстрированы прокси-функции для выгрузки таких типов данных 1С как таблица значений, запрос и результат компоновки данных. Смотрите в репозиторий "1C-Plus-MSOffice", там будут добавляться новые функции, инструкции и различные фичи при работе с приложениями MS Office.
Спасибо, что что Вы все еще здесь! :)
Удачи!
Другие ссылки
