Копирование числовых ячеек из 1С в Excel

21.01.2019 21 Comments

Администрирование, Администрирование данных 1С

,

    Screenshot_213.png
Решение проблемы, когда значения скопированных ячеек из табличных документов 1С в Excel воспринимаются последним как текст, т.е. без дополнительного форматирования значений невозможно применить арифметические операции. Поводом для публикации послужило понимание того, что целое предприятие с более сотней активных пользователей уже на протяжении года мучилось с такой, казалось бы на первый взгляд, тривиальной проблемой. Варианты решения, предложенные специалистами helpdesk, обслуживающими данное предприятие, а так же многочисленные обсуждения на форумах, только подтвердили убеждение в необходимости описания способа, который позволил мне качественно и быстро справиться с ситуацией.

Проблема была выявлена в инфраструктуре, где платформа 1С:Предприятие 8.3 опубликована на терминальном Windows Server 2024, а клиентами служат ПК с Windows 10 и Excel в составе пакета Microsoft Office 365. Обновления для ПП устанавливались достаточно своевременно. 

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

1) пользовательские: 

— замена значений в Excel через сочетание Ctrl+H;

— сохранение всего табличного документа 1С как файл формата Excel и последующая работа со значениями в рамках ПП Microsoft

2) 1С-ные:

— применить условное оформление к ячейкам

3) от Excel-еводов:

— написать макрос, который бы сам выполнял команду Ctrl+H

4) админские:

— даунгрейд/замена RDP-клиента

— редактирование региональных стандартов ОС

Перечисленные тут способы не позволили решить проблему достаточно качественно. Однако, последний вариант натолкнул на мысль попробовать изменить региональные установки самой информационной базы 1С. Для этого потребуется монопольно запустить режим "1С:Конфигуратор", где в подменю "Администрирование" выбрать пункт "Региональные установки информационной базы", в открывшейся форме убрать "галку" с флага "Использовать региональные установки текущего сеанса", что позволит установить пробел в поле "Разделитель групп". Да, вот так просто, не выбирая предложенных значений, нажимаем пробел, а затем применяем настройки нажатием кнопки "ОК". 

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

P.S. Данная  настройка повлияет на формат чисел не только при копировании в Excel! Например, при выгрузке числовых значений в txt файл, пробел будет так же добавлен как разделитель разрядов. Это может нарушить работу со сторонними сервисами, если те "не умеют" воспринимать такой символ разделения. Я столкнулся с подобной ситуацией при загрузке файла зарплатного проекта в клиент одного из банков. Решение данной проблемы я нашел в банальном применений функции СтрЗаменить() для числовых значений.

Related Posts

21 Comments

  1. kauksi

    Sub ПреобразоватьВТекст()

    Dim sTempValue As String

    Dim c As Range

    On Error Resume Next

    For Each c In ActiveWindow.Selection

    sTempValue = Str(c.Value)

    c.NumberFormat = «@»

    c = Trim(sTempValue)

    Next

    On Error GoTo 0

    End Sub

    1) открыть Ексель

    2) открыть меню Вид, справа видим подменю «Макросы»

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

    по умолчанию ее нет, надо создать. Как это делается?

    в подменю «Макросы» нажимаем «запись макроса», в появившейся форме выбираем «сохранить в» = «личная книга макросов».

    нажимаем ОК. началась запись, щелкни пару раз где-нибудь на листе открытой в данной момент книги.

    Для того чтобы остановить запись в левом нижнем углу интерфейса Екселя нажми на белый квадратик (справа от слова «Готово»). Запись прекратилась и у нас в Личной книге макросов записался макрос-пустышка скорее всего с названием «Макрос1».

    4) Теперь нажимаем ALT+F11 и попадаем в среду разработки. Находим нашу Личную книгу макросов (VBAProject (PERSONAL.XLSB)), в дереве элементов в папке Modules находим Module1, дважды по нему щелкаем, удаляем наш макрос-пустышку и вместо него вставляем следующее

    здесь же в редакторе нажимаем на кнопку Сохранить.

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

    Чтобы его вызвать предварительно выделяем в любой открытой книге диапазон ячеек, который нужно преобразовать в текст, нажимаем ALT+F8, выбираем наш макрос и жмем ОК.

    Reply ↓
  2. itriot11

    (1) это должен будет сделать каждый юзер для всех своих файлов?

    Reply ↓
  3. Bеgemoth

    (0) А если, кроме указания пробела в качестве разделителя групп, ещё и установить там же количество разрядов в группировке равным 0, то это не решит проблему лишних пробелов при выгрузке в txt файл?

    Reply ↓
  4. itriot11

    (3) Очень может быть и так! Нужно попробовать)

    Reply ↓
  5. itriot11

    (3) Попробовал. Да, решает эту проблему. А так же, поскольку, данная настройка регулирует наличие символа разделения групп чисел, то и заявленную в публикации проблему поможет решить без указания символа «Пробел» в поле «Разделитель групп». Однако, помимо этого, получим так же очевидный результат в том, что числовые значения во всей информационной базе будут представлены без разделителя, те было 123 456 678, 90, а станет 12345678,90. В качестве варианта — вполне можно использовать, если пользователи будут не шибко против. Ну а я пока остановился на решении описанном в публикации.

    Reply ↓
  6. Ashandy

    А подскажите, пожалуйста, где почитать об обратной ситуации.

    При использовании 

    ТабличныйДокумент.Прочитать(ФайлEXCEL, СпособЧтенияЗначенийТабличногоДокумента.Значение);

    если в екселе был формат ячейки число то оно переносится с разделителем в виде пробела, хотя в екселе без пробела.

    причем на Windows server было так

    а на локальной машине с 2010м офисом переносило значение без пробела.

    Правильно ли в таком случае использовать 

    ТабличныйДокумент.Прочитать(ФайлEXCEL, СпособЧтенияЗначенийТабличногоДокумента.Текст);

    ?

    Reply ↓
  7. itriot11

    (6)

    Правильно ли в таком случае использовать

    ТабличныйДокумент.Прочитать(ФайлEXCEL, СпособЧтенияЗначенийТаб

    Не готов ответить — правильно или нет. Для этого сперва бы стоило дать определение слову «правильно») На вашем месте, попробовал бы оба варианта и выбрал наиболее приемлемый в контексте задачи.

    Reply ↓
  8. lazovit

    А правой кнопкой в Excel и специальная вставка, вставить как текст никто не пробовал?

    Reply ↓
  9. itriot11

    (8) Пробовал — не взлетело.

    Reply ↓
  10. romankoav

    (6) и так и так делал — никакой разницы не было

    Reply ↓
  11. mikl79

    Спасибо, помогло

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

    1) Формат(ПеременнаяЧисло,»ЧГ=»);

    2) СтрЗаменить(ПеременнаяЧисло,Символы.НПП,»»);

    Reply ↓
  12. user819002

    Попробовали всё слово в слово по инструкции, всё равно копируется как текст.

    Проблему решаем даунгрейдом файлов на серверах и на клиентах, системы windows server 2016/ widows 10

    Пользователей очень много, мучений доставляет кучу)

    Подскажите пожалуйста, что ещё может быть не так?

    Reply ↓
  13. ytrewqwerty

    (12) Вот еще версия для размышлений. Помнится еще в 7.7 было так, что если число в 1С таблице в ячейке было выравнено ‘по центру’ или ‘влево’, то Эксель воспринимал его как текст, но если выравнивание было ‘вправо’, то — как число.

    Reply ↓
  14. VAAngelov

    Спасибо большое. Полезно и даже очень.

    Reply ↓
  15. erutan

    Если сделать просто так, как написано в Теме

    >> Однако, последний вариант натолкнул на мысль попробовать изменить региональные установки самой информационной базы 1С. Для этого потребуется монопольно запустить режим «1С:Конфигуратор», где в подменю «Администрирование» выбрать пункт «Региональные установки информационной базы», в открывшейся форме убрать «галку» с флага «Использовать региональные установки текущего сеанса», что позволит установить пробел в поле «Разделитель групп».

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

    в БП3 — решил расширением общ.модуля БухгалтерскиеОтчетыКлиентСервер

    &Вместо(«ВычислитьСуммуВЯчейках»)
Процедура Расш1_ВычислитьСуммуВЯчейках(Сумма, СтруктураАдресВыделеннойОбласти, Результат)

ТипЧисло = Новый ОписаниеТипов(«Число»);
Для ИндексСтрока = СтруктураАдресВыделеннойОбласти.Верх По СтруктураАдресВыделеннойОбласти.Низ Цикл
Если ИндексСтрока = 0 Тогда // Выделена колонка отчета (без ограничений), суммы из нее получить нельзя.
Продолжить;
КонецЕсли;

Для ИндексКолонка = СтруктураАдресВыделеннойОбласти.Лево По СтруктураАдресВыделеннойОбласти.Право Цикл
Если ИндексКолонка = 0 Тогда // Выделена строка отчета (без ограничений), суммы из нее получить нельзя.
Продолжить;
КонецЕсли;

Ячейка = Результат.Область(ИндексСтрока, ИндексКолонка, ИндексСтрока, ИндексКолонка);
Если Ячейка.Видимость = Истина Тогда
Если Ячейка.СодержитЗначение И ТипЗнч(Ячейка.Значение) = Тип(«Число») Тогда
Сумма = Сумма + Ячейка.Значение;
ИначеЕсли ЗначениеЗаполнено(Ячейка.Текст) Тогда
ТекстВЯчейке = СокрЛП(Ячейка.Текст);

// Удалим знак «+» перед числом. Используется в Платежном календаре.
Если Лев(ТекстВЯчейке, 1) = «+» Тогда
ТекстВЯчейке = Сред(ТекстВЯчейке, 2);
КонецЕсли;

ТекстВЯчейке = СтрЗаменить(ТекстВЯчейке,» «,»»);// доработка

// Разделим текст, включая пустые строки. Хотя бы одно значение (пустое) в массиве будет всегда.
ЧастиСтроки  = СтрРазделить(ТекстВЯчейке, » «);
ЧислоВЯчейке = ТипЧисло.ПривестиЗначение(ЧастиСтроки[0]);
Сумма        = Сумма + ЧислоВЯчейке;
КонецЕсли;
КонецЕсли;
КонецЦикла;
КонецЦикла;

КонецПроцедуры

    Показать

    Reply ↓
  16. itriot11

    (15) С таким не сталкивался. Вероятно, это какое-то особенное поведение на стыке систем. Спасибо, что сразу тут описали решение!

    Reply ↓
  17. erutan

    Коллеги, а как у вас копируются/вставляются в excel бухчсета из ОСВ (БП3)?

    сервер терминалов winserver2016, что в файловом, что в SQL-виде, при вставке за пределы RDP в MSOffice 2016 некоторые счета меняются на даты(

    1. счета выведены в отдельной колонке, без наименований

    2. Собственно, раньше работали на WinServ2008, файлово, такого поведения не было

    3. На скриншоте -«режим совместимости», файл xls. Если современный файл делать, т.е. xlsx, то таких ошибок меньше , но они все равно есть(

    Reply ↓
  18. itriot11

    (17) с таким не сталкивался. Попробуйте поиграться с региональными настройками даты.

    Reply ↓
  19. tasha2024

    Заменила все в региональных настройках, как написано в статье, но при копировании числе из ОСВ БП 3.0 и вставке числа в excel по прежнему пробел есть. Что делаю не так?

    Reply ↓
  20. tasha2024

    (19)

    Заменила все в региональных настройках, как написано в статье, но при копировании числа из ОСВ БП 3.0 и вставке числа в excel по прежнему пробел есть. Что делаю не так делаю?

    Упс. Получилось. Но меняются настройки сразу у всех пользователей базы. Можно ли только для одного пользователя сменить настройки региональные?

    Reply ↓
  21. itriot11

    (20) описанным тут способом нет. Тут рассматривали вариант с макросами — может стоит копать в эту сторону, если делаем только для одного пользователя.

    Reply ↓

Leave a Comment

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