I am using a Spreadsheet object in conjunction with my ASP code. I found various properties for changing an Excel spreadsheet but can't figure out how to change the cell's background.
Set objSpreadsheet = Server.CreateObject("OWC.S
objSpreadsheet.Cells(p,1).
objSpreadsheet.Cells(p,1).
objSpreadsheet.Cells(p,1).
objSpreadsheet.Cells(p,1).
I'm not sure if these properties are coming straight from Excel VBA or what. I also don't know what vb is in vbRed because when I print this to the screen doing a Response.Write in ASP, the value behind vbRed is 255 which is probably the R in RGB but I still don't get it. I can't just plug in a color # like #ffffff. For instance jSpreadsheet.Cells(p,1).fo
so my 3 questions are:
1) Is this Excel VBA when I am using the OWC.Spreadsheet object?
2) How to change the cell's background color
3) What is vbRed
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
1 ) Because Excel.Application is bad. It is resource intensive, creates one or more a running instances of Excel on our server. I am not going that route. Using COM is much more efficient.
can this be done in a one liner without using a With statment?
objSpreadsheet.Cells(p,1).
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Take a look at the following link:
http://msdn.microsoft.com/
It is Microsoft's reference for Office Web Components (the OWC in OWC.Spreadsheet).
The following code changes the first cell color to red:
<html>
<head>
<script language="VBScript">
Sub ColorRed()
document.SS1.Cells(1,1).In
End Sub
</script>
</head>
<body>
<button id="bt1" onclick="ColorRed()">Color
<object id="SS1" classid=CLSID:0002E510-000
</body>
</html>
Notice, however, that this is all client based as in the logic behind ColorRed() is contained and executed within the Browser. Although you can instantiate OWC objects from within ASP, I have not found any Save or SaveAs methods for the Spreadsheet object.
Hope this helps,
Sly
Oh yeah, the vb in vbRed is simply used as a moniker indicating that vbRed is a Visual Basic constant representing the color Red. You will notice that I used the RGB() function to create the color red. It takes three parameters: the red component, green component, and blue component.
In order to use this you will probably need to translate your #rrggbb value into the individual decimal parts.
Something like the following should work (VBScript):
Function HexToDec(sHex)
Const csHEXChars = "0123456789abcdef"
Dim DecValue, iIndex, sChar
DecValue = 0
For iIndex = 0 To (Len(sHex) - 1)
sChar = Mid(sHex, Len(sHex) - iIndex, 1)
DecValue = DecValue + ((InStr(1, csHEXChars, sChar) - 1) * (16 ^ iIndex))
Next
HexToDec = DecValue
End Function
It's really weird. I can go into Photoshop, pick a color from the color grid and then take the RGB values from it, shove it into my line here below and the color comes up almost close but sometimes much lighter or darker than what shows in photoshop. Also you are using a function where I just used the one liner. I assume it is because you are using straight VB and not ASP with VBSript is why you used the function ColorRed()?
objSpreadsheet.Cells(p,1).
I don't know why the colors are not exact. I assume Excel can't just take any color and is restricted to 256?
This may sound strange, but try switching around the values passed into RGB. Since two of the values are the same, you should need only try:
RGB(229,229,231) '::: From above
RGB(229,231,229)
RGB(231,229,229)
And see if any of those are closer. I have experienced where the decimal value, vs. the Hex value, are reversed for some reason. Never took the time to figure out why, just accpeted it.
It is entirely possible that the Excel OWC is limited to 256 colors. Plug the code described above into a .ASP or .HTML file and then view it in IE you will see the Excel OWC in action. From there, right click on a cell and select "Property Toolbox" from the right-click menu. If you click on the little down arrow next to the paint can (for Fill) you will see that the OWC only defines 40 colors and you cannot add custom colors. I would hate to think that the OWC limits you to 40 colors, but it wouldn't necessarily surprise me.
Hope this helps,
SlyW
Business Accounts
Answer for Membership
by: p_siePosted on 2004-04-28 at 09:41:13ID: 10941073
1 Why not use Set objSpreadsheet = Server.CreateObject("Excel
2
objSpreadsheet.Cells(p,1).
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
3 objSpreadsheet.Cells(p,1).
Selection.Font.ColorIndex = 3 (3 = red)
You can get these codes by recording a macro in Excel and then look at the code