|
- Sub test2()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- Dim wordapp As Word.Application
- Dim worddoc As Word.Document
- With Worksheets("sheet1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- c = .Cells(4, .Columns.Count).End(xlToLeft).Column
- arr = .Range("a3").Resize(r - 2, c)
- End With
- For j = 4 To UBound(arr, 2) Step 3
- Set d(arr(1, j)) = CreateObject("scripting.dictionary")
- nh3 = Application.Large(Application.Index(arr, 0, j + 2), 3)
- d1.RemoveAll
- For i = 3 To UBound(arr)
- If Len(arr(i, j)) <> 0 Then
- If Not d1.Exists(arr(i, 2)) Then
- m = 1
- ReDim brr(1 To m)
- Else
- brr = d1(arr(i, 2))
- m = UBound(brr) + 1
- ReDim Preserve brr(1 To m)
- End If
- brr(m) = arr(i, j + 1)
- d1(arr(i, 2)) = brr
- End If
- Next
- For Each aa In d1.keys
- brr = d1(aa)
- bh3 = Application.Large(brr, 3)
- d1(aa) = bh3
- Next
- For w = 1 To 2
- For i = 3 To UBound(arr)
- If Len(arr(i, j)) <> 0 Then
- If w = 1 Then
- xm = arr(i, 2)
- Else
- xm = 0
- End If
- If Not d(arr(1, j)).Exists(xm) Then
- Set d(arr(1, j))(xm) = CreateObject("scripting.dictionary")
- End If
- q = 0
- If arr(i, j + w) <= 3 Then
- q = 1
- ElseIf arr(i, j + w) >= IIf(w = 1, d1(arr(i, 2)), nh3) Then
- q = 2
- End If
- If q <> 0 Then
- If Not d(arr(1, j))(xm).Exists(q) Then
- m = 1
- ReDim brr(1 To 5, 1 To m)
- Else
- brr = d(arr(1, j))(xm)(q)
- m = UBound(brr, 2) + 1
- ReDim Preserve brr(1 To 5, 1 To m)
- End If
- brr(1, m) = arr(i, 1)
- brr(2, m) = arr(i, 2)
- brr(3, m) = arr(i, 3)
- brr(4, m) = arr(i, j)
- brr(5, m) = arr(i, j + w)
- d(arr(1, j))(xm)(q) = brr
- End If
- End If
- Next
- Next
- Next
- For Each aa In d.keys
- For Each bb In d(aa).keys
- For Each cc In d(aa)(bb).keys
- brr = d(aa)(bb)(cc)
- For i = 1 To UBound(brr, 2) - 1
- p = i
- For j = i + 1 To UBound(brr, 2)
- If IIf(cc = 1, brr(5, p) > brr(5, j), brr(5, p) < brr(5, j)) Then
- p = j
- End If
- Next
- If p <> i Then
- For k = 1 To UBound(brr)
- temp = brr(k, i)
- brr(k, i) = brr(k, p)
- brr(k, p) = temp
- Next
- End If
- Next
- d(aa)(bb)(cc) = brr
- Next
- Next
- Next
- Set wordapp = New Word.Application
- wordapp.Visible = True
- Set worddoc = wordapp.Documents.Add
- worddoc.Select
- With wordapp.Selection
- For Each aa In d.keys
- With .Font
- .Name = "黑体"
- .Size = 20
- End With
- .ParagraphFormat.Alignment = wdAlignParagraphCenter
- .ParagraphFormat.CharacterUnitFirstLineIndent = 0
- .ParagraphFormat.FirstLineIndent = CentimetersToPoints(0)
- .TypeText Text:=aa & "排名"
- .TypeParagraph
- With .Font
- .Name = "宋体"
- .Size = 16
- End With
- .Font.Bold = True
- .ParagraphFormat.Alignment = wdAlignParagraphLeft
- .TypeText Text:="一、年级排名"
- .Font.Bold = False
- .TypeParagraph
- .ParagraphFormat.Alignment = wdAlignParagraphJustify
- .ParagraphFormat.CharacterUnitFirstLineIndent = 2
- brr = d(aa)(0)(1)
- ss = ""
- For j = 1 To UBound(brr, 2)
- ss = ss & "第" & brr(5, j) & "名是" & brr(2, j) & brr(1, j) & ",学籍号是" & brr(3, j) & ",成绩是" & brr(4, j) & "分;"
- Next
- .TypeText Text:=ss
- .TypeParagraph
- brr = d(aa)(0)(2)
- ss = "后三名是"
- For j = 1 To UBound(brr, 2)
- ss = ss & brr(2, j) & brr(1, j) & ",学籍号是" & brr(3, j) & ",成绩是" & brr(4, j) & "分;"
- Next
- .TypeText Text:=ss
- .TypeParagraph
- ' .ParagraphFormat.Alignment = wdAlignParagraphLeft
- .ParagraphFormat.Alignment = wdAlignParagraphJustify
- .ParagraphFormat.CharacterUnitFirstLineIndent = 0
- .ParagraphFormat.FirstLineIndent = CentimetersToPoints(0)
- .Font.Bold = True
- .TypeText Text:="二、班级排名"
- .Font.Bold = False
- .TypeParagraph
- For Each bb In Array("一班", "二班", "三班")
- If d(aa).Exists(bb) Then
- .ParagraphFormat.Alignment = wdAlignParagraphCenter
- ' .ParagraphFormat.Alignment = wdAlignParagraphJustify
- .ParagraphFormat.CharacterUnitFirstLineIndent = 0
- .ParagraphFormat.FirstLineIndent = CentimetersToPoints(0)
- .Font.Bold = True
- .TypeText Text:=bb
- .Font.Bold = False
- .TypeParagraph
- .ParagraphFormat.Alignment = wdAlignParagraphLeft
- brr = d(aa)(bb)(1)
- ss = ""
- For j = 1 To UBound(brr, 2)
- ss = ss & "第" & brr(5, j) & "名是" & brr(2, j) & brr(1, j) & ",学籍号是" & brr(3, j) & ",成绩是" & brr(4, j) & "分;"
- Next
- .ParagraphFormat.Alignment = wdAlignParagraphJustify
- .ParagraphFormat.CharacterUnitFirstLineIndent = 2
- .TypeText Text:=ss
- .TypeParagraph
- brr = d(aa)(bb)(2)
- ss = "后三名是"
- For j = 1 To UBound(brr, 2)
- ss = ss & brr(2, j) & brr(1, j) & ",学籍号是" & brr(3, j) & ",成绩是" & brr(4, j) & "分;"
- Next
- .TypeText Text:=ss
- .TypeParagraph
- End If
- Next
- Next
- End With
- worddoc.SaveAs ThisWorkbook.Path & "\排名.doc"
- ' wordapp.Quit
- ' Set wordapp = Nothing
- ' Set worddoc = Nothing
- Application.DisplayAlerts = True
- End Sub
复制代码 |
|