第一关:获取工作簿中的数据
package com.educoder.savedata;
import java.io.InputStream;
import java.text.DecimalFormat;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class SaveWookbook {
public static void main(String[] args) throws Exception {
/********** Begin **********/
//1.通过类加载器获取本地文件并新建一个工作簿
InputStream resourceAsStream = SaveData.class.getClassLoader().getResourceAsStream("data.xls");
Workbook workbook = WorkbookFactory.create(resourceAsStream);
//2.拿到工作簿中第一个Sheet
Sheet sheet = workbook.getSheetAt(0);
//3.获取当前Sheet中的行数
int rows = sheet.getPhysicalNumberOfRows();
//4.对所有有效数据进行遍历并输出(期间无效数据通过异常捕获方式清除)
for(int n=1;n<rows;n++)
{
Row row = sheet.getRow(n);
//通过异常方式清除格式不准确、数据不存在的无效行
try{
DecimalFormat formatter1 = new DecimalFormat("########");
String trip_id = formatter1.format(row.getCell(0).getNumericCellValue());
//开始时间
FastDateFormat instance = FastDateFormat.getInstance("MM/dd/yyyyHH:mm");
String beginTimeValue = row.getCell(1).getStringCellValue();
long begintime = instance.parse(beginTimeValue).getTime();
//车辆id
int car_id = (int)row.getCell(3).getNumericCellValue();
//结束经度
double start_longitude = row.getCell(9).getNumericCellValue();
DecimalFormat formatter2 = new DecimalFormat("###.######");
String longitude = formatter2.format(start_longitude);
System.out.println("骑行id:"+trip_id+",开始时间:"+begintime+",车辆id:"+car_id+",结束经度:"+longitude);
}catch(Exception e){
}
}
/******** ** End ******* ***/
}
}
第二关:保存共享单车数据
package com.educoder.savedata;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.hadoop.hbase.client.Put;
import org.apache.hadoop.hbase.util.Bytes;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import com.educoder.util.HBaseUtil;
/*
* 读取共享单车城市行车数据
*
*/
public class SaveData {
public static void SaveBicycleData() throws Exception {
/******** ** Begin ******* ***/
HBaseUtil.createTable("t_shared_bicycle", "info");
InputStream resourceAsStream = SaveData.class.getClassLoader().getResourceAsStream("dataResources.xls");
Workbook workbook = WorkbookFactory.create(resourceAsStream);
Sheet sheet = workbook.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();
List<Put> puts = new ArrayList<Put>();
for (int n = 1; n < rows; n++) {
// 通过异常方式清除格式不准确、数据不存在的无效行
try {
Row row = sheet.getRow(n);
// 唯一骑行id,当作行rowkey
DecimalFormat formatter1 = new DecimalFormat("########");
String trip_id = formatter1.format(row.getCell(0).getNumericCellValue());
Put put = new Put(Bytes.toBytes(trip_id));
byte[] family = Bytes.toBytes("info");
// 开始时间
FastDateFormat instance = FastDateFormat.getInstance("MM/dd/yyyy HH:mm");
String beginTimeValue = row.getCell(1).getStringCellValue();
Date parse = instance.parse(beginTimeValue);
put.addColumn(family, Bytes.toBytes("beginTime"),Bytes.toBytes(String.valueOf(parse.getTime())));
// 结束时间
String endTimeValue = row.getCell(2).getStringCellValue();
Date parse2 = instance.parse(endTimeValue);
put.addColumn(family, Bytes.toBytes("endTime"),Bytes.toBytes(String.valueOf(parse2.getTime())));
// 单车识别码
int bicycleId = (int)row.getCell(3).getNumericCellValue();
put.addColumn(family, Bytes.toBytes("bicycleId"),
Bytes.toBytes(String.valueOf(bicycleId)));
// 出发地
String departure = row.getCell(4).getStringCellValue();
put.addColumn(family, Bytes.toBytes("departure"),
Bytes.toBytes(departure));
// 目的地
String destination = row.getCell(5).getStringCellValue();
put.addColumn(family, Bytes.toBytes("destination"),
Bytes.toBytes(destination));
// 所在城市
String city = row.getCell(6).getStringCellValue();
put.addColumn(family, Bytes.toBytes("city"), Bytes.toBytes(city));
// 清除目的地= 所在城市或者出发地= 目的地的无效数据
if (destination.equals(city)|| departure.equals(destination) ) {
continue;
}
//开始经度
DecimalFormat formatter2 = new DecimalFormat("###.######");
String start_longitude = formatter2.format(row.getCell(7).getNumericCellValue());
put.addColumn(family, Bytes.toBytes("start_longitude"), Bytes.toBytes(String.valueOf(start_longitude)));
//开始纬度
String start_latitude = formatter2.format(row.getCell(8).getNumericCellValue());
put.addColumn(family, Bytes.toBytes("start_latitude"), Bytes.toBytes(String.valueOf(start_latitude)));
//结束经度
String stop_longitude = formatter2.format(row.getCell(9).getNumericCellValue());
put.addColumn(family, Bytes.toBytes("stop_longitude"), Bytes.toBytes(String.valueOf(stop_longitude)));
//结束纬度
String stop_latitude = formatter2.format(row.getCell(10).getNumericCellValue());
put.addColumn(family, Bytes.toBytes("stop_latitude"),
Bytes.toBytes(String.valueOf(stop_latitude)));
puts.add(put);
} catch (Exception e) {
}
}
HBaseUtil.putByTable("t_shared_bicycle", puts);
/******* *** End ****** ****/
}
}