Filter / Autofilter

GemBox.Spreadsheet
1

Hello,
anybody knows how to add AutoFilter to a set of columns of a worksheet?
I’m using ComHelper in Classic ASP.

Thanks a lot
Cristian

2

Hi Cristian,

Try this:

Set workbook = CreateObject("GemBox.Spreadsheet.ExcelFile")
Set worksheet = workbook.Worksheets.Add("Sheet1")

Set row1 = comHelper.GetRow(worksheet, 0)
row1.Cells.Item(0).Value = "Name"
row1.Cells.Item(1).Value = "Value"

Set row2 = comHelper.GetRow(worksheet, 1)
row2.Cells.Item(0).Value = "First Item"
row2.Cells.Item(1).Value = 300

Set row3 = comHelper.GetRow(worksheet, 2)
row3.Cells.Item(0).Value = "Second Item"
row3.Cells.Item(1).Value = 200

Set row3 = comHelper.GetRow(worksheet, 3)
row3.Cells.Item(0).Value = "Third Item"
row3.Cells.Item(1).Value = 100

Set range = worksheet.Cells.GetSubrangeAbsolute(0, 0, 3, 1)
range.Filter()

workbook.Save(Server.MapPath(".") & "\Output.xlsx")

Or this:

range.Filter().SortBy(1).Apply()

Does this work for you?

Regards,
Mario

3

Thank you very much Mario.
Solution 1 worked well.

I’ve also additional problem to customize color of a cell (text and background).
Available examples do not work in ASP Classic.
This construct don’t work

worksheet.Cells(row, 1).Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue)

returning unknown object for SpreadsheetColor and ColorName

Do you have any suggestion?
Thanks a lot
Cristian

4

Hi Cristian,

You could try using the System.Drawing.Color, GemBox.Spreadsheet will implicitly convert it to SpreadsheetColor.

However, instead of that, I would suggest you remove the direct usage of GemBox.Spreadsheet from Classic ASP and instead create a wrapper DLL that will have it embedded within and will expose the exact members that you want and how you want.

You see, the problem is that due to COM limitations I suspect that quite a few style-related APIs could be unavailable.
Add the missing calls to the ComHelper class could complicate things too much, so perhaps the easiest solution would be to create a wrapper DLL that can utilize the full capability of GemBox.Spreadsheet and provide just the public methods that you want to use from the Classic ASP.

Would this work for you?
Let me know if you need help with this approach.

Regards,
Mario

5

Hello Mario,
the solution to prepare a DLL that use GemBox.Spreadsheet.dll directly is not a solution for me because of the server limitation and company policies. So I need to work using COM.

About coloring I’ve tried this:

Set SpreadsheetColor=System.Drawing.Color
Row.Cells.Item(15).Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue)

or this

Row.Cells.Item(15).Style.Font.Color=System.Drawing.Color.FromName(ColorName.Blue)

getting in both cases

Microsoft VBScript runtime error ‘800a01a8’
Object required: ‘System’
/test.asp, line 271

Do you have further details how to use your suggestion?

Thanks a lot
Cristian