1. Главная страница » Компьютеры » 1С установить формат ячейки excel

1С установить формат ячейки excel

Автор: | 16.12.2019

Формат ячеек Эксель.
Мне довольно часто приходится сталкиваться с задачами взаимодействия 1с и Экселя (Excel). Задачи по выгрузки данных в эксель тоже бывают. Не претендуя на полноту освещения вопроса, тем не менее, кое что о форматировании ячеек эксель из 1с.

// Получить лист
Лист = Книга . WorkSheets ( 1 ) ;

Как задавать что-либо для одной ячейки, строки, колонки, листа, диапазона ячеек.

// Задать формат одной ячейки
Лист . Cells ( 1 , 1 ) . HorizontalAlignment : = — 4108 ; &nbsp&nbsp&nbsp // Выравнивание одной ячейки

// Задать формат строки
Лист . Rows ( 1 ) . HorizontalAlignment : = — 4108 ; &nbsp&nbsp&nbsp &nbsp&nbsp&nbsp // Выравнивание строки

// задать формат колонки
Лист . Columns ( 1 ) . ColumnW >= 20 ; // Ширина колонки в символах

// Задать формат диапазона ячеек
Лист . Range ( Лист . Cells ( 1 , 1 ) , Лист . Cells ( НомерСтроки , 12 ) ) . WrapText = Истина ; &nbsp&nbsp&nbsp //Перенос текста по словам

Многие знают, что Excel гораздо старше 1С. На мой взгляд, это очень успешный продукт, и нареканий о нем я не слышал. Excel прост и универсален. Он способен выполнять не только простые арифметические операции, но и сложные вычисления, построение графиков и т.п. Знаю примеры, когда организации до перехода на 1С вели часть бухгалтерии в Excel. Многие и сейчас параллельно используют две программы. В этой статье рассмотрим способы обмена данными между 1С и Excel.

Таблицы 1С и Excel.

Многие пользователи для удобства работы сохраняют таблицы 1С (печатные формы) в формате Excel (*.xls). После чего в полученном файле делают различные группировки, сортировки, вычисления и т.п. Связано это с тем, что в таблицах 1С нет такого огромного функционала как в таблицах Excel. Но в версии 1С 8.0 есть нововведения, делающих работу с таблицами более комфортной.

Замечание: В Excel разделителем дробной части считается символ ",". Поэтому перед сохранением таблицы 1С в формате Excel замените в ней другой разделитель (например ".") на ",". Иначе в Excel с этими числами не удастся произвести вычисления, или они вообще не будут отображаться как числа. Например, в таблице 1С "15.2" отобразится в Excel как "15.фев".

Получение данных из Excel.

Доступ из 1С к Excel производится посредством OLE. Например, код

Попытка
Эксель = СоздатьОбъект( "Excel.Application" );
Исключение

Сообщить(ОписаниеОшибки() + " Программа Exсel не установлена на данном компьютере!" );
Возврат;

позволит нам получить доступ через переменную "Эксель" к запущенному приложению Excel. А далее уже можно получить доступ к книге (файлу), листу и ячейке с данными. Далее примеры кода.

Открытие книги (файла):

Книга = Эксель . WorkBooks . Open ( ПутьКФайлу );

ПутьКФайлу — полный путь к файлу книги Excel.

Выбор листа книги для работы с ним:

Лист = Книга . WorkSheets ( НомерЛиста );

Лист = Книга . WorkSheets ( ИмяЛиста );

НомерЛиста — номер листа в книге, ИмяЛиста — имя листа в книге.

Получение значения ячейки листа:

Значение = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;

НомерСтроки, НомерКолонки — номер строки и номер колонки, на пересечении которых находится ячейка.

Важно: не забывайте поле выполнения нужных действий добавлять код Эксель . Quit (); , иначе запущенный процесс останется незавершенным и будет занимать память и процессор компьютера.

Вывод данных в Excel.

Для вывода (выгрузки) данных в Excel необходимо либо открыть существующую книгу, либо

создать новую, и выбрать рабочий лист для вывода данных. Открытие существующей книги описано выше, а для создания новой книги нужно использовать следующий код:

Попытка
Эксель = СоздатьОбъект( "Excel.Application" );
Исключение

Сообщить(ОписаниеОшибки() + " Программа Exсel не установлена на данном компьютере!" );
Возврат;

КонецПопытки;
Книга = Эксель . WorkBooks . Add ();

Так как при создании книги в Excel автоматически создаются листы (Сервис->Параметры->Общие->Листов в новой книге), то нужно лишь произвести выбор листа, с которым будет вестись работа:

Лист = Книга . WorkSheets ( НомерЛиста );

либо добавить в книгу новый лист, если необходимо:
Лист = Книга . Sheets . Add ();

Следующим шагом будет установка значения ячейки:
Лист . Cells ( НомерСтроки , НомерКолонки ). Value = Значение ;

НомерСтроки, НомерКолонки — номер строки и номер колонки, на пересечении которых находится ячейка.

И в конце нужно произвести запись созданной книги:

Попытка
Книга . SaveAs ( ПутьКФайлу );
Исключение

Читайте также:  Philips azur очистка от накипи

Сообщить(ОписаниеОшибки() + " Файл не сохранен!" );
Возврат;

ПутьКФайлу — полный путь к файлу книги Excel (включая имя).

Важно: не забывайте, что в имени файлов не должно содержаться символов / : * ? " > Часто используемые методы для чтения/установки значений в Excel.

Эксель = СоздатьОбъект( "Excel.Application" );
Получение доступа к приложению Excel.

Эксель . Visible = Видимость ;

0 — Excel не виден, 1 — виден.

Книга = Эксель . WorkBooks . Add ();

Создание новой книги (файла) Excel.

Книга . SaveAs ( ИмяФайла );

Сохранение книги Excel.

Лист = Книга . WorkSheets . Add ();

Добавление нового листа в книгу.

Книга = Эксель . WorkBooks . Open ( ИмяФайла );

Открытие существующей книги (файла) Excel.

Лист = Книга . WorkSheets ( НомерЛиста );

Установка листа в качестве рабочего с номером НомерЛиста.

Лист . Name = ИмяЛиста ;

Задание рабочему листу имени ИмяЛиста

Лист . PageSetup . Zoom = Масштаб ;

Задание параметра страницы "Масштаб" (от 10 до 400).

Лист . PageSetup . Orientation = Ориентация ;

Ориентация: 1 — книжная, 2 — альбомная.

Лист . PageSetup . LeftMargin = Эксель . CentimetersToPoints ( Сантиметры );

Задание левой границы (в сантиметрах).

Лист . PageSetup . TopMargin =

Эксель . CentimetersToPoints ( Сантиметры );

Задание верхней границы (в сантиметрах).

Лист . PageSetup . RightMargin =

Эксель . CentimetersToPoints ( Сантиметры );

Задание правой границы (в сантиметрах).

Лист . PageSetup . BottomMargin =

Эксель . CentimetersToPoints ( Сантиметры );

Задание нижней границы (в сантиметрах).

Лист . Columns ( НомерКолонки ) . ColumnWidth = Ширина ;

Задание ширины колонке.

Лист . Cells ( НомерСтроки , НомерКолонки ) . Value = Значение ;

Ввод данных в ячейку.

Лист . Cells ( НомерСтроки , НомерКолонки ) . Font . Name = ИмяШрифта ;

Установка шрифта в ячейке.

Лист . Cells ( НомерСтроки , НомерКолонки ) . Font . Size = РазмерШрифта ;

Установка размера шрифта в ячейке.

Лист . Cells ( НомерСтроки , НомерКолонки ) . Font . Bold = Жирный ;

1 — жирный шрифт, 0 — нормальный.

Лист . Cells ( НомерСтроки , НомерКолонки ) . Font . Italic = Курсив ;

1 — наклонный шрифт, 0 — нормальный.

Лист . Cells ( НомерСтроки , НомерКолонки ) . Font . Underline =

2 — подчеркнутый, 1 — нет.

Лист . Cells ( НомерСтроки , НомерКолонки ) . NumberFormat =

Установка формата данных ячейки.

Лист . Cells ( НомерСтроки , НомерКолонки ) . Borders . Linestyle =

MS Excel давно стал стандартом для работы с электронными таблицами. В статье рассматриваются способы программной выгрузки и загрузки из 1С в файлы Excel.

Существует несколько способов программной работы с файлами Excel из 1С. Каждый из них имеет свои преимущества и недостатки.

Обмен через табличный документ

Данный способ простой. Его суть заключается в том, что объект ТабличныйДокумент имеет методы:

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

Внимание!

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

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

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

Примеры чтения:

// Создание COM-объекта
Эксель = Новый COMОбъект ( «Excel.Application» );
// Открытие книги
Книга = Эксель . Workbooks . Open ( ПутьКФайлу );
// Позиционирование на нужном листе
Лист = Книга . Worksheets ( 1 );

Читайте также:  Dtim период что это

// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;

// Закрытие книги
Книга . Close ( 0 );

// Закрытие Эксель и освобождение памяти
Эксель . Quit ();
Эксель = 0 ;

// Открытие книги
Книга = Получить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 ( НомерКолонки ). ColumnW >= Ширина ; Удаление строки Лист . 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 определяется при указании строки соединения к файлу. Обычно требуемый драйвер уже установлен на компьютере.

Читайте также:  Huawei p20 lite киров

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

Пример выгрузки:

// Создание COM-объекта для соединения
Соединение = Новый COMОбъект ( «ADODB.Connection» );

// Установка строки соединения
Соединение . ConnectionString = «
|Prov >
|Data Source=» + ИмяФайла + «;
|Extended Properties=»»Excel 12.0 XML;HDR=YES»»;» ;
Соединение . Open (); // Открытие соединения

// Создание COM-объекта для команды
Команда = Новый COMОбъект ( «ADODB.Command» );
Команда . ActiveConnection = Соединение ;

// Присвоение текста команды для создания таблицы
Команда . 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 = «
|Prov >
|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 состоит из следующих объектов:

Выгрузка без программирования

Для сохранения данных из 1С в Excel не всегда целесообразно прибегать к программированию. Если в режиме Предприятия пользователь может отобразить требуемые для выгрузки данные, то, их возможно сохранить в Excel без программирования.

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

В открывшемся окне требуется выбрать каталог, имя и формат сохраняемого файла.

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

  1. Вывести данные в табличный документ при помощи команды Еще ⇒ Вывести список…;
  2. Сохранить табличный документ в требуемый формат.

Остались вопросы?
Спросите в комментариях к статье.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *