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
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
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
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
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
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