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

16.05.2017 19 Comments

Загрузка и выгрузка в Excel , Обмен данными 1С, Разработка

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

Related Posts

19 Comments

  1. AlexandrIII

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

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

    Reply ↓
  2. akor77

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

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

    Reply ↓
  3. quebracho

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

    РабочийЛистКниги.Cells(1,1).NumberFormat = «@»; // текстовый формат

    РабочийЛистКниги.Cells(1,2).NumberFormat = «0»; // числовой формат

    РабочийЛистКниги.Cells(1,3).NumberFormat = «»; // Формат даты Selection.NumberFormat = «m/d/yyyy»

    Для строки 000001 , при установке в ячейке текстового формата и в формуле этой ячейки будет 000001 . При открытии в экселе формат у ячейки будет текстовый.

    Reply ↓
  4. akor77

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

    Reply ↓
  5. quebracho

    (4) Понятно.

    Reply ↓
  6. rasswet

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

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

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

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

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

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

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

    Показать

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

    Reply ↓
  7. MaiorovYury

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

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

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

    Reply ↓
  8. sergiobargio1

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

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

    Спасибо.

    Reply ↓
  9. akor77

    (8) sergiobargio1,

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

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

    Взял отсюда: http://infostart.ru/public/20438/

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

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

    Reply ↓
  10. akor77

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

    Reply ↓
  11. echo77

    Вот такой вариант решения проблемы лидирующих нулей мне больше всего понравился: http://1cstyle.ru/blog/8898/

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

    Reply ↓
  12. kame-lana

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

    Reply ↓
  13. echo77

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

    Reply ↓
  14. Vortigaunt

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

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

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

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

    Reply ↓
  15. akor77

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

    Reply ↓
  16. deme

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

    Reply ↓
  17. echo77

    (16) deme, Можно

    Reply ↓
  18. deme

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

    Reply ↓
  19. Dermidont

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

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *