首页 > 编程语言 > 详细

java 读写 xlsx

时间:2018-04-20 15:47:28      阅读:209      评论:0      收藏:0      [点我收藏+]

---恢复内容开始---

所需要依赖的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;
	}
	@Override
	public 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里面  形式为accountId
		public 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

原文:https://www.cnblogs.com/laowang12/p/8890773.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!