Выгрузка данных в excel без геморроя один но принципиально важный момент

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

AlexandrIII
29.03.14 10:50
Сейчас в теме

Для одного апострофа много букв. ИМХО 🙂

Но в целом вроде норм.

akor77
918
29.03.14 11:08
Сейчас в теме

(1) AlexandrIII, Хочется обратить внимание на то, что сколько времени было потрачено и изменений в коде, прежде чем выйти на этот самый апостроф. Мал золотник, да дорог. В статье описывается не сам апостроф (разработчики могли использовать какой-нибудь другой символ), а само поведение Excel. Апостроф это только «ключ-инструкция» к механизму.

К статье добавлю, что при необходимости создать ‘файл для загрузки/выгрузки данных в формате Excel’, то это можно сделать с помощью консоли отчётов (Консоль сохраняет результат в файл формата Excel). В результате, строки из чисел сам Excel так же преобразует в числа. Добавьте в запросе к каждому текстовому полю апостроф («»‘»» + Справочник.Номенклатура.Код), в консоли апостроф будет виден, а в файле Excel — будет Вам счастье!

sergiobargio1
115
02.04.14 15:35
Сейчас в теме

(2) большое Вам спасибо. для меня проблема была актуальна. Ранее справлялись таким образом: при сохранении отчёта из «1С» выбирали тип сохраняемого файла «Лист Excel95(*xls)». В этом случае все ведущие нолики сохраняются фактически, а не только визуально в ячейках. Затем его открывали уже в Excel и сохраняли как обычный xlsx-файл. Только, если перед сохранением поставить курсор на такую ячейку , то при сохранении всё таки ведущие нолики «съедались».

Пожалуйста, подскажите, каким образом можно решить проблему, что бы при сохранении отчёта, написанного на СКД, в формат *.xlsx или *.xls можно было присваивать имя сохраняемому листу? Приходиться после открытия отчёта в excel идти в параметры выставлять «галочку» что бы отображалась закладка с названиями листов, за тем растягивать её, иначе она не видна, а потом присваивать ей имя.

Спасибо.

akor77
918
02.04.14 17:18
Сейчас в теме

(8) sergiobargio1,

Excel.ActiveWindow.DisplayWorkbookTabs = -1; // Отобразит ярлычки

Excel.ActiveWindow.TabRatio = 0.25; // Сдвинет левый край горизонтальной полосы прокрутки.

Взял отсюда:

https://infostart.ru/public/20438/

Обратите внимание!

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

10.

akor77
918
02.04.14 19:32
Сейчас в теме

(8) sergiobargio1, Здесь описано как записать макрос, который открывает ярлычки. Инструкция ориентирована для бухгалтеров, экономистов и прочих пользователей.

https://infostart.ru/public/270640/

quebracho
24
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 . При открытии в экселе формат у ячейки будет текстовый.

akor77
918
30.03.14 15:41
Сейчас в теме

(3) quebracho, вв Вашем примере предлагается форматировать каждую ячейку или область ячеек. Потом в каждой ячейке по отдельности устанавливать значение. Этот вариант имеет основание для использования небольших таблиц или при создании особого варианта выгрузки. В интернете выложены различные обработки выгрузки и загрузки через Excel. Выгрузка, где используется обращение к каждой ячейке поотдельности (РабочийЛистКниги.Cells(1,2).Value = 5 или .Text = «Да») очень медленны, т.к. при каждом обращении (чтении/записи) к ячейке происходит запрос. В этом весь минус такого подхода. При поиске другого решения, я столкнулся с ADO. Но, в организации для которой мне пришлось делать обработку выгрузки и загрузки нельзя было установить соответствующий драйвер (запрет на излишний софт), по этому пришлось искать другое решение. Сейчас уже много обработок по считыванию данных из Excel с помощью COMSafeArray, этот объект не зависит ни от какого софта и драйверов, в этом его большой плюс. Но выгрузка с использованием данного объекта мне ещё не встречалась. По этому я начал изучать этот объект, проводить эксперименты, что в результате позволило создать универсальную функцию для выгрузки данных с помощью этого массива. Задача стоит максимально быстро слить данные с помощью COMSafeArray, а на какие при этом натыкаешься подводные камни, именно об одном из них написана данная статья. Почему отдельной статьёй? Т.к. апостроф можно использовать при различных вариантах выгрузки и везде он даст один и тот-же результат.

quebracho
24
30.03.14 15:56
Сейчас в теме

rasswet
82
02.04.14 08:39
Сейчас в теме

(4) по моему я как раз делал выгрузку через COMSafeArray

ВсегоСтрок = ТаблицаРезультата.Количество();
ВсегоКолонок = ТаблицаРезультата.Колонки.Количество();

МассивКОМ = Новый COMSafeArray(«VT_VARIANT», ВсегоКолонок, ВсегоСтрок);

Для индСтрок = 0 По ТаблицаРезультата.Количество() — 1 Цикл

СтрокаТаблицы = ТаблицаРезультата[индСтрок];

Для индКолонок = 0 По ВсегоКолонок — 1 Цикл
МассивКОМ.SetValue(индКолонок, индСтрок, СтрокаТаблицы[индКолонок]);
КонецЦикла;

КонецЦикла;
……
лист01_.Range(лист01_.Cells(НачатьСоСтроки,НачатьСоСтолбца), лист01_.Cells(ЗакончитьНаСтроке,ЗакончитьНаСтолбце)).Value = МассивКОМ;

Показать

но там были проблемы. при такой схеме при наличии определенных данных в ячейках (либо определенном значении ячейки) выгрузка падала в ошибку.

MaiorovYury
10
02.04.14 10:52
Сейчас в теме

А о каком методе загрузки тут идет речь?

Если через com объект Excel.Application, то при чтении Лист.Cells(а,2).Value — получим число 1, а при чтении Лист.Cells(а,2).Text — получим строку 0001

И ни с какими апострофами заморачиваться не надо

20.

user640247
24.06.20 21:53
Сейчас в теме

(7)

Text

Спасибо. При загрузке в 1с получилось. А при выгрузке из 1 использую апостроф.

11.

echo77
1157
02.12.15 19:38
Сейчас в теме

Вот такой вариант решения проблемы лидирующих нулей мне больше всего понравился:

https://1cstyle.ru/blog/8898/

Кратко-суть: в ячейке, которую нужно сохранить как текст ставим признак Содержит значение и в качестве типа значения поставить Строка

12.

kame-lana
03.12.15 20:39
Сейчас в теме

(11) echo77, не работает! выводит в общий формат…

13.

echo77
1157
04.12.15 08:15
Сейчас в теме

(12) kame-lana, да, формат общий — но лидирующие нули не обрезаются

14.

Vortigaunt
82
08.04.16 14:06
Сейчас в теме

Я попробовал ставить и пробел спереди и апостроф. Не помогает.

Если ставлю пробел то выгружается ровно так же, как и без него.

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

Я заполняю в 1с табличный документ из макета, а затем сохраняю его в формате XLS. Что я делаю не так?

15.

akor77
918
11.04.16 09:51
Сейчас в теме

(14) Vortigaunt, апостроф ставится в данные, которые выгружаются в Excel. Если их грузить в табличный документ 1С, то скорее всего этот апостроф так и останется, я эти варианты не исследовал. Попробуй из своего созданного табличного документа 1С копи-пастом скопировать данные в Excel, возможно Excel примет эти данные корректно. Пробуй.

16.

deme
4
26.05.16 14:31
Сейчас в теме

вопрос можно? может, не совсем в тему. А можно ли перенести строки отчета mxl в готовую таблицу xls? Так сказать, внедрить. В xls уже диаграммы нарисованы, все красиво.

17.

echo77
1157
29.05.16 07:15
Сейчас в теме

18.

deme
4
29.05.16 11:33
Сейчас в теме

дд. Уточню вопрос: как перенести строки отчета mxl в готовую таблицу xls?

19.

Dermidont
6
28.02.17 15:40
Сейчас в теме

Гошан, ты чё, там же просто. Открывай свой xls файл через ком соединение, выбирай лист и вписывай в него что хочешь.

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

Источник