Java如何输出Word报告

目录

思路：

注意事项

---

很多企业每月或每季都会出销售报告或绩效报告等，通常都是以PPT文件的形式上报给公司领导或客户。

因PPT在java运用中有所局限（有精通此方面的同行可具体沟通）本人做过的项目则是输出Word报告。

思路：

1、定义Word报告模板，编辑好通用的内容，数据和图表位置插入文本框，然后在文本框中插入书签名（方便调整位置）

2、定义Excel图表模板，编辑宏调整图表样式

 Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Public Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long

    
Sub adjustLineChart()

    Worksheets("净值走势").Activate
    Sheets("净值走势").Select
    ActiveSheet.ChartObjects("图表 1").Activate
    xlCategoryAmount = Excel.Application.WorksheetFunction.CountA(Sheet2.range("A2:A65536"))
    xTickAmount = Int(xlCategoryAmount / 6)
    ' MsgBox (xTickAmount)
    If xTickAmount <> 0 Then
    ActiveChart.Axes(xlCategory).TickLabelSpacing = xTickAmount
    End If
    
    Set hs300Set = Worksheets("净值走势").range("B2:B65536")
    hs300_max = Application.WorksheetFunction.Max(hs300Set)
    hs300_min = Application.WorksheetFunction.Min(hs300Set)
    
    Set productSet = Worksheets("净值走势").range("C2:C65536")
    product_max = Application.WorksheetFunction.Max(productSet)
    product_min = Application.WorksheetFunction.Min(productSet)
    
    line_max = IIf(hs300_max > product_max, hs300_max, product_max)
    line_min = IIf(hs300_min < product_min, hs300_min, product_min)
    'MsgBox (line_max - line_min)
    tmpUnit = Int((line_max - line_min) * 100 / 4) / 100
    If tmpUnit <> 0 Then
    ActiveChart.Axes(xlValue).MajorUnit = tmpUnit
    End If
    
      '按日期升序排序
    'Dim rng As range
    'Dim maxRow
   ' maxRow = ActiveSheet.UsedRange.Cells(1, 1).Row + ActiveSheet.UsedRange.Rows.Count - 1
  '  'Set rng = range("A" & maxRow)
 '  Worksheets("净值走势").range("A1:C" & maxRow).Sort _
      '  Key1:=Worksheets("净值走势").range("A1"), _
     '   Order1:=xlAscending, _
      '  Header:=xlYes
        
End Sub


Sub adjustColumnChart()

    Worksheets("行业持仓").Activate
    Sheets("行业持仓").Select
    ActiveSheet.ChartObjects("图表 2").Activate
    ActiveChart.ChartTitle.Text = Worksheets("净值走势").range("C1").Text & "行业持仓"
     With ActiveChart.ChartTitle.Format.TextFrame2.TextRange.Font
            .BaselineOffset = 0
            .NameFarEast = "SourceHanSansCN-Normal"
    End With
    
    Set hs300Set = Worksheets("行业持仓").range("B2:AA2")
    hs300_max = Application.WorksheetFunction.Max(hs300Set)
    hs300_min = Application.WorksheetFunction.Min(hs300Set)
    
    Set productSet = Worksheets("行业持仓").range("B3:AA3")
    product_max = Application.WorksheetFunction.Max(productSet)
    product_min = Application.WorksheetFunction.Min(productSet)
    
    line_max = IIf(hs300_max > product_max, hs300_max, product_max)
    line_min = IIf(hs300_min < product_min, hs300_min, product_min)
    tmpUnit = Int((line_max - line_min) * 100 / 4) / 100
    If tmpUnit <> 0 Then
    ActiveChart.Axes(xlValue).MajorUnit = tmpUnit
    End If
End Sub

Sub adjustJDColumnChart()

    Worksheets("季度行业持仓").Activate
    Sheets("季度行业持仓").Select
    ActiveSheet.ChartObjects("图表 2").Activate
    ActiveChart.ChartTitle.Text = Worksheets("净值走势").range("C1").Text & "季度行业持仓"
    With ActiveChart.ChartTitle.Format.TextFrame2.TextRange.Font
            .BaselineOffset = 0
            .NameFarEast = "SourceHanSansCN-Normal"
    End With
    

    Set hs300Set = Worksheets("季度行业持仓").range("B2:AA2")
    hs300_max = Application.WorksheetFunction.Max(hs300Set)
    hs300_min = Application.WorksheetFunction.Min(hs300Set)
    
    Set productSet = Worksheets("季度行业持仓").range("B3:AA3")
    product_max = Application.WorksheetFunction.Max(productSet)
    product_min = Application.WorksheetFunction.Min(productSet)
    
    line_max = IIf(hs300_max > product_max, hs300_max, product_max)
    line_min = IIf(hs300_min < product_min, hs300_min, product_min)
    tmpUnit = Int((line_max - line_min) * 100 / 4) / 100
    If tmpUnit <> 0 Then
    ActiveChart.Axes(xlValue).MajorUnit = tmpUnit
    End If
End Sub

Sub adjustText()

   Dim ShTest1 As Worksheet
   Dim ShTest2 As Worksheet
   Dim ShTest3 As Worksheet
   Dim ShTest4 As Worksheet
   Dim ShTest5 As Worksheet
   Dim ShTest6 As Worksheet
   Dim ShTest7 As Worksheet
   Dim introw As Double
  
   
   Set ShTest1 = Sheets("业绩指标")
   Set ShTest2 = Sheets("净值走势")
   Set ShTest3 = Sheets("月度收益率")
   Set ShTest4 = Sheets("行业持仓")
   Set ShTest5 = Sheets("季度行业持仓")
   Set ShTest7 = Sheets("组合暴露度")
   

   
   '设置文本字体、大小、颜色

    ShTest1.range("A2:B2").Font.Color = RGB(168, 33, 39)
    ShTest1.range("A2:B2").Font.Name = "思源黑体 CN Medium"
    
    If Len(ShTest1.range("B2:B2").Text) >= 8 Then
     ShTest1.range("A2:B2").Font.Size = 24
    Else
     ShTest1.range("A2:B2").Font.Size = 31
    End If
  
    ShTest1.range("B1").Font.Color = RGB(168, 33, 39)
    ShTest1.range("B1").Font.Size = 21
    ShTest1.range("B1").Font.Name = "思源黑体 CN Medium"
    
    ShTest1.range("A3:B3").Font.Color = RGB(168, 33, 39)
    ShTest1.range("A3:B3").Font.Size = 14
    ShTest1.range("A3:B3").Font.Name = "思源黑体 CN Medium"
  
    
    ShTest1.range("A5:C5").Font.Color = RGB(168, 33, 39)
    ShTest1.range("A5:C5").Font.Size = 16
    ShTest1.ra