ExcelReader类 import java.io.File; import java.io.IOException; import java.util.ArrayList; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; /** * *jxl操作excel包括对象Workbook,Sheet,Cell *一个excel就对应一个Workbook对象， *一个Workbook可以有多个Sheet对象 *一个Sheet对象可以有多个Cell对象 * @author Matrix42 * */ public class ExcelReader { private File excelFile; private Workbook workbook; private Sheet sheet; private int sheetNumber; //可以用String或File构造一个ExcelReader对象 public ExcelReader(String filePath,int sheetNumber){ this.excelFile = new File(filePath); this.sheetNumber = sheetNumber; } public ExcelReader(File excelFile,int sheetNumber){ this.excelFile = excelFile; this.sheetNumber = sheetNumber; } //初始化 public void init() throws BiffException, IOException{ this.workbook = Workbook.getWorkbook(excelFile); this.sheet = workbook.getSheet(sheetNumber); } //获取columnNumber列的所有行,两个重载 //取一个区间内的 public ArrayList<String> getColumns(int columnNumber,int start,int end){ int rows = sheet.getRows(); int columns = sheet.getColumns(); if(columns<0||columnNumber>columns-1){ throw new IllegalArgumentException("column not in range!"); } if(start<0||end>rows-1){ throw new IllegalArgumentException("row not in range!"); } ArrayList<String> list = new ArrayList<String>(); for(int i=start;i<=end;i++){ Cell cell = sheet.getCell(columnNumber,i); list.add(cell.getContents()); } return list; } //从start到最后 public ArrayList<String> getColumns(int columnNumber,int start){ int rows = sheet.getRows(); int columns = sheet.getColumns(); int end = rows - 1; if(columns<0||columnNumber>columns-1){ throw new IllegalArgumentException("column not in range!"); } if(start<0){ throw new IllegalArgumentException("row not in range!"); } ArrayList<String> list = new ArrayList<String>(); for(int i=start;i<=end;i++){ Cell cell = sheet.getCell(columnNumber,i); list.add(cell.getContents()); } return list; } } 时间转换类 public class Time { private int d; private int h; private int m; private int s; //解析出天,时,分,秒,与之前的结果相加 public void add(String time) { int indexOfDay = time.indexOf("天"); int indexOfHour = time.indexOf("时"); int indexOfMinute = time.indexOf("分"); int indexOfSecond = time.indexOf("秒"); if(indexOfDay>0){ d += Integer.parseInt(time.substring(0,indexOfDay)); } if(indexOfHour>0){ if(indexOfDay>0){ h += Integer.parseInt(time.substring(indexOfDay+1,indexOfHour)); }else { h += Integer.parseInt(time.substring(0,indexOfHour)); } } if(indexOfMinute>0){ if(indexOfHour>0){ m += Integer.parseInt(time.substring(indexOfHour+1,indexOfMinute)); }else { m += Integer.parseInt(time.substring(0,indexOfMinute)); } } if(indexOfSecond>0){ if(indexOfMinute>0){ s += Integer.parseInt(time.substring(indexOfMinute+1,indexOfSecond)); }else { s += Integer.parseInt(time.substring(indexOfMinute+1,indexOfSecond)); } } } //转换时间 public String format(){ int tmp; tmp = s/60; s %= 60; m = m + tmp; tmp = m/60; m %= 60; h = h + tmp; tmp = h/24; h %= 24; d = d + tmp; String t = ""; if(d!=0){ t += d+"天"; } if(h!=0){ t += h+"时"; } if(m!=0){ t += m+"分"; } if(s!=0){ t += s+"秒"; } return t; } public int getD() { return d; } public int getH() { return h; } public int getM() { return m; } public int getS() { return s; } } import java.io.IOException; import java.util.ArrayList; import jxl.read.biff.BiffException; public class Client { public static void main(String[] args) throws BiffException, IOException { //读取第1个Sheet ExcelReader reader = new ExcelReader("D:/01.xls", 0); reader.init(); //读取第4列 ArrayList<String> result = reader.getColumns(3, 1); Time time = new Time(); //把时间加一起 for (String string : result) { time.add(string); } System.out.println("您的通话总时长为:"+time.format()+"!"); } } 使用jxl库解析的excel excel: 运行结果:

继续使用nodejs，壮哉我大前端~哈哈 这里用到nodejs的xlsx模块，在github上很火哟~ var xlsx = require('xlsx'); var workbook = xlsx.readFile('callLog.xlsx'); // 获取 Excel 中所有表名 var sheetNames = workbook.SheetNames; // 根据表名获取对应某张表 var worksheet = workbook.Sheets[sheetNames[0]]; // 存放分解开的小时、分钟、秒 var hour = []; var minute = []; var second = []; //将excel表格中的数据转换成json数据 function toJson(workbook) { var result = {}; workbook.SheetNames.forEach(function(sheetName) { var value = xlsx.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]); if(value.length > 0){ result[sheetName] = value; } }); return result; } //对时间的处理，将时间数字从字符串中分离开，进行计算 function timeFormat(time) { if(time.indexOf('小时') == -1) { hour.push(0); } else { hour.push(parseInt(time.split('小时')[0])); time = time.split('小时')[1]; } if(time.indexOf('分') == -1) { minute.push(0); } else { minute.push(parseInt(time.split('分')[0])); time = time.split('分')[1]; } if(time.indexOf('秒') == -1) { second.push(0); } else { time = time.replace('秒', ''); second.push(time); } } // 将时长进行相加 function allTime(toArr, timeArr) { var res = 0; var carry = 0; //进位数 var residue = 0; //满60进位后，剩余的数字 if(toArr == null) { return (function() { for(var i=0 ; i<timeArr.length ; i++) { res += parseInt(timeArr[i]); } return res; })(); } else { return (function() { for(var j=0 ; j<timeArr.length ; j++) { res += parseInt(timeArr[j]); } if(res >= 60) { //这里解决进位的问题 carry = parseInt(res/60); toArr.push(carry); residue = parseInt(res%60); return residue; } else { return res; } })(); } } //对个位数字进行补‘0’操作 function shiftZero(time) { if(time/10 < 1) { time = '0' + time; } return time; } // 拿到json对象组成的数组 var jsonArr = toJson(workbook).Sheet1; // 将数组中的‘通讯时长’初始化存放数组 var timesArr = []; for(var i=0 ; i<jsonArr.length ; i++) { timesArr.push(jsonArr[i]['通信时长']); } for(var j=0 ; j<timesArr.lengt敏感词imeFormat(timesArr[j]); } var allSec = allTime(minute, second); var allMins = allTime(hour,minute); var allHour = allTime(null, hour); console.log(jsonArr); console.log('-------------通信时长----------------'); console.log(timesArr); console.log('-------------总时长----------------'); console.log(allHour + '小时' + shiftZero(allMins) + '分钟' + shiftZero(allSec) + '秒'); 这里将从移动营业厅生成的excel表格信息简化一下，只留下有用的时长信息： 代码结果： 代码目录结构： 不知道代码是否适合所有形式的数据，尽量做了些格式化处理，暂时测试数据是可以的，如果有bug，欢迎指出，一起学习进步喽，哈哈~~

我是2楼的，今天看了看昨天写的代码，觉得timeFormat()这个函数写的可扩展性不好，虽然题意中，生成的excel表格中的通信时长单位无非就是：小时、分、秒，但是我在2楼写的那个函数中用if...else将逻辑写死总觉得怪怪的。所以这里把这个方法抽象成一下，写一个自我感觉好维护的的方法，如果牛友们还有好的代码，欢迎留言交流哇（这里就只贴这个方法的改进，一个小的demo，放到整体代码中稍作调整即可）： var arr = [ {'通信时长': '5小时03分01秒'}, {'通信时长': '47秒'}, {'通信时长': '1小时'}, {'通信时长': '03分20秒'}, {'通信时长': '2小时13分'} ]; var timeArr = []; // 存储‘通信时长’的数组 var step = ['小时', '分', '秒']; var saveArr = []; // 存储分隔好的‘通信时长’的数组 function timeFormat(arr) { var parseTime = {}; for(var j=0 ; j<arr.lengt敏感词imeArr.push(arr[j]['通信时长']); } console.log(timeArr); for(var i=0 ; i<timeArr.length ; i++) { var currentItem = timeArr[i]; for(var k=0 ; k<step.length ; k++) { var currentStep = step[k]; if(currentItem.indexOf(currentStep) > -1) { var currentVaule = currentItem.split(currentStep)[0]; parseTime[currentStep] = currentVaule; currentItem = currentItem.split(currentStep)[1]; } } saveArr.push(parseTime); parseTime = {}; } return saveArr; } console.log(timeFormat(arr)); 效果： js，只要是它在数组和对象中了，一切存取就迎刃而解喽，哈哈~~

弱弱继续用python 完成工作： 1.使用python的xlrd模块读excel的数据，对数据时间进行解码和编码处理，统计用户的通话时长，主被叫次数，通话时段，每日通话次数 2.使用matplotlib把用户的行为数据简单的做个可视化，就是画个图 #coding=utf-8 # 用户通话信息类记录 class UserDataInfo(object): def __init__(self): self.calling_long = 0 #主叫时长 self.called_long = 0 #被叫时长 self.call_long = 0 #总时长 self.calling_times = 0 #主叫次数 self.called_times = 0 #被叫次数 self.call_times = 0 #总次数 self.time_intervel=[] #通话时段 for i in range(0,12): self.time_intervel.append(0) self.day_intervel=[] #每日通话次数 for i in range(0,31): self.day_intervel.append(0) self.call_freq= None #通话最频繁 #coding=utf-8 import xlrd import matplotlib.pyplot as plt import UserDataInfo def readData(url): data=xlrd.open_workbook(url) table=data.sheets()[0] #多张sheet的情况读取第一张 nrows=table.nrows ncols=table.ncols list=[] for rownum in range(0,nrows): row=table.row_values(rownum) for i in range(0,ncols): #转码unicode转utf-8 row[i]=row[i].encode('utf-8') if row: list.append(row) return list #行为分析 def behavior_analysis(datalist): t=1 for line in datalist: if(t==1): t=3 continue dh=dateDecode(line[2]) day=int(dh[0]) hour=int(dh[-1]) user.day_intervel[day]+=1 user.time_intervel[hour/2]+=1 timeStr=line[3] timelong = timeDecode(timeStr) if line[4]=='主叫': user.calling_times+=1 user.calling_long+=timelong if line[4]=='被叫': user.called_times+=1 user.called_long+=timelong user.call_times=user.calling_times+user.called_times #总次数 user.call_long=user.calling_long+user.called_long #总时长 #解码时间 def timeDecode(timeStr): hour = min = sec = 0 if timeStr.find('小时') != -1: hour = timeStr.split('小时')[0] timeStr = timeStr.split('小时')[1] if timeStr.find('分') != -1: min = timeStr.split('分')[0] timeStr = timeStr.split('分')[1] if timeStr.find('秒') != -1: sec = timeStr.split('秒')[0] timelong = int(sec) + int(min) * 60 + int(hour) * 60 * 60 return timelong #编码时间 def timeEncode(time): strtime=str(time%60)+"秒" time/=60 if(time!=0): strtime=str(time%60)+"分"+strtime time/=60 if (time != 0): strtime = str(time % 60) + "小时"+strtime return strtime #解码日期 def dateDecode(dateStr): dateStrlist=dateStr.split(' ') dayStr=dateStrlist[0] dayStrlist=dayStr.split('-') day=dayStrlist[-1] timeStr=dateStrlist[-1] timeStrlist=timeStr.split(':') timeHour=timeStrlist[0] dayAndHour=[] dayAndHour.append(day) dayAndHour.append(timeHour) return dayAndHour def printout(): print "被叫次数：", user.called_times print "被叫时长：", timeEncode(user.called_long) print "主叫次数：", user.calling_times print "主叫时长：", timeEncode(user.calling_long) print "总次数：",user.call_times print "总时长：",timeEncode(user.call_long) print "日期",user.day_intervel print "时段",user.time_intervel #数据可视化 def dataVisualization(userinfo): plt.plot(userinfo.day_intervel, 'k') plt.plot(userinfo.day_intervel, 'bo') plt.xlabel(u'日 期') plt.ylabel(u'通话次数') plt.title(u'每日通话分析') plt.grid(color='#95a5a6', linestyle='--', linewidth=1, axis='y', alpha=0.4) plt.show() url="/Users/SeeKHit/Downloads/2017年01月语音通信.xls" datalist=readData(url) user=UserDataInfo.UserDataInfo() behavior_analysis(datalist) printout() dataVisualization(user)

使用jxl.jar包读取excel数据 CallTimeCount类 public class CallTimeCount { public static void main(String[] args){ ReadExcel readExcel = new ReadExcel("C:\\Users\\CAD\\Desktop\\telephonefee.xls"); System.out.print("主叫通话时长："); readExcel.countTime(readExcel.getCallingTime()); System.out.println(" 通话次数：" + readExcel.getCallingInt()); System.out.print("被叫通话时长："); readExcel.countTime(readExcel.getCalledTime()); System.out.println(" 通话次数：" + readExcel.getCalledInt()); System.out.print("总通知时长: " + readExcel.outputTimeCnt()); } } ReadExcel类 import jxl.*; import java.io.*; import java.util.*; public class ReadExcel { private int callingInt = 0; //主叫次数 private int calledInt = 0; // 被叫次数 private List<String> callingTime; // 主叫时长 private List<String> calledTime; // 被叫时长 private int day = 0; private int hour = 0; private int minute = 0; private int second = 0; // 读取表格中内容 public ReadExcel(String filePath){ callingTime = new ArrayList<>(); calledTime = new ArrayList<>(); Workbook readwb = null; try{ // 构建Workbook对象，只读Workbook对象 // 直接从本地文件创建Workbook InputStream inStream = new FileInputStream(filePath); readwb = Workbook.getWorkbook(inStream); // Sheet下标从0开始，获取第一张sheet表 Sheet readSheet = readwb.getSheet(0); // 获取Sheet表中的总列数，总行数 int cntColumns = readSheet.getColumns(); int cntRows = readSheet.getRows(); // 获取单元格的对象引用 for(int i = 1;i < cntRows; i++){ //跳过第一行表格内容头 for(int j = 0; j < cntColumns; j++){ Cell cell = readSheet.getCell(j,i); if(j == 2){ if(cell.getContents().equals("主叫")){ // 主叫统计 callingInt++; callingTime.add(readSheet.getCell(j + 2,i).getContents()); } else { // 被叫统计 calledInt++; calledTime.add(readSheet.getCell(j + 2,i).getContents()); } } } } } catch (Exception e){ e.printStackTrace(); } finally{ readwb.close(); } } // 输出呼叫次数以及各自的时长 public void countTime(List<String> time){ int ad = 0,ah = 0,am = 0,as = 0; String res = ""; for(String s : time){ int indexOfHour = s.indexOf("时"); int indexOfMinute = s.indexOf("分"); int indexOfSecond = s.indexOf("秒"); if(indexOfHour > 0){ ah += Integer.parseInt(s.substring(0,indexOfHour)); if(ah > 24){ ah %= 24; ad++; day++; } } if(indexOfMinute > 0){ am += Integer.parseInt(s.substring(indexOfHour + 1,indexOfMinute)); if(am > 59){ am %= 60; ah++; } } if(indexOfSecond > 0){ as += Integer.parseInt(s.substring(indexOfMinute + 1,indexOfSecond)); if(as > 59){ as %= 60; am++; } } } if(ad > 0) res += ad + "天"; if(ah > 0) res += ah + "时"; res += am + "分" + as + "秒"; System.out.print(res); hour += ah;minute += am;second += as; } public String outputTimeCnt(){ String res = ""; if(day > 0) res += day + "天"; res += hour + "时" + minute + "分" + second + "秒"; return res; } public int getCallingInt() { return callingInt; } public int getCalledInt() { return calledInt; } public List<String> getCallingTime() { return callingTime; } public List<String> getCalledTime() { return calledTime; } public int getDay() { return day; } public int getHour() { return hour; } public int getMinute() { return minute; } public int getSeoncd() { return second; } } 表格样式：   运行结果：

好吧，仰望高端玩家

支持

移动的似乎是html格式的详单了，不过借用python还是可以试试，mark一下

用的POI解析xls； 代码很短，正则表达式不熟练，不然能短三分之一... 而且坑爹的移动服务密码搞了我半个小时。 需要POI包的可以去网上找找，找不到私聊我，QQ：613258200，备注牛客。 Excel文件： 代码： import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; public class POI_test { public static void main(String[] args) throws FileNotFoundException, IOException { getSumTimes(); } public static void getSumTimes() throws FileNotFoundException, IOException { // 得到Excel常用对象 POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( "f:/yidong.xls")); // 得到Excel工作簿对象 HSSFWorkbook wb = new HSSFWorkbook(fs); // 得到Excel工作表对象 HSSFSheet sheet = wb.getSheetAt(0); int maxRowNums = sheet.getLastRowNum(); int second = 0; int minute = 0; int hour = 0; for (int i = 1; i < maxRowNums; i++) { // 得到Excel工作表的行 HSSFRow row = sheet.getRow(i); // 得到Excel工作表指定行的单元格 HSSFCell cell = row.getCell(4); String time = cell.getStringCellValue(); for (int j = time.length() - 1; j >= 0; j--) { if (time.charAt(j) == '秒') { StringBuffer se = new StringBuffer(); for (int k = j - 1; k >= 0 && time.charAt(k) >= 48 && time.charAt(k) <= 57; k--) { se.append(time.charAt(k)); } se.reverse(); int se_int = Integer.parseInt(se.toString()); second += se_int; } if (time.charAt(j) == '分') { StringBuffer min = new StringBuffer(); for (int k = j - 1; k >= 0 && time.charAt(k) >= 48 && time.charAt(k) <= 57; k--) { min.append(time.charAt(k)); } min.reverse(); int min_int = Integer.parseInt(min.toString()); minute += min_int; } if (time.charAt(j) == '时') { StringBuffer hou = new StringBuffer(); for (int k = j - 1; k >= 0 && time.charAt(k) >= 48 && time.charAt(k) <= 57; k--) { hou.append(time.charAt(k)); } hou.reverse(); int hour_int = Integer.parseInt(hou.toString()); hour += hour_int; } } } minute = minute + second / 60; second = second % 60; hour = hour + minute / 60; minute = minute % 60; System.out.println("总通话时间为：" + hour + " 小时 " + minute + " 分钟 " + second + " 秒 "); } } 运行效果：

中间相隔一个新年，编程之美暂搁置啦，如今又开始啦，本期编程之美明天晚上公布，没有参与的还是可以继续参与哦~~

恭喜一楼的牛油获得本次的编程之星~