MyException - 我的异常网
当前位置:我的异常网» 报表 » excel表格开发点滴

excel表格开发点滴

www.MyException.Cn  网友分享于:2013-11-15  浏览:7次
excel报表开发点滴
这几天公司有个报表开发需求,UI要求也相当高,讨论决定使用excel模版来做报表需求。

报表开发主要使用宏,后端是JAVA调用POI进行隐藏sheet的数据填充,最后执行宏绘制报表。

宏如下:
Sub auto_Open()
    Sheets("i18n").Select
    v = Range("B2").Value
    If v = 0 Then
        TravellerReprot
        Sheets("i18n").Select
        Range("B2").Value = 1
        
        Sheets("Traveller Anlysis Report").Select
        ActiveWorkbook.Save
    End If
    Sheets("Traveller Anlysis Report").Select
End Sub

Rem 差旅人报表数据
Sub TravellerReprot()

    '------------------------常量定义------------------------
    '预留行数
    Const limitNum = 23
    
    '数据开始行数
    Const startNum = 7
    
    'en_US
    Const en_US_TitleMain = "Business Travel Account"
    
    Const en_US_TitleMain2 = "Traveler Analysis Report For 2012-01-01 To 2012-03-01"
    
    Const en_US_AccountName = "Account Name:"
    
    Const en_US_AccountNumber = "Account Number:"
    
    Const en_US_GenerationDate = "Generation Date:"
    
    Const en_US_Traveler = "Traveler"
    
    Const en_US_Class = "Class"
    
    Const en_US_Routing = "Routing"
    
    Const en_US_DeptDate = "Dept Date"
    
    Const en_US_TicketNumber = "Ticket Number"
    
    Const en_US_ValueRMB = "Value RMB"
    
    Const en_US_BottomName = "This is a management information report and is not to be used for accounting purposes"
    
    Const en_US_Page = "Page Number 1 Of 1"
    
    Const en_US_TotalReport = "Report Totals"
    
    'zh_CN
    Const zh_CN_TitleMain = "商务旅行账户"
    
    Const zh_CN_TitleMain2 = "员工差旅分析(2012-01-01 到 20120-03-01)"
    
    Const zh_CN_AccountName = "企业名称:"
    
    Const zh_CN_AccountNumber = "企业编号:"
    
    Const zh_CN_GenerationDate = "报表生成日期:"
    
    Const zh_CN_Traveler = "差旅人"
    
    Const zh_CN_Class = "舱位"
    
    Const zh_CN_Routing = "航程"
    
    Const zh_CN_DeptDate = "起飞/交易日期"
    
    Const zh_CN_TicketNumber = "票号"
    
    Const zh_CN_ValueRMB = "交易金额"
    
    Const zh_CN_BottomName = "这是一个管理信息报告且不被用于会计目的"
    
    Const zh_CN_Page = "第1页 共1页"
    
    Const zh_CN_TotalReport = "报表总计"
    
    '------------------------逻辑处理------------------------
    Application.ScreenUpdating = False
    
    '1.i18n初始化
    Sheets("i18n").Select
    lang = Range("B1").Value
    Debug.Print "语言选择为" & lang
    
    Debug.Print "初始化国际化信息"
    Sheets("Traveller Anlysis Report").Select
    
    If lang = "en_US" Then
        Range("C1").Value = en_US_TitleMain
        Range("B2").Value = en_US_TitleMain2
        Range("A4").Value = en_US_AccountName
        Range("C4").Value = en_US_AccountNumber
        Range("E4").Value = en_US_GenerationDate
        Range("A6").Value = en_US_Traveler
        Range("B6").Value = en_US_Class
        Range("C6").Value = en_US_Routing
        Range("D6").Value = en_US_DeptDate
        Range("E6").Value = en_US_TicketNumber
        Range("F6").Value = en_US_ValueRMB
        Range("B32").Value = en_US_BottomName
        Range("C33").Value = en_US_Page
    Else
        Range("C1").Value = zh_CN_TitleMain
        Range("B2").Value = zh_CN_TitleMain2
        Range("A4").Value = zh_CN_AccountName
        Range("C4").Value = zh_CN_AccountNumber
        Range("E4").Value = zh_CN_GenerationDate
        Range("A6").Value = zh_CN_Traveler
        Range("B6").Value = zh_CN_Class
        Range("C6").Value = zh_CN_Routing
        Range("D6").Value = zh_CN_DeptDate
        Range("E6").Value = zh_CN_TicketNumber
        Range("F6").Value = zh_CN_ValueRMB
        Range("B32").Value = zh_CN_BottomName
        Range("C33").Value = zh_CN_Page
    End If
    
    '2.是否增行判断:有效数据行数与预留行数对比
    Sheets("data").Select
    activeNum = Range("A65536").End(xlUp).Row
    Debug.Print "有效数据行数为" & activeNum
    
    totalTraveller = Range("A" & activeNum).Value
    Debug.Print "差旅人总数为" & totalTraveller
    
    If activeNum > limitNum Then
         Debug.Print "有效数据行数大于预留行数,进行增行操作"
        insertNum = activeNum - limitNum
        
        For num = 1 To insertNum Step 1
            Sheets("Traveller Anlysis Report").Select
            Rows("8:8").Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Next num
        
    End If
    
    '3.数据填充
    Debug.Print "增行判断完毕,进行数据填充"
    
    '当前正在执行的行数
    curNum = startNum
    
    '当前正在执行的差旅人
    Dim curTravelerID As Integer
    
    '标记需要数据条的单元格
    Dim needColor() As String
    Dim colorNum As Integer
    
    '循环填充
    curTravelerID = 1
    colorNum = 1
    ReDim needColor(colorNum To totalTraveller)
    For temp = 1 To activeNum Step 1
        Sheets("data").Select
        travelerID = Range("A" & temp).Value
        
        '3.1差旅人是否和上一次的为同一差旅人
        If curTravelerID = travelerID Then
            isSameTraveler = True
        Else
            isSameTraveler = False
        End If
        curTravelerID = travelerID
        
    
        '3.2判断是否需要添加数据条标记
        If isSameTraveler = False Then
            needColor(colorNum) = "F" & (curNum - 1)
            colorNum = colorNum + 1
        End If
        
        '3.3复制差旅人数据
        Sheets("Traveller Anlysis Report").Select
        
        Range("A" & curNum).FormulaR1C1 = "=data!R[-6]C[1]"
        If Range("A" & curNum) = 0 Then
            Range("A" & curNum).Value = ""
        End If
        
        Range("B" & curNum).FormulaR1C1 = "=data!R[-6]C[1]"
        If Range("B" & curNum) = 0 Then
            Range("B" & curNum).Value = ""
        End If
        
        Range("C" & curNum).FormulaR1C1 = "=data!R[-6]C[1]"
        If Range("C" & curNum) = 0 Then
            Range("C" & curNum).Value = ""
        End If
        
        Range("D" & curNum).FormulaR1C1 = "=data!R[-6]C[1]"
        If Range("D" & curNum) = 0 Then
            Range("D" & curNum).Value = ""
        End If
        
        Range("E" & curNum).FormulaR1C1 = "=data!R[-6]C[1]"
        If Range("E" & curNum) = 0 Then
            Range("E" & curNum).Value = ""
        End If
        
        Range("F" & curNum).FormulaR1C1 = "=data!R[-6]C[1]"
        
        '3.4当前行+1,进入下次循环
        curNum = curNum + 1
    Next temp
    
    '3.5有效数据行的最后一行的最后一列加入needColor
    needColor(colorNum) = "F" & (curNum - 1)
    
    '4.设置样式表
    '需要设置的单元格
    Dim colorBarCells As String
    '单元格的总和
    Dim colorBarCellsSum As Long
    For i = 1 To UBound(needColor)
        colorBarCells = colorBarCells + needColor(i) + ","
        colorBarCellsSum = colorBarCellsSum + Range(needColor(i)).Value
    Next i
    
    colorBarCells = Mid(colorBarCells, 1, Len(colorBarCells) - 1)
    Debug.Print "待添加渐变单元格:" & colorBarCells
    Debug.Print "待添加渐变单元格的总和:" & colorBarCellsSum
    
    Sheets("Traveller Anlysis Report").Select
    Range(colorBarCells).Select
    Selection.FormatConditions.AddDatabar
    Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    'With Selection.FormatConditions(1)
    '    .MinPoint.Modify newtype:=xlConditionValueLowestValue
   '     .MaxPoint.Modify newtype:=xlConditionValueHighestValue
   ' End With
    With Selection.FormatConditions(1).BarColor
        .Color = 8700771
        .TintAndShade = 0
    End With
    
    '5.设置统计栏
    Sheets("Traveller Anlysis Report").Select
    
    Range("A" & curNum).Select
    If lang = "en_US" Then
        Range("A" & curNum) = en_US_TotalReport
    Else
        Range("A" & curNum) = zh_CN_TotalReport
    End If
    Selection.Font.Bold = True
    
    Range("F" & curNum).Select
    Range("F" & curNum) = colorBarCellsSum
    Selection.Font.Bold = True
    
    Range("A" & curNum & ":" & "F" & curNum).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
    Range("A65000").Select
    Application.ScreenUpdating = True
     
End Sub


特别注意的是注释那段,当关闭excel后会报告excel格式错误问题,但手动操作的没问题,非常怪异,特此标记一下。

VBA代码可以进一步进行优化,不过在那里面写真的是没eclipse方便...
另外经常写到循环或者判断的时候,打进去的代码居然是JAVA代码,悲催啊...
丫的,突然想起来这货不是JAVA啊~~~

文章评论

聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
旅行,写作,编程
旅行,写作,编程
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
老程序员的下场
老程序员的下场
 程序员的样子
程序员的样子
程序员都该阅读的书
程序员都该阅读的书
我是如何打败拖延症的
我是如何打败拖延症的
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
程序员必看的十大电影
程序员必看的十大电影
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
漫画:程序员的工作
漫画:程序员的工作
为什么程序员都是夜猫子
为什么程序员都是夜猫子
我的丈夫是个程序员
我的丈夫是个程序员
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
10个调试和排错的小建议
10个调试和排错的小建议
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
如何成为一名黑客
如何成为一名黑客
代码女神横空出世
代码女神横空出世
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
程序员的鄙视链
程序员的鄙视链
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
中美印日四国程序员比较
中美印日四国程序员比较
程序员和编码员之间的区别
程序员和编码员之间的区别
那些争议最大的编程观点
那些争议最大的编程观点
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
程序员应该关注的一些事儿
程序员应该关注的一些事儿
每天工作4小时的程序员
每天工作4小时的程序员
总结2014中国互联网十大段子
总结2014中国互联网十大段子
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有