第一次发贴.
谢谢指教!
我在用excel作设计器作报表系统.
目前有一个问题,我用excel只能获取数据库的数据源.
我想能获得多种数据源
[b]问题补充:[/b]
可能早上没表述清楚.
谢谢回答.
是用excel作模板报表设计器,
数据源的导入是导入到excel中.
第一次发贴.
谢谢指教!
我在用excel作设计器作报表系统.
目前有一个问题,我用excel只能获取数据库的数据源.
我想能获得多种数据源
[b]问题补充:[/b]
可能早上没表述清楚.
谢谢回答.
是用excel作模板报表设计器,
数据源的导入是导入到excel中.
我前两天刚做了一个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;
/**
To change this template use File | Settings | File Templates.
*/
public class ExcelHandle
{
public ExcelHandle()
{
}
/**
/**
@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;
}
}
/**
}
/**
@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();
}
}
/**
@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;
}
/**
@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();
}
}
/**
@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();
}
}
}