Get data from a PivotTable in Excel

0

I'm trying to read from an excel sheet and I'm creating a PivotTable programmatically and it's working fine, however I'm having trouble picking up this filtered data because it does not return all of the items that have been filtered with more than 0 (zero).

P.S.: Delphi XE-2

Excel example I'm using.

uses ComObj;

var
  ExcelApp, ExcelSheetApp: OleVariant;
  Dados: Variant;
  LastLin, LastCol, ColQuantidade: Integer;      
  opgFile: TOpenDialog;
const
  xlCellTypeLastCell= $0000000B;
  xlCellTypeVisible = $0000000C;
  Excel = 'Excel.Application';
begin
  if MessageDlg('Deseja importar os produtos de uma planilha?', mtConfirmation, mbYesNo, 0, mbNo) = mrYes then      
    begin
      try
        opgFile := TOpenDialog.Create(Self);
        opgFile.Filter := 'Excel|*.xls*';
        if opgFile.Execute then
        begin
          ColQuantidade:=3;
          {Obteve as informações do excel}
          ExcelApp := CreateOleObject(Excel);
          ExcelApp.WorkBooks.Open(opgFile.FileName);
          ExcelApp.visible := True; {Somente para teste}
          ExcelSheetApp := ExcelApp.WorkBooks[1].WorkSheets[1];
          {Obtem a ultima linha}
          ExcelSheetApp.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate;
          LastLin := ExcelApp.ActiveCell.Row;
          LastCol := ExcelApp.ActiveCell.Column;
          {Filtra pela coluna Quantidade}
          ExcelSheetApp.Range['A1', ExcelApp.Cells.Item[LastLin, LastCol]].AutoFilter(Field := ColQuantidade, Criteria1 := '<>',
            Criteria2 := '<>0');
          {Obtem somente as linhas visibeis}
          ExcelSheetApp.Range['A1', ExcelApp.Cells.Item[LastLin, LastCol]].SpecialCells(xlCellTypeLastCell, EmptyParam)
            .SpecialCells(xlCellTypeVisible, EmptyParam).Activate;

          Dados := ExcelSheetApp.Range['A1', ExcelApp.Cells.Item[LastLin, LastCol]].SpecialCells(xlCellTypeLastCell, EmptyParam)
            .SpecialCells(xlCellTypeVisible, EmptyParam).value;
           {O problema ocorre aqui, pois não retorna todos os itens filtrados}
        end;
      finally
        if not VarIsEmpty(ExcelApp) then
        begin
          ExcelApp.Quit;
          ExcelApp := Unassigned;
          ExcelSheetApp := Unassigned;
        end;
        if Assigned(opgFile) then
          FreeAndNil(opgFile);
      end;
    
asked by anonymous 07.10.2016 / 21:19

0 answers