OExport full demo

This code creates a big workbook with several sheets that demonstrate all capabilities of OExport. The generated documents from this demo can be downloaded from OExport homepage.

Do not forget to add both units OExport and OExport_Vcl (or OExport_FMX for FireMonkey) to your uses clause!

uses
  {...}, OExport, OExport_Vcl, OExport_VclForms;

procedure TForm1.DoFullExport;
var
  xExport: TOExport;

  procedure AddWorkSheet_Information;
  var I: Integer;
  begin
    with xExport.AddWorkSheet('Information') do begin
      AddRow.AddCellString(Title).SetFontSize(20);
      AddRow;

      AddRow.AddCellString(
        'OExport is a modern XLSX/ODS export library for Delphi and Lazarus.'#13#10+
        'This file was generated directly from a Delphi application and has not '+
        'been modified after the export.'#13#10#13#10+
        'Registered users may get the full source code that was used to create '+
        'this document from the OExport online documentation. '+
        'Please use the link below.'
        ).SetColSpan(8).SetRowSpan(5);
      for I := 0 to 4 do
        AddRow;
      AddRow.AddCellString('Click here to download pascal code for this document!').
        SetHyperLink('http://www.kluug.at/oexport-help/code-snippets/'+
          'oexport-full-demo/').SetFontColor(clBlue).SetFontStyle([fsUnderline]).
        SetColSpan(8);

      AddRow;
      AddRow;
      AddRow.AddCellString('Please note that if you try to open ODS file '+
        'in Excel and XLSX file in Calc, you may notice that some properties '+
        'are missing. This is due to the fact that Excel doesn''t read all '+
        'features of ODS files properly and Calc doesn''t read all XLSX features '+
        'properly.'#13#10+
        'ODS in Calc and XLSX in Excel should look almost exactly the same.'
        ).SetColSpan(8).SetRowSpan(5);
    end;
  end;

  procedure AddWorkSheet_CellTypes;
  begin
    with xExport.AddWorkSheet('Cell types') do begin
      AddRow.AddCellString(Title).SetFontSize(20);
      AddRow;

      AddRow.AddCellString('Strings').SetFontSize(16);
      with AddRow do begin
        AddCellString('String: see correct whitespace handling').SetWidth(300);
        AddCellString(' my custom  string'#13#10' $ <>  ').SetWidth(160).SetHeight(50);
      end;
      with AddRow do begin
        AddCellString('Rich text');
        AddCellString.
          AppendFormattedText('Rich text ', 0, clRed, 'Courier New').
          AppendFormattedText('example ', 0, clNone, 'Times New Roman', efsYes).
          AppendFormattedText('- ').
          AppendFormattedText('nice and easy!', 0, clNavy, '', efsAuto, efuDouble);
      end;
      with AddRow do begin
        AddCellString('Mass–energy equivalence');
        AddCellString.
          AppendFormattedTextFontStyle('E', efsAuto, efuAuto, efsYes).
          AppendFormattedText(' = ').
          AppendFormattedTextFontStyle('mc', efsAuto, efuAuto, efsYes).
          AppendFormattedTextFontStyle('2', efsAuto, efuAuto, efsAuto, efsAuto, efeSuperscript);
      end;
      with AddRow do begin
        AddCellString('Hyperlink');
        AddCellString('Go to OExport homepage').SetHyperLink('http://www.kluug.at').
          SetFontColor(clBlue).SetFontStyle([fsUnderline]);
      end;
      with AddRow do begin
        AddCellString('Create new email message');
        AddCellString('Contact the White House').
          SetHyperLink('mailto:contact@whitehouse.gov?subject=I%20am%20bin%20Laden!').
          SetFontColor(clBlue).SetFontStyle([fsUnderline]);
      end;

      AddRow;
      AddRow.AddCellString('Numbers').SetFontSize(16);
      with AddRow do begin
        AddCellString('Number without formatting');
        AddCellNumber(15.1254, -1);
      end;
      with AddRow do begin
        AddCellString('Float number (8 dec. places)');
        AddCellNumber(15.125413248435321, 8);
      end;
      with AddRow do begin
        AddCellString('Float number (2 dec. places)');
        AddCellNumber(15.1254, 2);
      end;
      with AddRow do begin
        AddCellString('Integer number');
        AddCellNumber(15.1254, 0);
      end;
      with AddRow do begin
        AddCellString('Percent (1 dec. place)');
        AddCellPercent(15.1254, 1);
      end;
      with AddRow do begin
        AddCellString('Currency (symbol before value)');
        AddCellCurrency(15.1254, '€', ecpBefore);
      end;
      with AddRow do begin
        AddCellString('Currency (symbol before value with space fill)');
        AddCellCurrency(15.1254, '€', ecpBeforeFill);
      end;
      with AddRow do begin
        AddCellString('Currency (symbol after value)');
        AddCellCurrency(15.1254, 'Kč', ecpAfter);
      end;
      with AddRow do begin
        AddCellString('Currency (with thousand separator)');
        AddCellCurrency(15000, '$', ecpBefore).SetThousandSep(True);
      end;
      with AddRow do begin
        AddCellString('Currency (red when negative)');
        AddCellFormulaCurrency('=-'+ExcelRange(1, Rows.Count-2), '$', ecpBefore).
          SetThousandSep.SetNegativeRed;
      end;
      with AddRow do begin
        AddCellString('Scientific (2 dec places, 2 exp places)');
        AddCellScientific(15.1254);
      end;
      with AddRow do begin
        AddCellString('Scientific (3 dec places, 1 exp place)');
        AddCellScientific(15.1254, 3, 1);
      end;

      AddRow;
      AddRow.AddCellString('Formulas').SetFontSize(16);
      with AddRow do begin
        AddCellString('Formula formatted as number');
        AddCellFormulaNumber('=PI()*2');
      end;
      with AddRow do begin
        AddCellString('Formula formatted as percent');
        AddCellFormulaPercent('=1/3');
      end;
      with AddRow do begin
        AddCellString('Formula formatted as currency');
        AddCellFormulaCurrency('=SUM('+ExcelRange(1, Rows.Count-3, 1, 2)+')',
          'USD', ecpBefore);
      end;

      AddRow;
      AddRow.AddCellString('Date / time').SetFontSize(16);
      with AddRow do begin
        AddCellString('Date');
        AddCellDate(Now);
      end;
      with AddRow do begin
        AddCellString('Date/time with seconds');
        AddCellDateTime(Now);
      end;
      with AddRow do begin
        AddCellString('Date/time without seconds');
        AddCellDateTimeWS(Now);
      end;
      with AddRow do begin
        AddCellString('Time with seconds');
        AddCellTime(Now);
      end;
      with AddRow do begin
        AddCellString('Time without seconds');
        AddCellTimeWS(Now);
      end;
      with AddRow do begin
        AddCellString('ISO/SQL date format');
        AddCellDate(Now).SetDateTimeFormat('YYYY-MM-DD');
      end;
      with AddRow do begin
        AddCellString('Custom date format');
        AddCellDate(Now).SetDateTimeFormat('mmmm d, yyyy');
      end;

      AddRow;
      AddRow.AddCellString('Comments').SetFontSize(16);
      with AddRow do begin
        with AddCellString('This cell has a simple comment') do begin
          Comment.Text := 'Commented cell';
          Comment.Rect.Height := 50;
        end;
      end;
      with AddRow do begin
        with AddCellString('This cell has a rich-text comment') do begin
          Comment.AppendFormattedTextFontStyle('Kluug.net'+sLineBreak, [fsBold]);
          Comment.AppendFormattedTextFontColor('OExport library.', clRed);
          Comment.VAlignment := cavCenter;
          Comment.Alignment := cahCenter;
          Comment.Rect.Height := 70;
          Comment.BGColor := $FFFFAA;
        end;
      end;

    end;
  end;

  procedure AddWorkSheet_CellFormatting;
  var
    I: Integer;
  const
    DefColors: Array[0..19] of TColor =
      ($a87245, $4346ab, $4ea689, $8f5871, $b09941, $3d84dc, $d0aa93, $9293d2, $97ceba, $be9caa,
       $a87245, $4346ab, $4ea689, $8f5871, $b09941, $3d84dc, $d0aa93, $9293d2, $97ceba, $be9caa);
  begin
    with xExport.AddWorkSheet('Cell formatting') do begin
      AddRow.AddCellString(Title).SetFontSize(20);
      Cols[0].Width := 200;
      Cols[1].Width := 150;

      AddRow;
      AddRow.AddCellString('Horizontal alignment').SetFontSize(16);
      for I := 0 to Ord(High(TCellHAlignment)) do
      with AddRow do begin
        CalculateRowHeight := erhForceAuto;
        AddCellString(GetEnumName(TypeInfo(TCellHAlignment), I));
        AddCellString('some long text to show different horizontal alignment').
          SetWrapText.SetAlignment(TCellHAlignment(I));
      end;

      AddRow;
      AddRow.AddCellString('Vertical alignment').SetFontSize(16);
      for I := 0 to Ord(High(TCellVAlignment)) do
      with AddRow do begin
        Height := 80;
        AddCellString(GetEnumName(TypeInfo(TCellVAlignment), I));
        AddCellString('some long text to show different vertical alignment').
          SetWrapText.SetVAlignment(TCellVAlignment(I));
      end;

      AddRow;
      AddRow.AddCellString('Font family').SetFontSize(16);
      with AddRow do begin
        AddCellString('Times New Roman');
        AddCellString('Different font families').SetFontName('Times New Roman');
      end;
      with AddRow do begin
        AddCellString('Courier New');
        AddCellString('Different font families').SetFontName('Courier New');
      end;

      AddRow;
      AddRow.AddCellString('Font styles').SetFontSize(16);
      for I := 0 to Ord(High(TFontStyle)) do
      with AddRow do begin
        AddCellString(GetEnumName(TypeInfo(TFontStyle), I));
        AddCellString('Different font styles').SetFontStyle([TFontStyle(I)]);
      end;

      AddRow;
      AddRow.AddCellString('Font sizes').SetFontSize(16);
      for I := 6 to 15 do
      with AddRow do begin
        AddCellString(IntToStr(I));
        AddCellString('Different font size').SetFontSize(I);
      end;

      AddRow;
      AddRow.AddCellString('Font colors').SetFontSize(16);
      with AddRow do begin
        AddCellString('clMaroon');
        AddCellString('Different font color').SetFontColor(clMaroon);
      end;
      with AddRow do begin
        AddCellString('clGreen');
        AddCellString('Different font color').SetFontColor(clGreen);
      end;

      AddRow;
      AddRow.AddCellString('Background colors').SetFontSize(16);
      with AddRow do begin
        AddCellString('clYellow');
        AddCellString('Different cell color').SetBGColor(clYellow);
      end;
      with AddRow do begin
        AddCellString('clSkyBlue');
        AddCellString('Different cell color').SetBGColor(clSkyBlue);
      end;

      AddRow;
      AddRow.AddCellString('Border styles and colors').SetFontSize(16);
      for I := 0 to Ord(High(TCellBorderStyle)) do begin
        with AddRow do begin
          AddCellString(GetEnumName(TypeInfo(TCellBorderStyle), I)+', '+
            ColorToString(DefColors[I]));
          AddCellString('Different border style').
            SetBorders(cbAll, TCellBorderStyle(I), DefColors[I]);
        end;
        AddRow.SetHeight(5);
      end;

      AddRow;
      AddRow.AddCellString('Borders').SetFontSize(16);
      for I := 0 to Ord(High(TCellBorder)) do begin
        with AddRow do begin
          AddCellString(GetEnumName(TypeInfo(TCellBorder), I));
          AddCellString('Different border').
            SetBorder(TCellBorder(I), ebThick, clRed);
        end;
        AddRow.SetHeight(5);
      end;
      with AddRow do begin
        AddCellString('cbCross');
        AddCellString('Different border').SetBorders(cbCross, ebThick, clRed);
      end;
      AddRow.SetHeight(5);
      with AddRow do begin
        AddCellString('cbAll');
        AddCellString('Different border').SetBorders(cbAll, ebThick, clRed);
      end;
      AddRow.SetHeight(5);

      AddRow;
      AddRow.AddCellString('Wrap text').SetFontSize(16);
      for I := 0 to 1 do
      with AddRow do begin
        AddCellString(BoolToStr(Boolean(I), True));
        AddCellString('Text to demonstrate the wrap text property').
          SetWrapText(Boolean(I)).SetHeight(50);
      end;

      AddRow;
      AddRow.AddCellString('Text flow').SetFontSize(16);
      with AddRow do begin
        AddCellString('Top to bottom');
        AddCellString('Custom text').SetOrientation(efoTopToBottom).
          SetAlignment(cahCenter).SetVAlignment(cavCenter).SetHeight(200);
      end;

      AddRow;
      AddRow.AddCellString('Text rotation').SetFontSize(16);
      for I := +6 downto -6 do
      with AddRow do begin
        Height := 60;
        AddCellString(IntToStr(I*15)+' degrees');
        AddCellString('rotation').SetRotation(I*15).
          SetAlignment(cahCenter).SetVAlignment(cavCenter);
      end;

    end;
  end;

  procedure AddWorkSheet_ConditionalFormatting;
  var
    I: Integer;
  begin
    with xExport.AddWorkSheet('Conditional formatting') do begin
      AddRow.AddCellString(Title).SetFontSize(20);
      Cols[0].Width := 200;
      Cols[1].Width := 150;

      AddRow;
      AddRow.AddCellString('Constant numbers as limits').SetFontStyle([fsBold]);
      for I := 0 to 9 do begin
        with AddRow.AddCellNumber(I).ConditionalFormatting do begin
          AddRule(ecEqual, 0).SetFontColor(clWhite).SetBGColor(clRed);
          AddRule(ecGreaterEqual, 2).SetFontColor(clYellow).SetBGColor(clBlue);
          AddRule(ecBetween, 6, 8).SetFontColor(clWhite).SetBGColor(clGreen).SetFontStyle([fsBold]);
        end;
      end;

      AddRow;
      AddRow;
      AddRow.AddCellString('Formulas as limits').SetFontStyle([fsBold]);
      with AddRow.AddCellNumber(7).ConditionalFormatting do begin
        AddRule(ecBetween, 'A6', 'A13').SetFontColor(clRed).SetBGColor(clYellow);
      end;
    end;
  end;

  procedure AddWorkSheet_SheetColRowStyle;
  var
    I, J: Integer;
    xR: TExportRow;
    xC: TExportCell;
  begin
    with xExport.AddWorkSheet('WS + Col + Row style') do begin
      //SET WORKSHEET STYLE
      Style.SetBGColor($DDFFDD).SetFontName('Segoe UI').SetFontSize(12);

      //SET COLUMN STYLES
      Cols[2].Style.SetBGColor(clRed).SetFontColor(clWhite).SetAlignment(cahCenter);
      Cols[5].Style.SetBGColor(clMaroon).SetFontColor(clWhite).SetAlignment(cahRight);

      AddRow.AddCellString('Worksheet + Col + Row styles').SetFontSize(20);
      AddRow;

      for I := 0 to 9 do begin
        xR := AddRow;
        //SET ROW STYLES
        case I of
          2: xR.Style.SetBGColor(clBlue).SetFontColor($00FFFF);
          5: xR.Style.SetBGColor(clNavy).SetFontColor($00FFFF);
        end;

        for J := 0 to 9 do begin
          xC := xR.AddCellString(ExcelRange(J, xR.IndexInList));
          if ((I = 2) and (J = 2)) or ((I = 5) and (J = 5)) then
            xC.Style.Assign(xC.Col.Style);
          case I of
            0: xC.Borders.SetBorder(cbTop, ebThick, clBlack);
            9: xC.Borders.SetBorder(cbBottom, ebThick, clBlack);
          end;
          case J of
            0: xC.Borders.SetBorder(cbLeft, ebThick, clBlack);
            9: xC.Borders.SetBorder(cbRight, ebThick, clBlack);
          end;
        end;
      end;
    end;
  end;

  procedure AddWorkSheet_ColRowSpan;
  begin
    with xExport.AddWorkSheet('Column + Row span') do begin
      AddRow.AddCellString(Title).SetFontSize(20);
      AddRow;

      NewCellStringAlignment := cahCenter;
      NewCellVAlignment := cavCenter;
      NewCellBorders.SetBorders(cbAll, ebThin, clBlack);
      with AddRow do begin
        AddCellString(ExcelRange(0, Rows.Count-1, 1, 4)).SetRowSpan(4);
        AddCellString(ExcelRange(1, Rows.Count-1));
        AddCellString(ExcelRange(2, Rows.Count-1, 2, 1)).SetColSpan(2);
      end;
      with AddRow do begin
        AddCellString;
        AddCellString(ExcelRange(1, Rows.Count-1));
        AddCellString(ExcelRange(2, Rows.Count-1));
        AddCellString(ExcelRange(3, Rows.Count-1, 1, 2)).SetRowSpan(2);
      end;
      with AddRow do begin
        AddCellString;
        AddCellString(ExcelRange(1, Rows.Count-1, 2, 2)).SetColSpan(2).
          SetRowSpan(2);
      end;
      with AddRow do begin
        AddCellString;
        AddCellString;
        AddCellString;
        AddCellString(ExcelRange(3, Rows.Count-1));
      end;
      AddRow;
      AddRow;
      with AddRow do begin
        AddCellString('Cell spanned over 4 columns and 3 rows.').
          SetColSpan(4).SetRowSpan(3).
          SetBGColor(clYellow).SetBorders(cbAll, ebDouble, clRed);
      end;
    end;
  end;

  procedure AddWorkSheet_Grouping;
  var
    I, L: Integer;
  begin
    with xExport.AddWorkSheet('Grouping') do begin
      AddRow.AddCellString(Title).SetFontSize(20);
      AddRow;

      for I := 1 to 20 do
        with AddRow do
          for L := 1 to 5 do
            AddCellString(ExcelRange(L-1, RowIndex));

      Cols.GroupCols(2, 4);
      Rows.GroupRows(2, 21);
      Rows.GroupRows(2, 5);
      Rows.GroupRows(10, 15, True);
    end;
  end;

  procedure AddWorkSheet_PrintOptions;
  begin
    with xExport.AddWorkSheet('Print options') do begin
      AddRow.AddCellString(Title).SetFontSize(20);
      Cols[0].Width := 100;
      Cols[1].Width := 200;
      Cols[2].Width := 200;

      AddRow.AddCellString('Take a look at the print preview of this sheet!').
        SetFontSizeAdd(2).SetFontColor(clRed);
      AddRow.AddCellString('This text won''t be printed - there''s been '+
        'PrintRange set');
      AddRow.AddCellString('Table header will be printed on every page');

      AddRow;

      with AddRow do begin
        AddCellString;
        AddCellString('Header row #1').SetFontStyle([fsBold]);
        AddCellString('Header row #2').SetFontStyle([fsBold]);
      end;
      PageSettings.RowsToRepeat.SetRowRange(Rows.Count-1, 1);
      with AddRow do begin
        AddCellString('Header col #1').SetFontStyle([fsBold]);

        AddCellString('Cell on first page');
        AddCellString('Cell on second page');
      end;
      Cols[2].PageBreakBefore := True;
      with AddRow do begin
        AddCellString('Header col #2').SetFontStyle([fsBold]);

        PageBreakBefore := True;
        AddCellString('Cell on third page');
        AddCellString('Cell on forth page');
      end;

      PageSettings.PrintRange.SetRange(0, PageSettings.RowsToRepeat.Row, 3,
        Rows.Count-PageSettings.RowsToRepeat.Row);
      PageSettings.ColsToRepeat.SetColRange(0, 1);

      PageSettings.PrintScale := 200;
      PageSettings.CenterTable := [ectHorizontal, ectVertical];
      PageSettings.PageOrder := efoLeftToRight;

      with Header.Left do begin
        Text := 'OExport demo';
        Font.SetColor(clRed).SetName('Courier New').SetSizeAdd(2);
      end;
      with Header.Right do begin
        Text := 'Date: [DATE]';
        Font.SetStyle([fsBold]);
      end;
      with Footer.Right do begin
        Text := 'Page [PAGENUMBER] of [PAGECOUNT]';
        Font.SetStyle([fsBold]);
      end;
    end;
  end;

  procedure AddWorkSheet_TableHeader;
  var I, L, xAutoFilterTopRow: Integer;
  begin
    with xExport.AddWorkSheet('Table Header') do begin
      Header.Center.Text := 'Multiplication table';

      AddRow.AddCellString('Multiplication table').SetFontSize(20);
      AddRow.AddCellString('Example: Print big table').SetFontSizeAdd(2);
      AddRow.AddCellString('1) These informative rows above the table won''t '+
        'be printed.');
      AddRow.AddCellString('2) The first row and column of the table will be '+
        'printed on every page.');
      AddRow.AddCellString('3) The first row and column are fixed for scrolling.');
      AddRow;
      AddRow;

      //SET PRINT RANGE [1]
      PageSettings.PrintRange.Col := 0;
      PageSettings.PrintRange.Row := Rows.Count;

      //SET REPEAT COLUMN/ROW (THAT WILL BE PRINTED ON EVERY PAGE)
      PageSettings.ColsToRepeat.LeftCol := 0;
      PageSettings.ColsToRepeat.RightCol := 0;
      PageSettings.RowsToRepeat.TopRow := Rows.Count;
      PageSettings.RowsToRepeat.BottomRow := Rows.Count;

      //SET WINDOWS SPLIT / FIXED CELL (FOR DOCUMENT SCROLLING)
      WindowSettings.Split.Row := Rows.Count+1;
      WindowSettings.Split.Col := 1;

      xAutoFilterTopRow := Rows.Count;

      for I := 0 to 100 do
      with AddRow do begin
        for L := 0 to 30 do begin
          if I = 0 then begin
            if L = 0 then
              AddCellString('A * B').SetFontStyle([fsBold]).
                SetBGColor(clYellow).SetAlignment(taCenter)
            else
              AddCellString('A = '+IntToStr(L)).SetFontStyle([fsBold]).
                SetBGColor(clYellow).SetAlignment(taCenter);
          end else if L = 0 then begin
            AddCellString('B = '+IntToStr(I)).SetFontStyle([fsBold]).
              SetBGColor(clYellow).SetAlignment(taCenter);
          end else begin
            AddCellFormulaNumber(IntToStr(L)+'*'+IntToStr(I), 0);
          end;
        end;
      end;

      //SET PRINT RANGE [2]
      PageSettings.PrintRange.RightCol := Rows[Rows.Count-1].Cells.Count-1;
      PageSettings.PrintRange.BottomRow := Rows.Count-1;

      //SET AUTOFILTER
      WindowSettings.AutoFilter.LeftCol := 0;
      WindowSettings.AutoFilter.TopRow := xAutoFilterTopRow;
      WindowSettings.AutoFilter.RightCol := Rows[Rows.Count-1].Cells.Count-1;
      WindowSettings.AutoFilter.BottomRow := Rows.Count-1;
    end;
  end;

  procedure AddWorkSheet_RowHeightCalculation;
  const
    Text =
      'Lorem ipsum dolor sit amet, consectetur adipiscing elit. '+
      'Quisque id odio turpis, vulputate faucibus '+
      'dolor. Suspendisse rutrum pretium dolor ut aliquet. '+
      'Vivamus ultrices, tortor vel venenatis scelerisque, nunc '+
      'sem condimentum arcu, ut viverra odio tellus ut odio. Sed a '+
      'fringilla nunc. Curabitur eleifend neque eget nisl '+
      'gravida gravida. Sed porta dapibus turpis, sit amet eleifend '+
      'orci dictum sed. Nulla facilisi. Morbi '+
      'condimentum massa commodo elit sollicitudin cursus.';
  begin
    with xExport.AddWorkSheet('RowHeight Calculation') do begin
      Cols[0].Width := 150;
      Cols[1].Width := 300;

      AddRow.AddCellString(Title).SetFontSize(20);
      AddRow;

      with AddRow do begin
        AddCellString('Text without word wrap').SetFontStyle([fsBold]);
      end;
      with AddRow do begin
        AddCellString('Font.Size = 12');
        AddCellString(Text).SetFontSize(12);
      end;
      with AddRow do begin
        AddCellString('Font.Size = 15');
        AddCellString(Text).SetFontSize(15);
      end;
      with AddRow do begin
        AddCellString('Font.Size = 25');
        AddCellString(Text).SetFontSize(25);
      end;

      AddRow;
      with AddRow do begin
        AddCellString('Text with word wrap').SetFontStyle([fsBold]);
      end;
      with AddRow do begin
        AddCellString('Single line');
        AddCellString(Text).SetWrapText.SetCalculateRowHeight(erhSingleLine);
      end;
      with AddRow do begin
        AddCellString('Multi line');
        AddCellString(Text).SetWrapText.SetCalculateRowHeight(erhMultiLine);
      end;
      with AddRow do begin
        AddCellString('Multi line over ColSpan');
        AddCellString(Text).SetWrapText.SetColSpan(3).SetCalculateRowHeight(erhMultiLine);
      end;
      with AddRow do begin
        AddCellString('Auto row height');
        AddCellString(Text).SetWrapText.SetCalculateRowHeight(erhForceAuto);
      end;
    end;
  end;

  procedure AddWorkSheet_NewCellProperties;
  var
    I, L: Integer;
  begin
    with xExport.AddWorkSheet('NewCell* properties') do begin
      AddRow.AddCellString(Title).SetFontSize(20);

      PageSettings.PageOrientation := epoLandscape;

      AddRow;
      AddRow.AddCellString('This sheet shows you how to use the NewCell* '+
        'properties of OExport to generate tables more easily.');
      AddRow.AddCellString('Try to print this page!');
      AddRow;

      NewCellFont.SetSize(11).SetColor(clMaroon).SetName('Times New Roman').
        SetStyle([fsBold]);
      NewCellBGColor := clYellow;
      NewCellBorders.SetBorder(cbTop, ebDouble, clGreen);
      NewCellBorders.SetBorder(cbBottom, ebThick, clBlue);
      NewCellStringAlignment := cahCenter;

      with AddRow do
      for I := 0 to 9 do begin
        with AddCellString(Char(Ord('A')+I)) do
        if I = 0 then
          SetBorder(cbLeft, ebDouble, clGreen)
        else if I = 9 then
          SetBorder(cbRight, ebDouble, clGreen);
      end;

      NewCellStringAlignment := cahRight;
      NewCellBGColor := clNone;
      NewCellFont.SetStyle([]).SetColor(clBlack);
      NewCellBorders.SetBorders([]);

      for L := 0 to 19 do
      with AddRow do begin
        if L = 19 then
          NewCellBorders.SetBorder(cbBottom, ebDouble, clGreen);

        for I := 0 to 9 do begin
          with AddCellString(ExcelRange(I, Rows.Count-1)) do
          if I = 0 then
            SetBorder(cbLeft, ebDouble, clGreen)
          else if I = 9 then
            SetBorder(cbRight, ebDouble, clGreen);
        end;
      end;
    end;
  end;

  procedure AddWorkSheet_BarChart;
  var I: Integer;
  begin
    with xExport.AddWorkSheet('Bar chart') do begin
      AddRow.AddCellString('Sales in €').SetFontSize(20);

      with AddRow do begin
        AddCellString('Products');
        AddCellString('January');
        AddCellString('February');
        AddCellString('March');

        AddCellString('');
        AddCellString('');
        with AddCellString('').AddChart(TExportChartBars, 0, 0, 500, 300) do
        with TExportChartBars(Drawing) do begin
          Title := 'Sales in €';
          Fill.Color := clWhite;
          Border.Color := clBlue;
          Shadow.FillStyle := edfColor;
          Grouping := ecClustered;//TRY DIFFERENT SETTINGS
          Orientation := ecoVertical;//TRY DIFFERENT SETTINGS
          Form := ecfBox3D;//TRY DIFFERENT SETTINGS
          Legend := eclRight;//TRY DIFFERENT SETTINGS
          YAxis.TickSkip := 10;//TRY DIFFERENT SETTINGS
          XTicsRange.SetRange(0, Rows.Count, 1, 4);//x-axis description
          for I := 1 to 3 do
          with AddData(I, Rows.Count, 1, 4) do begin//y-axis data
            DataTitle.SetRange(I, Rows.Count-1, 1, 1);//legend entry title
            Border.FillStyle := edfNone;
            ShowLabels := False;
          end;
        end;
      end;
      with AddRow do begin
        AddCellString('Bubble gums');
        AddCellNumber(15);
        AddCellNumber(12);
        AddCellNumber(8);
      end;
      with AddRow do begin
        AddCellString('Doughnuts');
        AddCellNumber(30);
        AddCellNumber(40);
        AddCellNumber(25);
      end;
      with AddRow do begin
        AddCellString('Soups');
        AddCellNumber(10);
        AddCellNumber(18);
        AddCellNumber(12);
      end;
      with AddRow do begin
        AddCellString('Drinks');
        AddCellNumber(50);
        AddCellNumber(47);
        AddCellNumber(61);
      end;
    end;
  end;

  procedure AddWorkSheet_LineChart;
  var I: Integer;
  begin
    with xExport.AddWorkSheet('Line chart') do begin
      AddRow.AddCellString('Sales in €').SetFontSize(20);

      with AddRow do begin
        AddCellString('Products');
        AddCellString('Bubble gums');
        AddCellString('Doughnuts');
        AddCellString('Soups');
        AddCellString('Drinks');

        AddCellString('');
        AddCellString('');
        with AddCellString('').AddChart(TExportChartLines, 0, 0, 500, 300) do
        with TExportChartLines(Drawing) do begin
          Title := 'Sales in €';
          Fill.Color := clWhite;
          Border.Color := clBlue;
          Shadow.FillStyle := edfColor;
          Form := ecf2D;//TRY DIFFERENT SETTINGS
          Legend := eclLeft;//TRY DIFFERENT SETTINGS
          YAxis.TickSkip := 10;//TRY DIFFERENT SETTINGS
          XTicsRange.SetRange(0, Rows.Count, 1, 3);//x-axis description
          for I := 1 to 4 do
          with AddData(I, Rows.Count, 1, 3) do begin//y-axis data
            DataTitle.SetRange(I, Rows.Count-1, 1, 1);//legend entry title
            ShowLabels := False;
            Border.Style := TDrawingBorderStyle((I-1) mod
              (Ord(High(TDrawingBorderStyle))+1));
            Border.Size := 5;
            Markers.Size := 10;
          end;
        end;
      end;
      with AddRow do begin
        AddCellString('January');
        AddCellNumber(15);
        AddCellNumber(30);
        AddCellNumber(10);
        AddCellNumber(50);

      end;
      with AddRow do begin
        AddCellString('February');
        AddCellNumber(12);
        AddCellNumber(40);
        AddCellNumber(18);
        AddCellNumber(47);
      end;
      with AddRow do begin
        AddCellString('March');
        AddCellNumber(8);
        AddCellNumber(25);
        AddCellNumber(12);
        AddCellNumber(61);
      end;
    end;
  end;

  procedure AddWorkSheet_AreaChart;
  var I: Integer;
  begin
    with xExport.AddWorkSheet('Area chart') do begin
      AddRow.AddCellString('Sales in €').SetFontSize(20);

      with AddRow do begin
        AddCellString('Products');
        AddCellString('Bubble gums');
        AddCellString('Doughnuts');
        AddCellString('Soups');
        AddCellString('Drinks');

        AddCellString('');
        AddCellString('');
        with AddCellString('').AddChart(TExportChartAreas, 0, 0, 500, 300) do
        with TExportChartAreas(Drawing) do begin
          Title := 'Sales in €';
          Fill.Color := clWhite;
          Border.Color := clBlue;
          Shadow.FillStyle := edfColor;
          Grouping := ecPercentStacked;
          Form := ecf3D;//TRY DIFFERENT SETTINGS
          Legend := eclLeft;//TRY DIFFERENT SETTINGS
          XTicsRange.SetRange(0, Rows.Count, 1, 3);//x-axis description
          for I := 1 to 4 do
          with AddData(I, Rows.Count, 1, 3) do begin//y-axis data
            DataTitle.SetRange(I, Rows.Count-1, 1, 1);//legend entry title
            ShowLabels := False;
            Border.FillStyle := edfNone;
          end;
        end;
      end;
      with AddRow do begin
        AddCellString('January');
        AddCellNumber(15);
        AddCellNumber(30);
        AddCellNumber(10);
        AddCellNumber(50);

      end;
      with AddRow do begin
        AddCellString('February');
        AddCellNumber(12);
        AddCellNumber(40);
        AddCellNumber(18);
        AddCellNumber(47);
      end;
      with AddRow do begin
        AddCellString('March');
        AddCellNumber(8);
        AddCellNumber(25);
        AddCellNumber(12);
        AddCellNumber(61);
      end;
    end;
  end;

  procedure AddWorkSheet_XYChart;
  var
    I, L: Integer;
    xTime: Double;
  begin
    with xExport.AddWorkSheet('XY chart') do begin
      AddRow.AddCellString('Velocity in km/h').SetFontSize(20);

      with AddRow do begin
        AddCellString('Time');
        AddCellString('Car 1');
        AddCellString('Car 2');
        AddCellString('Car 3');

        AddCellString('');
        AddCellString('');
        with AddCellString('').AddChart(TExportChartXY, 0, 0, 500, 300) do
        with TExportChartXY(Drawing) do begin
          Title := 'Velocity in km/h';
          Fill.Color := clWhite;
          Border.Color := clBlue;
          Shadow.FillStyle := edfColor;
          LineType := eclSmooth;//TRY DIFFERENT SETTINGS
          Legend := eclRight;//TRY DIFFERENT SETTINGS
          XAxis.Caption := 'Time [h]';
          YAxis.Caption := 'Velocity [km/h]';
          for I := 1 to 3 do
          with AddData(I, Rows.Count, 1, 10) do begin//y-axis data
            XRange.SetRange(0, Rows.Count, 1, 10);//x-axis data
            DataTitle.SetRange(I, Rows.Count-1, 1, 1);//legend entry title
            ShowLabels := False;
            Border.Size := 2;
            Markers.Size := 8;
          end;
        end;
      end;

      Randomize;
      xTime := 0;
      with AddRow do//zero values
      for L := 0 to 3 do
        AddCellNumber(0, 2);

      for I := 1 to 10 do
      with AddRow do begin
        xTime := xTime + RandomRange(10, 40)/10;
        AddCellNumber(xTime, 2);
        for L := 1 to 3 do
          AddCellNumber(Random(12000) / 100, 2);
      end;
    end;
  end;

  procedure AddWorkSheet_BubbleChart;
  begin
    with xExport.AddWorkSheet('Bubble chart') do begin
      AddRow.AddCellString('Market share study').SetFontSize(20);

      with AddRow do begin
        AddCellString('Number of products');
        AddCellString('Sales [€]');
        AddCellString('Market share [%]');

        AddCellString('');
        AddCellString('');
        with AddCellString('').AddChart(TExportChartBubbles, 0, 0, 500, 300) do
        with TExportChartBubbles(Drawing) do begin
          Title := 'Market share study';
          Fill.Color := clWhite;
          Border.Color := clBlue;
          Shadow.FillStyle := edfColor;
          Form := ecf3D;
          Legend := eclNone;//TRY DIFFERENT SETTINGS
          XAxis.Caption := 'Number of products';
          YAxis.Caption := 'Sales [€]';
          with AddData(1, Rows.Count, 1, 4) do begin//y-axis data
            XRange.SetRange(0, Rows.Count, 1, 4);//x-axis data
            ExtraRange1.SetRange(2, Rows.Count, 1, 4);//bubble size
            Border.FillStyle := edfNone;
            ShowLabels := False;
          end;
        end;
      end;

      with AddRow do begin
        AddCellNumber(7, 0);
        AddCellNumber(12000, 0);
        AddCellPercent(0.02, 0);
      end;
      with AddRow do begin
        AddCellNumber(14, 0);
        AddCellNumber(19000, 0);
        AddCellPercent(0.10, 0);
      end;
      with AddRow do begin
        AddCellNumber(25, 0);
        AddCellNumber(21000, 0);
        AddCellPercent(0.40, 0);
      end;
      with AddRow do begin
        AddCellNumber(31, 0);
        AddCellNumber(17000, 0);
        AddCellPercent(0.77, 0);
      end;
    end;
  end;

  procedure AddWorkSheet_PieChart;
  begin
    with xExport.AddWorkSheet('Pie chart') do begin
      AddRow.AddCellString('Pie Chart').SetFontSize(20);

      with AddRow do begin
        AddCellString('Products');
        AddCellString('Sales [€]');

        AddCellString('');
        AddCellString('');
        with AddCellString('').AddChart(TExportChartPie, 0, 0, 500, 300) do
        with TExportChartPie(Drawing) do begin
          Title := 'Sales in January in €';
          Fill.Color := clWhite;
          Border.Color := clBlue;
          Shadow.FillStyle := edfColor;
          Form := ecf3D;
          Legend := eclRight;

          XTicsRange.SetRange(0, Rows.Count, 1, 4);
          with AddData(1, Rows.Count, 1, 4) do begin
            Border.FillStyle := edfNone;
            ShowLabels := True;
          end;
        end;
      end;
      with AddRow do begin
        AddCellString('Bubble gums');
        AddCellNumber(15);
      end;
      with AddRow do begin
        AddCellString('Doughnuts');
        AddCellNumber(30);
      end;
      with AddRow do begin
        AddCellString('Soups');
        AddCellNumber(10);
      end;
      with AddRow do begin
        AddCellString('Drinks');
        AddCellNumber(50);
      end;
    end;
  end;

  procedure AddWorkSheet_NamedCells;
  var
    I, J: Integer;
  begin
    with xExport.AddWorkSheet('Named cells') do begin
      AddRow.AddCellString(Title).SetFontSize(20);
      AddRow;

      AddRow.AddCellString('Named cells').SetFontStyle([fsBold]);
      with AddRow do begin
        AddCellString('first');
        AddCellString('second');
        AddCellString('=first*second');
      end;
      with AddRow do begin
        //ADD CELLS WITH NAMES
        AddCellNumber(15, 0).SetName('first');
        AddCellNumber(5, 0).SetName('second');
        AddCellFormulaNumber('=first*second', 0);
      end;
      AddRow;
      AddRow.AddCellString('Sum of matrix elements').SetFontStyle([fsBold]);
      //ADD CELL RANGE WITH NAME
      xExport.NamedCells.AddCellRangeSpan('matrix', xExport.ActiveWorkSheet, 0, Rows.Count, 3, 3);
      for I := 0 to 2 do
      with AddRow do begin
        for J := 0 to 2 do
          AddCellNumber(I+J, 0);
      end;
      with AddRow do begin
        AddCellString('Result:').SetFontStyle([fsBold]);
        AddCellEmpty;
        AddCellFormulaNumber('SUM(matrix)', 0).SetFontStyle([fsBold]);
      end;
    end;
  end;

  procedure AddWorkSheet_RTF;
  var
    xCell: TExportCellString;
    xRE: TRichEdit;
  begin
    with xExport.AddWorkSheet('Rich text') do begin
      AddRow.AddCellString(Title).SetFontSize(20);
      AddRow;

      AddRow.AddCellString('The following text was loaded from an external RTF file.'+sLineBreak+
        'You find the file in "doc\text.rtf".').SetCalculateRowHeight(erhMultiLine);

      AddRow;
      AddRow;

      xRE := TRichEdit.Create(nil);
      try
        xRE.Visible := False;
        xRE.Parent := Self;
        xRE.Lines.LoadFromFile(docDir+'text.rtf');

        xCell := AddRow.AddCellString;

        {$IF (CompilerVersion >= 18.0)}
        //DELPHI 2006 and newer
        xCell.LoadRichTextFromEditor(xRE);
        {$ELSE}
        //DELPHI 7
        RichEditToStringCell(xRE, xCell);
        {$IFEND}
        xCell.SetWrapText.SetWidth(400).SetHeight(150);
      finally
        xRE.Free;
      end;
    end;
  end;

  procedure AddWorkSheet_Protection;
  begin
    with xExport.AddWorkSheet('Protection') do begin
      Protection.Enabled := True;
      Protection.Password := 'Kluug.net';
      Protection.AllowOptions := [epoSelectUnlockedCells];
      AddRow.AddCellString(Title).SetFontSize(20).SetWidth(200);
      AddRow;

      AddRow.AddCellString('This sheet is protected with a password!');
      AddRow.AddCellString('Password: "Kluug.net"');
      AddRow.AddCellString('You are allowed to select not-protected cells but you are not allowed to select protected cells.');
      AddRow.AddCellString('(cells are protected by default)');

      AddRow;

      AddRow.AddCellString('Not protected cell').Style.SetProtection([]);
      AddRow.AddCellString('Protected cell').Style.SetProtection([ecpLocked]);
      AddRow;
      with AddRow do begin
        AddCellString('Hidden formula (2+5*4 = 22):');
        AddCellFormulaNumber('2+5*4', 0).Style.SetProtection([ecpFormulaHidden]);
      end;
    end;
  end;

  procedure AddWorkSheet_Image;
  var xLogoIndex: Integer;
  begin
    with xExport.AddWorkSheet('Image') do begin
      AddRow.AddCellString(Title).SetFontSize(20);
      AddRow;
      AddRow.AddCellString('The image below is saved only once in the document!');
      AddRow;
      AddRow.AddCellString('100%');
      with AddRow do begin
        with AddCellString do begin
          xLogoIndex := AddImage(docDir+'kluug-logo-128.png', 0, 0, 128, 45).DrawingIndex;
        end;
      end;
      AddRow;
      AddRow;
      AddRow;
      AddRow.AddCellString('200%');
      with AddRow do begin
        with AddCellString do begin
          ReuseImage(xLogoIndex, 0, 0, 256, 90);
        end;
      end;
    end;
  end;

begin
  xExport := TOExport.Create;
  try

    AddWorkSheet_Information;

    AddWorkSheet_CellTypes;
    AddWorkSheet_CellFormatting;
    AddWorkSheet_ConditionalFormatting;
    AddWorkSheet_SheetColRowStyle;
    AddWorkSheet_ColRowSpan;
    AddWorkSheet_Grouping;
    AddWorkSheet_PrintOptions;
    AddWorkSheet_TableHeader;
    AddWorkSheet_RowHeightCalculation;
    AddWorkSheet_NewCellProperties;
    AddWorkSheet_NamedCells;
    AddWorkSheet_RTF;
    AddWorkSheet_Protection;

    AddWorkSheet_Image;

    AddWorkSheet_BarChart;
    AddWorkSheet_LineChart;
    AddWorkSheet_AreaChart;
    AddWorkSheet_XYChart;
    AddWorkSheet_BubbleChart;
    AddWorkSheet_PieChart;

    xExport.SaveToFileWithDialog('complex', '', True);
  finally
    xExport.Free;
  end;
end;
Categories