如何在Excel表格中为顶部行启用自动筛选？

Question

如何在Excel表格中为顶部行启用自动筛选？

exceldelphiexcel-2007autofilterole-automation

4

以下是我的小测试应用程序，能够生成Excel 2007 XLSX文件：

uses
  Excel2007;

function CreateExportExcelWorkbook(AApp: ExcelApplication; ALCID: Integer): ExcelWorkbook;
var
  OldDefaultSaveFormat: XlFileFormat;
begin
  OldDefaultSaveFormat := AApp.DefaultSaveFormat;
  AApp.DefaultSaveFormat := xlOpenXMLWorkbook;
  try
    Result := AApp.Workbooks.Add(xlWBATWorksheet, ALCID);
  finally
    AApp.DefaultSaveFormat := OldDefaultSaveFormat;
  end;
end;

procedure FixTopRows(AApp: ExcelApplication; ARowCount: Integer);
var
  ActiveWindow: Window;
begin
  ActiveWindow := AApp.ActiveWindow;
  ActiveWindow.SplitColumn := 0;
  ActiveWindow.SplitRow := ARowCount;
  ActiveWindow.FreezePanes := True;
end;

procedure TForm1.Button1Click(Sender: TObject);
const
  cRowCount = 200;
  cColCount = 10;
var
  LCID: Integer;
  ExcelApp: ExcelApplication;
  Workbook: ExcelWorkbook;
  Worksheet: ExcelWorksheet;
  i, j: Integer;
  FVarArray: Variant;
  Cell1, Range: ExcelRange;
begin
  LCID := GetUserDefaultLCID;

  ExcelApp := CoExcelApplication.Create;
  try
    ExcelApp.Visible[LCID] := False;
    ExcelApp.UserControl := False;
    ExcelApp.DisplayAlerts[LCID] := False;

    Workbook := CreateExportExcelWorkbook(ExcelApp, LCID);
    Worksheet := Workbook.Worksheets.Item[1] as ExcelWorksheet;

    FVarArray := VarArrayCreate([0, cRowCount - 1, 0, cColCount - 1], varVariant);

    for j := 0 to cColCount - 1 do
      FVarArray[0, j] := Format('Column %d', [j]);


    for i := 1 to cRowCount - 1 do
      for j := 0 to cColCount - 1 do
        FVarArray[i, j] := 100 * i + j;

    Cell1 := Worksheet.Cells.Range['A1', 'A1'];
    Range := Worksheet.Range[Cell1, Cell1.Offset[cRowCount - 1, cColCount - 1]];
    Range.Value[EmptyParam] := FVarArray;
    VarClear(FVarArray);
    Range.EntireColumn.AutoFit;

    FixTopRows(ExcelApp, 1);

    Range := Worksheet.Range[Cell1, Cell1.Offset[0, cColCount - 1]];
    //Range := Cell1.EntireRow;
    //Range.AutoFilter(1, 'All', EmptyParam, EmptyParam, True);

    Workbook.SaveAs(ExpandFileName('test.xlsx'), EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
      xlNoChange, EmptyParam, False, EmptyParam, EmptyParam, EmptyParam, LCID);
  finally
    ExcelApp.Quit;
  end;
end;

单元格Excel2007是导入的Excel 2007类型库。到目前为止，它已经很好地工作了。但是我想让标题行像这个截图一样自动过滤: screenshot of AutoFilter 不幸的是，我的所有尝试都通过OLE自动化导致了OLE或变体异常。您有什么建议吗？

NB: 数据不应该被过滤 - 我只想要下拉按钮。

- Uli Gerhardt

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- David Heffernan · Accepted Answer

你需要指定运算符，但你没有这样做。例如：

Worksheet.Cells.AutoFilter(1, EmptyParam, xlAnd, EmptyParam, True);

请注意，Criteria1参数是可选的，可以省略，并默认为'All'。