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

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

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

Увага!

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

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

ТабДок . Записати (ПутьКФайлу, ТіпФайлаТаблічногоДокумента. XLSX);

тут ТабДок - сформований табличний документ, Шлях до файлу - ім'я файлу для вивантаження, ТіпФайлаТаблічногоДокумента.XLSX - формат створюваного файлу. Підтримуються наступні формати Excel:

XLS95 - формат Excel 95;
XLS97 - формат Excel 97;
XLSX - формат Excel 2007.

ТабДок \u003d Новий ТаблічнийДокумент;
ТабДок . Прочитати (ПутьКФайлу, СпособЧтеніяЗначенійТаблічногоДокумента.Значеніе);

тут Шлях до файлу - шлях до завантажуваного файлу Excel. СпособЧтеніяЗначенійТаблічногоДокумента.Значеніе визначає, яким чином потрібно інтерпретувати дані, що зчитуються з вихідного документа. Доступні варіанти:

значення;
Текст.

Обмін через OLE

Обмін через технологію OLE automation, мабуть, найпоширеніший варіант програмної роботи з файлами Excel. Він дозволяє використовувати весь функціонал, що надається Excel, але відрізняється повільною швидкістю роботи у порівнянні з іншими способами. Для обміну через OLE потрібна установка MS Excel:

На комп'ютері кінцевого користувача, якщо обмін відбувається на стороні клієнта;
На комп'ютері сервера 1С: Підприємство, якщо обмін відбувається на стороні сервера.

приклад вивантаження:

// Створення COM-об'єкта
Ексель \u003d Новий COMОб'ект ( «Excel.Application»);
// Відключення виведення попереджень та питань
Ексель . DisplayAlerts \u003d Брехня;
// Створення нової книги
книга \u003d Ексель. WorkBooks. Add ();
// Позиціонування на першому аркуші
лист \u003d Книга. Worksheets (1);

// Запис значення в клітинку
лист . Cells (НомерСтрокі, НомерКолонкі). Value \u003d ЗначеніеЯчейкі;

// Збереження файлу
книга . SaveAs (имяфайла);

Ексель . Quit ();
Ексель \u003d 0;

приклади читання:

// -- ВАРІАНТ 1 --

// Створення COM-об'єкта
Ексель \u003d Новий COMОб'ект ( «Excel.Application»);
// Відкриття книги
книга \u003d Ексель. Workbooks. Open ( Шлях до файлу );

лист \u003d Книга. Worksheets (1);

// Закриття книги
книга . Close (0);

// Закриття Ексель і звільнення пам'яті
Ексель . Quit ();
Ексель \u003d 0;

// -- ВАРІАНТ 2 --

// Відкриття книги
книга \u003d ПолучітьCOMОб'ект ( Шлях до файлу );
// Позиціонування на потрібному аркуші
лист \u003d Книга. Worksheets (1);

// Читання значення осередку, зазвичай тут розташовується цикл обходу осередків
ЗначеніеЯчейкі \u003d Лист. Cells (НомерСтрокі, НомерКолонкі). Value;

// Закриття книги
книга . Application. Quit ();

для обходу всіх заповнених рядків листа Excel можна використовувати такі прийоми:

// -- ВАРІАНТ 1 --
КолічествоСтрок \u003d Лист. Cells (1, 1). SpecialCells (11). Row;
Для НомерСтрокі \u003d 1 За КолічествоСтрок Цикл
ЗначеніеЯчейкі \u003d Лист. Cells (НомерСтрокі, НомерКолонкі). Value;
КонецЦікла;

// -- ВАРІАНТ 2 --
НомерСтрокі \u003d 0;
Поки Істина Цикл
НомерСтрокі \u003d НомерСтрокі + 1;
ЗначеніеЯчейкі \u003d Лист. Cells (НомерСтрокі, НомерКолонкі). Value;
Якщо НЕ ЗначеніеЗаполнено (ЗначеніеЯчейкі) Тоді
перервати;
КонецЕсли;
КонецЦікла;

Замість послідовного обходу всіх рядків листа можна вивантажити всі дані в масив і працювати з ним. Такий підхід буде швидше при читанні великого обсягу даних:

ВсегоКолонок \u003d Лист. Cells (1, 1). SpecialCells (11). Column;
ВсегоСтрок \u003d Лист. Cells (1, 1). SpecialCells (11). Row;

область \u003d Лист. Range (Лист. Cells (1, 1), Ліст. Cells (ВсегоСтрок, ВсегоКолонок));
дані \u003d Область. Value. вивантажити();

У таблиці нижче наведені найбільш затребувані властивості і методи для роботи з Excel через OLE:

Дія	код	коментар
Робота з додатком
Установка видимості вікна програми	Ексель . Visible \u003d Брехня;
Установка режиму виведення попереджень (виводити / не виводити)	Ексель . DisplayAlerts \u003d Брехня;
закриття програми	Ексель . Quit ();
Робота з книгою
Створення нової книги	книга \u003d Ексель. WorkBooks. Add();
Відкриття існуючої книги	книга \u003d Ексель. WorkBooks. Open (имяфайла);
збереження книги	книга . SaveAs (имяфайла);
закриття книги	книга . Close (0);
Робота з листом
Установка поточного листа	лист \u003d Книга. WorkSheets (НомерЛіста);
установка імені	лист . Name \u003d Ім'я;
установка захисту	лист . Protect();
зняття захисту	лист . UnProtect();
Установка орієнтації сторінки	лист . PageSetup. Orientation \u003d 2;	1 - книжкова, 2 - альбомна
Установка лівої межі	лист . PageSetup. LeftMargin \u003d Ексель. CentimetersToPoints (Сантиметри);
Установка верхньої межі	лист . PageSetup. TopMargin \u003d Ексель. CentimetersToPoints (Сантиметри);
Установка правої межі	лист . PageSetup. RightMargin \u003d Ексель. CentimetersToPoints (Сантиметри);
Установка нижньої межі	лист . PageSetup. BottomMargin \u003d Ексель. CentimetersToPoints (Сантиметри);
Робота з рядками, колонками, осередками
Установка ширини колонки	лист . Columns (НомерКолонкі). ColumnWidth \u003d Ширина;
видалення рядка	лист . Rows (НомерСтрокі). Delete();
видалення колонки	лист . Columns (НомерКолонкі). Delete ();
видалення осередки	лист . Cells (НомерСтрокі, НомерКолонкі). Delete();
установка значення	лист . Cells (НомерСтрокі, НомерКолонкі). Value \u003d Значення;
об'єднання осередків	лист . Range (Лист. Cells (НомерСтрокі, НомерКолонкі), Ліст. Cells (НомерСтрокі1, НомерКолонкі1)). Merge();
установка шрифту	лист . Cells (НомерСтрокі, НомерКолонкі). Font. Name \u003d ІмяШріфта;
Щоб вибрати розмір шрифту	лист . Cells (НомерСтрокі, НомерКолонкі). Font. Size \u003d РазмерШріфта;
Установка жирного шрифту	лист . 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
МассівКом \u003d Новий COMSafeArray ( «VT_Variant», ВсегоКолонок, ВсегоСтрок);
// Заповнення COMSafeArray
для Стр \u003d 0 За ВсегоСтрок - 1 Цикл
для К \u003d 0 За ВсегоКолонок - 1 Цикл
МассівКом . SetValue (Кол, Стор, Значення);
КонецЦікла;
КонецЦікла;
// Присвоєння області листа Excel значень з COMSafeArray
лист . Range (Лист. Cells (1, 1), Ліст. Cells (ВсегоСтрок, ВсегоКолонок)). Value \u003d МассівКом;

Обмін через ADO

Файл Excel при обміні через ADO являє собою базу даних, до якої можна звертатися за допомогою SQL-запитів. Установка MS Excel не потрібно, але обов'язкова наявність драйвера ODBC, за допомогою якого буде здійснюватися доступ. Використовуваний драйвер ODBC визначається при вказівці рядки з'єднання до файлу. Зазвичай необхідний драйвер вже встановлений на комп'ютері.

Обмін через ADO помітно швидше обміну через OLE, але при вивантаженні немає можливості використовувати функціонал Excel для оформлення осередків, розмітки сторінок, завдання формул і т.д.

приклад вивантаження:

З'єднання \u003d Новий COMОб'ект ( «ADODB.Connection»);

з'єднання . ConnectionString \u003d «

| Data Source \u003d » + Имяфайла + «;
;
з'єднання . Open (); // Відкриття з'єднання

// Створення COM-об'єкта для команди
Команда \u003d Новий COMОб'ект ( «ADODB.Command»);
команда

// Присвоєння тексту команди для створення таблиці
команда . CommandText \u003d «CREATE TABLE [Аркуш1] (Колонка1 char (255), Колонка2 date, Колонка3 int, Колонка4 float)»;
команда . Execute (); // Виконання команди

// Присвоєння тексту команди для додавання рядка таблиці
команда . CommandText \u003d «INSERT INTO [Аркуш1] (Колонка1, Колонка2, Колонка3, Колонка4) values \u200b\u200b( 'абвгдеё', '8/11/2017', '12345', '12345,6789')»;
Команда.Execute (); // Виконання команди

// Видалення команди і закриття з'єднання
Команда \u003d Не визначено;
з'єднання . Close();
З'єднання \u003d Не визначено;

Для створення нового листа і формування його структури можна скористатися об'єктами ADOX.Catalog і ADOX.Table. В цьому випадку код набуде вигляду:

// Створення COM-об'єкта для роботи з книгою
книга \u003d Новий COMОб'ект ( «ADOX.Catalog»);
книга . ActiveConnection \u003d З'єднання;

// Створення COM-об'єкта для роботи зі структурою даних на аркуші
Таблиця \u003d Новий COMОб'ект ( «ADOX.Table»);
Таблиця . Name \u003d "Лист1»;
Таблиця . Columns. Append ( «Колонка1», 202);
Таблиця . Columns. Append ( «Колонка2», 7);
Таблиця . Columns. Append ( «Колонка3», 5);
Таблиця . Columns. Append ( «Колонка4», 5);

// Створення в книзі листа з описаної структурою
книга . Tables. Append (Таблиця);
Таблиця \u003d Не визначено;
Книга \u003d Не визначено;

У наведеному прикладі в методі

Таблиця . Columns. Append( «Колонка1», 202);

у другому параметрі вказується тип колонки. Параметр необов'язковий, ось деякі значення типу колонки:

5 - adDouble;
6 - adCurrency;
7 - adDate;
11 - adBoolean;
202 - adVarWChar;
203 - adLongVarWChar.

приклад читання:

// Створення COM-об'єкта для з'єднання
З'єднання \u003d Новий COMОб'ект ( «ADODB.Connection»);

// Установка рядки з'єднання
з'єднання . ConnectionString \u003d «
| Provider \u003d Microsoft.ACE.OLEDB.12.0;
| Data Source \u003d » + Имяфайла + «;
| Extended Properties \u003d »» Excel 12.0 XML; HDR \u003d YES »»; »;
з'єднання . Open (); // Відкриття з'єднання

// Створення COM-об'єкта для отримання вибірки
Вибірка \u003d Новий COMОб'ект ( «ADODB.Recordset»);
ТекстЗапроса \u003d «SELECT * FROM [Аркуш1 $]»;

// Виконання запиту
вибірка . Open (ТекстЗапроса, З'єднання);

// Обхід результату вибірки
Поки НЕ Вибірка. EOF () Цикл
ЗначеніеКолонкі1 \u003d Вибірка. Fields. Item ( «Колонка1»). Value ; // Звернення на ім'я колонки
ЗначеніеКолонкі2 \u003d Вибірка. Fields. Item (0). Value; // Звернення по індексу колонки
вибірка . MoveNext ();
КонецЦікла;

вибірка . Close ();
Вибірка \u003d Не визначено;
з'єднання . Close ();
З'єднання \u003d Не визначено;

У рядку з'єднання параметр HDR визначає як буде сприйматися перший рядок на аркуші. Можливі варіанти:

YES - перший рядок сприймається як назви колонок. До значенням можна звертатися по імені і за індексом колонки.
NO - перший рядок сприймається як дані. До значенням можна звертатися тільки за індексом колонки.

У наведених прикладах розглянуто лише кілька об'єктів ADO. Об'єктна модель ADO складається з наступних об'єктів:

Connection;
Command;
Recordset;
Record;
Fields;
Stream;
Errors;
Parameters;
Properties.

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

Для збереження даних з 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 \u003d Новий COMОб'ект ( "Excel.Application"); Excel.WorkBooks.Open (ПолноеІмяФайла); Excel.Visible \u003d 0; Excel.ActiveWindow.DisplayWorkbookTabs \u003d 1; Excel.ActiveWindow.TabRatio \u003d 0.6; FullName \u003d Excel.ActiveWorkbook.FullName; Excel.DisplayAlerts \u003d false; Excel.ActiveWorkbook.SaveAs (FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013 //Excel.Visible \u003d 1; // якщо потрібно попрацювати далі з книгою
//Excel.Application.Quit () // якщо просто виходимо

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

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