Hbase import data through Excel

Package common; import Java. io. fileinputstream; import Java. io. filenotfoundexception; import Java. io. fileoutputstream; import Java. io. ioexception; import Java. util. arraylist; import Java. util. hashmap; import Java. util. list; import Java. util. map; import Org. apache. poi. hssf. usermodel. hssfworkbook; import Org. apache. poi. poifs. filesystem. poifsfilesystem; import Org. apache. poi. SS. usermodel. *;/***** @ author lFF * @ Version 0.5 Excel file operation help class **/public class excelpoihelper {// ddrive creates an empty workbook.xls file public static void create (string path, string name) {workbook WB = new hssfworkbook (); fileoutputstream fileout; try {fileout = new fileoutputstream ("D:/workbook.xls"); WB. write (fileout); fileout. close ();} catch (filenotfoundexception e) {e. printstacktrace ();} catch (ioexception e) {e. printstacktrace ();}} /*** Retrieve all the workbook names in Excel ** @ Param fullpath * full address of the Excel file ("D:/workbook.xls ") * @ return workbook name list */public static list <string> getsheets (string fullpath) {list <string> result = new arraylist <string> (); try {fileinputstream file = new fileinputstream (fullpath); poifsfilesystem Ts = new poifsfilesystem (File); workbook = new hssfworkbook (TS); For (INT I = 0; I <workbook. getnumberofsheets (); I ++) {String sheetname = workbook. getsheetname (I); result. add (I, sheetname);} file. close ();} catch (filenotfoundexception e) {e. printstacktrace ();} catch (ioexception e) {e. printstacktrace ();} return result;}/*** obtain the full address of all rows in the workbook ** @ Param fullpath * Excel file ("D:/workbook.xls ") * @ Param sheetname * workbook name * @ return key-Value Pair: <rowkey, <columnname, value> */public static Map <string, list <Map <string, STR Ing >>> getrows (string fullpath, string sheetname) {Map <string, list <Map <string, string >>> resultrow = new hashmap <string, list <Map <string, string >>>(); List <Map <string, string> resultcells; Map <string, string> resultcell; try {fileinputstream file = new fileinputstream (fullpath ); poifsfilesystem Ts = new poifsfilesystem (File); workbook = new hssfworkbook (TS); sheet = workbook. ge Tsheet (sheetname); int rowcounts = sheet. getphysicalnumberofrows (); // The number of rows int columncounts = sheet. getrow (0 ). getphysicalnumberofcells (); // Number of columns for (INT I = 1; I <rowcounts; I ++) {row = sheet. getrow (I); // returns the row of each row after the first row. getcell (0 ). setcelltype (cell. cell_type_string); resultcells = new arraylist <Map <string, string> (); resultcell = new hashmap <string, string> (); string rowkey = row. getcell (0 ). Tostring (); For (Int J = 1; j <columncounts; j ++) {Cell cell = row. getcell (j); // cyclically retrieve each column after the first column if (null! = Cell) {Cell. setcelltype (cell. cell_type_string); string columnname = sheet. getrow (0 ). getcell (j ). tostring (); string cellvalue = cell. tostring (); resultcell. put (columnname, cellvalue) ;}} resultcells. add (resultcell); resultrow. put (rowkey, resultcells);} file. close ();} catch (filenotfoundexception e) {e. printstacktrace ();} catch (ioexception e) {e. printstacktrace ();} return resultrow ;}}

Retrieval Method

Public static void main (string [] AGRs) {string fullpath = "C:/users/lFF/desktop/hbase .xls"; // temp = excelpoihelper. getsheets (fullpath); // obtain the names of all workbooks. String sheetname = "user-Info"; string tbname = sheetname. split ("-") [0]; string tbcf = sheetname. split ("-") [1]; Map <string, list <Map <string, string> rows = new hashmap <string, list <Map <string, string >>> (); rows = excelpoihelper. getrows (fullpath, sheetname); iterator rowiterator = rows. entryset (). iterator (); While (rowiterator. hasnext () {entry rowentry = (entry) rowiterator. next (); object rowkey = rowentry. getkey (); object cellsvalue = rowentry. getvalue (); // system. out. println (rowkey); // system. out. println (cellsvalue); List <Map <string, string> cells = new arraylist <Map <string, string> (); cells = (list <Map <string, string>) cellsvalue; iterator <Map <string, string> it = cells. iterator (); While (it. hasnext () {object cellobject = it. next (); Map <string, string> cell = new hashmap <string, string> (); cell = (hashmap <string, string>) cellobject; iterator celliterator = cell. entryset (). iterator (); While (celliterator. hasnext () {entry cellentry = (entry) celliterator. next (); object cellcolumn = cellentry. getkey (); object value = cellentry. getvalue (); string strcellcolumn = cellcolumn. tostring (); string columnname = strcellcolumn. split ("<") [0]; string valuetype = strcellcolumn. substring (strcellcolumn. indexof ("<") + 1, strcellcolumn. lastindexof (">"); system. out. println (string. format ("the value in the [% s] column of the column family [% s] stored in the table [% s] Where the row key is [% s] is [% s] (type: [% s]) ", tbname, tbcf, rowkey, columnname, value, valuetype); add (tbname, tbcf, rowkey. tostring (), columnname, value. tostring ());}}}

To add the poi-3.10.1-20140818.jar package, download it at http://poi.apache.org/download.html.

Hbase import data through Excel