gifts2017

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

Опубликовал Александр Кораблев (akor77) в раздел Обмен - Загрузка и выгрузка в Excel

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Александр (AlexandrIII) 29.03.14 10:50
Для одного апострофа много букв. ИМХО :)
Но в целом вроде норм.
Зеленоград; Evil Beaver; Азбука Морзе; +3 Ответить 1
2. Александр Кораблев (akor77) 29.03.14 11:08
(1) AlexandrIII, Хочется обратить внимание на то, что сколько времени было потрачено и изменений в коде, прежде чем выйти на этот самый апостроф. Мал золотник, да дорог. В статье описывается не сам апостроф (разработчики могли использовать какой-нибудь другой символ), а само поведение Excel. Апостроф это только "ключ-инструкция" к механизму.
К статье добавлю, что при необходимости создать 'файл для загрузки/выгрузки данных в формате Excel', то это можно сделать с помощью консоли отчётов (Консоль сохраняет результат в файл формата Excel). В результате, строки из чисел сам Excel так же преобразует в числа. Добавьте в запросе к каждому текстовому полю апостроф (""'"" + Справочник.Номенклатура.Код), в консоли апостроф будет виден, а в файле Excel - будет Вам счастье!
sergiobargio1; +1 Ответить 1
3. qwe qwerty (quebracho) 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 . При открытии в экселе формат у ячейки будет текстовый.
Xytras; kazann; Ivan_0110; Скользящий; iZhenius; qwed557; FractalizeR; sergiobargio1; detec; AlexanderKai; Операция1Ы; ravsan; wolfsoft; +13 Ответить 1
4. Александр Кораблев (akor77) 30.03.14 15:41
(3) quebracho, вв Вашем примере предлагается форматировать каждую ячейку или область ячеек. Потом в каждой ячейке по отдельности устанавливать значение. Этот вариант имеет основание для использования небольших таблиц или при создании особого варианта выгрузки. В интернете выложены различные обработки выгрузки и загрузки через Excel. Выгрузка, где используется обращение к каждой ячейке поотдельности (РабочийЛистКниги.Cells(1,2).Value = 5 или .Text = "Да") очень медленны, т.к. при каждом обращении (чтении/записи) к ячейке происходит запрос. В этом весь минус такого подхода. При поиске другого решения, я столкнулся с ADO. Но, в организации для которой мне пришлось делать обработку выгрузки и загрузки нельзя было установить соответствующий драйвер (запрет на излишний софт), по этому пришлось искать другое решение. Сейчас уже много обработок по считыванию данных из Excel с помощью COMSafeArray, этот объект не зависит ни от какого софта и драйверов, в этом его большой плюс. Но выгрузка с использованием данного объекта мне ещё не встречалась. По этому я начал изучать этот объект, проводить эксперименты, что в результате позволило создать универсальную функцию для выгрузки данных с помощью этого массива. Задача стоит максимально быстро слить данные с помощью COMSafeArray, а на какие при этом натыкаешься подводные камни, именно об одном из них написана данная статья. Почему отдельной статьёй? Т.к. апостроф можно использовать при различных вариантах выгрузки и везде он даст один и тот-же результат.
w-divin; AlexanderKai; +2 Ответить 2
5. qwe qwerty (quebracho) 30.03.14 15:56
6. rasswet (rasswet) 02.04.14 08:39
(4) по моему я как раз делал выгрузку через COMSafeArray
ВсегоСтрок = ТаблицаРезультата.Количество();
	ВсегоКолонок = ТаблицаРезультата.Колонки.Количество();
	
	МассивКОМ = Новый COMSafeArray("VT_VARIANT", ВсегоКолонок, ВсегоСтрок);
	
	Для индСтрок = 0 По ТаблицаРезультата.Количество() - 1 Цикл
		
		СтрокаТаблицы = ТаблицаРезультата[индСтрок];
		
		Для индКолонок = 0 По ВсегоКолонок - 1 Цикл
			МассивКОМ.SetValue(индКолонок, индСтрок, СтрокаТаблицы[индКолонок]);
		КонецЦикла; 
		
	КонецЦикла; 
......
лист01_.Range(лист01_.Cells(НачатьСоСтроки,НачатьСоСтолбца), лист01_.Cells(ЗакончитьНаСтроке,ЗакончитьНаСтолбце)).Value = МассивКОМ;  
...Показать Скрыть

но там были проблемы. при такой схеме при наличии определенных данных в ячейках (либо определенном значении ячейки) выгрузка падала в ошибку.
7. Юрий Майоров (MaiorovYury) 02.04.14 10:52
А о каком методе загрузки тут идет речь?
Если через com объект Excel.Application, то при чтении Лист.Cells(а,2).Value - получим число 1, а при чтении Лист.Cells(а,2).Text - получим строку 0001
И ни с какими апострофами заморачиваться не надо
8. Sergio Bargio (sergiobargio1) 02.04.14 15:35
(2) akor77, большое Вам спасибо. для меня проблема была актуальна. Ранее справлялись таким образом: при сохранении отчёта из "1С" выбирали тип сохраняемого файла "Лист Excel95(*xls)". В этом случае все ведущие нолики сохраняются фактически, а не только визуально в ячейках. Затем его открывали уже в Excel и сохраняли как обычный xlsx-файл. Только, если перед сохранением поставить курсор на такую ячейку , то при сохранении всё таки ведущие нолики "съедались".
Пожалуйста, подскажите, каким образом можно решить проблему, что бы при сохранении отчёта, написанного на СКД, в формат *.xlsx или *.xls можно было присваивать имя сохраняемому листу? Приходиться после открытия отчёта в excel идти в параметры выставлять "галочку" что бы отображалась закладка с названиями листов, за тем растягивать её, иначе она не видна, а потом присваивать ей имя.
Спасибо.
9. Александр Кораблев (akor77) 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) 02.04.14 19:32
(8) sergiobargio1, Здесь описано как записать макрос, который открывает ярлычки. Инструкция ориентирована для бухгалтеров, экономистов и прочих пользователей. http://infostart.ru/public/270640/
11. Александр Крынецкий (echo77) 02.12.15 19:38
Вот такой вариант решения проблемы лидирующих нулей мне больше всего понравился: http://1cstyle.ru/blog/8898/

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

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