VBS-设置excel的格式

**

excel格式的设置

**
Dim allfile, sheetName, Status

'allfile=“C:\Users***\Desktop\模板.xlsx”
'sheetName = “4.14N-82105090”
allfile = WScript.Arguments(0)
sheetName = WScript.Arguments(1)

Set allExcel= CreateObject(“Excel.Application”)
allExcel.Visible = False
Set all = allExcel.Workbooks.Open(allfile,0)
Set allSheet = allExcel.WorkSheets(sheetName)
allExcel.DisplayAlerts = False

'设置背景色，字体，字体大小
allSheet.Range(“A1”,“AM2”).interior.Color = RGB(149, 179, 215)
allSheet.Range(“A3”,“AM5”).interior.Color = RGB(191, 191, 191)
allSheet.Range(“A1”,“AM2”).Font.Bold = True
allSheet.Range(“A1”,“H44”).Font.Size = 11
allSheet.Range(“A1”,“G44”).Font.Name = “Calibri”
allSheet.Range(“I1”,“O44”).Font.Name = “Calibri”
allSheet.Range(“Q1”,“W44”).Font.Name = “Calibri”
allSheet.Range(“Y1”,“AE44”).Font.Name = “Calibri”
allSheet.Range(“AG1”,“AM44”).Font.Name = “Calibri”
'设置边框颜色
allSheet.Range(“A1”,“H105”).Borders.ColorIndex = 1
allSheet.Range(“I1”,“P105”).Borders.ColorIndex = 1
allSheet.Range(“Q1”,“X105”).Borders.ColorIndex = 1
allSheet.Range(“Y1”,“AF105”).Borders.ColorIndex = 1
allSheet.Range(“AG1”,“AM105”).Borders.ColorIndex = 1
'分隔列背景色
allSheet.Range(“H1”,“H44”).interior.Color = RGB(166, 166, 166)
allSheet.Columns(“H:H”).ColumnWidth = 1
allSheet.Range(“P1”,“P44”).interior.Color = RGB(166, 166, 166)
allSheet.Columns(“P:P”).ColumnWidth = 1
allSheet.Range(“X1”,“X44”).interior.Color = RGB(166, 166, 166)
allSheet.Columns(“X:X”).ColumnWidth = 1
allSheet.Range(“AF1”,“AF44”).interior.Color = RGB(166, 166, 166)
allSheet.Columns(“AF:AF”).ColumnWidth = 1

'合并单元格 列
allSheet.Range(allSheet.Cells(1, 8), allSheet.Cells(105, 8)).Merge
allSheet.Range(allSheet.Cells(1, 16), allSheet.Cells(105, 16)).Merge
allSheet.Range(allSheet.Cells(1, 24), allSheet.Cells(105, 24)).Merge
allSheet.Range(allSheet.Cells(1, 32), allSheet.Cells(105, 32)).Merge
'合并单元格 行
For i=2 To 7
allSheet.Range(allSheet.Cells(1, 1), allSheet.Cells(1, i)).Merge
Next
For i=10 To 15
allSheet.Range(allSheet.Cells(1, 9), allSheet.Cells(1, i)).Merge
Next
For i=18 To 23
allSheet.Range(allSheet.Cells(1, 17), allSheet.Cells(1, i)).Merge
Next
For i=26 To 31
allSheet.Range(allSheet.Cells(1, 25), allSheet.Cells(1, i)).Merge
Next
For i=34 To 39
allSheet.Range(allSheet.Cells(1, 33), allSheet.Cells(1, i)).Merge
Next

'设置行高
allSheet.Rows(2).RowHeight = 45

'所有内容水平居中
allSheet.Range(“A:AM”).HorizontalAlignment = 3

'单元格自动换行
allSheet.Range(“B2”).WrapText = True
allSheet.Range(“C2”).WrapText = True
allSheet.Range(“D2”).WrapText = True
allSheet.Range(“E2”).WrapText = True

allSheet.Range(“J2”).WrapText = True
allSheet.Range(“K2”).WrapText = True
allSheet.Range(“L2”).WrapText = True
allSheet.Range(“M2”).WrapText = True

allSheet.Range(“R2”).WrapText = True
allSheet.Range(“S2”).WrapText = True
allSheet.Range(“T2”).WrapText = True
allSheet.Range(“U2”).WrapText = True

allSheet.Range(“Z2”).WrapText = True
allSheet.Range(“AA2”).WrapText = True
allSheet.Range(“AB2”).WrapText = True
allSheet.Range(“AC2”).WrapText = True

allSheet.Range(“AH2”).WrapText = True
allSheet.Range(“AI2”).WrapText = True
allSheet.Range(“AJ2”).WrapText = True
allSheet.Range(“AK2”).WrapText = True

'成功
Status=“success”
'MsgBox Status

'关闭allExcel
all.Save
all.Close
allExcel.Quit
Set allExcel = Nothing
WScript.StdOut.WriteLine(Status)