读取Excel文件并把想要的数据写入Excel

虽然整体代码不是写很漂亮,但是在解耦方面个人还是比较满意的。
1.读:把Excel写入list;
2.写:把list数据解析并封装成自己想要的格式,然后写入本地文件;
3.调用:获取需要读的文件路径,指定保存路径;先调用读,再写。
根据以上思路,读写解耦,具体要解析成什么格式就看自己需要的数据了。在正常使用之前得先使用一些jar包,下载地址
由于太忙,不做详细解说了,直接上代码:

package com.globalization.tools;

import java.util.List;

public interface DocumentConversion {
	/**
	 * 
	 * @param folderName 指定生成的文件放在哪个文件夹下面
	 * @param datas
	 * @param offset 指定偏移多少列来获取指定的数据
	 */
	void excute(Nation nation, List<List<String>> datas);
}
package com.globalization.tools;

import com.sun.istack.internal.NotNull;

public interface XmlContent {
	boolean isInit();
	String getFolderName();
	String getSubFolderName();
	String getFileName();
	String getHeader();
	String getStartDocument();
	String getStartTag(String tagName);
	/**
	 * 正常使用必须先初始化 第一步
	 * @param folderName
	 * @param fileName
	 */
	void init(@NotNull String folderName, @NotNull String subFolderName, String fileName);
	/**
	 * 第二步
	 * @param tagName
	 * @param content
	 */
	void appendContent(String tagName, String content);
	String getEndTag();
	String getEndDocument();
	/**
	 * xml文件结束必须回调此方法 第三步
	 */
	void perform();
	/**
	 * 第四步,得到整个xml的内容
	 * @return
	 */
	String getXmlContentData();
}
package com.globalization.tools;

import java.io.BufferedWriter;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;

public class FileTools {

	/**
	 * java8提供的newBufferedWriter(),既可以创建文件,也可以通过设置参数来完成追加写
	 * @param filePath 文件的完整路径
	 * @param content 创建文件时需要写入的内容
	 */
	public static void WriteFileByBw(String filePath, String content) {
		Path path = Paths.get(filePath);
		try(BufferedWriter writer = Files.newBufferedWriter(path, StandardCharsets.UTF_8)) {
			writer.write(content);
			System.out.println("文件写入完成!filePath = " + filePath);
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("文件写入失败!filePath = " + filePath);
		}
	}
	
	/**
	 * java8提供的newBufferedWriter(),既可以创建文件,也可以通过设置参数来完成追加写
	 * @param filePath 文件的完整路径
	 * @param content 追加要写入的内容
	 */
	public static void appendFileByBw(String filePath, String content) {
		Path path = Paths.get(filePath);
		try(BufferedWriter writer = Files.newBufferedWriter(path, StandardCharsets.UTF_8, StandardOpenOption.APPEND)) {
			writer.append(content);
			System.out.println("文件追加完成!");
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("文件追加失败!");
		}
	}
	
//	不需要使用流,通过Files.write()直接传递路径内容以及格式。
	public static void WriteFileByFs(String filePath, String content) throws IOException {
        //JDK1.7开始的方法
        //不需要使用流 只需要通过Files的write传递文件的路径,内容以及格式
        Files.write(Paths.get(filePath), content.getBytes(StandardCharsets.UTF_8));
    }
	
//	不需要使用流,通过Files.write()直接传递路径内容以及格式。
	public static void appendFileByFs(String filePath, String content) throws IOException {
		//JDK1.7开始的方法
		//追加写模式
		Files.write(Paths.get(filePath), content.getBytes(StandardCharsets.UTF_8),StandardOpenOption.APPEND);
	}
	
//	java老版本中的一种写文件的方式,是通过println写入的,因此可以做到一行一行写入,在java10中增加了字符集参数。
	public static void writeFileByPw(String filePath, String content) throws FileNotFoundException, UnsupportedEncodingException {
        //jdk5 PrintWriter
//        String filename = "D:\\playground\\test\\newFile3.txt";
        try (PrintWriter printWriter = new PrintWriter(filePath,"utf-8")){
            //一行一行写
            printWriter.println(content);
        }
    }
	
//	java老版本中的一种写文件的方式,是通过println写入的,因此可以做到一行一行写入,在java10中增加了字符集参数。
	public static void appendFileByPw(String filePath, String content) throws FileNotFoundException, UnsupportedEncodingException {
		//jdk5 PrintWriter
//        String filename = "D:\\playground\\test\\newFile3.txt";
		try (PrintWriter printWriter = new PrintWriter(filePath,"utf-8")){
			//一行一行写
			printWriter.println(content);
		}
	}
	
//	管道流虽然原始,但因为是分步骤的,所以定制化比较高
	public static void testCreateFiles5(String filePath, String content) throws IOException {
//        String filename = "D:\\playground\\test\\newFile4.txt";
        try (FileOutputStream fos = new FileOutputStream(filePath);
             OutputStreamWriter osw = new OutputStreamWriter(fos);
             BufferedWriter bw = new BufferedWriter(osw)){
             bw.write(content);
             bw.flush();
        }
    }
}
package com.globalization.tools;

public class Nation {
	private final static String BASE_FOLDER = "E:\\files\\Documents/module/";
	private String nationFolder;
	/**
	 * 访问Excel文档的偏移量
	 */
	private int offset;
	
	public Nation(String nationFolder) {
		this.nationFolder = nationFolder;
	}

	public int getOffset() {
		return offset;
	}
	
	public void setOffset(int offset) {
		this.offset = offset;
	}
	
	public String getNationFolder() {
		return nationFolder;
	}

	public String getBaseFolder() {
		return BASE_FOLDER;
	}
}
package com.globalization.tools;

public class NationFactory {
	public final static String ENGLISH_FOLDER = "values-en-rEN/";
	public final static String POLASH_FOLDER = "values-pl-rPL/";
	
	public static Nation createNation(String nationFolder) {
		return new Nation(nationFolder);
	}

}
package com.globalization.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;

public class ReadLocalExcel {
	//总行数
//    private int totalRows = 0;
    //总列数
//    private int totalCells = 0;
    //错误信息
    private  String errorInfo;
	
//    public int getTotalRows() {
//        return totalRows;
//    }
//
//    public int getTotalCells() {
//        return totalCells;
//    }

    public String getErrorInfo() {
        return errorInfo;
    }

    //检查文件是否为excel或者为空
    private boolean validateExcel(String filePath){
        //检查文件格式
        if (filePath==null || !(UUtil.isExcel2003(filePath) || UUtil.isExcel2007(filePath))) {
            errorInfo = "不是excel格式";
            return false;
        }
        //检查文件是否存在
        File file = new File(filePath);
        if(file==null || !file.exists()){
            errorInfo = "文件不存在";
            return  false;
        }
        return  true;

    }


    public List<List<String>> read(String filePath){
        List<List<String>> dataList = new ArrayList<List<String>>();
        InputStream is = null;

        try {
            //验证文件
            if(!validateExcel(filePath)){
                System.out.println(errorInfo);
                return null;
            }

            //判断文件类型
            boolean isExcel2003 = true;
            if(UUtil.isExcel2007(filePath)){
                isExcel2003 = false;
            }

            //调用读取方法
            File file = new File(filePath);
//            file.delete();
            file.createNewFile();
            is = new FileInputStream(file);
            dataList = read(is, isExcel2003);
            is.close();

        }catch (Exception e ){
        	e.printStackTrace();
        }finally {
        	
        }
        return  dataList;
    }


    public List<List<String>> read(InputStream inputStream,boolean isExcel2003) throws  Exception{
        List<List<String>> dataLst = null;
//        Workbook wb = null;
//        if(isExcel2003){
//            wb = new HSSFWorkbook(inputStream);
//        }else {
//            wb = new XSSFWorkbook(inputStream);
        	wb = WorkbookFactory.create(inputStream);
//        }
        Workbook wb = WorkbookFactory.create(inputStream);
        dataLst =readWork(wb);
        return  dataLst;
    }

    public List<List<String>> readWork(Workbook wb){
    	List<List<String>> dataList = new ArrayList<List<String>>();
    	//得到第一个shell
    	Sheet sheet =wb.getSheetAt(0);
    	Iterator<Row> iterator = sheet.rowIterator();
    	//循环excel的行
    	while (iterator.hasNext()) {
			Row row = (Row) iterator.next();
			if(row == null){
    			continue;
    		}
    		List<String> rowList = new ArrayList<String>();
    		Iterator<Cell> cellIterator = row.cellIterator();
    		//循环excel的列
    		while (cellIterator.hasNext()) {
				Cell cell = (Cell) cellIterator.next();
				String cellValue = "";
    			if(null !=cell){
    				//判断数据类型
    				switch (cell.getCellType()){
    				case HSSFCell.CELL_TYPE_NUMERIC: //数字
    					cellValue = cell.getNumericCellValue()+"";
    					break;
    				case HSSFCell.CELL_TYPE_STRING:  //字符串
    					cellValue = cell.getStringCellValue();
    					break;
    				case  HSSFCell.CELL_TYPE_BOOLEAN: //布尔
    					cellValue = cell.getBooleanCellValue()+"";
    					break;
    				case  HSSFCell.CELL_TYPE_FORMULA: //公式
    					cellValue = cell.getCellFormula()+"";
    					break;
    				case HSSFCell.CELL_TYPE_BLANK: //空
    					cellValue = "";
    					break;
    				case HSSFCell.CELL_TYPE_ERROR:  //故障
//    					cellValue = "非法字符";
    					cellValue = "";
    					break;
    				default:
//    					cellValue = "未知类型";
    					cellValue = "";
    					break;
    				}
    			}
    			rowList.add(cellValue);
			}
    		dataList.add(rowList);
		}
    	return  dataList;
    }
    
}

class UUtil{
    public static boolean isExcel2003(String filePath){
        return  filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath){
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

}

```java
package com.globalization.tools;

public class StringXmlContent implements XmlContent {
	private StringBuilder xmlSb = new StringBuilder();
	private String folderName;
	private String fileName = "default.xml";
	private String subFolderName = "default/";
	private boolean isInit;
	public final static String DEFAULT_TAG_ELEMENT_NAME = "string";
	private final static String DEFAULT_START_ELEMENT = "<" + DEFAULT_TAG_ELEMENT_NAME + ">";
	private final static String DEFAULT_END_ELEMENT = "</" + DEFAULT_TAG_ELEMENT_NAME + ">";
	private String endElement;
	private String startElement;

	@Override
	public boolean isInit() {
		return isInit;
	}
	
	@Override
	public String getFolderName() {
		return folderName;
	}

	@Override
	public String getFileName() {
		return fileName;
	}
	
	@Override
	public String getHeader() {
		return "<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n";
	}

	@Override
	public String getStartDocument() {
		return "<resources>\r\n";
	}

	@Override
	public String getStartTag(String startElement) {
		if (startElement == null || startElement.length() == 0) {
			return this.startElement = DEFAULT_START_ELEMENT;
		}
		return this.startElement = startElement;
	}


	@Override
	public void init(String folderName, String subFolder, String fileName) {
		this.folderName = folderName;
		this.subFolderName = subFolder;
		this.fileName = (fileName != null && fileName.length() > 3 && fileName.endsWith("xml")) ? fileName : this.fileName;
		xmlSb.append(getHeader() + getStartDocument());
		isInit = true;
	}
	
	@Override
	public void appendContent(String startElement, String content) {
		xmlSb.append("	" + getStartTag(startElement) + content + getEndTag() + "\r\n");
	}

	@Override
	public String getEndTag() {
		if (endElement == null || endElement.length() == 0 || !startElement.contains(endElement.replace("</", "").replace(">", ""))) {
			String[] strs = startElement.split(" ");
			String sub= strs[0];
			sub = (sub.contains(">") ? sub : sub + ">").replace("<", "</"); 
			endElement = sub;
			if (endElement == null || endElement.length() == 0) {
				endElement = DEFAULT_END_ELEMENT;
			}
		}
		return endElement;
	}

	@Override
	public String getEndDocument() {
		return "</resources>";
	}

	@Override
	public void perform() {
		xmlSb.append(getEndDocument());
	}
	
	@Override
	public String getXmlContentData() {
		return xmlSb.toString();
	}

	public void setFolderName(String folderName) {
		this.folderName = folderName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	@Override
	public String getSubFolderName() {
		// TODO Auto-generated method stub
		return subFolderName;
	}
	
	public void setEndElement(String endElement) {
		this.endElement = endElement;
	}
}
package com.globalization.tools;

public class StringXmlContent2 implements XmlContent {
	private StringBuilder xmlSb = new StringBuilder();
	private String folderName;
	private String fileName = "default.xml";
	private String subFolderName = "default/";
	private boolean isInit;
	public final static String DEFAULT_TAG_ELEMENT_NAME = "string";
	private String tagElementName = DEFAULT_TAG_ELEMENT_NAME;

	@Override
	public boolean isInit() {
		return isInit;
	}
	
	@Override
	public String getFolderName() {
		return folderName;
	}

	@Override
	public String getFileName() {
		return fileName;
	}
	
	@Override
	public String getHeader() {
		return "<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n";
	}

	@Override
	public String getStartDocument() {
		return "<resources>\r\n";
	}

	@Override
	public String getStartTag(String tagName) {
		if (tagName == null || tagName.length() == 0) {
			return "	<" + tagElementName + ">";
		}
		return "	<" + tagElementName + " name=\"" + tagName + "\">";
	}


	@Override
	public void init(String folderName, String subFolder, String fileName) {
		this.folderName = folderName;
		this.subFolderName = subFolder;
		this.fileName = (fileName != null && fileName.length() > 3 && fileName.endsWith("xml")) ? fileName : this.fileName;
		xmlSb.append(getHeader() + getStartDocument());
		isInit = true;
	}
	
	@Override
	public void appendContent(String tagName, String content) {
		xmlSb.append(getStartTag(tagName) + content + getEndTag());
	}

	@Override
	public String getEndTag() {
		return "</" + tagElementName.split(" ")[0] + ">\r\n";
	}

	@Override
	public String getEndDocument() {
		return "</resources>";
	}

	@Override
	public void perform() {
		xmlSb.append(getEndDocument());
	}
	
	@Override
	public String getXmlContentData() {
		return xmlSb.toString();
	}

	public void setFolderName(String folderName) {
		this.folderName = folderName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	@Override
	public String getSubFolderName() {
		// TODO Auto-generated method stub
		return subFolderName;
	}

	public void setTagElementName(String tagElementName) {
		this.tagElementName = tagElementName;
	}
	
}

package com.globalization.tools;

import java.io.File;
import java.util.List;

public class ExcelToXml implements DocumentConversion {

	@Override
	public void excute(Nation nation, List<List<String>> datas) {
		StringXmlContent sxc = new StringXmlContent();
		System.out.println(datas.get(300));
		for(int i = 0; i< datas.size(); i++) {
    		List<String> ls = datas.get(i);
    		int oIndex = -1;
    		if (i != 0 && (oIndex = ls.lastIndexOf("OPEN")) != -1) {
    			int index = oIndex + nation.getOffset();
    			String subFolder = ls.get(0);
    			if (!isValidFileName(subFolder)) {
    				subFolder = sxc.getSubFolderName() + "-InvalidPath";
				}
    			String eFolderName = nation.getBaseFolder() + subFolder + "/" + nation.getNationFolder();
    			verifyFolder(eFolderName);
	    		String originString = ls.get(1);
//	    		if (originString != null && (originString.contains("<string-array") || !originString.contains("<string"))) {
//	    			System.out.println(ls);
//	    		}
//	    		String elementName = getElementName(originString);
//	    		String tagName = getTagName(originString);
	    		String[] element = getElement(originString);
	    		
	    		if (!subFolder.equals(sxc.getSubFolderName())) {
	    			if (sxc.getXmlContentData().length() > 0) {
						sxc.perform();
						writeToFile(sxc);
					}
					sxc = new StringXmlContent();
					sxc.init(eFolderName, subFolder, "strings.xml");
				}
	    		if (index < ls.size()) {
	    			if (!sxc.isInit()) {
	    				sxc.init(eFolderName, subFolder, "strings.xml");
					}
					String value = ls.get(index);
//					sxc.setTagElementName(elementName);
					sxc.setEndElement(element[1]);
					sxc.appendContent(element[0], value);
				}
			}
    		
    	}
		if (!sxc.getXmlContentData().endsWith(sxc.getEndDocument())) {
			sxc.perform();
			writeToFile(sxc);
		}
		System.out.println("所有内容写入xml文件执行完毕");
	}

	private String[] getElement(String originString) {
		String[] result = new String[2];
		int start1 = originString.indexOf("<");
		if (start1 < 0) {
			start1 = 0;
		}
		int end1 = originString.indexOf(">");
		if (end1 > start1) {
			result[0] = originString.substring(start1, end1 + 1);
		}
		int start2 = originString.lastIndexOf("</");
		int end2 = originString.lastIndexOf(">");
		if (result[0] != null && start2 > result[0].length() && end2 > start2) {
			result[1] = originString.substring(start2, end2 + 1);
		}
		return result;
	}

	private String getElementName(String originString) {
		String result = null;
		if (originString != null && originString.length() > 0) {
			int start = originString.indexOf("<") + 1;
			int end = originString.indexOf("name") - 1;
			if (end < 0) {
				end = originString.indexOf(">");
			}
			if (start >= originString.length() - 1 || end >= originString.length() - 1 || start >= end) {
				System.out.println(originString + "--------------- start = " + start + "; end = " + end);
			} else if (start > -1 && end > -1) {
				result = originString.substring(start, end);
			}
		}
		if (result == null || result.length() == 0) {
			result = StringXmlContent.DEFAULT_TAG_ELEMENT_NAME;
		}
		if (result.contains("quantity")) {
			System.out.println(originString);
		}
		return result;
	}

	private void writeToFile(StringXmlContent sxc) {
		String data = sxc.getXmlContentData();
		if (data != null && data.length() > 0) {
			FileTools.WriteFileByBw(sxc.getFolderName() + sxc.getFileName(), data);
		}
	}

	private String getTagName(String originString) {
//		System.out.print("; 原始英文键值对是:" + originString);
		String result = "";
		if (originString != null && originString.contains("name")) {
			int startIndex = originString.indexOf("\"");
			int endIndex = originString.indexOf(">") - 1;
//			int endIndex = originString.lastIndexOf("\"");
			if (startIndex > -1 && endIndex > -1 && endIndex > startIndex) {
				result = originString.substring(startIndex + 1, endIndex);
//				if (result.contains("\"")) {
//					System.out.println("Tag name is ======== " + result);
//				}
			}
		}
		return result;
	}

	private void verifyFolder(String folderName) {
		File folder = new File(folderName);
		if (!folder.exists()) {
			folder.mkdirs();
		} else if (folder.isFile()) {
			folder.delete();
			folder.mkdirs();
		}
	}	
	
	public static boolean isValidFileName(String fileName) { 
		if (fileName == null || fileName.length() > 255) 
			return false; 
		else return fileName.matches("[^\\s\\\\/:\\*\\?\\\"<>\\|](\\x20|[^\\s\\\\/:\\*\\?\\\"<>\\|])*[^\\s\\\\/:\\*\\?\\\"<>\\|\\.]$"); 
	}
}
package com.globalization.tools;

public class DocumentConversionFactory {
	public final static String EXCEL_TO_XML = "ExcelToXml";
	
	public static DocumentConversion createDocumentConversion(String flag) {
		DocumentConversion result;
		switch (flag) {
		case EXCEL_TO_XML:
			result = new ExcelToXml();
			break;

		default:
			result = new ExcelToXml();
			break;
		}
		return result;
	}

}

调用:

package com.globalization.tools;

import java.util.List;

public class GlobalizationApp {
	private final static String EXCEL_FILE_PATH = "E:\\files\\Documents\\excel\\翻译D379HM Menu_2021015 - 副本.xlsx";

	public static void main(String[] args) {
		ReadLocalExcel rle = new ReadLocalExcel();
    	List<List<String>> list = rle.read(EXCEL_FILE_PATH);
    	Nation nation = NationFactory.createNation(NationFactory.POLASH_FOLDER);
    	nation.setOffset(2);
    	DocumentConversion etx = DocumentConversionFactory.createDocumentConversion(DocumentConversionFactory.EXCEL_TO_XML);
		etx.excute(nation, list);
	}
	
}
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页