Выгрузка данных в Excel без «геморроя». Один, но принципиально важный момент.

29.03.14

Интеграция - Загрузка и выгрузка в Excel

Все, кто делал выгрузку в Excel, рано или поздно столкнулся с такой проблемой, как преобразование «строк из цифровых символов» в «числа». Например код «00001» (строка) или «001 » (строка) в результате выгрузки преобразуются в числа «1» (число) и «1» (число). Хотя, на экране, Excel будет отображать «00001» и «00001». Но, если мы активизируем любую из двух ячеек и посмотрим в строку формул, то увидим реальное содержимое – «1» (число). В этой статье будет рассмотрено то, как решить этот момент и где в последующем это пригодится.

Рассмотрим результат подобной выгрузки.

На экране будет отображаться «00001», а по сути это будет число.

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

Одним из вариантов для решения этой проблемы является добавление строкового символа в начале строки, например пробел. Но тогда при чтении этих данных придётся эти данные дополнительно обработать, например с помощью функции СокрЛП().

Перед тем, как описать другой вариант выгрузки подобных данных, который считаю наиболее удобным и корректным,  необходимо понимать то, что когда Excel получает данные, то он автоматически  и без нашего ведома преобразует «строки из цифровых символов» в «числа».

Excel, это программа. Какие в неё алгоритмы и механизмы заложили, так она и работает. И создавали её такие же программисты, только на другом языке. Неужели они не предвидели подобную ситуацию? Логическое предположение, говорит, что «Да».

Басня "ЛАРЧИК" - одна из первых оригинальных басен Крылова.

Случается не редко нам
И труд и мудрость видеть там,
Где стоит только догадаться
За дело просто взяться.
К кому-то принесли от мастера Ларец.
Отделкой, чистотой Ларец в глаза кидался;
Ну, всякий Ларчиком прекрасным любовался.
Вот входит в комнату механики мудрец.
Взглянув на Ларчик, он сказал: "Ларец с секретом,
Так, он и без замка;
А я берусь открыть; да, да, уверен в этом;
Не смейтесь так исподтишка!
Я отыщу секрет и Ларчик вам открою:
В механике и я чего-нибудь да стою.
Вот за Ларец принялся он:
Вертит его со всех сторон
И голову свою ломает;
То гвоздик, то другой, то скобку пожимает.
Тут, глядя на него, иной
Качает головой;
Те шепчутся, а те смеются меж собой.
В ушах лишь только отдается:
"Не тут, не так, не там!" Механик пуще рвется.
Потел, потел; но, наконец, устал,

От Ларчика отстал
И, как открыть его, никак не догадался:
А Ларчик просто открывался.

 

И вот, мы подошли к самой сути:

«'»  - символ апострофа.

Для того, чтобы отменить попытку преобразовать строку в число, достаточно в начале выгружаемой строки добавить символ апострофа. Это и будет тем самым сигналом для Excel. Теперь, выделив ячейку с выгруженной строкой, в строке формул мы увидим «'00001».

А что теперь делать с апострофом? А ничего делать не надо. Excel, при всех операциях, будет работать с этими данными как со строкой «00001».

Одно из самых важных преимуществ данного способа выгрузки является то, что и при считывании этого значения из внешних программ будет передано значение «00001», т.е. строка без апострофа. Необходимость дополнительной обработки (СокрЛП) отпадает.

А вообще, для чего это нужно?

Когда приходится постоянно сталкиваться с загрузками и выгрузками через книги Excel возникает желание автоматизировать часть работы при помощи универсальных функций. Мне пришлось столкнуться с одной такой функцией, но именно из за проблем описанных ранее, все загружаемые данные преобразовывались в строку. И при чтении, необходимо было конвертировать строки в даты, числа и булево. Что является явным недостатком и неудобством при использовании универсальных средств.

В следующей статье будет предложена универсальная обработка для экспорта данных из 1С в Excel.  Мне встречались обработки, которые для получения данных из Excel используют межплатформенного объекта COMSafeArray, что в разы увеличивает чтение данных. Преимущество предлагаемой обработки в том, что она выгружает данные именно с использованием этого же самого COMSafeArray и тоже, скорость выгрузки значительно увеличивается. Её необходимо будет «допилить». Всё, что необходимо сделать, так это изменить в ней запрос и передать в универсальную функцию выгрузку запроса или таблицу значений. У вас появится инструмент на все случаи подобных выгрузок. Все нюансы, с которыми мне пришлось столкнуться при выгрузке, будут описаны в следующей статье.

Excel Экспорт Выгрузка данных

См. также

SALE! 10%

Загрузка номенклатуры из Excel в УТ11, КА 2, ERP 2, Розница 2. Дополнительные реквизиты и сведения, характеристики, картинки, цены, остатки

Загрузка и выгрузка в Excel Розничная торговля Логистика, склад и ТМЦ Ценообразование, анализ цен Прайсы Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

Загрузка из файлов xls, xlsx, ods, csv, mxl в УТ11, КА 2, ERP 2, Розница 2. Задействованы все возможности конфигурации - заполнение реквизитов номенклатуры, дополнительных реквизитов и сведений, характеристики, доп.реквизиты и сведения характеристик. Дополнительные обработки для расширения возможностей.

10560 9504 руб.

29.10.2014    210140    620    524    

439

Загрузка номенклатуры c картинками (несколько потоков одновременно) и сопутствующими данными в базу и любые документы из yml, xls, xlsx, xlsm, ods, ots, csv для УТ 10.3, УТ 11 (все), БП 3, КА 2, ERP 2, УНФ 1.6/3.0, Розница 2

Загрузка и выгрузка в Excel Логистика, склад и ТМЦ Ценообразование, анализ цен Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Платные (руб)

Эволюция не стоит на месте - новая удобная версия функциональной обработки для Вашего бизнеса! Что же Вы получаете? Удобный и интуитивно понятный интерфейс с 3-мя этапами работы. 2 режима - автоматический и ручной. Чтение XLSX, XLSM, CSV, XML/YML форматов без офиса, на любом сервере! Визуальное связывание колонок файла и реквизитов простым перетаскиванием колонок. Создание или обновление номенклатуры с иерархией, характеристик, доп. реквизитов, упаковок, загрузка практически неограниченного количества картинок на одну номенклатуру (с возможностью загрузки в несколько потоков одновременно), с хранением в томах или в базе. Загрузка номенклатуры поставщиков или поиск по их данным номенклатуры. Загрузка доп. реквизитов в характеристики. Загрузка штрихкодов с генерацией новых. Создание элементов справочников и ПВХ "на лету" для выбранных реквизитов. (Обновление от 11.12.2023, версия 9.5 - 9.9)

13200 руб.

20.11.2015    150702    367    375    

501

Маркетплейсный загрузчик для 12-ти маркетплейсов в "БП 3", "УТ 11", "КА 2", ERP, УНФ

Загрузка и выгрузка в Excel Маркетплейсы Платформа 1С v8.3 Бухгалтерский учет 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Россия Бухгалтерский учет Управленческий учет Платные (руб)

Реальный помощник, с помощью которого Вы сделаете необходимые документы для Wildberries, OZON, ЯндексМаркет, Мегамаркет, Aliexpress, "Детский мир", Казань-Экспресс, "Леруа Мерлен", ЭНФАНТА (Акушерство), ЛаМода, Летуаль, "Твой дом" в документы "Отчет комиссионера (агента) о продажах" и другие, может работать в "Бухгалтерия 3", "Бухгалтерия 3 КОРП", УТ 11, УНФ, КА 2, ERP. Возможность подключить любые маркетплейсы. Анализ продаж ОЗОН. 30 дней БЕСПЛАТНОГО пользования!

1800 руб.

12.08.2021    31889    226    63    

117

SALE! 20%

Загрузка документов и номенклатуры из Excel в 1С "одним нажатием": УПД, ТОРГ-12, отчеты маркетплейсов, заказы, счета, прайсы

Загрузка и выгрузка в Excel Оптовая торговля Платформа 1С v8.3 Управляемые формы Платформа 1C v8.2 Конфигурации 1cv8 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная обработка для загрузки документов из Excel в 1С. Не требует указания параметров (номера колонок, номер первой строки таблицы и т.д.) и предварительной настройки. Просто выбираете файл Excel, документ 1С и нажимаете кнопку "Загрузить". Обработка сама находит таблицу в файле Excel, необходимые для загрузки данные в ней (номенклатура, количество, НДС, цена, сумма) и загружает ее в 1С. Вместе с номенклатурой может найти контрагента, номер и дату документа, штрих-коды, серии ГТД, страну и т.д. Распознает документы ЛЮБОЙ ФОРМЫ (УПД, ТОРГ-12, заказ, отчет комиссионера и т.д.). Не требует MS Office. Для поиска таблиц используются методы эвристического поиска. Загружает только то, что нужно, т.е. пропускает повторы шапки таблицы, заголовки, промежуточные итоги, подписи и т.д. Содержит модуль работы с электронной почтой и api-загрузчик отчетов о продажах маркетплейсов.

5000 4000 руб.

09.11.2016    214898    925    886    

939

Загрузка данных отчета о реализации товаров из Excel файла СберМегаМаркет

Загрузка и выгрузка в Excel Маркетплейсы Платформа 1С v8.3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Загрузка данных отчета о реализации товаров из сервиса СберМегаМаркет для конфигурации: Бухгалтерия предприятия, редакция 3.0; Управление торговлей, редакция 11 и Управление нашей фирмой, редакция 3.0 в документ "Отчет комиссионера (агента) о продажах".

4800 руб.

07.06.2022    15070    79    0    

60

SALE! 30%

Распознавание и загрузка сканов в 1С "одним нажатием": УПД, ТОРГ-12, накладные, счета, номенклатура, заказы и т.д.

Загрузка и выгрузка в Excel Документооборот и делопроизводство (СЭД) Учет документов Распознавание документов и образов Управляемые формы 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление холдингом 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная программа для распознавания ЛЮБЫХ СКАНОВ ИЛИ ФОТО ТОВАРНЫХ ДОКУМЕНТОВ в 1С. Не требует указания параметров и предварительной настройки. Просто выбираете файл (PDF, JPG, DOC, XLS, HTML и т.д.) выбираете документ 1С и нажимаете кнопку "Распознать и загрузить".

6000 5520 руб.

04.06.2019    101357    296    173    

312
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. AlexandrIII 29.03.14 10:50 Сейчас в теме
Для одного апострофа много букв. ИМХО :)
Но в целом вроде норм.
Зеленоград; Evil Beaver; Азбука Морзе; +3 Ответить
2. akor77 197 29.03.14 11:08 Сейчас в теме
(1) AlexandrIII, Хочется обратить внимание на то, что сколько времени было потрачено и изменений в коде, прежде чем выйти на этот самый апостроф. Мал золотник, да дорог. В статье описывается не сам апостроф (разработчики могли использовать какой-нибудь другой символ), а само поведение Excel. Апостроф это только "ключ-инструкция" к механизму.
К статье добавлю, что при необходимости создать 'файл для загрузки/выгрузки данных в формате Excel', то это можно сделать с помощью консоли отчётов (Консоль сохраняет результат в файл формата Excel). В результате, строки из чисел сам Excel так же преобразует в числа. Добавьте в запросе к каждому текстовому полю апостроф (""'"" + Справочник.Номенклатура.Код), в консоли апостроф будет виден, а в файле Excel - будет Вам счастье!
sergiobargio1; +1 Ответить
8. sergiobargio1 118 02.04.14 15:35 Сейчас в теме
(2) большое Вам спасибо. для меня проблема была актуальна. Ранее справлялись таким образом: при сохранении отчёта из "1С" выбирали тип сохраняемого файла "Лист Excel95(*xls)". В этом случае все ведущие нолики сохраняются фактически, а не только визуально в ячейках. Затем его открывали уже в Excel и сохраняли как обычный xlsx-файл. Только, если перед сохранением поставить курсор на такую ячейку , то при сохранении всё таки ведущие нолики "съедались".
Пожалуйста, подскажите, каким образом можно решить проблему, что бы при сохранении отчёта, написанного на СКД, в формат *.xlsx или *.xls можно было присваивать имя сохраняемому листу? Приходиться после открытия отчёта в excel идти в параметры выставлять "галочку" что бы отображалась закладка с названиями листов, за тем растягивать её, иначе она не видна, а потом присваивать ей имя.
Спасибо.
9. akor77 197 02.04.14 17:18 Сейчас в теме
(8) sergiobargio1,
Excel.ActiveWindow.DisplayWorkbookTabs = -1; // Отобразит ярлычки
Excel.ActiveWindow.TabRatio = 0.25; // Сдвинет левый край горизонтальной полосы прокрутки.
Взял отсюда: http://infostart.ru/public/20438/
Обратите внимание!
Операции выполняются над открытым окном! Предполагаю, что при простом сохранении в файл этот код не поможет. Попробуйте, расскажите.
sergiobargio1; +1 Ответить
10. akor77 197 02.04.14 19:32 Сейчас в теме
(8) sergiobargio1, Здесь описано как записать макрос, который открывает ярлычки. Инструкция ориентирована для бухгалтеров, экономистов и прочих пользователей. http://infostart.ru/public/270640/
3. quebracho 22 30.03.14 12:12 Сейчас в теме
В одной из типовых конфигураций, в коде сначала задают формат ячейке, потом сгружают.

РабочийЛистКниги.Cells(1,1).NumberFormat = "@"; // текстовый формат
РабочийЛистКниги.Cells(1,2).NumberFormat = "0"; // числовой формат
РабочийЛистКниги.Cells(1,3).NumberFormat = ""; // Формат даты Selection.NumberFormat = "m/d/yyyy"

Для строки 000001 , при установке в ячейке текстового формата и в формуле этой ячейки будет 000001 . При открытии в экселе формат у ячейки будет текстовый.
Torin57; LiebeMein; Xytras; kazann; Ivan_0110; Скользящий; iZhenius; qwed557; FractalizeR; sergiobargio1; detec; AlexanderKai; Операция1Ы; ravsan; wolfsoft; +15 Ответить
4. akor77 197 30.03.14 15:41 Сейчас в теме
(3) quebracho, вв Вашем примере предлагается форматировать каждую ячейку или область ячеек. Потом в каждой ячейке по отдельности устанавливать значение. Этот вариант имеет основание для использования небольших таблиц или при создании особого варианта выгрузки. В интернете выложены различные обработки выгрузки и загрузки через Excel. Выгрузка, где используется обращение к каждой ячейке поотдельности (РабочийЛистКниги.Cells(1,2).Value = 5 или .Text = "Да") очень медленны, т.к. при каждом обращении (чтении/записи) к ячейке происходит запрос. В этом весь минус такого подхода. При поиске другого решения, я столкнулся с ADO. Но, в организации для которой мне пришлось делать обработку выгрузки и загрузки нельзя было установить соответствующий драйвер (запрет на излишний софт), по этому пришлось искать другое решение. Сейчас уже много обработок по считыванию данных из Excel с помощью COMSafeArray, этот объект не зависит ни от какого софта и драйверов, в этом его большой плюс. Но выгрузка с использованием данного объекта мне ещё не встречалась. По этому я начал изучать этот объект, проводить эксперименты, что в результате позволило создать универсальную функцию для выгрузки данных с помощью этого массива. Задача стоит максимально быстро слить данные с помощью COMSafeArray, а на какие при этом натыкаешься подводные камни, именно об одном из них написана данная статья. Почему отдельной статьёй? Т.к. апостроф можно использовать при различных вариантах выгрузки и везде он даст один и тот-же результат.
w-divin; AlexanderKai; +2 Ответить
5. quebracho 22 30.03.14 15:56 Сейчас в теме
6. rasswet 82 02.04.14 08:39 Сейчас в теме
(4) по моему я как раз делал выгрузку через COMSafeArray
ВсегоСтрок = ТаблицаРезультата.Количество();
	ВсегоКолонок = ТаблицаРезультата.Колонки.Количество();
	
	МассивКОМ = Новый COMSafeArray("VT_VARIANT", ВсегоКолонок, ВсегоСтрок);
	
	Для индСтрок = 0 По ТаблицаРезультата.Количество() - 1 Цикл
		
		СтрокаТаблицы = ТаблицаРезультата[индСтрок];
		
		Для индКолонок = 0 По ВсегоКолонок - 1 Цикл
			МассивКОМ.SetValue(индКолонок, индСтрок, СтрокаТаблицы[индКолонок]);
		КонецЦикла; 
		
	КонецЦикла; 
......
лист01_.Range(лист01_.Cells(НачатьСоСтроки,НачатьСоСтолбца), лист01_.Cells(ЗакончитьНаСтроке,ЗакончитьНаСтолбце)).Value = МассивКОМ;  
Показать

но там были проблемы. при такой схеме при наличии определенных данных в ячейках (либо определенном значении ячейки) выгрузка падала в ошибку.
7. MaiorovYury 10 02.04.14 10:52 Сейчас в теме
А о каком методе загрузки тут идет речь?
Если через com объект Excel.Application, то при чтении Лист.Cells(а,2).Value - получим число 1, а при чтении Лист.Cells(а,2).Text - получим строку 0001
И ни с какими апострофами заморачиваться не надо
user640247; QWEP; +2 Ответить
20. user640247 24.06.20 21:53 Сейчас в теме
(7)
Text

Спасибо. При загрузке в 1с получилось. А при выгрузке из 1 использую апостроф.
11. echo77 1868 02.12.15 19:38 Сейчас в теме
Вот такой вариант решения проблемы лидирующих нулей мне больше всего понравился: http://1cstyle.ru/blog/8898/

Кратко-суть: в ячейке, которую нужно сохранить как текст ставим признак Содержит значение и в качестве типа значения поставить Строка
wmonref1; BackinSoda; Vlasenko.Oleg; +3 Ответить
12. kame-lana 03.12.15 20:39 Сейчас в теме
(11) echo77, не работает! выводит в общий формат...
13. echo77 1868 04.12.15 08:15 Сейчас в теме
(12) kame-lana, да, формат общий - но лидирующие нули не обрезаются
14. Vortigaunt 96 08.04.16 14:06 Сейчас в теме
Я попробовал ставить и пробел спереди и апостроф. Не помогает.
Если ставлю пробел то выгружается ровно так же, как и без него.
Если ставлю апостроф, то этот же апостроф вижу и в ячейке и при загрузке из такого табличного документа этот апостроф тоже вытягивается.

Я заполняю в 1с табличный документ из макета, а затем сохраняю его в формате XLS. Что я делаю не так?
15. akor77 197 11.04.16 09:51 Сейчас в теме
(14) Vortigaunt, апостроф ставится в данные, которые выгружаются в Excel. Если их грузить в табличный документ 1С, то скорее всего этот апостроф так и останется, я эти варианты не исследовал. Попробуй из своего созданного табличного документа 1С копи-пастом скопировать данные в Excel, возможно Excel примет эти данные корректно. Пробуй.
Vortigaunt; +1 Ответить
16. deme 4 26.05.16 14:31 Сейчас в теме
вопрос можно? может, не совсем в тему. А можно ли перенести строки отчета mxl в готовую таблицу xls? Так сказать, внедрить. В xls уже диаграммы нарисованы, все красиво.
17. echo77 1868 29.05.16 07:15 Сейчас в теме
18. deme 4 29.05.16 11:33 Сейчас в теме
дд. Уточню вопрос: как перенести строки отчета mxl в готовую таблицу xls?
19. Dermidont 11 28.02.17 15:40 Сейчас в теме
Гошан, ты чё, там же просто. Открывай свой xls файл через ком соединение, выбирай лист и вписывай в него что хочешь.

21. MasI 9 18.09.20 17:54 Сейчас в теме
спасибо большое, очень помогло)
22. Sarkikker 11.06.21 14:10 Сейчас в теме
Если это сохранение с табличного документа, а не программное из какой-нибудь выборки, то на ИТС есть вариант решения проблемы:

При сохранении табличного документа в формате листа Excel необходимо учитывать следующие особенности:

● Текст в ячейке преобразуется в число Microsoft Excel только если для ячейки установлено горизонтальное выравнивание вправо, в противном случае содержимое ячейки будет сохранено как текст.
Programmer_Ekatetina; VictorRGB2; imaster; +3 Ответить
Оставьте свое сообщение