Android开发实现读取excel数据并保存为xml的方法
本文实例讲述了Android开发实现读取excel数据并保存为xml的方法。分享给大家供大家参考,具体如下:
前阵子,公司请外面人翻译了一些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中的内容做相应变更即可,
但大体解析流程是一样的!
excel源数据表格:
保存为xml表格:
PS:这里再为大家提供几款关于xml操作的在线工具供大家参考使用:
在线XML/JSON互相转换工具:
http://tools.jb51.net/code/xmljson
在线格式化XML/在线压缩XML:
http://tools.jb51.net/code/xmlformat
XML在线压缩/格式化工具:
http://tools.jb51.net/code/xml_format_compress
XML代码在线格式化美化工具:
http://tools.jb51.net/code/xmlcodeformat
更多关于Android相关内容感兴趣的读者可查看本站专题:《Android操作XML数据技巧总结》、《Android编程之activity操作技巧总结》、《Android资源操作技巧汇总》、《Android文件操作技巧汇总》、《Android开发入门与进阶教程》、《Android视图View技巧总结》及《Android控件用法总结》
希望本文所述对大家Android程序设计有所帮助。