Какой столбец нужно обязательно указывать при загрузке услуг из excel
После манипуляции с файлом необходимо его закрыть. В противном случае он останется захваченным в системе, и мы не сможем его использовать в других приложениях.
Клиент-Серверный вариант в управляемых формах.
1. Считываем данные из файла в массив из структур и затем этот массив передаем на сервер для дальнейших манипуляций с данными.
2-ой способ - это передать файл Excel на сервер через хранилище данных и далее работать с ним уже на сервере.
Очень часто приходится работать с очень большими файлами Excel, и его обработка путем перебора строк занимает огромное количество времени.
В таких случаех удобно в одно действие загрузить всю таблицу в массив и в потом работать уже непосредственно с массивом.
На выходе получаем двумерный массив, который содержит все данные указанного листа Excel
Полезные функции при работе с Excel:
Устанавливает видимость Excel при работе |
Добавление новой книги в файл Excel |
Сохранение книги Excel |
Добавление нового листа к книге |
Переименование листа |
Изменение маштаба листа |
Изменение ориентации листа |
Отступы листа |
Обращение к ячейки чтение/запись |
Обращение к области ячеек |
Очень часто при чтении или записи значений в Excel ставятся лишние пробелы в числе, например, вместо 1502 он читает как 1 502 и в дальнейшем это значение не приводится к числу. Эту проблему можно решить заменой. |
Объединение ячеек |
Работа с именованными ячейками в Excel |
Удаление ячейки |
Удаление области ячеек |
Обращение к строке |
Изменение ширины колонки |
Обращение к колонке |
Удаление Строки |
Фон ячейки / области / Строки / |
Функция переводит цвет из формата RGB в формат Excel |
Управление шрифтом в ячейки/строке/области |
Р азрешает переносить по словам в ячейке |
Управление рамкой ячейки |
Устанавливаем формат ячейки |
Формула в ячейки |
Формула в ячейки |
Формула в ячейки |
Формула в ячейки |
Функция для получения ширины колонки Excel Спасибо пользователю goodwill |
Разрешить перенос слов в ячейке Спасибо пользователю roofless |
Группировки данных на листе Спасибо пользователю dr-wit, ignor |
При работе с Excel мы оперируем столбцами как числом (Например, 1 столбец), а у Excel адресация столбцов производится с помощью символов. И когда нам нужно отредактировать формулу, то нам нужно номер столбца преобразовать в символ. В таких случаях вам пригодится эта функция.
2. Работа с Excel через ТабличныйДокумент 1С
С помощью данного метода можно и загружать из Excel и выгружать в Excel. Но на мой взгляд этот метод идепально подходит когда вам необходимо посто сохранить информацию в Excel без дальнейшей манипуляции.
Итак, приступим: загрузка из Excel:
1. Загружаем файл Excel в табличный документ
2. Производим манипуляции уже с ТабличнымДокументом
Давайте теперь разберем сохранение в Excel с помощью данного метода:
Тут все очень просто сначала мы формируем обычный Табличный документ и затем записываем его в Excel
2. Работа с Excel ADODB
Выражаю особую благодарность коллеги Fragster за хороший комментарий
Данный метод позволяет работать с Excel через ODBC и имеет ряд преимуществ:
- Не требует установки самой Excel, необходима лишь установить ODBC. Но как правило он уже установлен. Это особенность позволяет работать на стороне сервера без дополнительных установок Excel.
- Позволяет работать с таблицой Excel как с БД и строить к ней запросы на T-SQL. Таким образом мы можем делать отборы еще на этапе чтения данных и другие преимущества что дает Т-SQL. Что на мой взгляд огромный плюс.
Подключение к ADO
Отключение от ADO
Выполнение запроса
Пример запроса:
Запись в Excel тоже производится в виде запроса:
Хочу отметить что наименование полей производится по первой строке в таблице
ADODB предоставляет ряд объектов, с которыми мы работаем
ADODB.Recordset
В файле продемонстрированны оба варианта работы с запросами.
На этом пока все. По возможности буду дополнять статью :)
В архиве находится обработка, которая демонстрирует все описанные и другие возможности при работе с Excel.
Если у вас уже есть привычный и удобный каталог в Excel, его без потерь можно загрузить в СБИС.
При импорте каталога не загружаются данные о количестве. Чтобы зафиксировать в СБИС количество по каждой позиции, загрузите начальные остатки.
Рассмотрим примеры настройки для каталогов, где разделы содержатся в отдельной колонке, в отдельной строке и в группировке строк.
Настройка «Разделы в отдельной колонке» установлена в СБИС по умолчанию. Заполните параметры загрузки и укажите, какие данные содержатся в столбцах.
Пример
Задача: загрузить код номенклатуры, раздел, наименование, единицу измерения, упаковку и количество в упаковке.
-
параметры импорта (порядок загрузки, тип номенклатуры и номер строки, с которой разбирать данные);
- убедитесь, что в поле «Раздел содержится» выбрано «в отдельной колонке»;
- в колонке нажмите «Не задано» и укажите, какие данные в ней находятся;
Выберите этот параметр, если в вашем файле разделы и подразделы указаны в отдельных строках. В блоке разбора таблицы появится дополнительная строка, где нужно отметить колонку с названием раздела. Заполните параметры загрузки и во второй строке выберите, какие данные вы хотите импортировать.
Подразделы в файле должны быть указаны в отдельных столбцах.
Пример №1
Задача: загрузить код, наименование, единицу измерения.
-
параметры импорта (порядок загрузки, тип номенклатуры и номер строки, с которой разбирать данные);
- в поле «Раздел содержится» выберите «в отдельной строке»;
- название раздела в файле находится в колонке «Код», значит здесь в первой строке нажмите «Не задано» и выберите «Раздел»;
- название подраздела 1 уровня — в колонке «Наименования», значит здесь в первой строке укажите «Подраздел (ур.1)» и так далее;
- во второй строке выберите, какие данные находятся в колонках;
- в столбцах, из которых вы не будете загружать информацию, оставьте «Не задано».
Пример №2
Задача: загрузить код, наименование, единицу измерения.
-
параметры импорта (порядок загрузки, тип номенклатуры и номер строки, с которой разбирать данные);
- в поле «Раздел содержится» выберите «в отдельной строке»;
- название раздела в файле находится в колонке «Наименование», значит здесь в первой строке нажмите «Не задано» и укажите «Раздел». В этом случае в файле у раздела не должно быть кода;
- во второй строке выберите, какие данные находятся в колонках;
- в столбцах, из которых вы не будете загружать информацию, оставьте «Не задано».
Пример №3
Задача: загрузить код, наименование, единицу измерения, штрихкод.
-
параметры импорта (порядок загрузки, тип номенклатуры и номер строки, с которой разбирать данные);
- в поле «Раздел содержится» выберите «в отдельной строке»;
- название раздела в файле находится в колонке «Код», значит здесь в первой строке нажмите «Не задано» и укажите «Раздел»;
- во второй строке выберите, какие данные находятся в колонках;
- в столбцах, из которых вы не будете загружать информацию, оставьте «Не задано».
В файле коды повторяются, поэтому в СБИС загрузится одна карточка — последняя по списку. В нашем примере «Блинница».
Пример №4
Задача: загрузить код, наименование, единицу измерения, штрихкод, дополнительный код.
-
параметры импорта (порядок загрузки, тип номенклатуры и номер строки, с которой разбирать данные);
- в поле «Раздел содержится» выберите «в отдельной строке»;
- название раздела в файле находится в колонке «Код», значит здесь в первой строке нажмите «Не задано» и укажите «Раздел»;
- во второй строке выберите, какие данные находятся в колонках;
- в столбцах, из которых вы не будете загружать информацию, оставьте «Не задано».
В файле у номенклатуры указан дополнительный код, он не уникальный и может содержать любые значения.
Этот параметр используется для импорта файлов с иерархией. Заполните параметры загрузки и выберите, какие данные вы хотите импортировать.
Пример №1
Задача: загрузить код, артикул, наименование, единицу измерения.
-
параметры импорта (порядок загрузки, тип номенклатуры и номер строки, с которой разбирать данные);
- в поле «Раздел содержится» выберите «в группировке строк»;
- в файле название раздела и номенклатур находится в одной и той же колонке, поэтому в настройке колонок кликните «Не задано» и выберите «Наименование»;
- укажите, какие данные находятся в остальных колонках;
- в столбцах, из которых вы не будете загружать информацию, оставьте «Не задано».
После того как настроена иерархия, можно загрузить файл. Структура каталога сохранится в СБИС.
При работе с таблицами Excel довольно часто приходится проводить отбор в них по определенному критерию или по нескольким условиям. В программе сделать это можно различными способами при помощи ряда инструментов. Давайте выясним, как произвести выборку в Экселе, используя разнообразные варианты.
Выполнение выборки
Выборка данных состоит в процедуре отбора из общего массива тех результатов, которые удовлетворяют заданным условиям, с последующим выводом их на листе отдельным списком или в исходном диапазоне.
Способ 1: применение расширенного автофильтра
Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.
-
Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр». Она размещается в блоке настроек «Редактирование». В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр».
- равно;
- не равно;
- больше;
- больше или равно;
- меньше.
При использовании любого из двух вышеуказанных методов фильтрация будет удалена, а результаты выборки – очищены. То есть, в таблице будет показан весь массив данных, которыми она располагает.
Способ 2: применение формулы массива
Сделать отбор можно также применив сложную формулу массива. В отличие от предыдущего варианта, данный метод предусматривает вывод результата в отдельную таблицу.
-
На том же листе создаем пустую таблицу с такими же наименованиями столбцов в шапке, что и у исходника.
Опять набираем сочетание клавиш Ctrl+Shift+Enter.
Теперь у нас имеется готовая выборка по указанному ограничению в отдельной надлежащим образом оформленной таблице.
Способ 3: выборка по нескольким условиям с помощью формулы
Так же, как и при использовании фильтра, с помощью формулы можно осуществлять выборку по нескольким условиям. Для примера возьмем всю ту же исходную таблицу, а также пустую таблицу, где будут выводиться результаты, с уже выполненным числовым и условным форматированием. Установим первым ограничением нижнюю границу отбора по выручке в 15000 рублей, а вторым условием верхнюю границу в 20000 рублей.
-
Вписываем в отдельном столбце граничные условия для выборки.
В последующие колонки вписываем точно такие же формулы, только изменив координаты сразу после наименования оператора ИНДЕКС на соответствующие нужным нам столбцам, по аналогии с предыдущим способом.
Способ 4: случайная выборка
В Экселе с помощью специальной формулы СЛЧИС можно также применять случайный отбор. Его требуется производить в некоторых случаях при работе с большим объемом данных, когда нужно представить общую картину без комплексного анализа всех данных массива.
-
Слева от таблицы пропускаем один столбец. В ячейке следующего столбца, которая находится напротив первой ячейки с данными таблицы, вписываем формулу:
Как видим, выборку в таблице Excel можно произвести, как с помощью автофильтра, так и применив специальные формулы. В первом случае результат будет выводиться в исходную таблицу, а во втором – в отдельную область. Имеется возможность производить отбор, как по одному условию, так и по нескольким. Кроме того, можно осуществлять случайную выборку, использовав функцию СЛЧИС.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Еще статьи по данной теме:
Все это конечно здорово.
Фильтр ставится и по другому быть не может.
Однако не понятно как работать с этими данными.
Допустим я дошел до пункта 6. и у меня набор выручек подходящих по условиям. теперь мне нужно сосчитать какова сумма выручек ЭТОЙ ВЫБОРКИ, какая среднедневная выручка ЭТОЙ ВЫБОРКИ и каков средний вес ЭТОЙ ВЫБОРКИ в общей сумме выручек.
При попытке высчитать сумму выручек ЭТОЙ ВЫБОРКИ — стандартными средствами( формула сумм)- получаем просто сумму ВСЕХ выручек.
о следующих шагах говорить вообще не имеет смысла тогда.
Спасибо очень занимательно, а то я вечно забываю как ставить фильтр в таблицах)
Загрузка данных из табличных документов, созданных в редакторе Microsoft Excel – довольно часто встречающаяся задача в работе любого программиста 1С. Программа эта широко распространена, любима пользователями и часто используется для хранения и обработки различного рода информации, будь то прайс-лист или данные по продажам за период.
Существует несколько различных методов работы с файлами Excel при загрузке данных в 1С, в том числе:
1. загрузка с помощью свойства Cells;
2. загрузка с помощью метода Range и последующим обращением к данным через метод GetValue();
3. загрузка с помощью метода Range и последующим обращением к данным через свойство Value;
4. загрузка с помощью технологии ADO [2] и использованием компоненты GameWithFire [3].
Рассмотрим вышеперечисленные методы подробнее.
1. Загрузка данных с помощью свойства Cells
Этот метод используется в обработке «ЗагрузкаДанныхИзТабличногоДокумента.epf», которая присутствует на диске ИТС, и основан он на обращении к объекту Excel.Application через технологию COM с последующим последовательным чтением данных в ячейках обращением к свойству Cells.
Значение = ExcelЛист.Cells(Row,Column).Text;
Собственно, здесь все достаточно просто:
а) подключаемся к Excel
б) получаем диапазон данных на листе:
в) последовательно считываем данные из файла:
Несомненно, такой способ гарантирует получение всех данных, содержащихся на листе Excel, однако он имеет весьма существенный недостаток – большую длительность работы при загрузке файлов больших объемов.
2. Загрузка данных с помощью метода Range и последующим обращением к данным через метод GetValue()
Этот метод так же основан на возможностях объекта Excel.Application, но, в отличие от загрузки с использованием свойства Cells, позволяет получить сразу все значения в виде двумерного массива. Работает следующим образом:
а) подключаемся к Excel (аналогично методу 1);
б) определяем диапазон используемых ячеек (аналогично методу 1)
в) считываем данные с помощью метода Range(начальнаяЯчейка, конечнаяЯчейка). Здесь начальная и конечная ячейки – это элементы свойства Cells, о котором шла речь выше.
В результате, в переменной Диапазон мы получим значение типа COMSafeArray, который представляет собой объектную оболочку над многомерным массивом SAFEARRAY. Иначе говоря, мы получаем массив, который, как известно, является областью памяти. А с областью в оперативной памяти работа всегда быстрее.
При этом надо учитывать, что массив этот многомерный. Для нашего случая это будет двумерный массив.
г) последовательно получаем значения из массива с помощью метода GetValue(столбец, строка)
3. Загрузка данных с помощью метода Range и последующим обращением к данным через свойство Value
Этот метод отличается от предыдущего только тем, что вместо работы с объектом типа COMSafeArray мы сразу получаем набор значений в виде стандартного массива 1С (тип Массив).
Также, в приведенном листинге вместо метода Range используется свойство UsedRange, которое, по сути, идентично значению, возвращаемому методом Range для всей используемой области листа (это свойство не годится, если нужно прочитать только какую-то часть ячеек, в отличие от метода Range).
а) подключаемся к Excel (как и в предыдущих случаях)
б) считываем данные используя свойство UsedRange и метод Выгрузить()
В результате, получаем двумерный массив типа Массив
в) последовательно получаем значения из массива
Еще одним преимуществом данного метода является то, что каждый элемент массива Данные представляет собой массив, содержащий значение одной колонки считанного файла. Тем самым, мы можем сразу получить все возможные значения определенной колонки в виде массива, просто обратившись к соответствующему элементу переменной Данные.
4. Загрузка данных с помощью технологии ADO и использованием компоненты GameWithFire
Данный способ использует технологию ADO [2], которая реализуется с помощью библиотеки ADOdb [4] и позволяет получать доступ к любым СУБД, реализующим технологию ODBC. Собственно, Excel попадает в данный список и, следовательно, мы можем получить данные, используя указанную технологию.
Также, для указанного метода используется компонента с оригинальным названием GameWithFire, которая перекладывает результат запроса через ADO в привычную таблицу значений.
Ниже приведен листинг части кода, который демонстрирует данную возможность. Пример во многом использует материал, описанный в источнике [1].
В результате, получаем таблицу значений ТЗ, которую можем обойти любым известным образом.
Заключение
В заключение, мы хотели бы привести таблицу сравнения скорости работы вышеописанных методов и рекомендации для использования.
Замеры производились на файле Excel с числом строк 16 000 и числом колонок 20. Таким образом, общее число ячеек в области составило 320 000. Значения в таблице говорят сами за себя.
Таблица 1. Время загрузки данных из Excel в 1С при использовании различных методов
После манипуляции с файлом необходимо его закрыть. В противном случае он останется захваченным в системе, и мы не сможем его использовать в других приложениях.
Клиент-Серверный вариант в управляемых формах.
1. Считываем данные из файла в массив из структур и затем этот массив передаем на сервер для дальнейших манипуляций с данными.
2-ой способ — это передать файл Excel на сервер через хранилище данных и далее работать с ним уже на сервере.
Очень часто приходится работать с очень большими файлами Excel, и его обработка путем перебора строк занимает огромное количество времени.
В таких случаех удобно в одно действие загрузить всю таблицу в массив и в потом работать уже непосредственно с массивом.
На выходе получаем двумерный массив, который содержит все данные указанного листа Excel
Полезные функции при работе с Excel:
Устанавливает видимость Excel при работе |
Добавление новой книги в файл Excel |
Сохранение книги Excel |
Добавление нового листа к книге |
Переименование листа |
Изменение маштаба листа |
Изменение ориентации листа |
Отступы листа |
Обращение к ячейки чтение/запись |
Обращение к области ячеек |
Очень часто при чтении или записи значений в Excel ставятся лишние пробелы в числе, например, вместо 1502 он читает как 1 502 и в дальнейшем это значение не приводится к числу. Эту проблему можно решить заменой. |
Объединение ячеек |
Работа с именованными ячейками в Excel |
Удаление ячейки |
Удаление области ячеек |
Обращение к строке |
Изменение ширины колонки |
Обращение к колонке |
Удаление Строки |
Фон ячейки / области / Строки / |
Функция переводит цвет из формата RGB в формат Excel |
Управление шрифтом в ячейки/строке/области |
Р азрешает переносить по словам в ячейке |
Управление рамкой ячейки |
Устанавливаем формат ячейки |
Формула в ячейки |
Формула в ячейки |
Формула в ячейки |
Формула в ячейки |
Функция для получения ширины колонки Excel Спасибо пользователю goodwill |
Разрешить перенос слов в ячейке Спасибо пользователю roofless |
Группировки данных на листе Спасибо пользователю dr-wit, ignor |
При работе с Excel мы оперируем столбцами как числом (Например, 1 столбец), а у Excel адресация столбцов производится с помощью символов. И когда нам нужно отредактировать формулу, то нам нужно номер столбца преобразовать в символ. В таких случаях вам пригодится эта функция.
2. Работа с Excel через ТабличныйДокумент 1С
С помощью данного метода можно и загружать из Excel и выгружать в Excel. Но на мой взгляд этот метод идепально подходит когда вам необходимо посто сохранить информацию в Excel без дальнейшей манипуляции.
Итак, приступим: загрузка из Excel:
1. Загружаем файл Excel в табличный документ
2. Производим манипуляции уже с ТабличнымДокументом
Давайте теперь разберем сохранение в Excel с помощью данного метода:
Тут все очень просто сначала мы формируем обычный Табличный документ и затем записываем его в Excel
2. Работа с Excel ADODB
Выражаю особую благодарность коллеги Fragster за хороший комментарий
Данный метод позволяет работать с Excel через ODBC и имеет ряд преимуществ:
- Не требует установки самой Excel, необходима лишь установить ODBC. Но как правило он уже установлен. Это особенность позволяет работать на стороне сервера без дополнительных установок Excel.
- Позволяет работать с таблицой Excel как с БД и строить к ней запросы на T-SQL. Таким образом мы можем делать отборы еще на этапе чтения данных и другие преимущества что дает Т-SQL. Что на мой взгляд огромный плюс.
Подключение к ADO
Отключение от ADO
Выполнение запроса
Пример запроса:
Запись в Excel тоже производится в виде запроса:
Хочу отметить что наименование полей производится по первой строке в таблице
ADODB предоставляет ряд объектов, с которыми мы работаем
ADODB.Recordset
В файле продемонстрированны оба варианта работы с запросами.
На этом пока все. По возможности буду дополнять статью 🙂
В архиве находится обработка, которая демонстрирует все описанные и другие возможности при работе с Excel.
Читайте также: