Основные методы, принципы и хитрости, используемые при работе с EXCEL через COM-объект
(Источник http://www.1c-h.ru)
Чтение данных из Excel
Доступ из 1С к Excel производится посредством OLE. Создание COM-объекта:
Попытка Эксель = Новый COMОбъект("Excel.Application"); // для v7 код будет: Эксель = СоздатьОбъект("Excel.Application");Исключение Сообщить(ОписаниеОшибки()); Возврат;КонецПопытки;
Теперь используя переменную Эксель можно управлять приложением Excel.
Внимание! Microsoft Excel должен быть установлен на компьютере!
//Следующая команда откроет книгу: Книга = Эксель.WorkBooks.Open(ПутьКФайлу);//Перед тем, как начать считывание данных, укажем лист книги, с которого будем считывать данные: Лист = Книга.WorkSheets(НомерЛиста);//Нумерация листов книги начинается с 1. Общее количество листов можно получить, используя следующую команду: КоличествоЛистов = Книга.Sheets.Count;//Лист можно выбрать по имени листа в книге: Лист = Книга.WorkSheets(ИмяЛиста);//Имя листа в книге можно получить по номеру: ИмяЛиста = Книга.Sheets(НомерЛиста).Name;//Итак, мы открыли книгу и выбрали лист, теперь посмотрим, сколько строк и колонок на выбранном листе: ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column; ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;//Получим значения ячейки листа в строке НомерСтроки и в колонке НомерКолонки: Значение = Лист.Cells(НомерСтроки, НомерКолонки).Value;//прочитаем все данные с первой страницы: Эксель = СоздатьОбъект("Excel.Application"); Книга = Эксель.WorkBooks.Open(ПутьКФайлу); Лист = Книга.WorkSheets(1); ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column; ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row; Для Строка = 1 По ВсегоСтрок Цикл Для Колонка = 1 По ВсегоКолонок Цикл Значение = СокрЛП(Лист.Cells(Строка,Колонка).Value); КонецЦикла; КонецЦикла;
Где ПутьКФайлу — полный путь к файлу книги Excel (включая имя).
//После выполнения действий необходимо закрыть книгу: Эксель.Application.Quit();
Выгрузка данных в Excel
Для вывода (выгрузки) данных в Excel необходимо либо открыть существующую книгу, либо создать новую, и выбрать рабочий лист для вывода данных.
Для создания новой книги можно использовать следующий код:
Книга = Excel.WorkBooks.Add();//При создании книги автоматически создаются листы (по умолчанию 3). Нам остается только выбрать нужный: Лист = Книга.WorkSheets(НомерЛиста);//Или добавить в книгу новый лист: Лист = Книга.Sheets.Add();//Добавим в ячейку на листе значение: Лист.Cells(НомерСтроки, НомерКолонки).Value = Значение;//Запишем книгу: Попытка Книга.SaveAs(ПутьКФайлу); Исключение Сообщить(ОписаниеОшибки()+" Файл не сохранен!"); КонецПопытки;
Где ПутьКФайлу — полный путь к файлу книги Excel (включая имя).
//После выполнения действий необходимо закрыть книгу: Эксель.Application.Quit();
Как программно сохранить файл Excel в формате 2003 года
Синтаксис команды «SaveAs» во втором параметре разрешает указать формат сохраняемого файла.
Числовое значение фрмата файла Excel 2003: FileFormatNum = -4143
Т.е. команду сохранения для этого можно написатьтак:
Книга.SaveAs(ПутьДляЗаписиФайла, -4143);
Часто используемые методы Excel
Хитрости Excel
Как выборочно разрешить / запретить редактирование ячеек листа
//Создаем объект EXCEL Эксель = СоздатьОбъект("Excel.Application"); Книга = Эксель.WorkBooks.Open(ФайлВыгрузки);// Выбор листа Лист =Книга.Worksheets("Список сотрудников"); //делаем шаблон незащищенным Книга.ActiveSheet.UnProtect(); // Заполняем лист// ................................... // Снимаем защиту с области ввода сумм Для Перем = 1 По 10 Цикл // Прописываем, какие ячейки будут доступными Книга.ActiveSheet.Cells(Перем, 2).Locked=0; КонецЦикла; // ставим защиту на лист Книга.ActiveSheet.Protect();
Как запретить появление на экране всяких вопросов от Excel
// чтоб Excel не задавал вопросы: Excel.DisplayAlerts = False;
Как программно скрыть колонку файла Excel
// ПРИМЕР как скрыть колонку программно - скроется колонка №2: ЛистОшибок.Cells(ПозицияШапкиФайла, 2).ColumnWidth = 0; // скрыть колонку №2
Как программно назначить ячейке файла Excel перенос по словам
// ПРИМЕР как осуществлять перенос в ячейке по словам программно ячейка в строке ПозицияШапкиФайла, колонке №2: ЛистОшибок.Cells(ПозицияШапкиФайла, 2).WrapText = Истина; // осуществлять перенос в ячейке по словам
Как обработать файл xls, если Excel не установлен на компьютере
Для этого можно использовать метод
СоздатьОбъект("ADODB.Connection");
Код для 7.7, решающий такую задачу, будет выглядеть примерно так:
db = СоздатьОбъект("ADODB.Connection"); ConectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ИмяФайла+";Extended Properties="+"Excel 8.0;"; rs=CreateObject("ADODB.Recordset"); db.Open(ConectionString); rs.ActiveConnection = db; rs.CursorType = 3; rs.LockType = 2; //Чтобы задать Область, надо выделить область в екселе и нажать Вставка-Имя-Присвоить... rs.Source = "Select * from [Лист$1]"; rs.Open(); КоличествоПолей = rs.Fields.Count; Сообщить(КоличествоПолей); КоличествоЗаписей = rs.RecordCount; Сообщить(КоличествоЗаписей); Если rs.Eof()=0 Тогда Сообщить(rs.Fields(0).Value); rs.MoveNext(); Иначе ТЗ.УстановитьЗначение(1,1,rs.Fields(1).Value); КонецЕсли; rs.Close(); db.Close();
Как указать цвет шрифта в ячейке, цвет рамки, цвет фона
Книга.Sheets(1).Cells(1,1).Borders.Color = 25525124; // цвет рамки Книга.Sheets(1).Cells(1,1).Font.Color = 255000000; // цвет шрифта Книга.Sheets(1).Cells(1,1).Interior.Color = 255045; // цвет фона
Организация автоматической обработки файлов xls из выбранной папки
В 8.1 код обработки файлов выглядит примерно так:
// примеры задания пути к файлам:ПримерПапкиВСети = "\\Adsf01\Public\ЗАГРУЗКА ЗАКАЗОВ\";ПримерПапкиЛокал = "C:\1с\Обмен\";// задаем путь загрузки:ПутьЗагрузки = ПримерПапкиЛокал;// Файлы - Массив из значений типа Файл, содержащий найденные файлы:Файлы = НайтиФайлы(ПутьЗагрузки,"*.xls*");// организовываем перебор файлов:Для Каждого Файл ИЗ Файлы Цикл // обрабатываем файлы.... // ... // В конце можно удалить бработанный файл: Попытка УдалитьФайлы(Файл.ПолноеИмя); Исключение Сообщить("Не удалось удалить файл " + ОписаниеОшибки()); КонецПопытки; // или в конце можно переместить обработанный файл в специально предназначенную подпапку исходной папки: Попытка ПереместитьФайл(Файл.ПолноеИмя, ПутьЗагрузки+"Arhiv\" + Файл.Имя); // папка архива: "C:\1с\Обмен\Arhiv\" Исключение Сообщить("Не удалось переместить файл " + ОписаниеОшибки()); КонецПопытки;КонецЦикла;
В 7.7 для аналогичных действий используются команды:
ФС.НайтиПервыйФайл()ФС.НайтиСледующийФайл()ФС.УдалитьФайл()ФС.ПереименоватьФайл(,,);
Создание кнопки в Excel в 7.7
ТекущийЛист.Shapes("CommandButton").Select ТекущийЛист.OLEObjects("CommandButton").Object.Caption = "Кнопуля";
Процедура открывает Эксель, втавляет на первый лист кнопку «Очистить» и назначает ей макрос,
устанавливающий автофильтр на колонку Е по не нулевым значениям. Текст макроса любой,
главное разделять Симв(13) строки
Попытка Ex=CreateObject("Excel.Application"); Исключение Сообщить(ОписаниеОшибки(),"!!!"); Предупреждение("Не удалось запустить MS Excel!"); Возврат; КонецПопытки; Состояние("Открытие файла..."); Попытка Wb=Ex.WorkBooks.Add(); Исключение Возврат; КонецПопытки; Ex.Visible=-1; Wb.Sheets(1).OLEObjects.Add("Forms.CommandButton.1",,,10, 99.75, 120.75, "Очистить");//27.75 st = "Private Sub CommandButton1_Click()" + Chr(13) + " ThisWorkbook.Sheets(1).Columns(""E:E"").AutoFilter Field:=1, Criteria1:="">0"", Operator:=xlAnd" + Chr(13) + "End Sub"; Ex.VBE.ActiveVBProject.VBComponents(Wb.Sheets(1).Name).CodeModule.AddFromString(st)
текст макроса пишется в переменную st
Как подключиться к запущенному Excel-евскому файлу в реальном времени, изменить его и даже не сохранять, а просто переключить окно на 1С и сразу же выгружать данные в табличную часть, лишь переключив окна
Excel = ПолучитьCOMОбъект(, "Excel.Application");
При этом первый параметр нужно оставить пустым. В этом случае при этом подцепится тот файл экселя, который был открыт последним, даже если порядок переключения окон был таким:
Excel1, Excel2, IE, Проводник, 1С (т.е. что-то и было открытым между 1с и экселевским файлом) — все равно откроется Excel2, потому что он был активен последним.
Описание команды ПолучитьCOMОбъект
Глобальный контекст
ПолучитьCOMОбъект (GetCOMObject)
Синтаксис:
ПолучитьCOMОбъект(<Имя файла>, <Имя класса COM>)
Параметры:
<Имя файла> (необязательный)
Тип: Строка. Имя файла, включающее полный путь.
<Имя класса COM> (необязательный)
Тип: Строка. Имя класса COM, экземпляр которого должен быть создан или получен. Если расширение имени файла, указанное в первом параметре полностью идентифицирует класс объекта, то параметр может быть опущен.
Возвращаемое значение:
Тип: COMОбъект.
Описание:
Основное применение функции ПолучитьCOMОбъект — это получение COM-объекта, соответствующего файлу. Для этого следует в качестве первого параметра функции задать имя файла, который будет определять COM-объект. Например, фрагмент кода
Таб = ПолучитьCOMОбъект("C:\DATA\DATA.XLS");
создает объект Excel.Application и открывает с его помощью файл документа «C:\DATA\DATA.XLS». Если указанный файл во время выполнения данного фрагмента уже открыт с помощью MS Excel, то будет получена ссылка на уже существующий объект.
Для файлов, указываемых в качестве параметра данной функции, должно быть установлено соответствие расширения имени файла и класса COM.
Если в качестве имени файла указана пустая строка, то будет создан новый экземпляр объекта. В этом случае необходимо указать имя класса COM.
Например, фрагмент кода
Таб = ПолучитьCOMОбъект("", "Excel.Application");
создает новый документ Excel. В дальнейшем этот документ может быть программно заполнен и сохранен в файл.
Если первый параметр функции пропущен, то будет произведена попытка получить активный объект указанного типа. Если активного объекта указанного типа в настоящий момент не существует, то будет вызвано исключение. Например, в результате выполнения оператора
П = ПолучитьCOMОбъект( , "Excel.Application");
Переменная П получит значение типа COMОбъект, соответствующее активному приложению MS Excel, если таковое имелось, или будет вызвано исключение, если активных экземпляров MS Excel не было.
Пример:
// Получение объекта COM, соответствующего файлу Таб = ПолучитьCOMОбъект("C:\DATA\DATA.XLS"); // Создание нового экземпляра объекта Таб = ПолучитьCOMОбъект("", "Excel.Application"); // Получение активного объекта Таб = ПолучитьCOMОбъект( , "Excel.Application");
Ниже приведена сравнительная таблица команд — один и тот же код на 7.7 и 8.1 с небольшими дополнениями
Отличия:
команда создания самого объекта в 7.7 и 8.1 различна;
в 8.1 выводится запись в журнал регистрации (просто для примера, например, когда вывод сообщения на экран невозможен из-за выполнения кода в фоновом задании);
в 8.1 параллельно создается, заполняется и сохраняется копия исходного файла с комментариями об ошибках