? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?Java結(jié)合POI清洗Excel

下文是Java結(jié)合POI清洗Excel的示例代碼，詳細(xì)內(nèi)容如下：

ShipEntry.java

package com.liang.bi.excelmodel;public class ShipEntry {private String sheetid; //excel里sheet的編號 private String compname;//公司名 private String boatname; //船名 private String boatcode; //船編碼 private String contactport;//來往港口 private String arrivedate; //到達(dá)時間 private String leavedate; //離開時間 private String cityname; //所在城市,同excel里的VOYAGE CODE private String iris_2; //港口編碼public String getCompname() {return compname; } public void setCompname(String compname) {this.compname = compname; } public String getSheetid() {return sheetid; } public void setSheetid(String sheetid) {this.sheetid = sheetid; }public String getBoatname() {return boatname; } public void setBoatname(String boatname) {this.boatname = boatname; } public String getBoatcode() {return boatcode; } public void setBoatcode(String boatcode) {this.boatcode = boatcode; } public String getContactport() {return contactport; } public void setContactport(String contactport) {this.contactport = contactport; } public String getArrivedate() {return arrivedate; } public void setArrivedate(String arrivedate) {this.arrivedate = arrivedate; } public String getLeavedate() {return leavedate; } public void setLeavedate(String leavedate) {this.leavedate = leavedate; } public String getCityname() {return cityname; } public void setCityname(String cityname) {this.cityname = cityname; } public String getIris_2() {return iris_2; } public void setIris_2(String iris_2) {this.iris_2 = iris_2; }@Override public String toString() {return "ShipEntry [sheetid=" + sheetid + ", compname=" + compname+ ", boatname=" + boatname + ", boatcode=" + boatcode+ ", contactport=" + contactport + ", arrivedate=" + arrivedate+ ", leavedate=" + leavedate + ", cityname=" + cityname+ ", iris_2=" + iris_2 + "]"; }}

ShipTitleData.java

package com.liang.bi.excelmodel;import java.util.HashMap; import java.util.List;public class ShipTitleData { private String addr; private String to; //TO private String from; //FM private String shipDate; //DD private String re; //RE private String title; private String vessl; //船名 private String vessllcode; //船名編碼 private HashMap<String,List> voyagecode; //經(jīng)過的港口 private HashMap<String,List> iris; //經(jīng)過的港口編碼public void ShipTitleData(String addr,String to,String from,String shipDate,String re,String title,String vessl,String vessllcode,List voyagecode,List iris){this.addr= addr;this.from=from;this.re = re;this.shipDate = shipDate;this.title = title;this.to = to;this.vessl = vessl;this.vessllcode = vessllcode;this.voyagecode = (HashMap<String, List>) voyagecode;this.iris = (HashMap<String, List>) iris; }public String getAddr() {return addr; }public void setAddr(String addr) {this.addr = addr; }public String getTo() {return to; }public void setTo(String to) {this.to = to; }public String getFrom() {return from; }public void setFrom(String from) {this.from = from; }public String getShipDate() {return shipDate; }public void setShipDate(String shipDate) {this.shipDate = shipDate; }public String getRe() {return re; }public void setRe(String re) {this.re = re; }public String getTitle() {return title; }public void setTitle(String title) {this.title = title; }public String getVessl() {return vessl; }public void setVessl(String vessl) {this.vessl = vessl; }public String getVessllcode() {return vessllcode; }public void setVessllcode(String vessllcode) {this.vessllcode = vessllcode; }public HashMap<String, List> getVoyagecode() {return voyagecode; }public void setVoyagecode(String port,List voyagecode) {this.voyagecode = (HashMap<String, List>) voyagecode; }public HashMap<String, List> getIris() {return iris; }public void setIris(HashMap iris) {this.iris = iris; }public String toString(){return "船表表頭數(shù)據(jù)見下:"+this.getAddr()+"\n~~~"+this.to+"\n~~~"+this.getFrom()+"\n~~~"+this.getShipDate()+"\n~~~"+this.getRe()+"\n~~~"+this.getTitle()+"\n~~~"+this.getVessl()+"\n~~~"+this.getVessllcode()+"\n~~~"+this.getVoyagecode()+"\n~~~"+this.getIris(); }public void setVoyagecode(HashMap voyagecode) {// TODO Auto-generated method stubthis.voyagecode = voyagecode; } }

Excelparse.java

package com.liang.bi.excelparse;import java.io.FileInputStream; import java.io.IOException; import java.sql.DriverManager; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; 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.xssf.usermodel.XSSFWorkbook; import com.liang.bi.excelmodel.ShipEntry; import com.liang.bi.excelmodel.ShipTitleData; import com.mysql.jdbc.Connection; import com.mysql.jdbc.PreparedStatement;public class Excelparse {HashMap<Integer,List> title = new HashMap<Integer,List>();//僅表頭的list屬性HashMap<Integer,List> data = new HashMap<Integer,List>(); //僅數(shù)據(jù)的list屬性List <ShipEntry> finaltotalentrys = new ArrayList<ShipEntry>(); //入數(shù)據(jù)庫的list定義/** Step1: 解析excel,分別將表頭賦值給title屬性,船進(jìn)出港數(shù)據(jù)賦值給data屬性,總數(shù)據(jù)(表頭和船進(jìn)出港數(shù)據(jù))作為函數(shù)值返回**/public HashMap readExcelData(String url)throws Exception{ // 從XLSX/ xls文件創(chuàng)建的輸入流 FileInputStream fis = new FileInputStream(url); int intstart=100; //Excel里船進(jìn)出港起始位置標(biāo)識HashMap<Integer,List> total = new HashMap<Integer,List>(); // 創(chuàng)建工作薄Workbook Workbook workBook = null; // 讀取2007版，以 .xlsx 結(jié)尾 if(url.toLowerCase().endsWith("xlsx")){ try { workBook = new XSSFWorkbook(fis); } catch (IOException e) { e.printStackTrace(); } } // 讀取2003版，以 .xls 結(jié)尾 else if(url.toLowerCase().endsWith("xls")){ try { workBook = new HSSFWorkbook(fis); } catch (IOException e) { e.printStackTrace(); } } //獲得xlsx文件里的sheet總數(shù) int numberOfSheets = workBook.getNumberOfSheets(); // 循環(huán) numberOfSheets for(int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++){ // 得到 工作薄 的第 N個表 Sheet sheet = workBook.getSheetAt(sheetNum);List <ShipEntry> shipentrys = new ArrayList<ShipEntry>();//接收船體屬性的對象,包含boatname; //船名 boatcode; //船編碼 contactport;//來往港口 arrivedate; //到達(dá)時間 leavedate; //離開時間 cityname;等List titleList = new ArrayList(); //Excel中表頭數(shù)據(jù)(不包含船的進(jìn)出港數(shù)據(jù))Row row;String cell; List totalDataList = new ArrayList();//Excel中表頭和船進(jìn)出港數(shù)據(jù)(即總數(shù)據(jù),此list是該方法的返回值僅做調(diào)試)for(int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++){ row = sheet.getRow(i);List dataList = new ArrayList();//Excel中船進(jìn)出港數(shù)據(jù)for(int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++){ cell = row.getCell(j).toString();//以sheet里的VESSEL為關(guān)鍵字,如果有,則認(rèn)定隔2行的是時間行(這里只是適應(yīng)IC23的模板,即可VESSEL占2行的情況)if("VESSEL".equals(cell)){intstart = i+2;}if(!"".equals(cell) && i<=intstart-1){totalDataList.add(cell);titleList.add(cell);}if(i>intstart-1){//如果Excle的cell的格式是公式或則數(shù)值的,需要進(jìn)行轉(zhuǎn)換,反之得到的不是日期數(shù)據(jù) 0是數(shù)值 1是字符串 2是公式 3是空值 4是布爾類型 5是錯誤if(row.getCell(j).getCellType() == 2 || row.getCell(j).getCellType() == 0){double celldouble = Double.valueOf(row.getCell(j).getNumericCellValue());cell = new SimpleDateFormat("yyyy-MM-dd").format(HSSFDateUtil.getJavaDate(celldouble));totalDataList.add(cell);dataList.add(cell);}else {totalDataList.add(cell); dataList.add(cell);}}} //針對每個sheet將dataList數(shù)據(jù)賦值到ShipEntry對象并添加到shipentrys中,這里其實(shí)的列從3開始,這里每隔2個對Arrivedate和Leavedate進(jìn)行賦值if(dataList.size() > 0){for(int datanum = 3;datanum<dataList.size() ;datanum+=2){ShipEntry shipentrytemp = new ShipEntry();shipentrytemp.setBoatname((String)dataList.get(0)); shipentrytemp.setBoatcode((String)dataList.get(1));shipentrytemp.setContactport((String)dataList.get(2));shipentrytemp.setArrivedate((String)dataList.get(datanum));shipentrytemp.setLeavedate((String)dataList.get(datanum+1));shipentrys.add(shipentrytemp);this.data.put(sheetNum, shipentrys);//賦值給對象的data屬性}}} this.title.put(sheetNum, titleList);//賦值給對象的title屬性total.put(sheetNum, totalDataList);//賦值給total作為函數(shù)的返回值} //Step2 解析Excel表頭里的數(shù)據(jù)[Todo 可在這里重構(gòu),即可將取到的title放到List<ShipTitleData>中]/* ArrayList ETLlist = new ArrayList();for(int i=0;i<title.size();i++){if ( title.get(i).size() > 0){ //過濾掉空的Excel數(shù)據(jù)System.out.println("xl.titile inner的數(shù)據(jù):"+i+title.get(i).get(0));} }*/ return total; } public static void main(String[] args)throws Exception { Excelparse xl = new Excelparse();HashMap list = xl.readExcelData(args[0]); System.out.println("xl.titile size:"+xl.title.size());System.out.println("xl.titile values:"+xl.title);System.out.println("data in list"+xl.data); //Step2: 將解析到的excel表頭數(shù)據(jù)賦值到List<ShipTitleData>中,voyagecode和iris是HashMap結(jié)構(gòu)System.out.println("********************解析中:********************"); List<ShipTitleData> sds = new ArrayList<ShipTitleData>();ArrayList ETLlist = new ArrayList();//臨時處理的listIterator iter = xl.title.keySet().iterator();for(int sheetnum=0;sheetnum<xl.title.size();sheetnum++){ShipTitleData SD= new ShipTitleData();if(!(list.get(sheetnum).toString() == "[]")){int j = 0;ETLlist = (ArrayList) xl.title.get(sheetnum);HashMap<Integer,String> hm = new HashMap<Integer,String>();HashMap<Integer,String> hmtemp = new HashMap<Integer,String>();for(int k = 0; k < ETLlist.size(); k++) { SD.setAddr((String)ETLlist.get(0));SD.setTo((String)ETLlist.get(1));SD.setFrom((String)ETLlist.get(2)); if(ETLlist.get(3).toString().length() <6) //如果含"DD："即字符串長度小于6即可DD和時間分兩列時{SD.setShipDate((String)ETLlist.get(3)+(String)ETLlist.get(4));SD.setRe((String)ETLlist.get(5));SD.setTitle((String)ETLlist.get(6));SD.setVessl((String)ETLlist.get(7));SD.setVessllcode((String)ETLlist.get(8)); List city = new ArrayList();int u=10;for(int z = 10;z<(ETLlist.size() -1 - 9)/2+10;z++){city.add((String)ETLlist.get(z)); u = z;}HashMap hm1 = new HashMap<String,ArrayList>();hm1.put("VOYAGECODE", city);SD.setVoyagecode(hm1);List ports = new ArrayList();for(int u1=u+2;u1<ETLlist.size();u1++){ports.add((String)ETLlist.get(u1));}HashMap hm2 = new HashMap<String,ArrayList>();hm2.put((String)ETLlist.get(u+1), ports);SD.setIris(hm2);}else{SD.setShipDate((String)ETLlist.get(3));SD.setRe((String)ETLlist.get(4));SD.setTitle((String)ETLlist.get(5));SD.setVessl((String)ETLlist.get(6));SD.setVessllcode((String)ETLlist.get(7));List city = new ArrayList();int u=9;for(int z = 9;z<(ETLlist.size() -1 - 9)/2+9;z++){city.add((String)ETLlist.get(z)); u = z;}HashMap hm1 = new HashMap<String,ArrayList>();hm1.put("VOYAGECODE", city);SD.setVoyagecode(hm1);List ports = new ArrayList();for(int u1=u+2;u1<ETLlist.size();u1++){ports.add((String)ETLlist.get(u1));}HashMap hm2 = new HashMap<String,ArrayList>();hm2.put((String)ETLlist.get(u+1), ports);SD.setIris(hm2);}}sds.add(SD);}}//Step3: 結(jié)合List<ShipTitleData>將數(shù)據(jù)賦值到List<ShipEntry>中,主要針對sheetid,cityname,Compname,Iris_2進(jìn)行賦值for(int d=0;d<xl.data.size();d++){int citysize=0;citysize = sds.get(d).getVoyagecode().get("VOYAGECODE").size();List<ShipEntry> innerShipEntry = new ArrayList<ShipEntry>();innerShipEntry = xl.data.get(d);//如果key對應(yīng)的value(arraylist為null則終止循環(huán))if(xl.data.get(d).size()==0){break;}int innerShipEntrysize = xl.data.get(d).size();for(int inner=0;inner<innerShipEntrysize;inner++){if(inner%citysize >0 || inner==0){ innerShipEntry.get(inner).setSheetid(d+1+"");innerShipEntry.get(inner).setCityname((java.lang.String) sds.get(d).getVoyagecode().get("VOYAGECODE").get(inner%citysize>0 ?inner%citysize:0));innerShipEntry.get(inner).setIris_2((java.lang.String) sds.get(d).getIris().get("IRIS-2").get(inner%citysize>0 ?inner%citysize:0));innerShipEntry.get(inner).setCompname(sds.get(d).getFrom());}else{innerShipEntry.get(inner).setSheetid(d+1+"");innerShipEntry.get(inner).setCityname((java.lang.String) sds.get(d).getVoyagecode().get("VOYAGECODE").get(0));innerShipEntry.get(inner).setIris_2((java.lang.String) sds.get(d).getIris().get("IRIS-2").get(0));innerShipEntry.get(inner).setCompname(sds.get(d).getFrom());}} xl.finaltotalentrys.addAll(innerShipEntry);}//Step4: 結(jié)合JDBC將數(shù)據(jù)導(dǎo)入進(jìn)數(shù)據(jù)庫insertAll(xl.finaltotalentrys,getConn(args[1],args[2],args[3],args[4])); }private static Connection getConn(String ip,String db,String user,String passwd) {String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://"+ip+":3306"+"/"+db+"?useUnicode=true&amp;characterEncoding=utf-8";String username = user;String password = passwd; Connection conn = null;try {Class.forName(driver); //classLoader,加載對應(yīng)驅(qū)動conn = (Connection) DriverManager.getConnection(url, username, password);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}return conn;}private static void insertAll(List<ShipEntry> shipsdata,Connection conn) throws SQLException {int i = 0;String sql = "insert into boatentry (sheetid,compname,boatname,boatcode,contactport,arrivedate,leavedate,cityname,iris_2) values(?,?,?,?,?,?,?,?,?)";PreparedStatement pstmt=null; pstmt=(PreparedStatement) conn.prepareStatement(" truncate table boatentry ");pstmt.execute();if(shipsdata.size() >0){try { for(ShipEntry s :shipsdata){pstmt = (PreparedStatement) conn.prepareStatement(sql);pstmt.setString(1, s.getSheetid());pstmt.setString(2, s.getCompname().replace("FM：", ""));pstmt.setString(3, s.getBoatname());pstmt.setString(4, s.getBoatcode());pstmt.setString(5, s.getContactport());pstmt.setString(6, s.getArrivedate());pstmt.setString(7, s.getLeavedate());pstmt.setString(8, s.getCityname());pstmt.setString(9, s.getIris_2());pstmt.execute();}pstmt=(PreparedStatement) conn.prepareStatement("delete from boatentry where boatcode = " + "''");pstmt.execute(); pstmt.close();conn.close();} catch (SQLException e) {e.printStackTrace();}}}private static void deleteAll(Connection conn) {int i = 0;String sql = "delete from boatentry where boatcode = " + "''";PreparedStatement pstmt=null; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); pstmt.execute(); pstmt.close();conn.close();} catch (SQLException e) { e.printStackTrace();}}private static String String(Object object) { return null;} }

解析后數(shù)據(jù)直接入數(shù)據(jù)庫，示例excel源文件見下：

總結(jié)

以上是生活随笔為你收集整理的Java结合POI清洗Excel的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。