TMSFNCDataGridExcelIO mit FNCDataGrid listet alle Datensätze

Kipper_Gerald · February 12, 2025, 1:08pm

I have a TMSFNCDataGridExcelIO and a FNCDataGrid that is connected to my database via an adapter and a query qCustomers that is connected to both.
I have filtered data records in the DataGrid.
When I output the grid via a TMSFNCDataGridPDFIO component, only the selected rows appear. With TMSFNCDataGridExcelIO all rows are output.

procedure TfrmKunden.btnExcelListClick(Sender: TObject);
Var
Filename:String;
begin
FileName := ProgPath + 'ExcelList.xls';
datagridtoexcel.XLSExport(Filename);
ShellExecute(Handle, 'open', PChar(FileName), nil, nil, SW_SHOWNORMAL);
end;

procedure TfrmKunden.btnPDFListClick(Sender: TObject);
Var
Filename:String;
begin
FileName := ProgPath + 'PDFList.pdf';
datagridtoPDF.Save(Filename);
ShellExecute(Handle, 'open', PChar(FileName), nil, nil, SW_SHOWNORMAL);
end;

<

Pieter · February 12, 2025, 2:12pm

Tested here and after applying a filter, the correct rows are exported.

Can you provide a test case?

Kipper_Gerald · February 12, 2025, 2:22pm

I will try to do a testcase.
Maybe my Properties of the DataGrid has somthing spezial.
Like showAll or LoadAll.

This is the code for my Initialisation of the DataGrid.

procedure InitDataGrid(grd: TTMSFNCDataGrid; lAdapter: TTMSFNCDataGridDatabaseAdapter);
var
i: Integer;
begin
with grd do
begin
BeginUpdate;
Color := AppColors.FormBackground; // Grid-Hintergrundfarbe

Options.Localization.FilterTypeStartsWithText := 'Startet mit';
Options.Localization.FilterTypeEndsWithText := 'Endet mit';
Options.Localization.FilterTypeContainsText := 'Enthält';
Options.Localization.FilterTypeNotContainsText := 'Enthält nicht';
Options.Localization.FilterTypeEqualText := 'gleich';
Options.Localization.FilterTypeNotEqualText := 'nicht gleich';
Options.Localization.FilterTypeSmallerThanText := 'kleiner als';
Options.Localization.FilterTypeLargerThanText := 'größer als';
Options.Localization.FilterTypeLargerOrEqualThanText := 'größer oder gleich';
Options.Localization.FilterTypeSmallerOrEqualThanText := 'kleiner oder gleich';
Options.Localization.FilterTypeEmptyText := 'leer';
Options.Localization.FilterTypeNotEmptyText := 'nicht leer';

grd.ColumnCount := ColMaxK + 1;
for i := 0 to ColMaxK do
begin
  grd.Columns[i].AddSetting(gcsFilterDefaultType);
  grd.Columns[i].FilterDefaultType := gftStartsWith;
end;

GlobalFont.Name := FontNameGlobal;
Options.Filtering.Enabled := True;
Options.Sorting.Enabled := True;
Options.Selection.Mode := gsmSingleRow;
DefaultColumnWidth := 100;
Options.Column.Stretching.Enabled := False;
Options.Column.Stretching.Index := -1;

CellAppearance.FocusedLayout.Fill.Color := AppColors.GridSelected;
CellAppearance.SelectedLayout.Fill.Color := AppColors.GridSelected;
CellAppearance.FixedLayout.Fill.Color := AppColors.GridHeader;

CellAppearance.FixedLayout.Stroke.Color := clBlack;
CellAppearance.FixedSelectedLayout.Stroke.Color := clBlack;
CellAppearance.NormalLayout.Stroke.Color := clBlack;
CellAppearance.SelectedLayout.Stroke.Color := clBlack;
CellAppearance.FocusedLayout.Stroke.Color := clBlack;

EndUpdate;

end;
end;

Pieter · February 12, 2025, 2:36pm

Is the database adapter configured to add all records? (LoadMode)

Kipper_Gerald · February 12, 2025, 3:19pm

LoadMode=almAllRecords

Pieter · February 13, 2025, 7:29am

With LoadMode = almAllRecords, the filtering happens directly on the grid, meaning, the rows are still there, but they are hidden. With filtering on dataset level, only the matching rows are displayed since the data comes from the dataset filtered state.

Either way, even with LoadMode = almAllRecords, I still can't reproduce the issue here.

Kipper_Gerald · February 13, 2025, 8:31am

I try a testprojekt with my data and it works for pdf and Excel.
nly in my Working Projekt is the problem.

Here are some Konfigurations from my .dfm File:

object GridDatabaseAdapter: TTMSFNCDataGridDatabaseAdapter
Left = 1137
Top = 11
Width = 26
Height = 26
Visible = True
DataSource = dsKunden
Columns = <>
AutoCreateColumns = False
ShowPictureFields = False
ShowMemoFields = True
LoadMode = almAllRecords
end

object DataGridToExcel: TTMSFNCDataGridExcelIO
  Left = 428
  Top = 271
  Width = 26
  Height = 26
  Visible = True
  DataGrid = GridKunden
  Options.ExportOverwriteMessage = 'File %s already exists'#13'Ok to overwrite ?'
  UseUnicode = False
  Version = '3.24'
end


object DataGridToPDF: TTMSFNCDataGridPDFIO
  Left = 428
  Top = 348
  Width = 26
  Height = 26
  Visible = True
  DataGrid = GridKunden
  Options.DefaultFont.Name = 'Arial'
  Options.Header = 'Liste'
  Options.Margins.Left = 20.000000000000000000
  Options.Margins.Top = 50.000000000000000000
  Options.Margins.Right = 20.000000000000000000
  Options.Margins.Bottom = 50.000000000000000000
  Options.HeaderFont.Name = 'Arial'
  Options.FooterFont.Name = 'Arial'
  Options.HeaderMargins.Left = 20.000000000000000000
  Options.HeaderMargins.Top = 5.000000000000000000
  Options.HeaderMargins.Right = 20.000000000000000000
  Options.HeaderMargins.Bottom = 5.000000000000000000
  Options.FooterMargins.Left = 20.000000000000000000
  Options.FooterMargins.Top = 5.000000000000000000
  Options.FooterMargins.Right = 20.000000000000000000
  Options.FooterMargins.Bottom = 5.000000000000000000
  Options.PageNumberMargins.Left = 20.000000000000000000
  Options.PageNumberMargins.Top = 5.000000000000000000
  Options.PageNumberMargins.Right = 20.000000000000000000
  Options.PageNumberMargins.Bottom = 5.000000000000000000
  Options.PageNumberFormat = '%d'
  Options.PageNumberFont.Name = 'Arial'
end

end

object GridKunden: TTMSFNCDataGrid
AlignWithMargins = True
Left = 5
Top = 508
Width = 1665
Height = 400
Margins.Left = 5
Margins.Top = 5
Margins.Right = 5
Margins.Bottom = 5
ParentDoubleBuffered = False
Color = 10066253
DoubleBuffered = True
TabOrder = 7
OnBeforeDraw = GridKundenBeforeDraw
Adapter = GridDatabaseAdapter
CellAppearance.BandLayout.Font.Charset = DEFAULT_CHARSET
CellAppearance.BandLayout.Font.Color = clBlack
CellAppearance.BandLayout.Font.Height = -11
CellAppearance.BandLayout.Font.Name = 'Segoe UI'
CellAppearance.BandLayout.Font.Style =
CellAppearance.BandLayout.Stroke.Color = 15987699
CellAppearance.FixedLayout.Fill.ColorTo = 16382457
CellAppearance.FixedLayout.Font.Charset = DEFAULT_CHARSET
CellAppearance.FixedLayout.Font.Color = clBlack
CellAppearance.FixedLayout.Font.Height = -11
CellAppearance.FixedLayout.Font.Name = 'Segoe UI'
CellAppearance.FixedLayout.Font.Style =
CellAppearance.FixedLayout.Stroke.Color = clGray
CellAppearance.FixedSelectedLayout.Fill.ColorTo = 15385233
CellAppearance.FixedSelectedLayout.Font.Charset = DEFAULT_CHARSET
CellAppearance.FixedSelectedLayout.Font.Color = clBlack
CellAppearance.FixedSelectedLayout.Font.Height = -11
CellAppearance.FixedSelectedLayout.Font.Name = 'Segoe UI'
CellAppearance.FixedSelectedLayout.Font.Style =
CellAppearance.FixedSelectedLayout.Stroke.Color = 15987699
CellAppearance.FocusedLayout.Fill.ColorTo = 15385233
CellAppearance.FocusedLayout.Font.Charset = DEFAULT_CHARSET
CellAppearance.FocusedLayout.Font.Color = clBlack
CellAppearance.FocusedLayout.Font.Height = -11
CellAppearance.FocusedLayout.Font.Name = 'Segoe UI'
CellAppearance.FocusedLayout.Font.Style =
CellAppearance.FocusedLayout.Stroke.Color = 15987699
CellAppearance.GroupLayout.Fill.ColorTo = 15650471
CellAppearance.GroupLayout.Font.Charset = DEFAULT_CHARSET
CellAppearance.GroupLayout.Font.Color = clBlack
CellAppearance.GroupLayout.Font.Height = -11
CellAppearance.GroupLayout.Font.Name = 'Segoe UI'
CellAppearance.GroupLayout.Font.Style =
CellAppearance.GroupLayout.Stroke.Color = 15987699
CellAppearance.NormalLayout.Font.Charset = DEFAULT_CHARSET
CellAppearance.NormalLayout.Font.Color = clBlack
CellAppearance.NormalLayout.Font.Height = -11
CellAppearance.NormalLayout.Font.Name = 'Segoe UI'
CellAppearance.NormalLayout.Font.Style =
CellAppearance.NormalLayout.Stroke.Color = clGray
CellAppearance.SelectedLayout.Fill.ColorTo = 15385233
CellAppearance.SelectedLayout.Font.Charset = DEFAULT_CHARSET
CellAppearance.SelectedLayout.Font.Color = clBlack
CellAppearance.SelectedLayout.Font.Height = -11
CellAppearance.SelectedLayout.Font.Name = 'Segoe UI'
CellAppearance.SelectedLayout.Font.Style =
CellAppearance.SelectedLayout.Stroke.Color = 15987699
CellAppearance.SummaryLayout.Fill.ColorTo = 15915709
CellAppearance.SummaryLayout.Font.Charset = DEFAULT_CHARSET
CellAppearance.SummaryLayout.Font.Color = clBlack
CellAppearance.SummaryLayout.Font.Height = -11
CellAppearance.SummaryLayout.Font.Name = 'Segoe UI'
CellAppearance.SummaryLayout.Font.Style =
CellAppearance.SummaryLayout.Stroke.Color = 15987699

ColumnCount = 0
Columns = <>
DefaultColumnWidth = 70.000000000000000000
Designer = False
FilterAppearance.Font.Charset = DEFAULT_CHARSET
FilterAppearance.Font.Color = clBlack
FilterAppearance.Font.Height = -11
FilterAppearance.Font.Name = 'Segoe UI'
FilterAppearance.Font.Style = []
OnSelectCell = GridKundenSelectCell
Options.Column.Stretching.Enabled = True
Options.Column.FixedAutoCheckAll = False
Options.Editing.Enabled = False
Options.Mouse.AutoScrollingInterval = 10
Options.Mouse.AutoScrollingSpeedMode = gasmCell
Options.Mouse.FixedCellSelection = [gfcsRow]
Options.Mouse.FixedColumnSizing = True
Options.Mouse.WheelScrollSize = 1
Options.Selection.Mode = gsmSingleRow
RowCount = 1
ScrollMode = gsmCellScrolling
Stroke.Color = clDarkgray

end

Maybe you can see somthing that can cause the Problem.

Kipper_Gerald · February 13, 2025, 9:05am

I found somthing.
Every time i make a new ExleList there is a new Sheet in the Excel-Document not a new ExcelFile.
So i have to delete the old File before saving the new one.

Is there a option to make always a new Excel-File?

Pieter · February 13, 2025, 9:41am

ExportOverwrite needs to be set to TOverwriteMode.omAlways;

Kipper_Gerald · February 13, 2025, 10:05am

Yes this works.
But the next Problem is that no Header is shown in the ExcelFile.
The First Column is deleted in the Excelfile.

Pieter · February 13, 2025, 12:11pm

  TMSFNCDataGridExcelIO1.DataGridStartCol := 0;
  TMSFNCDataGridExcelIO1.DataGridStartRow := 0;

Kipper_Gerald · February 13, 2025, 12:40pm

It works.
Thank you great Help