Работать с файлами Excel можно тремя способами:
- Через OLE объект;
- Через Табличный Документ 1С.
- Через ADODB
Разберем первый способ.
1. Подключаемся к OLE и октрываем файл Excel:
//Поключимся к Excel через OLE, откроем файл и вернем список страниц
//ИмяФайла = Полный путь к файлу Excel
Соотв = Новый Соответствие;
Попытка
Листы = Новый Массив;
Excel = Новый COMОбъект("Excel.Application");
Соотв.Вставить("Соединение",Excel);
ExcelФайл = Excel.WorkBooks.Open (ИмяФайла);
Соотв.Вставить("ExcelФайл",ExcelФайл);
Для Сч = 1 По ExcelФайл.Sheets.Count Цикл
Листы.Добавить(ExcelФайл.Sheets(Сч));
КонецЦикла;
Соотв.Вставить("Листы",Листы);
лРезультат = Соотв;
Исключение
Сообщить("Ошибка создания обьекта Microsoft Excel" + ОписаниеОшибки());
лРезультат = Неопределено;
КонецПопытки;
Возврат лРезультат;
Отсоединяемся от Excel и закрываем файл:
После манипуляции с файлом необходимо его закрыть. В противном случае он останется захваченным в системе, и мы не сможем его использовать в других приложениях.
Процедура ОтключатьсяОтExcel(Соответстие)
Попытка
Соответстие["Соединение"].DisplayAlerts = 0;
Соответстие["ExcelФайл"].Close();
Соответстие["Соединение"].DisplayAlerts = 1;
Соответстие["Соединение"].Quit();
Соответстие["Соединение"] = Неопределено;
Исключение
Сообщить("Не удалось отключиться от Excel - "+ОписаниеОшибки());
КонецПопытки;
КонецПроцедуры
Клиент-Серверный вариант в управляемых формах.
При работе в клиент-серверном варианте основная трудность заключается в том, что обычно файл мы загружаем на стороне клиента, а данные, как правило, обрабатываем на стороне сервера. Причем, таблицу значений нельзя передать от клиента на сервер (точнее, передать таблицу значений можно, но только в толстом клиенте. На тонком - 1С выдаст вам сообщение об ошибке). Есть два пути решения данной задачи:
1. Считываем данные из файла в массив из структур и затем этот массив передаем на сервер для дальнейших манипуляций с данными.
&НаКлиенте
Процедура Загрузить1(Команда)
Если Объект.ИмяФайла = "" Тогда
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = "Не указан файл для загрузки";
Сообщение.Поле = "Объект.ИмяФайла";
Сообщение.Сообщить();
Возврат;
КонецЕсли;
Соединение = ПоключитьсяКExcel(Объект.ИмяФайла);
Если Соединение = Неопределено Тогда
Возврат;
КонецЕсли;
Лист = Соединение["Листы"][0];
//Создаем Массив для строк
МассивДанных = Новый Массив;
ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;
Для Сч = 1 по ВсегоСтрок Цикл
//Создаем структуру для текущей строки
Строка = Новый Структура;
Для Сч2 = 1 по ВсегоКолонок Цикл
Строка.Вставить("Колонка"+Строка(Сч2),Лист.Cells(Сч,Сч2).Value);
КонецЦикла;
МассивДанных.Добавить(Строка);
КонецЦикла;
Загрузить1НаСервере(МассивДанных);
КонецПроцедуры
&НаСервере
Процедура Загрузить1НаСервере(МассивДанных)
//Создадим колонки
Если МассивДанных.Количество() = 0 Тогда
Возврат;
КонецЕсли;
Таб = РеквизитФормыВЗначение("Результат");
МассивРеквизитов = Новый Массив;
Для Каждого Кл Из МассивДанных[0] Цикл
Мас = Новый Массив;
Мас.Добавить(ТипЗнч(Кл.Значение));
Таб.Колонки.Добавить(Кл.Ключ,Новый ОписаниеТипов(Мас),Кл.Значение);
КонецЦикла;
Первая = Истина;
Для Каждого Ст ИЗ МассивДанных Цикл
Если Первая Тогда
Первая = Ложь;
Продолжить;
КонецЕсли;
ТБ = Таб.Добавить();
Для Каждого Кл из СТ Цикл
ТБ[Кл.Ключ] = Кл.Значение;
КонецЦикла;
КонецЦикла;
ОтобразитьТабНаФорме(Таб);
КонецПроцедуры
2-ой способ - это передать файл Excel на сервер через хранилище данных и далее работать с ним уже на сервере.
&НаСервере
Процедура Загрузить2НаСервере(пФайл)
лФайл = ПолучитьИмяВременногоФайла("xlsx");
лДвоичДанные = ПолучитьИзВременногоХранилища(пФайл);
лДвоичДанные.Записать(лФайл);
Соединение = ПоключитьсяКExcelСервер(Объект.ИмяФайла);
Если Соединение = Неопределено Тогда
Возврат;
КонецЕсли;
/// Обработка Excel
ОтключатьсяОтExcelСервер(Соединение);
КонецПроцедуры
&НаКлиенте
Процедура Загрузить2(Команда)
лДвоичДанные = Новый ДвоичныеДанные(Объект.ИмяФайла);
лФайл = ПоместитьВоВременноеХранилище(лДвоичДанные);
Загрузить2НаСервере(лФайл);
КонецПроцедуры
Очень часто приходится работать с очень большими файлами Excel, и его обработка путем перебора строк занимает огромное количество времени.
В таких случаех удобно в одно действие загрузить всю таблицу в массив и в потом работать уже непосредственно с массивом.
Лист = Соединение["Листы"][0];
ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;
Область = Лист.Range(ЛистЭксель.Cells(1,1), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();
На выходе получаем двумерный массив, который содержит все данные указанного листа Excel
Полезные функции при работе с Excel:
| Устанавливает видимость Excel при работе |
//0 - Excel не виден, 1 - виден.
Соединение.Visible = Видимость;
|
| Добавление новой книги в файл Excel |
Книга = Соединение.WorkBooks.Add();
|
| Сохранение книги Excel |
Книга.SaveAs(ИмяФайла);
|
| Добавление нового листа к книге |
Лист = Книга.WorkSheets.Add();
|
| Переименование листа |
Лист.Name = ИмяЛиста;
|
| Изменение маштаба листа |
//"Масштаб" (от 10 до 400).
Лист.PageSetup.Zoom = Масштаб;
|
| Изменение ориентации листа |
//1 - книжная, 2 - альбомная.
Лист.PageSetup.Orientation = Ориентация;
|
| Отступы листа |
//Левый отступ
Лист.PageSetup.LeftMargin = Соединение.CentimetersToPoints(Сантиметры);
//Верхний отступ
Лист.PageSetup.TopMargin = Соединение.CentimetersToPoints(Сантиметры);
//Правый отступ
Лист.PageSetup.RightMargin = Соединение.CentimetersToPoints(Сантиметры);
//Нижний отступ
Лист.PageSetup.BottomMargin = Соединение.CentimetersToPoints(Сантиметры);
|
| Обращение к ячейки чтение/запись |
//Прочитать значение ячейки
//Сч = Номер строки
//Сч2 = Номер колонки
Данные = Лист.Cells(Сч,Сч2).Value
//Записать значение в ячейку
Лист.Cells(Сч,Сч2).Value = Данные
|
| Обращение к области ячеек |
//В качестве параметров передаем ячейки по диогонали
Лист.Range(ЛистЭксель.Cells(1,1),ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок))
|
| Очень часто при чтении или записи значений в Excel ставятся лишние пробелы в числе, например, вместо 1502 он читает как 1 502 и в дальнейшем это значение не приводится к числу. Эту проблему можно решить заменой. |
ЗначениеЯчейки = Лист.Cells(1,3).Value;
СтрЗаменить(Строка(ЗначениеЯчейки),Символы.НПП,"");
|
| Объединение ячеек |
Лист.Range(Лист.Cells(1,1),Лист.Cells(10,1)).Merge();
|
| Работа с именованными ячейками в Excel |
Обл = Лист.Range("Имя_Ячейки_В_Excel").Select();
Обл.Value = "Присваиваем значение";
|
| Удаление ячейки |
Лист.Cells(1,3).Delete();
|
| Удаление области ячеек |
Лист.Range(Лист.Cells(1,1),Лист.Cells(10,1)).Delete();
|
| Обращение к строке |
//Сч = Номер строки
Лист.Rows(Сч)
|
| Изменение ширины колонки |
Лист.Columns(НомерКолонки).ColumnWidth = Ширина;
|
| Обращение к колонке |
//Сч = Номер Колонки
Лист.Cols(Сч)
|
| Удаление Строки |
Лист.Rows(Сч).Delete()
|
| Фон ячейки / области / Строки / |
//Фон Ячейки
Лист.Cells(1,1).Interior.Color = ПолучитьЦветExcelRGB(10,10,10);
//Фон Области
Лист.Range(Лист.Cells(1,1),Лист.Cells(10,1)).Interior.Color = ПолучитьЦветExcelRGB(10,10,10);
//Фон строки
Лист.Rows(Сч).Interior.Color = ПолучитьЦветExcelRGB(10,10,10);
|
| Функция переводит цвет из формата RGB в формат Excel |
Функция ПолучитьЦветExcelRGB(R,G,B)
Возврат ((B*256) + G) * 256 + R;
КонецФункции
|
| Управление шрифтом в ячейки/строке/области |
//Изменение шрифта
Лист.Cells(НомерСтроки,НомерКолонки).Font.Name = ИмяШрифта;
//Изменение размера шрифтв
Лист.Cells(НомерСтроки,НомерКолонки).Font.Size = РазмерШрифта;
//Управление жирностью шрифта
//1-жирный шрифт (bold)
//0-нормальный шрифт (normal)
Лист.Cells(НомерСтроки,НомерКолонки).Font.Bold = Жирный;
//Управление курсивом шрифта
//1-Курсив
//0-Нормальный
Лист.Cells(НомерСтроки,НомерКолонки).Font.Italic = Курсив;
//2 - Подчеркнутый шрифт
//1- нет
Лист.Cells(НомерСтроки,НомерКолонки).Font.Underline = Подчеркнутый;
|
| Разрешает переносить по словам в ячейке |
//1-Переносить
Лист.Cells(1, 1).WrapText = 1;
|
| Управление рамкой ячейки |
//1 - Тонкая сплошная линия
Лист.Cells(НомерСтроки,НомерКолонки).Borders.Linestyle = ТипЛинии;
|
| Устанавливаем формат ячейки |
//"@" - текстовый
//"0.00" - числовой
Лист.Cells(НомерСтроки, НомерКолонки).NumberFormat = Формат;
|
| Формула в ячейки |
Лист.Cells(Сч,Сч2).FormulaLocal = "=ОКРУГЛ(135,46456;0)";
|
| Формула в ячейки |
Лист.Cells(Сч,Сч2).FormulaLocal = "=ОКРУГЛ(135,46456;0)";
|
| Формула в ячейки |
Лист.Cells(Сч,Сч2).FormulaLocal = "=ОКРУГЛ(135,46456;0)";
|
| Формула в ячейки |
Лист.Cells(Сч,Сч2).FormulaLocal = "=ОКРУГЛ(135,46456;0)";
|
| Функция для получения ширины колонки Excel Спасибо пользователю goodwill |
&НаКлиенте
Функция ПолучитьШиринуКолнкиЭксель(ПараметрШиринаВПикселях)
Если ПараметрШиринаВПикселях > 9 Тогда
ШиринаВСимволах = (ПараметрШиринаВПикселях/0.75-5)/7;
Иначе
ШиринаВСимволах = ПараметрШиринаВПикселях/9;
КонецЕсли;
Возврат ШиринаВСимволах;
КонецФункции
Лист.Columns("A").ColumnWidth = ПолучитьШиринуКолнкиЭксель(РазмерШиринаВПикселях);
|
| Разрешить перенос слов в ячейке Спасибо пользователю roofless |
Лист.Cells(1, 1).WrapText = 1;
|
| Группировки данных на листе Спасибо пользователю dr-wit, ignor |
1. Развернуть все группы (строки и колонки):
Excel.ActiveSheet.Outline.ShowLevels(3, 3);
2. Сернуть все группы (строки и колонки) до первого уровня:
Excel.ActiveSheet.Outline.ShowLevels(1, 1);
3. Вернуть глубину дерева
Лист.Rows(Инд).OutlineLevel
|
При работе с Excel мы оперируем столбцами как числом (Например, 1 столбец), а у Excel адресация столбцов производится с помощью символов. И когда нам нужно отредактировать формулу, то нам нужно номер столбца преобразовать в символ. В таких случаях вам пригодится эта функция.
Функция ПреобразоватьНомерСтолбцаВФорматExcel(Столбец)
стСтолбец = "";
А = Окр(Столбец/27,0);
В = Столбец - (А*26);
Если А>0 Тогда
стСтолбец = Символ(А+64);
КонецЕсли;
Если В>0 Тогда
стСтолбец = стСтолбец + Символ(В+64);
КонецЕсли;
Возврат стСтолбец;
КонецФункции
2. Работа с Excel через ТабличныйДокумент 1С
С помощью данного метода можно и загружать из Excel и выгружать в Excel. Но на мой взгляд этот метод идепально подходит когда вам необходимо посто сохранить информацию в Excel без дальнейшей манипуляции.
Итак, приступим: загрузка из Excel:
1. Загружаем файл Excel в табличный документ
//Файл - это файл Excel
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(Файл, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = ОписаниеОшибки();
Сообщение.Сообщить();
Возврат Неопределено;
КонецПопытки;
2. Производим манипуляции уже с ТабличнымДокументом
//Создадим ТЗ куда будем собирать инфу
Таб = Новый ТаблицаЗначений;
Таб.Колонки.Добавить("Номенклатура",Новый ОписаниеТипов("Строка"));
Таб.Колонки.Добавить("Количество",Новый ОписаниеТипов("Число"));
Таб.Колонки.Добавить("Цена",Новый ОписаниеТипов("Число"));
//Определяем количество строк
КолСтр = ТабДок.ВысотаТаблицы;
Для Сч = 2 по КолСтр Цикл
Попытка
ТБ = Таб.Добавить();
//Обращаемся к ячейки и забираем данные
ТБ.Номенклатура = Строка(ТабДок.ПолучитьОбласть("R" + Формат(Сч, "ЧГ=0") + "C" + 1).ТекущаяОбласть.Текст);
ТБ.Количество = Число(ТабДок.ПолучитьОбласть("R" + Формат(Сч, "ЧГ=0") + "C" + 2).ТекущаяОбласть.Текст);
ТБ.Цена = Число(ТабДок.ПолучитьОбласть("R" + Формат(Сч, "ЧГ=0") + "C" + 3).ТекущаяОбласть.Текст);
Исключение
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = "Не удалось загрузить строку "+Строка(Сч);
Сообщение.Сообщить();
КонецПопытки;
КонецЦикла;
Давайте теперь разберем сохранение в Excel с помощью данного метода:
Тут все очень просто сначала мы формируем обычный Табличный документ и затем записываем его в Excel
//ТабДок - Табличный документ
ТабДок.Записать(Объект.ИмяФайла,ТипФайлаТабличногоДокумента.XLSX);
2. Работа с Excel ADODB
Выражаю особую благодарность коллеги Fragster за хороший комментарий
Данный метод позволяет работать с Excel через ODBC и имеет ряд преимуществ:
- Не требует установки самой Excel, необходима лишь установить ODBC. Но как правило он уже установлен. Это особенность позволяет работать на стороне сервера без дополнительных установок Excel.
- Позволяет работать с таблицой Excel как с БД и строить к ней запросы на T-SQL. Таким образом мы можем делать отборы еще на этапе чтения данных и другие преимущества что дает Т-SQL. Что на мой взгляд огромный плюс.
Подключение к ADO
Функция СоединитьсяСADO(ИмяФайла)
//Поз = СтрНайти(ИмяФайла,"\",НаправлениеПоиска.СКонца);
Поз = 0;
Найден = 1;
ТмпСтр = ИмяФайла;
Пока Найден <> 0 Цикл
Найден = Найти(ТмпСтр,"\");
ТмпСтр = Прав(ТмпСтр,СтрДлина(ТмпСтр) - Найден);
Если Найден <> 0 Тогда
Поз = Найден;
КонецЕсли;
КонецЦикла;
Путь = Лев(ИмяФайла,Поз-1);
Данные = Новый Структура;
СтрокаСоединения = "
| Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+СокрЛП(ИмяФайла)+";
| Extended Properties=""Excel 12.0 Xml;HDR=YES""";
Соединение = Новый COMОбъект("ADODB.Connection");
Попытка
Соединение.Open(СтрокаСоединения);
Исключение
Сообщить ("Не возможно подключится к Microsoft Excel Driver!!!
|Возможно файл ["+ИмяФайла+"] открыт другим пользователем.");
Возврат Неопределено;
КонецПопытки;
Данные.Вставить("Соединение",Соединение);
Возврат Данные;
КонецФункции
Строка подключения зависит от версии ODBC. И вызывает наибольшие трудности при подключение поэтомя я рекомендую ее сгенерировать на сайте http://www.connectionstrings.com
Отключение от ADO
Процедура ОтключитьсяОтADO(Подключение)
Попытка
Подключение["Соединение"].Close();
Подключение = Неопределено;
Исключение
КонецПопытки;
КонецПроцедуры
Выполнение запроса
Функция ВыполнитьЗапросADO(ТекстЗапроса,Подключение)
Попытка
Записи = Новый COMОбъект("ADODB.Recordset");
Исключение
Сообщить ("Не удалось Создать к Microsoft Excel Driver!!!");
Возврат Неопределено;
КонецПопытки;
Попытка
Записи.Open(ТекстЗапроса, Подключение["Соединение"]);
Возврат Записи;
Исключение
Сообщить ("Проблемы с выполнением запроса");
КонецПопытки;
КонецФункции
Пример запроса:
//Сразу отбираем только не пустые номенклатуры
ТекстЗапроса = "SELECT * FROM [Лист1$] WHERE `Номенклатура`<>""""";
Запись в Excel тоже производится в виде запроса:
ТекстЗапроса = "
|INSERT INTO [Лист1$] (`Номенклатура`,`Количество`,`Цена`,`Сумма`) VALUES ('"
+ТБ.Номенклатура+"','"+ТБ.Количество+"','"+ТБ.Цена+"','"+ТБ.Сумма+"')";
Хочу отметить что наименование полей производится по первой строке в таблице
ADODB предоставляет ряд объектов, с которыми мы работаем
| ADODB.Connection предназначен для соединения с ADO |
|
| ADOX.Table для работы с таблицей |
|
| ADODB.Command Для выполнения комманд на языке T-SQL |
|
|
ADODB.Recordset Похож на ADODB.Command предназначен для выполнения запросов и обработки результата |
|
В файле продемонстрированны оба варианта работы с запросами.
На этом пока все. По возможности буду дополнять статью :)
В архиве находится обработка, которая демонстрирует все описанные и другие возможности при работе с Excel.
И тренировочный файл Excel.
Готовое решение
Загрузка документов из Excel в 1С одним нажатием!
С помощью данной обработки вы сможете загружать любые товарные документы буквально в один клик! Для загрузки необходимо просто выбрать файлы Excel и нажать кнопку "Загрузить". Простейший интерфейс и мощный механизм поиска номенклатуры. Распознает документы любой формы (УПД, ТОРГ-12, заказ, отчет комиссионера и т.д.)
