欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

java实现excel文件转换为xml文件

程序员文章站 2022-05-17 14:45:00
一、导包: org.apache.poipoi3.17org.apache.poi ......

一、导包:

<dependency>
<groupid>org.apache.poi</groupid>
<artifactid>poi</artifactid>
<version>3.17</version>
</dependency>
<dependency>
<groupid>org.apache.poi</groupid>
<artifactid>poi-examples</artifactid>
<version>3.16</version>
</dependency>
<dependency>
<groupid>org.jdom</groupid>
<artifactid>jdom</artifactid>
<version>1.1.3</version>
</dependency>

二、代码部分:

public class analysisetox {

    public static void main(string[] args) {
        try {
            system.out.println("=============");
            // 用输入流从本地拿到对应的excel文件
            inputstream stream = new fileinputstream("c:\\users\\administrator\\desktop\\试题.xlsx");
            // 指定要生成的xml的路径,并构建文件对象
            file f = new file("test.xml");// 新建个file对象把解析之后得到的xml存入改文件中
            writerxml(stream, f);// 将数据以xml形式写入文本
        } catch (filenotfoundexception e) {
            system.out.println("未找到指定路径的文件!");
            e.printstacktrace();
        } catch (ioexception e) {
            // todo auto-generated catch block
            e.printstacktrace();
        }
    }

    private static void writerxml(inputstream stream, file f) throws ioexception {
        system.out.println("into writerxml");
        fileoutputstream fo = new fileoutputstream(f);// 得到输入流
        document doc = readexcell(stream);// 读取excel函数
        format format = format.getcompactformat().setencoding("utf-8").setindent("");
        xmloutputter xmlout = new xmloutputter(format);// 在元素后换行,每一层元素缩排四格
        xmlout.output(doc, fo);
        fo.close();
    }

    private static document readexcell(inputstream stream) {
        system.out.println("into readexcell");
        // 设置根<tax_institutions></tax_institutions>元素
        element root = new element("tax_institutions");
        document doc = new document(root);
        try {
            hssfworkbook hw = new hssfworkbook(stream);
            // 获取工作薄的个数,即一个excel文件中包含了多少个sheet工作簿
            int wblength = hw.getnumberofsheets();
            system.out.println("wblength=" + wblength);

            for (int i = 0; i < wblength; i++) {
                hssfsheet shee = hw.getsheetat(i);
                int length = shee.getlastrownum();
                system.out.println("行数:" + length);
                for (int j = 1; j <= length; j++) {
                    hssfrow row = shee.getrow(j);
                    if (row == null) {
                        continue;
                    }
                    int cellnum = row.getphysicalnumberofcells();// 获取一行中最后一个单元格的位置
                    system.out.println("列数cellnum:" + cellnum);
                    element e = null;
                    // 设置根元素下的并列元素<tax_institution></tax_institution>
                    e = new element("tax_institution");
                    // element[] es = new element[16];
                    for (int k = 0; k < cellnum; k++) {
                        hssfcell cell = row.getcell((short) k);
                        string temp = get(k);
                        system.out.print(k + " " + temp + ":");
                        element item = new element(temp);
                        if (cell == null) {
                            item.settext("");
                            e.addcontent(item);
                            cellnum++;// 如果存在空列,那么cellnum增加1,这一步很重要。
                            continue;
                        }

                        else {
                            string cellvalue = "";
                            switch (cell.getcelltype()) {
                            // 如果当前cell的type为numeric
                            case hssfcell.cell_type_numeric:
                            case hssfcell.cell_type_formula: {
                                // 判断当前的cell是否为date
                                if (hssfdateutil.iscelldateformatted(cell)) {
                                    // 如果是date类型则,转化为data格式

                                    // 方法1:这样子的data格式是带时分秒的:2011-10-12 0:00:00
                                    // cellvalue =
                                    cell.getdatecellvalue().tolocalestring();

                                    // 方法2:这样子的data格式是不带带时分秒的:2011-10-12
                                    date date = cell.getdatecellvalue();
                                    simpledateformat sdf = new simpledateformat("yyyy-mm-dd");
                                    cellvalue = sdf.format(date);
                                    item.settext(cellvalue);

                                }
                                // 如果是纯数字
                                else {
                                    // 取得当前cell的数值
                                    cellvalue = string.valueof((int) cell.getnumericcellvalue());
                                    item.settext(cellvalue);
                                }
                                break;
                            }
                            // 如果当前cell的type为strin
                            case hssfcell.cell_type_string:
                                // 取得当前的cell字符串
                                cellvalue = cell.getrichstringcellvalue().getstring();
                                item.settext(cellvalue);
                                break;
                            // 默认的cell值
                            default:
                                cellvalue = " ";
                                item.settext(cellvalue);
                            }
                            e.addcontent(item);
                            system.out.println(cellvalue);
                        }
                    }
                    root.addcontent(e);

                }

            }
        } catch (exception e) {
            e.printstacktrace();
        } finally {
            try {
                stream.close();
            } catch (ioexception e1) {
                e1.printstacktrace();
            }

        }
        return doc;
    }

    // 设置并列元素里的子元素名
    private static string get(int k) {
        string test = "";
        switch (k) {
        case 0:
            test = "org_name";
            break;
        case 1:
            test = "legal_mobile_phone";
            break;
        case 2:
            test = "org_address";
            break;
        case 3:
            test = "cert_type";
            break;
        case 4:
            test = "postal_code";
            break;
        case 5:
            test = "reg_sum";
            break;
        case 6:
            test = "business_scope";
            break;
        case 7:
            test = "social_credit_code";
            break;
        case 8:
            test = "reg_type";
            break;
        case 9:
            test = "legal_person_name";
            break;
        case 10:
            test = "cert_number";
            break;
        case 11:
            test = "found_time";
            break;
        case 12:
            test = "service_status";
            break;
        case 13:
            test = "staff_sum";
            break;
        case 14:
            test = "partner_sum";
            break;
        case 15:
            test = "is_branch_org";
            break;
        default:
        }
        return test;

    }

}

转载地址csdn:https://blog.csdn.net/weixin_40420734/article/details/79538772

 

注意:

excel版本保存文件的后缀名问题,如果是.xls,使用hssfworkbook;如果是xlsx,使用xssfworkbook。

版本错误会抛出officexmlfileexception!!!