- eyejava
- 等级:
- 性别:
- 文章: 1484
- 积分: 590
- 来自: 上海
|
需求:一些金融机构需要很多报表,比如资产负债表,他们希望按照以往的习惯把报表放在一个文本文件里面,并且用空格和横竖线划成表格的样子,最终可以用于打印或者字符终端显示。报表文本文件样子如下:(html里面对不齐,可以用记事本打开附件)。
xml 代码
- +------------------------------------------------------------------------
- | 资产 | 行 | 期初数 | 期末数 |
- | | 次 | | |
- |----------------------------+----+------------------+------------------+
- |流动资产 |0001| 0.00| 0.00|
- |----------------------------+----+------------------+------------------+
- | 现金及周转金 |0002| 96,482,019.59| 96,482,019.59|
- |----------------------------+----+------------------+------------------+
- | 存放中央银行款项 |0003| 0.00| 0.00|
- |----------------------------+----+------------------+------------------+
- | 专项央行票据 |0004| 0.00| 0.00|
- |----------------------------+----+------------------+------------------+
- | 央行专项扶持资金 |0005| 0.00| 0.00|
- |----------------------------+----+------------------+------------------+
- | 存放同业款项 |0006| 0.00| 0.00|
- |----------------------------+----+------------------+------------------+
- | 存放联行款项 |0007| 0.00| 0.00|
- |----------------------------+----+------------------+------------------+
- | 拆放同业 |0008| 0.00| 0.00|
- |----------------------------+----+------------------+------------------+
- | 拆放金融性公司 |0009| 0.00| 0.00|
- |----------------------------+----+------------------+------------------+
-
实现:报表数据在数据库,可以用jdbc查询出来,最终要生成文件,不由想到使用freemarker,那还需要的就是画一个ftl模版。
针对最终的文件形式,ftl如下:
java 代码
- %%${organization.number}
- <#assign prepage=0>
- <#assign pagenumber=30>
- <#assign currentnumber=0>
- <#assign currentpage=1>
- <#list rows as row>
- <#if (prerow?exists && prerow.curr_cd != row.curr_cd)>
- <#assign currentpage = currentpage + 1>
- <#assign currentnumber=0>
- +-----------------------------------------------------------------------------------------------------------------------------------------------+
- 事后监督 主管 复核 制表 报表日期 ${date?string("yyyy/MM/dd")}
- <!---->if>
- <#if currentnumber % pagenumber == 0> @@ xx金融机构资产负债表 &&
- 单位名称 ${organization.name} 报表类型 日报 制表日期 ${date?string("yyyy")} 年 ${date?string("MM")} 月 ${date?string("dd")} 日 币种 ${row.curr_cd}
- 单位: 元 第 ${currentpage} 页
- +-----------------------------------------------------------------------------------------------------------------------------------------------+
- | 资产 | 行 | 期初数 | 期末数 | 资产 | 行 | 期初数 | 期末数 |
- | | 次 | | | | 次 | | |
- <!---->if>
- |----------------------------+----+------------------+------------------+----------------------------+----+------------------+------------------|
- |${row.item_name}|${row.line_no?string("0000")}|${row.begin_bal?string("#,##0.00")?left_pad(18)}|${row.end_bal?string("#,##0.00")?left_pad(18)}|${row.item_name2}|${row.line_no?string("0000")}|${row.begin_bal2?string("#,##0.00")?left_pad(18)}|${row.end_bal2?string("#,##0.00")?left_pad(18)}|
- <#if (currentnumber % pagenumber == pagenumber - 1) || (row_has_next == false)>
- +-----------------------------------------------------------------------------------------------------------------------------------------------+
- 事后监督 主管 复核 制表 报表日期 ${date?string("yyyy/MM/dd")}
- <#assign currentpage = currentpage + 1><!---->if>
- <#assign prerow = row>
- <#assign prepage = currentpage>
- <#assign currentnumber = currentnumber + 1>
- <!---->
生成文件的Main方法代码如下:
java 代码
- import java.io.BufferedWriter;
- import java.io.File;
- import java.io.FileWriter;
- import java.io.IOException;
- import java.sql.Connection;
- import java.sql.Date;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.text.SimpleDateFormat;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
-
- import freemarker.template.Configuration;
- import freemarker.template.DefaultObjectWrapper;
- import freemarker.template.Template;
-
-
-
-
-
-
- public class Test {
-
- public static void main(String[] args) throws Exception {
- Class.forName("com.ibm.db2.jcc.DB2Driver");
-
- Connection conn = DriverManager.getConnection("jdbc:db2://ip:port/dbname", "username", "pwd");
-
- StringBuffer sb = new StringBuffer(
- "select record_date,cycle_no,bank_no,curr_cd,line_no,item_name,item_no,begin_bal,end_bal,item_name2,item_no2,begin_bal2,end_bal2 from FIN_BALANCE_REPORT ");
- sb.append("where RECORD_DATE = ?");
- sb.append("order by cycle_no,BANK_NO,CURR_CD,line_no");
- PreparedStatement pstmt = conn.prepareStatement(sb.toString());
- Date date = Date.valueOf("2006-6-30");
- pstmt.setDate(1, date);
- ResultSet rs = pstmt.executeQuery();
- Map root = new HashMap();
- List rows = new ArrayList();
- Map org = new HashMap();
- int pre_cycle_no = -1;
- String pre_bank_no = "";
- while (rs.next()) {
- Map row = new HashMap();
- int cycle_no = rs.getInt("cycle_no");
- String bank_no = rs.getString("bank_no");
- if (pre_cycle_no != -1 && pre_cycle_no != cycle_no
- || (pre_bank_no.length() > 0 && !pre_bank_no.equals(bank_no))) {
- org.put("number", pre_bank_no);
- org.put("name", pre_bank_no);
- root.put("organization", org);
- root.put("date", date);
- root.put("rows", rows);
-
- Configuration cfg = new Configuration();
- cfg.setDirectoryForTemplateLoading(new File("D:/work/template/"));
- cfg.setObjectWrapper(new DefaultObjectWrapper());
- Template temp = cfg.getTemplate("资产负债表.ftl");
- String dateString = new SimpleDateFormat("yyyyMMdd").format(date);
- String fileName = "T" + pre_bank_no + "_" + dateString + "_" + pre_cycle_no + pre_bank_no;
- File file = new File("D:/work/dest/" + fileName);
- FileWriter fw = new FileWriter(file);
- try {
- BufferedWriter bw = new BufferedWriter(fw);
- temp.process(root, bw);
- bw.flush();
- } catch (IOException e) {
- e.printStackTrace();
- } finally {
- fw.close();
- }
- rows = new ArrayList();
- }
- int line_no = rs.getInt("line_no");
- row.put("line_no", new Integer(line_no));
- row.put("item_name", rightPad(rs.getString("item_name"), 28));
- row.put("begin_bal", rs.getBigDecimal("begin_bal"));
- row.put("end_bal", rs.getBigDecimal("end_bal"));
- row.put("item_name2", rightPad(rs.getString("item_name2"), 28));
- row.put("begin_bal2", rs.getBigDecimal("begin_bal2"));
- row.put("end_bal2", rs.getBigDecimal("end_bal2"));
- row.put("curr_cd", rs.getString("curr_cd"));
-
- rows.add(row);
-
- root = new HashMap();
- pre_cycle_no = cycle_no;
- pre_bank_no = bank_no;
- }
-
- rs.close();
- pstmt.close();
- conn.close();
-
- }
-
-
-
-
- public static String rightPad(String s, int minLength) {
- int ln = length(s);
- if (minLength <= ln) {
- return s;
- }
-
- StringBuffer res = new StringBuffer(minLength);
-
- res.append(s);
-
- int dif = minLength - ln;
- for (int i = 0; i < dif; i++) {
- res.append(' ');
- }
-
- return res.toString();
- }
-
-
-
-
- public static String leftPad(String s, int minLength) {
- int ln = length(s);
- if (minLength <= ln) {
- return s;
- }
-
- StringBuffer res = new StringBuffer(minLength);
-
- int dif = minLength - ln;
- for (int i = 0; i < dif; i++) {
- res.append(' ');
- }
-
- res.append(s);
-
- return res.toString();
- }
-
-
-
-
- public static int length(String s) {
- int counter = 0;
- for (int i = 0; i < s.length(); i++) {
- char c = s.charAt(i);
- if (c < 255) {
- counter++;
- } else {
- counter = counter + 2;
- }
- }
- return counter;
- }
- }
这个代码很不完善,比如没有异常捕获、Log机制、目标目录和用到的模版文件都是写死,仅仅作为测试使用。
声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
|