一、主要代码
package com.sgcc.mcsas.bigdata.tool;
import com.sgcc.mcsas.bigdata.service.HBaseServiceImpl;
import com.sgcc.mcsas.bigdata.service.IHBaseService;
import org.apache.hadoop.hbase.Cell;
import org.apache.hadoop.hbase.client.Result;
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 java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
/**
* Created by dwxx-120 on 2016/7/12.
*/
public class Export {
private static IHBaseService service = new HBaseServiceImpl();
private static final int count = 5;//记录数
private static final String fileName = "D:/hbase_export.xls";
public static void exportExcel(List<String> tableNames) throws Exception {
//创建excel文件
File file = new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
//设定输出流
FileOutputStream fos = new FileOutputStream(file);
HSSFWorkbook book = new HSSFWorkbook();
for (int k = 0; k < tableNames.size(); k++) {
String tableName = tableNames.get(k);
//取部分数据
List<Result> results = service.getTopSomeRecords(tableName, "05M", count);
//创建sheet
HSSFSheet sheet = book.createSheet();
book.setSheetName(k, tableName.replace(":", "_"));
//为空返回
if (results.size() == 0) {
System.out.println(tableName + " has no data!");
continue;
}
//生成表头
HSSFRow header = sheet.createRow(0);
HSSFCell header_rowkey = header.createCell(0);
//rowkey表头
header_rowkey.setCellValue("ROWKEY");
//其他表头
List<Cell> cells0 = results.get(0).listCells();
for (int i = 0; i < cells0.size(); i++) {
HSSFCell header_other = header.createCell(i + 1);
String name = new String(cells0.get(i).getQualifier());
header_other.setCellValue(name);
}
//遍历查询的数据
for (int i = 0; i < results.size(); i++) {
//一个Result创建一行
HSSFRow data_row = sheet.createRow(i + 1);
Result r = results.get(i);
//设置rowkey的值
String rowkey = new String(r.getRow());
HSSFCell data_rowkey = data_row.createCell(0);
data_rowkey.setCellValue(rowkey);
//设置其他值
List<Cell> cellList = r.listCells();
for (int j = 0; j < cellList.size(); j++) {
HSSFCell data_other = data_row.createCell(j + 1);
data_other.setCellValue(new String(cellList.get(j).getValue()));
}
}
}
//写入
book.write(fos);
}
public static void main(String args[]) throws Exception {
//HBase表名
List<String> list = new ArrayList<String>();
list.add("mcsas:zxjc_aeolianvibration");
list.add("mcsas:zxjc_airmoisture");
list.add("mcsas:zxjc_airpresure");
list.add("mcsas:zxjc_brakecable");
exportExcel(list);
}
}
二、HBase取数据主要代码(getTopSomeRecords)
/**
* 获取指定位置向下N条记录
*/
public List<Result> getTopSomeRecords(String tableName, String startRow,
Integer count) throws HBaseException {
Connection connection = null;
Table hTable = null;
try {
connection = ConnectionFactory.createConnection(conf);
hTable = connection.getTable(TableName.valueOf(tableName));
Scan scan = new Scan();
List<Result> results = new ArrayList<Result>();
scan.setCacheBlocks(true);
scan.setCaching(10000);
scan.setStartRow(Bytes.toBytes(startRow));
PageFilter filter = new PageFilter(count);
scan.setFilter(filter);
ResultScanner scanner = hTable.getScanner(scan);
for (Result r : scanner) {
results.add(r);
}
scanner.close();
return results;
} catch (IOException e) {
e.printStackTrace();
logger.error("获取数据出错: ", e);
throw new HBaseException(HBaseException.UNKNOW_ERROR, "获取数据出错: ", e);
} finally {
closeHTable(hTable);
closeConn(connection);
}
}
三、备注
1、本文中一个表对应一个sheet,最终只有一个Excel文件,里面含有多个sheet
2、本文只是取部分数据,将数据先存入List中,如果数据量太大,会导致内存溢出,另外Excel也不可能存放太多的数据,建议只取部分数据