среда, 13 марта 2013 г.

1с и Excel. Часть 2. Выгрузка данных в Excel


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

Для создания новой книги можно использовать следующий код:
Попытка
 Эксель = СоздатьОбъект("Excel.Application"); 
Исключение
 Сообщить(ОписаниеОшибки()); 
 Возврат;
КонецПопытки; 
Книга = Эксель.WorkBooks.Add();



При создании книги автоматически создаются листы (по умолчанию 3). Нам остается только выбрать нужный:
Лист = Книга.WorkSheets(НомерЛиста);

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

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

Запишем книгу:
Попытка
 Книга.SaveAs(ПутьКФайлу); 
Исключение
 Сообщить(ОписаниеОшибки()+" Файл не сохранен!"); 
КонецПопытки;

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

После выполнения действий необходимо закрыть книгу:
Эксель.Application.Quit();

Часто используемые методы 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 = ТипЛинии;Установка рамок ячейки. 1 - тонкая сплошная.
Лист.Protect();Установка защиты на лист
Лист.UnProtect();Снятие защиты с листа
Лист.Cells(Строка, Столбец).Locked=0;Ячейка будет доступной (и после установки защиты на лист)

Хитрости Excel

Как выборочно разрешить / запретить редактирование ячеек листа

//Создаем объект EXCEL
Эксель = СоздатьОбъект("Excel.Application"); 
Книга = Эксель.WorkBooks.Open(ФайлВыгрузки); 
Лист =Книга.Worksheets("Список сотрудников"); // Выбор листа
       
Книга.ActiveSheet.UnProtect();    //делаем шаблон незащищенным

// Заполняем лист

...................................

// Снимаем защиту с области ввода сумм

Для Перем = 1 По 10 Цикл
 // Прописываем, какие ячейки будут доступными  
    Книга.ActiveSheet.Cells(Перем, 2).Locked=0; 
КонецЦикла;

Книга.ActiveSheet.Protect();    // ставим защиту на лист

Как осуществить поиск / замену значений ячеек на листе

//Выбираем область поиска - весь лист
ОбластьПоиска = Лист.Cells;

// Укажем начало листа
НачалоЛиста = Диапазон.Cells(1, 1);

// Ищем ячейку с текстом, указанным в переменной ТекстДляПоиска, 
//укажем откуда искать - с начала листа
ОбластьПараметр = ОбластьПоиска.Find(ТекстДляПоиска, НачалоЛиста);


// Если нашли, заполняем значением из переменной Значение
Если ОбластьПараметр <> неопределено Тогда
 ОбластьПараметр.Value = Значение;
КонецЕсли;

Замечание. Текст для замены лучше выделять, например, в угловые скобки, аналогично параметрам макета в 1С. Это улучшит наглядность, визуально отделит от рабочего текста. 
Текст для поиска в этом случае будет выглядеть так: 

ТекстДляПоиска = "<ИмяДляЗамены>" 

Как скопировать / удалить строку

//Выбираем область поиска - весь лист
ОбластьПоиска = Лист.Cells;

// Найдем нужную нам ячейку
// Если не указываем откуда искать - то с текущей ячейки, это первая ячейка на листе
НайденнаяОбласть = ОбластьПоиска.Find("Текст для поиска");

// Выделяем всю строку
НайденнаяОбласть.EntireRow.Select();

// Копируем выделенное
НайденнаяОбласть.EntireRow.Copy();

// Вставляем (новая строка будет вставлена тут же)
НайденнаяОбласть.EntireRow.Insert();


// Теперь как удалить строку. Найдем область
НайденнаяОбласть = ОбластьПоиска.Find("Текст для поиска", НачалоЛиста);
НайденнаяОбласть.EntireRow.Delete();

Замечание. Приведенные выше примеры позволят, например, организвать работу с шаблонами на основании листов Excel.
Отправить комментарий

Неактивный атрибут "скрытый" или как снять атрибут скрытый после вируса

Некоторые пользователи компьютеров часто сталкиваются с проблемой, когда папки и файлы вдруг становятся скрытыми. Или, проще говоря, исчез...