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;