j题目来源:牛客网编程之美栏目
登录中国联通网上营业厅 /电信/移动 后选择「自助服务」 --> 「详单查询」,然后选择你要查询的时间段,点击「查询」按钮,查询结果页面的最下方,点击「导出」,就会生成类似于 2017年01月01日~2017年01月31日通话详单.xls 文件。写代码,对每月通话时间做个统计。
使用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;
}
}
表格样式:
运行结果:
参考链接: