用excel作报表设计器,能不能获取多种数据源如xsl,javabean等(第一次发贴)

Question

第一次发贴.

谢谢指教!

我在用excel作设计器作报表系统.

目前有一个问题,我用excel只能获取数据库的数据源.

我想能获得多种数据源
[b]问题补充：[/b]
可能早上没表述清楚.

谢谢回答.

是用excel作模板报表设计器,

数据源的导入是导入到excel中.

Answer 1

我前两天刚做了一个EXCEL报表 ，我有的jxl中的方法
把所有的业务都用JAVA 实现
看下这个类

import jxl.*;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.Boolean;
import jxl.write.biff.RowsExceededException;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.LinkedList;
import java.util.List;

/**

• Created by IntelliJ IDEA.
• User: xl
• Date: 2005-7-17
• Time: 9:33:22

• To change this template use File | Settings | File Templates.
  */
  public class ExcelHandle
  {
  public ExcelHandle()
  {
  }

  /**

  • 读取Excel *
  • @param filePath */ public static void readExcel(String filePath) { try { InputStream is = new FileInputStream(filePath); Workbook rwb = Workbook.getWorkbook(is); //Sheet st = rwb.getSheet("0")这里有两种方法获取sheet表,1为名字，而为下标，从0开始 Sheet st = rwb.getSheet(0); for(int i=0;i<5;i++) { Cell c00 = st.getCell(i,0); //通用的获取cell值的方式,返回字符串 String strc00 = c00.getContents(); //获得cell具体类型值的方式 if(c00.getType() == CellType.LABEL) { LabelCell labelc00 = (LabelCell)c00; strc00 = labelc00.getString(); } //输出 System.out.println(strc00); } //关闭 rwb.close(); } catch(Exception e) { e.printStackTrace(); } }

  /**

  • 产生一个sheet *
  • @param os / public static WritableSheet createExcelSheet(WritableWorkbook wwb) { return createExcelSheet(wwb,"sheet1"); } /*
  • 产生一个sheet *
  • @param os

  • @param sheetName;
    */
    private static WritableSheet createExcelSheet(WritableWorkbook wwb,String sheetName)
    {

    try
    {

    WritableSheet ws = wwb.createSheet(sheetName,0);
    return ws;
    }
    catch(Exception e)
    {
    e.printStackTrace();
    return null;
    }
    }

  private static WritableWorkbook createExcelWorkbook(OutputStream os)
  {

  try
  {
     WritableWorkbook wwb = Workbook.createWorkbook(os);
     //创建Excel工作表 指定名称和位置
  
     return wwb;
  }
  catch(Exception e)
  {
      e.printStackTrace();
      return null;
  }
  

  }

  /**

  • 添加普通的字符串Lable *
  • @param
  • @param value
  • @param colNum
  • @param rowNum */ private static void addItemString(WritableSheet ws,String value,int colNum,int rowNum,String type) throws RowsExceededException, WriteException {
    if(type==null || type.equals("string")){ Label label = new Label(colNum,rowNum,value); ws.addCell(label); }else if(type.equals("double")){ Number num = new Number(colNum,rowNum,Double.parseDouble(value)); ws.addCell(num); }

  }
  /**

  • @param hasXH 是否带序号
  • @param path
    
  • @param filename
  • @param heads
  • @param itemNames

  • @param list
    */
    public static void writeExcel(boolean hasXH,String path,String filename,String[] heads,String[] itemNames,String[] types,List list)
    {

    OutputStream os = null;
    try{
    os= new FileOutputStream(path+filename);
    }catch(FileNotFoundException e1){
    
    File file = new File(path);
    if(!file.exists()){//判断文件夹是否存在
    file.mkdir();
    }
    try {
    os= new FileOutputStream(path+filename);
    } catch (FileNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }
    try
    {
    WritableWorkbook wwb=createExcelWorkbook(os);
    WritableSheet ws=createExcelSheet(wwb);
    int num = 0;
    if(hasXH){//如果需要序号
    addItemString(ws,"序号",0,0,null );
    num = 1;
    }

    for(int j=num;j<heads.length+num;j++)
    {               
        addItemString(ws,heads[j-num],j,0,null ); //ws,数据，列，行               
    }
    for(int i=1;i<=list.size();i++)
    {
        addItemString(ws,String.valueOf(i),0,i ,null);
        Object obj=list.get(i-1);
        String[] results=getItemsValue(itemNames,obj);//获取集合里一行的数据
        for(int j=0;j<results.length;j++)
        {
            if(types==null){
                addItemString(ws,results[j],j+num,i ,null);
            }else{
                addItemString(ws,results[j],j+num,i ,types[j]);
            }
        }
    
    }
    
    wwb.write();
    wwb.close();
    os.close();
    

    }catch(Exception e)
    {
    e.printStackTrace();
    }

  }

  /**

  • 根据ｈｅａｄｓ从ｏｂｊ中取出数据 *
  • @param os
  • @throws InvocationTargetException
  • @throws IllegalAccessException

  • @throws IllegalArgumentException
    */
    public static String[] getItemsValue(String[] heads,Object obj) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException
    {

    if(heads==null||obj==null)
        return null;
    String[] results=new String[heads.length];
    Method[] methods=obj.getClass().getMethods();
    for(int i=0;i<heads.length;i++)
    {
        for(int j=0;j<methods.length;j++)
        {
            if(methods[j].getName().compareToIgnoreCase("get"+heads[i])==0)
            {
                Object resultojb=methods[j].invoke(obj, null);
                if(resultojb!=null){
                    results[i]=resultojb.toString();
                }else{
                    results[i] = "";
                }
                break;
            }
        }
    }
    return results;
    

    }

  /**

  • 输出Excel *

  • @param os
    /
    public static void writeExcel(OutputStream os)
    {
    try
    {
    /*
    * 只能通过API提供的工厂方法来创建Workbook，而不能使用WritableWorkbook的构造函数，
    * 因为类WritableWorkbook的构造函数为protected类型
    * method(1)直接从目标文件中读取WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));
    * method(2)如下实例所示 将WritableWorkbook直接写入到输出流

     */
    WritableWorkbook wwb = Workbook.createWorkbook(os);
    //创建Excel工作表 指定名称和位置
    WritableSheet ws = wwb.createSheet("Test Sheet 1",0);
    
    //**************往工作表中添加数据*****************
    
    //1.添加Label对象
    Label label = new Label(0,0,"this is a label test");
    ws.addCell(label);
    
    //添加带有字型Formatting对象
    WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
    WritableCellFormat wcf = new WritableCellFormat(wf);
    Label labelcf = new Label(1,0,"this is a label test",wcf);
    ws.addCell(labelcf);
    
    //添加带有字体颜色的Formatting对象
    WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
            UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
    WritableCellFormat wcfFC = new WritableCellFormat(wfc);
    Label labelCF = new Label(1,0,"This is a Label Cell",wcfFC);
    ws.addCell(labelCF);
    
    //2.添加Number对象
    Number labelN = new Number(0,1,3.1415926);
    ws.addCell(labelN);
    
    //添加带有formatting的Number对象
    NumberFormat nf = new NumberFormat("#.##");
    WritableCellFormat wcfN = new WritableCellFormat(nf);
    Number labelNF = new jxl.write.Number(1,1,3.1415926,wcfN);
    ws.addCell(labelNF);
    
    //3.添加Boolean对象
    Boolean labelB = new jxl.write.Boolean(0,2,false);
    ws.addCell(labelB);
    
    //4.添加DateTime对象
    jxl.write.DateTime labelDT = new jxl.write.DateTime(0,3,new java.util.Date());
    ws.addCell(labelDT);
    
    //添加带有formatting的DateFormat对象
    DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");
    WritableCellFormat wcfDF = new WritableCellFormat(df);
    DateTime labelDTF = new DateTime(1,3,new java.util.Date(),wcfDF);
    ws.addCell(labelDTF);
    
    //添加图片对象,jxl只支持png格式图片
    /*
    File image = new File("f:\\2.png");
    WritableImage wimage = new WritableImage(0,1,2,2,image);
    ws.addImage(wimage);
    //写入工作表
     * 
     */
    wwb.write();
    wwb.close();
    

    }
    catch(Exception e)
    {
    e.printStackTrace();
    }
    }

  /**

  • 拷贝后,进行修改,其中file1为被copy对象，file2为修改后创建的对象
  • 尽单元格原有的格式化修饰是不能去掉的，我们还是可以将新的单元格修饰加上去，
  • 以使单元格的内容以不同的形式表现
  • @param file1

  • @param file2
    */
    public static void modifyExcel(File file1,File file2)
    {
    try
    {
    Workbook rwb = Workbook.getWorkbook(file1);
    WritableWorkbook wwb = Workbook.createWorkbook(file2,rwb);//copy
    WritableSheet ws = wwb.getSheet(0);
    WritableCell wc = ws.getWritableCell(0,0);
    //判断单元格的类型,做出相应的转换

    if(wc.getType() == CellType.LABEL)
    {
        Label label = (Label)wc;
        label.setString("The value has been modified");
    }
    wwb.write();
    wwb.close();
    rwb.close();
    

    }
    catch(Exception e)
    {
    e.printStackTrace();
    }
    }

  //测试
  public static void main(String[] args)
  {
  try
  {
  /*
  //读Excel
  ExcelHandle.readExcel("d:/testRead.xls");

      //输出Excel
  
      File fileWrite = new File("d:/testWrite.xls");
      fileWrite.createNewFile();
      OutputStream os = new FileOutputStream(fileWrite);
      ExcelHandle.writeExcel(os);
      //修改Excel
      ExcelHandle.modifyExcel(new File(""),new File(""));
      */
      List list=new LinkedList();
      String[] heads=new String[2];
      heads[0]="roleid";
      heads[1]="roleName";
      for(int i=0;i<100;i++)
      {
  
      }
  
  }
  catch(Exception e)
  {
     e.printStackTrace();
  }
  

  }
  }