很多人都直接編輯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("http://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();