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());
    }
}