1С

1С: Работа с внешними файлами. Работа с Excel

Опубликовано:

Описание

Примеры работы с Excel. Вставка формул, объединения ячеек, выравнивание текста, изменение высоты строк, форматирование текста и др.

Другое в 1С

Работа с Excel в 1С

Оглавление (нажмите, чтобы раскрыть)

Автоопределение ширины столбцов

Пример автоопределения ширины столбцов в Excel из 1С 

//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

//заносим информацию в ячейки
строка = "";
Для н = 1 По 9 Цикл
    строка = строка + н;
    Sheet.Cells(1, н).Value = строка;
КонецЦикла;

//автоопределение ширины первого столбца
Sheet.Columns(1).AutoFit();

//автоопределение ширины со 2-го по 9-й
Range = Sheet.Range(
    Sheet.Cells(1, 2),
    Sheet.Cells(1, 9));
Range.EntireColumn.AutoFit();

//показываем документ на экране
Excel.Visible = Истина;

Вставка массива данных

Пример вставки массива данных в Excel из 1С 

//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

//заполняем массив таблицей умножения   
data = Новый COMSafeArray("VT_VARIANT", 9, 9);
Для Н1 = 1 По 9 Цикл
    Для Н2 = 1 По 9 Цикл
        data.SetValue(Н1 - 1, Н2 - 1, Н1 * Н2);
    КонецЦикла;
КонецЦикла;

//выгружаем массив в Excel
//вставка массива происходит значительно
//быстрее, чем заполнение отдельных ячеек
Range = Sheet.Range(
    Sheet.Cells(1, 1),
    Sheet.Cells(9, 9));
Range.Value = data;

//показываем документ на экране
Excel.Visible = Истина;

Вставка формул

Пример вставки формулы в Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

Для Н = 1 По 5 Цикл
    Sheet.Cells(Н, 1).Value = Н * 2;
    Sheet.Cells(Н, 2).Value = Н * 3;
КонецЦикла;

//формула с прямыми ссылками на ячейки
Sheet.Cells(6, 1).Formula = "=sum(A1:A5)";

//формула с относительними ссылками на ячейки
Sheet.Cells(6, 2).Formula = "=sum(R[-1]C:R[-5]C)";

//показываем документ на экране
Excel.Visible = Истина;

Выгрузка данных в таблицу

Пример выгрузки данных в таблицу Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

//таблица значений, выгружаемая в Excel
Таб = ПолучитьТабЗначений();

//переносим заголовки столбцов
НомСтолбца = 1;
НомСтроки = 1;
Для Каждого Колонка Из Таб.Колонки Цикл
    Sheet.Cells(НомСтроки, НомСтолбца).Value = Колонка.Имя;
    НомСтолбца = НомСтолбца + 1;
КонецЦикла;

//переносим данные
Количество = Таб.Колонки.Количество();
Для Каждого Строка из Таб Цикл
    НомСтроки = НомСтроки + 1;
    Для Н = 0 По Количество - 1 Цикл
        Sheet.Cells(НомСтроки, Н + 1).Value = Строка[Н];
    КонецЦикла;
КонецЦикла;

//сохраняем таблицу
WorkBook.SaveAs("C:\tmp\2.xlsx");

//закрываем таблицу
Excel.Application.Quit();


&НаСервере
Функция ПолучитьТабЗначений()
    Таб = Новый ТаблицаЗначений;
    Таб.Колонки.Добавить("Код");
    Таб.Колонки.Добавить("Значение");

    Строка = Таб.Добавить();
    Строка.Код = 1;
    Строка.Значение = "Один";

    Строка = Таб.Добавить();
    Строка.Код = 2;
    Строка.Значение = "Два";

    Возврат Таб;
КонецФункции

Выравнивание текста в ячейках

Пример выравнивания текста в ячейках Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

Sheet.Cells(2, 2).Value = "Текст";
Sheet.Columns(2).ColumnWidth = 100;
Sheet.Rows(2).RowHeight = 100;

//по центру по вертикли
//левое - 1, правое - 3
Sheet.Cells(2, 2).VerticalAlignment = 2;
//по центру по горизонтали
//верхнее - 2, нижнее - 4 
Sheet.Cells(2, 2).HorizontalAlignment = 3;

//показываем документ на экране
Excel.Visible = Истина;

Задание отступов для печати

Пример задания отступов при печати файла Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

//задаем левый отспут 3 см
Sheet.PageSetup.LeftMargin =
    Excel.CentimetersToPoints(3);
//RightMargin - правый отступ
//TopMargin - верхний отступ
//BottomMargin - нижний отступ

//показываем документ на экране
Excel.Visible = Истина;

Изменение высоты строк

Пример изменения высоты листа Excel из 1С 
/создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

//меняем высоту первой строки
Sheet.Rows(1).RowHeight = 30;

//меняем высоту со 2-й по 5-ю
Range = Sheet.Range(
    Sheet.Cells(2, 1),
    Sheet.Cells(5, 1));
Range.RowHeight = 10;

//показываем документ на экране
Excel.Visible = Истина;

Изменение имени листа

Пример изменения имени листа Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

//задаем имя листа Excel
Sheet.Name = "НовоеИмя";

//показываем документ на экране
Excel.Visible = Истина;

Изменение ориентации листа

Пример изменения ориентации листа Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

//1 - книжная, 2- альбомная
Sheet.PageSetup.Orientation = 2;

//показываем документ на экране
Excel.Visible = Истина;

Изменение цвета ячеек

Пример изменения цвета ячеек Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

Sheet.Cells(2, 2).Value = "Текст";
//желтый цвет текста
Sheet.Cells(2, 2).Font.Color = -16711681;
//черный цвет фона
Sheet.Cells(2, 2).Interior.Color = 526344;

//показываем документ на экране
Excel.Visible = Истина;

Изменение ширины столбцов

Пример изменения ширины столбцов Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

//задаем ширину первых 5-ти столбцов
Для Н = 1 По 5 Цикл
    Sheet.Columns(Н).ColumnWidth = Н * 10;
КонецЦикла;

//показываем документ на экране
Excel.Visible = Истина;

Объединение ячеек

Пример объединения ячеек Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

Sheet.Cells(1, 1).Value =
    "Очень длинный заголовок";

//объединяем с 1-й по 3-ю ячейки
//первой строки
Sheet.Range(
    Sheet.Cells(1, 1),
    Sheet.Cells(1, 3)).Merge();

//показываем документ на экране
Excel.Visible = Истина;

Определение формата ячейки

Пример определения формата ячейки Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

//задаем значение и числовой формат ячейки
Sheet.Cells(1, 1).Value = 1000000;
Sheet.Cells(1, 1).NumberFormat = "# ##0.00";

//задаем значение и строковый формат ячейки
Sheet.Cells(1, 3).NumberFormat = "@";
Sheet.Cells(1, 3).Value = "112233445566778899";

//показываем документ на экране
Excel.Visible = Истина;

Открытие таблицы

Пример открытия таблицы Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//открываем таблицу C:\tmp\1.xlsx
WorkBook = Excel.WorkBooks.Open("C:\tmp\1.xlsx");

//показываем документ на экране
Excel.Visible = Истина;

Перенос текста по словам

Пример переноса текста по словам в Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

Sheet.Cells(1, 1).Value =
    "Очень днинный текст";
Sheet.Cells(1, 1).RowHeight = 45;
Sheet.Cells(1, 1).WrapText = Истина;

//показываем документ на экране
Excel.Visible = Истина;

Рамки вокруг ячеек

Пример добавления рамок вокруг ячеек Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

//все рамки толкой, сплошной линией
Range = Sheet.Range(
    Sheet.Cells(2, 2),
    Sheet.Cells(3, 3));
Range.Borders.Linestyle = 1;

//внешние рамки толстой, сплошной
Range = Sheet.Range(
    Sheet.Cells(2, 5),
    Sheet.Cells(3, 6));
Для Н = 1 По 4 Цикл
    Range.Borders(Н).Linestyle = 1;
    Range.Borders(Н).Weight = 4;
КонецЦикла;
//внутренние рамки средней прерывистой
Для Н = 11 По 12 Цикл
    Range.Borders(Н).Linestyle = -4115;
    Range.Borders(Н).Weight = -4138;
КонецЦикла;

//показываем документ на экране
Excel.Visible = Истина;

Форматирование текста

Пример форматирования текста в Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//создаем новую таблицу
WorkBook = Excel.WorkBooks.Add();

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

Sheet.Cells(2, 2).Value = "Текст";
//название шрифта
Sheet.Cells(2, 2).Font.Name = "Verdana";
//размер шрифта
Sheet.Cells(2, 2).Font.Size = 16;
//жирный шрифт - 1, обычный - 0
Sheet.Cells(2, 2).Font.Bold = 1;
//курсив - 1, обычный - 0
Sheet.Cells(2, 2).Font.Italic = 1;
//подчеркнутый - 2, обычный - 1
Sheet.Cells(2, 2).Font.Underline = 2;

//показываем документ на экране
Excel.Visible = Истина;

Чтение данных из таблицы

Пример чтения данных таблицы Excel из 1С 
//создаем объект для работы с Excel
Excel = Новый COMОбъект("Excel.Application");

//открываем таблицу C:\tmp\1.xlsx
WorkBook = Excel.WorkBooks.Open("C:\tmp\1.xlsx");

//получаем доступ к первому листу таблицы
Sheet = WorkBook.WorkSheets(1);

ВсегоСтолбцов = 2;

Таб = Новый ТаблицаЗначений;
Таб.Колонки.Добавить("Код");
Таб.Колонки.Добавить("Значение");

//считываем все данные в таблицу значений
НомСтроки = 1;

Пока Истина Цикл

    Код = Sheet.Cells(НомСтроки, 1).Value;
    //прерываемся если значение первого
    //столбца пустое
    Если Код = Неопределено Тогда
        Прервать;
    КонецЕсли;

    Значение = Sheet.Cells(НомСтроки, 1).Value;

    Строка = Таб.Добавить();
    Строка.Код = Код;
    Строка.Значение = Значение;

    НомСтроки = НомСтроки + 1;

КонецЦикла;

//закрываем таблицу
Excel.Quit();
Ключи: |