0
I am trying to read a spreadsheet in excel and I am creating a dynamic table via programming and it is working, but I’m having trouble to get this data filtered, because it does not return all the items that were filtered with the amount greater than 0 (Zero).
P.S.: Delphi XE-2
Example of excel 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;
Could provide the xls, to simulate your exact problem here ?
– Victor Tadashi
"You’re giving trouble". What problem? Your question is unclear (so much so that colleague @Victorzanella , with muuuiiiiiita good will, had to ask the xls to simulate his problem! He shouldn’t have to do that!). This site is not a forum. If you haven’t already, do the [tour] and, chiefly, read [Ask].
– Luiz Vieira
You commented in the source code that it does not return all the filtered data, so something from the dynamic table returns? On the line where the problem occurs, what are the values of the variables Lastlin and Lastcol?
– Andrey
The Lastlin and Lastcol it takes excel, the last rows and columns that have information.
LastLin := ExcelApp.ActiveCell.Row;LastCol := ExcelApp.ActiveCell.Column;What I was able to notice is that when the filter is done in excel, as for example the excel I sent, in row 9 I put a product with quantity 0. The return of the excel function that returns only the visible lines it does not return all the positions of the matrix, only up to line 8.– Jhonny Rm
@Luizvieira, Good at the source it was told that
{O problema ocorre aqui, pois não retorna todos os itens filtrados}– Jhonny Rm
LastLin=21andLastCol=3@Andrey– Jhonny Rm
Well, I admit I didn’t notice that comment within the code. I withdrew my vote to close. Anyway, it cost nothing for you to have left the statement also out of code, in the body of the question (something like: "in line XX, the YYY function does not return the filtered items"). I would just avoid needing this kind of clarification. Here’s the tip for the future. :)
– Luiz Vieira
@Luizvieira, ready, I added in the body of the question! Thks by tip!
– Jhonny Rm