Thứ Hai, 22 tháng 8, 2011

Exporting to excel by using jxls lib with double quote

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)]