刚刚做过把排好的考场座位导出为Excel格式,第一行为标题,说明第几考场、是哪个房间,每个座位一个单元格,里面包括报考学校、考号、姓名,每个单元格都要加边框。排考场的数据是从数据库查询到数据集中,包括考场号、第几行、第几列、考生信息。
代码如下。
其中rng = myExcel.Range(Chr(64 + tbl.Rows(i)(2)).ToString + (tbl.Rows(i)(1) + 2).ToString) ', Chr(64 + nCols).ToString + (nRows + 2).ToString)这句是根据数据库中记录的座位所在的行列坐标来确定放在Excel的哪个单元格中。
Dim myExcel As New Microsoft.Office.Interop.Excel.Application
myExcel.DisplayAlerts = False '这句使得在退出时不会询问“sheet1已更改,是否保存”之类的问题
Try
myExcel.Workbooks.Add(True)
myExcel.Visible = False
Dim rng As Excel.Range
Dim i As Integer
For i = 0 To tbl.Rows.Count - 1
rng = myExcel.Range(Chr(64 + tbl.Rows(i)(2)).ToString + (tbl.Rows(i)(1) + 2).ToString) ', Chr(64 + nCols).ToString + (nRows + 2).ToString)
rng.Value = tbl.Rows(i)(5) + vbCrLf + tbl.Rows(i)(3) + " " + tbl.Rows(i)(4)
rng.Font.Name = "宋体"
rng.Font.FontStyle = "加粗"
rng.HorizontalAlignment = 3 'xlCenter
rng.VerticalAlignment = 2 'xlCenter
rng.RowHeight = 50
rng.ColumnWidth = CStr(rng.Value).Length
'添加边框
rng.BorderAround(1, 3)
Next
'设置标题
myExcel.Range("a1", Chr(64 + nCols).ToString + "1").Merge(True)
rng = myExcel.Range("a1")
rng.Value = "第" + strKcbh + "考场 " + strKcmc + "(" + strMinKsbh + "-" + strMaxKsbh + ")"
rng.Font.Name = "宋体"
rng.Font.FontStyle = "加粗"
rng.Font.Size = 20
rng.HorizontalAlignment = 3 'xlCenter
rng.VerticalAlignment = 2 'xlCenter