Needed jar files:
- commons-beanutils-1.8.3
- commons-collections-3.2.1
- commons-digester-2.1
- commons-jexl-2.0.1
- commons-logging-1.1.1
- jxls-core-1.0-RC-2
- jxls-reader-1.0-RC-2
- log4j-1.2.13
- poi-3.7-20101029
- poi-ooxml-3.7-20101029
- postgresql-8.4-701.jdbc4
Code out excel file:
PostgresDAO dbCon = new PostgresDAO();try {
// connect DB
dbCon.ConnectDB();
Map beans = new HashMap();
ReportManager rm = new ReportManagerImpl(dbCon.con, beans);
beans.put("rm", rm);
InputStream is = new BufferedInputStream(new FileInputStream("D:/rptTemplateNew.xls"));
OutputStream os = new BufferedOutputStream(new FileOutputStream("D:/out.xls"));
XLSTransformer transformer = new XLSTransformer();
HSSFWorkbook resultWorkbook = (HSSFWorkbook) transformer.transformXLS(is, beans);
resultWorkbook.write(os);
os.close();
is.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
dbCon.Disconnect();
}
Code in Excel file:
<jx:forEach items="${rm.exec("select distinct objectname from tbdiary where diarytype='X' order by objectname asc")}" var="objectname"> ${objectname.objectname}
<jx:forEach items="${rm.exec("select distinct description from tbdiary where diarytype='X' and objectname='" + objectname.objectname + "' order by description asc")}" var="description">
<jx:forEach items="${rm.exec("select to_char(d.documentdate,'dd-mm-yyyy') as documentdate, documentnumber, m.materialid as materialid, materialname as materialname, unit as unit, d.amount as amount, d.price as price, (d.amount*d.price) as totalvalue, d.description as description from tbmaterial m, tbdiary d where objectname='" + objectname.objectname + "' and m.materialid=d.materialid and diarytype='X' and d.description='" + description.description + "' and to_date(to_char(d.documentdate,'DD-MM-YYYY'),'DD-MM-YYYY') >= to_date('01-07-2011','DD-MM-YYYY') and to_date(to_char(d.documentdate,'DD-MM-YYYY'),'DD-MM-YYYY') <= to_date('31-07-2011','DD-MM-YYYY') order by documentdate asc")}" var="supplier">
${supplier.documentdate} ${supplier.documentnumber} ${supplier.materialid} ${supplier.materialname} ${supplier.unit} ${supplier.amount} ${supplier.price} ${supplier.totalvalue} ${supplier.description}
</jx:forEach>
Total1 $[SUM(H10)]
</jx:forEach>
Total2 $[SUM(H12)]
</jx:forEach>
Grand total $[SUM(H14)]
Không có nhận xét nào:
Đăng nhận xét