Данный способ простой. Его суть заключается в том, что объект ТабличныйДокумент имеет методы:
- Записать(< ИмяФайла>, < ТипФайлаТаблицы >) для выгрузки данных в файл;
- Прочитать(< ИмяФайла>, < СпособЧтенияЗначений >) для загрузки данных из файла.
Внимание!
Метод Записать
()
доступен как на клиенте, так и на сервере. Метод Прочитать
()
доступен только на стороне сервера. Необходимо помнить об этом
при планировании клиент-серверного взаимодействия.
Рассмотрим пример сохранения табличного документа в файл. Необходимо любым способом создать и заполнить объект ТабличныйДокумент, а выгрузка в файл осуществляется всего лишь одной строкой:
ТабДок. Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента. 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 без программирования.
Для сохранения табличного документа (например, результата отчета) можно вызвать команду Сохранить или Сохранить как… главного меню.
В открывшемся окне требуется выбрать каталог, имя и формат сохраняемого файла.
Для сохранения данных динамических списков (например, списка номенклатуры) необходимо:
- Вывести данные в табличный документ при помощи команды Еще ⇒ Вывести список… ;
- Сохранить табличный документ в требуемый формат.
Для дальнейшей работы с данными, полученными в табличном документе из системы 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() // если просто выходим