1 #!/bin/bash
  2 
  3 #按时间定义文件名
  4 Date="交通"$(date "+%Y-%m-%d")"离线"
  5 
  6 
  7 #当天文件
  8 newfile=/mnt/txt/$Date.txt
  9 
 10 #(获取离线列表)
 11 cat /dev/null > /tmp/list1.txt
 12 /root/Mierx/ping_jt.sh |grep "离线" >> /tmp/list1.txt
 13 
 14 #重新排序
 15 sort -k 2 /tmp/list1.txt > $newfile
 16 
 17 #-------------------------------------------创建csv文件---------------------------------------------------#
 18 csv=/mnt/csv/jt.csv
 19 
 20 #写入头信息避免中文乱码
 21 printf "\xEF\xBB\xBF" > $csv
 22 count=`cat $newfile |wc -l`
 23 #写入标题
 24 echo ‘IP,通道名称,时间,状态,="离线数量"‘,$count >> $csv
 25 #---------------------------------------------------------------------------------------------------------#
 26 
 27 
 28 #--------------------------------------------按天向前逐步判断---------------------------------------------#
 29 cont=0
 30 for ((i=1;i<=30;i++));do
 31     file=/mnt/txt/"交通"$(date "+%Y-%m-%d" -d "-$i days")"离线".txt
 32     
 33     if [ -f "$file" ]; then
 34         
 35         #提取新增离线行IP
 36         IP=/tmp/ip.txt
 37         cut -d "," -f 1-2 $file > $IP
 38 
 39         #找出当天文件比昨天多出来的行，写入到new_line.txt (获取新增离线列表)
 40         grep -vFf $IP $newfile > /tmp/new_line.txt
 41     
 42     
 43         #判断新增行数是否大于0
 44         if (( $(cat /tmp/new_line.txt |wc -l) > 0));then
 45             
 46             #清空$IP重新赋值
 47             cat /dev/null > $IP
 48 
 49             #循环查找newfile新增行，并改名
 50             while read line
 51             do
 52                 sed "s|$line|$line $((i-cont))天|" $newfile >> $IP     
 53             done < /tmp/new_line.txt
 54 
 55             #获取改名后的新增离线列表
 56             new=0
 57             grep "离线 $((i-cont))天" $IP > $new
 58 
 59             
 60             #----------------------删除原有列表中的新增行-----------------------#
 61             #过滤新增离线行的首列关键字
 62             cat $new |cut -d "," -f 1-2 > $IP
 63 
 64             #清空1p.txt
 65             cat /dev/null > /tmp/tmp1$i.txt
 66 
 67             #复制源文件到tmp2.txt(避免源文件被覆盖)
 68             /bin/cp $newfile /tmp/tmp2$i.txt
 69 
 70             #循环删除tmp2.txt中未改名的离线行
 71             while read line
 72             do
 73                 cat /tmp/tmp2$i.txt | sed -e "/^$line/d" > /tmp/tmp1$i.txt
 74                 cat /tmp/tmp1$i.txt > /tmp/tmp2$i.txt
 75             done < $IP
 76             #-------------------------------------------------------------------#
 77 
 78 
 79             #输出离线行数
 80             cat $new >> $csv
 81             
 82             #剩余行数重新赋值给newfile
 83             newfile=/tmp/tmp2$i.txt
 84             
 85             cont=0
 86         fi
 87     else
 88         #如果文件不存在，进行计数
 89         let "cont++"
 90 
 91     fi
 92 
 93 done
 94 
 95 #---------------------------------------------------------------------------------------------------------#
 96 
 97 
 98 #--------------------------------------------输出剩余掉线行数---------------------------------------------#
 99 
100 for ((i=30;i>0;i--));do
101     file=/mnt/txt/"交通"$(date "+%Y-%m-%d" -d "-$i days")"离线".txt
102 
103     if [ -f "$file" ];then
104             
105             #拷贝newfile文件避免被覆盖
106             /bin/cp $newfile /tmp/tmp.txt
107             
108             IP=/tmp/ip.txt
109             cat /dev/null > $IP
110             #循环查找newfile新增行，并改名输出到$IP
111             while read line
112             do
113                 sed "s|$line|$line >$i天|" $newfile >> $IP     
114             done < /tmp/tmp.txt
115             
116             #获取改名后的新增离线列表
117             new=0
118             grep "离线 >$i天" $IP > $new
119 
120         
121             #输出剩余离线行
122             cat $new >> $csv
123             c=`cat /mnt/txt/$Date.txt |wc -l`
124             /usr/bin/python3 /root/py/jt.py
125             echo "输出$c行离线:)"
126 
127             return
128         else
129             #判断历史天数如果为1，输出当天离线行
130             if (( $i == 1 ));then
131                 
132                 #拷贝newfile文件避免被覆盖
133                 /bin/cp $newfile /tmp/tmp.txt
134                 
135                 IP=/tmp/ip.txt
136                 cat /dev/null > $IP
137                 #循环查找newfile新增行，并改名输出到$IP
138                 while read line
139                 do
140                     sed "s|$line|$line 1天|" $newfile >> $IP     
141                 done < /tmp/tmp.txt
142                 
143                 #获取改名后的新增离线列表
144                 new=0
145                 grep "离线 1天" $IP > $new
146 
147                 
148                 #输出原有离线行
149                 cat $new >> $csv
150                 c=`cat /mnt/txt/$Date.txt |wc -l`
151                 /usr/bin/python3 /root/py/jt.py
152                 echo "输出$c行离线:)"
153             fi
154     fi
155 
156 done

  1 # -*- coding: utf-8 -*
  2 coding=‘gbk‘
  3 import pandas as pd
  4 import xlrd
  5 import xlwt
  6 import datetime
  7 from openpyxl.styles  import Font
  8 from xlutils.copy import copy
  9 from openpyxl import load_workbook
 10 from openpyxl.styles import PatternFill, colors
 11 from openpyxl.styles import Border,Side
 12 from openpyxl.styles import Alignment
 13 
 14 #获取时间
 15 sj = datetime.datetime.now().strftime("%Y-%m-%d")
 16 file1="/mnt/csv/jt.csv"
 17 def csv_to_xlsx_pd():
 18     csv = pd.read_csv(file1, encoding=‘utf-8‘)
 19     csv.index = csv.index + 1
 20     csv.to_excel(‘/mnt/csv/jt.xlsx‘, sheet_name=‘data‘)
 21 
 22 if __name__ == ‘__main__‘:
 23     csv_to_xlsx_pd()
 24 
 25 filename = "/mnt/csv/jt.xlsx"
 26 wb = load_workbook(filename)
 27 ws = wb[wb.sheetnames[0]]
 28 ws.column_dimensions[‘A‘].width = 5.0
 29 ws.column_dimensions[‘B‘].width = 15.0
 30 ws.column_dimensions[‘C‘].width = 33.0
 31 ws.column_dimensions[‘D‘].width = 18.0
 32 ws.column_dimensions[‘E‘].width = 12.0
 33 ws.column_dimensions[‘F‘].width = 10.0
 34 ws.column_dimensions[‘G‘].width = 10.0
 35 ws.row_dimensions[1].height = 18
 36 ws.cell(1,1).value = ‘序号‘
 37 
 38 ws.freeze_panes = ‘A2‘
 39 #(标题字体)
 40 sheet=wb.active
 41 font=Font(name=‘微软雅黑‘,size=11,bold=True)
 42 
 43 #设置标题居中方式
 44 align = Alignment(horizontal=‘center‘,vertical=‘center‘)
 45 
 46 #置标题字体样式
 47 line1 = ["A1","B1","C1","D1","E1","F1","G1"]
 48 for x in line1:
 49     cell=sheet[x]
 50     cell.font=font
 51     ws[x].alignment = align
 52 
 53 #(在线率字体)
 54 sheet=wb.active
 55 font=Font(name=‘微软雅黑‘,size=11,bold=True,color=‘FF0000‘)
 56 #置标题字体样式
 57 line1 = ["F2","G2"]
 58 for x in line1:
 59     cell=sheet[x]
 60     cell.font=font
 61     ws[x].alignment = align
 62 
 63 
 64 #定义背景颜色
 65 color1 = PatternFill("solid", fgColor="BCEE68")
 66 color2 = PatternFill("solid", fgColor="FFFF00")
 67 color3 = PatternFill("solid", fgColor="FF0000")
 68 color4 = PatternFill("solid", fgColor="FFD700")
 69 
 70 line1 = ["A1","B1","C1","D1","E1"]
 71 for x in line1:
 72     ws[x].fill = color2
 73 
 74 
 75 #获取表格行数并转换为数据类型
 76 ws.cell(1,7).value = int(ws.cell(1,7).value)
 77 
 78 #将行数赋值给变量m
 79 m = ws.cell(1,7).value +2
 80 
 81 #定义边框样式
 82 border = Border(left=Side(border_style=‘thin‘,color=‘000000‘),
 83 right=Side(border_style=‘thin‘,color=‘000000‘),
 84 top=Side(border_style=‘thin‘,color=‘000000‘),
 85 bottom=Side(border_style=‘thin‘,color=‘000000‘))
 86 
 87 #循环嵌套添加边框
 88 col = ["A","B","C","D","E"]
 89 for x in col:
 90     #按列循环行
 91     for z in range(1,m):
 92         xz = x + str(z)
 93         ws[xz].border = border
 94 
 95 
 96 font=Font(name=‘宋体‘,size=11)
 97 #设置序列字体样式
 98 for i in range(2,m):
 99     l1 = "A" + str(i)
100     cell=sheet[l1]
101     cell.font=font
102 
103 #循环定位球机行,从第2开始到m行
104 col = ["A","B","C","D","E"]
105 for i in range(2,m):
106     #查找第3列包含球机的行
107     val = ws.cell(i,3).value
108     pd = "球机" in val
109     qj = "全景" in val
110     zd = "终端" in val
111     
112     #更改球机行背景色
113     if pd == 1:
114         for x in col:
115             l1 = x + str(i)
116             ws[l1].fill = color1
117         
118     #更改全景行背景色
119     if qj == 1:
120         for x in col:
121             l1 = x + str(i)
122             ws[l1].fill = color3
123         
124     #更改终端背景色
125     if zd == 1:
126         for x in col:
127             l1 = x + str(i)
128             ws[l1].fill = color4
129 
130 #excel公式    
131 ws.cell(1,7).value = ‘在线率‘
132 ws.cell(2,6).value = ‘=COUNTA(B:B)-1‘
133 ws.cell(2,7).value = ‘=TEXT((773-F2)/773,"0.00%")‘
134 
135 #文件另存为
136 file1="/mnt/lixian/交通"+ str(sj) +"离线.xlsx"
137 
138 wb.save(file1)