Excel作为一种常见的文件格式广泛存在，很多报表的数据来源于Excel。由于报表一般都带有参数，经常需要对原始数据进行分组、过滤等，都需要再编写程序代码才能满足有参数报表的查询需求。

润乾报表使用Excel数据源，需要用Java程序处理文件，通过自定义数据集为报表提供数据源支持。这里通过一个实例说明润乾报表实现过程，以及改进方式。

报表描述

基金公司将股票信息以每月一个Excel存储，命名方式为：stock_yyyyMM.xls。包括每只股票的股票编码、交易日期和收盘价，现报表参数为起始年月和结束年月，报表按照交易日排序，展现股票交易记录列表。

    Excel内容如下：

    报表格式如下：

润乾报表实现

自定义数据集

这里使用poi读取操作Excel，以下为实现步骤：

1、  定义股票信息类，用于存储股票交易信息

class Stock {

    //省略构造函数和get set方法

    private String code;

    private String tradingDate;

    private String price;

}

 

2、  在自定义数据集中接收并解析报表参数，判断使用哪些文件；

       // 取得参数列表并分别取得它的参数名与值,宏与之类似

       Mapmap = ctx.getParamMap(false);

       int begin = Integer.parseInt(map.get("begin").toString());

       int end = Integer.parseInt(map.get("end").toString());

 

       for (int i = begin; i <= end;i++) {

           System.out.println(i);

           if (i % 100 > 0&& i % 100 < 13) {

              StringfileName = "E:\\stock_" + i + ".xls";

              System.out.println("Read "+ fileName + " startup...");

              readExcel(fileName);//读入并解析Excel内容，并将结果存入List

           }

       }

 

3、  使用getCellValue()方法判断单元格格式，由于Excel会将日期按照numeric类型存储，所以要在程序中区分数值和日期，并完成相应转换

publicstatic StringgetCellValue(Cell cell) {

       switch (cell.getCellType()) { // 根据cell中的类型来输出数据

       case HSSFCell.CELL_TYPE_NUMERIC:

           //解析自定义日期格式yyyy-mm-ddd

           if(cell.getCellStyle().getDataFormat() == 184) {

              SimpleDateFormatsdf = newSimpleDateFormat("yyyy-MM-dd");

              double value =cell.getNumericCellValue();

              Datedate = org.apache.poi.ss.usermodel.DateUtil

                     .getJavaDate(value);

              return sdf.format(date);

           }

           returncell.getNumericCellValue() + "";

       case HSSFCell.CELL_TYPE_STRING:

           returncell.getStringCellValue();

       case HSSFCell.CELL_TYPE_FORMULA:

           returncell.getCellFormula().toString();

       default:

           returnnull;

       }

    }

 

4、实现读入Excel方法readExcel()，根据传入Excel文件名读入并解析，将文件内容存入List

publicstaticvoid readExcel(StringfileName) {

       boolean isE2007 = false; // 判断是否是excel2007格式

       if (fileName.endsWith("xlsx"))

           isE2007= true;

       try {

           InputStreaminput = newFileInputStream(fileName); // 建立输入流

           Workbookwb = null;

           // 根据文件格式(2003或者2007)来初始化

           if (isE2007)

              wb= newXSSFWorkbook(input);

           else

              wb= newHSSFWorkbook(input);

           Sheetsheet = wb.getSheetAt(0); // 获得第一个表单

           Iterator<Row>rows = sheet.rowIterator(); // 获得第一个表单的迭代器

           Stringcode = null;

           Stringdate = null;

           Stringprice = null;

           while (rows.hasNext()) {

              Rowrow = rows.next(); // 获得行数据

              Iterator<Cell>cells = row.cellIterator(); // 获得第一行的迭代器

              while (cells.hasNext()) {

                  Cellcell = cells.next();

                  System.out.println("Cell #"+ cell.getColumnIndex());

                  switch (cell.getColumnIndex()){

                  case 0:

                     code= getCellValue(cell);

                     break;

                  case 1:

                     date= getCellValue(cell);

                     break;

                  case 2:

                     price= getCellValue(cell);

                     break;

                  }

              }

              if (row.getRowNum() == 0){

                  colList.add(new Stock(code, date,price));

              }else{

                  list.add(new Stock(code, date,price));

              }

           }

       }catch(IOException ex) {

           ex.printStackTrace();

       }

    }

 

5、定义排序类，实现compare方法比较交易日期

publicclass ComparatorStock implementsComparator {

    publicint compare(Objecto1,Object o2){

       Stockstock1 = (Stock)o1;

       Stockstock2 = (Stock)o2;

       returnstock1.getTradingDate().compareTo(stock2.getTradingDate());

    }

}

 

6、使用Collections.sort完成List排序

    ComparatorStockcs = newComparatorStock();

    Collections.sort(list,cs);

 

7、创建数据集，并根据排序后List内容设置数据集数据

// 构造一个数据集ds1，设置列名

       DataSetds1 = newDataSet("ds1");

       Stockcol = colList.get(0);

       ds1.addCol(col.getCode());

       ds1.addCol(col.getTradingDate());

       ds1.addCol(col.getPrice());

 

       // 设置数据集中的数据

       for (int i = 1; i <list.size(); i++) {

           Stockstock = list.get(i);

           com.runqian.report4.dataset.Rowrr = ds1.addRow();

           rr.setData(1,stock.getCode());

           rr.setData(2,stock.getTradingDate());

           rr.setData(3,stock.getPrice());

       }

 

       return ds1;

报表调用

    报表使用自定义数据集类型:

    设置报表模板及表达式:

通过自定义数据集后在报表中调用可以完成本例的报表需求，润乾报表支持用户自定义数据集处理数据，体现了极大的灵活性。但过于依赖Java编程无疑增加了报表开发的难度，本例只是简单的完成了文件读取和排序，当包含分组、连接等计算时程序的复杂度会陡然上升，对报表开发人员来说都是不小的挑战。当然也可以用报表工具来完成排序等运算，但这样会导致占用较大的内存（特别是有过滤动作时，需要把数据都取到报表端再执行过滤），性能也会受到较大影响，有许多复杂的文件操作也会超出报表计算能力范围。

这种情况下，采用润乾报表基础上的计算强化版集算报表将是个不错的选择。集算报表内置的集算器对Excel文件读取的先天支持可以帮助集算报表快速完成Excel数据源报表。本例在集算报表中可以这样完成。

集算报表实现

    首先使用集算完成文件读入和排序，集算脚本如下：

    A1：根据起始结束月份参数列出中间包含的月份

    A2：根据A1的计算结果，逐个读入月份文件，并将结果合并

    A3：为报表返回按照交易日期排序后结果

报表调用

数据集设置

    集算报表中使用集算器数据集类型，选择上面编辑好的集算器脚本文件（fromExcel.dfx）:

    其中b、e为报表参数，begin、end为集算脚本参数.

 

报表模板及表达式

    通过上面的步骤可以看到，使用集算报表完成Excel数据源报表非常简单，既不需要大量的Java编程，也不必为到底选用何种方式（poi、jxl、jcom）操作Excel而费心。集算器脚本只需要3行即可完成计算过程，而同样的计算任务Java程序则需要几百行。集算报表让文件拥有了计算能力，使得开发文件数据源报表更为简单方便。

 

此外，如果集算脚本比较简单，可以直接使用集算报表内置的脚本数据集（一种数据集类型），将脚本直接嵌入到报表模板中，而不必在单独在集算器中完成。实现方式如下：

   1、在数据集设置窗口中点击“增加”按钮，弹出数据集类型对话框，选择“脚本数据集”:

    2、在弹出的脚本数据集编辑窗口中编写集算脚本:

    脚本数据集中可以直接使用报表定义的参数，如上述脚本中的begin、end即为报表参数。 

    3、报表调用，与其他数据集使用方式一致，不再赘述。