一个生成excel的工具类

很多人都直接编辑html,保存成xls就当成excel报表了。挺方便的,于是我写了这个简化工作的工具类——将一个html的表格模板解析成一个xls报表
模板如下
<?xml version="1.0" encoding="GB2312" ?>
<div style="width:100%;height:450;overflow-x:auto;overflow-y:auto">
    <table width="100%" border="1" cellspacing="2" cellpadding="0">
            <tr id="title" bgcolor="#fefcce">
                <td nowrap="true" >客户</td>
                <td nowrap="true" >产品</td>
                <td nowrap="true" >中文名称</td>
                <td nowrap="true" >英文名称</td>
                <td nowrap="true" >产品分类</td>
                <td nowrap="true" >包装</td>
                <td nowrap="true" >单位</td>
                <td nowrap="true" >数量</td>
                <td nowrap="true" >冻结数量</td>
                <td nowrap="true" >可用数量</td>
                <td nowrap="true"  id="CUBIC"></td>
                <td nowrap="true"  id="WEIGHT"></td>
            </tr>
            <tr id="record">
                <td nowrap="true" id="CUSTOMERID"></td>
                <td nowrap="true" id="SKU_ID"></td>
                <td nowrap="true" id="SKU_DESCR_C"></td>
                <td nowrap="true" id="SKU_DESCR_E"></td>
                <td nowrap="true" id="SKU_CLASS"></td>
                <td nowrap="true" id="PACKAGE_ID"></td>
                <td nowrap="true" id="UOM"></td>
                <td nowrap="true" id="QUANTITY"></td>
                <td nowrap="true" id="FREEZE_QUANTITY"></td>
                <td nowrap="true" id="AVAILABLE_QUANTITY"></td>
                <td nowrap="true" id="CUBIC"></td>
                <td nowrap="true" id="WEIGHT"></td>
            </tr>
    </table>
</div>
工具类如下

public class ExcelTemplateUtil {
    private static String CHARSET = "";
    private static final String ROOT = "ROOT";
    private static final String TITLE = "TITLE";
    private static final String RECORD = "RECORD";
    private static Map temp = new HashMap();

    public static String generateListToTemplate(Object titleObj, List recordList, File templateFile)
    {
        readTemplateFile(templateFile);
        ByteArrayOutputStream os = (ByteArrayOutputStream) builderExcelOutput(titleObj, recordList);
        return removeXMLHeader(os);
    }

    public static void readTemplateFile(File file)
    {
        try {
            Document templateDocument = new SAXReader().read(file);
            Element root = templateDocument.getRootElement();
            List trList = root.selectNodes("//div/table/tr");
            Element titleTemp = (Element) trList.get(0);
            Element recordTemp = (Element) trList.get(1);
            root.element("table").remove(titleTemp);
            root.element("table").remove(recordTemp);
            temp.put(TITLE, trList.get(0));
            temp.put(RECORD, trList.get(1));
            temp.put(ROOT, root);
        } catch (DocumentException e) {
            e.printStackTrace();
            throw new RuntimeException("Parse xml file error, Cause:", e);
        }
    }

    public static OutputStream builderExcelOutput(Object titleObj, List list)
    {

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        Element root = (Element) ((Element) temp.get(ROOT)).clone();
        Document document = DocumentHelper.createDocument();
        document.setRootElement(root);
        Element tableEle = root.element("table");
        tableEle.add(parseTitleElement(titleObj));
        for (int i = 0; i < list.size(); i++) {
            tableEle.add(parseRecordElement(list.get(i)));
        }
        try {
            OutputFormat format = new OutputFormat("", true, "GB2312");
            XMLWriter writer = new XMLWriter(os, format);
            writer.write(document);
            writer.flush();
            writer.close();
            os.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            throw new RuntimeException("Parse outstream error, Cause:", e);
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("Parse outstream error, Cause:", e);
        }
        return os;
    }

    public static Element parseTitleElement(Object titleObj)
    {
        Element titleEle = (Element) ((Element) temp.get(TITLE)).clone();
        if (null == titleObj) return titleEle;
        List tdList = titleEle.selectNodes("td");
        Element td;
        for (int i = 0; i < tdList.size(); i++) {
            td = (Element) tdList.get(i);
            fullField(td, titleObj);
        }
        return titleEle;
    }

    public static Element parseRecordElement(Object recordObj)
    {
        Element recordEle = (Element) ((Element) temp.get(RECORD)).clone();
        List tdList = recordEle.selectNodes("td");
        Element td;
        for (int i = 0; i < tdList.size(); i++) {
            td = (Element) tdList.get(i);
            fullField(td, recordObj);
        }
        return recordEle;
    }

    public static void fullField(Element tdEle, Object obj)
    {
        Attribute att = tdEle.attribute("id");
        if (null == att || null == att.getText() || 0 == att.getText().trim().length()) {
            return;
        }
        String fieldName = att.getText();
        if (null == fieldName || fieldName.trim().length() == 0) return;
        Method[] objMethod = obj.getClass().getDeclaredMethods();

        Object value;
        for (int i = 0; i < objMethod.length; i++) {
            if (("get" + (fieldName.trim())).equals(objMethod[i].getName())) {
                try {
                    value = objMethod[i].invoke(obj, new Object[]{});
                    value = (null == value ? "" : value);
                    tdEle.setText(value.toString());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

    public static String removeXMLHeader(OutputStream os)
    {
        String xml = os.toString();
        int position = xml.indexOf(">");
//        xml = xml.substring(position+1,xml.length());
//        position = xml.indexOf(">");
        return xml.substring(position + 1, xml.length());
    }
}

调用
OutputStream os = response.getOutputStream();
            response.setHeader(
"Content-disposition""attachment; filename=" + excelFileName);
            response.setContentType(
"application/msexcel");
            File excelTemplateFile 
= new File(report_path + templateFileName);
            String out 
= ExcelTemplateUtil.generateListToTemplate(titleObj, resultList, excelTemplateFile);
            response.getOutputStream().write(out.getBytes());
            os.close();

posted on 2007-08-08 21:47 wanglin 阅读(1150) 评论(3)  编辑  收藏

评论

# re: 一个生成excel的工具类 2007-08-09 15:20 静儿

原来excel报表通过html自动生成啊。  回复  更多评论   

# re: 一个生成excel的工具类[未登录] 2007-08-09 17:43 -274°C

<?php
header("Content-Type:application/vnd.ms-execl");
header("Content-Disposition:filename=test.xls");
……
很方便,其他语言道理一样。  回复  更多评论   

# re: 一个生成excel的工具类 2008-01-28 09:02 wanglin

有更好的方法
xlsWin = window.open('',''_blank, openPara);
xlsWin.document.write(inStr);
xlsWin.document.close();
xlsWin.document.execCommand('Saveas', true, fileName);

将response流instr放到一个document文件中保存。
  回复  更多评论   


只有注册用户登录后才能发表评论。


网站导航:
 
<2007年8月>
2930311234
567891011
12131415161718
19202122232425
2627282930311
2345678

导航

统计

常用链接

留言簿(1)

随笔档案

搜索

最新评论

阅读排行榜

评论排行榜