Export HBase data to Excel (Java code)

First, the main code

 PackageCom.sgcc.mcsas.bigdata.tool;ImportCom.sgcc.mcsas.bigdata.service.HBaseServiceImpl;ImportCom.sgcc.mcsas.bigdata.service.IHBaseService;ImportOrg.apache.hadoop.hbase.Cell;ImportOrg.apache.hadoop.hbase.client.Result;ImportOrg.apache.poi.hssf.usermodel.HSSFCell;ImportOrg.apache.poi.hssf.usermodel.HSSFRow;ImportOrg.apache.poi.hssf.usermodel.HSSFSheet;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook;ImportJava.io.File;ImportJava.io.FileOutputStream;Importjava.util.ArrayList;Importjava.util.List;/*** Created by dwxx-120 on 2016/7/12.*/ Public classExport {Private StaticIhbaseservice Service =NewHbaseserviceimpl (); Private Static Final intCount = 5;//number of records    Private Static FinalString fileName = "D:/hbase_export.xls";  Public Static voidExportexcel (list<string> tablenames)throwsException {//Create an Excel fileFile File =NewFile (fileName); if(!file.exists ())        {File.createnewfile (); }        //setting the output streamFileOutputStream fos =Newfileoutputstream (file); Hssfworkbook Book=NewHssfworkbook ();  for(intk = 0; K < Tablenames.size (); k++) {String tableName=Tablenames.get (k); //take part of the datalist<result> results = service.gettopsomerecords (TableName, "05M", Count); //Create sheetHssfsheet sheet =Book.createsheet (); Book.setsheetname (k, Tablename.replace (":", "_")); //return to NULL            if(results.size () = = 0) {System.out.println (TableName+ "has no data!"); Continue; }            //Generating a table headerHssfrow Header = Sheet.createrow (0); Hssfcell Header_rowkey= Header.createcell (0); //Rowkey table HeaderHeader_rowkey.setcellvalue ("Rowkey"); //Other table Headerslist<cell> Cells0 = results.get (0). Listcells ();  for(inti = 0; I < cells0.size (); i++) {Hssfcell Header_other= Header.createcell (i + 1); String name=NewString (Cells0.get (i). Getqualifier ());            Header_other.setcellvalue (name); }            //traversing the data of a query             for(inti = 0; I < results.size (); i++) {                //One result creates a rowHssfrow Data_row = Sheet.createrow (i + 1); Result R=Results.get (i); //set the value of the RowkeyString Rowkey =NewString (R.getrow ()); Hssfcell Data_rowkey= Data_row.createcell (0);                Data_rowkey.setcellvalue (Rowkey); //Set other valuesList<cell> celllist =R.listcells ();  for(intj = 0; J < Celllist.size (); J + +) {Hssfcell Data_other= Data_row.createcell (j + 1); Data_other.setcellvalue (NewString (Celllist.get (j). GetValue ())); }            }        }        //Writebook.write (FOS); }     Public Static voidMain (String args[])throwsException {//hbase Table namelist<string> list =NewArraylist<string>(); List.add ("Mcsas:zxjc_aeolianvibration"); List.add ("Mcsas:zxjc_airmoisture"); List.add ("Mcsas:zxjc_airpresure"); List.add ("Mcsas:zxjc_brakecable"); List.add ("Mcsas:zxjc_chargeelectricity"); List.add ("Mcsas:zxjc_electromotorstate"); List.add ("Mcsas:zxjc_gasinoil"); List.add ("Mcsas:zxjc_gispartdischarge"); List.add ("Mcsas:zxjc_icethickness"); List.add ("Mcsas:zxjc_ideoimage"); List.add ("Mcsas:zxjc_ironcoreelectricity"); List.add ("Mcsas:zxjc_lightningrod"); List.add ("Mcsas:zxjc_linegalloping"); List.add ("Mcsas:zxjc_linesag"); List.add ("Mcsas:zxjc_linetemperature"); List.add ("Mcsas:zxjc_oillevel"); List.add ("Mcsas:zxjc_partdischarge"); List.add ("Mcsas:zxjc_pollutiondegree"); List.add ("Mcsas:zxjc_spectrum"); List.add ("Mcsas:zxjc_substationweather"); List.add ("Mcsas:zxjc_tinywater"); List.add ("Mcsas:zxjc_topoiltemperature"); List.add ("Mcsas:zxjc_towerinclination"); List.add ("Mcsas:zxjc_weather"); List.add ("Mcsas:zxjc_windageyaw");    Exportexcel (list); }}

II. hbase Fetch Data main code (GETTOPSOMERECORDS)

/*** Gets the specified position down N records*/     PublicList<result>gettopsomerecords (String tableName, String startrow, Integer count)throwshbaseexception {Connection Connection=NULL; Table htable=NULL; Try{Connection=connectionfactory.createconnection (conf); Htable=connection.gettable (tablename.valueof (TableName)); Scan Scan=NewScan (); List<Result> results =NewArraylist<result>(); Scan.setcacheblocks (true); Scan.setcaching (10000);            Scan.setstartrow (Bytes.tobytes (StartRow)); Pagefilter Filter=NewPagefilter (count);            Scan.setfilter (filter); Resultscanner Scanner=Htable.getscanner (scan);  for(Result r:scanner) {Results.add (R);            } scanner.close (); returnresults; } Catch(IOException e) {e.printstacktrace (); Logger.error ("Error getting data:", E); Throw NewHbaseexception (hbaseexception.unknow_error, "Error getting data:", E); } finally{closehtable (htable);        Closeconn (connection); }    }

Third, remarks

1, a table in this article corresponds to a sheet, and eventually only one Excel file, which contains multiple sheet

2, this article just take part of the data, the data into the list first, if the amount of data is too large, will lead to memory overflow, and Excel can not store too much data, it is recommended to take only part of the data

Export HBase data to Excel (Java code)