Создаем свой генератор отчетов Microsoft Excel для Delphi

Зачем нужна система формирования отчетов

Отчеты Excel из DelphiВ свое время, когда для системы учета потребовалось сделать систему вывода информации на печать перебрал много разных систем формирования отчетов — некоторые, как FastReport, имели в составе редакторы, и замечательно формировали печатные формы, но чаще всего пользователям нравится, когда можно быстро и красиво получить информацию в ихнем любимом Excel, не прибегая к конверторам и т.п.

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

 

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

Устройство собственного генератора отчетов для Excel

Основу шаблона составляют “бэнды”. Бэнд это одна или более строк, расположенных подряд и имеющих одну и ту-же метку — название бэнда. Оно проставляется в первой колонке каждой строки. Вот так примерно будет выглядеть готовый шаблон отчета:

Пример шаблона отчета Excel для генератора отчетов

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

Сам класс отчета будет очень простым:

 TA7xReport = class(TComponent)
  private
    Excel, TemplateSheet: Variant;
    Progress: TWcProgress;
    CurrentLine: integer; // текущая строка построения отчета
    FirstBandLine, LastBandLine: integer; // положение последнего добавленного бэнда
  protected
  public
    procedure OpenTemplate(FileName: string);
    procedure PasteBand(BandName: string);
    procedure SetValue(VarName: string; Value: Variant);
    procedure Show;
    destructor Destroy; override;
  published
  end;

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

  Excel := CreateOleObject('Excel.Application');
  Excel.Workbooks.Open(FileName, True, True);
  TemplateSheet := Excel.Workbooks[1].Sheets[1];
  Excel.DisplayAlerts := False; // Чтобы подавить сообщение об ошибке при замене ненайденного значения в операции SetValue
  CurrentLine := 1;
  Progress := TA7xProgress.Create(Self);
  Application.ProcessMessages;

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

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

Технология добавления бэндов предполагает что мы вставляем бэнды сразу перед шаблоном, оставляя таким образом шаблон всегда в конце документа. Для этого мы находим нужный нам бэнд по имени, начиная искать после последнего вставленного нами бэнда и далее, а кроме того определяем и длину найденного бэнда. Тоесть следующий кусок кода в переменных FirstBandLine и LastBandLine сохранит начало и конец бэнда:

  FirstBandLine := 0; LastBandLine := 0;
  i := CurrentLine;
  while ((LastBandLine = 0) and (i < CurrentLine + MaxBandLines)) do begin
    v := Variant(TemplateSheet.Cells[i, 1].Value);
    if (varType(v) = varOleStr) and (FirstBandLine = 0) then begin
      if v = BandName then begin // нашли начало бенда
        FirstBandLine := i;
      end;
    end;
    if (FirstBandLine <> 0) then begin
      if not ((varType(v) = varOleStr) and (v = BandName)) then LastBandLine := i - 1;
    end;
    inc(i);
  end;

Далее этот найденный шаблон копипастим сразу за последним вставленным нами бэндом, если такой уже был:

  Range := TemplateSheet.Rows[IntToStr(FirstBandLine) + ':' + IntToStr(LastBandLine)];
  Range.Copy;
  Range := TemplateSheet.Rows[IntToStr(CurrentLine) + ':' + IntToStr(CurrentLine)];
  Range.Insert;

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

  CurrentLine := CurrentLine + (LastBandLine - FirstBandLine) + 1;
  // вычисляем позицию куда был скопирован бэнд
  FirstBandLine := CurrentLine - (LastBandLine - FirstBandLine) - 1;
  LastBandLine := CurrentLine - 1;

Следующий метод, который будет использоваться для вывода значений на уже скопированный кусок шаблона – SetValue. Код этого метода предельно прост, так как выполняет поиск-замену значения в заданном диапазоне средствами самого экселя:

  Range := TemplateSheet.Rows[IntToStr(FirstBandLine) + ':' + IntToStr(LastBandLine)];
  Range.Replace(VarName, s);

Метод SetValue должен вызываться столько раз, сколько у нас находится значений на нужном нам бэнде. Ну и наконец после того как все бэнды будут выведены, вызывается последнй метод – Show, в котором наконец удаляется сам шаблон, который вытеснялся в конец документа, и эксель перевдится из невидимого состояния в видимое.

Здесь стоит упомянуть, что если скажем прервать отладку отчета до вызова Excel.Visible := true; то эксель так и останется висеть в памяти невидимкой, а для следующего отчета будет создана новый экземпляр Excel.

Как выглядит применение генератора отчетов в Delphi-коде

Теперь самое интересное – пример использования этого генератора отчетов:

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

procedure TdrashodForm.Print(Template: string);
var i: integer;
  summa_: double;
  h : string;
begin
  Rep.OpenTemplate(Template);
  Rep.PasteBand('TITLE');
  Rep.SetValue('#ID_RASHOD#', ID_RASHOD);
  Rep.SetValue('#PB_NAME#', PbFrame.PbEdit.Text);
  Rep.SetValue('#D#', DateToStr(NaklDTP.Date));
  Rep.SetValue('#POSTNAME#', PostEdit.Text);
  i := 1; summa_ := 0;
  NaklQuery.First;
  while not NaklQuery.Eof do begin
      Rep.PasteBand('STR');
      Rep.SetValue('#N#', i);
      h := NaklQuery['KH_NAME'];
      if h<>'' then h := '['+h+']';
      Rep.SetValue('#NM_NAME#', NaklQuery['NM_NAME']+' '+h);
      Rep.SetValue('#QUANT#', NaklQuery['RSD_QUANT']);
      Rep.SetValue('#SUMMA#', coalesce(NaklQuery['RSD_OSUMMA'],0));
      Rep.SetValue('#NM_UNIT#', coalesce(NaklQuery['NM_UNIT'],''));
      Rep.SetValue('#ZK_NUMBER#', coalesce(NaklQuery['ZK_NUMBER'],''));
      Rep.SetValue('#TW_NAME#', coalesce(NaklQuery['TW_NAME'],''));
      summa_ := summa_ + coalesce(NaklQuery['RSD_OSUMMA'],0);
      inc(i);
    NaklQuery.Next;
  end;
  Rep.PasteBand('FOOT');
  Rep.SetValue('#SUMMA_#', summa_);
  Rep.Show;
end;

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

И теперь самое главное…

Где взять эту замечательную систему да еще и бесплатно

Домашняя страница проекта, находится на a7in.com

11 комментариев к “Создаем свой генератор отчетов Microsoft Excel для Delphi”

  1. Отличная вещь!!!!!!!!!!!!!!!!
    Вопрос — как добиться того, что бы если наименование товара длинное, то строка изменяла высоту под размер товара, но не для каждого товара одна высота!!!!!
    Тоесть один товар — длинный — высоту строки изменили в зависимости от длины второй товар не длинный высоту не меняем т д
    Огромное спасибо!!! а компонент то что надо!!!!!!!!!!!!!!

    a7:Легко! Поскольку шаблон отчета это обычный эксель то нужно в ячейке шаблона просто установить параметр “переносить по словам” ну и все остальные если надо параметры крутить. Все точно также как делается в экселе при обычной работе. Сам генератор отчета в доработках не нуждается

  2. Как я понял для Delphi RAD XE он не подойдет ?

    a7:Этот отчетный компонент не содержит ничего сложного в себе и его можно без проблем скомпилировать под нужную версию Delphi. Но я все же уже добавил сборку и под Delphi XE2

  3. Юрий Анатольевич

    Идея очень хорошая, что-то похожее в 1С.
    Вот бы хотя бы идею расшифровки данных в ячеке.
    Когда данные уже отданы в эксель то уже наврядли это получиться…

    a7: Я думаю что теоретически это все-же возможно, например вмонтировав в Excel код VBScript, но это действительно очень нетривиальный и тернистый путь — я таким заниматься пожалуй пока не буду 🙂

  4. можно ли вставить рисунок с помощью этого компонента?

    a7: К сожалению нет. Но есть возможность вставлять строки с рисунком, если они в таком виде будут в шаблоне. Можно даже заготовить несколько альтернативных бендов, если нужны несколько заранее известных вариантов рисунка, но вставлять произвольный рисунок на этапе генерации отчета все-же нельзя.

  5. Немного дописал исходный код:
    Процедура смены активного листа (иногда нужно начать заполнение не с первого листа, а потом вернуться к первому).

    procedure TA7Rep.ActivateWorkSheet(Name: string);
    begin
      if Name='' then
        TemplateSheet := Excel.Workbooks[1].Sheets[1]
      else
        TemplateSheet := Excel.Workbooks[1].Sheets[Name];
    
      CurrentLine := 1;
      MaxBandColumns := TemplateSheet.UsedRange.Columns.Count;
    
      if Assigned(Progress) then
        Progress.L3.Caption := 'Sheet: ' + Name;
    end;
    

    Процедура для переноса таблицы в любую часть отчета, например, справа от других таблиц. Таблица с фиксированными размерами, строки в нее не вставляются. Таблица шаблона выделяется в именованный диапазон, например totals, а в начальной ячейке, куда вставлять таблицу, пишется идентификатор, например, :totals_to. Эту таблицу в шаблоне не помечаем именами бэндов.

    procedure TA7Rep.PasteRange(RangeName, PasteTo: string);
    var
      Range: Variant;
    begin
      Range := TemplateSheet.Range[RangeName];
      Range.Copy;
      Range := TemplateSheet.Cells.Find(PasteTo);
      Range.Insert;
      Range := TemplateSheet.Cells.Find(PasteTo);
      Range.Delete;
    end;

    Перед переносом таблицы необходимо заполнить ее значения с помощью этой процедуры (бэндов-то нет).

    procedure TA7Rep.SetValueNamed(VarName: string; Value: Variant);
    var
      Range: Variant;
    begin
      Range := TemplateSheet.Cells.Find(VarName);
      if Value = null then
        Range.Replace(VarName, '')
      else
        Range.Replace(VarName, VarToStr(Value));
    end;
  6. Евгений

    Можно ли с помощью Вашего компонента передать в ексель цвет ячейки?

    1. Можно! Если добавить туда процедуру установки цвета ячейки:

      procedure TA7Rep.SetColor(VarName: string; Color: Variant);
      var
        x, y : Integer;
      begin
        ExcelFind(VarName, x, y, xlValues);
        if Color=null then begin
          TemplateSheet.Cells[y, x].Interior.Pattern := -4142; //xlNone;
        end else begin
          TemplateSheet.Cells[y, x].Interior.Pattern := 1; //xlSolid;
          TemplateSheet.Cells[y, x].Interior.PatternColorIndex := -4105; // xlAutomatic;
          TemplateSheet.Cells[y, x].Interior.Color := Color;
        end;  
      end;
      

      Цвет 255 это красный. Все это можно подсмотреть если перейти в режим записи макроса в Excel и выполнив интересующие вас действия посмотреть на код.

  7. Александр

    Добрый День!
    В Excel 2016 не удаляются Наименование бандов, как сделать, чтобы они удалялись?

  8. Александр

    Вопрос снимается, в procedure TA7Rep.PasteBand(BandName: string);
    вот эти строки были закомменктированны
    { // delete band name from result lines
    for i := CurrentLine to CurrentLine + (LastBandLine — FirstBandLine) do begin
    TemplateSheet.Cells[i, 1].Value := »;
    end;
    }
    Эти комментарии, были убраны и все заработало

Оставьте комментарий

Ваш адрес email не будет опубликован.