/**
*功能:登录中国联通网上营业厅 /电信/移动 后选择「自助服务」 –> 「详单查询」,然后选择你要查询的时间段,点击「查询」按钮,查询结果页面的最下方,点击「导出」,就会生成类似于 2017年01月01日~2017年01月31日通话详单.xls 文件。写代码,对每月通话时间做个统计。
1.在ExcelReader类中对导出的excel通讯录的通话部分数据进行读取
2.在Time类中,对读出的时间进行处理
3.在Client类中测试
*@author:某位牛客网大神
*/
import java.io.*;
import java.util.*;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.Sheet;
import jxl.Cell;
/**
* jxl操作excel包括对象Workbook,Sheet,Cell
* 一个excel就对应一个Workbook对象,
* 一个Workbook可以有多个Sheet对象,
* 一个Sheet对象可以有多个Cell对象
* @author NST_Xx
*
*/
public class ExcelReader {
private File excelFile;
private Workbook workbook;
private Sheet sheet;
private int sheetNumber;
//构造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);
}
//获取columNumber列的所有行,两个重载
//取一个区间内的
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("colum 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("colum 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;
}
}
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(0, indexOfSecond));
}
}
}
public String format(){
int temp;
temp = s/60;
s%=60;
m=m+temp;
temp=m/60;
m%=60;
h=h+temp;
temp=h/24;
h%=24;
d = d+temp;
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.*;
import jxl.read.biff.BiffException;
public class Client {
public static void main(String[] args) throws BiffException, IOException {
ExcelReader reader = new ExcelReader("E:/01.xls",0);
reader.init();
//读取第四列
ArrayList<String> result = reader.getColumns(3, 1);
Time time = new Time();
//把时间加在一起
for(String string:result){
time.add(string);
}
System.out.println("您的通话总时长为:"+time.format()+"!");
}
}