Android開發實現讀取excel數據并保存為xml的方法

2019-10-22 18:24:50

字體：大中小

來源：轉載

供稿：網友

本文實例講述了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源數據表格：

Android開發,讀取,excel,保存,xml