如何使用java脚本设置excel单元格的背景颜色 [英] how to set background color of an excel cell using java script

问题描述

i想要使用javascript设置excel单元格的背景我试试这段代码但是它无法工作

i want to set the background of excel cell using javascript i try this code but its not working

1)excelSheet.cell(yval + 1, j + 1).Font.Color = "red";
2)excelSheet.Cells(i + 1, 1).Interior.colorindex = "red";

推荐答案

不，那不行，因为.ColorIndex需要一个数字，而不是文本。



如果我只记录一个我设置单元格bkg颜色的宏，我会得到以下代码：

No, that wont work, since .ColorIndex is expecting a number, rather than text.

If I simply record a macro of me setting the bkg colour of a cell, I get the following code:

Sub colorBkg()
'
' colorBkg Macro
'
'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

这是用于Excel2007的标准颜色中可用的2种红色中较暗的颜色（rgb（192,0,0））



计算用于Interior.Color的数字的方法是：



红色+（256 *绿色）+（65536 *绿色）



考虑到这一点，请考虑对录制的宏进行以下修改，将所选单元格的颜色设置为rgb（100,100,100）

This is for the darker of the 2 reds available in 'standard-colors' of Excel2007 (rgb(192,0,0))

The method for computing the number used for Interior.Color is:

Red + (256*green) + (65536*green)

With that in mind, consider the following modification to the recorded macro which sets the colour of any selected cells to rgb(100,100,100)

Sub colorBkg()
'
' colorBkg Macro
'
'
    Dim r, g, b
    r = 100
    g = 100
    b = 100
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = ((b * 65536) + (g * 256) + r)
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

所以，我希望您需要稍微更改一下代码。如果我使用对象浏览器，我会得到常量的以下值：



Const xlAutomatic = -4105（& HFFFFEFF7）

Const xlSolid = 1



您似乎不需要设置最后2个属性，因此您可能可以逃脱：

So, I would expect that you'd need to change your code a little. If I use the object browser, I get the following values for the constants:

Const xlAutomatic = -4105 (&HFFFFEFF7)
Const xlSolid = 1

You don't appear to need to set the last 2 attributes, so you could probably get away with:

excelSheet.Cells(i + 1, 1).Interior.pattern = 1;
excelSheet.Cells(i + 1, 1).Interior.PatternColorIndex = -4105;
excelSheet.Cells(i + 1, 1).Interior.color = 255;

注意： 我无法记住属性名称是否与javascript区分大小写。

Note: I cant remember if the property names are Case-Sensitive from javascript or not.

var xls = new ActiveXObject("Excel.Application") ;

//set the font color of entire row 
xls.ActiveCell.EntireRow.Font.ColorIndex = 2;

//select the row i.e rangRow here
xls.range(rangRow).Select; 
xls.Selection.Interior.ColorIndex=5

如果它解决了您的问题或有任何想法，请告诉我

let me know if it resolves your problem or got any idea

这篇关于如何使用java脚本设置excel单元格的背景颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！