Мой личный справочник: Excel — основные методы и конструкции языка, подключение через COM — соединение (1С: v8 и v7: Программисту: Язык программирования)

Оригинал тут: http://www.1c-h.ru/?p=238

Вопрос Как осуществить чтение/запись данных из/в Excel на языке 1с (используя COM-объект)

Ответ Здесь можно скачать шаблонную обработку, разработанную в среде «1С:Предприятие 8.1» для работы с файлами Excel:

Шаблон обработки в 1С файла Excel (14,5 KiB, 3 804 hits)
В обработке осуществляются все основные действия с файлом Excel. Даны подробные комментарии. Можно использовать в качестве шаблона для разработки собственных выгрузок/загрузок в/из Excel.

Основные методы, принципы и хитрости, используемые при работе с EXCEL через COM-объект

Чтение данных из Excel

Доступ из 1С к Excel производится посредством OLE. Создание COM-объекта:

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

Теперь используя переменную Эксель можно управлять приложением Excel.
(Права на эту статью принадлежат сайту http://www.1c-h.ru)

Внимание! 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(ПутьКФайлу);
 Исключение
  Сообщить(ОписаниеОшибки()+" Файл не сохранен!");
 КонецПопытки;

       Эксель.Application.Quit();

Как программно сохранить файл Excel в формате 2003 года

Синтаксис команды «SaveAs» во втором параметре разрешает указать формат сохраняемого файла.
Числовое значение фрмата файла Excel 2003: FileFormatNum = -4143
Т.е. команду сохранения для этого можно написатьтак:

Книга.SaveAs(ПутьДляЗаписиФайла, -4143);

Часто используемые методы Excel

Эксель.Visible = Видимость;	0 — Excel не виден, 1 — виден.
Книга = Эксель.WorkBooks.Add();	Создание новой книги (файла) Excel.
Книга = Эксель.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(НомерСтроки,НомерКолонки).ColumnWidth = 0;	Скрыть всю колонку, в которой расположена ячейка
Лист.Cells(НомерСтроки, НомерКолонки).Value = Значение;	Ввод данных в ячейку.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Name = ИмяШрифта;	Установка шрифта в ячейке.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Color = ЦветШрифта;	Установка цвета шрифта в ячейке. Тип переменной ЦветШрифта — число десятичное.
Лист.Cells(НомерСтроки,НомерКолонки).Borders.Color = ЦветРамки;	Установка цвета рамки в ячейке.
Лист.Cells(НомерСтроки,НомерКолонки).Interior.Color = ЦветФона;	Установка цвета фона в ячейке.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Size = РазмерШрифта;	Установка размера шрифта в ячейке.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Bold = Жирный;	1 — жирный шрифт, 0 — нормальный.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Italic = Курсив;	1 — наклонный шрифт, 0 — нормальный.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Underline = Подчеркнутый;	2 — подчеркнутый, 1 — нет.
Лист.Cells(НомерСтроки, НомерКолонки).NumberFormat = Формат;	Установка формата данных ячейки.
Лист.Cells(НомерСтроки,НомерКолонки).Borders.Linestyle = ТипЛинии;	Установка рамок ячейки. 1 — тонкая сплошная.
Лист.Cells(НомерСтроки,НомерКолонки).WrapText = Истина;	Осуществлять перенос по словам в указанной ячейке
Лист.Protect();	Установка защиты на лист
Лист.UnProtect();	Снятие защиты с листа
Лист.Cells(Строка, Столбец).Locked=0;	Ячейка будет доступной (и после установки защиты на лист)
ПолучитьCOMОбъект(<Имя файла>, <Имя класса COM>);	Основное применение функции ПолучитьCOMОбъект — это получение COM-объекта, соответствующего файлу.

Хитрости 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 параллельно создается, заполняется и сохраняется копия исходного файла с комментариями об ошибках

1C 7.7	1C v8
Доступ из 1С к Excel производится посредством OLE. Создание COM-объекта:
Попытка Эксель = СоздатьОбъект(«Excel.Application»); Исключение Сообщить(ОписаниеОшибки()); Возврат; КонецПопытки;	Попытка Эксель = Новый COMОбъект(«Excel.Application»); Исключение ЗаписьЖурналаРегистрации(«Excel: Неудачная попытка подключения компоненты Excel. Возможно, программа Excel не установлена на данном компьютере!», УровеньЖурналаРегистрации.Ошибка,,, ОписаниеОшибки()); Возврат Ложь; КонецПопытки;
Теперь, используя переменную «Эксель», можно управлять приложением Excel. * Внимание! Microsoft Excel должен быть установлен на компьютере! Следующая команда откроет книгу:
Книга = Эксель.WorkBooks.Open(ПутьКФайлу);	Попытка Книга = Эксель.WorkBooks.Open(ИмяФЗагрузки); Исключение ЗаписьЖурналаРегистрации(«Excel: Неудачная попытка открытия файла Excel», УровеньЖурналаРегистрации.Ошибка,,, ОписаниеОшибки()); Возврат; КонецПопытки;
Перед тем, как начать считывание данных, укажем лист книги, с которого будем считывать данные:
Лист = Книга.WorkSheets(НомерЛиста)
Нумерация листов книги начинается с 1. Общее количество листов можно получить, используя следующую команду:
КоличествоЛистов = Книга.Sheets.Count;	КоличествоЛистов = Книга.Sheets.Count;
// создание файла для записи проблем
КнигаОшибок = Эксель.WorkBooks.Add();	КнигаОшибок = Эксель.WorkBooks.Add();
// читаем книгу по листам:
	Для СчетчикПоЛистам = 1 По КоличествоЛистов Цикл //цикл по листам // если листов больше 3, то потребуется добавить лист в книгу Если СчетчикПоЛистам > 3 Тогда ЛистОшибок = КнигаОшибок.Sheets.Add(); Иначе ИмяЛистаОшибок = КнигаОшибок.Sheets(СчетчикПоЛистам).Name; ЛистОшибок = КнигаОшибок.WorkSheets(ИмяЛистаОшибок); КонецЕсли;
//Имя листа в книге можно получить по номеру:
ИмяЛиста = Книга.Sheets(НомерЛиста).Name;	ИмяЛиста = Книга.Sheets(СчетчикПоЛистам).Name;
//Лист можно выбрать по имени листа в книге:
Лист = Книга.WorkSheets(ИмяЛиста);	Лист = Книга.WorkSheets(ИмяЛиста);
//Итак, мы открыли книгу и выбрали лист, теперь посмотрим, сколько строк и колонок на выбранном листе:
ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column; ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;	Попытка ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column; ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row; // используем для перебора строк Исключение ЗаписьЖурналаРегистрации(«Excel: Неудачная попытка получения количества колонок и строк Excel», УровеньЖурналаРегистрации.Ошибка,,, ОписаниеОшибки()); Возврат; КонецПопытки;
// получение значения из конкретной ячейки файла экселя:
Значение = Лист.Cells(НомерСтроки, НомерКолонки).Value;	Для счетчикПоКолонкам = 1 По ВсегоКолонок Цикл //цикл по колонкам ЗначениеВЯчейке=Книга.Sheets(СчетчикПоЛистам).Cells(ПозицияШапкиФайла,счетчикПоКолонкам).Value;
// установка нового значения ячейки экселя:
Лист.Cells(НомерСтроки, НомерКолонки).Value = Значение;	ЛистОшибок.Cells(ПозицияШапкиФайла, счетчикПоКолонкам).Value = ЗначениеВЯчейке;
// сохранение изменений в новом файле экселя:
	// если такой файл уже был записан, удалим его, чтобы эксель не спросил интерактивно про перезапись Попытка ИмяФайлаОшибок = ВыбФайл.Путь + ВыбФайл.ИмяБезРасширения + «_bad.xls»; ФайлОш = Новый Файл(ИмяФайлаОшибок); Если ФайлОш.Существует() Тогда УдалитьФайлы(ИмяФайлаОшибок); КонецЕсли; КнигаОшибок.SaveAs(ИмяФайлаОшибок); // файл с ошибочными данными Исключение ДобавитьСообщениеВОшибки(«Не удалось записать в файл сообщения об ошибках!»+ ОписаниеОшибки(),,, «Важно»); КонецПопытки;
// После выполнения действий закрываем книгу:
Эксель.Quit();	Эксель.Quit();

Мой личный справочник

пятница, 11 октября 2013 г.

Excel — основные методы и конструкции языка, подключение через COM — соединение (1С: v8 и v7: Программисту: Язык программирования)

Основные методы, принципы и хитрости, используемые при работе с EXCEL через COM-объект

Чтение данных из Excel

Выгрузка данных в Excel

Как программно сохранить файл Excel в формате 2003 года

Часто используемые методы Excel

Хитрости Excel

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

Как запретить появление на экране всяких вопросов от Excel

Как программно скрыть колонку файла Excel

Как программно назначить ячейке файла Excel перенос по словам

Как обработать файл xls, если Excel не установлен на компьютере

Как указать цвет шрифта в ячейке, цвет рамки, цвет фона

Организация автоматической обработки файлов xls из выбранной папки

Создание кнопки в Excel в 7.7

Описание команды ПолучитьCOMОбъект

Ниже приведена сравнительная таблица команд — один и тот же код на 7.7 и 8.1 с небольшими дополнениями

Комментариев нет:

Отправить комментарий

пятница, 11 октября 2013 г.

Excel — основные методы и конструкции языка, подключение через COM — соединение (1С: v8 и v7: Программисту: Язык программирования)

Основные методы, принципы и хитрости, используемые при работе с EXCEL через COM-объект

Чтение данных из Excel

Выгрузка данных в Excel

Как программно сохранить файл Excel в формате 2003 года

Часто используемые методы Excel

Хитрости Excel

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

Как запретить появление на экране всяких вопросов от Excel

Как программно скрыть колонку файла Excel

Как программно назначить ячейке файла Excel перенос по словам

Как обработать файл xls, если Excel не установлен на компьютере

Как указать цвет шрифта в ячейке, цвет рамки, цвет фона

Организация автоматической обработки файлов xls из выбранной папки

Создание кнопки в Excel в 7.7

Описание команды ПолучитьCOMОбъект

Ниже приведена сравнительная таблица команд — один и тот же код на 7.7 и 8.1 с небольшими дополнениями

Комментариев нет:

Отправить комментарий

пятница, 11 октября 2013 г.