生成xml格式的excel

最新推荐文章于 2025-07-12 16:01:56 发布

都叫我大帅哥

最新推荐文章于 2025-07-12 16:01:56 发布

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： POI 文章标签： xml excel

本文链接：https://blog.youkuaiyun.com/busixianyuzaisheng/article/details/51829586

POI 专栏收录该内容

3 篇文章

订阅专栏

这篇博客介绍如何利用Python编程将数据转换为XML格式的Excel文件，内容包括处理多工作表的功能，适合需要自定义Excel数据结构的项目。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这里只是代码片段，有好多数据变量要根据你们自己项目修改，不要完全照抄。这里考虑到多sheet功能。

/**
     * 创建xml格式的excel头部。
     * 大帅哥
     */
    private void createExcelXmlHeader(String path){
        if(document!=null){
            return;
        }
        file = new File(path);
        document = DocumentHelper.createDocument();
        workBook = document.addElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
        workBook.addAttribute("xmlns:o","urn:schemas-microsoft-com:office:office");
        workBook.addAttribute("xmlns:x","urn:schemas-microsoft-com:office:excel");
        workBook.addAttribute("xmlns:ss","urn:schemas-microsoft-com:office:spreadsheet");
        workBook.addAttribute("xmlns:html","http://www.w3.org/TR/REC-html40");
        workBook.addComment("备注");

        Element styles = workBook.addElement("Styles");
        styles.addElement("Style").addAttribute("ss:ID", "Default").addAttribute("ss:Name", "Normal");
        Element style = styles.addElement("Style").addAttribute("ss:ID", "s15");
        style.addElement("Alignment").addAttribute("ss:Vertical", "Bottom");
        style.addElement("Borders");
        style.addElement("Font").addAttribute("x:Family", "Swiss");
        style.addElement("Interior");
        style.addElement("NumberFormat");

        Element styleCenter = styles.addElement("Style").addAttribute("ss:ID", "s21");
        styleCenter.addElement("Alignment").addAttribute("ss:Vertical", "Bottom").addAttribute("ss:Horizontal", "Center");
        styleCenter.addElement("Borders");
        styleCenter.addElement("Font").addAttribute("x:Family", "Swiss");
        styleCenter.addElement("Interior");
        styleCenter.addElement("NumberFormat");

        Element styleRight = styles.addElement("Style").addAttribute("ss:ID", "s22");
        styleRight.addElement("Alignment").addAttribute("ss:Vertical", "Bottom").addAttribute("ss:Horizontal", "Right");
        styleRight.addElement("Borders");
        styleRight.addElement("Font").addAttribute("x:Family", "Swiss");
        styleRight.addElement("Interior");
        styleRight.addElement("NumberFormat");
    }
    //编写主体内容
    private void writeExcelXmlSheet(List<Object> data,List<String> pageHeader,
            Map<Integer, Map<String, Object>> formatMap){
        Element row = null;
        Element cell = null;
        String type = null;
        String value = null;

        for(int rowIndex=0; rowIndex<data.size();rowIndex++){
            if(pageCount>=fixCount||(sheetCount==0&&rowIndex==0)){
                worksheet = workBook.addElement("Worksheet");
                worksheet.addAttribute("ss:Name", "sheet"+(sheetCount+1));
                table = worksheet.addElement("Table");
                table.addAttribute("ss:StyleID", "s15");
                addXmlHeader(table, pageHeader);
                if(pageCount>=fixCount){
                    pageCount-=fixCount;
                }
                sheetCount++;
            }
            pageCount++;
            List lines = (ArrayList) data.get(rowIndex);
            row = table.addElement("Row");              
            //遍历行中的每一个单元格 
            for(int cellIndex=0;cellIndex<lines.size();cellIndex++){ 
                cell = row.addElement("Cell");
                value = (String) lines.get(cellIndex);
                if (value != null && p1.matcher(value).matches()) {
                    type="String";
                } else if (value != null && p.matcher(value).matches()) {
                    if(formatMap!=null&&formatMap.containsKey(cellIndex)){
                        BigDecimal b1 = new BigDecimal(value);
                        BigDecimal b2 = new BigDecimal(formatMap.get(cellIndex).get("dwRate").toString());
                        DecimalFormat decimalFormat = new DecimalFormat(formatMap.get(cellIndex).get("format").toString());
                        value = decimalFormat.format(b1.multiply(b2).doubleValue())+formatMap.get(cellIndex).get("dwName");
                    }
                    type = "String";
                } else if("(null)".equals(value)){
                    value = "";
                    type = "String";
                }else {
                    type = "String";
                }
                if(RateDateUtil.isNum(value)){
                    cell.addAttribute("ss:StyleID", "s22");
                }
                cell.addElement("Data").addAttribute("ss:Type", type).addText(value);
            }
        }
    }
    //当excel不同sheet时，用于添加头部信息
    private void addXmlHeader(Element table,List<String> pageHeader){
        Element row = table.addElement("Row");
        Element cell;
        String value;
        String type;
        //遍历行中的每一个单元格 
        for(int cellIndex=0;cellIndex<pageHeader.size();cellIndex++){ 
            cell = row.addElement("Cell");
            value = (String) pageHeader.get(cellIndex);
            if (value != null && p1.matcher(value).matches()) {
                type="String";
            } else if (value != null && p.matcher(value).matches()) {
                type = "Number";
            } else if("(null)".equals(value)){
                value = "";
                type = "String";
            }else {
                type = "String";
            }
            cell.addElement("Data").addAttribute("ss:Type", type).addText(value);
        }
    }