java 讀寫 xlsx

標籤：mon   失敗   contain   highlight   bre   SQ   cal   nal   led   

---恢複內容開始---

所需要依賴的jar包<dependencies>        <dependency>            <groupId>junit</groupId>            <artifactId>junit</artifactId>            <version>3.8.1</version>            <scope>test</scope>        </dependency>        <!-- for ftp -->        <dependency>            <groupId>commons-net</groupId>            <artifactId>commons-net</artifactId>            <version>3.6</version>        </dependency>        <dependency>            <groupId>log4j</groupId>            <artifactId>log4j</artifactId>            <version>1.2.12</version>        </dependency>        <!-- for 新浪抓取使用，但是和selenium需要的版本衝突 2.44selenium內建為4.3.4httplient --><!--         <dependency>            <groupId>org.apache.httpcomponents</groupId>            <artifactId>httpclient</artifactId>            <version>4.5.2</version>        </dependency> -->        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>fastjson</artifactId>            <version>1.2.4</version>        </dependency>        <dependency>            <groupId>net.sf.json-lib</groupId>            <artifactId>json-lib</artifactId>            <version>2.4</version>            <classifier>jdk15</classifier>        </dependency>    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>5.1.8</version>        </dependency>        <dependency>                <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>3.9</version>        </dependency>        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>3.9</version>        </dependency>        <dependency>              <groupId>org.seleniumhq.selenium</groupId>              <artifactId>selenium-java</artifactId>           <version>2.48.0</version>        </dependency>                <!-- for use selenium and poi -->         <dependency>            <groupId>xml-apis</groupId>            <artifactId>xml-apis</artifactId>            <version>1.4.01</version>        </dependency>       <!--Excal  -->         <dependency>                <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>3.9</version>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>3.9</version>        </dependency>        <dependency>            <groupId>dom4j</groupId>            <artifactId>dom4j</artifactId>            <version>1.6</version>        </dependency>                <dependency>            <groupId>net.sourceforge.jexcelapi</groupId>            <artifactId>jxl</artifactId>            <version>2.6.12</version>        </dependency>                 <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi</artifactId>      <version>3.14</version>  </dependency>  <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->  <!-- <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi-ooxml</artifactId>      <version>3.14</version>  </dependency> -->     </dependencies>

2.設定檔內容

wbZS=/home/lt/ZS1.xlsx
saveZS=/home/lt/saveZS.xlsx

3.讀取設定檔的工具類

 

package cn.iie.icm.xlsx;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.InputStreamReader;import java.io.OutputStream;import java.util.Properties;public class ServerConfig {            private static final String PROPERTY_PATH = "/server.properties";                     private Properties props = new Properties();            public ServerConfig(){                loadConfig();            }            public void loadConfig(){                 InputStream in = null;                    try{                    in = ServerConfig.class.getResourceAsStream(PROPERTY_PATH);//                    props.load(in);                    props.load(new InputStreamReader(in, "UTF-8"));   //for chinese                     }catch(Exception ex){                        ex.printStackTrace();                    }finally{                        if(in!=null){                            try{                                in.close();                            }catch(Exception ex){                                ex.printStackTrace();                            }                        }                    }            }            public void saveConfig(String adminid,String passwd){                OutputStream os = null;                try{                    String fileName = ServerConfig.class.getClassLoader().getResource(PROPERTY_PATH).getFile();                    os = new FileOutputStream(new File(fileName));                    props.clear();                    props.setProperty("username", adminid);                    props.setProperty("password", passwd);                    props.store(os, "");                    os.flush();                }catch(Exception ex){                    ex.printStackTrace();                }finally{                    if(os!=null){                        try {                            os.close();                        } catch (IOException e) {                            e.printStackTrace();                        }                    }                }            }            public String getAdminId(){                return this.props.getProperty("username");            }            public String getAdminPwd(){                return this.props.getProperty("password");            }            public Properties getProps(){                return props;            }                        public String getProp(String key){                return props.getProperty(key);            }                        public boolean containsKey(String key){                return props.containsKey(key);            }}

4.實體類

package cn.iie.icm.zhuanshi;import java.util.Arrays;public class User {private String xh;private String userId;private String userName;private String sourceSiteProduct;private String gkTimes;private String status;private String fensi;public String getXh() {return xh;}public void setXh(String xh) {this.xh = xh;}public String getUserId() {return userId;}public void setUserId(String userId) {this.userId = userId;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getSourceSiteProduct() {return sourceSiteProduct;}public void setSourceSiteProduct(String sourceSiteProduct) {this.sourceSiteProduct = sourceSiteProduct;}public String getGkTimes() {return gkTimes;}public void setGkTimes(String gkTimes) {this.gkTimes = gkTimes;}public String getStatus() {return status;}public void setStatus(String status) {this.status = status;}public String getFensi() {return fensi;}public void setFensi(String fensi) {this.fensi = fensi;}@Overridepublic String toString() {return "User [xh=" + xh + ", userId=" + userId + ", userName="+ userName + ", sourceSiteProduct=" + sourceSiteProduct+ ", gkTimes=" + gkTimes + ", status=" + status + ", fensi="+ fensi + "]";}}

　　

package cn.iie.icm.xlsx;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import org.apache.log4j.Logger;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import cn.iie.icm.zhuanshi.MyLevenshtein;import cn.iie.icm.zhuanshi.User;public class PaseXExcalZhuanshi {static Logger logger = Logger.getLogger(PaseXExcalZhuanshi.class);private static ServerConfig config = new ServerConfig();public static void main(String[] args) {String path =config.getProp("wbZS");//String path = "/home/lt/ZS.xlsx";logger.info("=============path:"+path); try {ArrayList<User> result =ReadExcelUtil.readExcel(path);logger.info("==========result:"+result.size());Map<String, User> maps=similarMap(result); ArrayList<User> resultInfo=new ArrayList<User>(); for (String key : maps.keySet()) {        System.out.println(key + " ：" + maps.get(key));        User info=maps.get(key);        String name=info.getUserId();                logger.info("====name:"+name);        resultInfo.add(info);                } if(resultInfo.size()>1){/*String jieguo=writeAddExcal(rs);logger.info("================jieguo:"+jieguo);*/WriteExcelxlxs w=new WriteExcelxlxs();    String ss=w.addWriteExcel(resultInfo);    logger.info("================ss:"+ss);}} catch (Exception e) {logger.info("==========讀取Excal失敗了");e.printStackTrace();}}  //將找到轉世帳號放到map裡面  形式為accountIdpublic static Map<String, User> similarMap(ArrayList<User> infos) {int group = 0;//Map<Integer, List<Info>> map = new LinkedHashMap<Integer, List<Info>>();//Map<Integer, Map> maps = new LinkedHashMap<Integer, Map>();Map<String, User> map = new LinkedHashMap<String, User>();try {logger.info("=======開始尋找轉世帳號！！！！");List<User> result = infos;logger.info("=======該集合的大小為：" + result.size());for (int i = 0; i < result.size(); i++) {int f = i;User targetUser = result.get(i);String targetuserName = targetUser.getUserName();String accountId=targetUser.getUserId();logger.info("====accountId:"+accountId+",targetuserName:" + targetuserName);/*targetUser.setGroup(group);System.out.println("group:"+targetUser.getGroup()+",name:"+targetUser.getAccountName());*/if (targetuserName == null || targetuserName == "null"|| targetuserName.equals(null)) {logger.info("=======================targetuserName為空白:"+ targetuserName);}for (int j = 0; j < result.size(); j++) {int k = ++i;logger.info("=======k:" + k);logger.info("======i:" + i);if (k < result.size()) {User bjuser = result.get(k);String userName = bjuser.getUserName();String bjaccountId=bjuser.getUserId();logger.info("=====bjaccountId："+bjaccountId+"，userName:" + userName);if (userName == null || userName.equals(null)|| userName.equals("")|| userName.length() == 0) {logger.info("====userName為空白:" + userName);} else {float bl = MyLevenshtein.levenshtein(targetuserName, userName);if (bl > 0.5) {map.put(accountId, targetUser);map.put(bjaccountId, bjuser);}}} else {i = f;logger.info("=====else裡的i：" + i);break;}}}} catch (Exception e) {                logger.error("=====錯誤記錄檔："+e.getMessage());e.printStackTrace();}return map;}    }

　　

---恢複內容結束---

java 讀寫 xlsx