Выгрузка excel лидирующие нули

27.01.16 — 09:03

Есть типовая обработка — Загрузка данных из табличного документа, с помощью которой нужно загрузить цены из экселя.

Все работает совершенно замечательно за исключением тех случаев, когда код номенклатуры состоит из числа и лидирующих нулей. Например 00000000255. В этом случае после открытия файла в обработке отображается 255 вместо 00000000255. Естессно цена для этой номенклатуры не загружается.

В самом экселе, в ячейке отображается 00000000255, но если открыить ее на редактирование, то отобразится 255. Формат у этой ячейки какой-то нестандартный, вот такой: 00000000000;[Красный]-00000000000.

Если указать текстовый формат, то ведущие нули сразу же обрезаются, но если после этого ввести эти нули руками, то — о чудо они сохраняются.

На самом деле никакого чуда нет — текстовый формат хранит значение в том виде как оно введено.

Собственно в чем вопрос, можно ли как-то преобразовать такие значения в текстовые? Может есть какой-то макрос или еще какой-то способ?

1 — 27.01.16 — 09:07

формат(,)

2 — 27.01.16 — 09:09

в коде заменить .Value на .Text

3 — 27.01.16 — 09:10

(2) +100

Либо — Дополнить(Строка, Чем, Длина).

4 — 27.01.16 — 09:11

Лист.Cells(НомерСтроки, НомерКолонки).NumberFormat = Формат;

5 — 27.01.16 — 09:11

Хмм.. прежде чем советовать, хотелось бы узнать что автор может изменить -формат Экселя или «есть типовая обработка»(цы) Если ни то ни другое изменить нельзя — то типовую обработку выгрузить во внешнюю и пририсовать свои бантики и рюшечки.

6 — 27.01.16 — 09:14

(5) В каком смысле изменить формат Экселя?

7 — 27.01.16 — 09:15

(6) в смысле внести изменения в файл экселя.

8 — 27.01.16 — 09:16

или в алгоритм той программы, что его формирует.

9 — 27.01.16 — 09:29

10 — 27.01.16 — 09:39

(9) Спасибо, очень полезная статья. Теперь осталось придумать как с этой бедой бороться.

11 — 27.01.16 — 09:42

(1) формат не понимает как нужно форматировать строки, то есть Формат(255,»ЧЦ=11; ЧВН=; ЧГ=») вернет «00000000255», а Формат(«255″,»ЧЦ=11; ЧВН=; ЧГ=») вернет «255»

А у меня как раз строка и преобразовывать ее в число нет никакого желания, потому как есть коды с буковками.

12 — 27.01.16 — 09:47

Хулиганю Прав(«00000000000» + «255», 11)

13 — 27.01.16 — 11:03

(12) А вот вы откуда знаете на этапе разработки — сколько лидирующих нулей имеется?

14 — 27.01.16 — 11:07

если ломать обработку лень, можно еще попытаться в файле нафигачить формат Текстовый и в ячейки вхерачить «00000000255» прямо с кавычками(!!!)

15 — 27.01.16 — 11:30

(14) Во все 65 535 строчек предлагаете?

16 — 28.01.16 — 09:27

(13) Открою тебе большой секрет: мне об этом сообщил автор в (11) и в (0)

17 — 28.01.16 — 09:42

В обработке есть чудо механизм «события». В событии при загрузке этого поля ставь «Формат» и будет тебе счастье

18 — 28.01.16 — 10:04

(13) тут надо знать длину поля, куда потом пойдет загрузка

19 — 28.01.16 — 10:23

(17) Да есть такой механизм. В каком событии мне нужно поставить формат, чтобы найти в справочнике номенклатуры позицию с кодом 00000000255, если в табличном документе из которого нужно загрузить, этот код записан как 255?

20 — 28.01.16 — 10:26

(19) Ёлки-моталки! Там работы на час (максимум)… Ты же вторые сутки мозг имеешь…

21 — 28.01.16 — 10:27

+ (19) По коду номенклатуры идет поиск, при загрузке из табличного документа. Ну то есть на вкладке настройка, в колонке режим загрузки, для номенклатуры установлено искать по коду

22 — 28.01.16 — 10:27

(20) Так я вчера уже все сделал

23 — 28.01.16 — 10:30

+ (22) …с помощью кувалды, лома и такой-то матери, а хочется гармонии…

24 — 28.01.16 — 11:35

25 — 28.01.16 — 12:16

(кстати) Если по метаданным в коде 11 символов — это вовсе не значит что «реальные» коды справочника будут формироваться длиной в 11 символов. Автонумерация, например, формирует коды/номера с учётом длины поледнего кода/номера.

26 — 28.01.16 — 12:17

*последнего

27 — 28.01.16 — 13:00

(24) я так и сделал, только не 30 нулей, 11 по длине кода

(25) я проверил длина везде одинаковая, это как раз критерий — если прочитанное из экселя меньше определенной длины, значит нужно дополнить нулями

(27) я делал нечто похожее однажды, строку нулей формировал в цикле, по длине метаданного

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

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

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

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

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

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

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

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

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

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

«’»  — символ апострофа.

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

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

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

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

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

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

Иногда, необходимо сделать так, чтобы нули в начале чисел автоматически не удалялись программой Excel. Например, вы составляете таблицу с артикулами и вам просто необходимо, чтобы числа отображались полностью.

Грубо говоря, нужно чтобы числа прописывались так же, как на картинке ниже.

Итак, я продемонстрирую вам несколько способов, как можно привести числа в такой вид.

У каждого способа, естественно, есть свои плюсы и минусы, подробнее об этом я расскажу в самой статье.

Способ 1: изменяем формат ячейки

у нас есть «ID» товаров магазина, как на картинке ниже.

И нам нужно записывать эти самые «ID», например, как 00001, а Excel автоматически переписывает его в 1.

Дело в том, что Excel пытается привести числа в простой вид. Он сравнивает числа, к примеру, 00002 и 2, понимает что они одинаковые (именно как числа) и преобразовывает 00002 в простой вид 2.

Но нам все равно необходимо записать 00002, тогда мы можем изменить формат ячейки в текстовый.

Пошаговая инструкция:

• Выделите ячейки с числами, которые нужно записать с нулями;

• Щелкните в раздел «Главная» и в формате выберите «Текстовый»;

В принципе все, теперь попробуйте заново записать ваши числа (нужно именно еще раз их прописать. Числа которые Excel уже преобразовал в «простой» вид, обратно не изменятся).

Важная информация: этот способ может не подойти вам, если вы, в будущем, будете использовать эти ячейки в расчетах, потому что ячейка теперь в текстовом формате, а считать возможно только числа.

Способ 2: указываем формат отображения чисел

Этот способ позволит вам, в дальнейшем, использовать записанные числа в расчетах.

В чем суть:

Число вы можете записать по-разному, например, 2000 и 2000.00 это одно и то же, по этой логике, мы можем просто изменить формат отображения числа. То есть придать именно отображению другой вид, при этом формат ячейки останется числовым.

Таким образом содержимое ячейки не изменится.

Пошаговая инструкция:

• Выделите нужные ячейки;

• Щелкните «Главная» и нажмите на стрелочку, смотрящую вниз, в разделе «Число»;

• В открывшемся окне, во вкладке «Число», щелкните на «(все форматы)»;

• В поле «Тип» (где сейчас написано «Основной») напишите «00000»;

• Подтвердите.

Итак, что произошло: мы поменяли вид отображения всех чисел в выделенных ячейках на пятизначный. Это значит, что все числа в этих ячейках будут отображаться, минимум, в пятизначном формате. Например, число «2» будет отображаться как «00002». А число «372847» будет отображаться как «372847».

В общем, тут все зависит от вашей ситуации, нулей в поле «Тип» можно вписать столько, сколько вам нужно. Можно сделать десятизначный формат отображения и так далее.

Важная информация: этот способ работает только с ячейками формата «Число», если у вашей ячейки будет текстовый формат, этот вариант не сработает.

Способ 3: с помощью функции ТЕКСТ

Итак, важно понимать, что функция ТЕКСТ изменяет формат самого значения, а не всей ячейки.

Но в целом, этот способ подходит если вам просто нужно сделать, чтобы число отображалось так, как вы хотите.

Но не забывайте, что использование этой функции превратит значение в текст. Это значит что для Excel ваше число (например, 00002) будет считаться обычным текстом, т.е. в расчетах его использовать будет невозможно.

Формула:

=ТЕКСТ(A2, “00000”)

Для числа, хранящегося в ячейке «A2».

Итак, число будет записываться в пятизначном формате(если число было «3», то станет «00003»).

Плюс этого метода заключается в том, что эти числа можно будет искать с помощью функций поиска. Так как они считаются обычным текстом, поиск по тексту будет работать.

Важная информация: этот способ может не подойти вам, если вы, в будущем, хотите использовать эти числа в расчетах, потому что они теперь будут считаться обычным текстом.

Способ 4: изменяем длину строки

Минус способа с использованием функции ТЕКСТ является то, что она не способна изменять формат чисел на текст тогда, когда эти числа находятся в комбинации с текстом. Она просто не будет работать.

Но в такой ситуации можно использовать метод с функциями ПОВТОР и ДЛСТР.

Формула:

=ПОВТОР(0;5-ДЛСТР(A2))&A2

При вызове такой функции, длина строки изменится на пятизначную. Это значит, что все числа будут записаны в пятизначном формате (например, число «3» будет записано как «00003»).

Что делает эта функция:

• Функция ДЛСТР(A2) возвращает длину нашей строки с числом.
• Функция =ПОВТОР(0;5-ДЛСТР(A2)) считает сколько нулей необходимо добавить к началу нашего числа. И так для каждой новой строки.

Грубо говоря, эта функция просто посчитает сколько нулей нужно добавить к началу числа чтобы в нем, в общем, получилось 5 знаков (т.к. в формуле мы указали 5).

Способ 5: используем Visual Basic

Способ с использованием Visual Basic отлично подойдет тем, кому очень часто нужно записывать числа определенным образом.

Этот код создает вашу собственную функцию для такого рода задачи:

Function AddLeadingZeroes(ref As Range, Length As Integer) 
Dim i As Integer 
Dim Result As String 
Dim StrLen As Integer 
StrLen = Len(ref) 
For i = 1 To Length 
If i <= StrLen Then 
Result = Result & Mid(ref, i, 1) 
Else 
Result = "0" & Result 
End If 
Next i 
AddLeadingZeroes = Result 
End Function

Нужно поместить код, данный выше, в Visual Basic модуль и тогда в вашем Excel добавится функция, которая автоматически будет прописывать числа с нулями.

А еще можно поделиться этой функцией с коллегами.