java--Excel读取及转XML

1 单纯的将excel转xml
下载jdom-2.0.6.jar和操作excel的jxl-2.6.jar架包

/**     * 将execl转成xml     *      * @param excelPath     *            被转换的excel文件路径     * @param xmlPath     *            转换成xml的路径     */    public static void execlConvertXml(String excelPath, String xmlPath) {        Workbook readwb = null;        try {            readwb = Workbook.getWorkbook(new File(excelPath));            Element data = new Element("data");// 创建根节点            Document doc = new Document(data);// 根节点添加到文档中；            // 循环每个sheet            for (int m = 0; m < readwb.getNumberOfSheets(); m++) {                Sheet sheet = readwb.getSheet(m);                int rsColumns = sheet.getColumns();// 获取Sheet表中所包含的总列数                int rsRows = sheet.getRows();// 获取Sheet表中所包含的总行数                Cell[] firstCells = sheet.getRow(0);// 获取每个sheet中的第一行标题                // 循环每行，从1行开始1开始，第0行为列名                for (int i = 1; i < rsRows; i++) {                    // 创建行节点;                    Element row = new Element("dataDetail");                    insertHead(row);// 每一行添加相同的信息，可忽略                    // 循环当前行的各单元格                    for (int j = 1; j < rsColumns; j++) {                        Cell cell = sheet.getCell(j, i);// 取出每个单元格                        if (cell.getContents() == "") {                            continue;                        }                        Element column = new Element(                                firstCells[j].getContents());// 创建单元格节点                        column.setText(cell.getContents());                        row.addContent(column);                    }                    data.addContent(row);                }            }            // 将标签内容格式化            Format format = Format.getPrettyFormat();            XMLOutputter XMLOut = new XMLOutputter(format);            XMLOut.output(doc, new FileOutputStream(xmlPath));        } catch (Exception e) {            e.printStackTrace();        } finally {            readwb.close();            System.out.println("run over");        }    }

2 将excel转成XMl后读取XML中的信息
下载dom4j-2.0.1.jar架包，https://dom4j.github.io/

    public static void readXmlContent(String xmlPath) {        SAXReader reader = new SAXReader();        try {            Document document = reader.read(new File(xmlPath));            Element root = document.getRootElement();//获取根结点            List<Element> lstFirstNode = root.elements();//获取根节点的子节点            for (Element element1 : lstFirstNode) {                List<Element> lstThirdNode = element1.elements();//子节点的下一级节点                for (Element element2 : lstThirdNode) {                    System.out.print(element2.getName()+"\t"+element2.getTextTrim());                }                System.out.println();            }        } catch (Exception exception) {            exception.printStackTrace();        } finally {            System.out.println("over");        }    }}

生成格式化的XML标签：

解析xml<shop name="shop for geeks" location="Tokyo, Japan">  <computer name="iBook" price="1200$" />  <comic_book name="Dragon Ball vol 1" price="9$" />  <geekyness_of_shop price="priceless" /><op>SAXBuilder builder = new SAXBuilder();Document doc = builder.build(new FileInputStream("foo.xml"));Element root = doc.getRootElement();

生成XMl

Element root = new Element("shop");root.setAttribute("name", "shop for geeks");root.setAttribute("location", "Tokyo, Japan");Element item1 = new Element("computer");item1.setAttribute("name", "iBook");item1.setAttribute("price", "1200$");root.addContent(item1);// perform similar steps for other elementsXMLOutputter outputter = new XMLOutputter();outputter.output(new Document(root), new FileOutputStream ("foo2.xml"));

3 读取Excel后将其部分写入新的excel中，仅支持2003的excel：

/**     * 将Element写入到输出流中     *      * @param element 待写入的标签     * @param outputStream 输出流位置     */    public static void writeToExcel01(Element element, OutputStream outputStream) {        WritableWorkbook workbook = null;        try {            workbook = Workbook.createWorkbook(outputStream);        } catch (IOException e) {            e.printStackTrace();        }        WritableSheet writableSheet = workbook.createSheet("sheet1", 0);// 创建一个sheet        List<Element> lstRow = element.getChildren();// 所有行        for (int i = 0; i < lstRow.size(); i++) {            List<Element> lstElem = lstRow.get(i).getChildren();// 当前行的所有列            for (int j = 0; j < lstElem.size(); j++) {                addlabelToSheet(writableSheet,j,i,lstElem.get(j).getText());            }        }        try {            workbook.write();// 从内存中写入文件中            workbook.close();// 关闭资源，释放内存        } catch (IOException e) {            e.printStackTrace();        } catch (WriteException e) {            e.printStackTrace();        }    }    /**     * 将指点的信息写到指定的sheet中，     * @param writableSheet 可看成是excel中的一个sheet      * @param column 列     * @param row 行     * @param text 内容     */    public static void addlabelToSheet(WritableSheet writableSheet,Integer column,Integer row,String text){        Label label = new Label(column, row, text);// 第一个参数表示列，第二个表示行        try {            writableSheet.addCell(label);// 将生成的单元格添加到工作表中        } catch (RowsExceededException e) {            e.printStackTrace();        } catch (WriteException e) {            e.printStackTrace();        }    }

更新2017.8.14日
读取excel时发现不能读取2007之后的版本，在网上找到一篇博客，成功的解决了该问题。
贴大声博客地址：http://blog.csdn.net/mmm333zzz/article/details/7962377
成功的解决了问题：

自己整理后的代码：

public static void main(String[] args) throws Exception {        // List<List<String>> list = poi.read("d:/aaa.xls");        List<List<String>> list = convertExcelToList("text.xlsx");        if (list != null) {            for (int i = 0; i < list.size(); i++) {                System.out.print("第" + (i) + "行");                List<String> cellList = list.get(i);                for (int j = 0; j < cellList.size(); j++) {                    System.out.print("    " + cellList.get(j));                }                System.out.println();            }        }    }    /**     * 根据文件名读取excel文件     *      */    public static List<List<String>> convertExcelToList(String filePath) {        List<List<String>> dataLst = new ArrayList<List<String>>();        InputStream inputStream = null;        try {            /** 验证文件是否合法 */            if (!validateExcel(filePath)) {                System.out.println("excel文件不合法");                return null;            }            /** 判断文件的类型，是2003还是2007 */            boolean isExcel2003 = true;            if (WDWUtil.isExcel2007(filePath)) {                isExcel2003 = false;            }            File file = new File(filePath);            inputStream = new FileInputStream(file);            /** 根据版本选择创建Workbook的方式 */            Workbook wb = null;            if (isExcel2003) {                wb = new HSSFWorkbook(inputStream);            } else {                wb = new XSSFWorkbook(inputStream);            }            Sheet sheet = wb.getSheetAt(0);// 第一个shell            Integer totalRowsNum = sheet.getPhysicalNumberOfRows();// 得到Excel的行数            Integer totalColumnNum = 0;// 得到Excel的列数            if (totalRowsNum >= 1 && sheet.getRow(0) != null) {                totalColumnNum = sheet.getRow(0).getPhysicalNumberOfCells();            }            // 读取excel各行和各列            for (int r = 0; r < totalRowsNum; r++) {// 循环Excel的行                Row row = sheet.getRow(r);                if (row == null) {                    continue;                }                List<String> rowLst = new ArrayList<String>();                for (int c = 0; c < totalColumnNum; c++) {// 循环当前行的列                    Cell cell = row.getCell(c);                    String cellValue = "";                    if (null != cell) {                        cellValue = judgeType(cell);                    }                    rowLst.add(cellValue);                }                dataLst.add(rowLst);            }            inputStream.close();        } catch (Exception ex) {            ex.printStackTrace();        } finally {            if (inputStream != null) {                try {                    inputStream.close();                } catch (IOException e) {                    inputStream = null;                    e.printStackTrace();                }            }        }        return dataLst;    }    /**     * 判断类型，根据类型获取指定的值返回     * @param cell     * @return     */    public static String judgeType(Cell cell) {        String cellValue = "";        // 以下是判断数据的类型        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: // 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 = "非法字符";            break;        default:            cellValue = "未知类型";            break;        }        return cellValue;    }    /**     *      * @描述：验证excel文件     */    public static boolean validateExcel(String filePath) {        /** 检查文件名是否为空或者是否是Excel格式的文件 */        if (filePath == null                || !(isExcel2003(filePath) || isExcel2007(filePath))) {            return false;        }        /** 检查文件是否存在 */        File file = new File(filePath);        if (file == null || !file.exists()) {            return false;        }        return true;    }    /**     * @描述：是否是2003的excel，返回true是2003     */    public static boolean isExcel2003(String filePath) {        return filePath.matches("^.+\\.(?i)(xls)$");    }    /**     * @描述：是否是2007的excel，返回true是2007     */    public static boolean isExcel2007(String filePath) {        return filePath.matches("^.+\\.(?i)(xlsx)$");    }

参考：
http://www.cnblogs.com/vastsum/p/5925033.html
http://blog.csdn.net/dengbodb/article/details/7839780
http://blog.csdn.net/chenyunlin1342/article/details/49737721
http://www.cnblogs.com/Lonnn/p/6890217.html