Итоги в Excel

[tolstjak]

Здравствуйте уважаемые!!!

данная тема обсуждалась, но что-то я не нашел ответа на интересующий меня вопрос.
Можно ли без особых заморочек получить итоги отчета по нужным столбцам?
Данные переносятся в эксел с помощью текстового буфера.
Знаю номер первой и последней строки в экселе.
Хочу, что-то примерно такое:

Range("B380").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-377]C:R[-1]C)"

Лучше с примером для особо одаренных.

Забыл написать, что итоги надо подводить из Аксапты

За ранее благодарен.

[lev]

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

[Eldar9x]

    boolean ret;
    ComExcelDocument_ru excel = new ComExcelDocument_RU();
    COM app;
    COM range;
    ;
    excel.newFile("", false);

    app = excel.getApplication();

    range = app.range("A10");

    range.value2("=СУММ(R[-9]C:R[-1]C)");

    excel.visible(true);

Только зачем это? Не проще накоплять сумму в переменную, а потом вывести как обычную строку?

[tolstjak]

Спасибо большое

[zZ_TOP_Zz]

Цитата:

Сообщение от Eldar9x

Только зачем это? Не проще накоплять сумму в переменную, а потом вывести как обычную строку?

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

[lev]

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

[Eldar9x]

Цитата:

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

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

[Gustav]

Цитата:

Сообщение от zZ_TOP_Zz

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

Пользователя такая статичность может огорчить (всё ж таки в Excel выводим, а не в snapshot). Поэтому он может быть вам очень благодарен за возможность вставить в середину выгруженного "отчета" каких-то своих дополнительных строк, и чтобы "оно само сразу пересчиталось".

[lev]

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

[lev]

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

[Eldar9x]

Цитата:

Поэтому он может быть вам очень благодарен

Его отношение в корне изменится )), когда он узнает, что данные неверны

[Gustav]

Размялся. Набросал метод (excelInsertSumBelowColumns), помещающий суммирующие ячейки в заданный итоговый диапазон под колонками с данными (_rangeSum). Если в диапазоне несколько ячеек, то суммирующие формулы будут созданы для каждого столбца над этими ячейками. Если заданный итоговый диапазон содержит более одной строки (случайно, ошибочно), то формулы будут помещены только в первую строку этого диапазона. Второй и третий необязательные параметры - первая и последняя строка суммирования данных (имеется ввиду номер строки Excel). Если эти параметры не указаны, то в конкретном столбце суммируются все ячейки, начиная со строки 1 и кончая строкой перед ячейкой с суммой.

В рамках примера демонстрируется в виде вложенной функции. В приложении может жить - как больше нравится: либо как метод класса ComExcelDocument_RU, либо как статический метод класса Global, либо как еще фантазия подскажет (я лично помещу статический метод в персональный "Global").

static void Job134(Args _args)
{
    ComExcelDocument_ru excel = new ComExcelDocument_RU();
    COM app;
    COM book;
    COM range;

/////////////////////////////////////////////////////////
    void excelInsertSumBelowColumns(COM _rangeSum,
                                    int _rowFirst   = 1,
                                    int _rowLast    = 0)
    {
        COM range1;
        int rowSum;
        ;
        range1 = _rangeSum.Rows();
        range1 = COM::createFromVariant(range1.Item(1));

        rowSum = range1.Row();

        if (rowSum == 1)
        {
            warning('Нечего суммировать!');
            return;
        }

        if (_rowFirst < 1)
            _rowFirst = 1;

        if (_rowFirst >= rowSum)
            _rowFirst = rowSum - 1;

        if (!_rowLast || _rowLast >= rowSum)
            _rowLast = rowSum - 1;

        if (_rowLast && _rowLast < _rowFirst)
            _rowLast = _rowFirst;

        range1.FormulaR1C1(strFmt('=SUM(R[-%1]C:R[-%2]C)',
                           rowSum-_rowFirst, rowSum-_rowLast));
    }
/////////////////////////////////////////////////////////
    ;

    excel.newFile("", false);

    book = excel.getComDocument();
    app = book.Parent();

    range = app.range("A10:F10");

    excelInsertSumBelowColumns( range );

    excel.visible(true);
}

[zZ_TOP_Zz]

Цитата:

Сообщение от Gustav

Пользователя такая статичность может огорчить (всё ж таки в Excel выводим, а не в snapshot). Поэтому он может быть вам очень благодарен за возможность вставить в середину выгруженного "отчета" каких-то своих дополнительных строк, и чтобы "оно само сразу пересчиталось".

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

[tolstjak]

Всем еще раз большое спасибо.

[Владимир Максимов]

До кучи

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

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

Для копирования строк в классе ComExcelDocument_RU сделал такой метод

/*
    Вставка указанного количества строк, как копии одной строки шаблона
    _rowTemplate - номер строки, которая будет размножена (скопирована)
    _rowsInsert - количество строк, вставляемых ПОД укаазнной строкой шаблона
    _workSheetId - номер листа Excel в котором это все происходит. Соответственно изменен и метод findRange()
*/
void rtg_insertRowsAsCopy(Row _rowTemplate, Row _rowsInsert, int _workSheetId = 1)
{
    COM     comRowSource;
    Row     currentRow,
            insertedRows;

    MSOfficeBookMark_RU bookMark;
    ;

    #define.xlDown(-4121)

    if (_rowsInsert < 2)
    {
        return;
    }

    // Итерационный цикл
    // Вставляю сначала 1, потом 2, 4, 8 и т.п. строк на последнем шаге вставляю остаток строк
    currentRow = 1;
    while (currentRow < _rowsInsert)
    {
        if (currentRow * 2 <= _rowsInsert)
        {
            insertedRows = currentRow;
            currentRow  += insertedRows;
        }
        else
        {
            insertedRows = _rowsInsert - currentRow;
            currentRow   = _rowsInsert;
        }
        bookMark        = int2str(_rowTemplate) + ":" + int2str(_rowTemplate + insertedRows - 1);
        comRowSource    = this.findRange(bookMark, _workSheetId);
        comRowSource.copy();
        comRowSource.insert(#xlDown);
    }    // while (currentRow < _rowsInsert)

}

Идея заключается в том, чтобы, по возможности, переложить всю работу по наведению красоты на сам Excel. А из Axapta импортировать только "гладкий" список, не задумываясь о его форматировании и обслуживании.

[Gustav]

Владимир, а в чём смысл этой прогрессивной (2,4,8,16...) вставки?

Есть первая строка - "начало", есть вторая - "конец", есть третья - "сумма". Для добавления 100 строчек между "началом" и "концом" вручную требуется встать на 2-ю строку, расширить выделение до 101-й включительно и потом выполнить команду меню "Вставить" - "Строки". Все вставленные строки АВТОМАТИЧЕСКИ унаследуют формат первой строки - заливку, шрифт и т.п. НО, увы, не границы (borders)! О границах - ниже. Программно на VBA вставку 100 строчек можно выполнить одним оператором:

 
Rows("2:101").Insert Shift:=xlDown.

Теперь о границах. Чтобы размножились и границы тоже, надо чтобы ячейки строки-"начала" и строки-"конца" имели одинаковое обрамление в одном и том же столбце. Если будет разное, то в данном столбце не будет выполнено копирование бордюров. Но, как правило, в шаблонах первая и последняя строка выглядят одинаково с точки зрения границ, поэтому проблем быть не должно. Понятно, что если первая и последняя должны быть разными (например, в первой верхняя граница - жирная, а в последней - нижняя) , то в шаблон следует добавить еще пару строк и вставлять между ними (т.е. между второй и предпоследней).



На 1-й картинке ячейка B1 имеет жирную границу с четырех сторон. Ячейка B2 не имеет границы, или другими словами "имеет границы, отличные от ячейки B1". Поэтому на 3-й картинке границы в столбце B не копируются.

На 4-й картинке одинаковые границы имеются у обеих ячеек. На 5-й - видим соответствующий результат.

[Владимир Максимов]

Не заметил подобную "фишку". Давно я этот код писал. Теперь вот исправлять придется...

Кстати, в моем примере команда

Rows("2:101").Insert Shift:=xlDown

это не есть "Вставка - Строки". Это команда "Вставка - Скопированные ячейки". В этом случае копируется вообще ВСЕ что есть в первой строке. И границы в том числе.

Последовательность действий такая:

1. Выделить строку - образец
2. Пункт меню: Правка - Копировать
3. Выделить строки в которые надо сделать вставку
4. Пункт меню: Вставка - Скопированные ячейки

Этот самый пункт "Скопированные ячейки" появляется только если есть активный буфер обмена.