通过JS调用ActiveX来控制Excel自动生成单元格文本,颜色,边框,公式以及统计图表,动态设置数据源等等
基本能实现对Excel或其他Office软件的任意操作,这里只是写一个参考
使用IE浏览器运行 ,是不是感觉电脑跟中病毒了一样,嘿嘿!
<!doctype html>
<head>
<meta charset="UTF-8">
<title>Excel Document</title>
</head>
<body>
<script>
var app = new ActiveXObject("Excel.Application"); //开启一个Excel服务
app.Visible = true; //显示Excel页面
var workbooks = app.Workbooks; //获取Excel文档容器对象
var workbook = workbooks.Add(); //向容器中添加一个文档对象
var worksheet = app.Worksheets(1); //选择该文档的第一个Sheet页对象
worksheet.Activate(); //将该对象激活为当前sheet页,即可编辑状态
var range1 = worksheet.Range("B1", "L1"); //通过range方法获取指定单元格区域的对象
range1.Select(); //将该区域选中
var txtArray = new Array(12);
txtArray[0] = "数据元素类型";
txtArray[1] = "表名";
txtArray[2] = "是表?";
txtArray[3] = "字段名";
txtArray[4] = "主键?";
txtArray[5] = "数据元素";
txtArray[6] = "检查表";
txtArray[7] = "数据类型";
txtArray[8] = "长度";
txtArray[9] = "精度";
txtArray[10] = "域名";
txtArray[11] = "搜索帮助类型";
range1.Activate();
range1.Value2 = new Array(txtArray);//不知道dynamic类型的数据怎么通过JS传到dll中去
//var range2 = worksheet.Range("C3", "M4");
//range2.Select();
worksheet.Cells(1,1).Activate();
app.ActiveCell.FormulaR1C1="原始数据"; //ActiveCell为当前激活的单元格,向单元格写入字符串
worksheet.Cells(2,1).Activate();
app.ActiveCell.FormulaR1C1="最大值";
worksheet.Cells(3,1).Activate();
app.ActiveCell.FormulaR1C1="最小值";
worksheet.Cells(4,1).Activate();
app.ActiveCell.FormulaR1C1="平均值";
for(var j=2;j<=12;j++){
worksheet.Cells(1,j).Activate();
app.ActiveCell.FormulaR1C1= (Math.random()*Math.PI).toFixed(3);
worksheet.Cells(2,j).Activate();
app.ActiveCell.FormulaR1C1="=MAX(R[-1]C["+(2-j)+"]:R[-1]C)";//向单元格写入公式
worksheet.Cells(3,j).Activate();
app.ActiveCell.FormulaR1C1="=MIN(R[-2]C["+(2-j)+"]:R[-2]C)";
worksheet.Cells(4,j).Activate();
app.ActiveCell.FormulaR1C1="=AVERAGEA(R[-3]C["+(2-j)+"]:R[-3]C)";
}
//设置单元格颜色
var range3 = worksheet.Range("A1:A4");
range3.Select();
//range3.Interior.ColorIndex = 3;
range3.Interior.Pattern = 1; //"xlSolid";
range3.Interior.PatternColorIndex = -4105;// "xlAutomatic";该常量的关系通过dll获取
range3.Interior.ThemeColor = 10;//"xlThemeColorAccent6";
range3.Interior.TintAndShade = 0.599993896298105;
range3.Interior.PatternTintAndShade = 0;
//设置线边框
var range4 = worksheet.Range("A1:L4")
range4.Select();
range4.Borders(5).LineStyle = -4142 //xlDiagonalDown = 5,xlNone = -4142
range4.Borders(6).LineStyle = -4142 //xlDiagonalUp = 6,
//xlEdgeLeft = 7,
range4.Borders(7).LineStyle = 1 //xlContinuous = 1,
range4.Borders(7).ThemeColor = 10
range4.Borders(7).TintAndShade = -0.249977111117893
range4.Borders(7).Weight = 2 //xlThin = 2,
//xlEdgeTop = 8,
range4.Borders(8).LineStyle = 1
range4.Borders(8).ThemeColor = 10
range4.Borders(8).TintAndShade = -0.249977111117893
range4.Borders(8).Weight = 2
//xlEdgeBottom = 9,
range4.Borders(9).LineStyle = 1
range4.Borders(9).ThemeColor = 10
range4.Borders(9).TintAndShade = -0.249977111117893
range4.Borders(9).Weight = 2
//xlEdgeRight = 10,
range4.Borders(10).LineStyle = 1
range4.Borders(10).ThemeColor = 10
range4.Borders(10).TintAndShade = -0.249977111117893
range4.Borders(10).Weight = 2
//xlInsideVertical = 11,
range4.Borders(11).LineStyle = 1
range4.Borders(11).ThemeColor = 10
range4.Borders(11).TintAndShade = -0.249977111117893
range4.Borders(11).Weight = 2
//xlInsideHorizontal = 12,
range4.Borders(12).LineStyle = 1
range4.Borders(12).ThemeColor = 10
range4.Borders(12).TintAndShade = -0.249977111117893
range4.Borders(12).Weight = 2
//添加统计图表,选择数据源
var range5 = worksheet.Range("A1:L4");
range5.Select();
var shape = worksheet.Shapes.AddChart2(201, 54);
shape.Select(); //XlChartType.xl3DColumnClustered=54
app.ActiveChart.SetSourceData(range5)
//设置图表的位置和大小
shape.Left=10;
shape.Top = 60;
shape.Height = 250;
shape.Width = 500;
//sheet的重命名
worksheet.Name = "Excel Test";
try {
var fname = app.Application.GetSaveAsFilename("Excel.xlsx", "Excel Test File (*.xlsx), *.xlsx");
} catch (e) {
print("Nested catch caught " + e);
} finally {
workbook.SaveAs(fname);
workbook.Close(savechanges = false);
app.Quit();
app = null;
}
</script>
</body>
</html>