Excel spreadsheet with unreadable cells when filtering

Asked

Viewed 86 times

3

I’m creating a spreadsheet of Excel using the phpExcel(). When placing filters, the result of the cell C3 and C4 where the subtotal is located SUBTOTAL() looks like this:

Unfiltered inserir a descrição da imagem aqui

Filtered inserir a descrição da imagem aqui

Does anyone know why this happens? I have tried to change the file to several encodings, including UNICODE-UTF8, ISO and WINDOWS. When saving the file in .xls works, but I need it saved in .xlsx

php excel phpexcel

1 answer

0

I’m sorry, buddy, but you weren’t very clear in presenting your problem, telling me what the problem is and what the outcome is. However, let me know if this helps you in any way:

The formula SUBTOTAL() is an alternative to traditional formulas such as SOMA(), CONT.VALUES(), AVERAGE()... when it is intended to perform operations in a filter range. As I can verify, your SYBTOTAL() formula is performing a counting operation (indicated po '3' in the first argument), so C3 will count all visible (not deleted in filters) items from the selected range. I further deduce that the above cell (C2), which did not change its value in both figures, must have as a formula =CONT.VALORES(B7:B878). In short, we have to:

  • Formula SUBTOTAL(3;intervalo) > will count the values of intervalo visible. Values hidden by filters are excluded.
  • Formula CONT.VALORES(intervalo) > will count the values of intervalo, independent there are lines hidden by filters.

NOTE: The SUBTOTAL() formula differs only in operations with ranges affected by filters. Manually hidden rows and columns will not have their values deleted from operations, returning identical results to other functions.

  • Hello, Julio, the problem that is occurring is that when I filter some column, the results of C3 and C4 cells are unreadable, as in figure 2. The strange thing is that when I resize the colula, the values (already with filters) are readable. This is only happening when I use Phpexcel_iofactory::createWriter($excelObject,'Excel2007'); if I go back to Phpexcel_iofactory::createWriter($excelObject,'Excel5'); it works normally, but Excel5 doesn’t have the features I’m looking for. Understands?

Browser other questions tagged php excel phpexcel

You are not signed in. Login or sign up in order to post.