Наглядный анализ данных посредством функции условного форматирования в MS Excel 2007\2010, запускаемой макросами из-под 1С 7.7 \ 8.х

Опубликовал Olvia в раздел Программирование - Практика программирования

Дабы не ломать голову над «мегараскрашиваниями» средствами 1С, предлагаю выводить данные в MS Excel и использовать простенькие макросы, запускаемые из 1С в процессе выгрузки, к тому же аналога функции "условное форматирование" в 1С нет

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

  1. выгрузка итоговых значений в Excel
  2. автофильтр
  3. условное форматирование,

тем более что, начиная с 2010, форматы ячеек кэшируются (в отличие от более ранних версий, перепрорисовываются только при изменении данных), не говоря о существенном расширении функционала уже в 2007 (подробнее см. http://www.microsoft.com/rus/business/smb/blog/01/).

Если с первым и вторым – все просто, то третий пункт вызывает затруднения, так как условное форматирование устанавливается на конкретную область ячеек, а не на значения в ней, следовательно,  после применения сортировки формат не перемещается вместе с данными. Для исправления этого досадного недоразумения, дабы не ломать голову над «раскрашиваниями» средствами 1С, предлагаю использовать простенькие макросы:

Sub Раскрасить(FormattingArea, xlColor)  ''применит гистограммы цвета xlColor для FormattingArea

       Set FormattingRange = Range(FormattingArea)

 

    FormattingRange.FormatConditions.AddDatabar

    With FormattingRange.FormatConditions(1)

        .ShowValue = False

 

        .MinPoint.Modify newtype:=xlConditionValueAutomaticMin

        .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax

 

        .NegativeBarFormat.ColorType = xlDataBarColor

        .AxisPosition = xlDataBarAxisAutomatic

        .NegativeBarFormat.Color.Color = 255

 

        With .BarColor

            .Color = xlColor

        End With

    End With

End Sub


Sub РасставитьЗначки(FormattingArea, minValue, maxValue, Optional xlType As Long = xlConditionValuePercent) ''проанализирует  FormattingArea по параметру xlConditionValuePercent

     Set FormattingRange = Range(FormattingArea)

    Set FC = FormattingRange.FormatConditions

 

    FC.AddIconSetCondition

    FormattingRange.FormatConditions(FC.Count).SetFirstPriority

    With FormattingRange.FormatConditions(1)

        .ShowIconOnly = True

        .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)

       

        With .IconCriteria(2)

            .Type = xlType

            .Value = minValue

        End With

        With .IconCriteria(3)

            .Type = xlType

            .Value = maxValue

        End With

    End With

End Sub

Осталось задать области Smile В моем случае - это множество подразделений, сотрудники которых по итогам заданного периода должны быть премированы \ уволены в зависимости от показателей. Во вложенном файле – процедура поиска строк для форматирования  после изменения их положения.

Оптимизация

  1. Для ускорения открытия книги сохраняю уже отформатированный вариант, что на языке 1С:

    ОбъектXLS.Run("ПрименитьУсловноеФорматирование");// ПрименитьУсловноеФорматирование – основная процедура общего модуля исходного Excel-файла

    После макроса буду заполнять еще страницы, а значит надо:

    ИсточникЗаписи.Worksheets(«СтраницаБезАвтофильтра»).Activate();

    и в основной процедуре исходного Excel-файла:

    If ActiveSheet.AutoFilter Is Nothing Then Exit Sub

  2. Для ускорения пересчета форматов до и после исполнения кода в основной процедуре вызываю:

    Sub Before()

        Application.ScreenUpdating = False

        Application.Calculation = xlCalculationManual

        Application.EnableEvents = False

        ActiveSheet.DisplayPageBreaks = False

        Application.DisplayStatusBar = False

        Application.DisplayAlerts = False

    End Sub

     

    Sub After()

        Application.ScreenUpdating = True

        Application.Calculation = xlCalculationAutomatic

        Application.EnableEvents = True

        'ActiveSheet.DisplayPageBreaks = True

        Application.DisplayStatusBar = True

        Application.DisplayAlerts = True

    End Sub

    Подробнее см. http://habrahabr.ru/blogs/microsoft/112458/

  3. Коллекция цветов гистограмм и колонок форматирования в модуле объекта книги:

Sub Workbook_Open()

    Call ЗаполнитьПредопределенныеЗначения

End Sub

Скачать файлы

Наименование Файл Версия Размер
УсловноеФорматирование.xlsm
.xlsm 53,78Kb
20.01.12
29
.xlsm 53,78Kb 29 Скачать

См. также

Комментарии
1. anry mc (AnryMc) 709 21.01.12 08:49 Сейчас в теме
Я встречал здесь как это сделать на 8.х-ке...
Чисто на 1С. Без внешних компонент
2. Павел (Pavel777777) 51 21.01.12 11:08 Сейчас в теме
Спасибо, интересный вариант... А правильно ли я понял общую схему работы в этом случае? -
Есть шаблон файла Excel с записанными макросами >>> средствами 1с в ячейках этого файла заполняются данные - запускаются макросы >>> сохраняется файл
3. Olvia 15 21.01.12 17:56 Сейчас в теме
(2) Pavel777777, да, Вы все абсолютно правильно поняли, единственное... если надумаете использовать Excel-автофильтр - средствами VBA нельзя (на текущий момент) отловить событие "ПослеПримененияАвтофильтра" (например, сортировки строк), поэтому приходится вешать макрос пересчета форматирования на Worksheet_Calculate() соответствующего листа, а значит, чтобы не выгружать данные на следующую страницу целую вечность (на объеме от 10-ти строк - зациклится), надо работать с .ActiveSheet и перед выгрузкой сделать активной ее
4. Olvia 15 21.01.12 18:02 Сейчас в теме
(1) AnryMc, какую внешнюю компоненту Вы имеете ввиду? Во вложении - Excel-файл, как пример работы с автофильтром, т. к. в этом случае возникает необходимость программного поиска исходных областей для применения условного форматирования
5. anry mc (AnryMc) 709 21.01.12 18:31 Сейчас в теме
(4) Olvia,
Я имел ввиду, что в ячейках таблицы в 1С можно сделать "гистограмму" только средствами 1С без внешних компонент. Т.е. получить тот же результат нагладности без выгрузки в Excel.
6. Olvia 15 22.01.12 00:13 Сейчас в теме
(5) AnryMc, да, действительно, есть такой вид универсального элемента управления ))) Я все-таки думаю, его нельзя применить к моей задаче: итоговый результат таки потеряет в визуализации... Если для "значков" еще можно организовать отдельные колонки, то что делать с количеством конечных объектов и размещением их в одной таблице со сторонними показателями? В любом случае, было бы интересно посмотреть на 1С-ную реализацию, пожалуйста, если вспомните, где видели, скиньте ссылку
7. anry mc (AnryMc) 709 23.01.12 12:07 Сейчас в теме
(6) Olvia,
Нашёл - http://infostart.ru/public/80628/ "Микрографики в табличной части"
Если еще добавить итоги по списку с отбором (или динамическому) то можно все это делать в 1С
8. Olvia 15 24.01.12 11:59 Сейчас в теме
(7) AnryMc, а... так вот почему мне самостоятельно не удалось найти - вертикальные рисочки ))))) правильно, зачем нам графические объекты, когда есть спец.кнопки клавиатуры )) Однако, признаю, что мне бы и в голову не пришло решать поставленную задачу подобным способом, автору - однозначно "плюс" за творчество!
Не поленившись скачать и затестить обработку, вывод следующий: используя 4 цвета, 50 строк данных, 2 вида показателей с двумя группировками и одной колонкой значков, вижу заметные тормоза при пересчете (использую ПриПолученииДанных вместо ПриВыводеСтроки, шаблон строки перед обходом таблицы, глобальные переменные цвета и значков), поэтому, к сожалению, в моем случае такое элегантное решение применить нельзя... Что же касается фильтра: реализовать отбор по цвету ячейки или части слова не составляет труда, но при прочих указанных условиях выполнение задачи таким способом кажется мне долговременным и даже в какой-то степени извращенским (( В любом случае, спасибо, AnryMc, за интересную ссылку
9. anry mc (AnryMc) 709 24.01.12 13:11 Сейчас в теме
(8) Olvia,
Я сослался на идею. Алгоритм тормоз - согласен. Построение в цикле...
Я бы например формировал "Шаблонную строку" при установке Маштаба, а затем Лев(ШаблоннаяСтрока, Значение)
Бысродействие вырастет в разы...
Или еще как - не думал еще
10. igor sarafanov (ms200999) 25.01.12 08:38 Сейчас в теме
Познавательно, спасибо.