Java Operations Excel (POI)

The Excel data is imported into the database because it is used in the project. Share it here.

The POI method used here supports two formats (XLS,XLSX)

Package Com.entity;import Java.io.file;import Java.io.fileinputstream;import java.io.ioexception;import Java.io.inputstream;import Java.util.arraylist;import Java.util.hashmap;import Java.util.Iterator;import Java.util.list;import Java.util.map;import Org.apache.poi.hssf.usermodel.hssfcell;import Org.apache.poi.hssf.usermodel.hssfworkbook;import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 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 Org.apache.poi.ss.util.cellrangeaddress;import            org.apache.poi.xssf.usermodel.xssfworkbook;/** * Excel Public method */public final class Excelutil {private Excelutil () {        }//Read result set of Excel private static arraylist<map<string, string>> result = null;        Record the number of rows in the empty row of a table private static int num = 0; private static list<string> numList = new arraylist<string> (); /** * Get data that needs to pass in the database */public static void Readexceldata (final String filePath) {try {Readexc        Eltoobj (FilePath);        } catch (Exception e) {System.out.println ("failed to import"); }}/** * Read Excel data */private static arraylist<map<string, string>> Readexceltoobj (FINA        L String Path) throws Exception {Workbook wb = null;        result = new arraylist<map<string, string>> ();            try {wb = workbookfactory.create (new File);            Sheet Sheet = wb.getsheetat (0);        result = Readexcel (WB, sheet, 0, 0);        } catch (Invalidformatexception e) {e.printstacktrace ();        } catch (IOException e) {e.printstacktrace ();    } return result; /** * Read Excel file * @param sheet Sheet page subscript: Starting from 0 * @param startreadline start reading line: Starting from 0 * @param tailline removing the most The line after read */private static ArraYlist<map<string, string>> readexcel (Final Workbook WB, Final Sheet Sheet, final int startreadline, final int        Tailline) {row row = null;            for (int i = startreadline; I < Sheet.getlastrownum ()-tailline + 1; i++) {row = Sheet.getrow (i);            map<string, string> map = new hashmap<string, string> ();                for (Cell c:row) {String returnstr = "";                Boolean ismerge = ismergedregion (sheet, I, C.getcolumnindex ()); Determines whether there is a merged cell if (ismerge) {String rs = getmergedregionvalue (sheet, Row.getrownum (), C.                    Getcolumnindex ());                Returnstr = RS;                } else {returnstr = C.getrichstringcellvalue (). getString ();                } if (c.getcolumnindex () = = 0) {map.put ("id", RETURNSTR); } else if (c.getcolumnindex () = = 1) {map.put ("RaIlwaytripsin ", RETURNSTR);                } else if (c.getcolumnindex () = = 2) {map.put ("Arrivetime", returnstr);                } else if (c.getcolumnindex () = = 3) {map.put ("Railwaynum", returnstr);                } else if (c.getcolumnindex () = = 4) {map.put ("Checktrack", returnstr);                } else if (c.getcolumnindex () = = 5) {map.put ("Maintenancetask", returnstr);                } else if (c.getcolumnindex () = = 6) {map.put ("Maintenanceunitname", returnstr);                } else if (c.getcolumnindex () = = 7) {map.put ("project", RETURNSTR);                } else if (c.getcolumnindex () = = 8) {map.put ("StartTime", returnstr);                } else if (c.getcolumnindex () = = 9) {map.put ("Finishtime", returnstr);          } else if (c.getcolumnindex () = = Ten) {map.put ("Linktrack", returnstr);      } else if (c.getcolumnindex () = = one) {map.put ("Outboundtime", returnstr);                } else if (c.getcolumnindex () = =) {Map.put ("Railwaytripsout", returnstr);        }} result.add (map);    } return result; } public static list<trackdata> Splitcenterdata (arraylist<map<string,string>> result) throws        Exception {tracklistdata = new arraylist<trackdata> ();        map<string,string> map = new hashmap<string,string> ();        Integer linenumdatasize = Result.size ()-5;            for (int i = 0; i < linenumdatasize; i++) {trackData = new trackData ();            Map = Result.get (i);            Trackdata.setid (Map.get ("id"). Trim ());            Trackdata.setintimes (Map.get ("Intimes"). Trim ());            if (Map.get ("InTime"). ToString (). Trim (). Equals ("\ \")) {trackdata.setintime (null); } else {TrackdatA.setintime (Map.get ("InTime"));            } Trackdata.setcargroud (Map.get ("Cargroud"). Trim ());            Trackdata.setchecktrack (Map.get ("Checktrack"). Trim ());            Trackdata.setjob (Map.get ("job"). Trim ());            Trackdata.setnewssource (Map.get ("NewsSource"). Trim ());            Trackdata.setproject (Map.get ("project"). Trim ());            Trackdata.setstarttime (Map.get ("StartTime"). Trim ());            Trackdata.setendtime (Map.get ("EndTime"). Trim ());            if (Map.get ("Linktrack"). ToString (). Trim (). Equals ("\ \")) {trackdata.setlinktrack (null);            } else {Trackdata.setlinktrack (Map.get ("Linktrack"). Trim ());            } if (Map.get ("Outtime"). ToString (). Trim (). Equals ("\ \")) {trackdata.setouttime (null);            } else {trackdata.setouttime (Map.get ("Outtime"). Trim ()); } if (Map.get ("Outtimes"). ToString (). Trim (). Equals ("\ \")) {Trackdata.setouttimes (null);            } else {trackdata.setouttimes (Map.get ("Outtimes"). Trim ());        } tracklistdata.add (TrackData);    } return tracklistdata; /** * Gets the value of the merged cell * @param sheet * @param row * @param column */private static String G Etmergedregionvalue (Final Sheet Sheet, final int row, final int column) {int sheetmergecount = Sheet.getnummergedr        Egions ();            for (int i = 0; i < Sheetmergecount; i++) {cellrangeaddress CA = sheet.getmergedregion (i);            int firstcolumn = Ca.getfirstcolumn ();            int lastcolumn = Ca.getlastcolumn ();            int firstrow = Ca.getfirstrow ();            int lastrow = Ca.getlastrow ();  if (row >= firstrow && row <= lastrow) {if (column >= firstcolumn && column <=                    Lastcolumn) {Row frow = Sheet.getrow (firstrow); Cell Fcell = Frow.getcell (firstcolumn);                Return Getcellvalue (Fcell);    }}} return null; }/** * Determined that the row was merged * @param sheet * @param row * @param column */private static Boolean Ismergedrow (Final Sheet Sheet, final int row, final int column)        {int sheetmergecount = sheet.getnummergedregions ();            for (int i = 0; i < Sheetmergecount; i++) {cellrangeaddress range = sheet.getmergedregion (i);            int firstcolumn = Range.getfirstcolumn ();            int lastcolumn = Range.getlastcolumn ();            int firstrow = Range.getfirstrow ();            int lastrow = Range.getlastrow (); if (row = = FirstRow && row = = lastrow) {if (column >= firstcolumn && column <= LASTC                Olumn) {return true;    }}} return false;   /** * Determines whether the specified cell is a merged cell * @param sheet * @param row row subscript * @param column subscript */ private static Boolean ismergedregion (Final Sheet Sheet, final int row, final int column) {int sheetmergecount =        Sheet.getnummergedregions ();            for (int i = 0; i < Sheetmergecount; i++) {cellrangeaddress range = sheet.getmergedregion (i);            int firstcolumn = Range.getfirstcolumn ();            int lastcolumn = Range.getlastcolumn ();            int firstrow = Range.getfirstrow ();            int lastrow = Range.getlastrow ();  if (row >= firstrow && row <= lastrow) {if (column >= firstcolumn && column <=                Lastcolumn) {return true;    }}} return false;        /** * Determine if the sheet page contains merged cells * @param sheet */private static Boolean hasmerged (final sheet sheet) { return sheet.getnummergedregions () > 0?    True:false; /** * Merge Cell * @param sheet * @param firstrow start line * @param lastrow End line * @param firstCol Start column * @param lastcol end column */private static void Mergeregion (Final Sheet Sheet, final int firstrow, final in T lastrow, Final int firstcol, final int lastcol) {sheet.addmergedregion (new cellrangeaddress (FirstRow, lastrow, F    Irstcol, lastcol));        }/** * Gets the value of the cell * @param cell * @return */private static String Getcellvalue (final cell cell) {        if (cell = = null) {return "";        } if (cell.getcelltype () = = cell.cell_type_string) {return cell.getstringcellvalue ();        } else if (cell.getcelltype () = = Cell.cell_type_boolean) {return string.valueof (Cell.getbooleancellvalue ());        } else if (cell.getcelltype () = = Cell.cell_type_formula) {return Cell.getcellformula ();        } else if (cell.getcelltype () = = Cell.cell_type_numeric) {return string.valueof (Cell.getnumericcellvalue ());    } return ""; }/** * Judging behavior null linenum is the beginning of a blank line */privateStatic Integer checkrownull (final Sheet Sheet, final int rows) {row row = null;            for (int i = 0; i < rows; i++) {int cols = Sheet.getrow (i). Getphysicalnumberofcells ();            int col = 0;            row = Sheet.getrow (i);                for (Cell c:row) {String returnstr = "";                Boolean ismerge = ismergedregion (sheet, I, C.getcolumnindex ()); Determines if there is a merged cell if (ismerge) {String rs = getmergedregionvalue (sheet, Row.getrownum (), C                    . Getcolumnindex ());                Returnstr = RS;                } else {returnstr = C.getrichstringcellvalue (). getString ();                } if (Returnstr.trim () = = NULL | | Returnstr.trim () = = "") {col++;            }} if (cols = = col) {num++;    }} return num; /** * Read content from Excel */private static void ReadconTent (Final String fileName) {Boolean isE2007 = false;        Determines whether the excel2007 format if (Filename.endswith ("xlsx")) {isE2007 = true;  try {inputstream input = new FileInputStream (fileName);            Establishing the input stream Workbook WB = null;            Initializes the IF (isE2007) {wb = new Xssfworkbook (input) According to the file format (2003 or 2007);            } else {wb = new Hssfworkbook (input);     } Sheet Sheet = Wb.getsheetat (0); Get the first form iterator<row> rows = Sheet.rowiterator ();  Gets the iterator for the first form while (Rows.hasnext ()) {Row row = Rows.next ();  Get Row data System.out.println ("Row #" + Row.getrownum ());    Get line number starting from 0 iterator<cell> cells = row.celliterator ();                    Gets the first line of the iterator while (Cells.hasnext ()) {Cell cell = Cells.next ();               System.out.println ("Cell #" + Cell.getcolumnindex ());     Switch (Cell.getcelltype ()) {///according to the type in the cell to output the data case Hssfcell.cell_type_numeric:                            System.out.println (Cell.getnumericcellvalue ());                        Break                            Case HSSFCell.CELL_TYPE_STRING:System.out.println (Cell.getstringcellvalue ());                        Break                            Case HSSFCell.CELL_TYPE_BOOLEAN:System.out.println (Cell.getbooleancellvalue ());                        Break                            Case HSSFCell.CELL_TYPE_FORMULA:System.out.println (Cell.getcellformula ());                        Break                            DEFAULT:SYSTEM.OUT.PRINTLN ("unsuported sell type=======" + cell.getcelltype ());                    Break        }}}} catch (IOException ex) {ex.printstacktrace (); }   }} 

　　

Java Operations Excel (POI)