1с як зберегти табличний документ у excel. Хитрості збереження табличного документа Excel для подальшої роботи з ним. Вивантаження без програмування

Для подальшої роботи з даними, отриманими в табличному документі із системи 1С будь-якої версії, їх можна зберегти у форматі xls(x).

Все б добре, але при відкритті в Excel-e користувачвідразу розуміє, що з таблицею щось не те, ну так, немає ярликів аркушів книги. %)

Так було і в 7-й версії 1С і перекочувало до 8-ї, так, є прогрес, у 8-ці можна зберегти у форматі xlsx, рідному форматі останніх версій Excel-я.

Для вирішення завдання є кілька нескладних маніпуляцій, перше, що спадає на думку будь-якому користувачеві, так це перезберегти таблицю в інший формат, але і тут немає потрібного результату, потім починаємо робити копі-паст у нову книгу Excel.
Є ще варіант, як би для просунутих користувачів, - це зайти в налаштування Excel і в розділі Додатково поставити галочку "Показувати ярлики листів". Він є правильним, але теж є рутинним, у світлі частої обробки даних, отриманих з 1С у вигляді плоских таблиць і подальшого аналізу даних у вигляді зведених таблицьта інших способів представлення даних у Excel-і.

Сам так само замаявся бігати по галочках і вирішив завдання, порившись у просторах інтернетів, наприклад, і подивившись деякі інші розробки, вдалося реалізувати нескладний (для когось це баян, комусь милиця, втім, про смаки не сперечаються) механізм автоматизації вирішення проблеми з відображенням ярликів аркушів.

Все, що потрібно, так це використовувати дві властивості вікна Excel:

DisplayWorkbookTabs // ознака відображення закладок аркушів книги TabRatio // співвідношення ширини області закладок робочої книги до ширини горизонтальної смуги прокручування вікна (як число між 0 (нуль) та 1, значення за замовчуванням 0.6)

ТабДокумент.Записати(ПовнеІм'яФайлу, ТипФайлуТабличногоДокумента.XLS); Excel = Новий COMОб'єкт ("Excel.Application"); Excel.WorkBooks.Open (Повне Ім'я Файлу); Excel.Visible = 0; Excel.ActiveWindow.DisplayWorkbookTabs = 1; Excel.ActiveWindow.TabRatio = 0.6; FullName = Excel.ActiveWorkbook.FullName; Excel.DisplayAlerts = false; Excel.ActiveWorkbook.SaveAs(FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013 // Excel.Visible = 1; // якщо потрібно попрацювати далі з книгою
//Excel.Application.Quit() // якщо просто виходимо

ТабДокумент.Записати(ПовнеІм'яФайлу, "XLS"); Excel = Створити Об'єкт ("Excel.Application"); Excel.WorkBooks.Open (Повне Ім'я Файлу); Excel.Visible = 0; Excel.ActiveWindow.DisplayWorkbookTabs = 1; Excel.ActiveWindow.TabRatio = 0.6; FullName = Excel.ActiveWorkbook.FullName; Excel.DisplayAlerts = false; Excel.ActiveWorkbook.SaveAs(FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013 // Excel.Visible = 1; // якщо потрібно попрацювати далі з книгою
//Excel.Application.Quit() // якщо просто виходимо

Для подальшої роботи з даними, отриманими в табличному документі із системи 1С будь-якої версії, їх можна зберегти у форматі xls(x).

Все добре, але при відкритті в Excel-e користувач відразу розуміє, що з таблицею щось не те, ну так, немає ярликів листів книги. %)

Так було і в 7-й версії 1С і перекочувало до 8-ї, так, є прогрес, у 8-ці можна зберегти у форматі xlsx, рідний формат останніх версій Excel-я.

Для вирішення завдання є кілька нескладних маніпуляцій, перше, що спадає на думку будь-якому користувачеві, так це перезберегти таблицю в інший формат, але і тут немає потрібного результату, потім починаємо робити копі-паст у нову книгу Excel.
Є ще варіант, як би для просунутих користувачів, - це зайти в налаштування Excel і в розділі Додатково поставити галочку "Показувати ярлики листів". Він є правильним, але теж є рутинним, у світлі частої обробки даних, отриманих з 1С у вигляді плоских таблиць і подальшого аналізу даних у вигляді зведених таблиць та інших способів подання даних в Excel-і.

Сам так само замаявся бігати по галочках і вирішив завдання, порившись у просторах інтернетів, наприклад, і подивившись деякі інші розробки, вдалося реалізувати нескладний (для когось це баян, комусь милиця, втім, про смаки не сперечаються) механізм автоматизації вирішення проблеми з відображенням ярликів аркушів.

Все, що потрібно, так це використовувати дві властивості вікна Excel:

DisplayWorkbookTabs // ознака відображення закладок аркушів книги TabRatio // співвідношення ширини області закладок робочої книги до ширини горизонтальної смуги прокручування вікна (як число між 0 (нуль) та 1, значення за замовчуванням 0.6)

ТабДокумент.Записати(ПовнеІм'яФайлу, ТипФайлуТабличногоДокумента.XLS); Excel = Новий COMОб'єкт ("Excel.Application"); Excel.WorkBooks.Open (Повне Ім'я Файлу); Excel.Visible = 0; Excel.ActiveWindow.DisplayWorkbookTabs = 1; Excel.ActiveWindow.TabRatio = 0.6; FullName = Excel.ActiveWorkbook.FullName; Excel.DisplayAlerts = false; Excel.ActiveWorkbook.SaveAs(FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013 // Excel.Visible = 1; // якщо потрібно попрацювати далі з книгою
//Excel.Application.Quit() // якщо просто виходимо

ТабДокумент.Записати(ПовнеІм'яФайлу, "XLS"); Excel = Створити Об'єкт ("Excel.Application"); Excel.WorkBooks.Open (Повне Ім'я Файлу); Excel.Visible = 0; Excel.ActiveWindow.DisplayWorkbookTabs = 1; Excel.ActiveWindow.TabRatio = 0.6; FullName = Excel.ActiveWorkbook.FullName; Excel.DisplayAlerts = false; Excel.ActiveWorkbook.SaveAs(FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013 // Excel.Visible = 1; // якщо потрібно попрацювати далі з книгою
//Excel.Application.Quit() // якщо просто виходимо

Цей спосіб простий. Його суть полягає в тому, що об'єкт ТабличнийДокументмає методи:

• Записати (< ИмяФайла>, < ТипФайлаТаблицы >) для вивантаження даних у файл;
• Прочитати (< ИмяФайла>, < СпособЧтенияЗначений >) для завантаження даних із файлу.

Увага!

Метод Записати () доступний як на клієнті, так і на сервері. Метод Прочитати () доступний лише на стороні сервера. Необхідно пам'ятати про це
при плануванні клієнт-серверної взаємодії.

Розглянемо приклад збереження табличного документау файл. Необхідно будь-яким способом створити та заповнити об'єкт ТабличнийДокумент, а вивантаженняу файл здійснюється лише одним рядком:

ТабДок . Записати(ШляхКФайлу, ТипФайлуТабличногоДокументу. 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:

Для того, щоб дізнатися, яку властивість потрібно змінювати або який метод викликати можна скористатися макросами 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;
• Параметри;
• Properties.

Вивантаження без програмування

Для збереження даних з 1С Excel не завжди доцільно вдаватися до програмування. Якщо в режимі Підприємства користувач може відобразити потрібні для вивантаження дані, їх можна зберегти в Excel без програмування.

Для збереження табличного документа (наприклад, результату звіту) можна викликати команду Зберегтиабо Зберегти як…головне меню.

У вікні потрібно вибрати каталог, ім'я і формат файлу, що зберігається.

Для збереження даних динамічних списків (наприклад, списку номенклатури) необхідно:

1. Вивести дані до табличного документа за допомогою команди Ще ⇒ Вивести список…;
2. Зберегти табличний документ у потрібний формат.