读取excel数据并保存为xml

最新推荐文章于 2021-06-01 20:56:11 发布

伟雪无痕

最新推荐文章于 2021-06-01 20:56:11 发布

阅读量1.3k

点赞数

分类专栏： java基础 android应用

本文链接：https://blog.csdn.net/j086924/article/details/47948107

版权

android应用同时被 2 个专栏收录

39 篇文章 6 订阅

订阅专栏

java基础

20 篇文章 0 订阅

订阅专栏

前阵子，公司请外面人翻译了一些android中values中的一些strings，然而保存的都是excel格式，如果单纯的将excel中的数据粘贴到指定的xml中的话，工作量非常的大，于是，自己写了个简单的demo，将excel中的数据读取并保存为xml对应的数据，下面的demo和图片展示：

1、数据保存在BeanValue中,包括key和value，方便后续数据读取

package cn.excel.parser;

public class BeanValue {
	
	private String key;
	private String Value;
	
	public BeanValue() {
	}
	
	public String getKey() {
		return key;
	}
	public void setKey(String key) {
		this.key = key;
	}
	public String getValue() {
		return Value;
	}
	public void setValue(String value) {
		Value = value;
	}
	
	
	
	

}

2、数据解析，包括测试，直接在main方法中进行

package cn.excel.parser;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
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 java.util.Map.Entry;
import java.util.TreeMap;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class ReadExcelFile {
	private static final String SRC_FILE = "d://exceldoc/original/test.xls";

	public static void main(String[] args) {
		Map<Integer, Map<Integer, BeanValue>> mapList = ReadExcelFile();
		System.out.println("excel size= " + mapList.size() + " ");
		List<String> namelists = readCol5Name();
		System.out.println("namelists= " + namelists.size() + " ");

		writeXmlFile(mapList, namelists);

	}
	
	
    /**
     * 读取excel表名，并保存在List列表中
     * @return
     */
	private static List<String> readSheetName() {

		InputStream is = null;
		Workbook wb = null;
		java.util.List<String> list = null;

		try {
			is = new FileInputStream(SRC_FILE);
			if (null != is) {
				list = new ArrayList<>();
				wb = Workbook.getWorkbook(is);

				Sheet[] sheets = wb.getSheets();
				int sheetLen = sheets.length;

				for (int j = 0; j < sheetLen; j++) {

					list.add(sheets[j].getName());

				}// for

			}// if

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (null != wb) {
				wb.close();
			}

			if (null != is) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}

		return list;

	}
	
    /**
     * 读取第五列的标题名，并保持在List中
     * @return
     */
	private static List<String> readCol5Name() {

		InputStream is = null;
		Workbook wb = null;
		java.util.List<String> list = null;

		try {
			is = new FileInputStream(SRC_FILE);
			if (null != is) {
				list = new ArrayList<>();
				wb = Workbook.getWorkbook(is);

				Sheet[] sheets = wb.getSheets();
				int sheetLen = sheets.length;

				for (int j = 0; j < sheetLen; j++) {

					Sheet rs = wb.getSheet(j);
					Cell[] cell = rs.getRow(0);
					String packageName = cell[5].getContents();
					list.add(packageName);
					// System.out.println(packageName);
				}// for

			}// if

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (null != wb) {
				wb.close();
			}

			if (null != is) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}

		return list;

	}
    
	/**
	 * Map<Integer, BeanValue>,保持单张表中第三行开始，第2列和第5列的值（TreeMap可以按顺序加载）
	 * 返回Map<Integer, Map<Integer, BeanValue>>，保证Integer和表的索引一一对应
	 * 也可保持为List<Map<Integer, BeanValue>>
	 * @return
	 */
	private static Map<Integer, Map<Integer, BeanValue>> ReadExcelFile() {
		InputStream is = null;
		Workbook wb = null;
		Map<Integer, Map<Integer, BeanValue>> mapList = null;
		Map<Integer, BeanValue> maps = null;
		java.util.List<Map<Integer, BeanValue>> list = null;
		WorkbookSettings woSettings = null;

		try {
			is = new FileInputStream(SRC_FILE);
			if (null != is) {
				mapList = new HashMap<Integer, Map<Integer, BeanValue>>();
				list = new ArrayList<>();
				woSettings = new WorkbookSettings();
				woSettings.setEncoding("ISO-8859-1");//设置编码格式
				wb = Workbook.getWorkbook(is, woSettings);

				Sheet[] sheets = wb.getSheets();
				int sheetLen = sheets.length;

				for (int j = 0; j < sheetLen; j++) {

					Sheet rs = wb.getSheet(j);
					int rowNum = rs.getRows();
					int colNum = rs.getColumns();
					maps = new TreeMap<>();

					for (int i = 2; i < rowNum; i++) {

						Cell[] cell = rs.getRow(i);
						if (cell[5].getContents() == null
								|| cell[5].getContents().trim().equals("")) {

						} else {
							BeanValue beanValue = new BeanValue();
							beanValue.setKey(cell[2].getContents());
							beanValue.setValue(cell[5].getContents());
							maps.put(i, beanValue);
						}

					}

					if (maps.size() > 0) {
						mapList.put(j, maps);
						System.out.println(sheets[j].getName());
					}

					// list.add(maps);

				}// for

			}// if

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (null != wb) {
				wb.close();
			}

			if (null != is) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}

		return mapList;

	}
    
	
	/**
	 * 返回DocumentBuilder
	 * @return
	 */
	public static DocumentBuilder getDocumentBuilder() {
		DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
		DocumentBuilder dbBuilder = null;
		try {
			dbBuilder = dbFactory.newDocumentBuilder();
		} catch (ParserConfigurationException e) {
			e.printStackTrace();
		}
		return dbBuilder;

	}
    
	
	/**
	 * 将所读excel的数据写入xml中，并按<String></string>格式保存
	 * @param mapList
	 * @param nameList
	 */
	private static void writeXmlFile(
			Map<Integer, Map<Integer, BeanValue>> mapList, List<String> nameList) {

		DocumentBuilder db = getDocumentBuilder();

		Document document = null;

		Iterator<Entry<Integer, Map<Integer, BeanValue>>> iteratorMap = mapList
				.entrySet().iterator();
		// int i = 0;
		while (iteratorMap.hasNext()) {
			Entry<Integer, Map<Integer, BeanValue>> entryMap = iteratorMap
					.next();
			int i = entryMap.getKey();
			Map<Integer, BeanValue> map = entryMap.getValue();

			document = db.newDocument();
			document.setXmlStandalone(true);

			Element resource = document.createElement("resource");//创建元素节点
			resource.setAttribute("xmlns:xliff",
					"urn:oasis:names:tc:xliff:document:1.2");
			document.appendChild(resource);//添加元素

			Iterator<Entry<Integer, BeanValue>> iterator = map.entrySet()
					.iterator();
			while (iterator.hasNext()) {

				Entry<Integer, BeanValue> entry = iterator.next();

				BeanValue beanValue = entry.getValue();
				String key = beanValue.getKey();
				String value = beanValue.getValue();
				if (value == null || value.trim().equals("")) {

				} else {
					Element string = document.createElement("string");
					string.setAttribute("name", key);
					string.appendChild(document.createTextNode(value));//添加值
					resource.appendChild(string);//添加子元素
				}


			}// while

			String nameStr = nameList.get(i);
			String packStr = nameStr.substring(0, nameStr.lastIndexOf("/"));
			String fileName = nameStr.substring(nameStr.lastIndexOf("/") + 1);
			File file = new File("d://exceldoc/" + packStr);
			if (!file.exists()) {
				file.mkdirs();

			}
			
			saveXmlData(document,packStr,fileName);

		
		}// while



	}


	private static void saveXmlData(Document document, String packStr,
			String fileName) {
		TransformerFactory tFactory = TransformerFactory.newInstance();
		try {
			Transformer tFTransformer = tFactory.newTransformer();
			tFTransformer.setOutputProperty(OutputKeys.INDENT, "yes");
			tFTransformer.transform(new DOMSource(document),
					new StreamResult("d://exceldoc/" + packStr + "/"
							+ fileName));

		} catch (TransformerConfigurationException e) {
			e.printStackTrace();
		} catch (TransformerException e) {
			e.printStackTrace();
		}
		
	}



}

提示：

1、需要引入的包：excel（jxl.jar）xml(dom4j-1.6.1.jar),excel解析poi-3.11-20141221.jar也可以；

2、读取excel会出现乱码问题，可通过WorkbookSettings进行编码格式转换；

3、以上demo针对本人读取的excel表格测试是可以的，具体需要根据你excel中的内容做相应变更即可，

但大体解析流程是一样的，希望以上demo能对你有所帮助！

excel源数据表格：